How to remove options from mariadb tables?

Question

Mariadb 10.2.8

I am trying to convert an aria table to InnoDB. My problem is

MariaDB> alter table materiales engine=innodb;

ERROR 1478 (HY000): Table storage engine 'InnoDB' does not support the create option 'TRANSACTIONAL=1'

I searched for this problem and didn't found a solution. Some people sugests using sql-mode = '', but it only changes the error and the conversion can't be done.

It looks like a bug, because the conversion to innodb should ignore this option but I think it could be walked arround by removing the "transactional=1" option.

Then my question is: I can modify options like ROW_FORMAT or TRANSACTIONAL, but how can I completely remove it? (whitout dumpling and re-creating the table because it is BIG)

Answer 1

Solution:

alter table materiales row_format=default,transactional=default, engine=InnoDB;

Answer 2

In MariaDB 10.1.26 your approach throws only a warning 1478, Table storage engine 'InnoDB' does not support the create option 'TRANSACTIONAL=1'.

The workaround given at https://mariadb.atlassian.net/browse/MDEV-8733 works very well:

alter table materiales transactional=default, engine=InnoDB;

No warning and the option TRANSACTIONAL has been removed from CREATE TABLE.

Unfortunately I could not test it on MariaDB 10.2.8!

Please do not test directly on a production environment, good luck!