/ Register

Get the most out of your Centmin Mod LEMP stack
Become a Member

Can't delete database

Discussion in 'System Administration' started by Jon Snow, Feb 11, 2020.

Previous Thread Next Thread
Loading...
  1. Feb 11, 2020 #1
    Jon Snow

    Jon Snow Active Member

    917
    188
    43
    Jun 30, 2017
    Ratings:
    +293
    Local Time:
    9:17 AM
    Nginx 1.13.9
    MariaDB 10.1.31
    When I try to drop the database, it gives me the error:
    Code (Text):
    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 'database_name' at line 1

    How can I delete it?
     
  2. Feb 11, 2020 #2
    Jon Snow

    Jon Snow Active Member

    917
    188
    43
    Jun 30, 2017
    Ratings:
    +293
    Local Time:
    9:17 AM
    Nginx 1.13.9
    MariaDB 10.1.31
    If it helps, the 1-2 GB database took ages to import (I had to exit the screen after hours of it doing its thing because I believe it was stuck). I used @eva2000's script to create the database, uploaded the sql file to the server and used the normal mysql import command to import the database file to the database.

    After, I upgraded my VPS from Linode's 1 GB to the 8 GB plan and the import went smoothly. I also had no issues deleting this database. The only difference between this and the first was that I didn't use numbers alone for names.

    The first database and its user were named after 4 numbers. Both database and user are unable to be deleted.
     
  3. Feb 11, 2020 #3
    brijendrasial

    brijendrasial Active Member

    214
    155
    43
    Mar 21, 2018
    Ratings:
    +237
    Local Time:
    5:47 PM
    1.13.9
    10.0.22-MariaDB
    what command you are running to delete?
     
  4. Feb 12, 2020 #4
    Jon Snow

    Jon Snow Active Member

    917
    188
    43
    Jun 30, 2017
    Ratings:
    +293
    Local Time:
    9:17 AM
    Nginx 1.13.9
    MariaDB 10.1.31
    Code (Text):
    mysql -u root
DROP DATABASE database_name;
     
  5. Feb 12, 2020 #5
    eva2000

    eva2000 Administrator Staff Member

    58,895
    12,490
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +19,122
    Local Time:
    10:17 PM
    Nginx 1.31.x
    MariaDB 10.x/11.4+/12.3+
    can you give example of none working database and user name ?
     
  6. Feb 12, 2020 #6
    Jon Snow

    Jon Snow Active Member

    917
    188
    43
    Jun 30, 2017
    Ratings:
    +293
    Local Time:
    9:17 AM
    Nginx 1.13.9
    MariaDB 10.1.31
    I'll PM you.
     
  7. Feb 12, 2020 #7
    eva2000

    eva2000 Administrator Staff Member

    58,895
    12,490
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +19,122
    Local Time:
    10:17 PM
    Nginx 1.31.x
    MariaDB 10.x/11.4+/12.3+
    see MySQL :: MySQL 8.0 Reference Manual :: 9.2 Schema Object Names

    interesting

    create database named = 1010
    Code (Text):
    mysqladmin create 1010

    check database listing
    Code (Text):
    mysql -e "show databases like '%1010';"
+------------------+
| Database (%1010) |
+------------------+
| 1010             |
+------------------+

    try deleting database = 1010
    Code (Text):
    mysqladmin drop 1010        
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.
Do you really want to drop the '1010' database [y/N] y
Database "1010" dropped

    worked fine here

    or database 1010_dbname
    Code (Text):
    mysqladmin create 1010_dbname

    Code (Text):
    mysql -e "show databases like '1010%';"
+------------------+
| Database (1010%) |
+------------------+
| 1010_dbname      |
+------------------+

    Code (Text):
    mysqladmin drop 1010_dbname          
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.
Do you really want to drop the '1010_dbname' database [y/N] y
Database "1010_dbname" dropped

    works as well
     
  8. Feb 12, 2020 #8
    Jon Snow

    Jon Snow Active Member

    917
    188
    43
    Jun 30, 2017
    Ratings:
    +293
    Local Time:
    9:17 AM
    Nginx 1.13.9
    MariaDB 10.1.31
    @eva2000 That worked. I guess I'll have to delete digit-only names that way.
     
Previous Thread Next Thread
Loading...

.