Bug #17501 TYPE= no longer works in CREATE/ALTER TABLE
Submitted: 17 Feb 2006 4:18 Modified: 11 Oct 2009 9:55
Reporter: Arjen Lentz Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.1.7-BK,6.0.9,5.4.4 OS:Any (any)
Assigned to: CPU Architecture:Any
Tags: ALTER TABLE, CREATE TABLE, engine, type

[17 Feb 2006 4:18] Arjen Lentz
Description:
CREATE/ALTER TABLE ... ( ... ) TYPE=MyISAM
no longer works. The TYPE keyword is no longer accepted in this context.

I'm aware that TYPE= is deprecated since 4.1 and generates a warning. That is fine.
However, existing applications (some not open source) rely on this syntax, and so it would actually hinder adoption of MySQL 5.1 and beyond if we were to remove this keyword.
Surely that can't be the intention.

The 5.1 manual still mentions ENGINE|TYPE, so I'm not sure whether the abovementioned behaviour was an intentiontional change or a mistake. Either way, I hope it can be rectified.

How to repeat:
mysql> CREATE TABLE t1 ( a INT ) TYPE=MyISAM;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TYPE=MyISAM' at line 1

Suggested fix:
Keep in TYPE= syntax, there's no technical/syntactical reason to refuse it, and there are very good commercial reasons to keep supporting it. We want people to use 5.1 and beyond!
[17 Feb 2006 14:42] Valeriy Kravchuk
Verified just as described:

openxs@suse:~/dbs/5.1> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.1.7-beta

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE t1 ( a INT ) TYPE=MyISAM;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'TYPE=MyISAM' at line 1
[18 Feb 2006 3:18] Brian Aker
We need to get this in the manual that it has been removed.
[18 Feb 2006 3:32] Peter Laursen
However it works in 5.1.6 here.
I suggest that it should stay that way.
People are using all sorts of third-party clients that for historical reason use TYPE and not ENGINE.
[21 Feb 2006 1:06] Paul DuBois
I've added a note to the 5.1.7 changelog and the 5.1
upgrading sections that TYPE support has been removed,
and removed other mentions of TYPE in the 5.1 manual as
well.
[21 Feb 2006 5:29] Jeremy Cole
Is there a good reason to drop support for TYPE completely?  This makes application support really difficult.  ENGINE wasn't even introduced all that long ago.
[2 Mar 2006 10:25] Sergei Golubchik
Ok, TYPE= was restored.

A warning for all deprecated features now specifies in what precisely version this feature will be removed. For all features that were marked as deprecate in 5.1.7 it is 5.2
[2 Mar 2006 10:29] Sergei Golubchik
to document:

for 5.1.8 beta changelog - see above (type= restored, and version printed in warnings)
do NOT document TYPE= in the create table/alter table section
document for all deprecated features that they will dissapear in 5.2. The list is:

@@table_type
@@log_bin_trust_routine_creators
TIMESTAMP(N)
TYPE=
RESTORE TABLE
BACKUP TABLE
SHOW TABLE TYPES
SHOW INNODB STATUS
SHOW MUTEX STATUS
SHOW BDB LOGS
SHOW LOGS
LOAD TABLE FROM MASTER
[2 Mar 2006 11:05] Jon Stephens
Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

I've updated the 5.1.8 changelog and related documentation pursuant to the latest developer notes above.
[2 Mar 2006 11:37] Arjen Lentz
Thanks - but this just moves the same problem forward so we'll be back her later.
Please specify WHY it needs to be removed, rather than keeping it around deprecated?
I believe others have asked this also and I think it is a relevant question.
It's not extra "old" code we need to keep around, it's just a few extra chars in the parser.
[2 Mar 2006 12:41] Jon Stephens
Due to a lost packet or somesuch thing, I only saw the first half of the last developer comment when I viewed this bug previously, so to clarify what I've done:

1. I updated ONLY the material relevant to TYPE/ENGINE.

2. Before this proceeds any farther, I'm asking for an internal discussion/ruling.

3. I've changed this bug's status to In Progress pending resolution of this issue.
[2 Mar 2006 14:21] Stefan Hinz
Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

We've reworded things a bit in the documentation, toning down the meaning of "deprecated". We've also added a section with upcoming 5.2 changes and additions (this covers Serg's list): http://dev.mysql.com/doc/refman/5.1/en/mysql-5-2-plans.html
[11 May 2006 20:26] jurgen DEBO
using 5.1.7-beta

Well Joomla (Mambo) 1.0.8 use many 3th party extensions.  
And I can imagine some people do want to have backwards 
compatibility too.  And what to do with encrypted scripts ?

Most source does rely on:
create table.....TYPE=MyISAM

Some solutions: 
a) Warnings should be generated, no fatal errors. 
b) a setting in Mysql to be less strict.
c) a setting in Mysq to auto correct, with warning
d) a grep/sed/awk based pre-parser to auto correct queries
    which can be defined in an ini file (like preparse.ini)
[4 Feb 2009 16:45] Anton Anikin
Same problem again with latest MySQL 6.0.9
[4 Feb 2009 23:44] Arjen Lentz
Here we go again. There's legacy foo out there that can't be changed.
The advantage of removing the TYPE keyword from the parser is really irrelevant compared to the amount of hassle this causes in the dozens of millions of deployments that MySQL has!

So please just leave it in - yes, do keep noting it's deprecated, that's good.
But please don't remove it.
[17 Jul 2009 4:34] Arjen Lentz
And in 5.4.4, TYPE= has been removed again.
http://dev.mysql.com/doc/refman/5.4/en/news-5-4-4.html

See previous notes for rationale against doing this.
[18 Jul 2009 0:11] Davi Arnaut
I wonder what is the point of deprecating the keyword if it's never going to be removed.
Either remove it or remove the deprecation.
[23 Jul 2009 14:31] Sheeri Cabral
4.0.18 came out in February 2004. Isn’t 5 years long enough for a developer to change a silly word? If not, what *else* is wrong with the software? (I hate it when software gives me spurious warnings.) Let’s take for example InnoDB Hot Backup, which *still* in 2009 used TYPE internally. Ridiculous, especially given how expensive it is.

So, yeah — on the one hand I agree that "backwards compatibility is good, especially when it’s not a big deal to keep it." On the other hand — "update your damn software within 5 years."

And as Davi said, endless deprecation is silly.  

Legacy apps are legacy for a reason.  They can be run on legacy versions of MySQL if they need that functionality.
[11 Oct 2009 9:55] Sveta Smirnova
Thank you for the feedback.

I agree with Davi and Sheeri and according to comment "[2 Mar 2006 11:29] Sergei Golubchik" this option should be completely removed in version 5.2, so setting this to "Closed" again. This warning is issuing since version 5.0 and in 5.1 it contains exact version 6.0 which is moving to 5.4.* now. Regarding to warning in 5.1 I created new bug #47974.
[4 Jan 2011 19:55] Robert Chapin
I've been using MySQL for many years and have never seen a message about deprecated syntax.  Whatever you think was accomplished by deprecating it, never happened.
[14 May 2011 16:19] daru sze
Its EASY.
If You need old sql backup files , copy (import) to new version from msql base,
Just rename TYPE=MyISAM; whit ENGINE=MyISAM DEFAULT CHARSET=utf8;

works!!!!