| Bug #37046 | Character sets: failure or hang with sharp s | ||
|---|---|---|---|
| Submitted: | 28 May 2008 18:43 | Modified: | 5 Jan 2010 15:26 |
| Reporter: | Peter Gulutzan | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Charsets | Severity: | S3 (Non-critical) |
| Version: | 5.0.x > 5.1.x upgrades | OS: | Linux |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
[29 May 2008 18:06]
MySQL Verification Team
Thank you for the bug report. To verify changed column name in the second set of queries from s2 to s1. mysql> show processlist; +----+------+-----------+------+---------+------+--------------+---------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+--------------+---------------------------------+ | 1 | root | localhost | test | Query | 242 | Sending data | select * from t where s1 > 'ss' | | 2 | root | localhost | test | Query | 0 | NULL | show processlist | +----+------+-----------+------+---------+------+--------------+---------------------------------+ 2 rows in set (0.00 sec)
[16 Jul 2008 6:07]
Alexander Barkov
Dear doc team. This problem appeared because of the fix for "Bug#27877 incorrect german order in utf8_general_ci" previously appeared in 5.1.24-rc. Please make sure that it's documented that: those using utf8_general_ci or ucs2_general_ci (especially those using German letter SHARP S) must rebuild indexes when upgrading to 5.1.24-rc and higher, or dump data and restore. Setting status to documenting, as no code changes required. Please modify "Assigned to" and "Lead" to doc team if necessary. Thanks!
[17 Jul 2008 0:48]
Paul DuBois
I've amended the changelog entry for Bug#27877 and added notes about the incompatibility to the upgrade sections in the 5.1 and 6.0 manuals.
[19 Jul 2008 16:16]
Michael Widenius
This is something that mysql_upgrade should detect. This needs to be fixed so that one does not have to dump and restore all tables when doing and upgrade to 5.1 This is a critical bug that should preferably be fixed before we can make MySQL 5.1 GA
[11 Nov 2008 18:34]
Peter Gulutzan
The hangs and crashes are gone. But searches still fail if the column contains values with sharp s. How to repeat: On a new database, with MySQL 5.0.66, say: USE test SET NAMES utf8; CREATE TABLE `t` ( `int_column` int(11) DEFAULT NULL, `char_column` char(10) CHARACTER SET utf8 DEFAULT NULL, KEY `i` (`char_column`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `t` VALUES (0,'araaxeaaaa'), (1,'waZaaTaaaß'),(2,'UaDaadaßa'),(3,'kßaexaqaa'),(4,'Tamdaaalaa'),(5,'aaganDaraa'),(6,'WTaaYPaaIj'),(7,'aßaOaazaa'), (8,'aaNßzCßa'),(9,'aGSLaaUaaa'),(10,'eUyaxaaßa'),(11,'oazaaaiaNa'),(12,'awafRgaaYm'),(13,'aaaRaXaaß'),(14,'aaßnaagal'), (15,'aißaaxaß'),(16,'gdBaLaUaaE'),(17,'CßawDmYua'),(18,'ßaßaaaXa'),(19,'OYOaaaJßa'),(20,'iaaßLhaaN'),(21,'ßnaßHoWf'), (22,'yavJataaaj'),(23,'atacßIvuf'),(24,'aqVßIXßO'),(25,'qIaaaaaßa'),(26,'aaaaxaxSaa'),(27,'BaabeSrApn'),(28,'YqaaaUraWa'), (29,'yßpaaJaIa'),(30,'TaaaaWkaaR'),(31,'GoWhaOaAaa'),(32,'pkJaaJaNTa'),(33,'aavapaaaaO'),(34,'Guyßaaaay'); Switch to MySQL 5.1.31-debug, pulled yesterday. Start the server. Of course there will be error messages, ignore them. On another shell say 'mysql_upgrade'. Ignore the message " test.t error : Table upgrade required. Please do "REPAIR TABLE `t`" to fix it! " (This is an InnoDB table, REPAIR TABLE won't work.) Stop ther server with mysqladmin --user=root shutdown. Start the server again. Run these statements on a mysql client: USE test SET NAMES utf8; SELECT * FROM t WHERE char_column ='aaßnaagal'; SELECT * FROM t WHERE char_column ='aßaOaazaa'; SELECT * FROM t WHERE char_column ='atacßIvuf'; SELECT * FROM t WHERE char_column ='awafRgaaYm'; SELECT * FROM t WHERE char_column ='ßaßaaaXa'; SELECT * FROM t WHERE char_column ='ßnaßHoWf'; All of these searches fail for me. But if I drop the index and create it again, they all work.
[11 Nov 2008 19:13]
Peter Gulutzan
The lines were too long in my previous comment, causing wrapping. I repeat the comment with shorter lines. The hangs and crashes are gone. But searches still fail if the column contains values with sharp s. How to repeat: On a new database, with MySQL 5.0.66, say: USE test SET NAMES utf8; CREATE TABLE `t` ( `int_column` int(11) DEFAULT NULL, `char_column` char(10) CHARACTER SET utf8 DEFAULT NULL, KEY `i` (`char_column`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `t` VALUES (0,'araaxeaaaa'), (1,'waZaaTaaaß'), (2,'UaDaadaßa'), (3,'kßaexaqaa'), (4,'Tamdaaalaa'), (5,'aaganDaraa'), (6,'WTaaYPaaIj'), (7,'aßaOaazaa'), (8,'aaNßzCßa'), (9,'aGSLaaUaaa'), (10,'eUyaxaaßa'), (11,'oazaaaiaNa'), (12,'awafRgaaYm'), (13,'aaaRaXaaß'), (14,'aaßnaagal'), (15,'aißaaxaß'), (16,'gdBaLaUaaE'), (17,'CßawDmYua'), (18,'ßaßaaaXa'), (19,'OYOaaaJßa'), (20,'iaaßLhaaN'), (21,'ßnaßHoWf'), (22,'yavJataaaj'), (23,'atacßIvuf'), (24,'aqVßIXßO'), (25,'qIaaaaaßa'), (26,'aaaaxaxSaa'), (27,'BaabeSrApn'), (28,'YqaaaUraWa'), (29,'yßpaaJaIa'), (30,'TaaaaWkaaR'), (31,'GoWhaOaAaa'), (32,'pkJaaJaNTa'), (33,'aavapaaaaO'), (34,'Guyßaaaay'); Switch to MySQL 5.1.31-debug, pulled yesterday. Start the server. Of course there will be error messages, ignore them. On another shell say 'mysql_upgrade'. Ignore the message " test.t error : Table upgrade required. Please do "REPAIR TABLE `t`" to fix it! " (This is an InnoDB table, REPAIR TABLE won't work.) Stop the server with mysqladmin --user=root shutdown. Start the server again. Run these statements on a mysql client: USE test SET NAMES utf8; SELECT * FROM t WHERE char_column ='aaßnaagal'; SELECT * FROM t WHERE char_column ='aßaOaazaa'; SELECT * FROM t WHERE char_column ='atacßIvuf'; SELECT * FROM t WHERE char_column ='awafRgaaYm'; SELECT * FROM t WHERE char_column ='ßaßaaaXa'; SELECT * FROM t WHERE char_column ='ßnaßHoWf'; All of these searches fail for me. But if I drop the index and create it again, they all work.
[24 Nov 2008 15:10]
Alexander Barkov
This problem was fixed under terms of Bug#40053. Closing as duplicate.
[5 Jan 2010 6:52]
Roel Van de Paar
Also see bug #42563 and bug #47205
[5 Jan 2010 7:01]
Roel Van de Paar
Re-opening (This is not a duplicate of #40053 as issue appears on later versions.)
[5 Jan 2010 15:26]
Peter Gulutzan
Setting back to 'duplicate'. This bug report has a long history. There's no point going through it all again, because the changes can no longer be undone. All bug reports involving Sharp S and upgrade failures should be declared 'duplicate', 'not a bug', or simply 'closed'.
Description: In MySQL 5.0 I create a table with a utf8 column. I use the default utf8_general_ci collation. I insert letters from 'p' to 'z', and SHARP S ('ß'). I create an index. I upgrade to 5.1. I search for anything greater than 'ss'. Either MySQL returns zero rows, or it hangs. See also Bug#27877. How to repeat: Switch to 5.0. (I used 5.0.58-debug; probably other 5.0 variants are the same.) Start mysqld. With mysql client: use test create table t (s1 varchar(5) character set utf8); create index i on t (s1); insert into t (s1) values ('p'),('q'),('r'),('s'),('t'); insert into t (s1) values ('u'),('v'),('w'),('x'),('y'),('z'); insert into t (s1) values (0xc39f); /* sharp s */ quit Shutdown mysqld. Switch to 5.1. (I used 5.1.26-rc-debug; probably other 5.1 variants are not the same.) Restart mysqld. Run mysql_upgrade. (Probably it doesn't matter if you don't run mysql_upgrade.) With mysql client: use test select * from t where s2 > 'ss'; create table t3 as select s2 from t; At this point MySQL should hang. Even if it doesn't, the row count will be wrong. If there are more rows, MySQL will not hang and row count will be wrong. If there is no index, MySQL will not hang and row count will be correct. It would be interesting to know whether the same problem occurs with 5.1.23, which was before the fix to Bug#27877.