| Bug #21149 | Replication fails because of apostrophe in data | ||
|---|---|---|---|
| Submitted: | 19 Jul 2006 13:30 | Modified: | 30 Mar 2007 19:58 |
| Reporter: | Brian Robinson | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 5.0.22-0 | OS: | Linux (SuSe 10.0) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | apostrophe, replication | ||
[19 Jul 2006 13:45]
Brian Robinson
I have restested with SQL-MODE set that it is not no-backslash-escapes on both master and slave replications works fine with apostrophes - apologies for not thinking of this before brian
[30 Mar 2007 15:21]
Ken Johanson
This bug should not be closed if it requires a workaround of disabling the NO_BACKSLASH_ESCAPES, as this is a desired default-mode of operation for many users. If this comment entry does not allow me to re-open (testing herein) this bug I will have to file a new one.
[30 Mar 2007 19:58]
Brian Robinson
Looks like my comment was. confusing. Scenarios was from begining 1) we had a insert statement that had backslashes in teh select section e.g. insert into table_1 (select a,b,c from table_2 where table_2.path like concat(table_1.path,%) not excatly the query but something like that. As the path could contain windows paths - I set (WRONGLY SO) SQL_MODE in my.cnf to be NO_BAKCLASH_ESCAPES). It turns out this setting was causing the replication slave to fail when it recived data with apostrophes in them. Now I have forund a work around for the original problem, removed the NO_BACKSLASH_ESCAPES from the my.cnf and all works fine - including the slave. So from my point of view - this bug can be closed.
Description: I have marked this as critical - it does not stop our production server - but it does stop our replication server - which is extremely critical to our operations. If I update a users details as follows update account set fullname='bill mc''hanrahan' where userid=12; through mysql client then this data gets replicted fine. If we do it through our JAVA client where we use preparedStatements and parameters. so the fullname to be updated on our client application would look like <bill mc'hanrahan> Then replication fails with following error 060719 15:53:13 [ERROR] Slave: 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 'hanrahan',PhoneNumber='+4561774990',Email='brian@soonr.com',IMAccount='',IMAccou' at line 1' on query. Default database: 'lyngby_test'. Query: 'UPDATE Account SET UserName='brian',Password='XXXXX',FullName='bill mc\'hanrahan',PhoneNumber='+4561774990',Email='XXXX@XXX.COM',IMAccount='',IMAccountState=0, TimeZoneId='Europe/Copenhagen', SMSEnabled='Y', MailSummary='F;1;9;17;T;30;T', QuotaEnabled='N', QuotaAllocated=-1, SkypeEnabled='Y', SkypeAccount='', payment_profile='', payment_expiredate=0, payment_billingref='' WHERE UserId=12', Error_code: 1064 At first I thought the problem was because we run with SQL-MODE='NO_BACKSLASH_ESCAPES' because it can be seen in the above update statement that the apostrophe in the fullname has been "backslashed" Buut I have removed this from the my.cnf - restarted mysql replication server (the replication client) and executed "start slave" the error occurs again How to repeat: create account table create table Account ( UserId integer AUTO_INCREMENT not null, UserName varchar(64) not null, Password varchar(64) not null, Fullname varchar(254) not null, PhoneNumber varchar(64) not null, Email varchar(64) not null, IMAccount varchar(64) not null, IMAccountState integer default 0, SMSAsIM integer default 0, TimeZoneId varchar(20) not null default 'PST', LoginCounter integer not null default 0, LastLogin bigint not null default 0, SecQuestion varchar(64) not null default '', SecAnswer varchar(64) not null default '', CreateTime bigint not null default 0, SMSEnabled varchar(1) not null default 'Y', SMSCount integer not null default 0, SMSCredit integer not null default 0, QuotaEnabled varchar(1) not null default 'N', QuotaAllocated bigint not null default 0, QuotaUsed bigint not null default 0, FailedLoginCount tinyint not null default 0, FailedPassword varchar(64) not null default '', MailSummary varchar(25) not null default '', Campaign varchar(10) not null default '', SkypeEnabled varchar(1) not null default 'N', SkypeAccount varchar(64) null default null, Partner varchar(4) not null default '', PartnerId varchar(64) not null default '', primary key (UserId) ); try the two updates mentioned above Suggested fix: handle backslashed apostrophe correctly on replication client It could be that I am dfoing something wrong on the client as I am sure this is not an uncommon form of data.