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?]
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]
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]
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]
Re: Running a query on multiple databases
That's exactly what a join does... join two or more tables as one, but you usually define a common field to join the tables by... If both tables have the same definition and you want to know how to display the data of one of them after the other you need to do a: select * from db1.table1 union select * from db2.table2; Andrés Villanueva Mahmoud Badreddine wrote: Hi, I would like to run a query on two tables that reside in two distinct databases: select * from db1.table1 join db2.table2; This lists both tables adjacent to one another rather than in a sequential fashion as one would get if both tables came from the same database. Is there a way to accomplish this task. Thank you. -- 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]
