Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

MySQL rejects CREATE TABLE ... TYPE=MyISAM from an old script

Errormysql error 'TYPE=MyISAM'

Short answer

TYPE is not a table option in the current MySQL manual. The option that names the storage engine is ENGINE, so TYPE=MyISAM in an old script or dump is a syntax error. Replace it with ENGINE=MyISAM or ENGINE=InnoDB, the default engine, and keep any table option before a SELECT in CREATE TABLE ... SELECT.

  • Applies to MySQL 8.0, MySQL 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Search the script, dump or application code for the old option, so you know how many statements need the same change.

    grep -rnE "TYPE ?= ?(MyISAM|InnoDB)" .
  2. 2Match what you see, then jump to its fix

    1. 1The error points at TYPE=MyISAM at the end of a CREATE TABLE statementThe statement names the engine with TYPE instead of ENGINEFix
    2. 2The failing file is an old export, a CMS installer or a script you did not write, with the same option on many tablesThe old option sits inside a dump or install script that you importFix
    3. 3The syntax error appears in a statement that builds a table from a query, and ENGINE comes after the SELECTENGINE is written after the SELECT in CREATE TABLE ... SELECTFix

What does the error look like?

  • mysql error 'TYPE=MyISAM'
  • ERROR 1064 (42000): You have an error in your SQL syntax; ... near 'TYPE=MyISAM' at line 5

Cause 1 of 3

The statement names the engine with TYPE instead of ENGINE

1Check

The CREATE TABLE section of the MySQL 8.4 manual lists the table option ENGINE, which specifies the storage engine for the table. It does not list TYPE.

grep -n "TYPE" create_tables.sql

2Fix

Use ENGINE. MyISAM works if you really need it; otherwise leave the option out, because the manual says new tables are created with InnoDB by default.

CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB;

3Verify

Run the statement again and look at the table's definition.

SHOW CREATE TABLE users;

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 3

The old option sits inside a dump or install script that you import

1Check

Count the statements that use the old option. Editing each one by hand is how a few get missed.

grep -c "TYPE=" dump.sql

2Fix

Replace the option in the whole file, keep a backup copy, and import the edited file. The replacement is ENGINE, the documented option.

sed -i.bak 's/TYPE=MyISAM/ENGINE=MyISAM/g' dump.sql

3Verify

The file no longer contains the old option, and the import runs without the syntax error.

grep -c "TYPE=MyISAM" dump.sql
mysql -u root -p mydb < dump.sql

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 3

ENGINE is written after the SELECT in CREATE TABLE ... SELECT

1Check

The manual says the ENGINE option is part of CREATE TABLE and should not be used following the SELECT, because that is a syntax error. The same is true for options such as CHARSET.

grep -n -i "select.*engine=" *.sql

2Fix

Move the table options in front of the SELECT.

CREATE TABLE test (a INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (a))
    ENGINE=InnoDB
    SELECT b, c FROM test2;

3Verify

Run the statement again.

SHOW CREATE TABLE test;

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Keep schema changes in migration files that you run on a current server before you ship them.
  • Leave the engine option out unless a table needs something other than the default InnoDB.
  • When you inherit old tables, convert them with ALTER TABLE table_name ENGINE=InnoDB, as the InnoDB conversion page describes.
  • Export dumps from the server you plan to import into, or test the import on a copy first.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.