Storage Engines …

[peterlaursen]

With MySQL version 5 three new storage engines have come into existence.

* ARCHIVE

* CSV

* FEDERATED

and ISAM is no longer available (it is completely removed from the server code). This

Code:

create table `test`.`TableName` (    `id` bigint   NOT NULL AUTO_INCREMENT ,  `t` varchar (20)   NULL  , PRIMARY KEY ( `id` )  )type=ISAM

just creates a MyISAM table instead! A 'Show warnings' returns:

Quote:

Level      Code  Message                                                               

---

 

---

 

---

Warning    1287  'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead

Warning    1266  Using storage engine MyISAM for table 'tablename4' 

BTW: what would happen in 'strict mode'? Would table be created? Would 'type= …' ('type' instead of 'engine') be accepted in 'strict mode' at all ? I believe not!

I think that SQLyog has its list of storage engines hard-coded and does not query the server about which engines are available (or just version information) when displaying the engine type from “change table type. Am I right?

BTW: I also think that GEMINI tables are long-long-long gone!

As far as goes:

ARCHIVE: it must be pretty simple to support this in SQLyog ?!

CSV: same! However I am in doubt whether the distributed binaries from MySQL include the CSV engine. I can't create a CSV table when I try (get a MyISAM instead!)! So maybe you'll have to compile the server yourself to get it?

FEDERATED: That's the fun stuff! See my Blogs .. but not so simple!

Comments ?

[peterlaursen]

NO!

'change tabletype' behaves identically with @@sql_mode = 'STRICT_ALL_TABLE' and @@sql_mode = ''. Tables are changed to specified type when possible and to MyISAM when not specified table type is available. No matter whether keyword 'type' or 'engine' is used. Same warnings.

That is not very 'strict' 😮

[peterlaursen]

Further research:

there is an sql_mode available to control this feature named NO_ENGINE_SUBSTITUTION

http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

to disable this automatic substitution send

Code:

set session|global sql_mode = 'NO_ENGINE_SUBSTITUTION'

[Ritesh]

Hmmm 😮

This subject requires detailed study.

We will need to find out all commands similar to

Quote:

set session|global sql_mode = 'NO_ENGINE_SUBSTITUTION'

completely know their effect on the working of SQLyog.

I have added this in the TO-DO list but not with high priority 😀

[peterlaursen]

of course it must be studied!

But I think you should add support for the ARCHIVE storage engine soon. It is all handled by MySQL server. Probably only a few lines in the SQLyog code!

[Ritesh]

This again has an issue.

I think MySQL versions < 4.x does not provide information about the various storage engines supported. I mean, you cannot execute a SQL statement to get the list. Thus the names have to be hardcoded. For versions > 4.x, you can get it from an SQL query 😀 I dont remember which one though 😛

[peterlaursen]

Well if it must be hard-coded then be so. User must then select valid engines.

As of MySQL ver. 5 you get MyISAM tables when trying to create a GEMINI or ISAM (unless set session|global sql_mode = 'NO_ENGINE_SUBSTITUTION'). If ARCHIVE was added of course same error would occur with earlier MySQL versions. But the ARCHIVE engine is great for big DB's that are not 'in production' anymore. To support it the only thing SQLyog needs to do is to put the keyword ARCHIVE into an ALTER TABLE statement.

The reall tricky stuff is the FEDERATED engine …

[Author]

Posts