Re: collation problems
can u please list out the table structure...as collation can also be set at column level regards anandkl On Tue, Aug 31, 2010 at 6:00 PM, mysql my...@ayni.com wrote: Hi listers mysql server here is mysql-server-5.1.48-2.fc13.x86_64 this morning i created a message with a literal string in chinese in it. the messages in the application i used are stored in a mysql database, when you submit them, like in a sent folder. With this chinese literal in it, i, however, got ERROR 1271 (HY000): Illegal mix of collations for operation 'concat' when i sent the message. without the chinese literal, the message was stored in the mysql db correctly. i, then, changed the connection names to utf8 and collation to utf8_bin and then the session parameters looked like: mysql set names utf8; Query OK, 0 rows affected (0.00 sec) mysql set collation_connection = utf8_bin; Query OK, 0 rows affected (0.00 sec) mysql show session variables like character_set%; +--++ | Variable_name| Value | +--++ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results| utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--++ 8 rows in set (0.00 sec) mysql show session variables like collation%; +--+---+ | Variable_name| Value | +--+---+ | collation_connection | utf8_bin | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +--+---+ 3 rows in set (0.00 sec) mysql i thought, if you set the collation_connection to utf8_bin, you may send fairly everything to mysql. but still, when i tried to concat the new string (including the chinese characers) i got: mysql update suomi_contacts2 set history = concat(now(), ' ', '' ,'concerne: utf-8-bin collation for chinese charset',' ', 'Hoi Suomixer,\r\n\r\nIf you\'re careful enough, nothing bad or good will ever happen to you.\r\n 葛斯克 愛德華 / 台北市八德路四段\r\n\r\n\r\n \r\nsuomi\r\n', ' ', '--- ', history) where counter = 1127; ERROR 1271 (HY000): Illegal mix of collations for operation 'concat' Question: What did i miss? what do i have to change? Thanks in advance suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com
Re: collation problems
Hi Ananda table structure is: mysql show full columns from suomi_contacts2; +--+--+---+--+-+---+-+-+-+ | Field| Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +--+--+---+--+-+---+-+-+-+ | name | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | firm | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | title| text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | phone| text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | phone_std| text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | fax | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | mail | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | standard_mail| text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | comment | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | status | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | url | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | businesscategory | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | address | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | addon| text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | givenname| text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | history | longtext | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | favorit | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | last_update | timestamp| NULL | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | select,insert,update,references | | | task_link| int(11) | NULL | YES | | NULL | | select,insert,update,references | | | counter | int(10) unsigned | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | | +--+--+---+--+-+---+-+-+-+ 20 rows in set (0.00 sec) mysql suomi On 2010-08-31 14:52, Ananda Kumar wrote: can u please list out the table structure...as collation can also be set at column level regards anandkl On Tue, Aug 31, 2010 at 6:00 PM, mysql my...@ayni.com mailto:my...@ayni.com wrote: Hi listers mysql server here is mysql-server-5.1.48-2.fc13.x86_64 this morning i created a message with a literal string in chinese in it. the messages in the application i used are stored in a mysql database, when you submit them, like in a sent folder. With this chinese literal in it, i, however, got ERROR 1271 (HY000): Illegal mix of collations for operation 'concat' when i sent the message. without the chinese literal, the message was stored in the mysql db correctly. i, then, changed the connection names to utf8 and collation to utf8_bin and then the
Re: collation problems
can u please give the output of desc suomi_contacts2; 2010/8/31 mysql my...@ayni.com Hi Ananda table structure is: mysql show full columns from suomi_contacts2; +--+--+---+--+-+---+-+-+-+ | Field| Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +--+--+---+--+-+---+-+-+-+ | name | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | firm | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | title| text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | phone| text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | phone_std| text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | fax | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | mail | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | standard_mail| text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | comment | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | status | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | url | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | businesscategory | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | address | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | addon| text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | givenname| text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | history | longtext | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | favorit | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | last_update | timestamp| NULL | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | select,insert,update,references | | | task_link| int(11) | NULL | YES | | NULL | | select,insert,update,references | | | counter | int(10) unsigned | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | | +--+--+---+--+-+---+-+-+-+ 20 rows in set (0.00 sec) mysql suomi On 2010-08-31 14:52, Ananda Kumar wrote: can u please list out the table structure...as collation can also be set at column level regards anandkl On Tue, Aug 31, 2010 at 6:00 PM, mysql my...@ayni.com mailto:my...@ayni.com wrote: Hi listers mysql server here is mysql-server-5.1.48-2.fc13.x86_64 this morning i created a message with a literal string in chinese in it. the messages in the application i used are stored in a mysql database, when you submit them, like in a sent folder. With this chinese literal in it, i, however, got ERROR 1271 (HY000): Illegal mix of collations for operation 'concat' when i sent the message. without
Re: collation problems
On 2010-08-31 15:17, Ananda Kumar wrote: desc suomi_contacts2; mysql desc suomi_contacts2; +--+--+--+-+---+-+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+---+-+ | name | text | YES | | NULL | | | firm | text | YES | | NULL | | | title| text | YES | | NULL | | | phone| text | YES | | NULL | | | phone_std| text | YES | | NULL | | | fax | text | YES | | NULL | | | mail | text | YES | | NULL | | | standard_mail| text | YES | | NULL | | | comment | text | YES | | NULL | | | status | text | YES | | NULL | | | url | text | YES | | NULL | | | businesscategory | text | YES | | NULL | | | address | text | YES | | NULL | | | addon| text | YES | | NULL | | | givenname| text | YES | | NULL | | | history | longtext | YES | | NULL | | | favorit | text | YES | | NULL | | | last_update | timestamp| NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | task_link| int(11) | YES | | NULL | | | counter | int(10) unsigned | NO | PRI | NULL | auto_increment | +--+--+--+-+---+-+ 20 rows in set (0.00 sec) mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: collation problems
did u try changing the collation for history column to UTF8 and try the update. 2010/8/31 mysql my...@ayni.com On 2010-08-31 15:17, Ananda Kumar wrote: desc suomi_contacts2; mysql desc suomi_contacts2; +--+--+--+-+---+-+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+---+-+ | name | text | YES | | NULL | | | firm | text | YES | | NULL | | | title| text | YES | | NULL | | | phone| text | YES | | NULL | | | phone_std| text | YES | | NULL | | | fax | text | YES | | NULL | | | mail | text | YES | | NULL | | | standard_mail| text | YES | | NULL | | | comment | text | YES | | NULL | | | status | text | YES | | NULL | | | url | text | YES | | NULL | | | businesscategory | text | YES | | NULL | | | address | text | YES | | NULL | | | addon| text | YES | | NULL | | | givenname| text | YES | | NULL | | | history | longtext | YES | | NULL | | | favorit | text | YES | | NULL | | | last_update | timestamp| NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | task_link| int(11) | YES | | NULL | | | counter | int(10) unsigned | NO | PRI | NULL | auto_increment | +--+--+--+-+---+-+ 20 rows in set (0.00 sec) mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com
Re: collation problems
Hi Ananda not sofar. But if you recommend it, i will give it a try. thanks so much. suomi On 2010-08-31 15:41, Ananda Kumar wrote: did u try changing the collation for history column to UTF8 and try the update. 2010/8/31 mysql my...@ayni.com mailto:my...@ayni.com On 2010-08-31 15:17, Ananda Kumar wrote: desc suomi_contacts2; mysql desc suomi_contacts2; +--+--+--+-+---+-+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+---+-+ | name | text | YES | | NULL | | | firm | text | YES | | NULL | | | title| text | YES | | NULL | | | phone| text | YES | | NULL | | | phone_std| text | YES | | NULL | | | fax | text | YES | | NULL | | | mail | text | YES | | NULL | | | standard_mail| text | YES | | NULL | | | comment | text | YES | | NULL | | | status | text | YES | | NULL | | | url | text | YES | | NULL | | | businesscategory | text | YES | | NULL | | | address | text | YES | | NULL | | | addon| text | YES | | NULL | | | givenname| text | YES | | NULL | | | history | longtext | YES | | NULL | | | favorit | text | YES | | NULL | | | last_update | timestamp| NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | task_link| int(11) | YES | | NULL | | | counter | int(10) unsigned | NO | PRI | NULL | auto_increment | +--+--+--+-+---+-+ 20 rows in set (0.00 sec) mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=anan...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
[SOLVED] Re: collation problems
Hi Ananda that worked fine: mysql alter table suomi_contacts2 modify history longtext character set utf8 collate utf8_bin; Query OK, 6327 rows affected (0.34 sec) Records: 6327 Duplicates: 0 Warnings: 0 mysql mysql update suomi_contacts2 set history = concat(now(), ' ', '' ,'concerne: utf-8-bin collation for chinese charset',' ', 'Hoi Suomixer,\r\n\r\nIf you\'re careful enough, nothing bad or good will ever happen to you.\r\n 葛斯克 愛德華 / 台北市八德路四段\r\n\r\n\r\n\r \nsuomi\r\n044 280 22 44\r\n079 239 29 01\r\n', ' ', '--- ', history) where counter = 1127; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql Thank you very much suomi On 2010-08-31 15:41, Ananda Kumar wrote: did u try changing the collation for history column to UTF8 and try the update. 2010/8/31 mysql my...@ayni.com mailto:my...@ayni.com On 2010-08-31 15:17, Ananda Kumar wrote: desc suomi_contacts2; mysql desc suomi_contacts2; +--+--+--+-+---+-+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+---+-+ | name | text | YES | | NULL | | | firm | text | YES | | NULL | | | title| text | YES | | NULL | | | phone| text | YES | | NULL | | | phone_std| text | YES | | NULL | | | fax | text | YES | | NULL | | | mail | text | YES | | NULL | | | standard_mail| text | YES | | NULL | | | comment | text | YES | | NULL | | | status | text | YES | | NULL | | | url | text | YES | | NULL | | | businesscategory | text | YES | | NULL | | | address | text | YES | | NULL | | | addon| text | YES | | NULL | | | givenname| text | YES | | NULL | | | history | longtext | YES | | NULL | | | favorit | text | YES | | NULL | | | last_update | timestamp| NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | task_link| int(11) | YES | | NULL | | | counter | int(10) unsigned | NO | PRI | NULL | auto_increment | +--+--+--+-+---+-+ 20 rows in set (0.00 sec) mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=anan...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [Fwd: Re: Collation problems or messed joins?]
Is it possible your data was changed during upgrade? How have you performed it? Andr$s Villanueva [EMAIL PROTECTED] wrote: Hi! thanks for your response. This are the values of the variables you asked for: collation_connection: utf8_general_ci collation_database: utf8_general_ci collation_server: utf8_general_ci character_set_client: utf8 character_set_connection: utf8 character_set_database: utf8 character_set_results: NULL character_set_server: utf8 character_set_system: utf8 These were taken from a query inside the app. From the query browser the only difference is: character_set_results: utf8 Anyway, the same query has the same results in the app and the query browser. The app is using the .net connector Thanks Andr$s Villanueva Gleb Paharenko wrote: Hello. Please, send the output of the following statements: show variables like '%colla%'; show variables like '%char%'; Andr$s Villanueva [EMAIL PROTECTED] wrote: Anyone?? Original Message The tables are now entirely in utf8, and that is also the instance's default. I'm using the .net dll to connect and the connection string has charset = utf8. I'm testing everything with the query browser, and i get the exact same behaviour. Again, if anyone wants a script to recreate the tables with some data, I can send it to you instantly. Thanks Andr$s Villanueva Kevin Cowley wrote: Have you check that the collation for the text columns match the collation for the table and that you've set UTF8 for the query (set char set utf8). Kevin Cowley Product Development Alchemetrics Ltd SMARTER DATA , FASTER Tel: 0118 902 9000 (swithcboard) Tel: 0118 902 9099 (direct) Web: www.alchemetrics.co.uk Email: [EMAIL PROTECTED] -- 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: [Fwd: Re: Collation problems or messed joins?]
Actually, I did a full backup, uninstalled everything, installed a clean 4.1.11 and restored the backup The data seems to be fine. The funny thing is that if i change one of the inners for a left everything works fine! I think it's a bug, and an ugly one... Andrés Villanueva Gleb Paharenko wrote: Is it possible your data was changed during upgrade? How have you performed it? Andr$s Villanueva [EMAIL PROTECTED] wrote: Hi! thanks for your response. This are the values of the variables you asked for: collation_connection: utf8_general_ci collation_database: utf8_general_ci collation_server: utf8_general_ci character_set_client: utf8 character_set_connection: utf8 character_set_database: utf8 character_set_results: NULL character_set_server: utf8 character_set_system: utf8 These were taken from a query inside the app. From the query browser the only difference is: character_set_results: utf8 Anyway, the same query has the same results in the app and the query browser. The app is using the .net connector Thanks Andr$s Villanueva Gleb Paharenko wrote: Hello. Please, send the output of the following statements: show variables like '%colla%'; show variables like '%char%'; Andr$s Villanueva [EMAIL PROTECTED] wrote: Anyone?? Original Message The tables are now entirely in utf8, and that is also the instance's default. I'm using the .net dll to connect and the connection string has charset = utf8. I'm testing everything with the query browser, and i get the exact same behaviour. Again, if anyone wants a script to recreate the tables with some data, I can send it to you instantly. Thanks Andr$s Villanueva Kevin Cowley wrote: Have you check that the collation for the text columns match the collation for the table and that you've set UTF8 for the query (set char set utf8). Kevin Cowley Product Development Alchemetrics Ltd SMARTER DATA , FASTER Tel: 0118 902 9000 (swithcboard) Tel: 0118 902 9099 (direct) Web: www.alchemetrics.co.uk Email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Fwd: Re: Collation problems or messed joins?]
Hello. Please, send the output of the following statements: show variables like '%colla%'; show variables like '%char%'; Andr$s Villanueva [EMAIL PROTECTED] wrote: Anyone?? Original Message The tables are now entirely in utf8, and that is also the instance's default. I'm using the .net dll to connect and the connection string has charset = utf8. I'm testing everything with the query browser, and i get the exact same behaviour. Again, if anyone wants a script to recreate the tables with some data, I can send it to you instantly. Thanks Andr$s Villanueva Kevin Cowley wrote: Have you check that the collation for the text columns match the collation for the table and that you've set UTF8 for the query (set char set utf8). Kevin Cowley Product Development Alchemetrics Ltd SMARTER DATA , FASTER Tel: 0118 902 9000 (swithcboard) Tel: 0118 902 9099 (direct) Web: www.alchemetrics.co.uk Email: [EMAIL PROTECTED] -- 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: [Fwd: Re: Collation problems or messed joins?]
Hi! thanks for your response. This are the values of the variables you asked for: collation_connection: utf8_general_ci collation_database: utf8_general_ci collation_server: utf8_general_ci character_set_client: utf8 character_set_connection: utf8 character_set_database: utf8 character_set_results: NULL character_set_server: utf8 character_set_system: utf8 These were taken from a query inside the app. From the query browser the only difference is: character_set_results: utf8 Anyway, the same query has the same results in the app and the query browser. The app is using the .net connector Thanks Andrés Villanueva Gleb Paharenko wrote: Hello. Please, send the output of the following statements: show variables like '%colla%'; show variables like '%char%'; Andr$s Villanueva [EMAIL PROTECTED] wrote: Anyone?? Original Message The tables are now entirely in utf8, and that is also the instance's default. I'm using the .net dll to connect and the connection string has charset = utf8. I'm testing everything with the query browser, and i get the exact same behaviour. Again, if anyone wants a script to recreate the tables with some data, I can send it to you instantly. Thanks Andr$s Villanueva Kevin Cowley wrote: Have you check that the collation for the text columns match the collation for the table and that you've set UTF8 for the query (set char set utf8). Kevin Cowley Product Development Alchemetrics Ltd SMARTER DATA , FASTER Tel: 0118 902 9000 (swithcboard) Tel: 0118 902 9099 (direct) Web: www.alchemetrics.co.uk Email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Collation problems or messed joins?
Have you check that the collation for the text columns match the collation for the table and that you've set UTF8 for the query (set char set utf8). Kevin Cowley Product Development Alchemetrics Ltd SMARTER DATA , FASTER Tel: 0118 902 9000 (swithcboard) Tel: 0118 902 9099 (direct) Web: www.alchemetrics.co.uk Email: [EMAIL PROTECTED] -Original Message- From: Andrés Villanueva [mailto:[EMAIL PROTECTED] Sent: 03 May 2005 15:33 To: mysql@lists.mysql.com Subject: Collation problems or messed joins? Hi everyone! I'm havng the weirdest problem with mysql. I had v1.1.07 and everything worked like a charm. But it turns out that we had the need to move everything to UTF8 (until then, only the columns in russian where set to utf8, and the rest was latin1). Once we moved everything to utf8, this (the query is at the end of the email) didn't work anymore, apparently because of a collation problem with the literals. After doing some research i found that this wasn't a problem anymore in 4.1.11, so i decided to upgrade... After upgrading the query doesn't throw an error, but it doesn't return all the data it should... The funny thing is that i thought that the problem was in the 'case's that i use in the query, so i remove them from the query and the query runs fine. But later, i tried something else, and insetead of removing the cases y switched the inners for left joins, and the query worked fine, just like before, and it turns out it doesn't even matter wether I switch both joins or just one of them (either one or the other). If anybody can test this, and needs more data, or needs a script with the tables and data, I'll be glad to send it. Thanks!! Andrés Villanueva This is the problematic query: SELECT paper_id, Number, Revision, b.type, c.type_en as type2, a.name_en as name , case name_en when '' then 'N' else 'Y' end as name_en , case name_fr when '' then 'N' else 'Y' end as name_fr , case name_ru when '' then 'N' else 'Y' end as name_ru , case name_sp when '' then 'N' else 'Y' end as name_sp FROM `tpatcm28_paper` a inner join `tpgen_fmt_type` b using (fmt_type_id) inner join `tpatcm_pap_type` c on (a.pap_type_id = c.pap_type_id) order by Number, Revision -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Collation problems or messed joins?
The tables are now entirely in utf8, and that is also the instance's default. I'm using the .net dll to connect and the connection string has charset = utf8. I'm testing everything with the query browser, and i get the exact same behaviour. Again, if anyone wants a script to recreate the tables with some data, I can send it to you instantly. Thanks Andrés Villanueva Kevin Cowley wrote: Have you check that the collation for the text columns match the collation for the table and that you've set UTF8 for the query (set char set utf8). Kevin Cowley Product Development Alchemetrics Ltd SMARTER DATA , FASTER Tel: 0118 902 9000 (swithcboard) Tel: 0118 902 9099 (direct) Web: www.alchemetrics.co.uk Email: [EMAIL PROTECTED] -Original Message- From: Andrés Villanueva [mailto:[EMAIL PROTECTED] Sent: 03 May 2005 15:33 To: mysql@lists.mysql.com Subject: Collation problems or messed joins? Hi everyone! I'm havng the weirdest problem with mysql. I had v1.1.07 and everything worked like a charm. But it turns out that we had the need to move everything to UTF8 (until then, only the columns in russian where set to utf8, and the rest was latin1). Once we moved everything to utf8, this (the query is at the end of the email) didn't work anymore, apparently because of a collation problem with the literals. After doing some research i found that this wasn't a problem anymore in 4.1.11, so i decided to upgrade... After upgrading the query doesn't throw an error, but it doesn't return all the data it should... The funny thing is that i thought that the problem was in the 'case's that i use in the query, so i remove them from the query and the query runs fine. But later, i tried something else, and insetead of removing the cases y switched the inners for left joins, and the query worked fine, just like before, and it turns out it doesn't even matter wether I switch both joins or just one of them (either one or the other). If anybody can test this, and needs more data, or needs a script with the tables and data, I'll be glad to send it. Thanks!! Andrés Villanueva This is the problematic query: SELECT paper_id, Number, Revision, b.type, c.type_en as type2, a.name_en as name , case name_en when '' then 'N' else 'Y' end as name_en , case name_fr when '' then 'N' else 'Y' end as name_fr , case name_ru when '' then 'N' else 'Y' end as name_ru , case name_sp when '' then 'N' else 'Y' end as name_sp FROM `tpatcm28_paper` a inner join `tpgen_fmt_type` b using (fmt_type_id) inner join `tpatcm_pap_type` c on (a.pap_type_id = c.pap_type_id) order by Number, Revision -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Fwd: Re: Collation problems or messed joins?]
Anyone?? Original Message The tables are now entirely in utf8, and that is also the instance's default. I'm using the .net dll to connect and the connection string has charset = utf8. I'm testing everything with the query browser, and i get the exact same behaviour. Again, if anyone wants a script to recreate the tables with some data, I can send it to you instantly. Thanks Andrés Villanueva Kevin Cowley wrote: Have you check that the collation for the text columns match the collation for the table and that you've set UTF8 for the query (set char set utf8). Kevin Cowley Product Development Alchemetrics Ltd SMARTER DATA , FASTER Tel: 0118 902 9000 (swithcboard) Tel: 0118 902 9099 (direct) Web: www.alchemetrics.co.uk Email: [EMAIL PROTECTED] -Original Message- From: Andrés Villanueva [mailto:[EMAIL PROTECTED] Sent: 03 May 2005 15:33 To: mysql@lists.mysql.com Subject: Collation problems or messed joins? Hi everyone! I'm havng the weirdest problem with mysql. I had v1.1.07 and everything worked like a charm. But it turns out that we had the need to move everything to UTF8 (until then, only the columns in russian where set to utf8, and the rest was latin1). Once we moved everything to utf8, this (the query is at the end of the email) didn't work anymore, apparently because of a collation problem with the literals. After doing some research i found that this wasn't a problem anymore in 4.1.11, so i decided to upgrade... After upgrading the query doesn't throw an error, but it doesn't return all the data it should... The funny thing is that i thought that the problem was in the 'case's that i use in the query, so i remove them from the query and the query runs fine. But later, i tried something else, and insetead of removing the cases y switched the inners for left joins, and the query worked fine, just like before, and it turns out it doesn't even matter wether I switch both joins or just one of them (either one or the other). If anybody can test this, and needs more data, or needs a script with the tables and data, I'll be glad to send it. Thanks!! Andrés Villanueva This is the problematic query: SELECT paper_id, Number, Revision, b.type, c.type_en as type2, a.name_en as name , case name_en when '' then 'N' else 'Y' end as name_en , case name_fr when '' then 'N' else 'Y' end as name_fr , case name_ru when '' then 'N' else 'Y' end as name_ru , case name_sp when '' then 'N' else 'Y' end as name_sp FROM `tpatcm28_paper` a inner join `tpgen_fmt_type` b using (fmt_type_id) inner join `tpatcm_pap_type` c on (a.pap_type_id = c.pap_type_id) order by Number, Revision -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
