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>

Do you need help?

In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software: Learn more about Pantek's Technical Support offerings Place an order for 24/7/365 Technical Support here Learn more about Pantek, who we are, and what we do Chat with a Live Operator Related lists.mysql.com Links announce benchmarks bugs certification cluster commits dotnet gui-tools internals java maxdb myodbc mysql mysqldoc packagers perl plusplus replication win32 Request more information about Pantek

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/win32?unsub=lists@pantek.com