PHP And Mysql Error ‘TYPE=MyISAM’ With Failure To Install Php Script

If you install an older PHP script or application that was designed to use MySQL 5.1, under MySQL 5.5 or 5.6, this error will either be displayed on-screen or will be logged in the website’s PHP or MySQL error log files.

#1064 – 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 …

MyISAM in MySQL 5.5+

The MyISAM storage engine is still available in ALL versions of MySQL, including 5.5 an 5.6 (and can also be used as the default storage engine).

The only things that have changed from MySQL 5.1 is that:

  1. InnoDB is used IF the table type is not explicitly specified when the table is created.
  2. The SQL syntax was changed from "TYPE=" to "ENGINE="

Fixing ‘TYPE=MyISAM’ MySQL Error

Your issue is a result of depreciated SQL syntax being used that is no longer valid in MySQL 5.5+.

MySQL 5.5+ no longer uses keyword "TYPE" to specify the database engine to use for the table (e.g., InnoDB, MyISAM). MySQL 5.5+ uses the keyword "ENGINE" instead.

To fix the broken PHP script or application, edit the files manually, or use an editor like Notepad++, to search and replace in all the *.sql files (or in *.php files if it’s generating the DB dynamically in some function), and change all occurrences of "TYPE=" to "ENGINE=".

Usually you only have to edit 1 SQL or PHP file, that is used to create the database on installation.

Default Storage Engine MyISAM

If your PHP script or application depends on MyISAM (for whatever reason), AND it does not specify the ENGINE type when creating new databases and tables…

Edit MySQL 5.5+’s my.ini file to change MySQL back to using MyISAM by default…

default-storage-engine=MYISAM

Otherwise the InnoDB storage engine will be used.

Other Incompatible Changes

While the majority of the issues will be solved with the above changes, there are also a number of other issues that can come up…

Upgrading from MySQL 5.1 to 5.5

For example, you should replace all occurrences of "TIMESTAMP(N)" with "TIMESTAMP".

Opening the MySQL shell, creating a test database, useing that database, and executing SOURCE C:/path/to/file.sql, will give you a good view of the issues in the SQL file – as it tries to create its tables.

If you import the MySQL data files instead of the SQL dumpfiles, make sure to also run ‘mysql_upgrade‘.

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>