[#MDEV-21387] Unable to convert Aria Tables to MyISAM after specifying TRANSACTIONAL=0

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)

[MDEV-21387] Unable to convert Aria Tables to MyISAM after specifying TRANSACTIONAL=0 Created: 2019-12-23 Updated: 2023-07-28 Resolved: 2019-12-24
Status:	Closed
Project:	MariaDB Server
Component/s:	Data Definition - Alter Table
Affects Version/s:	10.2.26, 10.3.20
Fix Version/s:	N/A

[MDEV-21387] Unable to convert Aria Tables to MyISAM after specifying TRANSACTIONAL=0 Created: 2019-12-23 Updated: 2023-07-28 Resolved: 2019-12-24