MySQL Bugs: #59288: CREATE TABLE TYPE Errors Make MySQL 5.5 Unusable

Bug #59288	CREATE TABLE TYPE Errors Make MySQL 5.5 Unusable
Submitted:	4 Jan 2011 19:19	Modified:	5 Jan 2011 13:17
Reporter:	Robert Chapin	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S1 (Critical)
Version:	5.5.8	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I am submitting this bug on behalf of the XMB forum community.  It was recently brought to my attention that the XMB software will not work on MySQL 5.5 due to incompatibility of the DDL.  Our software uses the TYPE=MyISAM syntax universally and it is not a simple matter for us to change this, test it, and release a new version to accommodate a new DDL.  It is far more important that we continue to support versions 4.1 through 5.0, which are used by the majority of our webmasters.  At this time, we cannot support MySQL 5.5 unless the DDL is backward compatible with existing servers.

How to repeat:
Try installing XMB with MySQL 5.5.

@Chapin.  TYPE keyword is depreciated (since 5.0) and not supported in MySQL5.5 (and I think even 5.1).  Instead of TYPE keyword use ENGINE keyword.

This is not a bug with MySQL but with the client application.

Peter
(not a MySQL person)

XMB pre-dates MySQL 5.0 by many years and is extremely stable in the wild.  Breaking the DDL does not mean XMB has bugs in it, nor is it going to convince anyone that something is deprecated when nobody is aware of that.

TYPE keyword is depreciated for 5 years and now totally unsupported in recent servers. You will get nowhere with this kindb of reprot.  Soon a MySQL person will close as *not a bug*.  

Client applications will have to be maintained to support updated server constructs. If XMB developers have been sleeping for 5 years it is not a MySQL problem.

Peter
(still not a MySQL person)

And likewise it is not an XMB problem when every version of MySQL works except for 5.5.  Nobody is asleep at the wheel in this situation.  I've personally never seen MySQL emit a deprecated message, and the MySQL devs must have considered the need for the DDL to not fail spectacularly.  If devs want to officially say that MySQL will not work with out software, then I'm all ears.

See also Joomla http://forum.joomla.org/viewtopic.php?p=2355273&sid=ed921b6deb1c972eea4184f0f2908106#p2355...

TYPE keyword won't work!  You will have to (in your application) to SELECT VERSION() and use TYPE or ENGINE version-dependently.

But forget about TYPE.  Everything in MySQL before 5.1 is out of support. 

Every MySQL developer will have to *carefully* read the release notes of every new MySQL server release.  If you team had done you would have handled this from 4-5 years ago. I devote 2-3 hours every day reading MySQL docs, bug reports and discussions on the mysql.planet - because that is my role in the organization I am a part of - and if I (or somebody else) we would fail to provide proper client code for every server version.

I am just a not-mysql-person.  But I have submitted several hundred bugs here for the last 5 years (of which ~50% were 'verified').  I know perfectly what the criteria are. The criteria are that if the server behave as it is documented it is per definition *not a bug* (but I do not always agree with this criteria myself, actually!).

The short thing is that you are wasting your time submitting such report. Use version-dependent SQL syntax if you want to support more server versions. Check documentation before submitting a bug report.

makes no difference.  It seems Joomla developers also compose client code not fit for every server version. READ RELEASE NOTES!

The only precedent for SQL version checks in the history of XMB was the minimum version number.  Perhaps it is time to add a maximum version number to avoid this error.

The MySQL server has been warning developers of this change for many years. Maybe it's just not one of your best practices to check warnings created by statements your application executes?

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.1.31    | 
+-----------+
1 row in set (0.00 sec)

mysql> create table foo (field1 int) type=innodb;
Query OK, 0 rows affected, 1 warning (0.14 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                     |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | The syntax 'TYPE=storage_engine' is deprecated and will be removed in MySQL 5.2. Please use 'ENGINE=storage_engine' instead | 
+---------+------+-----------------------------------------------------------------------------------------------------------------------------+

Mark, that is correct in so far as I have never heard of that before now.  Nothing in my experience with MySQL suggested that would be necessary.

> The only precedent for SQL version checks in the history of XMB was the minimum version
> number.  Perhaps it is time to add a maximum version number to avoid this error.

Another option is to fix XMB to use ENGINE instead of TYPE. The ENGINE syntax is supported since long before 4.1.

Our fine manual for versions 3.23 ... 4.1 (http://dev.mysql.com/doc/refman/4.1/en/create-table.html) says:

"The ENGINE and TYPE options specify the storage engine for the table. ENGINE was added in MySQL 4.0.18 (for 4.0) and 4.1.2 (for 4.1). It is the preferred option name as of those versions, and TYPE has become deprecated. TYPE is supported throughout the 4.x series, but likely will be removed in the future."

So, for all versions since 4.0.18 and 4.1.2 you can use ENGINE. 

And our manual for version 5.5 (http://dev.mysql.com/doc/refman/5.5/en/create-table.html) clearly says that you MUST use ENGINE in 5.5:

"Note

The older TYPE option was synonymous with ENGINE. TYPE was deprecated in MySQL 4.0 and removed in MySQL 5.5. When upgrading to MySQL 5.5 or later, you must convert existing applications that rely on TYPE to use ENGINE instead."

These are facts that nobody is going to change or fix in any way. Just take them into account. 

This is not a bug.

Unusuable it is, then.  We will consider supporting MySQL 5.5 after we can rewrite all of the DDL queries in our application.