Bug #53684 "MyODBC batch insert" does not report correctly the "inserts" that failed
Submitted: 16 May 2010 19:17 Modified: 8 Nov 2011 4:27
Reporter: frank vanden berghen Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / ODBC Severity:S1 (Critical)
Version:5.1 OS:Windows
Assigned to: Bogdan Degtyariov CPU Architecture:Any
Tags: batch inserts

[16 May 2010 19:17] frank vanden berghen
Description:
I have to synchronize 2 tables into 2 different database.
One of the 2 databases is a mysql database.
Basically, I need to "insert" some rows into the mysql database and if the insert fails (because I try to insert a row with a primary key already in use), i know that I must do an "update" instead.

So, I have a batch of "insert" that I am sending via MyODBC to MySQL: I prepare and execute the batch of "insert" and thereafter I call the ODBC functions "SQLMoreResults" and "SQLRowCount" to know if a specific insert inside the batch succeeded or not. This does not work. As soon, as one insert fails, the whole batch is lost.

The funny thing is that if I am doing a batch of "update" or "delete" (no "insert's") and thereafter I call the ODBC functions "SQLMoreResults" and "SQLRowCount", then the MyODBC driver is functioning properly and I can see exactly what "update" or "delete" did trigger an error. So the error inside the MyODBC driver is really specific to "insert's".

Frank

How to repeat:
Create a table "test" like this:
ID NAME
1  Frank
2  Sabrina
3  David
"ID" is the primary key and has thus a "unique constraint".

Inside your C++ application, run the following SQL batch of statement through myOdbc:
insert into test (id,name) values (1,'frank');
insert into test (id,name) values (4,'dominique');

You should receive an error that says that the first "insert" failed.

Run the ODBC function "SQLMoreResults".
You should receive a code that says that the second "insert" succeeded.

Here is the error in MyODBC: the second "insert" never succeed.
furthermore, the output code of "SQLMoreResults" is always wrong.

By the way, when using MS-SQL-server, everything runs fine.
By the way, when batch of "update" or "delete", everything runs fine.
[11 Oct 2010 9:26] Lawrenty Novitsky
Frank, thank for the report, but could you please clarify how do run your "batch". It's not quite clear from the report and there are couple of ways of doing that.
[27 Sep 2011 5:59] Bogdan Degtyariov
Test case

Attachment: bug53684.c (text/plain), 2.52 KiB.

[27 Sep 2011 6:04] Bogdan Degtyariov
Frank,

Can you please take a look inside the test case above (bug53684.c) and make the changes relevant to your problem?

So far we were not able to repeat it in any way.
SQLMoreResults() returns 100 (SQL_NO_DATA), which is absolutely correct.
There is no second resultset because the entire query failed and the server did not even try to run INSERT number two.
[27 Sep 2011 6:07] Bogdan Degtyariov
Checked Connector/ODBC 5.1.6/5.1.7/5.1.8 and they all returned the correct result 100 (SQL_NO_DATA).
[28 Sep 2011 9:55] frank vanden berghen
a second, more precise code to track the bug

Attachment: mysqlTest_v2.cpp (text/plain), 3.67 KiB.

[28 Sep 2011 10:04] frank vanden berghen
>There is no second resultset because the entire query failed and the
>server did not even try to run INSERT number two.

If you look at the second example code (that i just posted here above), the whole query did not failed: to be more precise, the first insert (in the new code) succeeded. This success is logical since I didn't ask for any transaction. This means that this batch of insert runs each insert separately (no transaction). This also means that each error should be reported separately (which is not the case today).

Thanks for your help!
Frank
[12 Oct 2011 8:52] Bogdan Degtyariov
Frank,

Thank you very much for editing the code.
From my point of view the test case displays expected results.

Let me explain what actually happened:

your batch of commands is not running as a single transaction meaning that successful insert #1 is not rolled back after insert #2 failed.

However, the batch cannot continue when one of batched queries returns the error. The server did not even try to run insert #3.

Briefly it can be presented as a sequence of steps:

 1. Insert #1 is executed [result: SUCCESS]
 2. Result set #1 is created using the result from step 1
 3. Insert #2 is executed [result: ERROR]
 4. Result set #2 is created using the result from step 3
 5. Batch is terminated

The driver gets only two result sets and the attempt to get the third result set ends with SQL_NO_DATA.

To make sure each statement returns the operation result you have to execute each insert command separately.
[12 Oct 2011 10:09] frank vanden berghen
I perfectly understand that you don't want to fix this issue because it most certainly involves some work inside some part of the MySQL code that is not easy to understand and modify.

...but, if you ask any random programmer about this bug, it will give the same answer as me: this is a bug and NOT a mis-interpretation of the documents on my side.

If you don't want to fix it, say it clearly, and don't bother trying to explain me stupid things.

Frank
[8 Nov 2011 4:27] Bogdan Degtyariov
Frank,

We would admit the bug disregard how easy or hard it is to fix.
Perhaps our documentation is not clear enough about processing batched queries, but THIS IS THE WAY HOW THE SERVER WORKS. It will not change because this behavior is expected by many applications, so many programmers would not want to do it your way.

Instead of just skipping the failing statement MySQL provides the means to avoid errors using special INSERT syntax extensions that handle duplicate keys:

 - if you want to proceed with inserting data and change the key value use
   "ON DUPLICATE KEY UPDATE "

 - otherwise consider "INSERT IGNORE". If you use the IGNORE keyword, 
   errors that occur while executing the INSERT statement are 
   treated as warnings instead.

http://dev.mysql.com/doc/refman/5.5/en/insert.html

This gives much more flexibility and data integrity than your solution.
Please check the online manual page I gave you and modify the code accordingly.

----------------------------------
Marking the report as "Not a bug".