| Bug #27552 | NO_BACKSLASH_ESCAPES with replication fails with apostrophes | ||
|---|---|---|---|
| Submitted: | 30 Mar 2007 15:37 | Modified: | 7 Jul 2011 16:53 |
| Reporter: | Ken Johanson | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S1 (Critical) |
| Version: | 5.0.37-log, 5.0, 5.1 BK | OS: | Linux (Linux (x86_64)) |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
| Tags: | replication apostrophe backslash | ||
[30 Mar 2007 15:37]
Ken Johanson
[3 Apr 2007 9:53]
Sveta Smirnova
Thank you for the report. Please provide wrong query.
[3 Apr 2007 14:13]
Ken Johanson
Executed on the master:
insert into tbl (col) values ('abc''def');
[3 Apr 2007 14:15]
Ken Johanson
Here is the error logged on the slave:
insert into tbl (col) values ('abc\'def');
[4 Apr 2007 7:57]
Sveta Smirnova
Thank you for the feedback. In my environment replication still success. Please provide your configuration files both for master and slave.
[4 Apr 2007 17:32]
Ken Johanson
Master my.cnf: [mysqld] set-variable=collation_server=utf8_unicode_ci set-variable=character_set_server=utf8 set-variable=max_allowed_packet=16M set-variable=max_connections=1000 set-variable=sql-mode=PIPES_AS_CONCAT,ANSI_QUOTES,NO_BACKSLASH_ESCAPES set-variable=back_log=250 basedir=/usr/local/mysql/ lower_case_table_names=1 datadir=/usr/local/mysql/data skip-name-resolve server-id=1 log-bin=sql01-bin Slave server my.cnf: [mysqld] set-variable=collation_server=utf8_unicode_ci set-variable=character_set_server=utf8 set-variable=max_allowed_packet=16M set-variable=max_connections=1000 set-variable=sql-mode=PIPES_AS_CONCAT,ANSI_QUOTES,NO_BACKSLASH_ESCAPES set-variable=back_log=250 basedir=/usr/local/mysql/ lower_case_table_names=1 datadir=/usr/local/mysql/data skip-name-resolve server-id=2 log-bin=sql02-bin relay-log=sql02-relay-bin
[27 Apr 2007 12:27]
Sveta Smirnova
Thank you for the feedback. I still can not repeat the problem with configuration you provided. Please indicate accurate version of the package you use to I can check if it is repeatable only with this package.
[27 May 2007 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[10 Aug 2007 15:43]
Ken Johanson
The version of Mysql on both the master and slave are:
5.0.45-log
Re: cannot duplicate, please ensure that:
a) You have set the my.conf:'set-variable=sql-mode=PIPES_AS_CONCAT,ANSI_QUOTES,NO_BACKSLASH_ESCAPES' on both master and slave, and;
b) you are sending DML statements that contain only apostrpohes, not backslash escapes: INSERT INTO foo VALUES ('abcd''efgh')
[10 Aug 2007 15:49]
Ken Johanson
This bug report is being noticed by others: http://lists.mysql.com/mysql/208428 http://forums.mysql.com/read.php?26,84229,117315
[20 Aug 2007 14:49]
Ken Johanson
Marking critical because there is no workaround at the replication layer (and because replication is fully inoperable), and it is NOT possible to disable the NO_BACKSLASH_ESCAPE setting on systems that have clients depending on it.
[22 Aug 2007 13:13]
Sveta Smirnova
test case
Attachment: rpl_bug27552.test (application/octet-stream, text), 323 bytes.
[22 Aug 2007 13:14]
Sveta Smirnova
option file, same for slave
Attachment: rpl_bug27552-master.opt (application/octet-stream, text), 62 bytes.
[22 Aug 2007 14:58]
MySQL Verification Team
I wasn't able to repeat this issue:
slave>show slave status;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: miguel
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: sql01-bin.000001
Read_Master_Log_Pos: 532
Relay_Log_File: sql02-relay-bin.000002
Relay_Log_Pos: 433
Relay_Master_Log_File: sql01-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 532
Relay_Log_Space: 433
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.00 sec)
slave>select * from test.tbl;
*************************** 1. row ***************************
col: abc'def
1 row in set (0.00 sec)
slave>
[22 Aug 2007 19:18]
Ken Johanson
Miguel, the only difference I see in the test cases is your use of innodb tbales, whereas my test was with myisam. Please try isam and let me know f you still cannot reproduce. Just on the chance that the replication stream encodes differently based on which engine is used.
[22 Aug 2007 21:40]
MySQL Verification Team
Hello Ken,
I didn't applied the test case attached by Sveta. I followed your instructions
master/slave my.cnf changing only basedir and datadir to custom on my side
and below the same result again:
miguel@luar:~/dbs/5.0> bin/mysql -uroot --socket=/home/miguel/dbs/5.0/mysql.sock --prompt='master >' -E test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.0.48-debug-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
master >show create table tbl;
*************************** 1. row ***************************
Table: tbl
Create Table: CREATE TABLE "tbl" (
"col" char(20) collate utf8_unicode_ci default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)
master >select * from tbl;
*************************** 1. row ***************************
col: abc'def
1 row in set (0.01 sec)
master >insert into tbl (col) values ('abc''def');
Query OK, 1 row affected (0.00 sec)
master >
miguel@luar:~/dbs/5.0s> bin/mysql -uroot --socket=/home/miguel/dbs/5.0s/mysql.sock --port=3307 --prompt='slave >' -E test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.48-debug-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
slave >show slave status;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: miguel
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: sql01-bin.000003
Read_Master_Log_Pos: 202
Relay_Log_File: sql02-relay-bin.000008
Relay_Log_Pos: 339
Relay_Master_Log_File: sql01-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 202
Relay_Log_Space: 339
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.00 sec)
slave >show create table tbl;
*************************** 1. row ***************************
Table: tbl
Create Table: CREATE TABLE "tbl" (
"col" char(20) collate utf8_unicode_ci default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.01 sec)
slave >select * from tbl;
*************************** 1. row ***************************
col: abc'def
*************************** 2. row ***************************
col: abc'def
2 rows in set (0.00 sec)
slave >
[22 Aug 2007 21:42]
MySQL Verification Team
I forgot to mention the OS tested: miguel@luar:~/dbs/5.0s> cat /etc/issue Welcome to openSUSE 10.2 (X86-64) - Kernel \r (\l). miguel@luar:~/dbs/5.0s>
[22 Aug 2007 21:52]
Ken Johanson
Thanks Miguel. I'm just about certain this wasn't an issue fixed between my .45 version and you .48; I see no comments about it in the changelog. So it appears then to be an environment variable (possible the SQL connector). And it DEFINITELY is an issue on my version at least. I see you are using the mysql shell to issue your DML, and I wonder if that is doing some backslash adding of its own (which would mask the problem). Can you send the query through an API like JDBC, etc, and not using Prepared-Statement type API calls?
[23 Aug 2007 5:38]
Sveta Smirnova
Hello Ken, for master doesn't matter which API used for inserting data. BTW I tested using C API, on 3 different machines and on 3 different versions including indicated 5.0.45 with both MyISAM and InnoDB tables without success.
[23 Aug 2007 8:40]
Andrea Gangini
Sveta, I really think is a JDBC connector issue, and I also think I was able to produce a test case.
The JDBC connector used for this test is mysql-connector-java-5.0.4-bin.jar since later versions are not usable in my production environment due problems with UTF-8 characters (which I don't have time to investigate :-)
On my production server I use CentOS Linux, but also the windows version of mysql is affected (I used that for this tests).
Steps to reproduce:
DROP DATABASE `test`;
CREATE DATABASE `test` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `test`;
DROP TABLE IF EXISTS `test`;
CREATE TABLE IF NOT EXISTS `test` (
`ID` int(11) NOT NULL auto_increment,
`TEXT` text NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;
RESET MASTER; #Not necessary but useful for obtaining a much simplier output of binary log
Then I insert a simple test row using JDBC and a prepared statement (see sample java code in attachment).
Then I examine the output of binary log:
C:\mysql\bin>mysqlbinlog.exe C:\mysql\data\mysql-bin.000001
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#070823 10:21:09 server id 1 end_log_pos 98 Start: binlog v 4, server v 5.0.45-community-nt-log created 070823 10:21:09 at startup
# Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.
ROLLBACK/*!*/;
# at 98
#070823 10:21:28 server id 1 end_log_pos 126 Intvar
SET INSERT_ID=1/*!*/;
# at 126
#070823 10:21:28 server id 1 end_log_pos 234 Query thread_id=92 exec_time=0 error_code=0
use test/*!*/;
SET TIMESTAMP=1187857288/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/;
SET @@session.sql_mode=3145728/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
INSERT INTO test (TEXT) VALUES ('abc\'\'def')/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
As you see the query in binary log contains backlashes and stops replication.
Please tell me if you need additional details.
[23 Aug 2007 8:41]
Andrea Gangini
Java program which insert a row using prepared statements
Attachment: TestMysql.java (text/java), 1.64 KiB.
[23 Aug 2007 9:00]
Andrea Gangini
Repeated the test with connector mysql-connector-java-5.0.7-bin.jar, the query now is written correctly: use `test`; INSERT INTO test (TEXT) VALUES (x'6162632727646566') which I think it translates in ASCII as abc''def
[23 Aug 2007 19:29]
Ken Johanson
Andrea, apparently you are able to reproduce this using an old JDBC driver, yes? If this is so, do we all agree that this is more of a mysql-server problem, and not a jdbc connector one per se?: -The server and replication should be immune to old connectors that people are using. -Regardless of the connector it is apparently possible to create a replication denial of service by sending an specially-crafted (old-format encoding) to the database. BTW To confirm, I *am* using an old JDBC driver version.
[24 Aug 2007 14:27]
Ken Johanson
I was able to confirm Andrea's test, and it seems clear this is a vulnerability in the server's replication engine (vulnerable to old backslash escaping).
[7 Sep 2007 10:58]
Sveta Smirnova
Thank you for the feedback. Please confirm or refuse if I understood correctly with latest Connector/J bug is not repeatable?
[7 Sep 2007 12:35]
Andrea Gangini
No, it's not repeatable with connector 5.0.7. I think that the default NO_BACKSLASH_ESCAPES Sql mode of the server gets modified by the connector issuing this command: SET @@session.sql_mode=3145728/*!*/; I don't know if this is a bug or not, in my opinion it should not be possible to alter a global variable like NO_BACKSLASH_ESCAPES, even if only for the session, but maybe this is the correct behaviour...
[7 Sep 2007 12:59]
Sveta Smirnova
Thank you for the feedback. I'll set status of the report as "Can't repeat" again as bug is closed already.
[7 Sep 2007 14:21]
Ken Johanson
Sveta, - just because you cannot duplicate this issue with the latest (or ANY specific version) of driver, even when someone suggested this IS an issue with old drivers, is NOT grounds for marking it as 'cant repeat'. Did you try an old version driver?.. Do you believe that the server is not at fault if an old driver causes the replication to fail?
[25 Sep 2007 6:16]
Sveta Smirnova
Ken, please indicate accurate version of Connector/J bug repatable with.
[25 Sep 2007 7:36]
Andrea Gangini
Repeatable with 5.0.4; not repeatable with 5.0.7
[25 Sep 2007 9:16]
Sveta Smirnova
Andrea, thank you for the feedback. I've just tested. Bug is repeatable with version 5.0.4 on my side too. Server is same. So this was surely Connector/J problem which is solved now.
[26 Sep 2007 14:17]
Ken Johanson
Confirming that this bug, in the server replication, can cause replication to fail. Problem can only be handled this server-side to prevent vulnerability-to /failure caused by intentional or naive use of, old drivers by clients.
[10 Oct 2007 9:16]
Konstantin Osipov
Replication failure, upgrading.
[14 Dec 2007 20:32]
Ken Johanson
According to: http://dev.mysql.com/doc/refman/5.1/en/replication-formats.html "For MySQL 5.1.20 and later (and MySQL 5.0.46 for backwards compatibility), the following session variables are written to the binary log and honoured by the replication slave when parsing the binary log: *SQL_MODE etc..." Has this been verified to resolve this issue at least in 5.1.20 and 5.0.46?
[18 Jan 2008 17:46]
Ken Johanson
Finally had time to verify this myself and in a production env. This bug appears to has been fixed (tested version 5.0.45), no longer can reproduce. Closing.
[2 May 2008 18:49]
Ken Johanson
This is still a problem, using server version 5.1.24-rc-log (master and slave). This is happening either because there was a regression, or because earlier I had incorrectly assessed that it was fixed when I closed this bug. Getting now on the slave: 080502 18:40:05 [Warning] Slave: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'menu.childMenuIcon \'','','GET','','Java/1.6.0_05','','0','404')' at line 1 Error_code: 1064 080502 18:40:05 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'sql01-bin.000007' position 10532012 Note the apostrophe is being escaped by a backslash; and the server while running in NO_BACKSLASH_ESCAPES mode considers this an error.
[11 May 2008 7:15]
Sveta Smirnova
To repeat:
1. Take script provided in comment "[23 Aug 2007 10:41] Andrea Gangini"
2. Replace Class.forName with Class.forName("com.mysql.jdbc.Driver");
3. Replace connection settings with yours.
4. Create table test as described in comment "[23 Aug 2007 10:40] Andrea Gangini"
5. Compile Java program using c/J 5.0.4
6. Run, then examine binary log.
This fixed in c/J 5.0.5. But I agree it should be in "Verified" as is strange why content of binary log depends from info sent by client.
[11 May 2008 8:05]
Sveta Smirnova
With version 5.1 BK incorrect string is inserting even if use c/J 5.0.8
[4 Mar 2011 13:15]
sameer joshi
Following error has caused my replication to stop.
i think this related to current bug report.
can anybody help me out.
Last_SQL_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Sorry\'' COLLATE 'latin1_swedish_ci'),
NAME_CONST('sequence',1' at line 11' on query. Default database: 'martjack_mysql_test'. Query: 'INSERT INTO tblPageMenu(PageControlId,
ItemId,
ItemType,
Url,
LinkName,
sequence)
VALUES ( NAME_CONST('PageControlId',451091),
NAME_CONST('ItemId',_latin1'M1299233717461' COLLATE 'latin1_swedish_ci'),
NAME_CONST('ItemType',_latin1'M' COLLATE 'latin1_swedish_ci'),
NAME_CONST('Url',_latin1'www.google.com' COLLATE 'latin1_swedish_ci'),
NAME_CONST('LinkName',_latin1'I\'m Sorry\'' COLLATE 'latin1_swedish_ci'),
NAME_CONST('sequence',1))'
[17 Jan 2013 10:00]
MySQL Verification Team
See also http://bugs.mysql.com/bug.php?id=68106
