GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

cannot drop a database created by xtrabackup

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • cannot drop a database created by xtrabackup

    I tested xtrabackup a few weeks ago, and it created a database with the name "#mysql50#xtrabackup-201107011748" in my MySQL v5.1.50 (remi) instance as follows:

    # mysql
    Welcome to the MySQL monitor. Commands end with ; or \g.
    Your MySQL connection id is 744523
    Server version: 5.1.50 MySQL Community Server (GPL) by Remi

    Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
    This software comes with ABSOLUTELY NO WARRANTY. This is free software,
    and you are welcome to modify and redistribute it under the GPL v2 license

    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

    mysql> show databases;
    +----------------------------------+
    | Database
    +----------------------------------+
    | information_schema
    | mysql
    | ...
    | ...
    | #mysql50#xtrabackup-201107011748
    +----------------------------------+
    8 rows in set (0.00 sec)

    I am trying to drop this database, and no matter what I do, I just get a syntax error.

    I'm sure I'm just being stupid... how do I get rid of this database?

    Thanks,
    Jay

    e.g.
    mysql> drop database '#mysql50#xtrabackup-201107011748';
    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 ''#mysql50#xtrabackup-201107011748'' at line 1
    mysql> drop database 'xtrabackup-201107011748';
    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 ''xtrabackup-201107011748'' at line 1

  • #2
    The object name includes unallowed characters for unquoted identifiers, to drop it you will have to quote it:

    DROP DATABASE `#mysql50#xtrabackup-201107011748`;

    http://dev.mysql.com/doc/refman/5.1/en/identifiers.html
    Our documentation has a lot of answers about common questions on Percona software, have you checked there before posting that question here? http://www.percona.com/forums/core/i...lies/smile.png

    Join us at the annual Percona Live MySQL Users Conference - http://www.percona.com/live/mysql-conference-2014/

    Comment


    • #3
      viiin wrote on Wed, 03 August 2011 00:39
      The object name includes unallowed characters for unquoted identifiers, to drop it you will have to quote it:

      DROP DATABASE `#mysql50#xtrabackup-201107011748`;

      http://dev.mysql.com/doc/refman/5.1/en/identifiers.html
      Thanks for the suggestion viiin. Here's what I get -

      Quote:
      mysql> show databases;
      +----------------------------------+
      | Database
      +----------------------------------+
      | information_schema
      | mysql
      | ...
      | #mysql50#xtrabackup-201107011748
      +----------------------------------+
      8 rows in set (0.00 sec)

      mysql> drop database `#mysql50#xtrabackup-201107011748`;
      ERROR 1008 (HY000): Can't drop database '#mysql50#xtrabackup-201107011748'; database doesn't exist
      Uh oh. Now what?
      -Jay

      Comment


      • #4
        libove, sorry - did you upgrade MySQL after the backup? Did you run mysql_upgrade afterwards? If not, run it first, I'd expect mysql_upgrade to correct the schema name.
        Our documentation has a lot of answers about common questions on Percona software, have you checked there before posting that question here? http://www.percona.com/forums/core/i...lies/smile.png

        Join us at the annual Percona Live MySQL Users Conference - http://www.percona.com/live/mysql-conference-2014/

        Comment


        • #5
          The MySQL server instance has not been upgraded since the installation and run of Xtrabackup. Perhaps I ran the wrong variant of Xtrabackup, which is why it created a MySQL version specific named database instance, but I'm not sure how that explains why I cannot drop that database?
          Thanks!
          Jay

          Comment


          • #6
            libove - I dont think XtraBackup creates any database during backup or restore. Please check this database within the data directory, it could be just an empty directory and not a valid database after all. MySQL treats all directories within the data directory as databases and will try to read them when attempted to be used.
            Our documentation has a lot of answers about common questions on Percona software, have you checked there before posting that question here? http://www.percona.com/forums/core/i...lies/smile.png

            Join us at the annual Percona Live MySQL Users Conference - http://www.percona.com/live/mysql-conference-2014/

            Comment


            • #7
              viiin wrote on Thu, 04 August 2011 02:01
              libove - I dont think XtraBackup creates any database during backup or restore. Please check this database within the data directory, it could be just an empty directory and not a valid database after all. MySQL treats all directories within the data directory as databases and will try to read them when attempted to be used.
              Hi, spot on! Thanks so much for identifying that.
              Xtrabackup - or maybe it was me in my ignorance - created its temporary directory under /var/lib/mysql, and indeed MySQL got confused about that directory being a database.

              I've removed that directory (I'll get back to testing Xtrabackup later, maybe) and MySQL is happy again.

              Thanks!
              Jay

              Comment

              Working...
              X