backtick is not working to run mysql queries in shell script

Question

Hi I am trying to run MySQL queries from shell script.

mysql -u root -p'1234' -e "CREATE TABLE $DB.aa_vv_cc
(
id int(10) unsigned NOT NULL AUTO_INCREMENT,
city varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
lat varchar(255) DEFAULT NULL,
`long` varchar(255) DEFAULT NULL,
 status int(11) NOT NULL DEFAULT '1',
 created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY ('id')
);"

I am getting error on "long".The back tick is not working from shell script.Any help will be appreciated.

As Long is a reserved word in mysql you should not use it as column name. — Jens
– Jens, Commented Sep 8, 2016 at 11:07
Don't know why the hell it's downvoted. Hey arun, did you find the solution? — learner
– learner, Commented Oct 4, 2019 at 12:00

Etan Reisner · Accepted Answer · 2016-09-08 11:07:10Z

4

Backticks are Command Substitution in the shell.

And they are evaluated in double quoted strings.

So the shell is seeing the

`long`

in your string and trying to run the command long and, presumably, failing.

You need to escape the backticks

\`long\`

in the double quoted string to prevent that (or use a single quoted string which doesn't evaluate them)

'... `long` ...'

answered Sep 8, 2016 at 11:07

Etan Reisner

81.7k8 gold badges120 silver badges154 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

learner Over a year ago

This didn't work in my case. I had to use this with Database name like CREATE TABLE reporting.details AS SELECT * FROM \`Temp-17-09-19\`.details;. But it gave me error com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'Temp-17-09-19.details' doesn't exist. So it's considering whole db.tablename as a tablename.

ajay panchal · Accepted Answer · 2016-09-08 11:54:30Z

0

You have to try this here i have modify two things 'long' replace by \'long\' and PRIMARY KEY ('id') replace by PRIMARY KEY (id) this query is work in my shell script.Here the test is the name of database.

mysql -u root  -p'1234' -e "USE test;CREATE TABLE $DB.aa_vv_cc (id int(10) unsigned NOT NULL AUTO_INCREMENT,city varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,lat varchar(255) DEFAULT NULL,\`long\`varchar(255) DEFAULT NULL, status int(11) NOT NULL DEFAULT '1', created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at timestamp NOT NULL DEFAULT '0000-00-0000:00:00',PRIMARY KEY (id));"

answered Sep 8, 2016 at 11:54

ajay panchal

1451 gold badge1 silver badge12 bronze badges

Collectives™ on Stack Overflow

backtick is not working to run mysql queries in shell script

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related