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?
Aucun commentaire:
Enregistrer un commentaire