1

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)

2

Solution:

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

1

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!

  • I've also tried this. The response is this: > MariaDB> alter table materiales transactional=default, engine=InnoDB; > ERROR 1005 (HY000): Can't create table testdb1.#sql-678c_c8c91 (errno: 140 "Wrong create options") – user3099887 Jul 30 '18 at 8:52
  • Just found a solution: it also needs to add "default" to other create options. In my case "row_format" – user3099887 Jul 30 '18 at 9:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.