Host Engineering Forum
General Category => Do-more CPUs and Do-more Designer Software => Topic started by: Bolt on January 07, 2018, 10:04:44 PM
-
How would I go about posting data from a Do-More to a MySQL database on a local server?
I don't really know where to begin, and have lots of questions. I have searched the forum and read the posts.
How does the Do-More send data to the Server? HTML POST from Do-More and then a PHP script on the Server uploads it to the Database?
My main objective is data logging. At this time I have no need to send data from the Server to the PLC.
What is too much data to send to a SQL database? If I log every second, and have 50 elements, thats 1.5 billion data points per year....
So, I need to log only status changes for 'bit' elements. Should be easy enough.
For some analog elements, I would need a low resolution, like once per minute. Others, once per second or 10 seconds would be better.
Do I log all this data, analog and bits, into the same Table? Or do I create separate Tables in the same Database for analog and bits?
Do I log the varying resolutions of analog data in the same Table?
I'll leave my questions at that for now. Thank you in advance for any input.
-
For sending data to the server you could do an HTML POST to a Web server (I think BobO has some sample code out there) or you could setup a server to accept TCP or UDP connections and avoid the overhead of the HTTP request handling. If you are OK with losing a data point every now and then, UDP is a decent way to stream data out. Otherwise stick with TCP whether it be via web requests or straight socket communications. I think MySQL ships with or has some add-ons that will allow direct access via web requests but then you'd need to do all the string building on the PLC. I think to start I'd use a PHP web server (or whatever language you prefer) to parse the data from the PLC and form the SQL. This will allow you to scrub and transform the data as needed.
Too much data is dependent on how your server is setup. I have had very successful implementations that handled 1.5B inserts per year. The key is to ensure your database is properly tuned. There are some scripts out there that when run against your server will recommend optimal settings if you are not or do not have a DBA. Back when I was working with MySQL regularly, I would run this before deployment (after load testing) and then periodically while in production to ensure the performance was optimal for the growth. Also, be sure to size your memory and CPUs properly.
How you log it in the database really depends on how you need to consume the data later. It may work fine in a single table. Another option would be to break up by your sampling period or sensor types. Maybe all of your 1 second data ends up in one table and the 10 second in another. This way you don't end up with a lot of NULL or empty values in the fields that aren't being updated. You could also use an Entity-Attribute-Value model and insert one row for each bit of data instead of having multiple columns per row. This can be useful but can cause performance issues when reporting if not done correctly.
A big factor when you start collecting this much data is really understand how you will be consuming or reporting the data once it is in the database. This will help you determine how to setup your schema and the indexes. The indexes could make or break your reporting.
If your data points are not proprietary (or if you could generalize) I'd be happy to take a look at what you are collecting and perhaps be able to make some better suggestions.
-
I agree with what @jcottrill said.
For simplicuty, I would probably just use a simple HTTP GET from the PLC to the server.
Do you already have the server?
If so, what OS is it running?
Do you prefer php or .net?
Is the database existing?
If you know exactly how the data is going to be used later, then that will help for knowing how to store it.
I will not be much help with php, but if you're using IIS with .net then I may be of some help.
-
I have recently setup a MariaDB SQL server on a QNAP server on my local network. That kind of rules out .net and leaves me with php. And maybe python for server side scripting?
I have setup a test database with some imported CSV data from Do-More via phpmyadmin. I have played with this some. I have most of the data I am interested in currently posting to Trend Views in DMD, I am looking at this data to get a feel for the scope of the data, will see what I can get together to show here.
I can write ladder logic to gather the data at desired intervals to send to the server. I will need some guidance on the HTTP GETs from PLC to server. I would think buffering should be possible, where if any connection were to be down (for a short time), data would not be lost?
My main objective is to access the database via a local, intranet website, and analyze the data from there. I am still looking for a suitable graphing script to use to display the data on website for easy analyzing. I like the looks of this https://www.highcharts.com/demo/line-boost (https://www.highcharts.com/demo/line-boost), would prefer something opensource, but may spend more than that trying to maintain it all. I have yet to see anything with the capabilities this has.
-
Is Maria actually running on the QNAP or are you just storing the data there? This is where you are likely to run into issues as you aren't going to have much control and relatively speaking, the QNAPs are pretty gutless. Don't get me wrong, they are fine as a NAS but to my knowledge they are not a general purpose server. If it works for you then great but I'd be a bit surprised at the amount of data you are talking about. There are plenty of great free charting libraries out there so take the money you were thinking of spending on that and buy a refurbed server. I just purchased an HP DL380 with 2 6 core processors and 64GB or RAM for $325. You don't need anywhere near that kind of power and should be able to find a server for closer to $100. If you need a warranty or are a bit worried then check out ServerMonkey or XBytes. Heck even a spare PC that may be collecting dust may due better than the QNAP but testing and time will tell you.
Not trying to dump on your parade, just making sure you don't get your hopes too high. There may be some here that have more experience with the QNAPs and maybe my data is outdated.
-
For the GET or POST, you can use a PING to check server availability, then use a TCPOPEN to the servers ip, then a STREAMOUT
GET HTTP://YourIP/YourPageName/yourQueryString$0D$0AYou will then JMP to a stage with a timer in it that watches for data to come into your .InQueue and do a STREAMIN. This will let you know if your GET/POST was successful. You can then CLOSE the connection.
Your PHP page will parse your query string and then pass the data over to SQL. I prefer to use stored procedures for all SQL transactions. You should be able to build a stored procedure, and then send data to it using your php scripts. This will also prevent SQL Injection (important even on a local intranet).
If any transaction is not successful, you should be able to save the query string and resend it later, this should limit your data loss.
-
I do not know anything about QNAP, but I would just buy a cheap refurbished PC and use it. You don't need a server OS, and should be able to get a decent system cheap that you could install a linux os on. Or get a cheap windows machine and run WAMP (Or IIS if you wanted).
-
There are some examples for GET requests in this thread: http://forum.hosteng.com/index.php/topic,993.60.html
-
plcnut brings up a good point with SQL injection but you can also use prepared statements and avoid the need for stored procedures. Stored procedures have their place but in a lot of implementations just add an extra hop when you are just getting started with SQL. I'd keep it simple and just use prepared statements.
// BAD -- Concatenated query string with values. If your code looks like this then stop
// and read up on prepared statements.
$query = "select * from data where id = " + $myId;
$res = $conn->query(sql);
-
For the GET or POST, you can use a PING to check server availability, then use a TCPOPEN to the servers ip, then a STREAMOUT GET HTTP://YourIP/YourPageName/yourQueryString$0D$0AYou will then JMP to a stage with a timer in it that watches for data to come into your .InQueue and do a STREAMIN. This will let you know if your GET/POST was successful. You can then CLOSE the connection.
Your PHP page will parse your query string and then pass the data over to SQL. I prefer to use stored procedures for all SQL transactions. You should be able to build a stored procedure, and then send data to it using your php scripts. This will also prevent SQL Injection (important even on a local intranet).
If any transaction is not successful, you should be able to save the query string and resend it later, this should limit your data loss.
I have written a PHP script to import the query string into database.
I have built a TCP connection program in DMD.
When I run the program, I either get no response, or HTTP/1.1 400 Bad Request. I have tried various versions, HTTP/1.0 $0D$0A, $0D$0A$0D$0A, etc.
When I copy the string from the DMLogger into the web browser (minus the GET), it uploads to the database successfully.
192.168.0.91 Port 29298(0x7272),01/08/18 18:03:45.029 ,"HTTP/1.1 400 Bad Request.."
192.168.0.91 Port 29298(0x7272),01/08/18 18:03:45.029 ,".."
192.168.0.91 Port 29298(0x7272),01/08/18 18:03:45.029 ," HTTP/1.0 "
192.168.0.91 Port 29298(0x7272),01/08/18 18:03:45.029 ,"GET HTTP://192.168.0.10/post.php/?TimeStamp=1515456225&T0Temp=72.22&P0Temp=77.57"
What am I overlooking? I always seem to struggle with these requests. Not my strong suit...
-
Is your page expecting a POST pr a GET?
You also need to remove the last forward slash between php and the question mark.
-
Oops, I did look at that / and thought it looked out of place and double check.
Anyways, I'm guessing my script needs a GET, as it's a URL containing the info, vs a POST with the info in the body.
Here's my script
<?php
$hostname = "localhost";
$username = "XXXXXXXX";
$password = "YYYYYYYYYYY";
$db = "TestData";
$conn = mysqli_connect($hostname,$username,$password,$db);
if (!$conn) {
die("Connection failed: ". $mysqli_connect_error());
}
echo "Connected successfully <br>";
$sql = "INSERT INTO Temperatures (TimeStamp, T0Temp, P0Temp) VALUES (FROM_UNIXTIME($_GET[TimeStamp]),$_GET[T0Temp],$_GET[P0Temp])";
if (mysqli_query($conn, $sql)) {
echo "New record created succcessfully";
} else {
echo "Error: " .$sql . "<br>" . mysqli_error($conn);
}
mysqli_close($conn);
?>
Like I said, the script works from a browser, and it returns:
Connected successfully
New record created succcessfully
I can clean these echo statements up later to let the PLC decipher them easier.
-
Try leaving out the HTTP/1.0 and just do a single $0D$0A immediately after the query string.
-
Hey Bolt,
Just a friendly word of caution. I'd consider changing your insert from your concatenated/interpolated string to using a prepared statement. It may seem a bit tedious at first but is a good habit to get into as it will help prevent SQL injection. When you get into inserting strings it also helps by handling escape codes for quotes and things like that. The following link provides some details. Where this becomes useful is when inserting batches of records. The SQL server doesn't have to redo the query plan for each insert.
https://www.w3schools.com/php/php_mysql_prepared_statements.asp
$stmt = $conn->prepare("INSERT INTO Temperatures (TimeStamp, T0Temp, P0Temp) VALUES (?, ?, ?)");
// If your temps are not represented by ints then the last two i's below should be changed to d's
$stmt->bind_param("iii", $ts, $t0Temp $p0Temp);
// set parameters and execute
$ts = $_GET[TimeStamp];
$t0Temp = $_GET[T0Temp];
$p0Temp = $_GET[P0Temp];
$stmt->execute();
-
Try leaving out the HTTP/1.0 and just do a single $0D%0A immediately after the query string.
Still no luck. Bad request. You do mean $0D$0A right?
-
Hey Bolt,
Just a friendly word of caution. I'd consider changing your insert from your concatenated/interpolated string to using a prepared statement. It may seem a bit tedious at first but is a good habit to get into as it will help prevent SQL injection. When you get into inserting strings it also helps by handling escape codes for quotes and things like that. The following link provides some details. Where this becomes useful is when inserting batches of records. The SQL server doesn't have to redo the query plan for each insert.
https://www.w3schools.com/php/php_mysql_prepared_statements.asp
Once I get all this working simply, I will go back and clean up things like that. I like that website, I have learned a lot lot there the last few days.
-
Try leaving out the HTTP/1.0 and just do a single $0D%0A immediately after the query string.
Still no luck. Bad request. You do mean $0D$0A right?
Yes, I corrected my post.
I wish I had a way to test from here. I have worked with WAMP in the past, and don't remember any issues like this.
I will be thinking on it.
-
I did some testing this morning using Fiddler from here: https://www.telerik.com/fiddler
Using this simple debugger, you can see what the request and response headers actually look like (Even in Hex).
I was using Do-more to open this thread on forum.hosteng using this request:
GET http://50.240.102.59/index.php/topic,2214.0.html HTTP/1.1$0D$0AUser-Agent: DoMore/2.1$0D$0AHost: forum.hosteng.com$0D$0A$0D$0A
Using Fiddler, you will find that each line of the request header is separated by a CRLF, and that the header is terminated by a double CRLF.
I am guessing that your server is looking for something in the request header that Do-more is not providing. Maybe all you need to do is add is User-Agent, or maybe the Host.
Either way, I would suggest that you use Fiddler, and enter your request string into the "Composer" tab, and then run the query. You can then click on the response shown in the left pane and view the RAW or the Hex of the Request as well as the Response, and find what makes the server happy.
-
Thank you for your continued help in my projects.
I have the GET command working from the PLC. It is back to the first statement, GET http://192.168.0.10/post.php?TimeStamp=1515510728&T0Temp=63.33&P0Temp=147.4<CR><LF>
I'm not sure what changed now, I am pretty sure I tried all these same iterations yesterday. Even after I took the / out infront of the ? My last attempts last night were via remote PC access on my phone, so I might have missed something there.
I can't get a response to the PLC from the server. After sending, I jump to a Response Timer. When .InQueue != 0, it jumps to a STREAMIN Stage, and that STREAMIN command Jumps to my Error Stage on Fail every time (and leaves the string empty). Is there something I can do for the server to keep connection open longer, or what is causing the data in the queue to "disappear"?
-
On your STREAMIN, change the 'Length' to '$MariaDB.InQueue'.
-
Thanks. Made all the difference.
-
Cool!
It is pretty grand when you get to watch your code do it's thing!
-
How would one go about logging edge triggered boolean data to MySQL? It seems silly to log continuously while bit is ON or OFF. But triggering it with a delta contact would get me a timestamp at bit ON, and a timestamp at bit OFF. How could I digest this in MySQL? I would like to display it graphically, like trend view does. I've googled it but am coming up empty on how to deal with the ON/OFF transition. In my database I would need to log an OFF bit right before before the transition to ON, and log an ON bit right before it turns OFF. Am I looking at this wrong?
I've got it working really well. I have built a website (on local server) to pull up all the data, it's working well, both on phone and desktop. Drop down menus to pull up various graphs. Here's a screenshot. I found a javascript charting solution that works nice, scrolls/zooms/pans well with lots of data. I can easily load 14 days of 1 minute data onto the webpage without taking very long to load. That is enough data on one graph for my purposes. I can reload 14 days older or newer with the arrow buttons.
P.S. building website on server is MUCH easier than on Do-More...
-
How would one go about logging edge triggered boolean data to MySQL? It seems silly to log continuously while bit is ON or OFF. But triggering it with a delta contact would get me a timestamp at bit ON, and a timestamp at bit OFF. How could I digest this in MySQL? I would like to display it graphically, like trend view does. I've googled it but am coming up empty on how to deal with the ON/OFF transition. In my database I would need to log an OFF bit right before before the transition to ON, and log an ON bit right before it turns OFF. Am I looking at this wrong?
You could just do 2 INSERT queries with the inverse of the bit as the first insert.
DECLARE @MyBit bool,
SET @MyBit =
CASE(IF BitValue = 1
THEN 0
ELSE 1);
INSERT INTO [dbo].[YourTable] ([MyBitColumn], [MyDateColumn]) VALUES(@MyBit, DATEADD(SECOND, GETDATE(), -1);
INSERT INTO [dbo].[YourTable] ([MyBitColumn], [MyDateColumn]) VALUES(BitValue, GETDATE());
I've got it working really well. I have built a website (on local server) to pull up all the data, it's working well, both on phone and desktop. Drop down menus to pull up various graphs. Here's a screenshot. I found a javascript charting solution that works nice, scrolls/zooms/pans well with lots of data. I can easily load 14 days of 1 minute data onto the webpage without taking very long to load. That is enough data on one graph for my purposes. I can reload 14 days older or newer with the arrow buttons.
Nice!
P.S. building website on server is MUCH easier than on Do-More...
It is pretty amazing that a PLC can do it to begin with, and then to have so much flexibility is pretty cool!
-
It is pretty amazing that a PLC can do it to begin with, and then to have so much flexibility is pretty cool!
And pretty sure that server won't run 19 axes of closed loop motion or 34 concurrent Modbus/RTU comm sessions, all while slapping 1K of relays at 100Hz. ;)
-
You could just do 2 INSERT queries with the inverse of the bit as the first insert.
DECLARE @MyBit bool,
SET @MyBit =
CASE(IF BitValue = 1
THEN 0
ELSE 1);
INSERT INTO [dbo].[YourTable] ([MyBitColumn], [MyDateColumn]) VALUES(@MyBit, DATEADD(SECOND, GETDATE(), -1);
INSERT INTO [dbo].[YourTable] ([MyBitColumn], [MyDateColumn]) VALUES(BitValue, GETDATE());
I did just that this morning. Only I'm using the PLC's $UTC-1 for queuing purposes.
On that note, is there an easy way to build a queue? Say I have a few events that triggered simultaneously. I'm not smart enough to write my PHP to digest them all at once, so I send them separately. I'm finding my manual queuing (stages, etc) to be getting cumbersome. Is there a way I can dump my created strings into a queue, and then have my transmit program send them one at a time? Or do I just use copy to move my table around manually? I've searched the forums but haven't found any examples. I'm just starting to wrap my head around building an actual queue.
-
On that note, is there an easy way to build a queue?
We have had a table feature request in the database (with a Queue being an option) since version 1.0, but it just keeps getting pushed.
If it were me, I'd do a simple ring buffer, but many people brute force it with copies.
-
And what might a ring buffer be?
-
Okay, I understand the concept behind a ring buffer, have some string registers, 0-x "slots", and as they are (successfully) transmitted, it will clear them out. On the writing to the slots, how do I (cleanly) determine which slot to write into?
I like how the Do-More has many ways to accomplish things. However, the OCD in me struggles with accomplishing it "properly". I don't like kludging something together to later have to rework it to make room for something else. I'm learning as I go here, and am learning a lot.
-
Head and Tail index....write to Head, then increment...read from Tail, then increment. Empty when Head == Tail, full when Adjusted(Tail+1) == Head. I generally make the wrap happen at powers of two (mask off the wrapped value), but it is arbitrary.
-
Should be obvious...Head and Tail default to 0, and I will frequently zero out Head and Tail when empty. Flushing the Queue is just setting Head and Tail to zero.
-
Head and Tail index....write to Head, then increment...read from Tail, then increment. Empty when Head == Tail, full when Adjusted(Tail+1) == Head. I generally make the wrap happen at powers of two (mask off the wrapped value), but it is arbitrary.
Should be obvious...Head and Tail default to 0, and I will frequently zero out Head and Tail when empty. Flushing the Queue is just setting Head and Tail to zero.
Thank you, I got it thinkered out. I was overlooking the fact that the slots do not need to be emptied, per-say.
Do you mean full when Head+1 == Tail?
Can you explain the masking the wrap? Currently I have a $tTopOfScan run if HeadPointer==64, HeadPointer=0, and the same for TailPointer.
What is the point of zeroing out the pointers when empty?
-
Yeah sorry...when Head bumps Tail it's full.
Wrapping can be done by masking the upper bits...in your example 64 is 0x40, masked with (ANDed) 0x3F also yields 0. Can be done in the same math operation that you increment the index...Head=(Head+1)&0x3F.
No need to zero them. Just easier to read when debugging.
-
It is pretty amazing that a PLC can do it to begin with, and then to have so much flexibility is pretty cool!
And pretty sure that server won't run 19 axes of closed loop motion or 34 concurrent Modbus/RTU comm sessions, all while slapping 1K of relays at 100Hz. ;)
Exactly!
I have a PC on a project that can do that sort of thing, but just the license to allow me to turn on the software is more than an entire Do-more system.
-
Could you point me in the right direction regarding my head an tail pointers?
I have if Head > Tail, JMP to sending Stage.
How do I robustly preform the wrap around, when Head + 1 jumps from 127 to 0, how do I keep the sending stage active if needed? I have tried a few things like if Head = 0, SET a bit, and when Tail = 0, RST the bit, but nothing seems to be 100% fail proof for me. What direction do I need to be looking?
Is this why BobO suggested frequently zeroing out the head and the tail when empty?
-
One way is to have a MATH box with
Result:
V0
Equation:
IF(V0<127,V0+1,0)
BobO was talking about using a mask, which I find intriguing, but the above example is how I do it.
-
MATH TotalInQueue = IF(Head >= Tail, Head-Tail, Head+128-Tail)
If new record available
if TotalInQueue < 127
AddNewRecord(Head)
MATH Head = (Head+1)&0x7F
else
Queue full
If TotalInQueue > 0 then
RecordToProcess = Tail
Tail = (Tail+1)&0x7F
DoTheThing(RecordToProcess)
It might looks something like the attachments.
-
One way is to have a MATH box with
Result:
V0
Equation:
IF(V0<127,V0+1,0)
BobO was talking about using a mask, which I find intriguing, but the above example is how I do it.
Same/same. My way processes considerably faster...which means precisely nothing, because they are both plenty fast.
-
Very nice BobO!
I have a way of getting into those situations where that little bit of "Faster" can be very important :)
-
Very nice BobO!
I have a way of getting into those situations where that little bit of "Faster" can be very important :)
I realized I left out the queue full check...we'll leave that as an exercise for the student. ;)
The speed difference is not huge in practice, but everything in the masked version runs in the hardware accelerator. I think the IF() is software.
-
Thanks for the tips. Here's what I came up with, second MATH block handles full queue scenarios.