MySQL Bugs: #55961: Savepoint Identifier should be enclosed with backticks

Bug #55961	Savepoint Identifier should be enclosed with backticks
Submitted:	13 Aug 2010 5:13	Modified:	6 Oct 2010 12:39
Reporter:	Mikiya Okuno	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:	Alfranio Tavares Correia Junior	CPU Architecture:	Any

Description:
When java.sql.Connection#.setSavepoint() is called without parameter, Connector/J generates a savepoint identifier using hex numbers. If the id is proceeded by digits, followed by "e" and digits, then the id is translated to floating point number.

This doesn't cause a problem on the master because Connector/J encloses the id with backticks (`). However, such backticks are not recorded in the binarylog, and it causes a replication failure.

How to repeat:
On the master:

mysql> create table t1 (a int) engine innodb;

mysql> begin;

mysql> insert into t1 values(1);

mysql> savepoint `123e456`;

mysql> insert into t1 values(1);

mysql> commit;

Now, you'll see the replication failure.

Suggested fix:
Enclose savepoint identifiers in binlogs.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/117416

3496 Alfranio Correia	2010-09-02
      BUG#55961 Savepoint Identifier should be enclosed with backticks
      
      Added backticks to the savepoint identifier.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/117417

3498 Alfranio Correia	2010-09-02
      BUG#55961 Savepoint Identifier should be enclosed with backticks
      
      Added backticks to the savepoint identifier.

Pushed to mysql-5.1-bugteam and mysql-5.5-merge.

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@sun.com-20100928153607-tdsxkdm5cmuym5sq) (version source revid:alik@sun.com-20100928153508-0saa6v93dinqx1u7) (merge vers: 5.6.1-m4) (pib:21)

Pushed into mysql-next-mr (revid:alik@sun.com-20100928153646-pqp8o1a92mxtuj3h) (version source revid:alik@sun.com-20100928153532-lr3gtvnyp2en4y75) (pib:21)

Pushed into mysql-5.5 5.5.7-rc (revid:alik@sun.com-20100928153459-4nudf4zgzlou4s7q) (version source revid:alik@sun.com-20100928153459-4nudf4zgzlou4s7q) (merge vers: 5.5.7-rc) (pib:21)

See also BUG#55962

Documented as follows in the 5.5.7 and 5.6.1 changelogs:
 

      Backticks used with names of savepoints were not preserved in 
      the binary log, which could lead to replication failure when 
      the name without backticks could be misinterpreted, causing a 
      syntax or other error.  

      This could cause problems with MySQL APIs that make use of 
      generated savepoint IDs. For example, if 
      java.sql.Connection.setSavepoint() is called without any 
      parameters, Connector/J generates a savepoint identifier 
      consisting of a string of hexadecimal digits (0-F) encased in 
      backtick (`) characters. If such an ID took the form `NeN` 
      (where N represents a string of 1 or more of the decimal digits 
      0-9, and e is a literal uppercase or lowercase E character), 
      then removing the backticks left behind a substring which the 
      slave MySQL server tried to interpret as a floating point number 
      rather than an identifier.

Set NM status -- waiting on 5.1 merge to close.

Still waiting on 5.1 merge to close -- reverted to NM status.

Bugfix also documented in the 5.1.52 changelog. Closed.

Pushed into mysql-5.1 5.1.53 (revid:build@mysql.com-20101101184443-o2olipi8vkaxzsqk) (version source revid:build@mysql.com-20101101184443-o2olipi8vkaxzsqk) (merge vers: 5.1.53) (pib:21)