Problem when upgrading to MySql 5

Ok, I had several databases on v4.1.17 that were working fine. I wanted
to upgrade to v5 to work with stored procedures and triggers a little.

I removed the instance of 4.1.17, and installed v5.

I backed up my tables using the MySql administrator. When I tried to
import the back up, it failed.

I tried recreating one of the smaller tables as shown below:


CREATE TABLE `class_review` (
`Rev_ID` bigint(20) NOT NULL auto_increment,
`Rev_Class_Date` date NOT NULL default '0000-00-00',
`Rev_Class_Instructor` varchar(10) NOT NULL default '',
`Rev_Class_Knowledge` int(10) unsigned NOT NULL default '0',
`Rev_Part` int(10) unsigned NOT NULL default '0',
`Rev_Friend` int(10) unsigned NOT NULL default '0',
`Rev_Prep` int(10) NOT NULL default '0',
`Rev_Expect` int(10) unsigned NOT NULL default '0',
`Rev_Cont` int(10) unsigned NOT NULL default '0',
`Rev_Enjoy` int(10) unsigned NOT NULL default '0',
`Rev_Comments` text NOT NULL,
PRIMARY KEY (`Rev_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I have the following Coldfusion code that was inserting the data:

insert into class_review
(Rev_Class_Date, Rev_Class_Instructor, Rev_Knowledge, Rev_Part,
Rev_Friend, Rev_Prep, Rev_Expect, Rev_Cont, Rev_Enjoy, Rev_Comments)
values
(
'#form.ClassDate#',
'#form.Instructor#',
'#form.Knowledge#',
'#form.Part#',
'#form.FR#',
'#form.Prep#',
'#form.Expect#',
'#form.Continue#',
'#form.OA#',
'#form.Comments#');


But now I'm getting a message that says:

Error Executing Database Query.
Unknown column 'Rev_Knowledge' in 'field list'

The error occurred in C:\Inetpub\wwwroot\SalleBoise\CF\add_review.cfm:
line 17

15 : '#form.Continue#',
16 : '#form.OA#',
17 : '#form.Comments#');
18 :
19 :



------------------------------------------------------------ --------------------

SQL insert into class_review (Rev_Class_Date, Rev_Class_Instructor,
Rev_Knowledge, Rev_Part, Rev_Friend, Rev_Prep, Rev_Expect, Rev_Cont,
Rev_Enjoy, Rev_Comments) values ( '2006-10-23', 'Steve Grosz', '5', '5',
'5', '5', '4', '4', '5', '

test

Re: Problem when upgrading to MySql 5

Hi Steve,

Steve Grosz wrote:
> Ok, I had several databases on v4.1.17 that were working fine. I wanted
> to upgrade to v5 to work with stored procedures and triggers a little.
>
> I removed the instance of 4.1.17, and installed v5.
>
> I backed up my tables using the MySql administrator. When I tried to
> import the back up, it failed.

It would be interesting to see the error message you receive during the
import. You might also try it directly from the command-line:

C:\Program Files\MySQL\MySQL Server 5.0 > bin\mysql.exe -uroot -ppass
db_name < backup.sql

> I tried recreating one of the smaller tables as shown below:
>
>
> CREATE TABLE `class_review` (
....
> `Rev_Class_Knowledge` int(10) unsigned NOT NULL default '0',

>
> insert into class_review
> (Rev_Class_Date, Rev_Class_Instructor, Rev_Knowledge, Rev_Part,
> Rev_Friend, Rev_Prep, Rev_Expect, Rev_Cont, Rev_Enjoy, Rev_Comments)
> values
....
> Error Executing Database Query.
> Unknown column 'Rev_Knowledge' in 'field list'

Are you sure that the manually created table is identical to the 4.1
instance? It seems as though you are referring to a column
(Rev_Knowledge) in your INSERT statement that does not exist in the
table definition (although there is one named Rev_Class_Knowledge).


--
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=gcdmw-win32@m.gmane.org

Re: Problem when upgrading to MySql 5

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

I have filled in the passworrd for root, by the way..... :)

Steve

C:\Program Files\MySQL\MySQL Server 5.0\bin>
"Todd Farmer" wrote in message
news:46CBAE97.7090909@mysql.com...
> Hi Steve,
>
> Steve Grosz wrote:
> > Ok, I had several databases on v4.1.17 that were working fine. I wanted
> > to upgrade to v5 to work with stored procedures and triggers a little.
> >
> > I removed the instance of 4.1.17, and installed v5.
> >
> > I backed up my tables using the MySql administrator. When I tried to
> > import the back up, it failed.
>
> It would be interesting to see the error message you receive during the
> import. You might also try it directly from the command-line:
>
> C:\Program Files\MySQL\MySQL Server 5.0 > bin\mysql.exe -uroot -ppass
> db_name < backup.sql
>
> > I tried recreating one of the smaller tables as shown below:
> >
> >
> > CREATE TABLE `class_review` (
> ...
> > `Rev_Class_Knowledge` int(10) unsigned NOT NULL default '0',
>
> >
> > insert into class_review
> > (Rev_Class_Date, Rev_Class_Instructor, Rev_Knowledge, Rev_Part,
> > Rev_Friend, Rev_Prep, Rev_Expect, Rev_Cont, Rev_Enjoy, Rev_Comments)
> > values
> ...
> > Error Executing Database Query.
> > Unknown column 'Rev_Knowledge' in 'field list'
>
> Are you sure that the manually created table is identical to the 4.1
> instance? It seems as though you are referring to a column
> (Rev_Knowledge) in your INSERT statement that does not exist in the table
> definition (although there is one named Rev_Class_Knowledge).
>
>
> --
> 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=gcdmw-win32@m.gmane.org

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_SUBSTITUT ION';
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/win32?unsub=gcdmw-win32@m.gmane.org

Re: Problem when upgrading to MySql 5

I get the same result, just with the error being on line 14 instead of 13
since I added the line at the top


"Todd Farmer" wrote in message
news:46CCBF7F.9010307@mysql.com...
> 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_SUBSTITUT ION';
> 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/win32?unsub=gcdmw-win32@m.gmane.org

Re: Problem when upgrading to MySql 5

Steve Grosz wrote:
> I get the same result, just with the error being on line 14 instead of
> 13 since I added the line at the top

Can you post the CREATE TABLE command that starts at line 13/14 of the
backup (no data, just the CREATE TABLE command in its entirety)? Even
better, everything up through the end of the CREATE TABLE command (you
can leave INSERT statements out, but I doubt there are any, as it
appears to fail at the first DDL operation).


--
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=gcdmw-win32@m.gmane.org

Re: Problem when upgrading to MySql 5

Ok, here ya go. This is just 1 of the databases I'm fighting with.... :)

SET @@sql_mode = 'ANSI_QUOTES';
SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
SET NAMES utf8;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,
FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=NO_AUTO_VALUE_ON_ZERO */;


CREATE DATABASE /*!32312 IF NOT EXISTS*/ `serenity`;
USE `serenity`;
CREATE TABLE "test" (
"OrderID" smallint(5) unsigned NOT NULL default '0',
"ModelID" smallint(5) unsigned NOT NULL default '0',
"test" enum('Y','N') NOT NULL default 'Y',
"ModelDesc" varchar(40) default NULL,
PRIMARY KEY ("OrderID"),
KEY "ModelID" ("ModelID")
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE "mailings" (
"Cust_ID" int(6) NOT NULL auto_increment,
"Cust_Name" varchar(50) NOT NULL default '',
"Cust_Address" varchar(50) NOT NULL default '',
"Cust_City" varchar(15) NOT NULL default '',
"Cust_State" char(2) NOT NULL default '',
"Cust_Email" varchar(35) default NULL,
"Cust_EmailNews" varchar(7) NOT NULL default '',
"Cust_EmailSpecials" varchar(7) NOT NULL default '',
"Cust_Email_Type" tinyint(1) NOT NULL default '0',
"Cust_DateAdded" date NOT NULL default '0000-00-00',
"Cust_DateModified" date default '0000-00-00',
"Cust_UUID" varchar(50) NOT NULL default '',
"Cust_Verified" varchar(5) NOT NULL default 'No',
"Cust_Zip" varchar(10) NOT NULL default '',
"ResendCount" int(1) unsigned zerofill NOT NULL default '0',
"Cust_UserName" varchar(11) NOT NULL default '',
"Cust_Pass" blob NOT NULL,
PRIMARY KEY ("Cust_ID")
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

after all the inserts, we have:

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

Steve

"Todd Farmer" wrote in message
news:46CCCA77.1000304@mysql.com...
> Steve Grosz wrote:
>> I get the same result, just with the error being on line 14 instead of 13
>> since I added the line at the top
>
> Can you post the CREATE TABLE command that starts at line 13/14 of the
> backup (no data, just the CREATE TABLE command in its entirety)? Even
> better, everything up through the end of the CREATE TABLE command (you can
> leave INSERT statements out, but I doubt there are any, as it appears to
> fail at the first DDL operation).
>
>
> --
> 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=gcdmw-win32@m.gmane.org