Re: Problem when upgrading to MySql 5

Steve Grosz wrote:
> 
> Ok, when I try it from the command line, I get:
> 
> C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql.exe -uroot -ppass Sereni
> ty < Serenity.sql
> ERROR 1064 (42000) at line 13: You have an error in your SQL syntax; 
> check the m
> anual that corresponds to your MySQL server version for the right syntax 
> to use
> near '"test" (
>  "OrderID" smallint(5) unsigned NOT NULL default '0',
>  "ModelID" smal' at line 1

Looks like the original dump was created with SQL_MODE 
(http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html) set to 
ANSI_QUOTES, and the current install is not.  The ANSI_QUOTES mode 
allows you to use double-quote as an identifier quote character (in 
addition to the backtick character).

Here's an example that shows how setting ANSI_QUOTES allows the CREATE 
TABLE command, while otherwise it generates the error that you observed:

mysql> set @@sql_mode = 'ANSI_QUOTES';
Query OK, 0 rows affected (0.00 sec)

mysql> create table "test" (id int);
Query OK, 0 rows affected (0.09 sec)

mysql> set @@sql_mode = 
'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

mysql> drop table test;
Query OK, 0 rows affected (0.11 sec)

mysql> create table "test" (id int);
ERROR 1064 (42000): You have an error in your SQL syntax; check the 
manual that corresponds to your MySQL server version for the right synta
x to use near '"test" (id int)' at line 1
mysql>

You could put the following line at the top of your backup file and give 
it another try:

set @@sql_mode = 'ANSI_QUOTES';

Best regards,

-- 
Todd Farmer, Support Engineer, Americas
MySQL Inc., www.mysql.com

Discover new MySQL Monitoring & Advisory features at:
http://www.mysql.com/products/enterprise/whats_new.html

-- 
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:    http://lists.mysql.com/[email protected]