Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Fix
-
10.2.26, 10.3.20
-
None
-
CentOS 7
Description
TRANSACTIONAL flag is not well managed in ALTER TABLE.
When an Aria table is created without specifying TRANSACTIONAL, it can be converted to MyISAM with 'ALTER TABLE ENGINE_MyISAM'.
When an Aria table is created and subsequently altered with 'ALTER TABLE X TRANSACTIONAL=1; ALTER TABLE X TRANSACTIONAL=0' it can no longer be converted to MyISAM at all because of the orphaned TRANSACTIONAL attribute. Instead, the server produces this (incorrect) message:
ERROR 1478 (HY000): Table storage engine 'MyISAM' does not support the create option 'TRANSACTIONAL=1'
|
To be clear - once the TRANSACTIONAL flag is explicitly started and set back to 0, this message failing the ALTER TABLE and incorrectly claiming TRANSACTIONAL=1 is the result.
What this means to users is that altering tables from MyISAM to Aria not only carries a 40% performance penalty, but is irreversible after using the TRANSACTIONAL flag.
The flag is harmless for other storage engines, producing the warning mentioned in MDEV-8733, but they make going back to MyISAM impossible without a full table migration in this case. 10.4 shows a warning instead of an error and completes the operation.
MariaDB [t34628]> create table taria3( id int(10) unsigned NOT NULL AUTO_INCREMENT,
|
-> `modified` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
|
-> `val` int(10) unsigned DEFAULT NULL,
|
-> `blah` varchar(511) DEFAULT NULL,
|
-> PRIMARY KEY (`id`) ) Engine=MyISAM;
|
Query OK, 0 rows affected (0.03 sec)
|
|
|
MariaDB [t34628]> alter table taria3 engine=aria, transactional=0;
|
Query OK, 0 rows affected (0.07 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [t34628]> alter table taria3 engine=myisam;
|
ERROR 1478 (HY000): Table storage engine 'MyISAM' does not support the create option 'TRANSACTIONAL=1'
|
MariaDB [t34628]> show warnings;
|
+-------+------+------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+------------------------------------------------------------------------------------+
|
| Error | 1478 | Table storage engine 'MyISAM' does not support the create option 'TRANSACTIONAL=1' |
|
+-------+------+------------------------------------------------------------------------------------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [t34628]> show create table taria3\G
|
*************************** 1. row ***************************
|
Table: taria3
|
Create Table: CREATE TABLE `taria3` (
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
`modified` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
|
`val` int(10) unsigned DEFAULT NULL,
|
`blah` varchar(511) DEFAULT NULL,
|
PRIMARY KEY (`id`)
|
) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1 TRANSACTIONAL=0
|
1 row in set (0.00 sec)
|
Attachments
Issue Links
- is caused by
-
-
- Closed
-
- relates to
-
-
- Closed
-
See IGNORE_BAD_TABLE_OPTIONS