[mysql] 1064 error in CREATE TABLE ... TYPE=MYISAM

Here is my error(if you need any more info just ask)- Error SQL query:

CREATE TABLE dave_bannedwords(

id INT( 11 ) NOT NULL AUTO_INCREMENT ,
word VARCHAR( 60 ) NOT NULL DEFAULT  '',
PRIMARY KEY ( id ) ,
KEY id( id )
) TYPE = MYISAM ;

MySQL said:

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 6

SELECT Email, COUNT(*)
FROM user_log    
WHILE Email IS NOT NULL    
GROUP BY Email    
HAVING COUNT(*) > 1    
ORDER BY UpdateDate DESC 

MySQL said: Documentation #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 36

Which correction below:

CREATE TABLE users_online (
  ip varchar(15) NOT NULL default '',
  time int(11) default NULL,
  PRIMARY KEY  (ip),
  UNIQUE KEY id (ip),
  KEY id_2 (ip)
  TYPE=MyISAM;
)
#                           
# Data untuk tabel `users_online`
#

INSERT INTO users_online VALUES ('127.0.0.1', 1158666872);

CREATE TABLE `admnih` (
  `id` int(255) NOT NULL auto_increment,
  `asim` varchar(255) NOT NULL default '',
  `brid` varchar(255) NOT NULL default '',
  `rwtbah` int(1) NOT NULL default '0',
  `esmmwkeh` varchar(255) NOT NULL default '',
  `mrwr` varchar(255) NOT NULL default '',
  `tid` int(255) NOT NULL default '0',
  `alksmfialdlil` int(255) NOT NULL default '0',
  `tariktsjil` varchar(255) NOT NULL default '',
  `aimwke` varchar(255) NOT NULL default '',
  `twkie` text NOT NULL,
  `rwtbahkasah` int(255) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=2 ;

Try the below query

CREATE TABLE card_types (
  card_type_id int(11) NOT NULL auto_increment,
  name varchar(50) NOT NULL default '',
  PRIMARY KEY  (card_type_id),
) ENGINE = MyISAM ;

A complementary note about CREATE TABLE .. TYPE="" syntax in SQL dump files

TLDR: If you still get CREATE TABLE ... TYPE="..." statements in SQL dump files generated by third party tools, it most certainly indicates that your server is configured to use a default sqlmode of MYSQL40 or MYSQL323.

Long story

As it was said by others, the TYPE argument to CREATE TABLE has been deprecated for a long time in MySQL. mysqldump correctly uses the ENGINE argument, unless you specifically ask it to generate a backward compatible dump (for example using --compatible=mysql40 in versions of mysqldump up to 5.7).

However, many external SQL dump tools (for example, those integrated in MySQL clients such as phpmyadmin, Navicat and DBVisualizer, as well as those used by external automated backup services such as iControlWP) are not specifically aware of this change, and instead rely on the SHOW CREATE TABLE ... command to provide table creation statements for each tables (and just to it make it clear: this is actually a good thing). However, the SHOW CREATE TABLE will actually produce outdated syntax, including the TYPE argument, if the sqlmode variable is set to MYSQL40 or MYSQL323.

Therefore, if you still get CREATE TABLE ... TYPE="..." statements in SQL dump files generated by third party tools, it most certainly indicates that your server is configured to use a default sqlmode of MYSQL40 or MYSQL323.

These sqlmodes basically configure MySQL to retain some backward compatible behaviours, and using them by default was largely recommended a few years ago. It is however highly improbable that you still have any code that wouldn't work correctly without these modes. Anyway, MYSQL40, MYSQL323 and several other similar sqlmodes have themselves been deprecated and are not supported in MySQL 8.0 and higher.

If your server is still configured with these sqlmodes and you are worried that some legacy program might fail if you change these, then one possibility is to set the sqlmode locally for that program, by executing SET SESSION sql_mode = 'MYSQL40'; immediately after connection. Note that this should only be considered as a temporary patch, and will not work in MySQL 8.0 and higher.

A more future-proof solution that do not involve rewriting your SQL queries would be to determine exactly which compatibility features need to be enable, and to enable only those, on a per-program basis (as described previously). The default sqlmode (that is, in server's configuration) should ideally be left unset (which will use official MySQL defaults for your current version). The full list of sqlmode (as of MySQL 5.7) is described here: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html.