renecannao
commented
1 year ago Hi @jaimesicam .
Unfortunately the real issue is your sample application.
If has at least 4 issues , but only one is relevant for SQLException .
I might assume that the first 3 issues are due the fact that you wanted to simplify the test case, but issue 4 is the real problem.
SELECT *is an anti-pattern. As you said it yourself , one should "explicitly specify the columns you would like to SELECT from the table";- prepared statements are meant to separate SQL code from its data . You should never prepare this:
INSERT INTO sample_table(value) VALUES('1'),('2'),('3'); - prepared statements are meant to provide efficiency, compiling the SQL code only once and executing it multiple time. Your function
executeQuery()goes absolutely against this principle, recompiling the SQL code for each query. This is a terrible idea, and make prepared statement not just useless but also very inefficient. Your code will be more efficient if you run text queries instead of prepared statements. ProxySQL transparently fixes applications doing this, reducing by 66% the number of commands sent by bad application; - (the real issue) your sample application is running the
ALTER TABLEwhen there is no prepared statement prepared against the same time. This is partly the result of my previous point number 3 , but also due the fact that your sample application is the only application running queries on that table. If another connection runALTER TABLE, your sample code will fail too. In other words, if anALTER TABLErun (for example in another connection) in betweenCOM_PREPAREandCOM_EXECUTE, your sample code will fail also if connecting directly to MySQL server.
Here your test2.c slightly modified to trigger the same exception:
#include <stdlib.h>
#include <iostream>
#include "mysql_connection.h"
#include <cppconn/driver.h>
#include <cppconn/exception.h>
#include <cppconn/resultset.h>
#include <cppconn/prepared_statement.h>
#include <string>
using namespace std;
void executeQuery(sql::Connection* con, const std::string& query, bool alter=false)
{
cout << "Query: " << query << endl;
sql::PreparedStatement *pStmt;
pStmt = con->prepareStatement(query);
if (alter) // let's pretend that another connection is altering the table between our PREPARE and EXECUTE
executeQuery(con, "ALTER TABLE sample_table ADD COLUMN new_col INT NOT NULL");
pStmt->execute();
pStmt->close();
delete pStmt;
}
int main(void)
{
try {
sql::Driver *driver;
sql::Connection *con;
driver = get_driver_instance();
con = driver->connect("tcp://127.0.0.1:3306", "sbtest", "sbtest");
con->setSchema("sbtest");
executeQuery(con, "DROP TABLE IF EXISTS sample_table");
executeQuery(con, "CREATE TABLE sample_table(id INT NOT NULL auto_increment PRIMARY KEY, value VARCHAR(42) DEFAULT NULL)");
executeQuery(con, "INSERT INTO sample_table(value) VALUES('1'),('2'),('3')");
executeQuery(con, "SELECT * FROM sample_table");
executeQuery(con, "SELECT * FROM sample_table LIMIT 1");
//executeQuery(con, "ALTER TABLE sample_table ADD COLUMN new_col INT NOT NULL");
executeQuery(con, "SELECT * FROM sample_table",true);
executeQuery(con, "SELECT * FROM sample_table LIMIT 1");
delete con;
} catch (sql::SQLException &e) {
cout << "# ERR: SQLException in " << __FILE__;
cout << "# ERR: " << e.what();
cout << " (MySQL error code: " << e.getErrorCode();
cout << ", SQLState: " << e.getSQLState() << " )" << endl;
}
cout << endl;
return EXIT_SUCCESS;
}For reference, here the diff:
@@ -10,11 +10,13 @@
using namespace std;
-void executeQuery(sql::Connection* con, const std::string& query)
+void executeQuery(sql::Connection* con, const std::string& query, bool alter=false)
{
cout << "Query: " << query << endl;
sql::PreparedStatement *pStmt;
pStmt = con->prepareStatement(query);
+ if (alter) // let's pretend that another connection is altering the table between our PREPARE and EXECUTE
+ executeQuery(con, "ALTER TABLE sample_table ADD COLUMN new_col INT NOT NULL");
pStmt->execute();
pStmt->close();
delete pStmt;
@@ -34,8 +36,8 @@
executeQuery(con, "INSERT INTO sample_table(value) VALUES('1'),('2'),('3')");
executeQuery(con, "SELECT * FROM sample_table");
executeQuery(con, "SELECT * FROM sample_table LIMIT 1");
- executeQuery(con, "ALTER TABLE sample_table ADD COLUMN new_col INT NOT NULL");
- executeQuery(con, "SELECT * FROM sample_table");
+ //executeQuery(con, "ALTER TABLE sample_table ADD COLUMN new_col INT NOT NULL");
+ executeQuery(con, "SELECT * FROM sample_table",true);
executeQuery(con, "SELECT * FROM sample_table LIMIT 1");
delete con;
} catch (sql::SQLException &e) {
As per https://proxysql.com/documentation/prepared-statements, prepared statements are cached. This can become a problem if in between same SELECT * statements that the table structure becomes different:
Sample code:
If connecting to a PXC node directly, the commands execute just fine:
The general log on Node 3 is:
But if you point the host to ProxySQL, this will be the result:
Here's the log on node 1:
Here's the log on node 2:
Here's the log on node3:
The workaround so far is to explicitly specify the columns you would like to SELECT from the table but perhaps it would be more resilient to invalidate the prepared statement in the cache and reprepare it before it's executed on the server.