MySQL Error Code 1064

a_m0d · 02-26-2009, 07:21 AM

I am trying to create a simple table in MySQL Administrator, but every time I press apply changes and then execute, it comes up with an error. Here is the SQL code:

Code:

CREATE TABLE `cabinetsDB`.`TabManufacturers` (
  `manufacturerID` INT  NOT NULL AUTO_INCREMENT,
  `manufacturerName` VARCHAR  NOT NULL,
  PRIMARY KEY (`manufacturerID`)
)
ENGINE = MyISAM;

and here is the error message:

Code:

Error executing SQL commands to create table.
MySQL Error Nr. 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 'NOT NULL,
  PRIMARY KEY (`manufacturerID`)
)
ENGINE = MyISAM' at line 3

Any insight would be appreciated. I think that this must be a bug, since I haven't changed any of the SQL code - it is exactly as generated by MySQL Admin.

JulianTosh · 02-27-2009, 01:04 AM

try:

Code:

CREATE TABLE `cabinetsDB`.`TabManufacturers` (
  `manufacturerID` INT  NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `manufacturerName` VARCHAR  NOT NULL
);

a_m0d · 02-27-2009, 01:34 AM

Sorry, still doesn't work. Here is the error message:

Code:

Error executing SQL commands to create table.
MySQL Error Nr. 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 'NOT NULL
)' at line 3

JulianTosh · 02-27-2009, 01:53 AM

Hmm. Sorry, I got nuthin.

The only thing I can think of is to try some minor syntax changes like:

Code:

USE cabinetsDB;
CREATE TABLE TabManufacturers (
  manufacturerID INT  NOT NULL AUTO_INCREMENT PRIMARY KEY,
  manufacturerName VARCHAR  NOT NULL
);

...taking the db out of the namespace and removing the single quotes (they actually looked like back ticks) since there were no spaces in the names...

If that doesn't work, a real mysql guru will be by shortly to bitch slap me and help you out. 8D

a_m0d · 02-27-2009, 02:11 AM

No, it still doesn't work. It almost seems to be a problem with the server rather than the code. I know that I have got the permissions necessary to do this - I have even tried it with root access. So it certainly isn't a permissions problem, and I can't think of anything else it could be, since it seems to be valid syntax.

tanveer · 02-27-2009, 03:15 AM

Hi,
MySQL DB version, compiled/rpm? What user are you using and what are permisson set for that user in mysql.user table?

Can you create any database?

a_m0d · 02-27-2009, 04:02 AM

Hi, Sorry for all the hassle. I worked out what was wrong. For some reason, when I select a VARCHAR or CHAR field type, MySQL Admin does not create a length for the field, even though the correct syntax should be VARCHAR(lenght). Adding this by hand fixed my problem, however there is actually no option that I can see in MySQL Admin to let you set this length. Maybe this is a bug? Anyway, thanks for the help guys.