0

Using UBUNTU 16.

MySQL query fails from bash, but works from MySQL client:

    query=$(cat << EOF
CREATE DATABASE \`${mysql_local_database}\` /*!40100 COLLATE \'utf8_hungarian_ci\' */; use \`${mysql_local_database}\`; GRANT SELECT, EXECUTE, SHOW VIEW, ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, INDEX, INSERT, REFERENCES, UPDATE, LOCK TABLES  ON \`${mysql_local_database}\`.* TO 'myuser'@'%'; FLUSH PRIVILEGES;
EOF
)
echo $query
mysql -u ${mysql_local_user} -p${mysql_local_pass} -h localhost "${query}"

I get the following error after executing the bash script:

    CREATE DATABASE `mydb` /*!40100 COLLATE 'utf8_hungarian_ci' */; use `mydb`; GRANT SELECT, EXECUTE, SHOW VIEW, ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, INDEX, INSERT, REFERENCES, UPDATE, LOCK TABLES ON `mydb`.* TO 'user'@'%'; FLUSH PRIVILEGES;
ERROR 1102 (42000): Incorrect database name 'CREATE DATABASE `mydb` /*!40100 COLLATE 'utf8_hungarian_ci' */'

If I execute the printed MySQL query in MySQL client (HeidiSQL), it works fine:

CREATE DATABASE `mydb` /*!40100 COLLATE 'utf8_hungarian_ci' */; use `mydb`; GRANT SELECT, EXECUTE, SHOW VIEW, ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, INDEX, INSERT, REFERENCES, UPDATE, LOCK TABLES ON `mydb`.* TO 'myuser'@'%'; FLUSH PRIVILEGES;

/* Affected rows: 1  Found rows: 0  Warnings: 0  Duration for 4 queries: 0,000 sec. */

Please advise how to fix it.

SOLUTION

As @yoonix pointed, yes, the -e option was missing.

mysql -u ${mysql_local_user} -p${mysql_local_pass} -h localhost - e "${query}"

Now works well. Thanks for the help!

  • You're missing the flag '-e' telling it to execute your query and it's interpreting your input as the database name exactly like the syntax shows in the documentation. – yoonix Mar 30 '17 at 20:37
  • Do $query | mysql ... instead? – Zoredache Mar 30 '17 at 21:13
  • @yoonix yes, the -e option was missing. Please send as answer, so I can set your answer as solution. – klor Mar 30 '17 at 21:20
  • @Zoredache I like the other solution better, because it keeps the normal order of mysql command line. – klor Mar 30 '17 at 21:28
  • I guess, IMO the 'normal', or more common usage of the mysql command line IS to feed it queries via stdin, but use whatever works for you. – Zoredache Mar 30 '17 at 22:30
1

I'm not 100% sure about the collate utf8_hungarian_ci part but I was able to get it to create the database without errors by omitting backslash and ticks, changing the create database line to:

CREATE DATABASE $mysql_local_database  /*!40100 COLLATE utf8_hungarian_ci */ ; use $mysql_local_database ; GRANT SELECT, EXECUTE, SHOW VIEW, ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, INDEX, INSERT, REFERENCES, UPDATE, LOCK TABLES ON $mysql_local_database.* TO 'myuser'@'%'; FLUSH PRIVILEGES;

and also added -se here:

mysql -u ${mysql_local_user} -p${mysql_local_pass} -h localhost -se "${query}"
  • Database names needs to be backtick quoted, because db names are using non-conventional characters, like dot. Permitted characters in quoted identifiers: dev.mysql.com/doc/refman/5.7/en/identifiers.html Also I modified the 'mydb'@'%' to 'myuser'@'%' as there should be username (I mistyped it in my original post). – klor Mar 31 '17 at 12:48
  • Changed my answer back to the way I had it as the command worked for me without backslashes or ticks. If it works better for you with them, follow your bliss. :-) – bootbeast Apr 3 '17 at 20:19

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.