Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
1.0.1
-
None
Description
When enabling the `rewriteBatchedStatements` option in the C++ connector, the resulting queries are invalid, even for a trivial table.
Given a simple INSERT operation such as:
INSERT INTO tester (id, someval) VALUES (?, ?) |
The resulting batch operation will fail with an error such as this:
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INSERT INTO tester (id, someval) VALUES1, 'someval_1'),INSERT INTO tester (id...' at line 1
|
Query is: INSERT INTO tester (id, someval) VALUES (?, ?)
|
java thread: 139933589112640
|
At the very least, the statements are missing parentheses before the first value in the VALUES clause (should read `VALUES (1`, not `VALUES1`.)
Below is a minimal example to reproduce the error.
Sample app code
#include <iostream>
|
#include <string>
|
#include <mariadb/conncpp.hpp>
|
|
|
const int QUANTITY = 10; |
int main(int argc, char *argv[]) |
{
|
try { |
sql::Driver* driver = sql::mariadb::get_driver_instance();
|
sql::SQLString url("jdbc:mariadb://some-mariadb/somedb"); |
sql::SQLString timeoutLength = "60000"; |
sql::Properties props(
|
{
|
{"user", "example-user"}, |
{"password", "my_cool_secret"}, |
{"socketTimeout", timeoutLength}, |
{"rewriteBatchedStatements", "true"}, |
{"useBulkStmts", "true"}, |
{"useBatchMultiSend", "true"}, |
{"allowMultiQueries", "true"} |
}
|
);
|
std::unique_ptr<sql::Connection> conn(driver->connect(url, props));
|
std::unique_ptr<sql::Statement> s(conn->createStatement());
|
s->execute("DROP TABLE IF EXISTS tester"); |
s->execute("CREATE TABLE tester (id int not null, someval varchar(255) not null, primary key(id))"); |
|
|
std::unique_ptr<sql::PreparedStatement> stmnt(
|
conn->prepareStatement("INSERT INTO tester (id, someval) VALUES (?, ?)") |
);
|
|
|
for (int i = 1; i <= QUANTITY; ++i) |
{
|
std::string someval = "someval_" + std::to_string(i); |
stmnt->setInt(1, i);
|
stmnt->setString(2, someval);
|
stmnt->addBatch();
|
}
|
|
|
stmnt->executeBatch();
|
return 0; |
}
|
catch(sql::SQLException& e) { |
std::cerr << "Error Connecting to MariaDB Platform: " |
<< e.what() << std::endl;
|
// Exit (Failed) |
return 1; |
}
|
return 0; |
}
|
The app can be compiled the same way as the sample task app, e.g.,
g++ -o test main.cpp -std=c++11 -lmariadbcpp
|
App output
Error Connecting to MariaDB Platform: (conn=16) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INSERT INTO tester (id, someval) VALUES1, 'someval_1'),INSERT INTO tester (id...' at line 1
|
Query is: INSERT INTO tester (id, someval) VALUES (?, ?)
|
java thread: 139933589112640
|
Expected output
App should run and successfully insert 10 rows to the table without errors.
General query log
220426 9:40:19
|
16 Connect example-user@172.28.0.2 on somedb using TCP/IP
|
16 Query set autocommit=1, session_track_schema=1, session_track_system_variables='auto_increment_increment' , sql_mode = concat(@@sql_mode,',STRICT_TRANS_TABLES')
|
16 Query SELECT @@max_allowed_packet,@@system_time_zone,@@time_zone,@@auto_increment_increment
|
16 Query DROP TABLE IF EXISTS tester
|
16 Query CREATE TABLE tester (id int not null, someval varchar(255) not null, primary key(id))
|
16 Query (INSERT INTO tester (id, someval) VALUES1, 'someval_1'),INSERT INTO tester (id, someval) VALUES2, 'someval_2'),INSERT INTO tester (id, someval) VALUES3, 'someval_3'),INSERT INTO tester (id, someval) VALUES4, 'someval_4'),INSERT INTO tester (id, someval) VALUES5, 'someval_5'),INSERT INTO tester (id, someval) VALUES6, 'someval_6'),INSERT INTO tester (id, someval) VALUES7, 'someval_7'),INSERT INTO tester (id, someval) VALUES8, 'someval_8'),INSERT INTO tester (id, someval) VALUES9, 'someval_9'),INSERT INTO tester (id, someval) VALUES10, 'someval_10')
|
16 Quit
|
Workarounds tried
I tried commenting out/removing the rewriteBatchedStatements line and running it in 'normal' batched mode. In that mode, the queries will work, but they do not appear to actually be batched. The general query log in that case just shows regular queries being executed ten times over (notice the repeat 'Query' event in the general log below.)
220426 10:02:18 24 Connect example-user@172.28.0.2 on somedb using TCP/IP
|
24 Query set autocommit=1, session_track_schema=1, sql_mode = concat(@@sql_mode,',STRICT_TRANS_TABLES')
|
24 Query SELECT @@max_allowed_packet,@@system_time_zone,@@time_zone,@@auto_increment_increment
|
24 Query DROP TABLE IF EXISTS tester
|
24 Query CREATE TABLE tester (id int not null, someval varchar(255) not null, primary key(id))
|
24 Query INSERT INTO tester (id, someval) VALUES (1, 'someval_1')
|
24 Query INSERT INTO tester (id, someval) VALUES (2, 'someval_2')
|
24 Query INSERT INTO tester (id, someval) VALUES (3, 'someval_3')
|
24 Query INSERT INTO tester (id, someval) VALUES (4, 'someval_4')
|
24 Query INSERT INTO tester (id, someval) VALUES (5, 'someval_5')
|
24 Query INSERT INTO tester (id, someval) VALUES (6, 'someval_6')
|
24 Query INSERT INTO tester (id, someval) VALUES (7, 'someval_7')
|
24 Query INSERT INTO tester (id, someval) VALUES (8, 'someval_8')
|
24 Query INSERT INTO tester (id, someval) VALUES (9, 'someval_9')
|
24 Query INSERT INTO tester (id, someval) VALUES (10, 'someval_10')
|
24 Quit
|
We then tried enabling useServerPrepStmts (with rewriteBatchedStatements still false). This was to try to force the usage of server side prepared statements to see if this would remove the unnecessary round trips. The queries themselves will succeed in this mode as well, but once again, we see what appears to be continuous round-trips to the DBMS instead of a true batched operation:
34 Query DROP TABLE IF EXISTS tester
|
34 Query CREATE TABLE tester (id int not null, someval varchar(255) not null, primary key(id))
|
34 Prepare INSERT INTO tester (id, someval) VALUES (?, ?)
|
34 Execute INSERT INTO tester (id, someval) VALUES (1, 'someval_1')
|
34 Execute INSERT INTO tester (id, someval) VALUES (2, 'someval_2')
|
34 Execute INSERT INTO tester (id, someval) VALUES (3, 'someval_3')
|
34 Execute INSERT INTO tester (id, someval) VALUES (4, 'someval_4')
|
34 Execute INSERT INTO tester (id, someval) VALUES (5, 'someval_5')
|
34 Execute INSERT INTO tester (id, someval) VALUES (6, 'someval_6')
|
34 Execute INSERT INTO tester (id, someval) VALUES (7, 'someval_7')
|
34 Execute INSERT INTO tester (id, someval) VALUES (8, 'someval_8')
|
34 Execute INSERT INTO tester (id, someval) VALUES (9, 'someval_9')
|
34 Execute INSERT INTO tester (id, someval) VALUES (10, 'someval_10')
|
34 Close stmt
|
34 Quit
|
Essentially, with useServerPrepStmts mode the repetitive Query log entries are just replaced with repetitive Execute calls on the statement handler. At the end of the day it still appears to be ten separate round trips to the DBMS to perform 10 trivial row inserts.
Expected server side log pattern
Here is what the comparable event looks like when run from the Node.JS connector.
220426 10:41:02 36 Connect example-user@172.28.0.2 on somedb using TCP/IP
|
36 Prepare DROP TABLE IF EXISTS tester
|
36 Execute DROP TABLE IF EXISTS tester
|
36 Prepare CREATE TABLE tester (id int not null, someval varchar(255) not null, primary key(id))
|
36 Execute CREATE TABLE tester (id int not null, someval varchar(255) not null, primary key(id))
|
36 Prepare INSERT INTO tester (id, someval) VALUES (?, ?)
|
36 Execute INSERT INTO tester (id, someval) VALUES (?, ?)
|
Notice in the NodeJS version that only one Execute operation is done on the INSERT statement. This demonstrates a benefit of batching, e.g., fewer round trips to the server, since the ten trivial inserts are all done in one call.
I am including the sample code used to run the Node.JS version as an attachment, since NodeJS code is not directly germane to this bug report about the C++ connector; but running such code may still be helpful in comparing the General Log output of the two implementations.
Server info
Our DB is running mariadb:10.7.3.
Attachments
Issue Links
- relates to
-
-
- Closed
-