0

I need to change a database charset because of this issue and I solved my issue in staging environment with the SQL command

ALTER TABLE `articles` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

In this way I get the content converted to utf8 and I now can insert utf8 strings in those tables. Now I need to apply that fix on production databases which have a master-slave setup.

I created a latin1 unused table to test the command before doing that on real data. In a replicated database I added a table and some rows

-- On master host
CREATE TABLE `test_table` (
  `content` mediumtext NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- Insert a utf8 only chars, it produces 'H?rvatistan' initially
INSERT INTO `test_table` (`content`) VALUES ('Hırvatistan');
-- Change table charset
ALTER TABLE `test_table` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
-- Insert again the same value in master, this time I get the right value saved
INSERT INTO `test_table` (`content`) VALUES ('Hırvatistan');
-- On master I get
SELECT content, HEX(content), HEX('Hırvatistan') FROM test_table;
+-------------+--------------------------+--------------------------+
| content     | HEX(content)             | HEX('Hırvatistan')       |
+-------------+--------------------------+--------------------------+
| H?rvatistan | 483F72766174697374616E   | 48C4B172766174697374616E |
+-------------+--------------------------+--------------------------+
| Hırvatistan | 48C4B172766174697374616E | 48C4B172766174697374616E |
+-------------+--------------------------+--------------------------+

So everything has worked as expected. However when I go to the slave host I find a strange thing. All these commands has been executed (all of them are present in master binlog) and the table is now utf8 in slave host as well but the last query return a different result set in slave host:

SELECT content, HEX(content), HEX('Hırvatistan') FROM test_table;
+-------------+------------------------+--------------------------+
| content     | HEX(content)           | HEX('Hırvatistan')       |
+-------------+------------------------+--------------------------+
| H?rvatistan | 483F72766174697374616E | 48C4B172766174697374616E |
+-------------+------------------------+--------------------------+
| H?rvatistan | 483F72766174697374616E | 48C4B172766174697374616E |
+-------------+------------------------+--------------------------+

How that is possible?

Update

I tried it again, but this time I waited a little bit before executing the second insert and everything has worked as expected. Maybe in the first attempt second insert was executed before actual table conversion? Is this possible?

3
  • What is the setting of binlog_format on each machine? (No, I don't know if it is important.) – Rick James Mar 26 '15 at 23:08
  • It's STATEMENT on both master and slave. I changed that because I needed to run pt-table-checksum and it requires it to be STATEMENT – Fabio Mar 27 '15 at 11:30
  • It's a mystery. If you want to, write a bug at bugs.mysql.com. – Rick James Mar 27 '15 at 16:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.