user1699099
user1699099

Reputation: 11

Latest version of MySQL gives error 1064 on code that used to work fine

Many servers now a days have the latest version of MySQL - which is giving an error when running the following piece of code:

$drop_table = mysql_query("DROP TABLE IF EXISTS `$t_ads`");
$sqls[$t_ads] = "CREATE TABLE `$t_ads` (
  `adid` int unsigned NOT NULL auto_increment,
  `adtitle` varchar(100) NOT NULL default '',
  `addesc` longtext NOT NULL,
  `area` varchar(50) NOT NULL default '',
  `email` varchar(50) NOT NULL default '',
  `showemail` enum('0','1','2') NOT NULL default '0',
  `password` varchar(50) NOT NULL default '',
  `code` varchar(35) NOT NULL default '',
  `cityid` smallint unsigned NOT NULL default '0',
  `subcatid` smallint unsigned NOT NULL default '0',
  `price` DECIMAL( 10, 2 ) NOT NULL default '0',
  `othercontactok` enum('0','1') NOT NULL default '0',
  `hits` int unsigned NOT NULL default '0',
  `ip` varchar(15) NOT NULL default '',
  `verified` enum('0','1') NOT NULL default '0',
  `abused` int unsigned NOT NULL default '0',
  `enabled` enum('0','1') NOT NULL default '0',
  `createdon` datetime NOT NULL default '0000-00-00 00:00:00',
  `expireson` datetime NOT NULL default '0000-00-00 00:00:00',
  `timestamp` timestamp(14) NOT NULL,
  `paid` enum('0','1','2') NOT NULL default '2',
  PRIMARY KEY  (`adid`),
  KEY `subcatid` (`subcatid`),
  KEY `cityid` (`cityid`),
  KEY `verified` (`verified`),
  KEY `enabled` (`enabled`),
  KEY `paid` (`paid`)
) TYPE=MyISAM;";

Servers with an older version of mysql seem to accept this, but the new version is not accepting it at all and giving an error. I have many errors related to it since the PHP database setup is little bigger. An example of the error is below:

#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, paid enum, NOT NULL default '2', PRIMARY KEY (adid), K' at line 21

Upvotes: 1

Views: 297

Answers (2)

Bjoern
Bjoern

Reputation: 16304

The plain SQL part of your code looks ok (just tested it with MySQL 5.6), though I'd generally advise not to use keywords like timestamp as a column name. The table will be created in MySQL 5.6, but you'll encounter two warnings:

  1. The syntax TIMESTAMP(14) is deprecated and will be removed in MySQL 6.0. Please use TIMESTAMP instead
  2. The syntax TYPE=storage_engine is deprecated and will be removed in MySQL 6.0. Please use ENGINE=storage_engine instead

If this doesn't help, please post the actual MySQL version you're working with.

Upvotes: 1

Salil
Salil

Reputation: 47482

Change your column name it should not be mysql keyword 'timestamp' and

chnage

`timestamp` timestamp(14) NOT NULL,

to

`<SOME OTHERNAME>` timestamp NOT NULL,

Upvotes: 0

Related Questions