[MDEV-8733] Table storage engine conversion does not remove TRANSACTIONAL flags Created: 2015-09-02  Updated: 2023-07-28  Resolved: 2019-12-24

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table
Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3, 10.4
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Karsten Richter Assignee: Unassigned
Resolution: Not a Bug Votes: 1
Labels: upstream
Environment:

Ubuntu 14.04 x86_64 deb from official binary repo

Issue Links:
Problem/Incident
causes MDEV-21387 Unable to convert Aria Tables to MyIS... Closed
Relates
relates to MDEV-31794 Preserved unsupported table flags bre... Open

 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' |
 
+---------+------+------------------------------------------------------------------------------------+



 Comments   
Comment by Elena Stepanova [ 2015-09-03 ]

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

Comment by Elena Stepanova [ 2019-05-01 ]

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)

Comment by Sergei Golubchik [ 2019-12-24 ]

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.

Generated at Thu Feb 08 07:29:22 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.