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.

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.

  • 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). – user141074 Feb 17 at 21:09
  • When using a language (Perl, Java, PHP, VB, etc), you get the resultsets and errors and can act on them. – Rick James Feb 17 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? – user141074 Feb 19 at 18:12
  • @jww - More details, please. As for "crappy tools" -- they know to pick on you. – Rick James Feb 19 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). – user141074 Feb 19 at 18:54

Your Answer

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