MySQL Bugs: #69564: Please disable writing to binlogs in fill_help

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:	None
Category:	MySQL Server: Installing	Severity:	S3 (Non-critical)
Version:	5.6.13,5.7.1	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I recently upgraded a server to 5.6.12 and had a similar server running 5.6.12 "downstream".
It looks like I hadn't upgraded the help files on the downstream server "for some time". I think they probably had the 5.5 data.

Running mysql mysql < /usr/share/mysql/fill_help_tables.sql on the master of course wrote out (as the mysql database is configured to be replicated) data to the slave and this broke it, giving errors like:

2013-06-25 07:19:29 24227 [ERROR] Slave SQL: Column 5 of table 'mysql.help_topic' cannot be converted from type 'char(128)' to type 'text', Error_code: 1677

and many more.

If I'm going to upgrade the help files I want to do it on the local server. I don't necessarily know what version any slaves may be running and given you do an upgrade on the slave first it usually makes no sense to push out old help files downstream.

How to repeat:
See above.

Suggested fix:
Ensure that fill_help_tables.sql has some SQL to temporarily disable writes to the binlogs while inserting the local help files by adding a simple statement (similar to ps_helper.sql):

SET sql_log_bin = 0;

That fixes the problem and ensures that only the local server gets the changes and downstream is not affected.

A minor nit but given I've just been bitten by it I'd like to not see it again.

Also ensure some sort of comment is added to the file explaining that this is happening.

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;

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

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

?

So if you want this comment referred to a different bug and thus feature request please let me know.

Still broken in 5.6.13 and 5.7.1 and it's a  tiny patch to the file.

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.

The other work around is do something like:

( echo 'set  sql_log_bin = 0 ;' ; cat /usr/share/mysql/fill_help_tables.sql  ) | mysql mysql

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.

Documentation is much clearer now. So thanks.

As of MySQL 5.7.5, the fill_help_tables.sql file will include the SET sql_log_bin=0; statement.