Discover Centmin Mod today
Register Now

Can't delete database

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

  1. Jon Snow

    Jon Snow Active Member

    477
    70
    28
    Jun 30, 2017
    Ratings:
    +109
    Local Time:
    4:44 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. Jon Snow

    Jon Snow Active Member

    477
    70
    28
    Jun 30, 2017
    Ratings:
    +109
    Local Time:
    4:44 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. brijendrasial

    brijendrasial Member

    89
    80
    18
    Mar 21, 2018
    Ratings:
    +118
    Local Time:
    2:14 PM
    1.13.9
    10.0.22-MariaDB
    what command you are running to delete?
     
  4. Jon Snow

    Jon Snow Active Member

    477
    70
    28
    Jun 30, 2017
    Ratings:
    +109
    Local Time:
    4:44 AM
    Nginx 1.13.9
    MariaDB 10.1.31
    Code (Text):
    mysql -u root
    DROP DATABASE database_name;
     
  5. eva2000

    eva2000 Administrator Staff Member

    43,132
    9,792
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,122
    Local Time:
    6:44 PM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    can you give example of none working database and user name ?
     
  6. Jon Snow

    Jon Snow Active Member

    477
    70
    28
    Jun 30, 2017
    Ratings:
    +109
    Local Time:
    4:44 AM
    Nginx 1.13.9
    MariaDB 10.1.31
    I'll PM you.
     
  7. eva2000

    eva2000 Administrator Staff Member

    43,132
    9,792
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,122
    Local Time:
    6:44 PM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    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
     
    • Like Like x 1
  8. Jon Snow

    Jon Snow Active Member

    477
    70
    28
    Jun 30, 2017
    Ratings:
    +109
    Local Time:
    4:44 AM
    Nginx 1.13.9
    MariaDB 10.1.31
    @eva2000 That worked. I guess I'll have to delete digit-only names that way.