MySQL rejects CREATE TABLE ... TYPE=MyISAM from an old script
mysql 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.
Fix it fast
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)" .2Match what you see, then jump to its fix
- 1
The error points at TYPE=MyISAM at the end of a CREATE TABLE statementThe statement names the engine with TYPE instead of ENGINEFix - 2
The 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
The 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
- 1
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.sql2Fix
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
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.sql2Fix
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.sql3Verify
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.sqlConfirmed by dev.mysql.com
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=" *.sql2Fix
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
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:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
MySQL says the table already exists when you create it
ERROR 1050 (42S01): Table 'users' already exists
Error 1050 means a table with that name is already in the schema.
3 causes, each with a fixDatabases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fix