Error in SQL Syntax and I have tried changing the NOT NULL and DEFAULT

[Sutarusu]

Ok, here is what I get when I run the query.

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 newmessageint(4) unsigned NOT NULL DEFAULT0,companyi' at line 4

Here is what it says is wrong.

CREATE TABLE `user` (
  `userid` char(50) NOT NULL default '',
  `password` char(12) NOT NULL default '',
  `lastlogin` timestamp(14) NOT NULL,
  `newmessage`int(4) unsigned NOT NULL DEFAULT `0`,
  `companyid` char(10) default NULL,
  `departmentid` char(10) default NULL,
  `status` char(50) default NULL,
  PRIMARY KEY  (`userid`),
  KEY `loginuser` (`userid`,`lastlogin`)
) ENGINE=MyISAM;

It is very frustrating because I know it is going to be something simple. Any suggestions would be greatly appreciated.

[jstfsklh211]

try

CREATE TABLE `user` (
  `userid` char(50) NOT NULL,
  `password` char(12) NOT NULL,
  `lastlogin` timestamp(14) NOT NULL,
  `newmessage`int(4) NOT NULL DEFAULT 0,
  `companyid` char(10) default NULL,
  `departmentid` char(10) default NULL,
  `status` char(50) default NULL,
  PRIMARY KEY  (`userid`),
  KEY `loginuser` (`userid`,`lastlogin`)
) ENGINE=MyISAM;

[Sutarusu]

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,
newmessageint(4) NOT NULL DEFAULT 0,
companyid char(10)' at line 4

[cereal]

Ok, here there are two errors on this line:

`newmessage`int(4) unsigned NOT NULL DEFAULT `0`,

The space between the column name and the type is missing, and the default value is defined with backticks instead of quotes, so:

`newmessage` int(4) unsigned NOT NULL DEFAULT '0',

But there is also another problem with the previous line:

`lastlogin` timestamp(14) NOT NULL,

Depending on the version in use you will get this error:

ERROR 1426 (42000): Too big precision 14 specified for 'lastlogin'. Maximum is 6.

To avoid it you can avoid to define the precision:

`lastlogin` timestamp NOT NULL,

For more information read these links:

• http://shankargopal.blogspot.it/2013/03/mysql-566-timestamp-columns-and-default.html
• http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp

[Sutarusu]

Thanks cereal, I added the space back in and I get the same message.

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 '
newmessage int(4) unsigned NOT NULL DEFAULT 0,
companyid char(10) d' at line 4

[cereal]

Works fine for me:

CREATE TABLE `user` (
  `userid` char(50) NOT NULL default '',
  `password` char(12) NOT NULL default '',
  `lastlogin` timestamp NOT NULL,
  `newmessage` int(4) unsigned NOT NULL DEFAULT 0,
  `companyid` char(10) default NULL,
  `departmentid` char(10) default NULL,
  `status` char(50) default NULL,
  PRIMARY KEY  (`userid`),
  KEY `loginuser` (`userid`,`lastlogin`)
) ENGINE=MyISAM;

Here's a live test: http://sqlfiddle.com/#!9/855fe/1

If it still does not work for you, then paste your updated schema.

[Sutarusu]

It worked that time. Thank you so much.