Fwd: character set problem
Begin forwarded message: From: Napster Cao tx...@hotmail.com Subject: character set problem Date: June 11, 2013 11:04:18 PM GMT+08:00 To: mysql@lists.mysql.com Hi Guys, I installed a new CentOS server (6.4 x86_64), and when I try to log into phpmyadmin, there's an ERROR: Can't initialize character set utf-8 (path: /usr/local/mysql/share/charsets/) BTW: Everytime I logged into system, I got: -bash: warning: setlocale: LC_CTYPE: cannot change locale (UTF-8): No such file or directory and I cannot find locale-gen on my system, the latest version of glibc is installed. [admin@zxue /]# rpm -qa | grep glibc glibc-devel-2.12-1.107.el6.x86_64 glibc-2.12-1.107.el6.x86_64 glibc-headers-2.12-1.107.el6.x86_64 glibc-common-2.12-1.107.el6.x86_64 and here's the output of locale command: [admin@zxue /]# locale LANG=en_US LC_CTYPE=en_US.utf-8 LC_NUMERIC=en_US.utf-8 LC_TIME=en_US.utf-8 LC_COLLATE=en_US.utf-8 LC_MONETARY=en_US.utf-8 LC_MESSAGES=en_US.utf-8 LC_PAPER=en_US.utf-8 LC_NAME=en_US.utf-8 LC_ADDRESS=en_US.utf-8 LC_TELEPHONE=en_US.utf-8 LC_MEASUREMENT=en_US.utf-8 LC_IDENTIFICATION=en_US.utf-8 LC_ALL=en_US.utf-8 How to resolve those two problems?(or maybe they are the same problem?) Thanks in advance!
Re: character set problem
independent how often you re-post it will not become magically a MySQL problem if you have messed up your OS environment Am 12.06.2013 15:27, schrieb Napster Cao: BTW: Everytime I logged into system, I got: -bash: warning: setlocale: LC_CTYPE: cannot change locale (UTF-8): No such file or directory and I cannot find locale-gen on my system, the latest version of glibc is installed signature.asc Description: OpenPGP digital signature
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 ewen.fort...@gmail.com wrote: Uma, On Mon, Jun 1, 2009 at 8:41 AM, Uma Bhat bhat@gmail.com 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 dbname 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, 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 Bhatbhat@gmail.com 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 ewen.fort...@gmail.com wrote: Uma, On Mon, Jun 1, 2009 at 8:41 AM, Uma Bhat bhat@gmail.com 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 dbname 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?
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 dstepli...@gmail.com Date: Sun, 7 Jun 2009 22:52:22 To: Uma Bhatbhat@gmail.com Cc: ewen fortuneewen.fort...@gmail.com; mysql@lists.mysql.com 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 Bhatbhat@gmail.com 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 ewen.fort...@gmail.com wrote: Uma, On Mon, Jun 1, 2009 at 8:41 AM, Uma Bhat bhat@gmail.com 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 dbname 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
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 dbname CHARSET=utf8; would result in unexpected behaviour of the data. 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 bhat@gmail.com 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 dbname 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
Speeding up character set conversion
Hi, I just tried this on a local copy of the table with ~500.000 rows: execute 'ALTER TABLE users MODIFY email VARCHAR(255) CHARACTER SET latin1 NOT NULL' The old character set was UTF8. We're doing this to make the index smaller. This took around 45 minutes to complete. In production, we have about 1.000.000 rows. While the production servers are dedicated DB servers in comparison to my MacBook, I'm still concerned that this is going to literally take hours. How can this be speeded up if at all? Dropping the index first and then recreate? Br, Morten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Speeding up character set conversion
Hi Morten, You might want to read this post on MySQL Performance Blog that was posted a few days ago: http://www.mysqlperformanceblog.com/2009/03/17/converting-character-sets/ Have a nice day, - Martijn On Wed, Mar 18, 2009 at 11:50, Morten my.li...@mac.com wrote: Hi, I just tried this on a local copy of the table with ~500.000 rows: execute 'ALTER TABLE users MODIFY email VARCHAR(255) CHARACTER SET latin1 NOT NULL' The old character set was UTF8. We're doing this to make the index smaller. This took around 45 minutes to complete. In production, we have about 1.000.000 rows. While the production servers are dedicated DB servers in comparison to my MacBook, I'm still concerned that this is going to literally take hours. How can this be speeded up if at all? Dropping the index first and then recreate? Br, Morten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mart...@crystal-labs.nl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Anybody can tell me why our data couldn't be saved incorrect character set in the database?
When the memory can not load our application,the error occurs. 080630 17:32:38 [ERROR] /usr/local/mysql/bin/mysqld: Out of memory (Needed 1651540 bytes) And there are some data displayed ? in my database. For example. mysql select * from passport where name like '%??%' limit 1\G *** 1. row *** id: 399502 name: ?? email: [EMAIL PROTECTED] password: 9e0fe63bb59388909d31e85d2b111216 Our front language is .net(C#) and the database is MySQL 5.0.45. Any help is appreciated. -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Anybody can tell me why our data couldn't be saved incorrect character set in the database?
Is this data comming from application or loaded using LOAD DATA command. Can u please check the character set of the db and columns. On 6/30/08, Moon's Father [EMAIL PROTECTED] wrote: When the memory can not load our application,the error occurs. 080630 17:32:38 [ERROR] /usr/local/mysql/bin/mysqld: Out of memory (Needed 1651540 bytes) And there are some data displayed ? in my database. For example. mysql select * from passport where name like '%??%' limit 1\G *** 1. row *** id: 399502 name: ?? email: [EMAIL PROTECTED] password: 9e0fe63bb59388909d31e85d2b111216 Our front language is .net(C#) and the database is MySQL 5.0.45. Any help is appreciated. -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Anybody can tell me why our data couldn't be saved incorrect character set in the database?
Yeah,they came from .net application and not using load data infile statement. The character set of my db and columns is both utf8; Some wield thing is that the data is normal yesterday ,but they displayed today after the error happened. On Mon, Jun 30, 2008 at 7:01 PM, Ananda Kumar [EMAIL PROTECTED] wrote: Is this data comming from application or loaded using LOAD DATA command. Can u please check the character set of the db and columns. On 6/30/08, Moon's Father [EMAIL PROTECTED] wrote: When the memory can not load our application,the error occurs. 080630 17:32:38 [ERROR] /usr/local/mysql/bin/mysqld: Out of memory (Needed 1651540 bytes) And there are some data displayed ? in my database. For example. mysql select * from passport where name like '%??%' limit 1\G *** 1. row *** id: 399502 name: ?? email: [EMAIL PROTECTED] password: 9e0fe63bb59388909d31e85d2b111216 Our front language is .net(C#) and the database is MySQL 5.0.45. Any help is appreciated. -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Anybody can tell me why our data couldn't be saved incorrect character set in the database?
try to optimize the table and see if the error goes away On 7/1/08, Moon's Father [EMAIL PROTECTED] wrote: Yeah,they came from .net application and not using load data infile statement. The character set of my db and columns is both utf8; Some wield thing is that the data is normal yesterday ,but they displayed today after the error happened. On Mon, Jun 30, 2008 at 7:01 PM, Ananda Kumar [EMAIL PROTECTED] wrote: Is this data comming from application or loaded using LOAD DATA command. Can u please check the character set of the db and columns. On 6/30/08, Moon's Father [EMAIL PROTECTED] wrote: When the memory can not load our application,the error occurs. 080630 17:32:38 [ERROR] /usr/local/mysql/bin/mysqld: Out of memory (Needed 1651540 bytes) And there are some data displayed ? in my database. For example. mysql select * from passport where name like '%??%' limit 1\G *** 1. row *** id: 399502 name: ?? email: [EMAIL PROTECTED] password: 9e0fe63bb59388909d31e85d2b111216 Our front language is .net(C#) and the database is MySQL 5.0.45. Any help is appreciated. -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: mysqldump character set
If you create all your databases and tables with utf8,then every thing is fine. On Mon, Apr 28, 2008 at 1:43 AM, Velen [EMAIL PROTECTED] wrote: Hi, I'm storing some ascii codes in a table. When I do a dump using mysqldump from the server and then restoring it on another station, the ascii codes in the table has changed. But if i'm accessing the table from another station the code is good. Even if I insert it from a station, it goes fine in the server. Anyone knows why it changes when using mysqldump? How can I prevent this problem to happen in the future? Regards, Velen -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Problem with character set and connection collation
Hi Sulo, Open the file you are importing as it is probably that it contains references of another character set in the table creation. If so replace that character set for '' or 'utf8'. I hope it helps, Leandro sulochan acharya wrote: Hello all, here is my problem: I am trying to set mysql to unicode character, so that i can get my dictionary application to look at words in Nepali. here is my setting: mysql charset: utf-8 unicode when i make a new database: mysql connection collation is utf8-general-ci and my new database collation is also utf-general-ci i import tables into this database using mysql command : source /path to file when i browse the tables in this database i dont see nepali world instead it seems like mysql cant read the characters; please help!! best, sulo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with character set and connection collation
A week or so ago I explored this in depth because I was having the same problems. (It was affecting an English file that had some Windows (CP-1252) characters that didn't directly map to UTF-8. That message is at http://lists.mysql.com/mysql/212392. I didn't mention it in my posting, but latin1 is CP-1252. I couldn't get that to work, either. I don't know how it would apply directly to your situation, but I think you are running into a similar kind of problem. I also can't guarantee that my conclusions were entirely correct. Perhaps someone else can add to this. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: sulochan acharya [mailto:[EMAIL PROTECTED] Sent: Sunday, April 27, 2008 6:01 AM To: mysql@lists.mysql.com Subject: Problem with character set and connection collation Hello all, here is my problem: I am trying to set mysql to unicode character, so that i can get my dictionary application to look at words in Nepali. here is my setting: mysql charset: utf-8 unicode when i make a new database: mysql connection collation is utf8-general-ci and my new database collation is also utf-general-ci i import tables into this database using mysql command : source /path to file when i browse the tables in this database i dont see nepali world instead it seems like mysql cant read the characters; please help!! best, sulo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with character set and connection collation
On Mon, 28 Apr 2008, Jerry Schwartz [EMAIL PROTECTED] wrote: A week or so ago I explored this in depth because I was having the same problems. (It was affecting an English file that had some Windows (CP-1252) characters that didn't directly map to UTF-8. That message is at http://lists.mysql.com/mysql/212392. I didn't mention it in my posting, but latin1 is CP-1252. I couldn't get that to work, either. Latin-1 is not Microsoft Windows codepage 1252: Latin-1 has control characters in the 0x80 - 0x9F block of code points, but 1252 replaces some of those code points with printable characters. -- Tim McDaniel, [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with character set and connection collation
Well, if latin1 is not CP-1252, then that explains why it didn't fix my problem; but here's what 5.0.45-community-nt says: mysql show character set; +--+-+-++ | Charset | Description | Default collation | Maxlen | +--+-+-++ | big5 | Big5 Traditional Chinese| big5_chinese_ci | 2 | | dec8 | DEC West European | dec8_swedish_ci | 1 | | cp850| DOS West European | cp850_general_ci| 1 | | hp8 | HP West European| hp8_english_ci | 1 | | koi8r| KOI8-R Relcom Russian | koi8r_general_ci| 1 | | latin1 | cp1252 West European| latin1_swedish_ci | 1 | | latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 | | swe7 | 7bit Swedish| swe7_swedish_ci | 1 | | ascii| US ASCII| ascii_general_ci| 1 | | ujis | EUC-JP Japanese | ujis_japanese_ci| 3 | | sjis | Shift-JIS Japanese | sjis_japanese_ci| 2 | | hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 | | tis620 | TIS620 Thai | tis620_thai_ci | 1 | | euckr| EUC-KR Korean | euckr_korean_ci | 2 | | koi8u| KOI8-U Ukrainian| koi8u_general_ci| 1 | | gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 | | greek| ISO 8859-7 Greek| greek_general_ci| 1 | | cp1250 | Windows Central European| cp1250_general_ci | 1 | | gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 | | latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 | | armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 | | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | | ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 | | cp866| DOS Russian | cp866_general_ci| 1 | | keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 | | macce| Mac Central European| macce_general_ci| 1 | | macroman | Mac West European | macroman_general_ci | 1 | | cp852| DOS Central European| cp852_general_ci| 1 | | latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 | | cp1251 | Windows Cyrillic| cp1251_general_ci | 1 | | cp1256 | Windows Arabic | cp1256_general_ci | 1 | | cp1257 | Windows Baltic | cp1257_general_ci | 1 | | binary | Binary pseudo charset | binary | 1 | | geostd8 | GEOSTD8 Georgian| geostd8_general_ci | 1 | | cp932| SJIS for Windows Japanese | cp932_japanese_ci | 2 | | eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 | +--+-+-++ Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Tim McDaniel [mailto:[EMAIL PROTECTED] Sent: Monday, April 28, 2008 12:19 PM Cc: mysql@lists.mysql.com Subject: RE: Problem with character set and connection collation On Mon, 28 Apr 2008, Jerry Schwartz [EMAIL PROTECTED] wrote: A week or so ago I explored this in depth because I was having the same problems. (It was affecting an English file that had some Windows (CP-1252) characters that didn't directly map to UTF-8. That message is at http://lists.mysql.com/mysql/212392. I didn't mention it in my posting, but latin1 is CP-1252. I couldn't get that to work, either. Latin-1 is not Microsoft Windows codepage 1252: Latin-1 has control characters in the 0x80 - 0x9F block of code points, but 1252 replaces some of those code points with printable characters. -- Tim McDaniel, [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with character set and connection collation
Hello all, here is my problem: I am trying to set mysql to unicode character, so that i can get my dictionary application to look at words in Nepali. here is my setting: mysql charset: utf-8 unicode when i make a new database: mysql connection collation is utf8-general-ci and my new database collation is also utf-general-ci i import tables into this database using mysql command : source /path to file when i browse the tables in this database i dont see nepali world instead it seems like mysql cant read the characters; please help!! best, sulo
mysqldump character set
Hi, I'm storing some ascii codes in a table. When I do a dump using mysqldump from the server and then restoring it on another station, the ascii codes in the table has changed. But if i'm accessing the table from another station the code is good. Even if I insert it from a station, it goes fine in the server. Anyone knows why it changes when using mysqldump? How can I prevent this problem to happen in the future? Regards, Velen
CHARACTER SET
When you create a table, you can specify a character set for a column. How can you tell what character set was used when the column was created? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 http://www.the-infoshop.com www.the-infoshop.com http://www.giiexpress.com www.giiexpress.com www.etudes-marche.com
Re: CHARACTER SET
When you create a table, you can specify a character set for a column. How can you tell what character set was used when the column was created? SHOW CREATE TABLE. If no character set is shown for the column, it uses the table default character set. Example: mysql create table t (c1 char(5) character set utf8, c2 char(5)); Query OK, 0 rows affected (0.16 sec) mysql show create table t\G *** 1. row *** Table: t Create Table: CREATE TABLE `t` ( `c1` char(5) CHARACTER SET utf8 DEFAULT NULL, `c2` char(5) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) The definition for c1 shows that utf8 is used The definition for c2 shows nothing, so the table character set (latin1) is used. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: CHARACTER SET
Bingo! You get a cookie. Thanks, I knew there had to be a way. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 16, 2008 10:25 AM To: Jerry Schwartz; 'Mysql' Subject: Re: CHARACTER SET When you create a table, you can specify a character set for a column. How can you tell what character set was used when the column was created? SHOW CREATE TABLE. If no character set is shown for the column, it uses the table default character set. Example: mysql create table t (c1 char(5) character set utf8, c2 char(5)); Query OK, 0 rows affected (0.16 sec) mysql show create table t\G *** 1. row *** Table: t Create Table: CREATE TABLE `t` ( `c1` char(5) CHARACTER SET utf8 DEFAULT NULL, `c2` char(5) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) The definition for c1 shows that utf8 is used The definition for c2 shows nothing, so the table character set (latin1) is used. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CHARACTER SET
On Wed, Apr 16, 2008 at 7:24 AM, Paul DuBois [EMAIL PROTECTED] wrote: When you create a table, you can specify a character set for a column. How can you tell what character set was used when the column was created? SHOW CREATE TABLE. If no character set is shown for the column, it uses the table default character set. Example: mysql create table t (c1 char(5) character set utf8, c2 char(5)); Query OK, 0 rows affected (0.16 sec) mysql show create table t\G *** 1. row *** Table: t Create Table: CREATE TABLE `t` ( `c1` char(5) CHARACTER SET utf8 DEFAULT NULL, `c2` char(5) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) The definition for c1 shows that utf8 is used The definition for c2 shows nothing, so the table character set (latin1) is used. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com Is there any reason that the information_schema would not be the preferred method of finding this information? mysql select table_collation from tables WHERE `table_name` = 'mytable' AND table_schema ='mydatabase'\G -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: CHARACTER SET
The production system is running 4.1.22. Does it supports schemas? In any event, I'm not familiar with using them. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Rob Wultsch [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 16, 2008 11:50 AM To: Paul DuBois Cc: Jerry Schwartz; Mysql Subject: Re: CHARACTER SET On Wed, Apr 16, 2008 at 7:24 AM, Paul DuBois [EMAIL PROTECTED] wrote: When you create a table, you can specify a character set for a column. How can you tell what character set was used when the column was created? SHOW CREATE TABLE. If no character set is shown for the column, it uses the table default character set. Example: mysql create table t (c1 char(5) character set utf8, c2 char(5)); Query OK, 0 rows affected (0.16 sec) mysql show create table t\G *** 1. row *** Table: t Create Table: CREATE TABLE `t` ( `c1` char(5) CHARACTER SET utf8 DEFAULT NULL, `c2` char(5) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) The definition for c1 shows that utf8 is used The definition for c2 shows nothing, so the table character set (latin1) is used. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com Is there any reason that the information_schema would not be the preferred method of finding this information? mysql select table_collation from tables WHERE `table_name` = 'mytable' AND table_schema ='mydatabase'\G -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CHARACTER SET
At 8:49 AM -0700 4/16/08, Rob Wultsch wrote: On Wed, Apr 16, 2008 at 7:24 AM, Paul DuBois [EMAIL PROTECTED] wrote: When you create a table, you can specify a character set for a column. How can you tell what character set was used when the column was created? SHOW CREATE TABLE. If no character set is shown for the column, it uses the table default character set. Example: mysql create table t (c1 char(5) character set utf8, c2 char(5)); Query OK, 0 rows affected (0.16 sec) mysql show create table t\G *** 1. row *** Table: t Create Table: CREATE TABLE `t` ( `c1` char(5) CHARACTER SET utf8 DEFAULT NULL, `c2` char(5) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) The definition for c1 shows that utf8 is used The definition for c2 shows nothing, so the table character set (latin1) is used. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com Is there any reason that the information_schema would not be the preferred method of finding this information? mysql select table_collation from tables WHERE `table_name` = 'mytable' AND table_schema ='mydatabase'\G You can do that, too, unless your version of MySQL is older than 5.0. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data infile and character set
Hi, Try this. set session collation_database=latin1_swedish_ci; set session character_set_database=latin1; regards anandkl On 10/29/07, Dušan Pavlica [EMAIL PROTECTED] wrote: Are you sure your file is coded in utf8? Character set of your file must be same as charset of your database. Dusan Caleb Racey napsal(a): Does anyone know how to get the load data infile command to load utf8 data? I have setup a database as utf8 with a collation of utf8_general_ci, the mysqld server is started with --character-set-server=utf8. Server variables say character_set_database = utf8. I use the sql below LOAD DATA INFILE 'filename' REPLACE INTO TABLE db.table CHARACTER SET utf8 FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' Yet when i try to load a file with an accented value in it e.g. Agustín the value gets truncated to Agust anyone got any solutions to this? Regards Cal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data infile and character set
Are you sure your file is coded in utf8? Character set of your file must be same as charset of your database. Dusan Caleb Racey napsal(a): Does anyone know how to get the load data infile command to load utf8 data? I have setup a database as utf8 with a collation of utf8_general_ci, the mysqld server is started with --character-set-server=utf8. Server variables say character_set_database = utf8. I use the sql below LOAD DATA INFILE 'filename' REPLACE INTO TABLE db.table CHARACTER SET utf8 FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' Yet when i try to load a file with an accented value in it e.g. Agustín the value gets truncated to Agust anyone got any solutions to this? Regards Cal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data infile and character set
Ananda Kumar wrote: Hi, Try this. set session collation_database=latin1_swedish_ci; set session character_set_database=latin1; Rather: set session collation_database=utf8_general_ci; set session character_set_database=utf8; Also, make sure you have these in my.cnf: [client] default-character-set=utf8 [mysqld] default-character-set=utf8 init-connect='SET NAMES utf8' And you might want to add these also: collation_server=utf8_unicode_ci character_set_server=utf8 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
load data infile and character set
Does anyone know how to get the load data infile command to load utf8 data? I have setup a database as utf8 with a collation of utf8_general_ci, the mysqld server is started with --character-set-server=utf8. Server variables say character_set_database = utf8. I use the sql below LOAD DATA INFILE 'filename' REPLACE INTO TABLE db.table CHARACTER SET utf8 FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' Yet when i try to load a file with an accented value in it e.g. Agustín the value gets truncated to Agust anyone got any solutions to this? Regards Cal
Re: load data infile and character set
Caleb Racey wrote: Does anyone know how to get the load data infile command to load utf8 data? I have setup a database as utf8 with a collation of utf8_general_ci, the mysqld server is started with --character-set-server=utf8. Server variables say character_set_database = utf8. I use the sql below LOAD DATA INFILE 'filename' REPLACE INTO TABLE db.table CHARACTER SET utf8 FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' Yet when i try to load a file with an accented value in it e.g. Agustín the value gets truncated to Agust anyone got any solutions to this? It is indeed buggy and badly documented. It depends on the current database's character set instead. Try this: SET NAMES utf8; SET character_set_database=utf8; LOAD DATA INFILE... Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data infile and character set
Caleb Racey wrote: Does anyone know how to get the load data infile command to load utf8 data? I have setup a database as utf8 with a collation of utf8_general_ci, the mysqld server is started with --character-set-server=utf8. Server variables say character_set_database = utf8. I use the sql below LOAD DATA INFILE 'filename' REPLACE INTO TABLE db.table CHARACTER SET utf8 FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' Yet when i try to load a file with an accented value in it e.g. Agustín the value gets truncated to Agust If you haven't already, issue this first: SET CHARACTER SET utf8; SET NAMES utf8; LOAD DATA INFILE ... b -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data infile and character set
Caleb Racey wrote: On 10/26/07, Baron Schwartz [EMAIL PROTECTED] wrote: Caleb Racey wrote: It is indeed buggy and badly documented. It depends on the current database's character set instead. Try this: SET NAMES utf8; SET character_set_database=utf8; LOAD DATA INFILE... Baron Thanks for the suggestion I'm afraid i get the same behaviour when i try this approach accented values still truncate at the accent when i use load data infile. OK, the next suggestion is to use the 'binary' character set. By the way, I accidentally omitted quotes above; I should have typed SET character_set_database='utf8'; You should verify your connection's character sets with SHOW VARIABLES LIKE '%character%'; Try it with 'binary' and see if that works. Baron thanks again I'm afraid 'binary' achieved the same result as did using utf8 with quotes. I'm out of ideas, then. I wish I could help more. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT ... LIKE and the Korean character set
Hello. I have a question about the behavior of SELECT ... LIKE and dealing with the Korean language. For those who don't know anything about the way the language's characters are formulated, I'll give you a quick crash course: Korean has its own alphabet, just like Japanese, Chinese and most other Asian languages. However, each Korean character in a word is actually a syllable composed of those alphabets. So ㅎ (H) + ㅏ (A) + ㄴ (N) = 한. (If the mailing list doesn't support Unicode/UTF-8, please tell me so I can direct you to a better example.) But with the way Unicode works, it's not exactly like H is in the same block that HAN is represented in, especially in the Korean character table. So here's the issue I'm having: I'm writing a Korean dictionary for myself. Basically, an application for adding words I've learned in my Korean lessons. But I'm having a problem with the LIKE part of the SELECT statement. Essentially, I'd like to do the Korean equivalent of LIKE 'A%'. However, doing LIKE 'ㅎ%', when a word like 'hangul' (한글) is in the database, I get no results. Obviously, creating a table of every combination of every character possible for each consonant in the Korean language is nonoptimal. So what can I do to solve this problem? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
character set problem
Hi, I want to search from a table , I have a table with a column that each record filled with different character set ,is it possible to know what are their character set and then is it possible to change all of them to a character set ,how can I do that? these records show like below that I don't know what are their charset : ارÚ(c)يد شيمی جهان #1578;#1608;#1604;#1610;#1583; thanks
Re: automated character set conversions for tables
Hi You can 'attack' the problem from another perspective :-P I found out a tool (Linux) to convert all characters in a latin1 file to utf8. The command name is 'iconv' and in Debian it is shipped with the libc6 package. So basically dump you DB and convert the file then import it. See the article here: http://gentoo-wiki.com/TIP_Convert_latin1_to_UTF-8_in_MySQL Hope it helps. [EMAIL PROTECTED] wrote: Hello Jerry, Thanks for the reply. You are right. Thats why it finally took me 10-12 work hours to convert a single DB (split over two work days) to a UTF-8 compliant version. While it wasnt necessarily difficult to do (once you figured it out), it can put extra pressure on your eyes if you have to concentrate on the screen all the time ;-). If somebody knows of a smart tool that is doing the hart work feel free to speak ;-) Best regards Nils Valentin Quoting Jerry Schwartz [EMAIL PROTECTED]: Columns can have character set definitions, also. In this case, I hope not. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Chris White [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 10, 2007 10:59 AM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: automated character set conversions for tables I did a DB conversion before that with ALTER DATABASE db_name CHARACTER SET utf8 That worked wonderfully, except not as expected. ;-) It basically converted only the database itself. so I had to do a separate ALTER TABLE ... for each table. The database encoding more establishes the default to use when creating new tables. As far as adjusting every single table, you can work with your Favorite Scripting Program (tm) and run the query: `SHOW TABLES` to get a list of all tables for that database (the column you want is called Tables_in_[database name here]), which you can get the exact column by running it in console or your Favorite SQL Program (tm). Then simply loop over the result set and run the alter table command on each table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Character Set Conversion Scenarios / Detection
Hi, We are deploying a software upgrade and need to convert the MySQL database of all installations from their previous character set (default) to UTF-8. I have read the instructions at http://dev.mysql.com/doc/refman/5.0/en/charset-conversion.html and the discussion at http://bugs.mysql.com/bug.php?id=22719. According to the documentation, there are 3 different scenarios: Case A: old tables from MySQL 4.0 or earlier where a non-binary column contains values that actually are encoded in a character set different from the server's default character set. - Upgrade / conversion path: 1. convert VARCHAR to VARBINARY 2. convert VARBINRARY to VARCHAR UTF8 Case B: same but starting with MySQL 4.1 or later - Upgrade path: 1. convert VARCHAR to VARCHAR UTF8 directly Case C: starting with case A but there have been INSERTs/UPDATEs after the MySQL upgrade to 4.1 - Upgrade path: what to do here to minimize data loss? We tried to store UTF8 in the database even in pre MySQL 4.1 databases. What we Did until now is assume case A. But this leads to string truncation in case B (see the above mentioned bug report) and that's why we need to improve the upgrade path. Questions: 1. How can we detect the 3 different cases? We don't know the history of the specific installations since all we do is offer the software. The software should auto-detect the scenario and run the appropriate upgrade code. 2. Do you have any advice for case C? Thanks in advance, - Andy Staudacher, Gallery.sourceforge.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: automated character set conversions for tables
Hello Jerry, Thanks for the reply. You are right. Thats why it finally took me 10-12 work hours to convert a single DB (split over two work days) to a UTF-8 compliant version. While it wasnt necessarily difficult to do (once you figured it out), it can put extra pressure on your eyes if you have to concentrate on the screen all the time ;-). If somebody knows of a smart tool that is doing the hart work feel free to speak ;-) Best regards Nils Valentin Quoting Jerry Schwartz [EMAIL PROTECTED]: Columns can have character set definitions, also. In this case, I hope not. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Chris White [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 10, 2007 10:59 AM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: automated character set conversions for tables I did a DB conversion before that with ALTER DATABASE db_name CHARACTER SET utf8 That worked wonderfully, except not as expected. ;-) It basically converted only the database itself. so I had to do a separate ALTER TABLE ... for each table. The database encoding more establishes the default to use when creating new tables. As far as adjusting every single table, you can work with your Favorite Scripting Program (tm) and run the query: `SHOW TABLES` to get a list of all tables for that database (the column you want is called Tables_in_[database name here]), which you can get the exact column by running it in console or your Favorite SQL Program (tm). Then simply loop over the result set and run the alter table command on each table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: automated character set conversions for tables
I did a DB conversion before that with ALTER DATABASE db_name CHARACTER SET utf8 That worked wonderfully, except not as expected. ;-) It basically converted only the database itself. so I had to do a separate ALTER TABLE ... for each table. The database encoding more establishes the default to use when creating new tables. As far as adjusting every single table, you can work with your Favorite Scripting Program (tm) and run the query: `SHOW TABLES` to get a list of all tables for that database (the column you want is called Tables_in_[database name here]), which you can get the exact column by running it in console or your Favorite SQL Program (tm). Then simply loop over the result set and run the alter table command on each table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: automated character set conversions for tables
Columns can have character set definitions, also. In this case, I hope not. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Chris White [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 10, 2007 10:59 AM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: automated character set conversions for tables I did a DB conversion before that with ALTER DATABASE db_name CHARACTER SET utf8 That worked wonderfully, except not as expected. ;-) It basically converted only the database itself. so I had to do a separate ALTER TABLE ... for each table. The database encoding more establishes the default to use when creating new tables. As far as adjusting every single table, you can work with your Favorite Scripting Program (tm) and run the query: `SHOW TABLES` to get a list of all tables for that database (the column you want is called Tables_in_[database name here]), which you can get the exact column by running it in console or your Favorite SQL Program (tm). Then simply loop over the result set and run the alter table command on each table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
automated character set conversions for tables
Hello fellow MySQL friends, I recently had the fun to convert some 60 tables within a DB f.e. from latin1 to UTF8. I did a DB conversion before that with ALTER DATABASE db_name CHARACTER SET utf8 That worked wonderfully, except not as expected. ;-) It basically converted only the database itself. so I had to do a separate ALTER TABLE ... for each table. Are you aware of a much smarter way to do this ? (perhaps a function I missed, script, batch file etc.) Best regards Nils Valentin http://www.be-known-online.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
character set for French... confused :\
Hi all, I need a database that is able to handle french characters. I read the Mysql Reference Manual and have done the following in a table to test the different results but I wasn't successful which leads me to believe I am doing something wrong... and yes, I'm a noob. ;) CREATE TABLE companies ( c1 VARCHAR(30) CHARACTER SET utf8, c2 VARCHAR(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci, c3 VARCHAR(30) CHARACTER SET latin1, c4 VARCHAR(30) CHARACTER SET latin1 COLLATE latin1_general_ci, c5 VARCHAR(30) CHARACTER SET dec8, c6 VARCHAR(30) CHARACTER SET dec8 COLLATE dec8_bin, c7 VARCHAR(30) CHARACTER SET hp8, c8 VARCHAR(30) CHARACTER SET hp8 COLLATE hp8_bin, c9 VARCHAR(30) CHARACTER SET cp850, c10 CHAR(30)NOT NULL, c11 TEXTCHARACTER SET latin1 COLLATE latin1_general_ci ); When I insert 'Récré, Vive La Fête! the best I get is R?cr?, Vive La F?te! from c11. Can anyone advise me on what I need done to get these accents show up? Pointers to more documentation are also welcome. Many thanks, -Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: character set for French... confused :\
On Thursday 07 December 2006 10:09, Ed wrote: Hi all, I need a database that is able to handle french characters. Sorry about that, it's probably due to my OS rather than MySQL. $ echo Fête Fête $ touch Fête $ ls -l -rw-r--r-- 1 me me 0 Dec 7 14:20 F?te $ rm Fête Hmmm, back to OS ;) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Connection character set be changed silently?
My programm connect to MYSQL server(4.1.12), set it's character set by set NAMES gbk, the variables when client programm started is character_set_client gbk character_set_connection gbk character_set_databasegbk character_set_resultsgbk character_set_server gbk after a long while, like one night, I found the client character set have been changed to latin, character_set_client latin1 character_set_connection latin1 character_set_database gbk character_set_results latin1 character_set_servergbk it seems related to some timeout value, the wait_timeout and interative_timeout is 28800(8 hours). I tested same program with MySQL 4.1.7, the connection was disconnnected by server since wait_timeout was set to 8 hours. why the characoter set would be changed with MYSQL 4.1.12? My programm use MYSQL C API. MySQL setting : My.cnf [mysqld] socket=/var/lib/mysql/mysql.sock # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 default-character-set=gbk [mysql.server] user=mysql default-character-set=gbk default-collation=gbk_bin [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid default-character-set=gbk [mysql] default-character-set=gbk Thanks Hanson
Client connection character set be changed after a long while?
My programm connect to MYSQL server, adn set it's charactor set by set NAMES gbk, after a long while, like one night, I found the client charactor set have been changed to latin, it seems related to some timeout value. why the characotor set would be changed? My programm use MYSQL C API. Thanks Hanson
Character Set convert?
Hey all, got a simple question: Is there a quick way to convert all the data in my database to a specific character set? I want to convert all the text to UTF-8. -Charlie -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
CHARACTER SET COLLATE NULL error with mySQL 4.0.27
Hola. With mySQL 4.0.27 I'm trying to create this table -- CREATE TABLE `test`.`user` ( `user_id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `email` TEXT CHARACTER SET COLLATE NULL , `firstname` TEXT CHARACTER SET COLLATE NOT NULL , ) -- but I get this error: -- #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHARACTER SET COLLATE NULL, `firstname` TEXT CHARACTER SET -- Does anybody know which the right way is for this mySQL version? I'm using mySQL-Workbench to design the tables, and that's the code I get? Thank you very much. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CHARACTER SET COLLATE NULL error with mySQL 4.0.27
Hello Thomas You have an error in your SQL syntax. Please try this : CREATE TABLE test.user ( user_id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY(user_id), email text CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci', firstname text CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci'); The error is due to the AUTO_INCREMENT column, which is not defined as PRIMARY KEY and the CHARACTER SET column, which is not syntatically defined. Also to list the MySQL support Character Set try: Show Character Set; Thanks ViSolve MySQL Support Team. - Original Message - From: thomas Armstrong [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, July 20, 2006 2:41 PM Subject: CHARACTER SET COLLATE NULL error with mySQL 4.0.27 Hola. With mySQL 4.0.27 I'm trying to create this table -- CREATE TABLE `test`.`user` ( `user_id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `email` TEXT CHARACTER SET COLLATE NULL , `firstname` TEXT CHARACTER SET COLLATE NOT NULL , ) -- but I get this error: -- #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHARACTER SET COLLATE NULL, `firstname` TEXT CHARACTER SET -- Does anybody know which the right way is for this mySQL version? I'm using mySQL-Workbench to design the tables, and that's the code I get? Thank you very much. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Character set problem
Hi. I just installed MySQL 4.1.20 for the UTF-8 support it offers. I'm trying to set the charcter set collation at the database level but can't get it to work. It works by setting it at the server level (in my.cnf) but I want to set it at the database level because I'll be hosting the web app on a third-party web hosting server, where likely I won't have access to server level settings via my.cnf (is there a way around this?). I perform the following query: ALTER DATABASE my schema name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; After the query a dump of the relevant MySQL variables is as follows: character_set_client: utf8 character_set_connection: utf8 character_set_database: utf8 character_set_results: utf8 character_set_server: latin1 character_set_system: utf8 collation_connection: utf8_general_ci collation_database: utf8_general_ci collation_server: latin1_swedish_ci The variables look right but the characters (chinese in this case) do not display correctly in the web page. As mentioned before setting the charset at server level results in the characters displaying just fine. Any thoughts/guesses? Thanks in advance. _ Consolidate your email! http://www.fusemail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Character set problem
From: Spiros Philopoulos Sent: 06 July 2006 13:32 Hi. I just installed MySQL 4.1.20 for the UTF-8 support it offers. I'm trying to set the charcter set collation at the database level but can't get it to work. It works by setting it at the server level (in my.cnf) but I want to set it at the database level because I'll be hosting the web app on a third-party web hosting server, where likely I won't have access to server level settings via my.cnf (is there a way around this?). I perform the following query: ALTER DATABASE my schema name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; After the query a dump of the relevant MySQL variables is as follows: character_set_client: utf8 character_set_connection: utf8 character_set_database: utf8 character_set_results: utf8 character_set_server: latin1 character_set_system: utf8 collation_connection: utf8_general_ci collation_database: utf8_general_ci collation_server: latin1_swedish_ci The variables look right but the characters (chinese in this case) do not display correctly in the web page. As mentioned before setting the charset at server level results in the characters displaying just fine. Any thoughts/guesses? The ALTER above only sets the character set for any future tables created, it won't convert any data already in tables. If you recreate the tables and data after setting the database char set, does it work? You can use 'SHOW CREATE table_name' to check what character set the tables are actually using. If you need to convert existing data see: http://dev.mysql.com/doc/refman/4.1/en/charset-conversion.html Mind you not sure how that fits with it working with the database char set set to utf8! hth, mark -- MARK ADDISON WEB DEVELOPER 200 GRAY'S INN ROAD LONDON WC1X 8XZ UNITED KINGDOM T +44 (0)20 7430 4678 F E [EMAIL PROTECTED] WWW.ITN.CO.UK Please Note: Any views or opinions are solely those of the author and do not necessarily represent those of Independent Television News Limited unless specifically stated. This email and any files attached are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error, please notify [EMAIL PROTECTED] Please note that to ensure regulatory compliance and for the protection of our clients and business, we may monitor and read messages sent to and from our systems. Thank You. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data truncated during character set conversion
On Mon, Jul 03, 2006 at 01:55:26PM +0400, Timur Izhbulatov wrote: I'm following the instructions [1] to convert character set. Unfortunately I get warnings about truncated data for some rows in several columns. All the truncated columns are text type. Assuming the `col' column is text type and actually contains correct utf8 data but has wrong character set I use the following queries to fix it: ALTER TABLE table MODIFY col BLOB; ALTER TABLE table MODIFY col TEXT CHARACTER SET utf8; After the second query I get warnings about truncated data for some rows and I can actually see the rows truncated. I played a bit with different data types (LONGBLOB/LONGTEXT) but wasn't successful. So my question is what can be the cause of the data loss and how to avoid it? After some additional investigations I found the cause. It was some exotic non-ASCII characters like '–' (long dash). At the same time Russian letters don't cause any problems. Seems the problem arises because the table itself also has wrong default character set (latin1). Setting default character set to utf8 with ALTER TABLE doesn't solve the problem. Bug if I create a new table with utf8 as default character set convertion works fine. What's happening? Please see the testcase attached. My character set settings are: SHOW VARIABLES LIKE '%char%'; +--+--+ | Variable_name| Value| +--+--+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_results| utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/local/share/mysql/charsets/ | +--+--+ Cheers, -- Timur Izhbulatov OILspace, 26 Leninskaya sloboda, bld. 2, 2nd floor, 115280 Moscow, Russia P:+7 495 105 7245 + ext.205 F:+7 495 105 7246 E:[EMAIL PROTECTED] Building Successful Supply Chains - One Solution At A Time. www.oilspace.com -- MySQL dump 10.9 -- -- Host: localhostDatabase: tizhbulatov_aquarium -- -- -- Server version 4.1.19 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `stories` -- DROP TABLE IF EXISTS `test_table`; CREATE TABLE `test_table` ( `overview` text ) ENGINE=MyISAM DEFAULT CHARSET=latin1; ALTER TABLE `test_table` DEFAULT CHARACTER SET utf8; INSERT INTO `test_table` SET `overview` = '–'; SELECT * FROM `test_table`; ALTER TABLE `test_table` MODIFY `overview` BLOB; ALTER TABLE `test_table` MODIFY `overview` text CHARACTER SET utf8; SHOW WARNINGS; /*!40101 SET [EMAIL PROTECTED] */; /*!40014 SET [EMAIL PROTECTED] */; /*!40014 SET [EMAIL PROTECTED] */; /*!40101 SET [EMAIL PROTECTED] */; /*!40101 SET [EMAIL PROTECTED] */; /*!40101 SET [EMAIL PROTECTED] */; /*!40111 SET [EMAIL PROTECTED] */; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data truncated during character set conversion
On Tue, Jul 04, 2006 at 01:55:30PM +0400, Timur Izhbulatov wrote: After some additional investigations I found the cause. It was some exotic non-ASCII characters like '–' (long dash). At the same time Russian letters don't cause any problems. Sorry, I was wrong concerning Russian letters. Acutally *any* non-ASCII character causes the problem. The problem seems to be limited only to TEXT fields, non-ASCII characters (including Russian) in CHAR/VARCHAR fields are OK. -- Timur Izhbulatov OILspace, 26 Leninskaya sloboda, bld. 2, 2nd floor, 115280 Moscow, Russia P:+7 495 105 7245 + ext.205 F:+7 495 105 7246 E:[EMAIL PROTECTED] Building Successful Supply Chains - One Solution At A Time. www.oilspace.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Data truncated during character set conversion
Hi all, I'm following the instructions [1] to convert character set. Unfortunately I get warnings about truncated data for some rows in several columns. All the truncated columns are text type. Assuming the `col' column is text type and actually contains correct utf8 data but has wrong character set I use the following queries to fix it: ALTER TABLE table MODIFY col BLOB; ALTER TABLE table MODIFY col TEXT CHARACTER SET utf8; After the second query I get warnings about truncated data for some rows and I can actually see the rows truncated. I played a bit with different data types (LONGBLOB/LONGTEXT) but wasn't successful. So my question is what can be the cause of the data loss and how to avoid it? Thanks in advance. [1] http://dev.mysql.com/doc/refman/4.1/en/charset-conversion.html Cheers, -- Timur Izhbulatov OILspace, 26 Leninskaya sloboda, bld. 2, 2nd floor, 115280 Moscow, Russia P:+7 495 105 7245 + ext.205 F:+7 495 105 7246 E:[EMAIL PROTECTED] Building Successful Supply Chains - One Solution At A Time. www.oilspace.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: Mysql C API character set
From: He, Gang Sent: Wednesday, April 12, 2006 8:43 PM To: '[EMAIL PROTECTED]' Subject: Mysql C API character set Hi, I install MySQL server 4.1.12 in Redhat Linux in Japanese environment ( env LANG=ja_JP.UTF-8 ), I setup MySQL server utf8 character set, the configuration is as follows: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 default-character-set=utf8 [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Then, I start MySQL server, and type mysql command, the result is as follows: mysql show variables like 'char%'; +--++ | Variable_name| Value | +--++ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_results| utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql/share/mysql/charsets/ | +--++ 7 rows in set (0.00 sec) But, I write a sample code using MySQL C API 4.1.10, the code is as follows: int main(int argc, char* argv[]) { MYSQLm_conn; MYSQL_RES *res; MYSQL_ROW row; mysql_init(m_conn); mysql_real_connect(m_conn, localhost, root, 123456, mysql, 0, NULL, 0); mysql_query(m_conn, show variables like 'character_set_%'); res=mysql_store_result(m_conn); while((row=mysql_fetch_row(res))!=NULL) { printf(row[0]); printf(\t); printf(row[1]); printf(\n); } mysql_free_result(res); mysql_close(m_conn); return 0; } The output result is as follows: character_set_clientlatin1 character_set_connectionlatin1 character_set_database utf8 character_set_results latin1 character_set_serverutf8 character_set_systemutf8 character_sets_dir /usr/local/mysql/share/mysql/charsets/ I want to why character_set_client , character_set_connection and character_set_results in the output result be latin1, not utf8 ? If I want to change MySQL C API default value when the application call mysql_real_connect() function, what I should do ? Can I change MySQL C API default value via modifying '/etc/my.cnf ' configuration file ? ( I think that the best way is to modify configuration file ) Thanks Gang
Re: migrating 4.0 to 4.1 character set problems
Hi Sean, I used to have similar problems with different character sets and my solution is: 1) dump all data using mysql dump utility 2) check if data are OK - view your data with viewer which suports correct character set 3) if data are not OK try to dump data in different character set 4) you must be sure that data are dumped OK and you know their character set 5) you can convert your dumped data manualy using some conversion program or you can let MySQL server do it 6) if you did conversion of dumped file you must also change SET NAMES 'new character set' command 7) setup correct default character set for your MySQL server or change or add character sets to your definitions of tables and databases in dumped file 8) load data HTH Dusan - Original Message - From: Sean O'Hara [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, March 14, 2006 5:09 PM Subject: migrating 4.0 to 4.1 character set problems Hi, I am having character set problems while trying to migrate my data from a server running 4.0.25-standard to a server running 4.1.16. I believe that the orginal database was using the latin1 character set (not sure, is there any way to tell? show full column doesn't seem to be supported in 4.0.x) and the default character set for the 4.1 server is latin1 as well. However when I try to import my data from a mysqldump from the older server all accents are turned into question marks. I've searched the forums and mailing lists extensively for clues as to how to resolve this issue. The most helpful post I found was here: http://lists.mysql.com/mysql/186657 However, even following the instructions on this post I ran into problems. Rather than turniing accents into question marks, any data with accent was truncated at the first instance of such a character (e.g. López becomes L). So that is not working either. Incidentally, I would be happy to convert the data to utf8 while upgrading to 4.1 but mostly I just want to get rid of the question mark characters. Any ideas? Thanks, Sean -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
migrating 4.0 to 4.1 character set problems
Hi, I am having character set problems while trying to migrate my data from a server running 4.0.25-standard to a server running 4.1.16. I believe that the orginal database was using the latin1 character set (not sure, is there any way to tell? show full column doesn't seem to be supported in 4.0.x) and the default character set for the 4.1 server is latin1 as well. However when I try to import my data from a mysqldump from the older server all accents are turned into question marks. I've searched the forums and mailing lists extensively for clues as to how to resolve this issue. The most helpful post I found was here: http://lists.mysql.com/mysql/186657 However, even following the instructions on this post I ran into problems. Rather than turniing accents into question marks, any data with accent was truncated at the first instance of such a character (e.g. López becomes L). So that is not working either. Incidentally, I would be happy to convert the data to utf8 while upgrading to 4.1 but mostly I just want to get rid of the question mark characters. Any ideas? Thanks, Sean -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: migrating 4.0 to 4.1 character set problems
Hi, I have figured out a way to get this to work. I am just posting what I did in case anyone else stumbles across my original message. I am sure there are other ways to get this to work, but this is the way that worked for me: ### # My HOWTO: Covernt mysql 4.0.x DBs to 4.1.x # # This will also update the character sets from # # Latin1 to utf8 # # # ### 1. Backup the old database. It must be backed up as separate tab separate files like the following: mysqldump --tab=/tmp/database database -u user --password=password 2. Tar and transfer the directory if necessary, then import it using the following commands: mysql -e CREATE DATABASE database default character set latin1 cat /tmp/database/*sql | mysql database -u user --password=password mysqlimport --default-character-set=latin1 alien8_orig /tmp/tables/ *txt -u user --password=password 3. Convert the database tables to utf-8 ALTER TABLE database.table type=MyISAM, CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; 4. For use with php5, after every database connection send the following query: mysql_query(SET NAMES 'utf8'); 5. Also, for PHP5, you will want to make sure that server is using utf-8 as the encoding. I believe this is the default. And pages should be valid xhtml. I use the content-type meta tag just to be safe: meta http-equiv=Content-Type content=text/html; charset=UTF-8 / Thanks, Sean On 14-Mar-06, at 11:09 AM, Sean O'Hara wrote: Hi, I am having character set problems while trying to migrate my data from a server running 4.0.25-standard to a server running 4.1.16. I believe that the orginal database was using the latin1 character set (not sure, is there any way to tell? show full column doesn't seem to be supported in 4.0.x) and the default character set for the 4.1 server is latin1 as well. However when I try to import my data from a mysqldump from the older server all accents are turned into question marks. I've searched the forums and mailing lists extensively for clues as to how to resolve this issue. The most helpful post I found was here: http://lists.mysql.com/mysql/186657 However, even following the instructions on this post I ran into problems. Rather than turniing accents into question marks, any data with accent was truncated at the first instance of such a character (e.g. López becomes L). So that is not working either. Incidentally, I would be happy to convert the data to utf8 while upgrading to 4.1 but mostly I just want to get rid of the question mark characters. Any ideas? Thanks, Sean -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] __ ALIEN8 RECORDINGS P.O. BOX 666, STATION R MONTREAL, QC CANADA, H2S 3L1 http://www.alien8recordings.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CONCAT() returns not correct character set
Hi there, I tried a few other queries: first, confirm that what you think is a binary is indeed a binary: mysql SELECT CHARSET(_binary'Binary'); +--+ | CHARSET(_binary'Binary') | +--+ | binary | +--+ 1 row in set (0.00 sec) check the regular text (for completeness' sake) mysql select charset ('binary'); ++ | charset ('binary') | ++ | latin1 | ++ 1 row in set (0.00 sec) OK, so concatenating the 2 should result in a binary according to the manual: mysql SELECT CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1))); +--+ | CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1))) | +--+ | latin1 | +--+ 1 row in set (0.00 sec) Hrm, I got the same result you did. However, mysql SELECT CHARSET(CONCAT(_binary'Binary','foo')); +--+ | CHARSET(CONCAT((_binary'Binary'),'foo')) | +--+ | binary | +--+ 1 row in set (0.00 sec) works just fine. Why are you converting the text to latin1? It's already there. That conversion seems to be messing things up. Perhaps someone can explain why the conversion messes things up -- seems like a bug to me. -Sheeri On 2/26/06, Hirofumi Fujiwara [EMAIL PROTECTED] wrote: Dear MySQL fans, I tested CONCAT() with binary strings and I got strange result. Manual says: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html If the arguments include any binary strings, the result is a binary string. But the following test says: bianry + latin1 latin1 (not bianry) mysql SELECT CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1))); +--+ | CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1))) | +--+ | latin1 | +--+ 1 row in set (0.00 sec) Hirofumi Fujiwara (Tokyo JAPAN) enjoy JAVA and Puzzle World [EMAIL PROTECTED]http://www.pro.or.jp/~fuji/index-eng.html [EMAIL PROTECTED] Puzzle Japanhttp://www.puzzle.jp/ My SUDOKU Probs http://www.pro.or.jp/~fuji/sudoku/problems/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CONCAT() returns not correct character set
Hi, Is there any difference between _binary'Binary' and CONVERT('Binary' USING binary) mysql SELECT CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1))); +--+ | CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1))) | +--+ | latin1 | +--+ 1 row in set (0.00 sec) mysql SELECT CHARSET(CONCAT(CONVERT('Bianry' USING binary),CONVERT('abc' USING latin1))); +-+ | CHARSET(CONCAT(CONVERT('Bianry' USING binary),CONVERT('abc' USING latin1))) | +-+ | binary | +-+ 1 row in set (0.00 sec) In Japan, we have to use many kinds of character set. eucjpms,ujis for unix cp932,sjis for Windows utf8 for Java, MySQL meta data So, it's a very complicated world. Hi there, I tried a few other queries: first, confirm that what you think is a binary is indeed a binary: mysql SELECT CHARSET(_binary'Binary'); +--+ | CHARSET(_binary'Binary') | +--+ | binary | +--+ 1 row in set (0.00 sec) check the regular text (for completeness' sake) mysql select charset ('binary'); ++ | charset ('binary') | ++ | latin1 | ++ 1 row in set (0.00 sec) OK, so concatenating the 2 should result in a binary according to the manual: mysql SELECT CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1))); +--+ | CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1))) | +--+ | latin1 | +--+ 1 row in set (0.00 sec) Hrm, I got the same result you did. However, mysql SELECT CHARSET(CONCAT(_binary'Binary','foo')); +--+ | CHARSET(CONCAT((_binary'Binary'),'foo')) | +--+ | binary | +--+ 1 row in set (0.00 sec) works just fine. Why are you converting the text to latin1? It's already there. That conversion seems to be messing things up. Perhaps someone can explain why the conversion messes things up -- seems like a bug to me. -Sheeri On 2/26/06, Hirofumi Fujiwara [EMAIL PROTECTED] wrote: Dear MySQL fans, I tested CONCAT() with binary strings and I got strange result. Manual says: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html If the arguments include any binary strings, the result is a binary string. But the following test says: bianry + latin1 latin1 (not bianry) mysql SELECT CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1))); +--+ | CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1))) | +--+ | latin1 | +--+ 1 row in set (0.00 sec) Hirofumi Fujiwara (Tokyo JAPAN) enjoy JAVA and Puzzle World [EMAIL PROTECTED]http://www.pro.or.jp/~fuji/index-eng.html [EMAIL PROTECTED] Puzzle Japanhttp://www.puzzle.jp/ My SUDOKU Probs http://www.pro.or.jp/~fuji/sudoku/problems/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
CONCAT() returns not correct character set
Dear MySQL fans, I tested CONCAT() with binary strings and I got strange result. Manual says: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html If the arguments include any binary strings, the result is a binary string. But the following test says: bianry + latin1 latin1 (not bianry) mysql SELECT CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1))); +--+ | CHARSET(CONCAT(_binary'Bianry',CONVERT('abc' USING latin1))) | +--+ | latin1 | +--+ 1 row in set (0.00 sec) Hirofumi Fujiwara (Tokyo JAPAN) enjoy JAVA and Puzzle World [EMAIL PROTECTED]http://www.pro.or.jp/~fuji/index-eng.html [EMAIL PROTECTED] Puzzle Japanhttp://www.puzzle.jp/ My SUDOKU Probs http://www.pro.or.jp/~fuji/sudoku/problems/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Character set problem
Hy I have a table datas like that : name Mester József Job György Czibere Lajos If I create : select name from dolgozok where name like '%jó%' ; then all data will be shown. But I wolud like see datas which really contain ó character (only Mester József). Joe - Win a BlackBerry device from O2 with Yahoo!. Enter now.
Re: Character set problem
Hello. Perhaps it is an issue of your collation: mysql select a from ts where a like '%ó%' collate utf8_bin ; ++ | a | ++ | Mester József | ++ 1 row in set (0.00 sec) mysql select a from ts where a like binary '%ó%'; ++ | a | ++ | Mester József | ++ mysql select a from ts where a like '%ó%'; ++ | a | ++ | Mester József | | Job György | | Czibere Lajos | ++ See: http://dev.mysql.com/doc/refman/5.0/en/charset-collations.html Mester József wrote: Hy I have a table datas like that : name Mester József Job György Czibere Lajos If I create : select name from dolgozok where name like '%jó%' ; then all data will be shown. But I wolud like see datas which really contain ó character (only Mester József). Joe - Win a BlackBerry device from O2 with Yahoo!. Enter now. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best case-insensitive search for Character Set utf8 COLLATE utf8_bin Columns.
What is the best (most optimal) way to perform a case-insensitive search for a VARCHAR column with COLLATE utf8_bin? I'm assuming the answer is not: SELECT * FROM MyTable WHERE UPPER(MyColumn) LIKE Upper('%pattern%'); Tia! R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Character set issue ( maybe )
Hello. But anyway, the source *IS* latin1, so maybe this shouldn't be a problem anyway? In theory if the source contains only characters from latin1 set there shouldn't be any problems, but are you sure that you're inserting latin1 characters? What is your locale settings? If you can, temporary switch it to utf8. Force character_set_connection, character_set_client, character_set_results variables to have 'utf8' value. Change the default character set of all fields to utf8 (ALTER TABLE can do this or just create a new table). Check if you're able to store and get correct data in mysql command line client. Thanks for the reply, by the way :) I also read your posts to the list with great pleasure :) Daniel Kasak wrote: Gleb Paharenko wrote: Hello. I've been able to insert Russian characters (utf8) through the QueryBrowser under Linux (FC4). Please, could you provide CREATE statement for you table CREATE TABLE `WebNewsPostings` ( `PRID` mediumint(8) unsigned NOT NULL auto_increment, `MyStamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `DateDay` tinyint(3) unsigned NOT NULL default '0', `DateMonth` tinyint(3) unsigned NOT NULL default '0', `DateYear` mediumint(8) unsigned NOT NULL default '0', `CopyTitle` varchar(255) character set latin1 NOT NULL default '', `CopyMain` mediumtext character set latin1 NOT NULL, `Image` varchar(50) character set latin1 default '', `Language` tinyint(3) unsigned NOT NULL default '0', PRIMARY KEY (`PRID`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 I don't know what those individual 'character set latin1' bits are. I haven't been messing with per-field character sets - I've just been using mysql-administrator to change the character set for the entire table. But anyway, the source *IS* latin1, so maybe this shouldn't be a problem anyway? and output of: show variables like '%char%'; mysql show variables like '%char%'; +--++ | Variable_name| Value | +--++ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_results| latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--++ 7 rows in set (0.00 sec) As far as I know, QueryBrowser invokes SET NAMES 'utf8' by default. And the only way to force it to use your connection character sets is to run a transaction. Are characters that you're pasting into QueryBrowser displayed correctly in the query tab? Yes. They display correctly - both in query browser AND in my Perl Gtk2 app. What's more, when I inspect the binary query log, the same sequence of reverse-coloured characters is used to represent the characters that are giving problems. Which ever method I use to get the data in ( query browser / in-house Perl Gtk2 app ), the data *looks* perfect when I paste it in, but when I return to look at it later it's corrupted. Thanks for the reply, by the way :) Dan -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Character set issue ( maybe )
Hello. I've been able to insert Russian characters (utf8) through the QueryBrowser under Linux (FC4). Please, could you provide CREATE statement for you table and output of: show variables like '%char%'; As far as I know, QueryBrowser invokes SET NAMES 'utf8' by default. And the only way to force it to use your connection character sets is to run a transaction. Are characters that you're pasting into QueryBrowser displayed correctly in the query tab? Daniel Kasak wrote: OK then. Lets re-word the question ... Has anyone been able to successfully enter text of a non-standard character set ( Latin 1, UTF8 ) into Query Browser? How about upload via a Perl script? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Character set issue ( maybe )
Octavian Rasnita wrote: From: Daniel Kasak [EMAIL PROTECTED] OK then. Lets re-word the question ... Has anyone been able to successfully enter text of a non-standard character set ( Latin 1, UTF8 ) into Query Browser? How about upload via a Perl script? Yes you can insert those chars in MySQL using a perl program, but you cannot do it using Windows' copy and paste from a web page. I think this is because of Windows clipboard that doesn't copy the text correctly. I'm running a Linux desktop :) -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Character set issue ( maybe )
Gleb Paharenko wrote: Hello. I've been able to insert Russian characters (utf8) through the QueryBrowser under Linux (FC4). Please, could you provide CREATE statement for you table CREATE TABLE `WebNewsPostings` ( `PRID` mediumint(8) unsigned NOT NULL auto_increment, `MyStamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `DateDay` tinyint(3) unsigned NOT NULL default '0', `DateMonth` tinyint(3) unsigned NOT NULL default '0', `DateYear` mediumint(8) unsigned NOT NULL default '0', `CopyTitle` varchar(255) character set latin1 NOT NULL default '', `CopyMain` mediumtext character set latin1 NOT NULL, `Image` varchar(50) character set latin1 default '', `Language` tinyint(3) unsigned NOT NULL default '0', PRIMARY KEY (`PRID`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 I don't know what those individual 'character set latin1' bits are. I haven't been messing with per-field character sets - I've just been using mysql-administrator to change the character set for the entire table. But anyway, the source *IS* latin1, so maybe this shouldn't be a problem anyway? and output of: show variables like '%char%'; mysql show variables like '%char%'; +--++ | Variable_name| Value | +--++ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_results| latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--++ 7 rows in set (0.00 sec) As far as I know, QueryBrowser invokes SET NAMES 'utf8' by default. And the only way to force it to use your connection character sets is to run a transaction. Are characters that you're pasting into QueryBrowser displayed correctly in the query tab? Yes. They display correctly - both in query browser AND in my Perl Gtk2 app. What's more, when I inspect the binary query log, the same sequence of reverse-coloured characters is used to represent the characters that are giving problems. Which ever method I use to get the data in ( query browser / in-house Perl Gtk2 app ), the data *looks* perfect when I paste it in, but when I return to look at it later it's corrupted. Thanks for the reply, by the way :) Dan -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Character set issue ( maybe )
Greetings. I'm trying to copy paste some data from a web page into MySQL ( and yes it's our data ). I'm getting problems with characters such as quotes, dollar / euro signs, etc, that won't import - I get symbols and stuff instead. If I right-click on the web page and select 'View Page Info', the dialog that appears tells me that the web page is using: Content-Type: text/html; charset=iso8859-1 I've searched on 8859-1 and it's 'Latin'. My MySQL ( 4.1.14 ) table was using the 'default' character set. I've had a look at the list of options in MySQL-Administrator, and there's no 'Latin' or 8859-1. I've got an 8859-2, so I tried that ... no luck. I've also tried using MySQL-Query-Browser, and my own home-grown Perl Gtk2 front-end to the tables - both produce exactly the same results. I'm using the latest version of Query Browser, and with my home-grown effort, I'm using DBD-mysql-2.9007, compiled against mysql-5.0.something. I've got gtk+-2.8.6. Anyone got any ideas? Is this a Gtk2 problem ( Query Browser and my app are using Gtk2 ), or is it a driver issue, or a table issue? -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Character set issue ( maybe )
Hi Daniel, I'm trying to copy paste some data from a web page into MySQL ( and How do you mean pasting into MySQL? Which program do you use on the destination side? You can use clipboard to copy into mysql (the console application), phpMyAdmin, some visual program, etc. Anyway, if your database is iso-8859-1 / iso-8859-2 (Latin1 / Latin2) coded, you won't really able to store euro sign and special quotes with it, as Latin character sets just contains 255 different characters and special quotes are not in these characters. You should use utf-8, or you can store these characters as HTML encodings (#8222;). Bye, Andras -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Character set issue ( maybe )
BÁRTHÁZI András wrote: Hi Daniel, I'm trying to copy paste some data from a web page into MySQL ( and How do you mean pasting into MySQL? Which program do you use on the destination side? Query browser *and* an in-house Perl Gtk2 app both produce the same results. I'm pretty sure I mentioned them both throughout my original post. You can use clipboard to copy into mysql (the console application), phpMyAdmin, some visual program, etc. Anyway, if your database is iso-8859-1 / iso-8859-2 (Latin1 / Latin2) coded, you won't really able to store euro sign and special quotes with it, as Latin character sets just contains 255 different characters and special quotes are not in these characters. You should use utf-8, or you can store these characters as HTML encodings (#8222;). I've also tried utf-8 now, both with Query Browser and our Perl Gtk2 app. This also produces exactly the same results. I can't go back to using the mysql console, as other people have to be able to do this, and they require a GUI. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Character set issue ( maybe )
OK then. Lets re-word the question ... Has anyone been able to successfully enter text of a non-standard character set ( Latin 1, UTF8 ) into Query Browser? How about upload via a Perl script? -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Character set issue ( maybe )
From: Daniel Kasak [EMAIL PROTECTED] OK then. Lets re-word the question ... Has anyone been able to successfully enter text of a non-standard character set ( Latin 1, UTF8 ) into Query Browser? How about upload via a Perl script? Yes you can insert those chars in MySQL using a perl program, but you cannot do it using Windows' copy and paste from a web page. I think this is because of Windows clipboard that doesn't copy the text correctly. For example, you can copy some special chars like s t a from a web page into a common text processor and you will see that they are not copied correctly. They are changed with other chars like ?. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
I'm having great trouble setting the default character set to utf8
No matter what I set in the my.ini option file, when I type show variables at the command line I get the following result: character_set_client | latin1 character_set_connection | latin1 character_set_database | utf8 character_set_results| latin1 character_set_server | utf8 character_set_system | utf8 collation_connection | latin1_swedish_ci collation_database | utf8_general_ci collation_server | utf8_general_ci I want ALL character sets everywhere to be utf8 at all times. I also want all collations to be utf8_general_ci everywhere and at all times. If I start the command line client like this: mysql -uroot --default-character-set=utf8 -p Then I get the result that I want. That is, ALL relevant variables are set to utf8 or utf8_general_ci. My question: how do I set the default character set in the options file? What do I have to do to set everything to utf8 once and for all? I have tried the variations listed below in the options file, and they don't work. I have tried them all individually and in combination. The only option seems to be setting the default character set at the command line, but I just don't want to have to do this every time, and it won't solve the illegal collation problems I get when I try to connect through JDBC (setting the characterEncoding variable to utf8 in the JDBC url doesn't seem to fix the problem either.). [mysqld] default-character-set=utf8 character-set-client=utf8 character-set-connection=utf8 character-set-results=utf8 collation-connection=utf8_general_ci [client] default-character-set=utf8 character-set-client=utf8 character-set-connection=utf8 character-set-results=utf8 collation-connection=utf8_general_ci [mysql] default-character-set=utf8 character-set-client=utf8 character-set-connection=utf8 character-set-results=utf8 collation-connection=utf8_general_ci [mysql-4.1.14] default-character-set=utf8 character-set-client=utf8 character-set-connection=utf8 character-set-results=utf8 collation-connection=utf8_general_ci [mysql-4.1.14-nt] default-character-set=utf8 character-set-client=utf8 character-set-connection=utf8 character-set-results=utf8 collation-connection=utf8_general_ci [mysql.server] default-character-set=utf8 character-set-client=utf8 character-set-connection=utf8 character-set-results=utf8 collation-connection=utf8_general_ci Given that none of the following options being set in the options file has any effect on the results of the show variables command, how do I get all my character sets set to utf8 at startup? You are probably thinking that I am playing with the wrong options file, but I am not. I have double checked, and changing other variables in the same options file does result in variables actually changing. I am running mysql 4.1.14-nt on Windows XP. Thanks for your help. Jon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to change table character set
Hello. Have a look here: http://dev.mysql.com/doc/mysql/en/charset-upgrading.html http://dev.mysql.com/doc/mysql/en/charset-conversion.html MightyData wrote: I have a database that was created with MySQL 4.0. The character set for each table is latin1. I have upgraded the server to MySQL 4.1. I would like to change the character set for each table to utf8. What is the correct procedure? Can I just change the table character set with an alter statement or do I need to export data, alter table, then import data? - Kirk Bowman Phone: 972-390-8600 MightyData, LLC http://www.mightydata.com FileMaker 7 Certified Developer FileMaker Authorized Trainer Check out our FileMaker 7 training classes! - -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to change table character set
I have a database that was created with MySQL 4.0. The character set for each table is latin1. I have upgraded the server to MySQL 4.1. I would like to change the character set for each table to utf8. What is the correct procedure? Can I just change the table character set with an alter statement or do I need to export data, alter table, then import data? - Kirk Bowman Phone: 972-390-8600 MightyData, LLC http://www.mightydata.com FileMaker 7 Certified Developer FileMaker Authorized Trainer Check out our FileMaker 7 training classes! - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Character Set Question
Need more information... what exactly is Our older version, and what exactly is the newest version of MySql, without this it's hard to know what issues you may be facing... I imagine you are on 4.0.n for the old and 4.1.n for the new... but we can't really tell from the information you gave us. When you run mysqldump you get an output file with everything in it... I suggest running mysqldump --tab=/var/tmp/somedirectory which will create a series of files in the location you specify, with a .sql file for each table with the create table command, and a .txt file for each table with the data in tab delimited format. This gives you an easy way to edit the create table statements to make sure each table has the character set information you really want in it before you import the data. Then you can do the import using cat *sql | mysql database to create the tables, and run mysqlimport against the .txt files to insert the data. Using this process you can more precisely manage your tables so they have the right character set for each column... you can do it by editing your regular mysqldump output file, but it's a big file and this way is just easier... it's also quicker to do the import this way. Be sure to dump the old database using the old mysqldump, that way if there was no character set information it won't put something in there by mistake (the new mysqldump could insert something of it's choosing if there is nothing defined)... be sure to use the new mysql client and mysqlimport to insert the data into the new version, making sure to use an appropriate --default-character-set setting each time you call it. Best Regards, Bruce On Aug 12, 2005, at 4:24 AM, James Sherwood wrote: Hello, We have installed the newest version of MySql and cannot get it to play nice with French characters. Our older version worked fine. The problem may (or may not) be that when we put the dump into the new database(yes its default charset is Utf8) the default character set for the table is Utf8 but some fields are like this: 'Story' longtext character set latin1 NOT NULL We tried linking our tomcat to the old database on the other server through this tomcat and everything works fine but when we link the tomcat back to the new database, it will not play nice with french characters. (they come out as outlined squares etc) Any ideas would be greatly appreciated James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Character Set Question
Hello, We have installed the newest version of MySql and cannot get it to play nice with French characters. Our older version worked fine. The problem may (or may not) be that when we put the dump into the new database(yes its default charset is Utf8) the default character set for the table is Utf8 but some fields are like this: 'Story' longtext character set latin1 NOT NULL We tried linking our tomcat to the old database on the other server through this tomcat and everything works fine but when we link the tomcat back to the new database, it will not play nice with french characters. (they come out as outlined squares etc) Any ideas would be greatly appreciated James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
migrate from 3.x to 4.1 character set problem
hi, we've got an old mysql-3.23.58 and a new mysql-4.1.10a and we'de like to migrate our data, but it doesn't seems to be so easy:-( out old server has a latin2 database. after we dump it and try tp import into the new ones we always got errors or the spical accented hungarian characters are getting wrong. - what is the prefered (and working) way to migrate from the old to the new? - how can define the new char sets? we try these variations (and manualy create the database with defult char set and latin2): 1. mysqldump --opt -p xxx xxx.sql mysql xxx xxx.sql 2. mysqldump --opt --default-character-set=latin2 -p xxx xxx.sql mysql --default-character-set=latin2 xxx xxx.sql 3. mysqldump --opt -p xxx xxx.sql iconv -f ISO_8859-2 -t UTF-8 -o xxx2.sql xxx.sql mysql xxx xxx2.sql and many more combination, try to read all docs, but can't find any solutions. another question what is the collations latin2_hungarian_ci contains? how can i interpret that xml file? eg. a is equal to á or not? is there any way to find out how is the buildin contains defined? or any description? thank you for your help in advance. yours. -- Levente Si vis pacem para bellum! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: migrate from 3.x to 4.1 character set problem
hi, we've got an old mysql-3.23.58 and a new mysql-4.1.10a and we'de like to migrate our data, but it doesn't seems to be so easy:-( out old server has a latin2 database. after we dump it and try tp import into the new ones we always got errors or the spical accented hungarian characters are getting wrong. - what is the prefered (and working) way to migrate from the old to the new? - how can define the new char sets? we try these variations (and manualy create the database with defult char set and latin2): 1. mysqldump --opt -p xxx xxx.sql mysql xxx xxx.sql 2. mysqldump --opt --default-character-set=latin2 -p xxx xxx.sql mysql --default-character-set=latin2 xxx xxx.sql 3. mysqldump --opt -p xxx xxx.sql iconv -f ISO_8859-2 -t UTF-8 -o xxx2.sql xxx.sql mysql xxx xxx2.sql and many more combination, try to read all docs, but can't find any solutions. another question what is the collations latin2_hungarian_ci contains? how can i interpret that xml file? eg. a is equal to á or not? is there any way to find out how is the buildin contains defined? or any description? thank you for your help in advance. yours. Generally speaking you need to define the character set for each column or table in your 4.1 database, or set a default character set for the database or for the server, this is independent of the default character set used by the clients... Then you need your clients to connect to the database using the appropriate character set... while the examples above seem correct, there are some opportunities for errors to occur. Firstly export the data using the mysql tools provided with 3.23.58... eg make sure you use the mysqldump that comes with the 3.23.58 mysql binary - chances are that is will be mysqldump 3.23.58. I expect that version off mysqldump will not support the --default- character-set flag and should have thrown an error if you try to give it that flag... It's important that you export the 3.23.58 data the way it is, and let the 4.1 tools deal with putting it into the new format appropriately. using mysqldump from 4.1 may not give you exactly the same results, so you should avoid that. Also for what it is worth you may want to try doing a dump slightly differently... we always use --tab=/var/tmp/database or some such thing and that creates a series of files in the folder you specify, one .sql file for each table containing just the create table statement, and one .txt file for each table containing just the data for each table in tab delimited format. It means your import process will be slightly different, but it's faster, and because we have done it regularly it's more likely to handle the data conversion. Next when doing the import make sure you use mysql tools that match the database you are installing. Here you will need to specify the default character set for the clients, they will understand and use that when speaking to the database. Here is the process we use to do the export from 4.0 and import into 4.1, there should be no great difference in how 3.23.58 and 4.0 handle the character sets so the results should be much the same. We use UTF8, and our 4.0 databases had no special character settings, so it was stored in the database as latin1. On the original server using 4.0.n server and tools to match we run this: mysqldump --tab=/var/tmp/database database You should be able to do the same thing provided you use mysqldump 3.23.58, again make no allowances for character set in the dump process, you just want the data dumped to disk the same way it is stored now. Then we move the directory /var/tmp/database to /var/tmp on the new server with 4.1 running... note this has the 4.1.n server AND the 4.1.n tools (such as mysql, mysqldump, mysqlimport and so on). Finally we go ahead and import our data into the server using this sequence of commands (we use a shell script, so that's what you get here). Call the shell script by giving it the database name as a flag (eg ./import database) - watch for differences in line breaks caused by email clients here, there are three lines of commands after setting DB=$1. #!/bin/sh # # LiveWorld's MySQL Import Script # Use for converting 4.0 databases to 4.1 UTF8 databases # Suitable for LiveWorld Servers only, use at your own risk # DB=$1 mysql -e CREATE DATABASE $DB default character set utf8; cat /var/tmp/$DB/*sql | mysql --socket=/tmp/mysql.sock $DB mysqlimport --default-character-set=utf8 $DB /var/tmp/$DB/*txt Obviously you are going from latin2 to latin2 so it should be a little easier for you than it was for us... and you'll want to make some changes in the script compared to our utf8 stuff (of course you may want to just go with utf8 anyway, should handle most anything you want to throw at it that way, our databases run in 30 languages). So be careful to match your tools with your server version and try
character set in MySQL 4.1
Dear all, I am a Chinese and using Chinese in my MySQL databases. On my old server, the version of MySQL is 3.23.58. And my new MySQL is 4.1.7. On my old server, the MySQL works well with my Chinese contents. However, after I transfer tables to the new server using: mysqldump --opt database | mysql -h 'newserver' database The new server can't display Chinese contents correctly. What need I do? What's more, I can't find gbk.xml and gb2312.xml at /usr/share/mysql/charsets, need I download them from somewhere? Thanks a lot, CB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: character set in MySQL 4.1
Hello. First read: http://dev.mysql.com/doc/mysql/en/charset.html mysqldump could put SET NAMES 'utf8' at the beginning of the dump file, check it and remove or perform a dump using --set-names=gbk. Use --defaults-character-set=gbk for mysql client (or put correct values for character_set_xxx variables in configuration file). Use show variables like '%char%'; to debug this issue. What's more, I can't find gbk.xml and gb2312.xml at /usr/share/mysql/charsets, need I download them from somewhere? Probably it is a compiled in character set. See README file in charsets directory. Chenzhou Cui [EMAIL PROTECTED] wrote: Dear all, I am a Chinese and using Chinese in my MySQL databases. On my old server, the version of MySQL is 3.23.58. And my new MySQL is 4.1.7. On my old server, the MySQL works well with my Chinese contents. However, after I transfer tables to the new server using: mysqldump --opt database | mysql -h 'newserver' database The new server can't display Chinese contents correctly. What need I do? What's more, I can't find gbk.xml and gb2312.xml at /usr/share/mysql/charsets, need I download them from somewhere? Thanks a lot, CB -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Character Set
I need in depth information on the character set used by MySQL including invisible/escape characters/codes. Is there online documentation that I can read on this? Thanks. - Asad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Character Set
Hello. This page contains information about escape characters: http://dev.mysql.com/doc/mysql/en/string-syntax.html The general information about character set could be found at: http://dev.mysql.com/doc/mysql/en/charset.html Asad Habib [EMAIL PROTECTED] wrote: I need in depth information on the character set used by MySQL including invisible/escape characters/codes. Is there online documentation that I can read on this? Thanks. - Asad -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Character set on 4.1 and ujis support
Hi, Thank you very much for the reply. $dbh-do(SET character_set_results=ujis'); This works! Is there any way I can set this value on MySQL config file, so I don't need to change all my scripts? Right now my my.cnf looks like: [mysqld] default-character-set=ujis default-collation=ujis_japanese_ci [client] default-character-set=ujis [mysql] default-character-set=ujis What do your 'show' statements return when you execute them from the perl script? character_set_clientujis character_set_connectionlatin1 character_set_database ujis character_set_results latin1 character_set_serverujis character_set_systemutf8 character_sets_dir /usr/share/mysql/charsets/ Why does character_set_connection set back to latin1? I already set the ujis value in my.cnf, and if I connect using mysql client, this value is correctly set to ujis. How can I set character_set_connection so it defaults to ujis, no matter where is the connection coming from? --bk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Character set on 4.1 and ujis support
Hi, I just moved my DB from 4.0 to 4.1.11. I used mysqldump to dump the data first, and then inserted it to the new DB. The character set of the data is EUC-JP (ujis). My problem is, I can see the character correctly if I connect to mysql server using mysql client. For example: # mysql -e SELECT name FROM USER LIMIT 1; test_db But, when I call it from Perl DBI, I get data with wrong character set (ex. The character displayed as ??? mark). Here is my setting: mysql show variables like %character%; +--++ | Variable_name| Value | +--++ | character_set_client | ujis | | character_set_connection | ujis | | character_set_database | ujis | | character_set_results| ujis | | character_set_server | ujis | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--++ 7 rows in set (0.01 sec) mysql show variables like %collation%; +--+--+ | Variable_name| Value| +--+--+ | collation_connection | ujis_japanese_ci | | collation_database | ujis_japanese_ci | | collation_server | ujis_japanese_ci | +--+--+ 3 rows in set (0.00 sec) What did I do wrong? How can I fix this problem? Thank you very much. --bk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Character set on 4.1 and ujis support
Hello. What do your 'show' statements return when you execute them from the perl script? Batara Kesuma [EMAIL PROTECTED] wrote: Hi, I just moved my DB from 4.0 to 4.1.11. I used mysqldump to dump the data first, and then inserted it to the new DB. The character set of the data is EUC-JP (ujis). My problem is, I can see the character correctly if I connect to mysql server using mysql client. For example: # mysql -e SELECT name FROM USER LIMIT 1; test_db But, when I call it from Perl DBI, I get data with wrong character set (ex. The character displayed as ??? mark). Here is my setting: mysql show variables like %character%; +--++ | Variable_name| Value | +--++ | character_set_client | ujis | | character_set_connection | ujis | | character_set_database | ujis | | character_set_results| ujis | | character_set_server | ujis | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--++ 7 rows in set (0.01 sec) mysql show variables like %collation%; +--+--+ | Variable_name| Value| +--+--+ | collation_connection | ujis_japanese_ci | | collation_database | ujis_japanese_ci | | collation_server | ujis_japanese_ci | +--+--+ 3 rows in set (0.00 sec) What did I do wrong? How can I fix this problem? Thank you very much. --bk -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Character set on 4.1 and ujis support
Hi, you may use somethinh lik ethis : $dbh-do(SET character_set_results=ujis'); look at http://dev.mysql.com/doc/mysql/en/charset-general.html Hope that helps Mathias Selon Gleb Paharenko [EMAIL PROTECTED]: Hello. What do your 'show' statements return when you execute them from the perl script? Batara Kesuma [EMAIL PROTECTED] wrote: Hi, I just moved my DB from 4.0 to 4.1.11. I used mysqldump to dump the data first, and then inserted it to the new DB. The character set of the data is EUC-JP (ujis). My problem is, I can see the character correctly if I connect to mysql server using mysql client. For example: # mysql -e SELECT name FROM USER LIMIT 1; test_db But, when I call it from Perl DBI, I get data with wrong character set (ex. The character displayed as ??? mark). Here is my setting: mysql show variables like %character%; +--++ | Variable_name| Value | +--++ | character_set_client | ujis | | character_set_connection | ujis | | character_set_database | ujis | | character_set_results| ujis | | character_set_server | ujis | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--++ 7 rows in set (0.01 sec) mysql show variables like %collation%; +--+--+ | Variable_name| Value| +--+--+ | collation_connection | ujis_japanese_ci | | collation_database | ujis_japanese_ci | | collation_server | ujis_japanese_ci | +--+--+ 3 rows in set (0.00 sec) What did I do wrong? How can I fix this problem? Thank you very much. --bk -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump : Character set '#33' is not a compiled character set
Hi It's my first time using mysqldump. [EMAIL PROTECTED] mysql]$ mysqldump -uroot --all-databases backup_test.sql mysqldump: File '/usr/share/mysql/charsets/?.conf' not found (Errcode: 2) mysqldump: Character set '#33' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index' file [EMAIL PROTECTED] mysql]$ [EMAIL PROTECTED] mysql]$ mysqldump -uroot --character-sets-dir=/usr/local/mysql/share/mysql/charsets --all-databases --default-character-set=utf8 backup_test.sql mysqldump: Character set 'utf8' is not a compiled character set and is not specified in the '/usr/local/mysql/share/mysql/charsets/Index' file [EMAIL PROTECTED] mysql]$ What can I do? Thanks Regards, Gu Lei --
Re: mysqldump : Character set '#33' is not a compiled character set
hi, look at : usr/bin/mysqldump --defaults-extra-file=/.../backup-credentials.cnf in http://dev.mysql.com/doc/mysql/en/mysqldump.html add the port, protocol,password mathias Selon Gu Lei [EMAIL PROTECTED]: Hi It's my first time using mysqldump. [EMAIL PROTECTED] mysql]$ mysqldump -uroot --all-databases backup_test.sql mysqldump: File '/usr/share/mysql/charsets/?.conf' not found (Errcode: 2) mysqldump: Character set '#33' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index' file [EMAIL PROTECTED] mysql]$ [EMAIL PROTECTED] mysql]$ mysqldump -uroot --character-sets-dir=/usr/local/mysql/share/mysql/charsets --all-databases --default-character-set=utf8 backup_test.sql mysqldump: Character set 'utf8' is not a compiled character set and is not specified in the '/usr/local/mysql/share/mysql/charsets/Index' file [EMAIL PROTECTED] mysql]$ What can I do? Thanks Regards, Gu Lei -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump : Character set '#33' is not a compiled character set
Hello. What version of MySQL do you use? Complete support for utf8 exists only in 4.1.xx and 5.x versions. Gu Lei [EMAIL PROTECTED] wrote: [-- text/plain, encoding 7bit, charset: us-ascii, 23 lines --] Hi It's my first time using mysqldump. [EMAIL PROTECTED] mysql]$ mysqldump -uroot --all-databases backup_test.sql mysqldump: File '/usr/share/mysql/charsets/?.conf' not found (Errcode: 2) mysqldump: Character set '#33' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index' file [EMAIL PROTECTED] mysql]$ [EMAIL PROTECTED] mysql]$ mysqldump -uroot --character-sets-dir=/usr/local/mysql/share/mysql/charsets --all-databases --default-character-set=utf8 backup_test.sql mysqldump: Character set 'utf8' is not a compiled character set and is not specified in the '/usr/local/mysql/share/mysql/charsets/Index' file [EMAIL PROTECTED] mysql]$ What can I do? Thanks Regards, Gu Lei -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump : Character set '#33' is not a compiled character set
Hello Gleb Paharenko Version is 4.1.10-max Regards Gu Lei Gleb Paharenko : Hello. What version of MySQL do you use? Complete support for utf8 exists only in 4.1.xx and 5.x versions. Gu Lei [EMAIL PROTECTED] wrote: [-- text/plain, encoding 7bit, charset: us-ascii, 23 lines --] Hi It's my first time using mysqldump. [EMAIL PROTECTED] mysql]$ mysqldump -uroot --all-databases backup_test.sql mysqldump: File '/usr/share/mysql/charsets/?.conf' not found (Errcode: 2) mysqldump: Character set '#33' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index' file [EMAIL PROTECTED] mysql]$ [EMAIL PROTECTED] mysql]$ mysqldump -uroot --character-sets-dir=/usr/local/mysql/share/mysql/charsets --all-databases --default-character-set=utf8 backup_test.sql mysqldump: Character set 'utf8' is not a compiled character set and is not specified in the '/usr/local/mysql/share/mysql/charsets/Index' file [EMAIL PROTECTED] mysql]$ What can I do? Thanks Regards, Gu Lei
Re: mysqldump : Character set '#33' is not a compiled character set
[EMAIL PROTECTED] : hi, look at : usr/bin/mysqldump --defaults-extra-file=/.../backup-credentials.cnf in http://dev.mysql.com/doc/mysql/en/mysqldump.html add the port, protocol,password mathias Selon Gu Lei [EMAIL PROTECTED]: Hi It's my first time using mysqldump. [EMAIL PROTECTED] mysql]$ mysqldump -uroot --all-databases backup_test.sql mysqldump: File '/usr/share/mysql/charsets/?.conf' not found (Errcode: 2) mysqldump: Character set '#33' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index' file [EMAIL PROTECTED] mysql]$ [EMAIL PROTECTED] mysql]$ mysqldump -uroot --character-sets-dir=/usr/local/mysql/share/mysql/charsets --all-databases --default-character-set=utf8 backup_test.sql mysqldump: Character set 'utf8' is not a compiled character set and is not specified in the '/usr/local/mysql/share/mysql/charsets/Index' file [EMAIL PROTECTED] mysql]$ What can I do? Thanks Regards, Gu Lei -- Hi Thanks. What does --defaults-extra-file mean? I've seen http://dev.mysql.com/doc/mysql/en/mysqldump.html. But I don't know what should --defaults-extra-file be set. Regards Gu Lei --
Re: mysqldump : Character set '#33' is not a compiled character set
Gu Lei ??: [EMAIL PROTECTED] ??: hi, look at : usr/bin/mysqldump --defaults-extra-file=/.../backup-credentials.cnf in http://dev.mysql.com/doc/mysql/en/mysqldump.html add the port, protocol,password mathias Selon Gu Lei [EMAIL PROTECTED]: Hi It's my first time using mysqldump. [EMAIL PROTECTED] mysql]$ mysqldump -uroot --all-databases backup_test.sql mysqldump: File '/usr/share/mysql/charsets/?.conf' not found (Errcode: 2) mysqldump: Character set '#33' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index' file [EMAIL PROTECTED] mysql]$ [EMAIL PROTECTED] mysql]$ mysqldump -uroot --character-sets-dir=/usr/local/mysql/share/mysql/charsets --all-databases --default-character-set=utf8 backup_test.sql mysqldump: Character set 'utf8' is not a compiled character set and is not specified in the '/usr/local/mysql/share/mysql/charsets/Index' file [EMAIL PROTECTED] mysql]$ What can I do? Thanks Regards, Gu Lei -- Hi Thanks. What does --defaults-extra-file mean? I've seen http://dev.mysql.com/doc/mysql/en/mysqldump.html. But I don't know what should --defaults-extra-file be set. Regards Gu Lei -- Sorry , it's my fault. There are two version of mysql on the same machine and I used the wrong one. Reagards, Gu Lei -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump : Character set '#33' is not a compiled character set
Gu Lei : Hello Gleb Paharenko Version is 4.1.10-max Regards Gu Lei Gleb Paharenko : Hello. What version of MySQL do you use? Complete support for utf8 exists only in 4.1.xx and 5.x versions. Gu Lei [EMAIL PROTECTED] wrote: [-- text/plain, encoding 7bit, charset: us-ascii, 23 lines --] Hi It's my first time using mysqldump. [EMAIL PROTECTED] mysql]$ mysqldump -uroot --all-databases backup_test.sql mysqldump: File '/usr/share/mysql/charsets/?.conf' not found (Errcode: 2) mysqldump: Character set '#33' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index' file [EMAIL PROTECTED] mysql]$ [EMAIL PROTECTED] mysql]$ mysqldump -uroot --character-sets-dir=/usr/local/mysql/share/mysql/charsets --all-databases --default-character-set=utf8 backup_test.sql mysqldump: Character set 'utf8' is not a compiled character set and is not specified in the '/usr/local/mysql/share/mysql/charsets/Index' file [EMAIL PROTECTED] mysql]$ What can I do? Thanks Regards, Gu Lei Sorry , it's my fault. There are two version of mysql on the same machine and I used the wrong one. Reagards, Gu Lei -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Character Set Problem
Is it possible to change the character set just for an individual table and if so which character set should I try to display this european characters? Cheers, Lee - Original Message - From: Sumito_Oda [EMAIL PROTECTED] To: Lee Denny [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, May 03, 2005 1:42 PM Subject: Re: Character Set Problem Hello, Is the MySQL server that you are using MySQL4.1.x or MySQL5.0.x? As for most binarys of PHP and MySQL, the default charset of the MySQL connection client is set as 'latin1'. Therefore, if charset with the server is not 'latin1', it is necessary to set the MySQL connection client properly. It is whether to set to use the charset that you use by default, to compile the binary or to set the MySQL connection first by 'SET NAMES' syntax. http://dev.mysql.com/doc/mysql/en/set-option.html Regards, -- Sumito_Oda mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Character Set Problem
Hello, this is probably quite simple but I've got a text file that has non-english characters, when I view it I see : 'Dcouvrez un rseau europen d'htels et de restaurants beignant dans une atmosphre conviviale et familliale' I've imported this straight into my myisam DB which is set up with default charsets and collations - and these characters are just the same. I've looked into this but can't really grasp charsets and collations. I'd be happy to translate these codes back into the default charset (english characters). I'm using PHP to query the database, but would like to change the data in the DB if possible. I know this is a bit vague but I was wondering if anyone had any insight into this. Cheers, Lee -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Character Set Problem
Hello, Is the MySQL server that you are using MySQL4.1.x or MySQL5.0.x? As for most binarys of PHP and MySQL, the default charset of the MySQL connection client is set as 'latin1'. Therefore, if charset with the server is not 'latin1', it is necessary to set the MySQL connection client properly. It is whether to set to use the charset that you use by default, to compile the binary or to set the MySQL connection first by 'SET NAMES' syntax. http://dev.mysql.com/doc/mysql/en/set-option.html Regards, -- Sumito_Oda mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Character Set problem
Hello. If you can reproduce this problem on several different installations, you may open a new bug (because #312 is closed) and leave there a note about bug #312. Stephen Moretti (cfmaster) [EMAIL PROTECTED] wrote: Gleb Paharenko wrote: Thanks for the reply. See: http://dev.mysql.com/doc/mysql/en/problems-with-character-sets.html Yeah Thanks - seen that already. Check that you have the charsets directory in c:\mysql\share. Again, thanks, but that doesn't actually solve the issue. There are entries in the Index file for the appropriate language number. There isn't, however, an xml file for the language (utf8 in this instance). I've tried changing the server default character set to cp1251. I've recreated complete databases from scratch making sure that the character set it uses is cp1251. None of the above have worked. Any other thoughts? This is mySQL 4.1.10-nt on win2003 server giving : File 'c:\mysql\share\charsets\?.conf' not found (Errcode: 22) ^GCharacter set '#33' is not a compiled character set and is not specified in the 'c:\mysql\share\charsets\Index' file which is classified as Bug number 312 (http://bugs.mysql.com/bug.php?id=312) Regards Stephen -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Character set information not found
Description: I installed MySQL-server-4.0.15-0 and am getting the following error message: 050330 12:09:32 [ERROR] Character set information not found in '/usr/share/mysql/english/errmsg.sys'. Please install the latest version of this file. 050330 12:09:32 [ERROR] Aborting How-To-Repeat: Any time I try to start mysql Fix: Submitter-Id: submitter ID Originator:root Organization: MySQL support: [none] Synopsis: english character set not found on startup Severity: Priority: Category: mysql Class: Release: mysql-4.0.15-standard (Official MySQL RPM) C compiler:2.95.3 C++ compiler: 2.95.3 Environment: System: Linux server.kenward.org 2.4.20-31.9 #1 Tue Apr 13 18:04:23 EDT 2004 i686 i686 i386 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2.2/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --with-system-zlib --enable-__cxa_atexit --host=i386-redhat-linux Thread model: posix gcc version 3.2.2 20030222 (Red Hat Linux 3.2.2-5) Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=i486 -fno-strength-reduce' CXX='g++' CXXFLAGS='-O2 -mcpu=i486 -fno-strength-reduce -felide-constructors -fno-exceptions -fno-rtti ' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Apr 4 2004 /lib/libc.so.6 - libc-2.3.2.so -rwxr-xr-x1 root root 1561228 Nov 12 2003 /lib/libc-2.3.2.so -rw-r--r--1 root root 2332200 Nov 12 2003 /usr/lib/libc.a -rw-r--r--1 root root 204 Nov 12 2003 /usr/lib/libc.so Configure command: ./configure '--disable-shared' '--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static' '--with-server-suffix=-standard' '--without-embedded-server' '--without-berkeley-db' '--with-innodb' '--without-vio' '--without-openssl' '--enable-assembler' '--enable-local-infile' '--with-mysqld-user=mysql' '--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--prefix=/' '--with-extra-charsets=complex' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--libdir=/usr/lib' '--sysconfdir=/etc' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql' '--infodir=/usr/share/info' '--includedir=/usr/include' '--mandir=/usr/share/man' '--enable-thread-safe-client' '--with-comment=Official MySQL RPM' 'CC=' 'CFLAGS=-O2 -mcpu=i486 -fno-strength-reduce' 'CXXFLAGS=-O2 -mcpu=i486 -fno-strength-reduce-felide-constructors -fno-exceptions -fno-rtti ' 'CXX=' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Character Set problem
Gleb Paharenko wrote: Thanks for the reply. See: http://dev.mysql.com/doc/mysql/en/problems-with-character-sets.html Yeah Thanks - seen that already. Check that you have the charsets directory in c:\mysql\share. Again, thanks, but that doesn't actually solve the issue. There are entries in the Index file for the appropriate language number. There isn't, however, an xml file for the language (utf8 in this instance). I've tried changing the server default character set to cp1251. I've recreated complete databases from scratch making sure that the character set it uses is cp1251. None of the above have worked. Any other thoughts? This is mySQL 4.1.10-nt on win2003 server giving : File 'c:\mysql\share\charsets\?.conf' not found (Errcode: 22) Character set '#33' is not a compiled character set and is not specified in the 'c:\mysql\share\charsets\Index' file which is classified as Bug number 312 (http://bugs.mysql.com/bug.php?id=312) Regards Stephen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]