0

I'm trying to script the creation of a database with tables. I'm encountering errors but the error messages are not helpful to me because I'm a novice.

I'm finding the mysql tool is providing mostly useless error messages. It is telling me there's an error but it is not telling me what the error is. mysql -v just echos the command, and does not detail the actual error.

My question is, how do I get useful error information from mysql command?

Here is an example of the script:

# Whitelist table mysql -v -u"${MARIADB_USER}" -p"${MARIADB_PASS}" -e ' USE '"${MY_DB}"'; CREATE TABLE IF NOT EXISTS '"${MY_WHITE_TABLE}"' ("key" INT PRIMARY KEY NOT NULL AUTO_INCREMENT, "number" VARCHAR(20) CHARACTER SET utf8); '

Here is an example of the error message:

-------------- CREATE TABLE IF NOT EXISTS whitelist ("key" INT PRIMARY KEY NOT NULL AUTO_INCREMENT, "number" VARCHAR(20) CHARACTER SET utf8) -------------- ERROR 1064 (42000) at line 3: You have an error in your SQL syntax; check the ma nual that corresponds to your MariaDB server version for the right syntax to use near '"key" INT PRIMARY KEY NOT NULL AUTO_INCREMENT, "number" VARCHAR(20) CHARA CTER SE' at line 2

I've tried leaving the column name unquoted, single quoting the column name, and double quoting the column name. At this point I believe the problem lies elsewhere, but I don't know where.

Improve this question

1 Answer 1

Reset to default
1

Quoting of column names and table names (and some other things) is with backtics:

(`key` INT ...

You can change that with a sql_mode, but MySQL users rarely do that.

In the long run, I think you will abandon using shell scripts to run MySQL commands.

Improve this answer
8
  • Thanks. How can I get a useful error message from the tool? That appears to be my biggest problem. (Experienced users probably don't suffer the problem). Commented Feb 17, 2019 at 21:09
  • When using a language (Perl, Java, PHP, VB, etc), you get the resultsets and errors and can act on them. Commented Feb 17, 2019 at 21:53
  • Thanks again. @Rick. I tried to switch to a *.sql file. Unfortunately I can't get the damn thing to work. Even something as simple as using a variable is broken. How do you database guys work with these crappy tools? Commented Feb 19, 2019 at 18:12
  • @jww - More details, please. As for "crappy tools" -- they know to pick on you. Commented Feb 19, 2019 at 18:25
  • I'm am absolutely baffled one cannot get useful error messages from this tool. It is absolute junk. I would be embarrassed to publish a tool as broken as this (and I am a C/C++/ObjC programmer). Commented Feb 19, 2019 at 18:54