Bug #43707 Columns in general_log and slow_log changed to "longtext" during upgrade
Submitted: 17 Mar 2009 16:29
Reporter: Vemund Østgaard Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Charsets Severity:S3 (Non-critical)
Version:6.0.10, 5.6 OS:Linux (2.6.9-34.ELsmp #1 SMP Fri Feb 24 16:56:28 EST 2006 x86_64 x86_64 x86_64 GNU/Linux)
Assigned to: CPU Architecture:Any

[17 Mar 2009 16:29] Vemund Østgaard
Description:
In testing of upgrade from 5.1.32 to 6.0.10, the tables general_log and slow_log are affected differently by upgrade depending on whether a "dump" upgrade or a "live" upgrade is done. The difference is that two columns in each table end up with type "mediumtext" when mysqldump is used to upgrade the database, and "longtext" if it is not.

A "dump" upgrade is done by dumping the 5.1.32 database with mysqldump and loading it again after restarting the server with a 6.0.10 version, and then running the mysql_upgrade command. A "live" upgrade is done by just restarting the server with a 6.0.10 version (no dump and load) and then running mysql_upgrade.

Now what happens when doing a "dump" upgrade is that the general_log and slow_log tables have utf8 as charset after the data has been loaded into the new server. When doing a "live" upgrade however, the charset is changed to utf8mb3 after the server has been restarted with the new version. Then, when the mysql_upgrade command is run, an ALTER TABLE is executed to convert character set from utf8mb3 to utf8 for these two tables. An additional consequence of this ALTER TABLE command seems to be that data type for a couple of columns in each table is converted from "mediumtext" to "longtext". This effect is only achieved when doing "live" upgrade, as for "dump" upgrade the tables already are utf8 and the ALTER TABLE apparently has no side effect because of that.

Why the data type of the columns is converted when charset is changed is explained in http://bugs.mysql.com/bug.php?id=31291. 

What I am concerned about is that these two system tables have columns where data type is depending on their upgrade history. I think the result should be "mediumtext" or "longtext" independent of how the database is upgraded.

The columns in question are the ones with "longtext" in the output below:

> mysql> show create table slow_log\G
> *************************** 1. row ***************************
>       Table: slow_log
> Create Table: CREATE TABLE `slow_log` (
>  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
>  `user_host` longtext NOT NULL,
>  `query_time` time NOT NULL,
>  `lock_time` time NOT NULL,
>  `rows_sent` int(11) NOT NULL,
>  `rows_examined` int(11) NOT NULL,
>  `db` varchar(512) NOT NULL,
>  `last_insert_id` int(11) NOT NULL,
>  `insert_id` int(11) NOT NULL,
>  `server_id` int(11) NOT NULL,
>  `sql_text` longtext NOT NULL
> ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'
> 1 row in set (0.00 sec)
>
> mysql> show create table general_log\G
> *************************** 1. row ***************************
>       Table: general_log
> Create Table: CREATE TABLE `general_log` (
>  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
>  `user_host` longtext NOT NULL,
>  `thread_id` int(11) NOT NULL,
>  `server_id` int(11) NOT NULL,
>  `command_type` varchar(64) NOT NULL,
>  `argument` longtext NOT NULL
> ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'
> 1 row in set (0.00 sec)

How to repeat:
1. Start a 5.1.32 server
2. Restart server with 6.0.10 version
3. Run mysql_upgrade command
4. Observe general_log and slow_log columns have data type longtext.
[29 Jan 2010 7:53] Nidhi Shrotriya
Seen in mysql-6.1-glob tree and seems to be related to WL#1213. Need to be checked for Celosia.