"CHARACTER SET COLLATE NULL" error with mySQL 4.0.27

thomas Armstrong · *nix forums beginner Joined: 16 Sep 2005 Posts: 15

Hola.

With mySQL 4.0.27 I'm trying to create this table
------
CREATE TABLE `test`.`user` (

`user_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`email` TEXT CHARACTER SET COLLATE NULL ,
`firstname` TEXT CHARACTER SET COLLATE NOT NULL ,
)
----------

but I get this error:
------
#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 'CHARACTER SET COLLATE NULL,
`firstname` TEXT CHARACTER SET
------

Does anybody know which the right way is for this mySQL version? I'm using
mySQL-Workbench to design the tables, and that's the code I get?

Thank you very much.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

Visolve DB Team · *nix forums beginner Joined: 21 Jul 2006 Posts: 3

Hello Thomas

You have an error in your SQL syntax.

Please try this :

CREATE TABLE test.user
( user_id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY(user_id),
email text CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci',
firstname text CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci');

The error is due to the AUTO_INCREMENT column, which is not defined as
PRIMARY KEY and the CHARACTER SET column, which is not syntatically
defined.

Also to list the MySQL support Character Set try:
Show Character Set;

Thanks
ViSolve MySQL Support Team.

----- Original Message -----
From: "thomas Armstrong" <tarmstrong@gmail.com>
To: <mysql@lists.mysql.com>
Sent: Thursday, July 20, 2006 2:41 PM
Subject: "CHARACTER SET COLLATE NULL" error with mySQL 4.0.27

Google