| Bug #45183 | Prepared statement with eucjpms made wrong strings for binlog. | ||
|---|---|---|---|
| Submitted: | 29 May 2009 9:40 | Modified: | 4 Sep 2012 12:30 |
| Reporter: | Meiji KIMURA | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Prepared statements | Severity: | S2 (Serious) |
| Version: | 5.0.44 5.0.82 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
[31 May 2009 8:24]
Meiji KIMURA
binlog for this problem.
Attachment: mysql-bin.000001 (application/octet-stream, text), 1.32 KiB.
[14 Oct 2009 7:05]
Meiji KIMURA
I did two mistakes. 1) This problem for Server-side prepared statement. These procedure uses old interface, but not client-side prepared statement. It's 'Server-side prepared' statement. 2) It seems that strings of prepared statement for binlog are affected by character_set_connection. not character_set_client, so if I use 'SET NAMES eucjpms;' instead of 'SET CHARACTER SET eucjpms; ', it works well. [New work around] Use 'SET NAMES eucjpms;' instead of 'SET CHARACTER SET eucjpms;'
[19 Oct 2009 8:03]
Meiji KIMURA
I supposed that the specification is not good. It is difficult to understand these behaviors. Especially, the read data are different from binlog data. They should be the same, so I hope that it will be improved.
[18 Feb 2011 0: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".
[4 Sep 2012 12:30]
Praveenkumar Hulakund
"SET CHARACTER SET eucjpms" affects the following connection-related character set variables SET character_set_client = eucjpms; SET character_set_results = eucjpms; SET collation_connection = @@collation_database; collection_connection is important for the literals used. Here, collation_connection is set to character set of collation_database. As we are not setting collation_database explicitly, the value of collation_database is set to default character set type. Because of this, the value of @0 is converted to default character set(collation_connection) type while inserting tuple into the table using prepared statements (thats why binlog contains converted value). And while sending result of the query to client character_set_results(i.e. eucjpms) is used. Set collation_connection to "eucjpms" explicitly to avoid conversion of string literal value(@0) used in prepared statement or as mentioned in Workaround, use "SET NAMES eucjpms". "SET NAMES eucjpms" affects the following connection-related character set variables SET character_set_client = eucjpms; SET character_set_results = eucjpms; SET character_set_connection = eucjpms; Setting character_set_connection to "eucjpms" also implicitly sets collation_connection to the default collation for "eucjpms". Please refer following link for more information http://dev.mysql.com/doc/refman/5.6/en/charset-connection.html MySQL is behaving properly with character set. Improper setting of character set resulted in the behavior mentioned above. After discussing this with Sveta, setting this bug status to "No Bug".
Description: Many programming language still use 'Client-side prepared statement' for its implement of prepared statement. When specify strings as argument for it with eucjpms, it made a wrong strings for binlog, so it break a string data on Slaves. How to repeat: (1) Use MySQL-Sandbox with mysql-enterprise-gpl-5.0.44-linux-i686-glibc23.tar.gz(or mysql-5.0.82-linux-i686-glibc23.tar.gz) (2) Make a replication environment with 'make_sandbox 5.0.44'. (3) On Master, plese execute these commands. CREATE DATABASE meiji DEFAULT CHARACTER SET utf8; USE meiji; SET CHARACTER SET eucjpms; CREATE TABLE sample ( product_name varchar(10) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO sample VALUES('焼肉'); PREPARE sample_ps FROM 'INSERT INTO sample VALUES(?)'; SET @0 = '焼肉'; EXECUTE sample_ps USING @0; DEALLOCATE PREPARE sample_ps; (4) Check data on Master. 'E784BC E88289' is UTF8 for '焼肉'. mysql> SELECT HEX(product_name) FROM sample; +-------------------+ | hex(product_name) | +-------------------+ | E784BCE88289 | | E784BCE88289 | +-------------------+ 2 rows in set (0.00 sec) (5) Check data on Slave. Second data(is made by client-side prepared statement) is broken. +-------------------+ | hex(product_name) | +-------------------+ | E784BCE88289 | | 3F3FE58F963F | +-------------------+ 3 rows in set (0.00 sec) Suggested fix: [Workaround] Don't use Client-side prepared statement, use normal interface for SQLs. [Suggested fix] Master's data is correct, so I supposed that something wrong to make(or output) for binlogs.