Bug #49733 using backslash in NO_BACKSLASH_ESCAPES mode can cause corruption
Submitted: 16 Dec 2009 11:05 Modified: 17 Dec 2009 9:50
Reporter: Lothar Bongartz Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.0, mysqld-5.1.41-community, 5.1, next-mr bzr OS:Any (MS Windows, Linux)
Assigned to: CPU Architecture:Any
Tags: NO_BACKSLASH_ESCAPES, SQLBindParameter

[16 Dec 2009 11:05] Lothar Bongartz
Description:
I have detected a bug:

While migrating from MSSQL to MySQL I get the following error:
Ret=SQL_ERROR, State=07001, Err=506, Msg=[MySQL][ODBC 5.1 Driver][mysqld-5.1.41-community]SQLBindParameter not used for all parameters

after extended checking I found out, that the reason for it is a Bug in MySQL:
I have used NO_BACKSLASH_ESCAPES mode.
This works OK so far. But if I do a multiple INSERT INTO and one of the parameters ends with a backslash, I get the error mentioned above.

The error vanishes, if the backslash is not at the end of the column value and it vanishes, if there are less rows to insert at a time.

Failing example:
"INSERT INTO searchs(memb_id,log,date_entered) VALUES(904792,'ok\\','2008-11-15 04:45:13.023'),(659287,'who u talkin too?','2008-11-15 06:34:40.770'),(613855,'boobs','2008-12-18 06:25:52.213'),(862886,'wb','2008-12-11 03:16:51.200'),(702067,'christmas decorations','2008-11-15 06:53:50.700')"

My workaround: if a value ends with backslash, I add a blank.

How to repeat:
CREATE TABLE searchs(
  memb_id int NOT NULL,
  log varchar(64) NOT NULL,
  date_entered datetime NOT NULL
)

INSERT INTO searchs(memb_id,log,date_entered) VALUES(904792,'ok\','2008-11-15 04:45:13.023'),(659287,'who u talkin too?','2008-11-15 06:34:40.770'),(613855,'boobs','2008-12-18 06:25:52.213'),(862886,'wb','2008-12-11 03:16:51.200'),(702067,'christmas decorations','2008-11-15 06:53:50.700')

Suggested fix:
Fix parser for NO_BACKSLASH_ESCAPES mode
[16 Dec 2009 14:49] Sveta Smirnova
Thank you for the report.

Verified as described.

Most likely library bug like bug #38731
[16 Dec 2009 15:04] Sveta Smirnova
To repeat: run following test using MTR.

set sql_mode='NO_BACKSLASH_ESCAPES';

drop table if exists searchs;
CREATE TABLE searchs(
  memb_id int NOT NULL,
  log varchar(64) NOT NULL,
  date_entered datetime NOT NULL
);

INSERT INTO searchs(memb_id,log,date_entered) VALUES(904792,'ok\','2008-11-15 04:45:13.023');

Problem is not repeatable in command line client.
[16 Dec 2009 16:20] Lothar Bongartz
Since the database I try to migrate has more than 4GB, I needed some time to detect, that the workaround does not fix all cases. Even removing all ending backslashes doesn't cure the problem.
Using NO_BACKSLASH_ESCAPES seems to corrupt internal buffers with unpredictable results. It is difficult to reproduce, but it is real.
The usage of escape characters like backslashes is a feature for programmers.
It does not make sense for user data, which are the normal data in databases. For this reason the standard for other databases is NO_BACKSLASH_ESCAPES.
Since NO_BACKSLASH_ESCAPES cannot be used for MySQL, I consider this bug to be a show stopper.
It is nearly impossible to migrate existing production databases to MySQL.

Since this bug seems to be reported more than a year ago and still is in the code, you could even loose trust in MySQL and even in open source.
[17 Dec 2009 9:50] Sveta Smirnova
Thank you for the feedback.

What I verified - is not related to the problem which you originally described. I'll open new report for this.

Regarding to the problem which you experience this is bug #49029 which is fixed already. Please subscribe to bug #49029: you will be informed when patch is pushed and you can get fixed binaries from Snapshot download web page.