← Back to team overview

maria-discuss team mailing list archive

Re: MariaDB 10: how to convert Aria table to InnoDB?


BTW: that crazy behavior also breaks silently replication

Version: '10.0.17-MariaDB' socket: '/var/lib/mysql/mysql_replication.sock' port: 3307 thelounge 150319 18:34:05 [Note] Slave SQL thread initialized, starting replication in log 'bin.000325' at position 320, relay log './mysql-relay-bin.000709' position: 601 150319 18:34:05 [ERROR] Slave SQL: Error 'Can't create table `dbmail`.`#sql-7ba3_3` (errno: 140 "Wrong create options")' on query. Default database: 'dbmail'. Query: 'ALTER TABLE `systemevents` ENGINE = InnoDB', Gtid 0-1-147094, Internal MariaDB error code: 1005 150319 18:34:05 [Warning] Slave: Table storage engine 'InnoDB' does not support the create option 'TRANSACTIONAL=1' Error_code: 1478 150319 18:34:05 [Warning] Slave: InnoDB: invalid ROW_FORMAT specifier. Error_code: 1478 150319 18:34:05 [Warning] Slave: Can't create table `dbmail`.`#sql-7ba3_3` (errno: 140 "Wrong create options") Error_code: 1005 150319 18:34:05 [Warning] Slave: Got error 140 "Wrong create options" from storage engine InnoDB Error_code: 1030 150319 18:34:05 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'bin.000325' position 320
150319 18:34:05 [Note] InnoDB: 128 rollback segment(s) are active

Am 02.03.2015 um 15:54 schrieb Reindl Harald:
Am 02.03.2015 um 15:22 schrieb Jean Weisbuch:
Just as a side note, its possible to remove the TRANSACTIONAL=x option
by adding TRANSACTIONAL=default on the ALTER statement.

wouldn't a "ALTER TABLE `systemevents` ENGINE=InnoDB DEFAULTS;" be more
logical which should be default in case of strict mode or at least give
a clear message about it?

A similar issue arises if for example ROW_FORMAT=PAGE is used on the
ARIA table as it doesnt exists on InnoDB, it will issue a warning
"InnoDB: assuming ROW_FORMAT=COMPACT." but the resulting table will show
ROW_FORMAT=PAGE even if it uses the COMPACT row format...

and that is really confusing and may lead to weird problems

it should not assume but set it to ROW_FORMAT=COMPACT and only isse
errors if that for whatever reasons fails

what we now have is that you can't trust the output of params at all

Le 02/03/2015 15:18, Reindl Harald a écrit :

Am 02.03.2015 um 15:03 schrieb Ian Gilfillan:

On 02/03/2015 15:37, Reindl Harald wrote:
Am 02.03.2015 um 14:26 schrieb Ian Gilfillan:
This is probably because strict mode is set. If unset, it should
a warning, not an error. From MariaDB 10.1.2, you can use:
SET STATEMENT sql_mode='' FOR ALTER TABLE `systemevents` ENGINE =
which will unset strict mode just for that query, otherwise for older
versions just set it off and on again for the session to alter.

indeed *but* why does "TRANSACTIONAL=0" don't go away after convert to

Why is so that the change is reversible

that makes no sense, keep options which are invalid for the target
engine is completly wrong and leads to the above problems that you
can't convert tables only in the dirty default mode

if you now convert to InnoDB "TRANSACTIONAL=0" makes no sense
if you later decide to convert back to Aria you need to set that flag
in a follow-up step - but keep invalid and dirty options is just
asking for troubles until "alter table" has no way to delete them

Obviously this may not be desirable in many cases. See
https://mariadb.atlassian.net/browse/MDEV-5867 for a similar discussion
and a fix that was applied in 10.0.13

it is not desirebale in *any* sane case

Attachment: signature.asc
Description: OpenPGP digital signature

Follow ups