Member Avatar for 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.

Edited by Reverend Jim because: removed header markup
  • 3 Contributors
  • 6 Replies
  • 2K Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by Sutarusu

Dani AI

Generated

Nice catch by on the quoting issue. A quick rule of thumb for future readers: backticks are only for identifiers (table/column names), not for data values. Writing DEFAULT \0`tells MySQL to look for an identifier named 0 and triggers a parse error. For numeric columns, prefer a bare numeric literal likeDEFAULT 0`; single quotes produce a string literal and will be coerced, but numbers are clearer. See MySQL’s notes on identifier quoting and default-value handling. Schema object names, Data type default values.

The other stumbling block here is TIMESTAMP(14). In modern MySQL, the number in parentheses for temporal types is fractional-seconds precision (0..6), not a display width as in very old releases. So TIMESTAMP(14) is invalid and yields the “precision 14” error. Use plain TIMESTAMP or, if you need subsecond precision, TIMESTAMP(3) or similar. Example: 

created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)

Fractional seconds in time values.

While you are tidying the DDL, a couple of small improvements will save pain later:

  • Drop integer display widths; INT(4) does not limit range and the display width attribute is deprecated. Just use INT (optionally UNSIGNED) for counters, for example: 
messages_unread INT UNSIGNED NOT NULL DEFAULT 0

Numeric type attributes.

  • Prefer InnoDB over MyISAM unless you have a specific read-mostly use case; InnoDB is the default, supports transactions and FK constraints, and is generally recommended. Storage engines overview.

Thanks and for sharing the intermediate errors; those “near ...” hints often point exactly at the token where quoting or spacing went sideways.

Recommended Answers

All 6 Replies

Member Avatar for 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;
Member Avatar for 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

Edited by Reverend Jim because: removed header markup
Member Avatar for 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:

Member Avatar for 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

Edited by Reverend Jim because: removed header markup
Member Avatar for 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.

Member Avatar for Sutarusu

It worked that time. Thank you so much.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.