| Bug #69564 | Please disable writing to binlogs in fill_help_tables.sql | ||
|---|---|---|---|
| Submitted: | 25 Jun 2013 5:46 | Modified: | 28 Feb 2014 8:53 |
| Reporter: | Simon Mudd (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Installing | Severity: | S3 (Non-critical) |
| Version: | 5.6.13,5.7.1 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[25 Jun 2013 5:46]
Simon Mudd
[25 Jun 2013 5:55]
Simon Mudd
The diff is not against your original source version but something like this should I think do the trick: diff --git a/fill_help_tables.sql.orig b/fill_help_tables.sql index 50ad90d..570e8c3 100755 --- a/fill_help_tables.sql.orig +++ b/fill_help_tables.sql @@ -25,6 +25,10 @@ -- mysql -u root -p mysql < file_name +-- Note: as this server may have a slave and the slave version may be different +-- to the server's version do not replicate changes downstream. See Bug#69564. + +set sql_log_bin = 0; set names 'utf8'; delete from help_topic;
[26 Jun 2013 10:12]
MySQL Verification Team
Hello Simon,
Thank you for the report and contribution.
How to repeat:
- Setup simple replication using 5.5.30 (ensure binlog_format=row, don't use 5.5.32, url colunm is already TEXT)
- Upgrade slave to 5.6.12
- run mysql_upgrade(this would also update help tables :) so no need to run below ALTER TABLE stament)
ALTER TABLE mysql.help_category MODIFY url TEXT NOT NULL;
ALTER TABLE mysql.help_topic MODIFY url TEXT NOT NULL;
- Ensure replication is up
- Stop slave, upgrade master to 5.6.12 (don't run mysql_upgrade)
- Start slave, ensure replication is up
- Compare mysql.help_topic from master and slave
master> desc mysql.help_topic;
+------------------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+----------------------+------+-----+---------+-------+
| help_topic_id | int(10) unsigned | NO | PRI | NULL | |
| name | char(64) | NO | UNI | NULL | |
| help_category_id | smallint(5) unsigned | NO | | NULL | |
| description | text | NO | | NULL | |
| example | text | NO | | NULL | |
| url | char(128) | NO | | NULL | |
+------------------+----------------------+------+-----+---------+-------+
slave> desc mysql.help_topic;
+------------------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+----------------------+------+-----+---------+-------+
| help_topic_id | int(10) unsigned | NO | PRI | NULL | |
| name | char(64) | NO | UNI | NULL | |
| help_category_id | smallint(5) unsigned | NO | | NULL | |
| description | text | NO | | NULL | |
| example | text | NO | | NULL | |
| url | text | NO | | NULL | |
+------------------+----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
- Import help tables on master
bin/mysql -u root --port=3306 --protocol=tcp mysql < ./share/fill_help_tables.sql
- Check replication
show slave status\G
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-cluster-repo-bin.000005
Read_Master_Log_Pos: 1575883
Relay_Log_File: cluster-repo-relay-bin.000010
Relay_Log_Pos: 297
Relay_Master_Log_File: master-cluster-repo-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1677
Last_Error: Column 5 of table 'mysql.help_topic' cannot be converted from type 'char(128)' to type 'text'
Skip_Counter: 0
Exec_Master_Log_Pos: 120
Relay_Log_Space: 1576417
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1677
Last_SQL_Error: Column 5 of table 'mysql.help_topic' cannot be converted from type 'char(128)' to type 'text'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3306
Master_UUID: f81c616c-defe-11e2-aba5-fdf0bbd6007b
Master_Info_File: /tmp/slave/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 130627 13:39:39
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
// From slave's error log
2013-06-27 13:39:39 14486 [ERROR] Slave SQL: Column 5 of table 'mysql.help_topic' cannot be converted from type 'char(128)' to type 'text', Error_code: 1677
2013-06-27 13:39:39 14486 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'master-cluster-repo-bin.000005' position 120
Workaround:
1)On slave
set global slave_type_conversions=ALL_NON_LOSSY;
2) mysql_upgrade is executed post upgrade on master/slave.
Thanks,
Umesh
[2 Jul 2013 10:32]
Simon Mudd
Further question: why does mysql_upgrade _not_ upgrade the help files automatically on every upgrade? Result: - avoid a manual step - ensure the help system (inside mysql) is up to date and inline with the current mysql version - keep the DBAs happy - save time ?
[2 Jul 2013 10:36]
Simon Mudd
So if you want this comment referred to a different bug and thus feature request please let me know.
[6 Sep 2013 6:50]
Simon Mudd
Still broken in 5.6.13 and 5.7.1 and it's a tiny patch to the file.
[6 Sep 2013 6:54]
Simon Mudd
This is a bug not a feature request. I just broke replication on a couple of slaves by not automating the workaround and bumping into the same issue again. Also if working with RBR you get errors like this: 2013-09-06 08:11:33 16338 [ERROR] Slave SQL: Could not execute Delete_rows event on table mysql.help_relation; Can't find record in 'help_relation', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log binlog.000554, end_log_pos 549088, Error_code: 1032 2013-09-06 08:15:05 16338 [Note] Slave SQL thread initialized, starting replication in log 'binlog.000554' at position 540750, relay log '../log/relaylog.001541' position: 540910 2013-09-06 08:15:05 16338 [ERROR] Slave SQL: Could not execute Write_rows event on table mysql.help_category; Duplicate entry '39' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log binlog.000554, end_log_pos 560703, Error_code: 1062 2013-09-06 08:15:05 16338 [Note] Slave SQL thread initialized, starting replication in log 'binlog.000554' at position 560515, relay log '../log/relaylog.001541' position: 560675 2013-09-06 08:15:05 16338 [ERROR] Slave SQL: Could not execute Write_rows event on table mysql.help_category; Duplicate entry '40' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log binlog.000554, end_log_pos 560971, Error_code: 1062 ... So any inconsistencies immediately show up.
[6 Sep 2013 6:55]
Simon Mudd
The other work around is do something like: ( echo 'set sql_log_bin = 0 ;' ; cat /usr/share/mysql/fill_help_tables.sql ) | mysql mysql
[27 Feb 2014 15:40]
Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products. A decision was made to handle this by documenting the help-table upgrade process and lay out the various options for accomplishing it depending on your goals. The page describing this is: http://dev.mysql.com/doc/refman/5.6/en/replication-features-server-side-help.html If you have feedback, please let me know. Thanks for bringing up the issue.
[28 Feb 2014 8:53]
Simon Mudd
Documentation is much clearer now. So thanks.
[9 Apr 2014 17:37]
Paul DuBois
As of MySQL 5.7.5, the fill_help_tables.sql file will include the SET sql_log_bin=0; statement.
