MySQL Bugs: #49733: using backslash in NO_BACKSLASH

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
Triage:	Needs Triage: D2 (Serious)

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

Thank you for the report.

Verified as described.

Most likely library bug like bug #38731

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.

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.

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.