Bug #54653 inconsistent reporting of warnings/notes for ddl operations on multiple tables
Submitted: 21 Jun 2010 7:43 Modified: 21 Jun 2010 8:27
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.0.91, 5.1.48, 5.5.3, 5.6.99 bzr OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D4 (Minor)

[21 Jun 2010 7:43] Shane Bester
Description:
Consider this output from 5.1.48:

mysql> optimize table t1;
+---------+----------+----------+-------------------------------------------
| Table   | Op       | Msg_type | Msg_text
+---------+----------+----------+-------------------------------------------
| test.t1 | optimize | note     | Table does not support optimize, doing rec
| test.t1 | optimize | status   | OK
+---------+----------+----------+-------------------------------------------
2 rows in set, 2 warnings (0.08 sec)

mysql> show warnings;
+-------+------+--------------------------------------------+
| Level | Code | Message                                    |
+-------+------+--------------------------------------------+
| Note  | 1291 | Column 'a' has duplicated value '' in ENUM |
| Note  | 1291 | Column 'a' has duplicated value '' in ENUM |
+-------+------+--------------------------------------------+
2 rows in set (0.00 sec)

mysql>
mysql> optimize table t1,t2;
+---------+----------+----------+-------------------------------------------
| Table   | Op       | Msg_type | Msg_text
+---------+----------+----------+-------------------------------------------
| test.t1 | optimize | note     | Table does not support optimize, doing rec
| test.t1 | optimize | status   | OK
| test.t2 | optimize | Note     | Column 'a' has duplicated value '' in ENUM
| test.t2 | optimize | Note     | Column 'a' has duplicated value '' in ENUM
| test.t2 | optimize | note     | Table does not support optimize, doing rec
| test.t2 | optimize | status   | OK
+---------+----------+----------+-------------------------------------------
6 rows in set (0.27 sec)

The inconsistency is that optimizing multiple tables at once caused the 'warnings' or notes to be shown in the resultset instead of via show warnings.

First detected this when studying output from "mysqlcheck -1 -o test" vs "mysqlcheck -o test"

How to repeat:
drop table if exists `t1`,`t2`;
create table `t1`(`a` enum('',''))engine=innodb;
create table `t2`(`a` int)engine=innodb;
optimize table `t1`,`t2`;
optimize table `t1`;
show warnings;
[21 Jun 2010 8:27] Sveta Smirnova
Thank you for the report.

Verified as described.