phantom database

Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (x86_64) using readline 6.1

Logged in as root, SHOW DATABASES displays a DB name that is
inaccessible. I haven't done anything with this DB for ~5 years. I was
recently asked to do some work on the project and was actually surprised
that I (supposedly) still had it. When I moved from fedora to ubuntu I
did copy over some DBs although I can't remember if I'd specifically
copied this one over.

When I noticed it was still there I tried to access it:

mysql mysql > \u db_enzyme;
ERROR 1049 (42000): Unknown database 'db_enzyme;'

OK, no big deal. I've got the dump file, so I figured I'd drop &
recreate it:

mysql mysql > DROP DATABASE db_enzyme;
Query OK, 11 rows affected (0.00 sec)

mysql mysql > SET NAMES 'utf8';
Query OK, 0 rows affected (0.01 sec)

mysql mysql > CREATE DATABASE db_enzyme DEFAULT CHARACTER SET utf8;
Query OK, 1 row affected (0.00 sec)

mysql mysql > GRANT ALL PRIVILEGES ON db_enzyme.* TO ...;
Query OK, 0 rows affected (0.05 sec)

mysql mysql > GRANT ALL PRIVILEGES ON db_enzyme.* TO ...;
Query OK, 0 rows affected (0.01 sec)

mysql mysql > FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.04 sec)

mysql mysql > \u db_enzyme;
ERROR 1049 (42000): Unknown database 'db_enzyme;'

OK. Is it really being dropped?

mysql mysql > DROP DATABASE db_enzyme;
Query OK, 0 rows affected (0.00 sec)

mysql mysql > SHOW DATABASES;

It's not there.

mysql mysql > CREATE DATABASE db_enzyme DEFAULT CHARACTER SET utf8;
Query OK, 1 row affected (0.00 sec)

mysql mysql > SHOW DATABASES;

It's there.

mysql mysql > \u db_enzyme;
ERROR 1049 (42000): Unknown database 'db_enzyme;'

It shows up in /var/lib/mysql no different than the others:

drwx------ 2 mysql mysql 4096 2011-03-28 17:19 db_enzyme

As I said, I have a dump to work with. I am not interested in recovering
the DB, which was why I tried dropping it. And I can obviously use a
different name. But I'd like to sort out what's going on here. There
appears to be corruption somewhere, or at least something's out of sync.
Which tables in the mysql DB should I be looking at? Or could the
trouble be at the filesystem level?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: phantom database

In the last episode (Mar 28), brian said:
> Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (x86_64) using readline 6.1
>
> Logged in as root, SHOW DATABASES displays a DB name that is inaccessible.
> I haven't done anything with this DB for ~5 years. I was recently asked
> to do some work on the project and was actually surprised that I
> (supposedly) still had it. When I moved from fedora to ubuntu I did copy
> over some DBs although I can't remember if I'd specifically copied this
> one over.
>
> When I noticed it was still there I tried to access it:
>
> mysql mysql > \u db_enzyme;
> ERROR 1049 (42000): Unknown database 'db_enzyme;'

I think you want "\u db_enzyme" here, without a semicolon. \u isn't an SQL
command so it isn't terminated by a semicolon. That why the error message
included it in the database name. The long version of that comand (use)
will strip a trailing semicolon automatically:

mysql> use test;
Database changed
mysql> use test
Database changed
mysql> \u test;
ERROR 1049 (42000): Unknown database 'test;'
mysql> \u test
Database changed
mysql>

From the docs:

MYSQL COMMANDS
mysql sends each SQL statement that you issue to the server to be
executed. There is also a set of commands that mysql itself
interprets. For a list of these commands, type help or \h at the
mysql> prompt:

Each command has both a long and short form. The long form is not
case sensitive; the short form is. The long form can be followed by
an optional semicolon terminator, but the short form should not.



--
Dan Nelson
dnelson@allantgroup.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: phantom database

On 11-03-28 06:38 PM, Dan Nelson wrote:
> In the last episode (Mar 28), brian said:
>> Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (x86_64) using readline 6.1
>>
>> Logged in as root, SHOW DATABASES displays a DB name that is inaccessible.
>> I haven't done anything with this DB for ~5 years. I was recently asked
>> to do some work on the project and was actually surprised that I
>> (supposedly) still had it. When I moved from fedora to ubuntu I did copy
>> over some DBs although I can't remember if I'd specifically copied this
>> one over.
>>
>> When I noticed it was still there I tried to access it:
>>
>> mysql mysql> \u db_enzyme;
>> ERROR 1049 (42000): Unknown database 'db_enzyme;'
>
> I think you want "\u db_enzyme" here, without a semicolon. \u isn't an SQL
> command so it isn't terminated by a semicolon. That why the error message
> included it in the database name. The long version of that comand (use)
> will strip a trailing semicolon automatically:
>
> mysql> use test;
> Database changed
> mysql> use test
> Database changed
> mysql> \u test;
> ERROR 1049 (42000): Unknown database 'test;'
> mysql> \u test
> Database changed
> mysql>
>
> From the docs:
>
> MYSQL COMMANDS
> mysql sends each SQL statement that you issue to the server to be
> executed. There is also a set of commands that mysql itself
> interprets. For a list of these commands, type help or \h at the
> mysql> prompt:
>
> Each command has both a long and short form. The long form is not
> case sensitive; the short form is. The long form can be followed by
> an optional semicolon terminator, but the short form should not.
>
>
>

Oh, good frigging grief! I only use that command several times a day.
For years now.

And that, boys & girls, is why posting the relevant passages from your
terminal session will almost always be better than a description of your
problem alone.

And, using the history facility is not always a good idea when trying to
sort out a problem.

Thanks!

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org