0

Why am I getting error when I try to create database with name '5e370227_db'?

I am getting the following error:

mysql> create database 5e370227_db;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '5e370227_db' at line 1

When I try to create a database with name '5a370227_db' (e -> a) it is created successfully:

mysql> create database 5a370227_db;
Query OK, 1 row affected (0.00 sec)

Details:

  • MYSQL: mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1
  • Linux Centos 2.6.32-358.el6.x86_64
  • logged as root
  • database admin
  • 1
    A SQL identifier is not allowed to start with a number. You must quote the name: "5e370227_db" or using MySQL's dreaded backticks: `5e370227_db` – a_horse_with_no_name Jan 30 '15 at 12:55
2

Interesting problem and I think that I've figured it out.

For some reason, MySQL interprets 1e_a_number_ as an exponent - i.e. 10 to the power of something. 1a_a_number has no mathematical meaning, therefore it's not parsed as being a number and accepted as valid. If you notice at the beginning of my experiments below - pure numbers aren't allowed as database/schema names.

Congratulations - it looks like you've spotted a MySQL bug.

Something like this (underscore following number and e) works.

MariaDB [(none)]> create schema 1_e34343_db;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> 

which appears to confirm my conclusion.

This does also - i.e. 1ex works, but 1e1 fails.

MariaDB [(none)]> create schema 1ex;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create schema 1e1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1e1' at line 1
MariaDB [(none)]> 

=== Experiments leading me to my conclusion ============

MariaDB [(none)]> create schema 5e370227_db;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '5e370227_db' at line 1
MariaDB [(none)]> create database 5e370227_db;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '5e370227_db' at line 1
MariaDB [(none)]> create schema  5a370227_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> drop schema  5a370227_db;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> create database  5a370227_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> drop database  5a370227_db;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> create database 100;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '100' at line 1
MariaDB [(none)]> create database 100_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 1000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 10000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 100000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 1000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 10000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 100000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 1000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 10000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 100000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 1000000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 10000000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 100000000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 1000000000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 10000000000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 100000000000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 10000000000000000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 10000000000000000000000000000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 1e0000000000000000000000000000000000_db;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1e0000000000000000000000000000000000_db' at line 1
MariaDB [(none)]> create database 1e000000000000000_db;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1e000000000000000_db' at line 1
MariaDB [(none)]> create database 1e0000000000_db;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1e0000000000_db' at line 1
MariaDB [(none)]> create database 1e4_db;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1e4_db' at line 1
MariaDB [(none)]> create database 1e4_zx;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1e4_zx' at line 1
MariaDB [(none)]> 
MariaDB [(none)]> create database 1a4_zx;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> 
  • Thank you, I have tried to create a new defect to mysql: bugs.mysql.com/75700 – Felix Jan 30 '15 at 12:42
  • +1 for solid sleuthing but it's not an entirely unknown issue (as was also noted in the bug report). The same exception applies to usernames, and any reserved word used as an identifier. The workaround, in any of these cases, is backticks. I would wager against this being fixed any time soon, and plan to use the backticks, or prefix the databases with a character, instead. – Michael - sqlbot Feb 2 '15 at 0:11
  • Thanks for that (the +1 and the docco). Saves me going to the bother of reporting it - would have been my first MySQL bug <sob, sob...>. Oops, just noticed Felix's report - it's annoying,but not mission critical as they point out. – Vérace Feb 2 '15 at 0:18

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.