Export/import of column names with space

[Bart Van der Donck]

Hello,

I am using MySQL 4.0.
Say that I have a table named "mytable" having a column "ID" and a
column "test columnname".

When dumping:

mysqldump --opt DATABASE -uUSER -hHOST -pPASS > myfile.txt

it gives someting like:

CREATE TABLE mytable(ID int(11) NOT NULL, test columnname text NOT
NULL);

Then I use the mysql command to import myfile.txt in another database:

mysql DATABASE -uUSER -hHOST -pPASS < myfile.txt

but it fails with:

"ERROR 1064 at line 1: 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 'test columnname text NOT NULL)' at line 1"

Obviously, the mysql command can't handle column names with spaces. Is
this a bug? I would say so - mysqldump should put the column names
between quotes or so (alternatively, mysql shouldn't allow spaces in
column names).

My problem is that I have to work with these columns as they are.
They're also referred to from a lot of queries in the API's. Any
possible workarounds?

Thanks
Bart

[Chris Hope]

Bart Van der Donck wrote:

I am using MySQL 4.0.
Say that I have a table named "mytable" having a column "ID" and a
column "test columnname".

When dumping:

mysqldump --opt DATABASE -uUSER -hHOST -pPASS > myfile.txt

it gives someting like:

CREATE TABLE mytable(ID int(11) NOT NULL, test columnname text NOT
NULL);

Then I use the mysql command to import myfile.txt in another database:

mysql DATABASE -uUSER -hHOST -pPASS < myfile.txt

but it fails with:

"ERROR 1064 at line 1: 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 'test columnname text NOT NULL)' at line 1"

Obviously, the mysql command can't handle column names with spaces. Is
this a bug? I would say so - mysqldump should put the column names
between quotes or so (alternatively, mysql shouldn't allow spaces in
column names).

My problem is that I have to work with these columns as they are.
They're also referred to from a lot of queries in the API's. Any
possible workarounds?

Dump the data with the "-Q" / "--quote-names" flag. This puts backticks
around all table and column names and prevents this problem from happening.

eg: mysqldump --opt DATABASE -Q -uUSER -hHOST -pPASS > myfile.txt

--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/

[Bart Van der Donck]

Chris Hope wrote:

Dump the data with the "-Q" / "--quote-names" flag. This puts backticks
around all table and column names and prevents this problem from happening.

eg: mysqldump --opt DATABASE -Q -uUSER -hHOST -pPASS > myfile.txt

Thanks Chris, that's what I was looking for.
However, illogical mysql behaviour I think...
When one can create column names with spaces by default, mysqldump
should also use backticks by default. (Or the opposite, not allowing
spaces and then not using backticks in dump)

Anyway, it works fine now.

Bart