Re: Getting SQL errors porting databases between MySQL v4 and v5

[Micah Stevens]

group is a  reserved word, so MySQL thinks you're attempting a 'group 
by' statement. Put backticks around group, you should always quote your 
table and column names.

DROP TABLE IF EXISTS `admission_quotes`;
CREATE TABLE `admission_quotes` (
 `id` int(4) NOT NULL auto_increment,
 `quote` text,
 `author` text,
 `category` text,
 `class` text,
 `active` text,
 `group` varchar(5) default NULL,
 PRIMARY KEY  (id)
) TYPE=MyISAM;

-Micah
On 03/26/2007 10:31 AM, Rob Tanner wrote:
Hi,

I am porting over 6 databases from a MySQL v4 installation to a MySQL 
v5 installation and getting an SQL error in the process.

I am using the following command to dump the data in the v4 installation:

mysqldump  -u root --password=secret  --add-drop-table --databases db1 
db2 db3 db4 db5 db6  > db.sql

and using the following command to upload the databases on to the v5 
installation:

mysql -u root -p < db.sql

But then I get this error:

    ERROR 1064 (42000) at line 140784: 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 'group varchar(5) default
    NULL,
      PRIMARY KEY  (id)
    ) TYPE=MyISAM' at line 8



The whole create table sequence from the db.sql file is:

    DROP TABLE IF EXISTS admission_quotes;
    CREATE TABLE admission_quotes (
      id int(4) NOT NULL auto_increment,
      quote text,
      author text,
      category text,
      class text,
      active text,
      group varchar(5) default NULL,
      PRIMARY KEY  (id)
    ) TYPE=MyISAM;


Any idea what the issue is?  Is there something special I need to do 
when porting the databases between MySQL v4 and v5?

Thanks,
Rob


--
Rob Tanner
UNIX Services Manager
Linfield College, McMinnville OR