[Maria-discuss] MariaDB 10: how to convert Aria table to InnoDB?
how do someone convert a Aria table to another engine? MyISAM and InnoDB results in the same error ___________________________________________________________
MariaDB [dbmail]> ALTER TABLE `systemevents` ENGINE = InnoDB; ERROR 1478 (HY000): Table storage engine 'InnoDB' does not support the create option 'TRANSACTIONAL=1'
MariaDB [dbmail]> ALTER TABLE `systemevents` TRANSACTIONAL=0; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0
MariaDB [dbmail]> ALTER TABLE `systemevents` ENGINE = InnoDB; ERROR 1478 (HY000): Table storage engine 'InnoDB' does not support the create option 'TRANSACTIONAL=1' ___________________________________________________________
CREATE TABLE `systemevents` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `DeviceReportedTime` datetime DEFAULT NULL, `FromHost` varchar(30) CHARACTER SET latin1 COLLATE latin1_german1_ci NOT NULL, `Message` text, `SysLogTag` varchar(60) DEFAULT NULL, PRIMARY KEY (`ID`), KEY `FromHost` (`FromHost`), KEY `SysLogTag` (`SysLogTag`), KEY `DeviceReportedTime` (`DeviceReportedTime`) ) ENGINE=Aria AUTO_INCREMENT=698044 DEFAULT CHARSET=utf8 PACK_KEYS=0 PAGE_CHECKSUM=0 DELAY_KEY_WRITE=1 ROW_FORMAT=PAGE TRANSACTIONAL=0
This is probably because strict mode is set. If unset, it should return a warning, not an error. From MariaDB 10.1.2, you can use: SET STATEMENT sql_mode='' FOR ALTER TABLE `systemevents` ENGINE = InnoDB; 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.
On 02/03/2015 15:09, Reindl Harald wrote:
how do someone convert a Aria table to another engine? MyISAM and InnoDB results in the same error ___________________________________________________________
MariaDB [dbmail]> ALTER TABLE `systemevents` ENGINE = InnoDB; ERROR 1478 (HY000): Table storage engine 'InnoDB' does not support the create option 'TRANSACTIONAL=1'
MariaDB [dbmail]> ALTER TABLE `systemevents` TRANSACTIONAL=0; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0
MariaDB [dbmail]> ALTER TABLE `systemevents` ENGINE = InnoDB; ERROR 1478 (HY000): Table storage engine 'InnoDB' does not support the create option 'TRANSACTIONAL=1' ___________________________________________________________
CREATE TABLE `systemevents` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `DeviceReportedTime` datetime DEFAULT NULL, `FromHost` varchar(30) CHARACTER SET latin1 COLLATE latin1_german1_ci NOT NULL, `Message` text, `SysLogTag` varchar(60) DEFAULT NULL, PRIMARY KEY (`ID`), KEY `FromHost` (`FromHost`), KEY `SysLogTag` (`SysLogTag`), KEY `DeviceReportedTime` (`DeviceReportedTime`) ) ENGINE=Aria AUTO_INCREMENT=698044 DEFAULT CHARSET=utf8 PACK_KEYS=0 PAGE_CHECKSUM=0 DELAY_KEY_WRITE=1 ROW_FORMAT=PAGE TRANSACTIONAL=0
Am 02.03.2015 um 14:26 schrieb Ian Gilfillan:
This is probably because strict mode is set. If unset, it should return a warning, not an error. From MariaDB 10.1.2, you can use: SET STATEMENT sql_mode='' FOR ALTER TABLE `systemevents` ENGINE = InnoDB; 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 InnoDB?
'show create table' looks like below and the seems to confuse phpMyAdmin
i am pretty sure that i played around switching bewteen Aria/MyISAM/InnoDB with 5.5 multiple times and "sql-mode = STRICT_ALL_TABLES" is enabled on all machines for years - strange
CREATE TABLE `systemevents` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `DeviceReportedTime` datetime DEFAULT NULL, `FromHost` varchar(30) CHARACTER SET latin1 COLLATE latin1_german1_ci NOT NULL, `Message` text, `SysLogTag` varchar(60) DEFAULT NULL, PRIMARY KEY (`ID`), KEY `FromHost` (`FromHost`), KEY `SysLogTag` (`SysLogTag`), KEY `DeviceReportedTime` (`DeviceReportedTime`) ) ENGINE=InnoDB AUTO_INCREMENT=698045 DEFAULT CHARSET=utf8 PACK_KEYS=0 DELAY_KEY_WRITE=1 ROW_FORMAT=COMPRESSED TRANSACTIONAL=0
On 02/03/2015 15:09, Reindl Harald wrote:
how do someone convert a Aria table to another engine? MyISAM and InnoDB results in the same error ___________________________________________________________
MariaDB [dbmail]> ALTER TABLE `systemevents` ENGINE = InnoDB; ERROR 1478 (HY000): Table storage engine 'InnoDB' does not support the create option 'TRANSACTIONAL=1'
MariaDB [dbmail]> ALTER TABLE `systemevents` TRANSACTIONAL=0; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0
MariaDB [dbmail]> ALTER TABLE `systemevents` ENGINE = InnoDB; ERROR 1478 (HY000): Table storage engine 'InnoDB' does not support the create option 'TRANSACTIONAL=1' ___________________________________________________________
CREATE TABLE `systemevents` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `DeviceReportedTime` datetime DEFAULT NULL, `FromHost` varchar(30) CHARACTER SET latin1 COLLATE latin1_german1_ci NOT NULL, `Message` text, `SysLogTag` varchar(60) DEFAULT NULL, PRIMARY KEY (`ID`), KEY `FromHost` (`FromHost`), KEY `SysLogTag` (`SysLogTag`), KEY `DeviceReportedTime` (`DeviceReportedTime`) ) ENGINE=Aria AUTO_INCREMENT=698044 DEFAULT CHARSET=utf8 PACK_KEYS=0 PAGE_CHECKSUM=0 DELAY_KEY_WRITE=1 ROW_FORMAT=PAGE TRANSACTIONAL=0
I agree with Harald here!
-- Peter
On Mon, Mar 2, 2015 at 2:37 PM, Reindl Harald h.reindl@thelounge.net wrote:
Am 02.03.2015 um 14:26 schrieb Ian Gilfillan:
This is probably because strict mode is set. If unset, it should return a warning, not an error. From MariaDB 10.1.2, you can use: SET STATEMENT sql_mode='' FOR ALTER TABLE `systemevents` ENGINE = InnoDB; 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 InnoDB?
'show create table' looks like below and the seems to confuse phpMyAdmin
i am pretty sure that i played around switching bewteen Aria/MyISAM/InnoDB with 5.5 multiple times and "sql-mode = STRICT_ALL_TABLES" is enabled on all machines for years - strange
CREATE TABLE `systemevents` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `DeviceReportedTime` datetime DEFAULT NULL, `FromHost` varchar(30) CHARACTER SET latin1 COLLATE latin1_german1_ci NOT NULL, `Message` text, `SysLogTag` varchar(60) DEFAULT NULL, PRIMARY KEY (`ID`), KEY `FromHost` (`FromHost`), KEY `SysLogTag` (`SysLogTag`), KEY `DeviceReportedTime` (`DeviceReportedTime`) ) ENGINE=InnoDB AUTO_INCREMENT=698045 DEFAULT CHARSET=utf8 PACK_KEYS=0 DELAY_KEY_WRITE=1 ROW_FORMAT=COMPRESSED TRANSACTIONAL=0
On 02/03/2015 15:09, Reindl Harald wrote:
how do someone convert a Aria table to another engine? MyISAM and InnoDB results in the same error ___________________________________________________________
MariaDB [dbmail]> ALTER TABLE `systemevents` ENGINE = InnoDB; ERROR 1478 (HY000): Table storage engine 'InnoDB' does not support the create option 'TRANSACTIONAL=1'
MariaDB [dbmail]> ALTER TABLE `systemevents` TRANSACTIONAL=0; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0
MariaDB [dbmail]> ALTER TABLE `systemevents` ENGINE = InnoDB; ERROR 1478 (HY000): Table storage engine 'InnoDB' does not support the create option 'TRANSACTIONAL=1' ___________________________________________________________
CREATE TABLE `systemevents` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `DeviceReportedTime` datetime DEFAULT NULL, `FromHost` varchar(30) CHARACTER SET latin1 COLLATE latin1_german1_ci NOT NULL, `Message` text, `SysLogTag` varchar(60) DEFAULT NULL, PRIMARY KEY (`ID`), KEY `FromHost` (`FromHost`), KEY `SysLogTag` (`SysLogTag`), KEY `DeviceReportedTime` (`DeviceReportedTime`) ) ENGINE=Aria AUTO_INCREMENT=698044 DEFAULT CHARSET=utf8 PACK_KEYS=0 PAGE_CHECKSUM=0 DELAY_KEY_WRITE=1 ROW_FORMAT=PAGE TRANSACTIONAL=0
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
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 return a warning, not an error. From MariaDB 10.1.2, you can use: SET STATEMENT sql_mode='' FOR ALTER TABLE `systemevents` ENGINE = InnoDB; 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 InnoDB?
Why is so that the change is reversible. 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.
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 return a warning, not an error. From MariaDB 10.1.2, you can use: SET STATEMENT sql_mode='' FOR ALTER TABLE `systemevents` ENGINE = InnoDB; 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 InnoDB?
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
Just as a side note, its possible to remove the TRANSACTIONAL=x option by adding TRANSACTIONAL=default on the ALTER statement.
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...
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 return a warning, not an error. From MariaDB 10.1.2, you can use: SET STATEMENT sql_mode='' FOR ALTER TABLE `systemevents` ENGINE = InnoDB; 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 InnoDB?
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
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Thanks to Jean. Even this works:
CREATE TABLE `systemevents` ( `ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `DeviceReportedTime` DATETIME DEFAULT NULL, `FromHost` VARCHAR(30) CHARACTER SET latin1 COLLATE latin1_german1_ci NOT NULL, `Message` TEXT, `SysLogTag` VARCHAR(60) DEFAULT NULL, PRIMARY KEY (`ID`), KEY `FromHost` (`FromHost`), KEY `SysLogTag` (`SysLogTag`), KEY `DeviceReportedTime` (`DeviceReportedTime`) ) ENGINE = aria AUTO_INCREMENT=698044 DEFAULT CHARSET=utf8 PACK_KEYS=0 PAGE_CHECKSUM=0 DELAY_KEY_WRITE=1 ROW_FORMAT=PAGE TRANSACTIONAL=0;
ALTER TABLE `systemevents` ENGINE = INNODB ;
ALTER TABLE `systemevents` TRANSACTIONAL=DEFAULT;
On Mon, Mar 2, 2015 at 3:22 PM, Jean Weisbuch jean@phpnet.org wrote:
Just as a side note, its possible to remove the TRANSACTIONAL=x option by adding TRANSACTIONAL=default on the ALTER statement.
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...
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 return a warning, not an error. From MariaDB 10.1.2, you can use: SET STATEMENT sql_mode='' FOR ALTER TABLE `systemevents` ENGINE = InnoDB; 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 InnoDB?
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
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
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 return a warning, not an error. From MariaDB 10.1.2, you can use: SET STATEMENT sql_mode='' FOR ALTER TABLE `systemevents` ENGINE = InnoDB; 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 InnoDB?
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
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 return a warning, not an error. From MariaDB 10.1.2, you can use: SET STATEMENT sql_mode='' FOR ALTER TABLE `systemevents` ENGINE = InnoDB; 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 InnoDB?
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
Hi, Reindl!
On Mar 19, Reindl Harald wrote:
BTW: that crazy behavior also breaks silently replication
That's a bug.
The intention always was to ignore unknown engine options in the replication thread. But TRANSACTIONAL is an old option, added before a proper support for engine specific options was implemented. So may be it slipped somehow and the check doesn't completely apply to it. Could you report a bug, please?
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
Regards, Sergei
At least the error/warning message is wrong IMO.
this
Table storage engine 'InnoDB' does not support the create option 'TRANSACTIONAL=1'
should rather be something like
Table storage engine 'InnoDB' does not support the create option 'TRANSACTIONAL'. We preserve it though so that ALTER operation can reversed"
In Haralds test case it was 'TRANSACTIONAL=0'. But it does not matter if it was "1" or 0". The same error or warning occurs.
And if it confuses clients, applications and scripts (as Harald reports with phpMyAdmin), how do we remove it? Is there an ALTER TABLE statement that can do it (like "ALTER TABLE .. CLEANUP CREATE_OPTIONS;" removing any option that has no effect with current storage engine)?
-- Peter
On Mon, Mar 2, 2015 at 3:03 PM, Ian Gilfillan ian@mariadb.org wrote:
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 return a warning, not an error. From MariaDB 10.1.2, you can use: SET STATEMENT sql_mode='' FOR ALTER TABLE `systemevents` ENGINE = InnoDB; 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 InnoDB?
Why is so that the change is reversible. 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.
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Hi, Reindl!
On Mar 02, Reindl Harald wrote:
how do someone convert a Aria table to another engine? MyISAM and InnoDB results in the same error ___________________________________________________________
MariaDB [dbmail]> ALTER TABLE `systemevents` ENGINE = InnoDB; ERROR 1478 (HY000): Table storage engine 'InnoDB' does not support the create option 'TRANSACTIONAL=1'
MariaDB [dbmail]> ALTER TABLE `systemevents` TRANSACTIONAL=0; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0
Try TRANSACTIONAL=DEFAULT.
Regards, Sergei
Actually I think the behavior of MariaDB here is somewhat consistent with how MySQL always did - see
CREATE TABLE blah (id INT) ENGINE = MYISAM MAX_ROWS = 500; ALTER TABLE blah ENGINE = INNODB; SHOW CREATE TABLE blah; /* CREATE TABLE `blah` ( `id` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 MAX_ROWS=500 */
In my understanding MAX_ROWS (and more create options back from MySQL 2 and 3) has no effect whatsoever with InnoDB (correct me if am wrong). But they are still listed and not removed by ALTER TABLE. Actually I am not even sure it has any effect with MyISAM of recent versions. However the above works as shown in *ALL* sql_modes (even 'strict_all tables'). So there is only a *somewhat* consistency. The behavior shown was like that even before sql_modes in MySQL came into existence.
Just an example that MySQL (and MariaDB) is ridden with lots of old stuff causing inconsistencies that could need a cleanup. But it is difficult to do without breaking compability (with Oracle, with old applications).
-- Peter
On Mon, Mar 2, 2015 at 3:57 PM, Sergei Golubchik serg@mariadb.org wrote:
Hi, Reindl!
On Mar 02, Reindl Harald wrote:
how do someone convert a Aria table to another engine? MyISAM and InnoDB results in the same error ___________________________________________________________
MariaDB [dbmail]> ALTER TABLE `systemevents` ENGINE = InnoDB; ERROR 1478 (HY000): Table storage engine 'InnoDB' does not support the create option 'TRANSACTIONAL=1'
MariaDB [dbmail]> ALTER TABLE `systemevents` TRANSACTIONAL=0; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0
Try TRANSACTIONAL=DEFAULT.
Regards, Sergei
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
But InnoDB doesn't support DELAY_KEY_WRITE or ROW_FORMAT=PAGE. In other words, TRANSACTIONAL is the only option that causes this problem? This doesn't seem too consistent to me...
Federico
-------------------------------------------- Lun 2/3/15, Peter Laursen peter_laursen@webyog.com ha scritto:
Oggetto: Re: [Maria-discuss] MariaDB 10: how to convert Aria table to InnoDB? A: "Sergei Golubchik" serg@mariadb.org Cc: "Mailing-List mariadb" maria-discuss@lists.launchpad.net Data: Lunedì 2 marzo 2015, 17:01
Actually I think the behavior of MariaDB here is somewhat consistent with how MySQL always did - see CREATE TABLE blah (id INT) ENGINE = MYISAM MAX_ROWS = 500;ALTER TABLE blah ENGINE = INNODB;SHOW CREATE TABLE blah;/*CREATE TABLE `blah` ( `id` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1 MAX_ROWS=500*/ In my understanding MAX_ROWS (and more create options back from MySQL 2 and 3) has no effect whatsoever with InnoDB (correct me if am wrong). But they are still listed and not removed by ALTER TABLE. Actually I am not even sure it has any effect with MyISAM of recent versions. However the above works as shown in *ALL* sql_modes (even 'strict_all tables'). So there is only a *somewhat* consistency. The behavior shown was like that even before sql_modes in MySQL came into existence.
Just an example that MySQL (and MariaDB) is ridden with lots of old stuff causing inconsistencies that could need a cleanup. But it is difficult to do without breaking compability (with Oracle, with old applications).
-- Peter On Mon, Mar 2, 2015 at 3:57 PM, Sergei Golubchik serg@mariadb.org wrote: Hi, Reindl!
On Mar 02, Reindl Harald wrote:
how do someone convert a Aria table to another
engine?
MyISAM and InnoDB results in the same error
___________________________________________________________
MariaDB [dbmail]> ALTER TABLE `systemevents` ENGINE
= InnoDB;
ERROR 1478 (HY000): Table storage engine
'InnoDB' does not support the
create option 'TRANSACTIONAL=1'
MariaDB [dbmail]> ALTER TABLE `systemevents`
TRANSACTIONAL=0;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
Try TRANSACTIONAL=DEFAULT.
Regards,
Sergei
_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp
-----Segue allegato-----
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
participants (6)
-
Federico Razzoli -
Ian Gilfillan -
Jean Weisbuch -
Peter Laursen -
Reindl Harald -
Sergei Golubchik