Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Not a Bug
-
5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
-
Ubuntu 14.04 x86_64 deb from official binary repo
Description
If a table is created as MyISAM or ARIA engine or was at some point in the past converted to this engine type, a later conversion to INNODB and any subsequent ALTER TABLE statement produces the warning #1478. This is a critical bug as e.g. Ruby on rails interprets this warning as a failed transaction and does not apply DB migrations.
This can be reproduced using the following SQL:
CREATE DATABASE testdb; |
CONNECT testdb; |
CREATE TABLE testtable (id INT(11)) ENGINE=INNODB; |
ALTER TABLE testtable ENGINE=INNODB; |
ALTER TABLE testtable ENGINE=INNODB; |
ALTER TABLE testtable ENGINE=ARIA TRANSACTIONAL=1; |
ALTER TABLE testtable ENGINE=INNODB; |
SHOW WARNINGS;
|
ALTER TABLE testtable ENGINE=INNODB; |
SHOW WARNINGS;
|
Result:
MariaDB [(none)]> connect testdb;
|
Connection id: 6698
|
Current database: testdb
|
|
|
MariaDB [testdb]> create table testtable (id INT(11)) ENGINE=INNODB;
|
Query OK, 0 rows affected (0.30 sec)
|
|
|
MariaDB [testdb]> alter table testtable ENGINE=INNODB;
|
Query OK, 0 rows affected (0.35 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [testdb]> alter table testtable ENGINE=INNODB;
|
Query OK, 0 rows affected (0.36 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [testdb]> alter table testtable ENGINE=ARIA TRANSACTIONAL=1;
|
Query OK, 0 rows affected (0.49 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [testdb]> alter table testtable ENGINE=INNODB;
|
Query OK, 0 rows affected, 1 warning (0.54 sec)
|
Records: 0 Duplicates: 0 Warnings: 1
|
|
|
MariaDB [testdb]> show warnings;
|
+---------+------+------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+---------+------+------------------------------------------------------------------------------------+
|
| Warning | 1478 | Table storage engine 'InnoDB' does not support the create option 'TRANSACTIONAL=1' |
|
+---------+------+------------------------------------------------------------------------------------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [testdb]> alter table testtable ENGINE=INNODB;
|
Query OK, 0 rows affected, 1 warning (0.39 sec)
|
Records: 0 Duplicates: 0 Warnings: 1
|
|
|
MariaDB [testdb]> show warnings;
|
+---------+------+------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+---------+------+------------------------------------------------------------------------------------+
|
| Warning | 1478 | Table storage engine 'InnoDB' does not support the create option 'TRANSACTIONAL=1' |
|
+---------+------+------------------------------------------------------------------------------------+
|
Attachments
Issue Links
- causes
-
-
- Closed
-
- relates to
-
-
- Closed
-
- links to
Activity
In 10.4, the flag is still not deleted, but in this particular case InnoDB doesn't complain anymore:
MariaDB [testdb]> ALTER TABLE testtable ENGINE=INNODB; |
Query OK, 0 rows affected (0.55 sec) |
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [testdb]> SHOW WARNINGS;
|
Empty set (0.00 sec) |
|
|
MariaDB [testdb]> SHOW CREATE TABLE testtable; |
+-----------+---------------------------------------------------------------------------------------------------------------+ |
| Table | Create Table | |
+-----------+---------------------------------------------------------------------------------------------------------------+ |
| testtable | CREATE TABLE `testtable` ( |
`id` int(11) DEFAULT NULL |
) ENGINE=InnoDB DEFAULT CHARSET=latin1 TRANSACTIONAL=1 | |
+-----------+---------------------------------------------------------------------------------------------------------------+ |
1 row in set (0.00 sec) |
This is intentional. The goal is to preserve as many attributes as possible, so that if you convert back to Aria you get your table in its original state.
It's an upstream bug https://bugs.mysql.com/bug.php?id=67727 .
Workaround:
MariaDB [test]> create table t1 (i int) engine=Aria transactional=1;Query OK, 0 rows affected (0.42 sec)MariaDB [test]> alter table t1 transactional = default, engine=InnoDB;Query OK, 0 rows affected (1.65 sec)Records: 0 Duplicates: 0 Warnings: 0