Problem with second execution in same connection

[alexgalstyan]

Hello dear chuckbell,

I think I have found new issues. I have not found about it something.

I am using ESP8266 with MySQL MariaDB (logged : Connected to server version 5.5.5-10.1.26-MariaDB-0+deb9u1) All SQL queries tried from direct terminal and worked properly

connection initialization sketch:

struct MySQLConCnf {
    String _sqlipaddr;
    String _sqlport; 
    String _sqlusr; 
    String _sqlpw; 
};
bool initDB(MySQL_Connection & _myconn, MySQLConCnf * _mysqlconconf){ //This work properly result with right configuration data is Serial is : Connected to server version 5.5.5-10.1.26-MariaDB-0+deb9u1
    if(_mysqlconconf == NULL || mysqlconconf->_sqlipaddr == "" ) //Checking for configurations
            return false;
    IPAddress server_addr;
    if(server_addr.fromString(_mysqlconconf->_sqlipaddr)){ //
        if(_myconn.connect(server_addr, _mysqlconconf->_sqlport.toInt(), (char *)_mysqlconconf->_sqlusr.c_str(), (char *)_mysqlconconf->_sqlpw.c_str())){
            return true;
        }
    }
    return false;
}

```First issue
sketch :
    WiFiClient localclient;       
    MySQL_Connection localconn(&localclient);
    MySQLConCnf mysqlconf;
    mysqlconf._sqlipaddr="x.x.x.x"; //you IP
    mysqlconf._sqlport="3306"; //or you port
    mysqlconf._sqlus="youruser";
    mysqlconf._sqlpw="yourpass";
    if(initDB(localconn, &mysqlconf){
        MySQL_Cursor * SQLcursor = new MySQL_Cursor(&localconn);
        String strquery = "";
        strquery = query1 ; //this SELECT  
        if(SQLcursor->execute((char *)strquery.c_str())){
            SQLcursor->get_columns();
            row_values *row = SQLcursor->get_next_row();
            while(row != NULL){
                uint8_t id = atol(row->values[0]);
                // Using id 
                row = SQLcursor->get_next_row();
            }
        }
        delete SQLcursor;
////"SELECT" sketch was executed well 
        strquery = query2; //this some delete, insert or update queries 
        SQLcursor = new MySQL_Cursor(&localconn);
        SQLcursor->execute((char *)strquery.c_str()); //this execution made good but returned "false"
        delete SQLcursor;

        SQLcursor = new MySQL_Cursor(&localconn);
        strquery = "";
        strquery = query3; //this some delete, insert or update queries 
        Serial.println("query : "+strquery);
        SQLcursor->execute((char *)strquery.c_str()); //this execution did not work and returned "false"
        Serial.println("is executed : "+String(bt));
        delete SQLcursor;
        localconn.close();
    }
It dose not work only using pointer type 

Second issue : when I tried query "SELECT" two times in one connection I have get an Error logged in serial : Bad mojo. EOF found reading column header.ERROR: You must read the columns first!

Can you help me with these issues ?

[ChuckBell]

I am sorry, but I cannot help you solve the errors. Here's why:

The connector was designed specifically for use with the Oracle MySQL database system. It is neither guaranteed nor supported for use with other variants. It may work, but please understand that I am limited in what support I can provide to any issues encountered with non Oracle MySQL variants.

Why? There are differences in how the server works with the client protocol. Oracle MySQL is the only one that will work with the connector.

Those said, I can only suggest some things that may (or may not) help.

1. The Bad MOJO error occurs when the protocol finds an unexpected packet. Which is not surprising here as I've said the connector does not work with non Oracle MySQL variants this being the most common error encountered. The follow on message suggests the server was attempting to send more packets or there are packets that have not been read.

2. Consider opening and closing the connection for each query you want to run. This may help with the protocol gaffes.

3. Switch to using an Oracle MySQL release. I would suggest using MySQL 5.7.21 (or latest of the 5.7 releases).

[alexgalstyan]

OK, I have found how to solve this issue (for this condition). In one declaration of :

  WiFiClient localclient;                 // Use this for WiFi instead of EthernetClient
  MySQL_Connection localconn(&localclient);

impossible use two selection query (query that need fetch); For it need declare second object but it not good solve. for multi table selections use UNION operator.

INSERT, UPDATE and DELETE operation is working not stable, but it possible to solve with stored procedures. cursors execution very stable for many time calls with one opened object of cursor.

good luck. Thanks a lot for this library

[dachshund-digital]

If someone wanted to expand support, how should they go about it? I would like to see support for MariaDB for example.

[dachshund-digital]

Oh, and just for reference, multiple queries randomly results in the bad mojo issue, but with MariaDB, I found that 1 select followed by 1 insert, is stable for a given connection, at least for the 100 odd tests I have done.

[ChuckBell]

I cannot reply to this request on this forum. Please PM me at d r c h a r l e s b e l l at g m a i l dot c o m.

On Tue, Jan 29, 2019 at 20:49 dachshund-digital notifications@github.com wrote:

Oh, and just for reference, multiple queries randomly results in the bad mojo issue, but with MariaDB, I found that 1 select followed by 1 insert, is stable for a given connection, at least for the 100 odd tests I have done.

— You are receiving this because you modified the open/close state. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/58#issuecomment-458779622, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4IbCCLjKyTi8459gxlVmqnzYYBk7ks5vIPojgaJpZM4VbTHn .