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!

klor
  • 344
  • 4
  • 8
  • 25
  • 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. –  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 Answers1

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}"
bootbeast
  • 34
  • 4
  • Database names needs to be backtick quoted, because db names are using non-conventional characters, like dot. Permitted characters in quoted identifiers: https://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 03 '17 at 20:19