MySQL, UTF8 and collations
Hey, We're upgrading MySQLs from 5.0 to 5.5, and running into the predictable utf8_general_ci bu^Wwe-meant-to-do-that with german ß and similar characters. The server swallowed the existing datafiles (thank god for that) so we're up and running, but check table does whine about needing a full rebuild, which is clearly that issue. Now, I'm aware that this should be fixed by using the utf8_unicode_ci collation; but somehow it isn't... The its-not-a-bug-but-heres-the-fix collation utf8_general_mysql500_ci does work, but honestly I feel that that's kind of dirty. Any ideas why utf8_unicode_ci doesn't work as expected? The new server version is 5.5.24-1~dotdeb.1-log. Thx, Johan mysql set names UTF8; Query OK, 0 rows affected (0.00 sec) mysql show create table search_index; +--+-+ | Table | Create Table | +--+-+ | search_index | CREATE TABLE `search_index` ( `word` varchar(50) NOT NULL DEFAULT '', `sid` int(10) unsigned NOT NULL DEFAULT '0', `type` varchar(16) DEFAULT NULL, `score` float DEFAULT NULL, UNIQUE KEY `word_sid_type` (`word`,`sid`,`type`), KEY `sid_type` (`sid`,`type`), KEY `word` (`word`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +--+-+ 1 row in set (0.00 sec) mysql show table status like search_index; +--++-++-++-+-+--+---++-+-++-+--++-+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +--++-++-++-+-+--+---++-+-++-+--++-+ | search_index | InnoDB | 10 | Compact | 6893296 | 58 | 405536768 | 0 | 725172224 | 255852544 | NULL | 2012-08-27 14:53:20 | NULL | NULL | utf8_general_ci | NULL | | | +--++-++-++-+-+--+---++-+-++-+--++-+ 1 row in set (0.07 sec) mysql CREATE TABLE `search_johan` ( `word` varchar(50) NOT NULL DEFAULT '', `count` float DEFAULT NULL, PRIMARY KEY (`word`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 collate=utf8_unicode_ci; Query OK, 0 rows affected (0.00 sec) mysql insert into search_johan select * from search_total; ERROR 1062 (23000): Duplicate entry 'cœur' for key 'PRIMARY' -- Linux Bier Wanderung 2012, now also available in Belgium! August, 12 to 19, Diksmuide, Belgium - http://lbw2012.tuxera.be
Re: Having trouble with SQL query
Hi Nitin Thanks - I tried that and got 0 rows... I have spent more time on describing my problem -- see below hopefully this will make the issue more clear... Rich I have a MySQL database with a menu table and a product table. - The products are linked to the menus in a one-to-many relationship i.e. each product can be linked to more than one menu - The menus are nested in a parent child relationship - Some menus may contain no products The desire is that when a user clicks on a menu entry then all products linked to that menu - there may be none - will get displayed as well as all products linked to any child menus of the menu clicked on ... So say we have a menu like this:- Motor cycles - Sports bikes - Italian - Ducati Motor cycles - Sports bikes - Italian - Moto Guzzi Motor cycles - Sports bikes - British - Triumph Motor cycles - Tourers - British - Triumph Motor cycles - Tourers - American - Harley-Davidson . etc etc Clicking on 'Sports bikes' will show all products linked to 'Sports bikes' itself as well as all products linked to ALL menus below 'Sports bikes', clicking on 'Harley-Davidson' will just show products for that entry only. Below are 'describe table' for the 2 main tables in question NB there is a 3rd table that holds product descriptions which I won't show as I don't think it is relevant here:- CREATE TABLE `menu` ( `menuid` int(11) unsigned NOT NULL AUTO_INCREMENT, `parent_menuid` int(11) unsigned NOT NULL, `name` varchar(255) NOT NULL, PRIMARY KEY (`menuid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 CREATE TABLE `menu_product` ( `menuid` int(11) unsigned NOT NULL, `productid` int(11) unsigned NOT NULL, PRIMARY KEY (`menuid`,`productid`), KEY `prodidx` (`productid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 For the sake of this question I will simplify it and say there is only 2 levels of nesting i.e. root level and 1 level below that... this is the query I came up with:- SELECT DISTINCT p.productid, pd.name FROM menu_product as p INNER JOIN menu as m ON (m.menuid = p.menuid) INNER JOIN product_description as pd ON (pd.productid = p.productid) LEFT JOIN menu as m2 ON (m2.parent_menuid = m.menuid) # User selected menu may itself be a child menu... WHERE (m.name = 'name obtained from user's click' OR p.productid IN (SELECT p2.productid from menu_product AS p2 WHERE p2.menuid = m2.menuid) Anyway when I run the above query it returns far too many entries from menus that are totally unrelated... I have been staring too hard at this for too long - I am sure it will be a forehead slapper! I hope I have explained this sufficiently and I TYIA for any guidance Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Having trouble with SQL query
Hello Rich, On 8/27/2012 12:19 PM, rich gray wrote: Hi Nitin Thanks - I tried that and got 0 rows... I have spent more time on describing my problem -- see below hopefully this will make the issue more clear... Rich ... snip ... There are many resources out there that can tell you how to build this type of data structure. However, my favorite and the one I think is most accessible is this: http://www.sitepoint.com/hierarchical-data-database/ As you can see, his menu also has branches (fruit) and leaves (cherry, banana) just as your equipment menu does. I think this will be an excellent starting point for you to use to build the menu tree. From there, it should be easy to extend this to link your leaf nodes to any information records you may want. Let us know if we can give any additional insights or suggestions. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: MySQL, UTF8 and collations
There is a section on German Sharp-s in http://mysql.rjweb.org/doc.php/charcoll I agree with dirty. As I understand it, about 5.1.24, they said Oops, sharp-s is collated wrong, let's fix it. The fix broke things, but they stuck by the correct sorting. Eventually they said Oh, let's add another, compatible, collation. They were caught between a rock and a hard place. -Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.be] Sent: Monday, August 27, 2012 7:43 AM To: mysql Subject: MySQL, UTF8 and collations Hey, We're upgrading MySQLs from 5.0 to 5.5, and running into the predictable utf8_general_ci bu^Wwe-meant-to-do-that with german ß and similar characters. The server swallowed the existing datafiles (thank god for that) so we're up and running, but check table does whine about needing a full rebuild, which is clearly that issue. Now, I'm aware that this should be fixed by using the utf8_unicode_ci collation; but somehow it isn't... The its-not-a-bug-but-heres-the-fix collation utf8_general_mysql500_ci does work, but honestly I feel that that's kind of dirty. Any ideas why utf8_unicode_ci doesn't work as expected? The new server version is 5.5.24-1~dotdeb.1-log. Thx, Johan mysql set names UTF8; Query OK, 0 rows affected (0.00 sec) mysql show create table search_index; +--+--- --- --- --- -+ | Table | Create Table | +--+--- --- --- --- -+ | search_index | CREATE TABLE `search_index` ( `word` varchar(50) NOT NULL DEFAULT '', `sid` int(10) unsigned NOT NULL DEFAULT '0', `type` varchar(16) DEFAULT NULL, `score` float DEFAULT NULL, UNIQUE KEY `word_sid_type` (`word`,`sid`,`type`), KEY `sid_type` (`sid`,`type`), KEY `word` (`word`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +--+--- --- --- --- -+ 1 row in set (0.00 sec) mysql show table status like search_index; +--++-++-+- ---+-+-+--+---+ +-+-++- +--++-+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +--++-++-+- ---+-+-+--+---+ +-+-++- +--++-+ | search_index | InnoDB | 10 | Compact | 6893296 | 58 | 405536768 | 0 | 725172224 | 255852544 | NULL | 2012-08-27 14:53:20 | NULL | NULL | utf8_general_ci | NULL | | | +--++-++-+- ---+-+-+--+---+ +-+-++- +--++-+ 1 row in set (0.07 sec) mysql CREATE TABLE `search_johan` ( `word` varchar(50) NOT NULL DEFAULT '', `count` float DEFAULT NULL, PRIMARY KEY (`word`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 collate=utf8_unicode_ci; Query OK, 0 rows affected (0.00 sec) mysql insert into search_johan select * from search_total; ERROR 1062 (23000): Duplicate entry 'cœur' for key 'PRIMARY' -- Linux Bier Wanderung 2012, now also available in Belgium! August, 12 to 19, Diksmuide, Belgium - http://lbw2012.tuxera.be
