469,148 Members | 1,275 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,148 developers. It's quick & easy.

Export/import of column names with space

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
Jul 20 '05 #1
2 2162
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/
Jul 20 '05 #2
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
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by R.A. Joemman | last post: by
2 posts views Thread by Bart Van der Donck | last post: by
205 posts views Thread by Jeremy Siek | last post: by
1 post views Thread by Janne Ruuttunen | last post: by
1 post views Thread by CARIGAR | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.