Re: Best way to convert character set from "latin1 to utf8" for existing database?
Export schema Export data Change exported schema to utf8 Import schema into new db Import exported data into new db Sent via BlackBerry from T-Mobile -Original Message- From: Darryle Steplight Date: Sun, 7 Jun 2009 22:52:22 To: Uma Bhat Cc: ewen fortune; Subject: Re: Best way to convert character set from "latin1 to utf8" for existing database? Uma, I apologize in advance if this is redundant ,because I did not click on any of Ewen's link. Nonetheless, this is the approach I would take. start your mysql server with different --character-set-server and ---collation-server options Type SHOW COLLATION; in your mysql shell to determine which collations are available for each character set If you want to change the character set while running MySql, that may also change the sort order. you must run myisamchk -r -q -set-collation=collation_name on all MyISAM tables or your indexes may not be ordered correctly There are numerous collations for the uft8 charset so I'm assuming mysql is selecting a collation that you don't want to use. Additionally, if you did not run myisamchk on any of your MyISAM tables that may be why you are getting unexpected results. I hope this helps. On Sun, Jun 7, 2009 at 10:29 PM, Uma Bhat wrote: > Thank was great piece of info Ewen, Thanks! > > However this approach works for new data. But the existing data in the > database does not show us the Japanese characters from application side. > > Appreciate responses who 'actually' got to work on this conversion. > > Thanks! > Uma > > > On 6/1/09, ewen fortune wrote: >> >> Uma, >> >> On Mon, Jun 1, 2009 at 8:41 AM, Uma Bhat wrote: >> > Hi All, >> > >> > I have read many blogs suggesting some examples for this. >> > But suggestions from you guys who have ACTUALLY worked on such a scenario >> > would help me out the best. >> > >> > >> > Current Database has: >> > DEFAULT CHARACTER SET - latin1 >> > DEFAULT COLLATION : latin1_swedish_ci >> > >> > We need to convert this to >> > DEFAULT CHARACTER SET - utf8 >> > DEFAULT COLLATION : utf8_general_ci >> > >> > >> > Note that this has to be done on a database that has *existing data* in >> it . >> > >> > Hence just by doing a: >> > >> > ALTER DATABASE CHARSET=utf8; >> > >> > would result in unexpected behaviour of the data. >> >> Ryan Lowe blogged about this. >> >> http://www.mysqlperformanceblog.com/2009/03/17/converting-character-sets/ >> >> He wrote a tool for it (linked from post) >> >> http://www.pablowe.net/convert_charset >> >> And Schlomi Noach commented that openark also has a tool. >> >> http://code.openark.org/forge/openark-kit >> >> Cheers, >> >> Ewen >> >> > >> >> > Thanks! >> > Uma >> > >> > -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=chaim.rie...@gmail.com
Re: Best way to convert character set from "latin1 to utf8" for existing database?
Uma, I apologize in advance if this is redundant ,because I did not click on any of Ewen's link. Nonetheless, this is the approach I would take. start your mysql server with different --character-set-server and ---collation-server options Type SHOW COLLATION; in your mysql shell to determine which collations are available for each character set If you want to change the character set while running MySql, that may also change the sort order. you must run myisamchk -r -q -set-collation=collation_name on all MyISAM tables or your indexes may not be ordered correctly There are numerous collations for the uft8 charset so I'm assuming mysql is selecting a collation that you don't want to use. Additionally, if you did not run myisamchk on any of your MyISAM tables that may be why you are getting unexpected results. I hope this helps. On Sun, Jun 7, 2009 at 10:29 PM, Uma Bhat wrote: > Thank was great piece of info Ewen, Thanks! > > However this approach works for new data. But the existing data in the > database does not show us the Japanese characters from application side. > > Appreciate responses who 'actually' got to work on this conversion. > > Thanks! > Uma > > > On 6/1/09, ewen fortune wrote: >> >> Uma, >> >> On Mon, Jun 1, 2009 at 8:41 AM, Uma Bhat wrote: >> > Hi All, >> > >> > I have read many blogs suggesting some examples for this. >> > But suggestions from you guys who have ACTUALLY worked on such a scenario >> > would help me out the best. >> > >> > >> > Current Database has: >> > DEFAULT CHARACTER SET - latin1 >> > DEFAULT COLLATION : latin1_swedish_ci >> > >> > We need to convert this to >> > DEFAULT CHARACTER SET - utf8 >> > DEFAULT COLLATION : utf8_general_ci >> > >> > >> > Note that this has to be done on a database that has *existing data* in >> it . >> > >> > Hence just by doing a: >> > >> > ALTER DATABASE CHARSET=utf8; >> > >> > would result in unexpected behaviour of the data. >> >> Ryan Lowe blogged about this. >> >> http://www.mysqlperformanceblog.com/2009/03/17/converting-character-sets/ >> >> He wrote a tool for it (linked from post) >> >> http://www.pablowe.net/convert_charset >> >> And Schlomi Noach commented that openark also has a tool. >> >> http://code.openark.org/forge/openark-kit >> >> Cheers, >> >> Ewen >> >> > >> >> > Thanks! >> > Uma >> > >> > -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Best way to convert character set from "latin1 to utf8" for existing database?
Thank was great piece of info Ewen, Thanks! However this approach works for new data. But the existing data in the database does not show us the Japanese characters from application side. Appreciate responses who 'actually' got to work on this conversion. Thanks! Uma On 6/1/09, ewen fortune wrote: > > Uma, > > On Mon, Jun 1, 2009 at 8:41 AM, Uma Bhat wrote: > > Hi All, > > > > I have read many blogs suggesting some examples for this. > > But suggestions from you guys who have ACTUALLY worked on such a scenario > > would help me out the best. > > > > > > Current Database has: > > DEFAULT CHARACTER SET - latin1 > > DEFAULT COLLATION : latin1_swedish_ci > > > > We need to convert this to > > DEFAULT CHARACTER SET - utf8 > > DEFAULT COLLATION : utf8_general_ci > > > > > > Note that this has to be done on a database that has *existing data* in > it . > > > > Hence just by doing a: > > > > ALTER DATABASE CHARSET=utf8; > > > > would result in unexpected behaviour of the data. > > Ryan Lowe blogged about this. > > http://www.mysqlperformanceblog.com/2009/03/17/converting-character-sets/ > > He wrote a tool for it (linked from post) > > http://www.pablowe.net/convert_charset > > And Schlomi Noach commented that openark also has a tool. > > http://code.openark.org/forge/openark-kit > > Cheers, > > Ewen > > > > > > Thanks! > > Uma > > >
Re: Best way to convert character set from "latin1 to utf8" for existing database?
Uma, On Mon, Jun 1, 2009 at 8:41 AM, Uma Bhat wrote: > Hi All, > > I have read many blogs suggesting some examples for this. > But suggestions from you guys who have ACTUALLY worked on such a scenario > would help me out the best. > > > Current Database has: > DEFAULT CHARACTER SET - latin1 > DEFAULT COLLATION : latin1_swedish_ci > > We need to convert this to > DEFAULT CHARACTER SET - utf8 > DEFAULT COLLATION : utf8_general_ci > > > Note that this has to be done on a database that has *existing data* in it . > > Hence just by doing a: > > ALTER DATABASE CHARSET=utf8; > > would result in unexpected behaviour of the data. Ryan Lowe blogged about this. http://www.mysqlperformanceblog.com/2009/03/17/converting-character-sets/ He wrote a tool for it (linked from post) http://www.pablowe.net/convert_charset And Schlomi Noach commented that openark also has a tool. http://code.openark.org/forge/openark-kit Cheers, Ewen > > Thanks! > Uma > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Best way to convert character set from "latin1 to utf8" for existing database?
Hi All, I have read many blogs suggesting some examples for this. But suggestions from you guys who have ACTUALLY worked on such a scenario would help me out the best. Current Database has: DEFAULT CHARACTER SET - latin1 DEFAULT COLLATION : latin1_swedish_ci We need to convert this to DEFAULT CHARACTER SET - utf8 DEFAULT COLLATION : utf8_general_ci Note that this has to be done on a database that has *existing data* in it . Hence just by doing a: ALTER DATABASE CHARSET=utf8; would result in unexpected behaviour of the data. Thanks! Uma