You know everything about how MySQL treats UTF8 and LATIN1 charsets and how the collation table impacts on selection and insertion of data, right?<\/p>\n
Great, then stop reading :)<\/p>\n
I don't and since I'm in the process of setting up a new version of the Opera accounts database, I really don't want to screw up things. I tried to fully understand how MySQL works in this respect (charsets, collations, etc…) but reading documentation and memorizing it wasn't very easy. Plus, there's a thousands<\/a> blog posts<\/a> on the matter, not always 100% accurate.<\/p>\n So I thought I'd better get hands on and I wrote a kind of database test suite<\/b>.<\/p>\n Now this test suite is hooked up to the main project builds on Jenkins. Here's a sample output:<\/p>\n And here's an example of "sanity check" test case, which doesn't do much:<\/p>\n This SQL test code uses mytap<\/a>. You can see how the Another, more interesting test case, is the following:<\/p>\n An interesting thing that I didn't know how to do in the beginning is how to trap errors<\/b>. I left out that part from the test code to simplify, but here it is:<\/p>\n It's a bit convoluted. To trap errors you have use the In this particular tests, the table uses the This of course may seem trivial. And I guess it is, but for me it's a much better way of learning<\/b> than scouring through the manuals or the many blog posts out there that may or may not reflect the environment I'm working with.<\/p>\n Routinely running this kind of test suite makes it possible and easy to verify the database behaviour:<\/p>\n I consider this my live documentation on how MySQL works.<\/b> I would really appreciate if you have any feedback on this. Have fun!<\/p>\n","protected":false},"excerpt":{"rendered":" You know everything about how MySQL treats UTF8 and LATIN1 charsets and how the collation table impacts on selection and insertion of data, right? Great, then stop reading :) I don't and since I'm in the process of setting up a new version of the Opera accounts database, I really don't want to screw up […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[58,55,54,56,50,59,53,57],"yoast_head":"\n\r\n[...]\r\n[workspace] $ \/bin\/sh -xe \/tmp\/hudson3255767718598715423.sh\r\n+ .\/bin\/run-dbtest-suite\r\nbasedir=\/var\/lib\/jenkins\/jobs\/auth-db\/workspace\r\n\/var\/lib\/jenkins\/jobs\/auth-db\/workspace\/t\/database-tests\/__initdb__.my ........................................... \r\n1..2\r\nok 1 - Using utf8tests database\r\nok 2 - Server charset is latin1\r\nok\r\n\/var\/lib\/jenkins\/jobs\/auth-db\/workspace\/t\/database-tests\/collation-utf8_bin.my ................................... \r\n1..6\r\nok 1 - All our records are there. No duplicate key error.\r\nok 2 - utf8_bin collation does not collate a\/\u00c3\u00a2\/\u00c3\u00a0\/A\/...\r\nok 3 - utf8_bin collation does not collate a\/\u00c3\u00a2\/\u00c3\u00a0\/A\/...\r\nok 4 - utf8_bin collation does not collate a\/\u00c3\u00a2\/\u00c3\u00a0\/A\/...\r\nok 5 - Query for mixed-case username does not return lowercase username\r\nok 6 - Query for upper-case username does not return lowercase username\r\nok\r\n\/var\/lib\/jenkins\/jobs\/auth-db\/workspace\/t\/database-tests\/collation-utf8_general_ci.my ............................ \r\n1..7\r\nok 1 - Collation for t007 is utf8_general_ci\r\nok 2 - utf8_general_ci collation normalizes accents, diacritics and the like\r\nok 3 - A and \u00c3\u2026 are collated to the same character in the utf8_general_ci table\r\nok 4 - \u00c3\u00a5 and \u00c3\u2026 are collated to the same character in the utf8_general_ci table\r\nok 5 - lower\/upper case chars are collated in the utf8_general_ci table\r\nok 6 - lower\/upper case chars are collated in the utf8_general_ci table\r\nok 7 - We are allowed to insert all records just because there is no unique constraint\r\nok\r\n\/var\/lib\/jenkins\/jobs\/auth-db\/workspace\/t\/database-tests\/collation-utf8_unicode_ci.my ............................ \r\n1..7\r\nok 1 - Collation for t005 is utf8_unicode_ci\r\nok 2 - utf8_unicode_ci collation normalizes accents, diacritics and the like\r\nok 3 - A and \u00c3\u2026 are collated to the same character in the utf8_unicode_ci table\r\nok 4 - \u00c3\u00a5 and \u00c3\u2026 are collated to the same character in the utf8_unicode_ci table\r\nok 5 - lower\/upper case chars are collated in the utf8_unicode_ci table\r\nok 6 - lower\/upper case chars are collated in the utf8_unicode_ci table\r\nok 7 - We are allowed to insert all records just because there is no unique constraint\r\nok\r\n\/var\/lib\/jenkins\/jobs\/auth-db\/workspace\/t\/database-tests\/default-table-charset.my ................................ \r\n1..3\r\nok 1 - Default character set is utf8 when no charset is specified (from server)\r\nok 2 - Default character set is utf8 when "CHARSET utf8" specified in the CREATE TABLE\r\nok 3 - Default character set is utf8 when "CHARSET utf8" and "COLLATE" specified in the CREATE TABLE\r\nok\r\n...\r\n\/var\/lib\/jenkins\/jobs\/auth-db\/workspace\/t\/database-tests\/username-with-utf8-chars.my ............................. \r\n1..5\r\nok 1 - We have some UTF-8 encoded string in our hands (hex)\r\nok 2 - We have some UTF-8 encoded string in our hands (charset)\r\nok 3 - Can select back UTF-8 content from a CHARSET utf8 table\r\nok 4 - Given string is exactly 24 bytes long (length)\r\nok 5 - Given string is exactly 8 (wide) characters long (char_length)\r\nok\r\nAll tests successful.\r\nFiles=11, Tests=80, 0.739731 wallclock secs ( 0.05 usr 0.02 sys + 0.07 cusr 0.01 csys = 0.15 CPU)\r\nResult: PASS\r\nRecording test results\r\nFinished: SUCCESS\r\n<\/code><\/pre>\n\r\n 1 -- Check that we can insert and retrieve UTF-8 content correctly\r\n 2 \r\n 3 BEGIN;\r\n 4 \r\n 5 SET NAMES utf8;\r\n 6 \r\n 7 SELECT tap.plan(5);\r\n 8 \r\n 9 USE auth_utf8tests;\r\n 10 \r\n 11 SET @username = '\u00e4\u00bb\u0160\u00e6\u2014\u00a5\u00e8\u00af\u009d\u00e9\u00a2\u02dc\u00e4\u00bb\u0160\u00e6\u2014\u00a5\u00e8\u00af\u009d\u00e9\u00a2\u02dc';\r\n 12 SET @encoded = 'C1BB8AE697A5E8AF9DE9A298E4BB8AE697A5E8AF9DE9A298';\r\n 13 \r\n 14 SELECT tap.eq(\r\n 15 HEX(@username),\r\n 16 @encoded,\r\n 17 'We have some UTF-8 encoded string in our hands (hex)'\r\n 18 );\r\n 19 \r\n 20 SELECT tap.eq(\r\n 21 CHARSET(@username),\r\n 22 'utf8',\r\n 23 'We have some UTF-8 encoded string in our hands (charset)'\r\n 24 );\r\n 25 \r\n 26 INSERT INTO t001 (f1) VALUES (@username);\r\n 27 \r\n 28 SELECT tap.eq(\r\n 29 (SELECT HEX(f1) FROM t001 WHERE f1 = @username),\r\n 30 @encoded,\r\n 31 'Can select back UTF-8 content from a CHARSET utf8 table'\r\n 32 );\r\n 33 \r\n 34 SELECT tap.eq(\r\n 35 (SELECT LENGTH(f1) FROM t001 WHERE f1 = @username),\r\n 36 24,\r\n 37 'Given string is exactly 24 bytes long (length)'\r\n 38 );\r\n 39 \r\n 40 SELECT tap.eq(\r\n 41 (SELECT CHAR_LENGTH(f1) FROM t001 WHERE f1 = @username),\r\n 42 8,\r\n 43 'Given string is exactly 8 (wide) characters long (char_length)'\r\n 44 );\r\n 45 \r\n 46 -- Finish the tests and clean up.\r\n 47 CALL tap.finish();\r\n 48 ROLLBACK;\r\n<\/code><\/pre>\nSELECT tap.*<\/code> calls are just the equivalents of the TAP testing framework<\/a> of Perl. SELECT tap.eq()<\/code> is the equivalent of Test::More::is()<\/code><\/a>, and so on.<\/p>\n\r\n 1 --\r\n 2 -- Verify how the utf8_unicode_ci collation works\r\n 3 --\r\n 4 \r\n 5 BEGIN;\r\n 6 \r\n 7 SET NAMES utf8;\r\n 8 \r\n 9 SELECT tap.plan(12);\r\n 10 \r\n 11 USE auth_utf8tests;\r\n 12 \r\n\r\n [...]\r\n\r\n 40 SELECT tap.eq(\r\n 41 (SELECT TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='t015'),\r\n 42 'utf8_unicode_ci',\r\n 43 'Collation for t015 is utf8_unicode_ci'\r\n 44 );\r\n\r\n [...]\r\n\r\n 48 \r\n 49 SELECT tap.eq(\r\n 50 (SELECT GROUP_CONCAT(id) FROM t015 WHERE username = 'testuser1a' ORDER BY id),\r\n 51 '10',\r\n 52 'utf8_unicode_ci collation normalizes accents, diacritics and the like'\r\n 53 );\r\n 54 \r\n 55 SELECT tap.eq(\r\n 56 (SELECT GROUP_CONCAT(id) FROM t015 WHERE username = 'testuser1\u00c3\u2026' ORDER BY id),\r\n 57 '10',\r\n 58 'A and \u00c3\u2026 are collated to the same character in the utf8_unicode_ci table'\r\n 59 );\r\n 60 \r\n 61 SELECT tap.eq(\r\n 62 (SELECT GROUP_CONCAT(id) FROM t015 WHERE username = 'testuser1\u00c3\u00a5' ORDER BY id),\r\n 63 '10',\r\n 64 '\u00c3\u00a5 and \u00c3\u2026 are collated to the same character in the utf8_unicode_ci table'\r\n 65 );\r\n 66 \r\n 67 SELECT tap.eq(\r\n 68 (SELECT GROUP_CONCAT(id) FROM t015 WHERE username = 'TestUser1A' ORDER BY id),\r\n 69 '10',\r\n 70 'lower\/upper case chars are collated in the utf8_unicode_ci table'\r\n 71 );\r\n 72 \r\n 73 SELECT tap.eq(\r\n 74 (SELECT GROUP_CONCAT(id) FROM t015 WHERE username = 'TESTUSER1A' ORDER BY id),\r\n 75 '10',\r\n 76 'lower\/upper case chars are collated in the utf8_unicode_ci table'\r\n 77 );\r\n 78 \r\n 79 SELECT tap.eq(\r\n 80 (SELECT COUNT(*) FROM t015),\r\n 81 1,\r\n 82 'We are allowed to insert only 1 record, because the others collate to the same string'\r\n 83 );\r\n 84 \r\n 85 -- Finish the tests and clean up.\r\n 86 CALL tap.finish();\r\n 87 ROLLBACK;\r\n<\/code><\/pre>\n\r\n 13 DELIMITER \/\/\r\n 14 \r\n 15 DROP PROCEDURE IF EXISTS populate_table \/\/\r\n 16 \r\n 17 CREATE PROCEDURE populate_table ()\r\n 18 BEGIN\r\n 19 \r\n 20 DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' BEGIN\r\n 21 SELECT tap.ok(\r\n 22 1,\r\n 23 'We should get dupkey errors when inserting data with collation utf8_unicode_ci'\r\n 24 );\r\n 25 END;\r\n 26 \r\n 27 INSERT INTO t015 (id,username,note) VALUES (10, 'testuser1a', 'plain');\r\n 28 INSERT INTO t015 (id,username,note) VALUES (20, 'testuser1\u00c3\u00a2', 'circumflex a');\r\n 29 INSERT INTO t015 (id,username,note) VALUES (30, 'testuser1\u00c3\u00a0', 'a grave');\r\n 30 INSERT INTO t015 (id,username,note) VALUES (40, 'testuser1\u00c3\u2026', 'A circ');\r\n 31 INSERT INTO t015 (id,username,note) VALUES (50, 'TestUser1A', 'mixed case');\r\n 32 INSERT INTO t015 (id,username,note) VALUES (60, 'TESTUSER1A', 'upper case');\r\n 33 \r\n 34 END;\r\n 35 \r\n 36 \/\/\r\n 37 \r\n 38 DELIMITER ;\r\n 39 \r\n 46 \/* Should generate 5 dupkey errors (taken as successful tests) *\/\r\n 47 CALL populate_table;\r\n<\/code><\/pre>\nDECLARE HANDLER<\/code> statement. DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'<\/code> means that whenever SQLSTATE is '23000', and that corresponds to a duplicate key error<\/b>, then execute this block of code. All of that must necessarily be wrapped into a stored procedure<\/b>. Handlers outside of stored procedures are not allowed.<\/p>\nutf8_unicode_ci<\/code> collation table, so we are expecting a duplicate key error on username<\/b> whenever we insert the string 'testuser1\u00c3\u00a0'<\/code> or 'TESTUSER1A'<\/code>, because 'testuser1a'<\/code> was already inserted at the beginning. Of all the INSERT<\/code> statements, only the first one is bound to succeed, so I put a SELECT tap.ok(1)<\/code> for the duplicate key HANDLER and I expect 5 tests when I make the CALL populate_table;<\/code>.<\/p>\n\n
default-charset=utf8<\/code> in my MySQL config breaks everything.<\/li>\n<\/ul>\n