| Bug #30227 | mysql error 1267 on replication system | ||
|---|---|---|---|
| Submitted: | 3 Aug 2007 10:53 | Modified: | 22 Dec 2007 18:21 |
| Reporter: | Jens Knoop | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S2 (Serious) |
| Version: | 5.0.32-Debian_7etch1 | OS: | Linux (Debian 4.0) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | 1267 replication Error 'Illegal mix of collations (latin1_german1_ci, IMPLICIT) and (latin1_swedish_ci, IMPLICIT) for operation '='' on query | ||
[3 Aug 2007 10:57]
Jens Knoop
one more information: the error doesn't occur on the live-server.
[3 Aug 2007 11:05]
Sveta Smirnova
Thank you for the report. But version 5.0.32 is quite old. Please upgrade to current version 5.0.45/5.0.46, try with it and if you can repeat error provide configuration files for both master and slave. Also provide output of SHOW GLOBAL VARIABLES LIKE 'char%'; and SHOW GLOBAL VARIABLES LIKE 'coll%'; on both master and slave.
[3 Aug 2007 11:29]
Jens Knoop
hi sveta, thank you for the quick answer! version 5.0.32 is the stable package of debian 4.0 - and it's our policy to use the stable debian releases. so i think: no chance to upgrade on suspicion. is this bug know for older mysql-releases? in a forum i found a simmilar report for mysql 4.1.x and the recommendation was to upgrade to a current version. 5.0 sounds more up-to-date than 4.1 ... Standby-Server ============== mysql> SHOW GLOBAL VARIABLES LIKE 'char%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.00 sec) mysql> SHOW GLOBAL VARIABLES LIKE 'coll%'; +----------------------+-------------------+ | Variable_name | Value | +----------------------+-------------------+ | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +----------------------+-------------------+ 3 rows in set (0.00 sec) Live-Server =========== mysql> SHOW GLOBAL VARIABLES LIKE 'char%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.00 sec) mysql> SHOW GLOBAL VARIABLES LIKE 'coll%'; +----------------------+-------------------+ | Variable_name | Value | +----------------------+-------------------+ | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +----------------------+-------------------+ 3 rows in set (0.00 sec) regards, jens
[22 Nov 2007 18:21]
Sveta Smirnova
Jans, thank you for the reply. But what are the stand-by and live-by servers? Is some of them master and other is slave? We need output of these statements from both master and slave.
[23 Dec 2007 0:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
Description: i have a repeating 1267-error on a brandnew replication system - but i can't see any collation-mix in the database/table-setup: mysql> show table status from typo3_cms like 'tx_news_entries'; +--------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +--------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ | tx_birnews_entries | MyISAM | 10 | Dynamic | 643 | 5639 | 3626216 | 281474976710655 | 21504 | 0 | 810 | 2007-07-16 17:16:47 | 2007-08-03 06:00:02 | NULL | latin1_german1_ci | NULL | | | +--------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ 1 row in set (0.01 sec) here the error-message: | Waiting for master to send event | 195.82.66.180 | ap_replication | 3306 | 60 | mysql-bin.000279 | 194132951 | mysqlserver-stby-relay-bin.001100 | 87400939 | mysql-bin.000276 | Yes | No | | | | | | | 1267 | Error 'Illegal mix of collations (latin1_german1_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='' on query. Default database: 'typo3_cms'. Query: 'UPDATE tx_news_entries SET id = @'id', release_date = @'release_date', date_formated = @'date_formated', pubdate_timestamp = @'pubdate_timestamp', title = @'title', headline = @'headline', subheadline = @'subheadline', content = @'content', pdf_source = @'pdf_source', pdf_name = @'pdf_name', medienlib_id = @'medienlib_id', created = @'created', hidden = @'hidden' WHERE document_id = @'document_id' AND language = @'language'' | 0 | 192585458 | 895565625 | None | | 0 | No | | | | | | NULL | regards, jens How to repeat: i don't know - i can't find similar problem in forums. i think we have a standard-setup, a standard database and a standard table.