CODEWITHSUNDEEP
mysqlsyntax-errormysql-error-1064
user2792736
user2792736

Reputation: 13

MySQL Version Related Syntax Errors

When uploading a SQL script using MySQL version 5.5.23-55 and importing using phpMyadmin version 3.4.11.1, I am getting the following goofy errors:

SQL query:

CREATE TABLE `dp_banks` (
  `id` int(11) NOT NULL auto_increment,
  `owner` int(11) NOT NULL default '0',
  `bname` varchar(128) NOT NULL default '',
  `baddress` varchar(128) NOT NULL default '',
  `bcity` varchar(64) NOT NULL default '',
  `bzip` varchar(16) NOT NULL default '',
  `bcountry` char(2) NOT NULL default '',
  `bstate` varchar(32) NOT NULL default '',
  `bphone` varchar(32) NOT NULL default '',
  `bnameacc` varchar(128) NOT NULL default '',
  `baccount` varchar(32) NOT NULL default '',
  `btype` char(2) NOT NULL default '',
  `brtgnum` varchar(9) NOT NULL default '',
  `bswift` varchar(32) NOT NULL default '',
  `status` tinyint(1) NOT NULL default '0',
  `default` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

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 AUTO_INCREMENT=1' at line 19

Researching on the net, I found an article that recommended changing TYPE=MyISAM to ENGINE=MyISAM. Making this change now gives the following errors:

SQL query:

CREATE TABLE `dp_confirms` (
  `id` int(11) NOT NULL auto_increment,
  `newuser` varchar(32) NOT NULL default '',
  `newpass` varchar(32) NOT NULL default '',
  `newquestion` varchar(255) NOT NULL default '',
  `newanswer` varchar(255) NOT NULL default '',
  `newmail` varchar(255) NOT NULL default '',
  `newfname` varchar(32) NOT NULL default '',
  `newlname` varchar(32) NOT NULL default '',
  `newcompany` varchar(128) NOT NULL default '',
  `newregnum` varchar(32) NOT NULL default '',
  `newdrvnum` varchar(32) NOT NULL default '',
  `newaddress` varchar(128) NOT NULL default '',
  `newcity` varchar(64) NOT NULL default '',
  `newcountry` char(2) NOT NULL default '',
  `newstate` varchar(32) NOT NULL default '',
  `newzip` varchar(32) NOT NULL default '',
  `newphone` varchar(64) NOT NULL default '',
  `newfax` varchar(64) NOT NULL default '',
  `sponsor` int(11) NOT NULL default '0',
  `confirm` varchar(255) NOT NULL default '',
  `cdate` timestamp(14) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `newuser` (`newuser`),
  KEY `newmail` (`newmail`)
) ENGINE=MyISAM AUTO_INCREMENT=1 ;

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 '(14) NOT NULL, PRIMARY KEY (id), UNIQUE KEYnewuser(newuser), KEY ' at line 22

Thanks in advance for all help.

Upvotes: 1

Views: 7308

Answers (3)

Jesus Flores
Jesus Flores

Reputation: 640

My experience was that i had to change two things:

TYPE=MyISAM AUTO_INCREMENT=1 ;

Replace this with

ENGINE=MyISAM AUTO_INCREMENT=1;

AND

cdate timestamp(14) NOT NULL,

to

cdate timestamp NOT NULL,

That worked for me

Upvotes: 0

peterm
peterm

Reputation: 92785

Other than changing TYPE to ENGINE change

  `cdate` timestamp(14) NOT NULL,
                   ^^^^

to

  `cdate` timestamp NOT NULL,

Here is SQLFiddle demo

Upvotes: 1

Vishwanath Dalvi
Vishwanath Dalvi

Reputation: 36631

) TYPE=MyISAM AUTO_INCREMENT=1 ;

Replace this with 

) ENGINE = MYISAM AUTO_INCREMENT =1;

Type has been replaced with engine.

TYPE keyword is depreciated (since 5.0) and not supported in MySQL5.5 (and I think even 5.1). Instead of TYPE keyword use ENGINE keyword.

Upvotes: 0

Related Questions