MySQL Bugs: #69718: INSERT ON DUPLICATE KEY UPDATE does regularly report "-1"

Bug #69718	INSERT ON DUPLICATE KEY UPDATE does regularly report "-1"
Submitted:	10 Jul 2013 18:36	Modified:	27 Aug 2013 8:14
Reporter:	Louis at oracle	Email Updates:
Status:	Patch pending	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.2.5	OS:	Windows (window7 64bit)
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any
Tags:	affected, ON DUPLICATE KEY

Description:
Hello,

I am improving the performance of my programm by using the "INSERT ON DUPLICATE KEY UPDATE syntax, which allow me to trow a record towards the DB without having to worry if it is an insert or an update 

This sytax is supposed to return the affected number of rows as follows (assumuing a singel row insert):
1 = 1 row inserted
2 = 1 row updated
and perhaps not sure
-1 = error / nothing happened

Other values are not vallid assuming IMHO.

But on a very regular base the code is returning an "-1" where a normal insert took place.  

How to repeat:
- using visual studio with VB.NET
- ODBC 5.2.5
- MySQL 5.7.1m11
- INNODB 64 bit

'using ODBC
cn_Dest.Execute(sqlInsertOrUpdate, NoLinesAffected)

Select case  NoLinesAffected

case 1   'insert

case 2    'update

case else 'error
> I notice that every xx.xxx records -1 is returned 
> and the record is infact inserted in the DB

end select

** SQL DOTNET adapter ** 
replacing the ODBC cn.execute command with

sqldotnet adapter code
DBcmd.CommandText = sqlInsertOrUpdate
NoLinesAffected = DBcmd.ExecuteNonQuery() 

^fixes^ the problem

So almost sure it is the ODBC adapter

Sincerely

Louis

Hi Louis,

Thank you for reporting a problem in MySQL Connector/ODBC and for your interest in MySQL software.

Unfortunately, it is not clear from your explanation which class exactly you used for connecting and executing queries. Was that System.Data.Odbc.OdbcConnection or ADODB.Connection()? It would be the best if you could come up with a small test VB.NET project, so we could see all variable declarations and functions usage.

Thanks.

Hello,

On request some extra info.

- Dim cn_dest As New ADODB.Connection 
cn_dest.Open(connectionstring)

- An example of the insert sql is like this
INSERT INTO DB_Vast( `primkeypart1`, `primkeypart2`, `somefield1`, `somefield2`, `collisioncntr`, `ErrCode`, `LastDate`) VALUES ('123456' , 'abc' , 'xyz' , 'x' ,  y ,  Null ,  '2013-07-12 01:50:01') ON DUPLICATE KEY UPDATE somefield1 = if(IsNull(somefield1) ,'xyz',null), ErrCode = if(somefield2 = 'X','1','2'), collisioncntr = collisioncntr +1;

- code looks like
>> DOTNET (OK)
DBcmd.CommandText = sqlInsertOrUpdate
NoLinesAffected = DBcmd.ExecuteNonQuery()
>> ODBC (NOT OK)
'cn_Dest.Execute(sqlInsertOrUpdate, NoLinesAffected)

If NoLinesAffected = 1 Then
'data was inserted
Else
If NoLinesAffected = 2 Then
    'data was updated
Else
    'error orccured
End If
End If

Sincerely,

Louis

Louis,

Thank you for your feedback.
Unfortunately, I still cannot repeat the problem you described.
I am attaching a VB.NET test project (for Visual Studio 2008 or newer).
Can you please make the changes that would help me to get it right?

Right now the test case creates a simple table with just one primary key and one varchar(32) column. The first insert status is 1 (as expected). The 2nd insert has the key value duplication and the status is 2.

The text field in the dialog has the connection string (DSN=text). You can modify the DSN name accordingly to your environment.

Thanks.

VB.NET test case

Attachment: bug69718_VB.zip (application/zip, text), 11.86 KiB.

