MySQL Bugs: #61520: help-tableformat doesn't match help-files

Bug #61520	help-tableformat doesn't match help-files
Submitted:	15 Jun 2011 7:51	Modified:	22 Mar 2013 1:23
Reporter:	erkan yanar	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Installing	Severity:	S3 (Non-critical)
Version:	5.1.57	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
using mysql_install_db breaks with:

WARNING: HELP FILES ARE NOT COMPLETELY INSTALLED!
The "HELP" command might not work properly.

Doing the relvant command by hand:

# mysql -u root mysql < fill_help_tables.sql
ERROR 1406 (22001) at line 74: Data too long for column 'url' at row 1

Thats because fill_help_tables.sql have changed and doesnt match the url-column of the table help_topic.

mysql> desc 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    |       |
+------------------+----------------------+------+-----+---------+-------+

Lets look for url-values in 5.1.56 longer than 128:
#v+
# grep 'insert into help_topic ' fill_help_tables.sql  | perl -ne " m/.*,'(.*)'/; print \$1.\"\n\"   if (length(\$1) > 128) "
#v-
And now lets have a look into 5.1.57:

#v+
# grep 'insert into help_topic ' fill_help_tables.sql  | perl -ne " m/.*,'(.*)'/; print \$1.\"\n\"   if (length(\$1) > 128) "
http://dev.mysql.com/doc/refman/5.1/en/functions-for-testing-spatial-relations-between-geo...
http://dev.mysql.com/doc/refman/5.1/en/functions-for-testing-spatial-relations-between-geo...
http://dev.mysql.com/doc/refman/5.1/en/functions-for-testing-spatial-relations-between-geo...
http://dev.mysql.com/doc/refman/5.1/en/functions-for-testing-spatial-relations-between-geo...
http://dev.mysql.com/doc/refman/5.1/en/functions-for-testing-spatial-relations-between-geo...
http://dev.mysql.com/doc/refman/5.1/en/functions-for-testing-spatial-relations-between-geo...
http://dev.mysql.com/doc/refman/5.1/en/functions-for-testing-spatial-relations-between-geo...
http://dev.mysql.com/doc/refman/5.1/en/functions-for-testing-spatial-relations-between-geo...
http://dev.mysql.com/doc/refman/5.1/en/functions-for-testing-spatial-relations-between-geo...
http://dev.mysql.com/doc/refman/5.1/en/functions-for-testing-spatial-relations-between-geo...
http://dev.mysql.com/doc/refman/5.1/en/functions-for-testing-spatial-relations-between-geo...
http://dev.mysql.com/doc/refman/5.1/en/functions-for-testing-spatial-relations-between-geo...
http://dev.mysql.com/doc/refman/5.1/en/functions-for-testing-spatial-relations-between-geo...
http://dev.mysql.com/doc/refman/5.1/en/functions-for-testing-spatial-relations-between-geo...
http://dev.mysql.com/doc/refman/5.1/en/functions-for-testing-spatial-relations-between-geo...
#v-

Regards
Erkan

How to repeat:

mysql -u root -p mysql $PATH/fill_help_tables.sql

Suggested fix:
Change help_topic definition.

Thank you for the bug report. Verified on Ubuntu 10.04 with our generic Linux binaries:

openxs@ubuntu:/tmp$ grep 'insert into help_topic' mysql-5.1.57-linux-x86_64-glibc23/share/fill_help_tables.sql | perl -ne " m/.*,'(.*)'/; print \$1.\"\n\" if (length(\$1) > 128)"
http://dev.mysql.com/doc/refman/5.1/en/functions-for-testing-spatial-relations-between-geo...
http://dev.mysql.com/doc/refman/5.1/en/functions-for-testing-spatial-relations-between-geo...
...

Same problem on 5.5.14:

mysql --database mysql < fill_help_tables.sql
ERROR 1406 (22001) at line 74: Data too long for column 'url' at row 1

Verified on 5.6.4-m7

Until a patch is released, a quick and dirty fix can be applied, just before using the cmake command :

grep -risl 'CREATE TABLE help_topic' * | xargs sudo sed -i -e 's/ char\\(/ varchar(/g'
grep -risl 'CREATE TABLE help_topic' * | xargs sudo sed -i -e 's/128/175/g'
grep -risl 'CREATE TABLE IF NOT EXISTS help_topic' * | xargs sudo sed -i -e 's/ char\\(/ varchar(/g'
grep -risl 'CREATE TABLE IF NOT EXISTS help_topic' * | xargs sudo sed -i -e 's/128/175/g'

It pass, even if utf8 charset by default :)

Also to note, it happens also because an existing my.cnf has SQL-MODE=STRICT_TRANS_TABLE, you can temporarily remove that so the truncation doesn't cause an error (just warning), then reset that. If you fill help tables later say after an upgrade you can do this in a session with set session sql_mode=''; ./path/to/mysql/share/fill_help_tables.sql

Bug#60539 was marked as a duplicate.

Noted in 5.1.70, 5.5.32, 5.6.12, 5.7.2 changelogs.

The url columns in the mysql datatbase help tables were too short to 
hold some of the URLs in the help content. These columns are now type
TEXT to accommodate longer URLs.