Issue:Collation

Description:	Collation Error
Extension / Version:	DPL   /   1.6.3
Type / Status:	Bug   /   closed

Problem

This is the error I get:

The DPL extension (version 1.6.3) produced a SQL statement which lead to a Database error. The reason may be an internal error of DPL or an error which you made, especially when using DPL options like titleregexp. Query text is: SELECT DISTINCT `lwikipage`.page_namespace as page_namespace,`lwikipage`.page_title as page_title, REPLACE(REPLACE(CONCAT( IF(`lwikipage`.page_namespace=0, , CONCAT(CASE `lwikipage`.page_namespace WHEN 1 THEN 'Talk' WHEN 2 THEN 'User' WHEN 3 THEN 'User_talk' WHEN 4 THEN 'LabWiki' WHEN 5 THEN 'LabWiki_talk' WHEN 6 THEN 'Image' WHEN 7 THEN 'Image_talk' WHEN 8 THEN 'MediaWiki' WHEN 9 THEN 'MediaWiki_talk' WHEN 10 THEN 'Template' WHEN 11 THEN 'Template_talk' WHEN 12 THEN 'Help' WHEN 13 THEN 'Help_talk' WHEN 14 THEN 'Category' WHEN 15 THEN 'Category_talk' END, ':')), `lwikipage`.page_title), '_', ' '),'♣','⣣') as sortkey FROM `lwikitemplatelinks` as tl, `lwikipage` WHERE 1=1 AND `lwikipage`.page_is_redirect=0 AND `lwikipage`.page_id=tl.tl_from AND ((tl.tl_namespace=10 AND tl.tl_title='Todo')) AND `lwikipage`.page_id not in (select `lwikitemplatelinks`.tl_from from `lwikitemplatelinks` where ((`lwikitemplatelinks`.tl_namespace=10 AND `lwikitemplatelinks`.tl_title='Status_Legend')) ) ORDER BY sortkey ASC LIMIT 0, 500

Error message is: Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_bin,IMPLICIT) for operation 'concat' (localhost)

Now, I thought it was a simple issue of editing the file and changing ordercollation. But that just brought me the following:

The DPL extension (version 1.6.3) produced a SQL statement which lead to a Database error. The reason may be an internal error of DPL or an error which you made, especially when using DPL options like titleregexp. Query text is: SELECT DISTINCT `lwikipage`.page_namespace as page_namespace,`lwikipage`.page_title as page_title, REPLACE(REPLACE(CONCAT( IF(`lwikipage`.page_namespace=0, , CONCAT(CASE `lwikipage`.page_namespace WHEN 1 THEN 'Talk' WHEN 2 THEN 'User' WHEN 3 THEN 'User_talk' WHEN 4 THEN 'LabWiki' WHEN 5 THEN 'LabWiki_talk' WHEN 6 THEN 'Image' WHEN 7 THEN 'Image_talk' WHEN 8 THEN 'MediaWiki' WHEN 9 THEN 'MediaWiki_talk' WHEN 10 THEN 'Template' WHEN 11 THEN 'Template_talk' WHEN 12 THEN 'Help' WHEN 13 THEN 'Help_talk' WHEN 14 THEN 'Category' WHEN 15 THEN 'Category_talk' END, ':')), `lwikipage`.page_title), '_', ' '),'♣','⣣') latin1_swedish_ci as sortkey FROM `lwikitemplatelinks` as tl, `lwikipage` WHERE 1=1 AND `lwikipage`.page_is_redirect=0 AND `lwikipage`.page_id=tl.tl_from AND ((tl.tl_namespace=10 AND tl.tl_title='Todo')) AND `lwikipage`.page_id not in (select `lwikitemplatelinks`.tl_from from `lwikitemplatelinks` where ((`lwikitemplatelinks`.tl_namespace=10 AND `lwikitemplatelinks`.tl_title='Status_Legend')) ) ORDER BY sortkey ASC LIMIT 0, 500

Error message is: 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 'as sortkey FROM `lwikitemplatelinks` as tl, `lwikipage` WHERE 1=1 AND `lwikipa' at line 1 (localhost)

I would really appreciate any help.

I'm getting the pretty much the same error:

Error in numRows(): Illegal mix of collations (utf8_general_ci,COERCIBLE) 
and (latin1_bin,IMPLICIT) for operation 'concat' (localhost)

I installed DPL to support the support TODO Extension. I had no problems with my WAMP installation, but when I made the same changes in the Linux server, the above error popped up. The MySQL wiki tables are all latin1_swedish_ci on the Linux server.

Mediawiki: 1.13
DPL: 1.7.5
MySQL: 5.0.45
PHP: 5.2.6

--Samtediou 23:01, 29 December 2008 (UTC)

Reply

Does the error happen with all DPL statements which produce SQL code that conatins the CONCAT? Generally I would recommend that you

1. strip down the DPL statement as far as possible while you still get the error message
2. try to modify the SQL statement within phpmysql (or some other database client) until it works
3. try to delete the literal part with the card suit symbols; this aims at the suit ranking in the card game of bridge but will probably not be important in most cases.

Gero 19:16, 26 December 2007 (CET)

1. Not sure how to do this exactly.
2. I tried this and I'm pretty sure the problem is where DPL puts the collation in the statement. I'm not sure where it is supposed to go though...
3. I tried this and it didn't work. I think #2 is going to be the answer, I just need to figure out (With any help appreciated) where collation goes in that statement...

Thanks, --Ecogeek 21:44, 26 December 2007 (CET)