Bug #69853 mysql_upgrade does not update the structure of a help table

• Submitted: 26 Jul 2013 23:50
• Modified: 27 Jul 2013 2:14
• Reporter: Elena Stepanova
• Status: Verified
• Category: MySQL Server: Command-line Clients
• Severity: S3 (Non-critical)
• Version: 5.5.32
• OS: Any
• CPU Architecture: Any

[26 Jul 2013 23:50] Elena Stepanova

Description:
Filing on behalf of our user who cannot login into this system any longer. 
Initially filed as https://mariadb.atlassian.net/browse/MDEV-4808.

After bugfix for Bug #61520, the structure of help tables was changed. Now newly installed tables are created with url columns of type TEXT; but tables created before the fix do not get upgraded by mysql_upgrade, so the problem still exists, although it's currently hidden, because on some reason in the new version the links in fill_help_tables.sql became shorter and fit the old column length. 

mysql_upgrade is known (and is documented) not to touch the *contents* of help tables, but nothing suggests that it shouldn't upgrade the table definition when it changes. 

How to repeat:
- create a datadir with a previous version of MySQL;
- start 5.5.32 on this datadir;
- run mysql_upgrade;
- see that url columns are still of type char(128).

[27 Jul 2013 2:14] MySQL Verification Team

Thank you for the bug report.

[miguel@TikalVM mysql-5.5.32-linux2.6-x86_64]$ bin/mysql_upgrade -uroot
Looking for 'mysql' as: bin/mysql                                      
Looking for 'mysqlcheck' as: bin/mysqlcheck                            
Running 'mysqlcheck with default connection arguments                  
Running 'mysqlcheck with default connection arguments                  
mysql.columns_priv                                 OK                  
mysql.db                                           OK                  
mysql.event                                        OK                  
mysql.func                                         OK                  
mysql.general_log                                  OK                  
mysql.help_category                                OK                  
mysql.help_keyword                                 OK                  
mysql.help_relation                                OK                  
mysql.help_topic                                   OK                  
mysql.host                                         OK                  
mysql.ndb_binlog_index                             OK                  
mysql.plugin                                       OK                  
mysql.proc                                         OK                  
mysql.procs_priv                                   OK                  
mysql.proxies_priv                                 OK                  
mysql.servers                                      OK                  
mysql.slow_log                                     OK                  
mysql.tables_priv                                  OK                  
mysql.time_zone                                    OK                  
mysql.time_zone_leap_second                        OK                  
mysql.time_zone_name                               OK                  
mysql.time_zone_transition                         OK                  
mysql.time_zone_transition_type                    OK                  
mysql.user                                         OK                  
Running 'mysql_fix_privilege_tables'...                                
OK                                                                     
[miguel@TikalVM mysql-5.5.32-linux2.6-x86_64]$ bin/mysql -uroot mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.5.32 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW CREATE TABLE help_topic\G
*************************** 1. row ***************************
       Table: help_topic
Create Table: CREATE TABLE `help_topic` (
  `help_topic_id` int(10) unsigned NOT NULL,
  `name` char(64) NOT NULL,
  `help_category_id` smallint(5) unsigned NOT NULL,
  `description` text NOT NULL,
  `example` text NOT NULL,
  `url` char(128) NOT NULL,
  PRIMARY KEY (`help_topic_id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='help topics'
1 row in set (0.00 sec)

mysql>

[30 Dec 2017 6:45] Daniel Black

this has been fixed https://github.com/mysql/mysql-server/commit/924b64dec1e3fa58da2de286869153842914a123