Illegal mix of collations

First thing to do is check each step in the handbook: Adding nodes to your site map.

Maybe there is an error in the database. Try uninstalling: Uninstalling Drupal modules. Also check your Drupal error log.

Renamed to prevent duplicate issues.

Hi Tarlow,

If you paste this query into PHPMyAdmin and run it *on an empty database to ensure it fails*, it will redisplay the query as shown below, making it a little easier to read.

SELECT n.nid, n.type, n.promote, s.comment_count, n.changed, xn.previously_changed, s.last_comment_timestamp, xn.previous_comment, xn.priority_override, COALESCE( ua.dst ) AS alias
FROM node n
LEFT JOIN node_comment_statistics s ON n.nid = s.nid
LEFT JOIN url_alias ua ON ua.src = CONCAT( 'node/', CAST( n.nid AS CHAR ) )
LEFT JOIN xmlsitemap_node xn ON n.nid = xn.nid
INNER JOIN node_access na ON na.nid = n.nid
WHERE (
na.grant_view >=1
AND (
(
na.gid =0
AND na.realm = 'all'
)
OR (
na.gid =1
AND na.realm = 'forum_access'
)
)
)
AND (
n.status >0
AND (
n.type NOT
IN (
''
)
AND xn.priority_override IS NULL
OR xn.priority_override >=0
)
AND n.nid <>49
)
GROUP BY n.nid, n.type, n.promote, s.comment_count, n.changed, xn.previously_changed, s.last_comment_timestamp, xn.previous_comment, xn.priority_override

The important bit for this issue is which tables are being used and what their collation is. The tables are in the section near the top, between the words FROM and WHERE.

I suggest you use PHPMyAdmin or other database admin tool to check the collation for each of the tables in this query in your site's database.

Tables:

node
node_comment_statistics
url_alias
xmlsitemap_node
node_access

If you can list the collation of these tables it might help Darren understand where the problem is and if it's being caused by XML sitemap.

@ Darren: Fantastic module by the way :)

Setting it to fixed because there have been no feedbacks in the past two months.

Sorry, but figured anyone out HOW TO FIX this issue? I have the same message on screen after any node update or add:

Illegal mix of collations (utf8_czech_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' query: INSERT INTO drupal_xmlsitemap_node (nid, pid, last_changed, priority_override) SELECT 906, ua.pid, 1228921492, NULL FROM drupal_node n LEFT JOIN drupal_url_alias ua ON ua.src = CONCAT('node/', CAST(906 AS CHAR)) LIMIT 1 v souboru /data/home/zakaznici/timurova.com/www/includes/database.mysql.inc

Also, no new content appear in sitemap.xml :-/

I guess the problem is caused by CONCAT(). If the SQL query would not use that function, then there would not be that error.

I'm getting the same error message in 5.x-1.6:

user warning: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' query: UPDATE xmlsitemap_node xn INNER JOIN url_alias ua ON ua.src = CONCAT('node/', CAST(xn.nid AS CHAR)) SET xn.pid = ua.pid WHERE xn.pid IS NULL in /<...>/includes/database.mysql.inc on line 174.

I just moved the whole site (files & database) to an new server. Before (using 5.x-1.4), I didn't get that message.

I can see in phpmyadmin that the two xmlsitemap tables (xmlsitemap_additional and xmlsitemap_node) are utf8_general_ci, while the rest of the site's tables are utf8_unicode_ci (it was like that before, too). When I change it, the problem persists. When I uninstall the module and reactivate it, the tables are in utf8_general_ci again, the problem persists.

Edit: The message doesn't show up anymore when I downgrade to 5.x-1.4 but persists in 5.x-1.5

The referring version is 5.x-2.x-dev because it's the only Drupal 5 version that eventually will get any fixes, or any other modifies in the code. Any additional comments about the 5.x-1.6 version is therefore useless.

The postponed status doesn't mean the issue reported is not active; it simply mean that the maintainers of the project are investigating on the issue, and they postponed any change on the code.
Who is not a maintainer of the project should avoid to change it back to active.

Just to understand better: what database engine are you using?

The postponed status doesn't mean the issue reported is not active; it simply mean that the maintainers of the project are investigating on the issue, and they postponed any change on the code. Who is not a maintainer of the project should avoid to change it back to active.

Sorry for that.

Just to understand better: what database engine are you using?

I am using MySQL. The version installed on the server is 5.0.51a.

I don't see anything wrong in the declaration of the database tables; maybe the problem is simply caused by the use of the CONCAT() SQL function in the query.

That part should be changed, but to do that most of the code of the project should be changed as well.
I will see what can be done.

DwightAspinwall pointed out the solution to this problem.
I am going to change the code in xmlsitemap_node.module to correct the call to CAST() made in the SQL query.

Thanks again to DwightAspinwall.

@yan: The collation of Drupal tables should be utf8_general_ci. If you have any tables with a different collation, you should convert them to utf8_general_ci.

I committed the fixed code in CVS.

I have not found any documentation about PostgreSQL, therefore the fix is only for MySQL.
If anybody would have a problem with PostgreSQL is free to reopen this report.