View source for Issue:DPL SQL Error with MediaWIki 1.14

{{Issue
 |Type        = Bug
 |Extension   = DPL
 |Version     = 1.7.4
 |Description = SQL error
 |Status      = open
}}

==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.
 <nowiki>{{#dpl:namespace=|shownamespace=no}}</nowiki>
Produces this error.
<pre>
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) 
</pre>

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. [[User:Gero|Gero]] 21:05, 20 January 2009 (UTC)

== Resolution ==
I have been able to get DPL 1.7.4 to work with MW 1.14 svn r 45762. I believe the problem was that the MW upgrade script changed the database in a way that made the core MW tables incompatible with the existing DPL tables (which would have been untouched). To recover the situation here is the process steps that may help others.
*After 10.14 upgrade DPL is non-functional
*Use Special:Export to export all the articles to XML
*Drop database and run web /config page to set up from scratch
*Import articles
*Import images

Thanks for your response to my questions. --[[User:Rob|Rob]] 01:31, 22 January 2009 (UTC)

=== A less drastic resolution ===
I had this same problem moving from a 1.11 to a 1.13 and solved it by looping through all the tables setting the character set as in [http://www.organicdesign.co.nz/Change_the_character-set_of_a_wiki_database this procedure]. First '''drop prefix_depl_clview''', then change the character sets as shown in the procedure, and then put the DPL view back as shown in [[DPL:Manual - Source and Installation]]. --[[User:Nad|Nad]] 01:03, 28 January 2009 (UTC)