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