Extra info related to the used ODBC-connection

- the server and client are running on the same 64-bit windows7 server
- ODBC connections can be made tcp-ip and shared memory based
- I first noticed the problem when using an shared memory based connection
- I did a small test which shows that both the shared memory and the tcp-ip version have the problem
- but I also noticed that the problem occurs a lot more when using the shared memory version
- in my case the error happens every "10.000" inserts when using the memory based connection to give an impression) 

Louis

Louis,

How exactly do you establish the ODBC connection through the shared memory?
Formally MySQL Connector/ODBC does not support the shared memory connections.

I am asking because in the current version of Connector/ODBC you cannot specify the memory base name in the ODBC connection string.

Hello,

The ODBC-connection (in case of memory connection) is opened as follows

connINDB_ODBC_MEM = "DRIVER={" + "MySQL ODBC 5.2w Driver" + "};SERVER=localhost;Port=3307;DATABASE=INDB;UID=<mydb>;PASSWORD=<secret>;Protocol=memory;Shared Memory Name=FastConn57;"

Dim cn_DestADODB As New ADODB.Connection

cn_DestADODB.Open(connINDB_ODBC_MEM)

I did a lot of changes in the past few days, but I could still repeat the problem with both normal and memory connections

The problem does occur on what seems to be random places, however also very soon after the start of the insert process.

Sincerely,

Louis

Note that:

Looking again at the ODBC connection string it is a bit strange that I specify port number as well

The ODBC-options I used for the normal connection is just "2" 
Public Const cStrODBC5_Options As String = "2" 
Note that the related comment for "2" is 'Return Matching Rows The client can't handle that MySQL returns the true value of affected rows.

???? that is already verdict ???

Louis

Louis,

Please note that some options you used ("Protocol=memory;Shared Memory Name=FastConn57;") are not supported by Connector/ODBC and they have no effect on anything. Specifying "Protocol=Memory" in the connection string does not really enable the memory protocol for the ODBC connection. Same is for "Shared Memory Name" option.
You can put anything in these options and the connection will still work as before because it is done through the TCP protocol.

What is really important is using OPTION=2 for matched rows. With this option I was able to get the -1 status once per 200K updates.

So, the bug status is Verified. I need more time to find out what caused the error -1 status and why.

Here is what I see in the ODBC trace (SQLRowCount() returned -1):

bug69718_VB.vsh 36ac-42a8       ENTER SQLRowCount
                HSTMT               0x04D59398
                SQLLEN *            0x06F8DF14

bug69718_VB.vsh 36ac-2388       ENTER SQLFreeStmt
                HSTMT               0x04D5A360
                UWORD                        1 <SQL_DROP>

bug69718_VB.vsh 36ac-42a8       EXIT  SQLRowCount  with return code 0 (SQL_SUCCESS)
                HSTMT               0x04D59398
                SQLLEN *            0x06F8DF14 (-1)

Hum,

Note that I even entered the following IMHO valid entry’s in the my.ini file

shared-memory
shared-memory-base-name=FastConn57

By the way the "insert on duplicate key update" is a fantastic option, it did allow me to clean up a lot of code.
I had expected that the option would boost performance as well since you save on inter process communication and DB internally you save the select before the insert. Did not notice that yet. But as said very worth full sql extension.

Louis

This looks like a bug in libmysql rather than in the ODBC driver.
The reliable test case is hard to come up with because ADODB does horrible things to STMT handlers (it allocates a pool of STMTs) then use a pack of them and then disposes randomly.

Patch and test case

Attachment: patch69718.diff (application/octet-stream, text), 4.22 KiB.

Hello,

Thanx you found and repaired the problem. However sorry, I can not test it now because I am not there next week and apart from that, I do not compile the source myself. 

But it you have / mail me an 64 bit executable, I will test it for withing two weeks.

Sincerely,

Louis