June 19, 2013

Post: Converting Character Sets

March 17, 2009 By 22 Comments

… latin1 have begun to migrate their databases to utf8.  Googling for “mysql convert charset to utf8” results in a plethora of sites, each…` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; Take the following table as an example why this approach will not work: mysql> CREATE…, 0 rows affected (0.02 sec) mysql> ALTER TABLE `t1` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; Query OK, 0 rows affected…

Filed Under: Insight for Developers Tagged With: , ,

Post: MySQL Upgrade Webinar Questions Followup

June 28, 2012 By 7 Comments

… might be making MySQL Replication to work. You might need to inject MySQL 5.0 in between so it will convert binary log… to MySQL 5.5 do we really need all tables changed to Innodb and character set set to utf8? No. MySQL 5.5… to utf8 – this is not needed as MySQL 5.5 continues to support various character sets same as previous MySQL versions. Moreover utf8 character…

Filed Under: Insight for DBAs, MySQL

Post: Fixing column encoding mess in MySQL

December 18, 2007 By 32 Comments

… needed to be fixed. Simply using CONVERT(column USING xxx) did not work because MySQL treated the source data as if it… then do charset conversion: UPDATE table SET column=CONVERT(CONVERT(CONVERT(column USING binary) USING utf8) USING cp1251) WHERE id=123; This can be… automatically, and the 3rd explicit CONVERT can be omitted. UPDATE table SET column=CONVERT(CONVERT(column USING binary) USING utf8) WHERE id=123; The…

Filed Under: Insight for Developers Tagged With:

Post: On Character Sets and Disappearing Tables

June 21, 2012 By 1 Comment

… UTF8 in this database at all, and that we want to achieve the best performance possible, so we’re going to convert… way we were expecting. Whatever the reason, we decide to convert these columns to be ASCII.  We might assume that since… I think there’s something of an inconsistency present when MySQL won’t allow us to change an FK-referenced column…

Filed Under: Insight for DBAs, MySQL

Post: Percona Server 5.1.58-12.9

August 17, 2011 By 2 Comments

…_clean). Bugs Fixed: #802825 / #61341 in MySQL (Yasufumi Kinoshita). Compatibility Collations Two new collations, utf8_general50_ci and ucs2_general50_ci, have… by converting the affected tables or columns to the collations introduced: utf8_general_ci to utf8_general50_ci, and ucs2_general_ci to utf8…

Filed Under: MySQL, Percona Software

Post: Percona Server 5.5.14-20.5

August 17, 2011 By 4 Comments

… Two new collations, utf8_general50_ci and ucs2_general50_ci, have been to improve compatibility for those upgrading from MySQL 5.0… a MySQL bug (#27877) introduced an incompatible change in collations in MySQL 5.1.24. If the following collations were used: utf8_general… to Percona Server by converting the affected tables or columns to the collations introduced: utf8_general_ci to utf8_general50_ci, and ucs2…

Filed Under: MySQL

Post: InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

March 4, 2013 By 7 Comments

… name, address, and the like. We are using MySQL 5.5.30 and MySQL 5.6.10 with no configuration tuning other…=InnoDB DEFAULT CHARSET=utf8 But, when we try to use this table, here’s what comes back: mysql: SET GLOBAL innodb_ft… be possible to take the MyISAM full-text parser code, convert it to a plugin, and use it for InnoDB FT…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: JOIN Performance & Charsets

October 21, 2008 By 2 Comments

… index 2 rows in set, 1 warning (0.00 sec) mysql> SHOW WARNINGS\G *************************** 1. row *************************** Level: Note Code: 1003 Message: select….33 seconds to execute with t1 as utf8 and t2 as latin1. Converting both tables to utf8 resulted in an average execution time…

Filed Under: Uncategorized Tagged With: , , ,

Comment: Fixing column encoding mess in MySQL

September 1, 2008 By 32 Comments

… MySQL performs a “utf8 -> latin1″ conversion. In order to fix the encoding I need to treat this “latin1″ data as binary, then convert it to utf8. And so here is the statement I need: UPDATE table SET col = convert(CONVERT(CONVERT(col USING latin1) USING binary) using utf8…

Filed Under: Insight for Developers Tagged With:

Comment: Fixing column encoding mess in MySQL

September 1, 2008 By 32 Comments

… to import the data into mysql with “\.” syntax. The user forgot to run “SET NAMES utf8“, and MySQL sees all data as latin1. Now, the script can use “set names latin1″ to get back utf8 data. However, “CONVERT(CONVERT(offending_col USING binary) USING utf8)” doesn’t work – the same wrong…

Filed Under: Insight for Developers Tagged With:
« Older Posts