MySQL Bugs: #67073: MySQL 5.6.7, GTID options and help file problems

Bug #67073	MySQL 5.6.7, GTID options and help file problems
Submitted:	4 Oct 2012 8:05	Modified:	1 Nov 2012 17:49
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.6.7-rc1	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	windmill

Description:
I noticed my help files seemed to be incomplete on a recently installed 5.6.7-rc1 server, so I thought I'd check if they should be loaded in:

"6.1.9 Server-Side Help" says "For a MySQL binary distribution on Unix, help table setup occurs when
you run *Note `mysql_install_db': mysql-install-db." so I assumed this should have been installed on my Mac. In any case I tried to load this manually (following the manual and see it fails), due to the GTID options being set:

m7:share root# grep gtid /etc/my.cnf
gtid_mode = ON
disable-gtid-unsafe-statements
m7:share root#

Also when I disable this I notice the data is being truncated (see below).

Finally it seems impossible to disable this GTID functionality (temporarily) , see below:

How to repeat:

On a Mac (I can't load help files):

m7:mysql root# cd share/
m7:share root# less fill_help_tables.sql
m7:share root# mysql mysql < fill_help_tables.sql
ERROR 1785 (HY000) at line 26: Updates to non-transactional tables are forbidden when DISABLE_GTID_UNSAFE_STATEMENTS = 1.
m7:share root# pwd
/usr/local/mysql/share
m7:share root# 

Truncation of help file info:

Query OK, 1 row affected (0.00 sec)

--------------
insert into help_topic (help_topic_id,help_category_id,name,description,example,url) values (2,30,'CONTAINS','Contains(g1,g2)\n\nReturns 1 or 0 to indicate whether g1 completely contains g2. This\ntests the opposite relationship as Within().\n\nURL: http://dev.mysql.com/doc/refman/5.6/en/functions-for-testing-spatial-relations-between-geo...;)
--------------

ERROR 1406 (22001) at line 73: Data too long for column 'url' at row 1
Bye

Not possible to disable this GTID functionality even temporarily:

[sjmudd@m7 mysql]$ ( echo 'set disable_gtid_unsafe_statements = 0;' ; cat share/fill_help_tables.sql ) | mysql mysql
ERROR 1238 (HY000) at line 1: Variable 'disable_gtid_unsafe_statements' is a read only variable
[sjmudd@m7 mysql]$ ( echo 'set global disable_gtid_unsafe_statements = 0;' ; cat share/fill_help_tables.sql ) | mysql mysql
ERROR 1238 (HY000) at line 1: Variable 'disable_gtid_unsafe_statements' is a read only variable
[sjmudd@m7 mysql]$

Suggested fix:

1. Make it possible to load the help files.
2. Possibly make it possible to temporarily disable the GTID functionality so that these files can be loaded, or alternatively convert them to INNODB (?)
3. Writing these changes to binlogs will mean that anyone with mysql.% replicated will get the changes. I'd suggest that the help file suggests the use of:  set sql_log_bin = 0; to avoid this.
4. There is no versioning in the help files so it's not possible to check if the files are up to date, short of loading them into the server. Please adjust this so it's easier to perform this check.
5. I'm not sure if mysql_upgrade does not (iirc) upgrades the help files. If it does not it would be convenient for it to do that as part of the upgrade process, when the system tables are checked and updated.

Further suggestion: make mysql_install_db:

* use STRICT_ALL_TABLES so that out of bound values will be caught when setting up the system tables.
* Stop on the first error and report what it was trying to do as well as the error message
* Also include the timezone information which is not normally installed.  People will not use this timezone information if it's not installed by default and the extra functionality it provides does not take up much disk space and can be useful. So include it and then it'll get used a lot more.

I'd also be tempted to say the same for mysql_upgrade:
* update the help files automatically ( don't enable binlogging as downstream servers might get affected in a replicated environment)
* update the timezone files (again , don't binlog to avoid this affecting downstream servers)

Same error with gtid enabled and binary logging enabled for me now. You can turn those off it seems to load the tables however without the error, so that is a workaround, should be fixed though.

grotta:share dany$ mysql -u root mysql < fill_help_tables.sql
ERROR 1785 (HY000) at line 26: Updates to non-transactional tables are forbidden when DISABLE_GTID_UNSAFE_STATEMENTS = 1.
grotta:share dany$

You haven't commented on the other issue. It seems that the url column for help_topic is not big enough to hold the content of the string. Again given MySQL's notorious default behaviour of happily chopping off data and not making it too apparent no-one notices these things. That either requires fixing the table definition or making the URL smaller.  If you don't have something like sql_mode = TRADITIONAL of course you won't notice.

Again it's possible to work around all of this but it's not documented, and from the looks of things simply hasn't been noticed. I can only imagine that the testing of the GTID functionality has been done in a nice clean environment, and no thought was made for these settings outside of that.

fill_help_tables.sql now includes comments containing revision information. Example:

-- File generation date: 2012-10-08
-- MySQL series: 5.5
-- Document repository revision: 32507

Simon,

>  If you don't have something like sql_mode = TRADITIONAL of course you won't notice.

Start mysql client with option --show-warnings and you will notice.

Anchors in URLs loaded into the url columns now are stripped, leaving just the parent page name. This should address the load errors resulting from too-long url values.

Thanks Paul for the adjustments that helps.

Sveta, you said that I'll notice if I enable --show-warnings in the mysql client. If I don't? Given that's not I believe the default behaviour then your argument does not hold.  I can imagine the very reason that this slipped through is because the warnings were not visible AND did not generate a non-recoverable error.  Hence my comment.  Again this is not a big thing but it's just pointing out how easy it is for everyone to miss warnings about MySQL chewing away at input and not necessarily storing everything you gave it.

Simon,

I agree, but this is how MySQL was designed and I am not sure having strict SQL mode by default can make all users happy. Although this is recommended since 5.5 or even 5.1.

Wrong category--should be Replication.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Fixed in 5.6.9.