MySQL schema name with dash does not allow me to execute command line query

Question

I have create a shell script to automate my database migration from host to host, that extracts my Multi WordPress site tables into separated sql files, so I can decide what site I will migrate each time.

So in my shell script I have the following code:

schema="internet-safe"
PROJECT_FOLDER_NAME="internet-safe"
vagrant_export_folder="/var/www/projects/${PROJECT_FOLDER_NAME}/database/tmp"

query="mysql -uroot -proot -e \"SELECT blog_id AS ID, path AS Slug FROM ${schema}.wp_blogs "
query="$query INTO OUTFILE '$vagrant_export_folder/blogs.csv' "
query="$query FIELDS TERMINATED BY ',' "
query="$query ENCLOSED BY '\\\"' "
query="$query LINES TERMINATED BY '\n' \" > /dev/null"

vagrant ssh --command "cd $vagrant_export_folder && $query"

But by executing this script I get the following error:

ERROR 1064 (42000) 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 '-safe.wp_blogs  INTO OUTFILE '/var/www/projects/internet-safe/database/tmp/blogs' at line 1

Connection to 127.0.0.1 closed.

An the problem is the dash in the schema name internet-safe. Is there a way to correct this issue ?

I know that there is an option, to just rename the schema, but, unfortunately I run on unstable vagrant, on xUbuntu, that has destroy my database several times, while I try to provision modifications in my databases.

I also have try the following combinations but I get error messages like the following:

`${schema}`.`wp_blogs`


ERROR 1064 (42000) 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 'INTO OUTFILE '/var/www/projects/internet-safe/database/tmp/blogs.csv'  FIELDS TE' at line 1


`${schema}`.wp_blogs


ERROR 1046 (3D000) at line 1: No database selected



`${schema}.wp_blogs`


ERROR 1064 (42000) 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 'INTO OUTFILE '/var/www/projects/internet-safe/database/tmp/blogs.csv'  FIELDS TE' at line 1

@AbhikChakraborty Not possible in shell script. See my answer. — fancyPants
– fancyPants, Commented Mar 26, 2015 at 10:09
escaping the backticks worked for me: --execute="DROP DATABASE `router-xxxxxx`" — staggart
– staggart, Commented Mar 21, 2017 at 17:46

fancyPants · Accepted Answer · 2015-03-26 10:09:03Z

Best option would be of course to simply rename your database.

Usually when special characters appear in schema/table/column names, you can fix this with backticks. Since you're trying to run a shell script, it will interpret anything within backticks as a command, unfortunately.

In MySQL you can set the sql_mode "ANSI_QUOTES". Do so as administrator/root (in MySQL) with

mysql> SET GLOBAL sql_mode="ANSI_QUOTES";

Check first, if you have any modes set already with

mysql> SHOW VARIABLES LIKE 'sql_mode';

If there are already modes set, include those in the SET GLOBAL sql_mode statement (separated by ,).

You can also set this option in your my.cnf file, so that this mode is applied every time you start your MySQL server.

The effect is, that " is also allowed as identifier quote character. But be careful, that you don't use " in queries to specify any text, use single-quotes there instead.

read more about it here

Collectives™ on Stack Overflow

MySQL schema name with dash does not allow me to execute command line query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related