http POST fails via ESP32. Httpresponse -11 generated by HTTPClient.h

[Pcborges]

Hi, I have an ESP32 app that accesses a MySQL database via a php script.
I noticed that INSERT was returning error but the data was being recorded successfully.
I finally traced the error message to be generated by HTTPClient.h

The error, -11, generated by httpclient.h (https://github.com/espressif/arduino-es ... TPClient.h) reffers to timeout so I included http.setTimeout(10000); but it did nothing to solve the problem.

For some reason when it is an SQL INSERT it returns -11 but the INSERT executes successfully.

Arduino test code:

Code: Select all

#include <HTTPClient.h>
 
const char* ssid = "MySSID";
const char* password =  "MyPass";

char* txtSQL[]={"INSERT INTO activity (mac,jd,date,time,area,type,value) VALUES ('a9c4952de6b4',2458454,'2018-12-01','10:22','Area0002','h',130)",
                "INSERT INTO activity (mac,jd,date,time,area,type,value) VALUES ('a9c4952de6b4',2458454,'2018-12-01','10:22','Area0002','h',130)",
                "INSERT INTO activity (mac,jd,date,time,area,type,value) VALUES ('a9c4952de6b4',2458454,'2018-12-01','10:22','Area0002','h',130)",
                "INSERT INTO activity (mac,jd,date,time,area,type,value) VALUES ('a9c4952de6b4',2458454,'2018-12-01','10:22','Area0002','h',130)",
                "UPDATE activity set value=333 where value=130",
                "SELECT sum(value) from activity where mac='a9c4952de6b4'",
                "DELETE from activity WHERE value=333"};

String SQLKEY = "MyKey";
int sqlArray = 6;
int count=0;
 
void setup() {
 
  Serial.begin(115200);
  delay(4000);   //Delay needed before calling the WiFi.begin
 
  WiFi.begin(ssid, password); 
  Serial.print("Connecting to WiFi");
  while (WiFi.status() != WL_CONNECTED) { //Check for the connection
    delay(1000);
    Serial.print(".");
  }
  Serial.println("\nConnected to the WiFi network");
 
}
 
void loop() {
 if(WiFi.status()== WL_CONNECTED){   //Check WiFi connection status
 
   HTTPClient http;
   http.setTimeout(10000);   
   http.begin("http://www.MySite.com/ESP32web/hydroflux.php"); 
   http.addHeader("Content-Type", "application/x-www-form-urlencoded");
   
   String _post  = "query=";
          _post += txtSQL[count];
          _post += "&key=";
          _post += SQLKEY;
          
   int httpResponseCode = http.POST(_post);
   //Serial.print("Count= ");
   //Serial.println(count);
    
   if (count==sqlArray){
     count=0;
   } else { 
     count+=1;
   }
   
   if(httpResponseCode>0){
 
    String response = http.getString();                       //Get the response to the request
    Serial.println(_post);
    Serial.print("HttpResponse: ");  
    Serial.print(response);
    Serial.print(" * ");
    Serial.print("  httpResponseCode: ");
    Serial.println(httpResponseCode);   //Print return code

   }else{

    String response = http.getString();                       //Get the response to the request
    Serial.println(_post);
    Serial.print("HttpResponse: ");  
    Serial.print(response);
    Serial.print(" - ");
    Serial.print("  httpResponseCode: ");
    Serial.println(httpResponseCode);   //Print return code
   }
   http.end();  //Free resources
 }else{
    Serial.println("Error in WiFi connection");   
 }
  delay(2000);  //Send a request every 10 seconds 
}

I set a monitor to check what was being returned and got:

Code: Select all

Connecting to WiFi.
Connected to the WiFi network
query=INSERT INTO activity (mac,jd,date,time,area,type,value) VALUES ('a9c4952de6b4',2458454,'2018-12-01','10:22','Area0002','h',130)&key=19872007
HttpResponse:  -   httpResponseCode: -11
query=INSERT INTO activity (mac,jd,date,time,area,type,value) VALUES ('a9c4952de6b4',2458454,'2018-12-01','10:22','Area0002','h',130)&key=19872007
HttpResponse:  -   httpResponseCode: -11
query=INSERT INTO activity (mac,jd,date,time,area,type,value) VALUES ('a9c4952de6b4',2458454,'2018-12-01','10:22','Area0002','h',130)&key=19872007
HttpResponse:  -   httpResponseCode: -11
query=INSERT INTO activity (mac,jd,date,time,area,type,value) VALUES ('a9c4952de6b4',2458454,'2018-12-01','10:22','Area0002','h',130)&key=19872007
HttpResponse:  -   httpResponseCode: -11
query=UPDATE activity set value=333 where value=130&key=19872007
HttpResponse: 4 *   httpResponseCode: 201                ------------------------------------->PROOF INSERT worked
query=SELECT sum(value) from activity where mac='a9c4952de6b4'&key=19872007
HttpResponse: 1332  *   httpResponseCode: 200
query=DELETE from activity WHERE value=333&key=19872007
HttpResponse: 4 *   httpResponseCode: 201
query=INSERT INTO activity (mac,jd,date,time,area,type,value) VALUES ('a9c4952de6b4',2458454,'2018-12-01','10:22','Area0002','h',130)&key=19872007
HttpResponse:  -   httpResponseCode: -11
query=INSERT INTO activity (mac,jd,date,time,area,type,value) VALUES ('a9c4952de6b4',2458454,'2018-12-01','10:22','Area0002','h',130)&key=19872007
HttpResponse:  -   httpResponseCode: -11
.....

The PHP script do ing the job i s as be low :

Code: Select all

<?php
include('connection.php');

//these are just in case setting headers forcing it to always expire 
header('Cache-Control: no-cache, must-revalidate');

error_log(print_r($_POST,TRUE));

if( isset($_POST['query']) && isset($_POST['key']) ){                                   //checks if the tag post is there and if its been a proper form post
  header('Content-type: application/x-www-form-urlencoded');
  if($_POST['key']==$SQLKEY){                                                           //validates the SQL key
    $query=urldecode($_POST['query']);
    if(get_magic_quotes_gpc()){     //check if the worthless pile of crap magic quotes is enabled and if it is, strip the slashes from the query
      $query=stripslashes($query);
    }
    $conn = new mysqli($DB_ADDRESS,$DB_USER,$DB_PASS,$DB_NAME);    //connect

    if($conn->connect_error){                                                           //checks connection
      header("HTTP/1.0 400 Bad Request");
      echo "ERROR Database Connection Failed: " . $conn->connect_error, E_USER_ERROR;   //reports a DB connection failure
    } else {

      $result=$conn->query($query);                                                     //runs the posted query                        

      if($result === false){
        header("HTTP/1.0 400 Bad Request");                                             //sends back a bad request error
        echo "Wrong SQL: " . $query . " Error: " . $conn->error, E_USER_ERROR;    //errors if the query is bad and spits the error back to the client
      } else {
        if (strlen(stristr($query,"SELECT"))>0) {                                       //tests if it's a SELECT statement
          $csv = '';                                                                                // bug fix Undefined variable: csv
          while ($fieldinfo = $result->fetch_field()) {
            $csv .= $fieldinfo->name.",";
          }
          $csv = rtrim($csv, ",")."\n";
//********************************          echo $csv;                                                                    //prints header row
          $csv = '';

          $result->data_seek(0);
          while($row = $result->fetch_assoc()){
            foreach ($row as $key => $value) {
              $csv .= $value.",";
            }
            $csv = rtrim($csv, ",");                                                 //."\n";
          }
          echo $csv;                                                                    //prints all data rows
        } else {
          header("HTTP/1.0 201 Rows");
          echo $conn->affected_rows;       //if the query is anything but a SELECT, it will return the number of affected rows (INSERT IS  RETURNING -11)
        }
      }
      $conn->close();                                          //closes the DB
    }
  } else {
     header("HTTP/1.0 400 Bad Request");
     echo "-Bad Request";                                       //reports if the secret key was bad
  }
} else {
        header("HTTP/1.0 400 Bad Request");
        echo "*Bad Request";
}
?>

Any help will be much appreciated.
Thanks

Paulo Borges

[boarchuz]

Well what happens if you send the exact same request composed with your browser or Fiddler or similar? That's the first thing I would try, then you'll know if the issue is with your ESP32 code or the server.

[Pcborges]

Hi, thanks for stopping by.
Yes I did create a post.html sending the exact same sql statement and it works.

Code: [Select all] [Expand/Collapse]

1. <html>
2. <head>
3.     <title>Registration Form</title>
4.     <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
5. </head>
6. <body>
7.  
8.     <h2>Registration Form</h2>
9.  
10.     <form action="http://www.MySite.com.br/ESP32web/mysql.html" method="POST">
11. Query: <input type="text" name="query"> <br>
12. SqlKey: <input type="text" name="SQLKEY" value=73163>
13.  
14.         <input type="submit" value="Submit">
15.  
16.     </form>
17. </body>
18. </html>

GeSHi © Codebox Plus Extension