Though this is more of a mysql issue, I think we might be able to come up with a helpful solution or workaround for those running into this issue.

"ß" is a special character. In German it means "ss". Though the site this happens on is generally an english site, some users have used this character for names of german sites, etc. it has shown up in some articles. When search was turned on, these words were indexed, and seem to be stored correctly in that database, but now when I try to mysqldump the site and import it on my local machine, I'm getting errors about duplicate entries.

ERROR 1062 (23000) at line 6032297: Duplicate entry 'spaß' for key 'PRIMARY'

I found this related "bug" for mysql
http://bugs.mysql.com/bug.php?id=41281

I think I then might have to change the collation then, but i thought mysqldump set the collation by default anyway?

Comments

frankcarey’s picture

frankcarey commented

used this query to see collation info on each server's DB but they seems to be the same. (probably because mysqldump does set this i think).

show variables like 'collation%';

Dev DB collation:

+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci | 
| collation_database   | utf8_unicode_ci   | 
| collation_server     | latin1_swedish_ci | 
+----------------------+-------------------+

Local DB collation:

+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci | 
| collation_database   | utf8_unicode_ci   | 
| collation_server     | latin1_swedish_ci | 
+----------------------+-------------------+
frankcarey’s picture

frankcarey commented
Status: Closed (works as designed) » Closed (won't fix)

With both server and db having the same settings, this is odd, but looking at the last comment in the mysql bug link, it mentions that it was working in 5.0 and not in 5.1, and this is the same difference I'm seeing.

DEV SERVER:

mysql> select VERSION();
+------------------------+
| VERSION()              |
+------------------------+
| 5.0.51a-3ubuntu5.4-log | 
+------------------------+

LOCAL SERVER:

mysql> select VERSION();
+-----------------+
| VERSION()       |
+-----------------+
| 5.1.37-1ubuntu5 | 
+-----------------+

So I'll just disable search and remove the tables for now, but upgrading the dev server to 5.1 might make the most sense. Hopefully, the result would be that search index should just treat something like "hass" and "haß" the same and use the same index row.

JimNastic’s picture

JimNastic commented

Hi Frank,
I am seeing a similar problem copying the database to localhost and using german special characters.

Using your query against my host database I see:
collation_connection utf8_general_ci
collation_database latin1_german1_ci
collation_server latin1_german1_ci

and with my local database I have:
Variable_name Value
collation_connection utf8_unicode_ci
collation_database latin1_swedish_ci
collation_server latin1_swedish_ci

Have you any advice? Is it possible to change my local database to latin1_germany1_ci?
Any other suggestions?

JimNastic’s picture

JimNastic commented

I tried deleting the local database and creating a new database with latin1_german1_ci but this didnt seem to help
Note that:
host database is version 5.0.67
local database is version 5.0.51a

JimNastic’s picture

JimNastic commented

Workaround:
1) delete offending row from MySQL
2) export from live site and import to localhost
3) re-index live site

gpk’s picture

gpk commented

I'd recommend always using UTF8 unicode - I think this is what Drupal likes best and it can cope with special characters from any language. This is what new Drupal DBs have their tables' collation set to (see http://api.drupal.org/api/drupal/includes--database.mysql-common.inc/6/s... and look for function db_create_table_sql()). Whether it's worth actually changing collation_database and/or collation_server I'm not sure.