mysql - SQL query is not working on phpMyAdmin as I am getting an error

Question

I have entered this code on phpMyAdmin as an SQL query. I am trying to create a table (this is a copy and pasted code from another site)

   CREATE TABLE `members` (
`id` int(4) NOT NULL auto_increment,
`username` varchar(65) NOT NULL default '',
`password` varchar(65) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=2 ;
-- 
-- Dumping data for table `members`
--
INSERT INTO `members` VALUES (1, 'john', '1234');

I get the error

"#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 AUTO_INCREMENT=2' at line 6 "

Answer 1

It should be ENGINE not TYPE to specify the storage engine:

CREATE TABLE `members` (
`id` int(4) NOT NULL auto_increment,
`username` varchar(65) NOT NULL default '',
`password` varchar(65) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 ;

see CREATE TABLE syntax:

table_option: ENGINE [=] engine_name

The option TYPE was removed with MySQL 5.5, deprecated since MySQL 4.0:

The older TYPE option was synonymous with ENGINE. TYPE has been deprecated since MySQL 4.0 but is still supported for backward compatibility in MySQL 5.1 (excepting MySQL 5.1.7). Since MySQL 5.1.8, it produces a warning. It is removed in MySQL 5.5. You should not use TYPE in any new applications, and you should immediately begin conversion of existing applications to use ENGINE instead. (See the Release Notes for MySQL 5.1.8.)

Source: CREATE TABLE, MySQL 5.1