Difference between revisions of "Issue:DPL SQL Error with MediaWIki 1.14"

Revision as of 22:05, 20 January 2009

Note

This problem seems to be caused by character encoding the the mysql database. After trying and 1.13.3 MW with the same database, the same error occurred.

Problem

After upgrading to MW 1.14 DPL gives this error when the parser function is used.

{{#dpl:namespace=|shownamespace=no}}

Produces this error.

The DPL extension (version 1.7.4) 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 `page`.page_namespace as page_namespace,`page`.page_title as page_title, REPLACE(REPLACE(CONCAT( IF(`page`.page_namespace=0, , CONCAT(CASE `page`.page_namespace WHEN 1 THEN 'Talk' WHEN 2 THEN 'User' WHEN 3 THEN 'User_talk' WHEN 4 THEN 'Mathsreach' WHEN 5 THEN 'Mathsreach_talk' WHEN 6 THEN 'File' WHEN 7 THEN 'File_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' WHEN 503 THEN 'Navigation' WHEN 504 THEN 'Navigation_talk' END, ':')), `page`.page_title), '_', ' '),'♣','⣣') as sortkey FROM `page` WHERE 1=1 AND `page`.page_namespace IN ('0') AND `page`.page_is_redirect=0 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' (cs-db.cs.auckland.ac.nz) 

There seems to be a string encoding issue here. I can't be sure that DPL is the cause, but perhaps this error has been seen before. It may be a bleeding edge MW 1.14 issue.

In the code the character following the '♣' is another multibyte/encoded character that cannot render to the web page. Perhaps this gives a clue to the cause.

Reply

To make sure that the built-in card suit symbol conversion doesn't cause the harm you could remove the corresponding php source code line. It is easy to find.

Another way would be to copy/paste the above SQL statement to a mysql GUI client and see what happens there. It should create the same error. You can then modify the SQL statement until it works.

Gero 21:05, 20 January 2009 (UTC)