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;