Mysql – Cannot create MySQL database with name ‘5e370227_db’

MySQL

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

Best Answer

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)]>
Related Question