Do-More to MySQL Server

[Bolt]

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.

[jcottrill]

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.

[plcnut]

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.

[Bolt]

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, 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.

[jcottrill]

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.

[plcnut]

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

Code: [Select]

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.

[plcnut]

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).

[plcnut]

There are some examples for GET requests in this thread: http://forum.hosteng.com/index.php/topic,993.60.html

[jcottrill]

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.

Code: [Select]

// 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);



[Bolt]

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

Code: [Select]

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.

Code: [Select]

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...

[plcnut]

Is your page expecting a POST pr a GET?
You also need to remove the last forward slash between php and the question mark.

[Bolt]

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

Code: [Select]

<?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:

Code: [Select]

Connected successfully
New record created succcessfully

I can clean these echo statements up later to let the PLC decipher them easier.

[plcnut]

Try leaving out the HTTP/1.0 and just do a single $0D$0A immediately after the query string.

[jcottrill]

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

Code: [Select]

$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();


[Bolt]

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?