Description:
Using mysql_real_connect() and CLIENT_MULTI_STATEMENTS with transactions has an interesting/odd behavior when one of the operations fail.
Suppose we execute mysql_query() with a set of 10 inserts to database and one of the inserts fail for any reason. It is supposed to rollback the whole transaction.
If we try to send COMMIT or ROLLBACK immediately after the mysql_query() it will return the error Commands out of sync.
If we do other operation like loop the result using mysql_next_result() to check the whether there is error or not and after that send COMMIT or ROLLBACK it will execute without error.
The most odd/weird here is that if we execute ROLLBACK it will execute as expected and will rolled back the whole transaction but if we send COMMIT it will commit all the transactions until the first error. For the example, if the 4th operation fails the DB will commit the first 3 operations and rollback the other 7!! It completely break the concept of atomicity for transactions where or ALL of them COMMIT or ALL of them ROLLBACK.
How to repeat:
-- SQL to create the table
Create Table: CREATE TABLE `test_table` (
`id` int(11) NOT NULL,
`name` varchar(50) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB;
#include <mysql.h>
#include <stdio.h>
void main() {
MYSQL *conn;
MYSQL_RES *result;
MYSQL_ROW row;
char *server = "localhost";
char *user = "usr_test";
char *password = "XXX";
char *database = "test";
conn = mysql_init(NULL);
int status = 0;
/* Connect to database */
if (!mysql_real_connect(conn, server, user, password, database, 0, NULL, CLIENT_MULTI_STATEMENTS)) {
fprintf(stderr, "[%d]%s\n", __LINE__, mysql_error(conn));
exit(1);
}
/* execute multiple statements */
status = mysql_query(conn, "BEGIN;");
if (status) {
fprintf(stderr, "[%d]%s\n", __LINE__, mysql_error(conn));
goto clean;
}
status = mysql_query(conn,
"INSERT INTO test_table VALUES(10, 'ABCD1');\
INSERT INTO test_table VALUES(11, 'ABCD2');\
INSERT INTO test_table VALUES(12, 'ABCD3');\
INSERT INTO test_table VALUES(13, 'ABCD4');\
INSERT INTO test_table VALUES(10, 'ABCD5');\
INSERT INTO test_table VALUES(16, 'ABCD6');\
INSERT INTO test_table VALUES(17, 'ABCD7');\
INSERT INTO test_table VALUES(18, 'ABCD8');\
INSERT INTO test_table VALUES(19, 'ABCD9');\
INSERT INTO test_table VALUES(20, 'ABCD10');\
");
if (status) {
fprintf(stderr, "[%d]%s\n", __LINE__, mysql_error(conn));
goto clean;
}
/* process each statement result */
int ret = 0;
do {
if (mysql_field_count(conn) != 0) { /* some error occurred */
printf("[%d]Could not retrieve result set\n", __LINE__);
break;
}
/* more results? -1 = no, >0 = error, 0 = yes (keep looping) */
ret += (int)mysql_affected_rows(conn);
printf("[%d]ret: %d\n", __LINE__, ret);
if ((status = mysql_next_result(conn)) > 0)
printf("[%d]Could not execute statement if it works \n", __LINE__);
} while (status == 0);
printf("[%d]Total Ret: %d\n", __LINE__, ret);
status = mysql_query(conn, "COMMIT;");
if (status)
fprintf(stderr, "[%d]%s\n", __LINE__, mysql_error(conn));
clean:
mysql_close(conn);
}
For this example the DB will commit the first 3 operations and rollback the other 7. It was tested with MySQL 5.5 and 5.6. Using either the default configuration and changing the transaction_isolation to READ-COMMITTED and REPEATABLE-READ.
Description: Using mysql_real_connect() and CLIENT_MULTI_STATEMENTS with transactions has an interesting/odd behavior when one of the operations fail. Suppose we execute mysql_query() with a set of 10 inserts to database and one of the inserts fail for any reason. It is supposed to rollback the whole transaction. If we try to send COMMIT or ROLLBACK immediately after the mysql_query() it will return the error Commands out of sync. If we do other operation like loop the result using mysql_next_result() to check the whether there is error or not and after that send COMMIT or ROLLBACK it will execute without error. The most odd/weird here is that if we execute ROLLBACK it will execute as expected and will rolled back the whole transaction but if we send COMMIT it will commit all the transactions until the first error. For the example, if the 4th operation fails the DB will commit the first 3 operations and rollback the other 7!! It completely break the concept of atomicity for transactions where or ALL of them COMMIT or ALL of them ROLLBACK. How to repeat: -- SQL to create the table Create Table: CREATE TABLE `test_table` ( `id` int(11) NOT NULL, `name` varchar(50) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ) ENGINE=InnoDB; #include <mysql.h> #include <stdio.h> void main() { MYSQL *conn; MYSQL_RES *result; MYSQL_ROW row; char *server = "localhost"; char *user = "usr_test"; char *password = "XXX"; char *database = "test"; conn = mysql_init(NULL); int status = 0; /* Connect to database */ if (!mysql_real_connect(conn, server, user, password, database, 0, NULL, CLIENT_MULTI_STATEMENTS)) { fprintf(stderr, "[%d]%s\n", __LINE__, mysql_error(conn)); exit(1); } /* execute multiple statements */ status = mysql_query(conn, "BEGIN;"); if (status) { fprintf(stderr, "[%d]%s\n", __LINE__, mysql_error(conn)); goto clean; } status = mysql_query(conn, "INSERT INTO test_table VALUES(10, 'ABCD1');\ INSERT INTO test_table VALUES(11, 'ABCD2');\ INSERT INTO test_table VALUES(12, 'ABCD3');\ INSERT INTO test_table VALUES(13, 'ABCD4');\ INSERT INTO test_table VALUES(10, 'ABCD5');\ INSERT INTO test_table VALUES(16, 'ABCD6');\ INSERT INTO test_table VALUES(17, 'ABCD7');\ INSERT INTO test_table VALUES(18, 'ABCD8');\ INSERT INTO test_table VALUES(19, 'ABCD9');\ INSERT INTO test_table VALUES(20, 'ABCD10');\ "); if (status) { fprintf(stderr, "[%d]%s\n", __LINE__, mysql_error(conn)); goto clean; } /* process each statement result */ int ret = 0; do { if (mysql_field_count(conn) != 0) { /* some error occurred */ printf("[%d]Could not retrieve result set\n", __LINE__); break; } /* more results? -1 = no, >0 = error, 0 = yes (keep looping) */ ret += (int)mysql_affected_rows(conn); printf("[%d]ret: %d\n", __LINE__, ret); if ((status = mysql_next_result(conn)) > 0) printf("[%d]Could not execute statement if it works \n", __LINE__); } while (status == 0); printf("[%d]Total Ret: %d\n", __LINE__, ret); status = mysql_query(conn, "COMMIT;"); if (status) fprintf(stderr, "[%d]%s\n", __LINE__, mysql_error(conn)); clean: mysql_close(conn); } For this example the DB will commit the first 3 operations and rollback the other 7. It was tested with MySQL 5.5 and 5.6. Using either the default configuration and changing the transaction_isolation to READ-COMMITTED and REPEATABLE-READ.