mysqldump -Q issue in 4.1.0

I'm using the following:

mysql Ver 13.5 Distrib 4.1.0-alpha, for pc-linux (i686)
mysqldump Ver 10.2 Distrib 4.1.0-alpha, for pc-linux (i686)

mysqldump -CceQ -O net_buffer_length=32MB -h -u
-p mystic_backend > mystic_backend.sql


This gives me a nice .sql file with quotes around all the field name and
such. However when I try and import the file with the following command
(to the local machine):
mysql -u root -p < mystic_backend.sql

I get the following error:
ERROR 1064 at line 13: 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 '"acl_func_dbfield" (
"field_id" smallint(3) unsigned zerofill

If I dump the sql file without the -Q option, I can import to the point
where I would need the quotes around the fieldnames. Below is the first
line from the .sql file:

DROP TABLE IF EXISTS `acl_func_dbfield`;
CREATE TABLE "acl_func_dbfield" (
"field_id" smallint(3) unsigned zerofill NOT NULL default '000',
"function_id" mediumint(4) unsigned zerofill NOT NULL default '0000',
"module_id" tinyint(2) unsigned zerofill NOT NULL default '00',
"table_id" tinyint(2) unsigned zerofill NOT NULL default '00',
"table_type" enum('DISTRICT','SCHOOL','BACKEND') NOT NULL default
'DISTRICT',
"acl_type" enum('REQ_RW','REQ_RO','EXTEND') NOT NULL default 'REQ_RW',
PRIMARY KEY
("field_id","function_id","module_id","table_id","table_type ")
) TYPE=InnoDB CHARSET=latin1;


Thanks,
-Aaron


---------------
Aaron Blew
Jackson County School District #6 Network/Systems Analyst
aaron.blew@district6.org
(541)494-6900

You can destroy your now by worrying about tomorrow.
-- Janis Joplin


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: mysqldump -Q issue in 4.1.0

At 11:29 -0700 7/15/03, Aaron Blew wrote:
>I'm using the following:
>
>mysql Ver 13.5 Distrib 4.1.0-alpha, for pc-linux (i686)
>mysqldump Ver 10.2 Distrib 4.1.0-alpha, for pc-linux (i686)
>
> mysqldump -CceQ -O net_buffer_length=32MB -h -u
>-p mystic_backend > mystic_backend.sql
>
>
>This gives me a nice .sql file with quotes around all the field name and
>such. However when I try and import the file with the following command
>(to the local machine):
>mysql -u root -p < mystic_backend.sql
>
>I get the following error:
>ERROR 1064 at line 13: 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 '"acl_func_dbfield" (
> "field_id" smallint(3) unsigned zerofill

