0

I'm trying to create a MySQL database and tables by sending commands to mysql(1) through a text file. The file is simple:

$ cat test.sql
SET @DB_DATABASE_NAME = "test_db";
SET @DB_TABLE_NUMBERS = "name_number";

DROP DATABASE IF EXISTS @DB_DATABASE_NAME;
CREATE DATABASE IF NOT EXISTS @DB_DATABASE_NAME;

USE @DB_DATABASE_NAME;

CREATE TABLE IF NOT EXISTS @DB_TABLE_NUMBERS
(
        `nn_id` INT PRIMARY KEY AUTO_INCREMENT,
        `nn_name` VARCHAR(64) CHARACTER SET utf8,
        `nn_number` VARCHAR(18) CHARACTER SET utf8
);

And the command to invoke mysql is simple. The problem is, there's something wrong with it and mysql refuses to tell me what it is:

$ mysql -v -uroot -pMMGhbfb8sFYYbJCh < test.sql
--------------
SET @DB_DATABASE_NAME = "test_db"
--------------

--------------
SET @DB_TABLE_NUMBERS = "name_number"
--------------

--------------
DROP DATABASE IF EXISTS @DB_DATABASE_NAME
--------------

ERROR 1064 (42000) at line 4: You have an error in your SQL syntax; check the ma
nual that corresponds to your MariaDB server version for the right syntax to us
e near '@DB_DATABASE_NAME' at line 1

Searching for ERROR 1064 (42000) is producing irrelevant hits. And reading the mysql(1) man page is equally useless. I still have not figured out how to get the tool to print a useful error message, let alone help me diagnose the problem.

I have two questions.

  1. What section of the manual do I need to look in for the error message?
  2. How can I get this tool to provide useful error messages?
1

it looks like you cannot use database name as a varible according to stackoverflow, sqlservercentral.com (the same example).

there is already example how to solve this issue

  • I understand I'm not answering your questions. But I'm not sure if this limitation is even written anywhere in the man pages or documentation. The only clue in the error message is line number of original sql script which points you to DROP DATABASE statement. – Jakub Jindra Feb 19 at 20:23
  • It's not so bad after all. stackoverflow.com/a/37295007/4064310 SET PREPARE EXECUTE But you're right, it's not so handy and it can become more complex if you want to iterate over list of databases etc… – Jakub Jindra Feb 19 at 20:39
  • 1
    Now open in the MySQL Issue Tracker: Issue 94398, mysql provides useless error messages. Oracle should be ashamed of themselves. – user56041 Feb 19 at 21:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy