Bug #18508 Problem with inserting records with apostrophe's...
Submitted: 25 Mar 2006 13:49 Modified: 27 Mar 2006 16:42
Reporter: Hwa Siang KOK Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Query Browser Severity:S3 (Non-critical)
Version: OS:Windows (Windows XP)
Assigned to: CPU Architecture:Any

[25 Mar 2006 13:49] Hwa Siang KOK
Description:
I have the following SQL Script file that I am opening in the Query Browser to execute:

1. A table that holds information for some movie directors:

CREATE TABLE director(
Fname varchar(20)	NOT NULL,
Lname varchar(20)	NOT NULL,
DOB date		NOT NULL,
Gender varchar(1),
Nationality varchar(20),
Pic varchar(72),
Primary key(Fname, Lname, DOB));

2. A table that holds information about movies:

CREATE TABLE movie(
Title varchar(72)	NOT NULL,
ReleaseDate date	NOT NULL,
Genre varchar(12),
Rating numeric,
Mrating varchar(5),
Duration numeric,
Studio varchar(72)	NOT NULL,
Pic varchar(72),
primary key(Title, ReleaseDate ,Studio));

3. And a table that holds information about the movies a director is directing:

CREATE TABLE directs(
DFname varchar(20)	NOT NULL,
DLname varchar(20)	NOT NULL,
DDOB date		NOT NULL,
MTitle varchar(72) NOT NULL,
MReleaseDate date NOT NULL,
MStudio varchar(72) NOT NULL,
Payment numeric		NOT NULL,
Primary key(DFname, DLname, DDOB, MTitle, MReleaseDate, MStudio),
Foreign key(DFname, DLname) REFERENCES director(Fname, Lname)
  ON UPDATE CASCADE ON DELETE CASCADE,
Foreign key(Mtitle,MReleaseDate,Mstudio) REFERENCES movie(Title, ReleaseDate, Studio)
  ON UPDATE CASCADE ON DELETE CASCADE);

When I try to insert the below two entries, Query Browser gives me the below error:

1. insert into directs(DFname, DLname, DDOB, MTitle, MReleaseDate, MStudio, Payment) values('Allen', 'Elizabeth', '1970-09-07', 'Madea\'s Family Reunion', '2006-11-11', 'Lions Gate Releasing', '1000000');

2. insert into directs(DFname, DLname, DDOB, MTitle, MReleaseDate, MStudio, Payment) values('Allen', 'Elizabeth', '1970-09-07', 'Big Momma\'s House 2', '2006-01-11', '20th Century Fox Distribution', '1000000');

"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 ';
insert into directs(DFname, DLname, DDOB, MTitle, MReleaseDate, MStudio, Paym' at line 1 ErrorNr. 1064

I'm using the latest version of Query Browser (v1.1.20), and MySQL Server v.5.0.19.

How to repeat:
It happens everytime I execute these two rows consecutively.

Suggested fix:
If I insert *either* of them one at a time, everything works perfectly fine.
[27 Mar 2006 16:42] Valeriy Kravchuk
Looks like a duplicate of bug #18044.