Is the server you're dumping from different than the server you're
importing into? It looks like the server you're dumping from is running
in ANSI mode (which is why it's quoting with " characters), and the
server you're importing into is *not* running in ANSI mode (which requires
quoting with ` characters).

>If I dump the sql file without the -Q option, I can import to the point
>where I would need the quotes around the fieldnames. Below is the first
>line from the .sql file:
>
>DROP TABLE IF EXISTS `acl_func_dbfield`;
>CREATE TABLE "acl_func_dbfield" (
> "field_id" smallint(3) unsigned zerofill NOT NULL default '000',
> "function_id" mediumint(4) unsigned zerofill NOT NULL default '0000',
> "module_id" tinyint(2) unsigned zerofill NOT NULL default '00',
> "table_id" tinyint(2) unsigned zerofill NOT NULL default '00',
> "table_type" enum('DISTRICT','SCHOOL','BACKEND') NOT NULL default
>'DISTRICT',
> "acl_type" enum('REQ_RW','REQ_RO','EXTEND') NOT NULL default 'REQ_RW',
> PRIMARY KEY
>("field_id","function_id","module_id","table_id","table_typ e")
>) TYPE=InnoDB CHARSET=latin1;
>
>
>Thanks,
>-Aaron
>
>
>---------------
>Aaron Blew
>Jackson County School District #6 Network/Systems Analyst
>aaron.blew@district6.org
>(541)494-6900
>
>You can destroy your now by worrying about tomorrow.
> -- Janis Joplin


--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com

Are you MySQL certified? http://www.mysql.com/certification/


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: mysqldump -Q issue in 4.1.0

Hi!

On Jul 15, Aaron Blew wrote:
> I'm using the following:
>
> mysql Ver 13.5 Distrib 4.1.0-alpha, for pc-linux (i686)
> mysqldump Ver 10.2 Distrib 4.1.0-alpha, for pc-linux (i686)
>
> mysqldump -CceQ -O net_buffer_length=32MB -h -u
> -p mystic_backend > mystic_backend.sql
>
> This gives me a nice .sql file with quotes around all the field name and
> such. However when I try and import the file with the following command
> (to the local machine):
> mysql -u root -p < mystic_backend.sql
>
> I get the following error:
> ERROR 1064 at line 13: 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 '"acl_func_dbfield" (
> "field_id" smallint(3) unsigned zerofill

It's because remote mysqld runs in --ansi mode and used double quote to
quote names. Your local mysqld runs in default mode and used backticks
to quote names. Run your local mysqld with --ansi switch to import this
dump.

If you're using MySQL 4.1 you can prepend the dump with

SET sql_mode="ansi";

for the same effect.

Regards,
Sergei

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/ www.mysql.com

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: mysqldump -Q issue in 4.1.0

Just a note to indicate the closure on this problem.

Serg and I both indicated that it was like a problem of one server
running in ANSI mode and the other not. I did hear back from Aaron,
who said that he's got things working now.

At 11:29 -0700 7/15/03, Aaron Blew wrote:
>I'm using the following:
>
>mysql Ver 13.5 Distrib 4.1.0-alpha, for pc-linux (i686)
>mysqldump Ver 10.2 Distrib 4.1.0-alpha, for pc-linux (i686)
>
> mysqldump -CceQ -O net_buffer_length=32MB -h -u
>-p mystic_backend > mystic_backend.sql
>
>
>This gives me a nice .sql file with quotes around all the field name and
>such. However when I try and import the file with the following command
>(to the local machine):
>mysql -u root -p < mystic_backend.sql
>
>I get the following error:
>ERROR 1064 at line 13: 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 '"acl_func_dbfield" (
> "field_id" smallint(3) unsigned zerofill
>
>If I dump the sql file without the -Q option, I can import to the point
>where I would need the quotes around the fieldnames. Below is the first
>line from the .sql file:
>
>DROP TABLE IF EXISTS `acl_func_dbfield`;
>CREATE TABLE "acl_func_dbfield" (
> "field_id" smallint(3) unsigned zerofill NOT NULL default '000',
> "function_id" mediumint(4) unsigned zerofill NOT NULL default '0000',
> "module_id" tinyint(2) unsigned zerofill NOT NULL default '00',
> "table_id" tinyint(2) unsigned zerofill NOT NULL default '00',
> "table_type" enum('DISTRICT','SCHOOL','BACKEND') NOT NULL default
>'DISTRICT',
> "acl_type" enum('REQ_RW','REQ_RO','EXTEND') NOT NULL default 'REQ_RW',
> PRIMARY KEY
>("field_id","function_id","module_id","table_id","table_typ e")
>) TYPE=InnoDB CHARSET=latin1;
>
>
>Thanks,
>-Aaron
>
>
>---------------
>Aaron Blew
>Jackson County School District #6 Network/Systems Analyst
>aaron.blew@district6.org
>(541)494-6900
>
>You can destroy your now by worrying about tomorrow.
> -- Janis Joplin
>
>
>--
>MySQL Bugs Mailing List
>For list archives: http://lists.mysql.com/bugs
>To unsubscribe: http://lists.mysql.com/bugs?unsub=paul@mysql.com


--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com

Are you MySQL certified? http://www.mysql.com/certification/


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org