Bug #94398 mysql provides useless error messages
Submitted: 19 Feb 2019 21:07 Modified: 19 Feb 2019 21:44
Reporter: Jeffrey Walton Email Updates:
Status: Unsupported Impact on me:
None 
Category:MySQL Server: Errors Severity:S3 (Non-critical)
Version:5.6 OS:Any
Assigned to: CPU Architecture:Any

[19 Feb 2019 21:07] Jeffrey Walton
Description:
I'm a SQL novice, but I have about 20 years of C/C++ experience. I also understand SQL language is standardized by ANSI and some other organizations.

The following SQL file:

<begin>

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
);

</end>

Results in:

<begin>

$ 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

</end>

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

After asking on Unix & Linux Stack Exchange I learned MySQL does not use variables as expected (https://unix.stackexchange.com/q/501694/56041). What genius thought that was a good idea...

Looking around the web for MySQL error messages has shown me that thousands of man hours are wasted because of the useless MySQL error messages. Telling someone to look in the SQL manual is absolutely useless. This is from experience after spending two days reading mysql(1) looking for clues.

Please provide helpful error messages.

How to repeat:
Read an error message produced by MySQL tool.

Suggested fix:

Please provide helpful error messages.
[19 Feb 2019 21:17] MySQL Verification Team
Thank you for the bug report. Server MariaDB isn't supported.
"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"
[19 Feb 2019 21:44] Jeffrey Walton
And now open downstream in MariaDB: mysql command line tool provides useless error messages (https://jira.mariadb.org/browse/MDEV-18652).

Why would you kick this bug report downstream when the problem is with the MySQL tool?

It is very disingenuous.