getting packet out of of order error when trying to insert to mysql database .

Projects Programming
1

Hi

I recently raspberry pi on new SD card and installed mysql and commented bind address in /etc/mysql/my.cnf

so remote arduino can connect to it, this part work, but now I am getting error

Inserting : INSERT INTO arduinoSensorData.sensorLog (out_temperature, out_humidity,  drwngRoom_temperature, drwngRoom_humidity, pot1_soilMoisture, pot1_avg_SoilMoisture, millieVolt, uv_index, wateringPot1) VALUES ('24.60', '27.40', '29.80', '22.20', '64', '64', '0.00', '0', 'N/A');
Error: 155 = You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1.
Connection Successfull,inserting to database.
Error: 33 = Got packets out of order.

and code used to insert mysql query you see above in first line is:

String getSqlInsertString()
{
  // inserting to sql database on mysql server
  insertSql = "";
  insertSql.concat("INSERT INTO arduinoSensorData.sensorLog (out_temperature, out_humidity, ");
  insertSql.concat(" drwngRoom_temperature, drwngRoom_humidity, pot1_soilMoisture, pot1_avg_SoilMoisture, ");
  insertSql.concat("millieVolt, uv_index, wateringPot1) VALUES ('");
  insertSql.concat(outdoorTempInC);
  insertSql.concat("', '");
  insertSql.concat(outoorHumidity);
  insertSql.concat("', '");
  insertSql.concat(indorTempinC);
  insertSql.concat("', '");
  insertSql.concat(indoorHumidity);
  insertSql.concat("', '");
  insertSql.concat(val);
  insertSql.concat("', '");
  insertSql.concat(avgVal);
  insertSql.concat("', '");
  insertSql.concat(Vsig);
  insertSql.concat("', '");
  insertSql.concat(uvIndex);
  insertSql.concat("', 'disbaled");
  insertSql.concat("');");

if (sqlEnDis == 1) {
        if (!connected) {
          Serial.println("Establishing Connection");
          my_conn.mysql_connect(server_addr, 3306, user, password);
          connected = true;
        }
        else if (connected == true)
        {
//          my_conn.cmd_query("use arduinoSensorData;");
//          Serial.print("total Rows: ");
//          Serial.println(my_conn.cmd_query("select count(*) from sensorLog;"));

          insertSql = getSqlInsertString();
          const char *mycharp = insertSql.c_str();
                my_conn.cmd_query("use arduinoSensorData;");
                Serial.print("Inserting : ");
                Serial.println(insertSql);
          my_conn.cmd_query(mycharp);
          Serial.println("Connection Successfull,inserting to database.");
          sqlInsertInterval = 10000; // set the repeat interval to a  minute after first insertion.
        } else
        {
          // TODO : log to sd card when server is not reachable.
          Serial.println("Connection failed.");
        }

on earlier set up of mysql on previous raspberry pi it was working. but now it doesnt. gets the error and mega get stuck

2

That trailing semicolon is not part of SQL syntax - it's used sometimes to separate sql statements in a stream or file. It depends on how my_conn.cmd_query() works.

Also - why are you inserting numeric values as text?

3

PaulMurrayCbr:
That trailing semicolon is not part of SQL syntax - it's used sometimes to separate sql statements in a stream or file. It depends on how my_conn.cmd_query() works.

Also - why are you inserting numeric values as text?

I tried your suggestion but I am still getting this error (serial monitor output below):

INSERT INTO arduinoSensorData.sensorLog (out_temperature, out_humidity,  drwngRoom_temperature, drwngRoom_humidity, pot1_soilMoisture, pot1_avg_SoilMoisture, millieVolt, uv_index, wateringPot1) VALUES (41.10, 5.00, 28.90, 26.40, 76, 76, 778.89, 0, 'disbaled')
Error: 158 = You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INS' at line 1.
Connection Successfull,inserting to database.
Error: 33 = Got packets out of order.
INSERT INTO arduinoSensorData.sensorLog (out_temperature, out_humidity,  drwngRoom_temperature, drwngRoom_humidity, pot1_soilMoisture, pot1_avg_SoilMoisture, millieVolt, uv_index, wateringPot1) VALUES (41.40, 5.00, 28.90, 26.40, 113, 94, 783.75, 0, 'disbaled')
4

using

          my_conn.cmd_query("use arduinoSensorData;");
          Serial.print("total Rows: ");
          Serial.println(my_conn.cmd_query("select count(*) from sensorLog;"));

returns

total Rows: 1

so I tested again with different SQL query , I realize its returned value of 1 if its success full .

but int the sensorLog table I have 3 entries I made manually !

5

I got it working by first selecting the database ,

use arduinoSensorData;

and then doing the insert

INSERT INTO sensorLog (out_temperature, out_humidity,  drwngRoom_temperature, drwngRoom_humidity, pot1_soilMoisture, pot1_avg_SoilMoisture, millieVolt, uv_index, wateringPot1) VALUES (31.70, 5.00, 29.20, 16.80, 114, 117, 262.87, 0, 'disbaled')

however i want to know how can i read total number of rows from the table ?

6

To get the number of rows in an SQL table, try
select count (*) from sensorLog

7

6v6gt:
To get the number of rows in an SQL table, try
select count (*) from sensorLog

i have tried that already it always returns 1 not the number of rows, perhaps

my_conn.cmd_query("select count(*) from sensorLog;")

returns success for 1 and 0 for failure not the actual data we expect

8

Ok. I now see you got that far already.
In principle, if you issue an SQL command which returns values like from a select statement , you get a structured result set back which you have navigate through and parse as required.
Here is an example: