MySQL Bugs: #52161: When rebuilding a table, ALTER TABLE does not check SE setting before executing

Bug #52161	When rebuilding a table, ALTER TABLE does not check SE setting before executing
Submitted:	18 Mar 2010 0:12	Modified:	1 Feb 2013 9:38
Reporter:	Roel Van de Paar	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	5.1.42, 5.1.46	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
As per subject line.

Faulty statement could return immediately based on error, but instead takes about the same amount of time as an actual rebuild.

--------------
mysql> SHOW CREATE TABLE a;
+-------+----------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                           |
+-------+----------------------------------------------------------------------------------------------------------------------------------------+
| a     | CREATE TABLE `a` (
  `id` int(11) DEFAULT NULL,
  `p` varchar(20) NOT NULL,
  PRIMARY KEY (`p`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM a;
+----------+
| COUNT(*) |
+----------+
|   999999 |
+----------+
1 row in set (0.71 sec)

mysql> ALTER TABLE a ENGINE=noenginenamedlikethis; /* Notice duration */5
Query OK, 999999 rows affected, 1 warning (12.51 sec)
Records: 999999  Duplicates: 0  Warnings: 0

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1286 | Unknown table engine 'noenginenamedlikethis' |
+---------+------+----------------------------------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE a ENGINE=MyISAM;
Query OK, 999999 rows affected (18.50 sec)
Records: 999999  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE a ENGINE=InnoDB;
Query OK, 999999 rows affected (9.39 sec)
Records: 999999  Duplicates: 0  Warnings: 0
--------------

How to repeat:
Build a large table, execute ALTER TABLE tablename ENGINE=noenginenamedlikethis;

Suggested fix:
Check all syntax before starting ALTER.

Thank you for the bug report. Verified just as described:

77-52-28-202:5.1 openxs$ bin/mysql -uroot testReading 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 4
Server version: 5.1.46-debug Source distribution

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

mysql> alter table ti engine=fgdfjhdjhd;
Query OK, 524288 rows affected, 1 warning (3 min 7.44 sec)
Records: 524288  Duplicates: 0  Warnings: 0

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1286
Message: Unknown table engine 'fgdfjhdjhd'
1 row in set (0.00 sec)

Maybe value of 'no_engine_substitution' variable matters?

Yes, NO_ENGINE_SUBSTITUTION affects the outcome:

----------
mysql> SHOW GLOBAL VARIABLES LIKE 'storage_engine'; SELECT @@global.sql_mode; SELECT @@session.sql_mode;
+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| storage_engine | MyISAM |
+----------------+--------+
+-------------------+
| @@global.sql_mode |
+-------------------+
|                   |
+-------------------+
+--------------------+
| @@session.sql_mode |
+--------------------+
|                    |
+--------------------+

mysql> SHOW CREATE TABLE a;
| a     | CREATE TABLE `a` (
  `id` int(11) DEFAULT NULL,
  `p` varchar(20) NOT NULL,
  PRIMARY KEY (`p`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

mysql> ALTER TABLE a ENGINE=fkjsalfjds;
Query OK, 8457900 rows affected, 1 warning (2 min 50.34 sec)
Records: 8457900  Duplicates: 0  Warnings: 0

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------+
| Level   | Code | Message                           |
+---------+------+-----------------------------------+
| Warning | 1286 | Unknown table engine 'fkjsalfjds' |
+---------+------+-----------------------------------+

mysql> SET @@SESSION.sql_mode='NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE a ENGINE=fkjsalfjds;
ERROR 1286 (42000): Unknown table engine 'fkjsalfjds'
----------

So, the bug remains, but applies only to systems that do not have "NO_ENGINE_SUBSTITUTION" set.

The manual lists the correct/expected behavior (notice second sentence):

'Up through MySQL 5.1.11, with NO_ENGINE_SUBSTITUTION  disabled, the default engine is used and a warning occurs if the desired engine is known but disabled or not compiled in. If the desired engine is invalid (not a known engine name), an error occurs and the table is not created or altered.'
http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html#sqlmode_no_engine_substitution

For some reason, "something" is happening while the statement is processing, though it is unclear what this is. It is also not using the default engine:

----------
mysql> SHOW GLOBAL VARIABLES LIKE 'storage_engine'; SELECT @@global.sql_mode; SELECT @@session.sql_mode;
+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| storage_engine | InnoDB |
+----------------+--------+
+-------------------+
| @@global.sql_mode |
+-------------------+
|                   |
+-------------------+
+--------------------+
| @@session.sql_mode |
+--------------------+
|                    |
+--------------------+

mysql> SHOW CREATE TABLE a;
| a     | CREATE TABLE `a` (
  `id` int(11) DEFAULT NULL,
  `p` varchar(20) NOT NULL,
  PRIMARY KEY (`p`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

mysql> ALTER TABLE a ENGINE=fkjsalfjds;
Query OK, 8457900 rows affected, 1 warning (2 min 5.15 sec)
Records: 8457900  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE a;
| a     | CREATE TABLE `a` (
  `id` int(11) DEFAULT NULL,
  `p` varchar(20) NOT NULL,
  PRIMARY KEY (`p`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
----------

Hello!

This is not a bug but a documented and expected behavior.

Note that in 5.1 specifying non-existent storage engine in ALTER TABLE doesn't cause an error but rather a warning. In such a case we use default storage engine (MyISAM) instead of specified storage engine.

Also our documentation clearly says at
http://dev.mysql.com/doc/refman/5.1/en/alter-table.html :
===
When you specify an ENGINE clause, ALTER TABLE rebuilds the table. This is true even if the table already has the specified storage engine. 
===

So table rebuild is expected in a situation like this when wrong storage engine name is used.

Also note that you can use NO_ENGINE_SUBSTITUTION sql_mode to ensure that
non-existing engine is not accepted and ALTER quickly ends with an appropriate error.

Closing this report as "Not a bug".