Schrodingers MySQL table: exists, yet it does not

I am having the weirdest error of all.

Sometimes, when creating or altering tables, I get the 'table already exists' error. However, DROP TABLE returns '#1051 - unknown table'. So I got a table I cannot create, cannot drop.

When I try to drop the database, mysqld crashes. Sometimes it helps to create another db with different name, sometimes it does not.

I use a DB with ~50 tables, all InnoDB. This problem occurs with different tables.

I experienced this on Windows, Fedora and Ubuntu, MySQL 5.1 and 5.5. Same behaviour, when using PDO, PHPMyAdmin or commandline. I use MySQL Workbench to manage my schema - I saw some related errors (endlines and stuff), however none of them were relevant for me.

No, it is not a view, it is a table. All names are lowercase.

I tried everything I could google - flushing tables, moving .frm files from db to db, reading mysql log, nothing helped but reinstalling the whole damn thing.

'Show tables' reveals nothing, 'describe' table says 'table doesn't exist,' there is no .frm file, yet 'create table' still ends with an error (and so does 'create table if not exists') and dropping database crashes mysql

Related, yet unhelpful questions:

• Mysql 1050 Error "Table already exists" when in fact, it does not
• MySQL Table does not exist error, but it does exist

Edit:

mysql> use askyou;
Database changed

mysql> show tables;
Empty set (0.00 sec)

mysql> create table users_has_friends (id int primary key);
ERROR 1050 (42S01): Table '`askyou`.`users_has_friends`' already exists

mysql> drop table users_has_friends;
ERROR 1051 (42S02): Unknown table 'users_has_friends'

And such, all the same: table doesn't exist, yet cannot be created;

mysql> drop database askyou;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Names change, this is not the only table / database I've run into problems with

 Answers

I've seen this issue when the data file is missing in the data directory but the table definition file exists or vise-versa. If you're using innodb_file_per_table, check the data directory to make sure you have both an .frm file and .ibd file for the table in question. If it's MYISAM, there should be a .frm, .MYI and a .MYD file.

The problem can usually be resolved by deleting the orphaned file manually.

---

I doubt this is a direct answer to the question case here, but here is how I solved this exact perceived problem on my OS X Lion system.

I frequently create/drop tables for some analytics jobs I have scheduled. At some point, I started getting table already exists errors half-way through my script. A server restart typically solved the issue, but that was too annoying of a solution.

Then I noticed in the local error log file this particular line:

[Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive

This gave me the idea that maybe if my tables contained capital letters, MySQL would be fooled into thinking they are still there even after I had dropped them. That turned out to be the case and switching to using only lowercase letters for table names made the problem go away.

It is likely the result of some misconfiguration in my case, but hopefully this error case will help someone waste less time trying to find a solution.

---

In my case the problem was solved by changing the ownership of the mysql data directory to the user that ran the application. (In my case it was a Java application running Jetty webserver.)

Even though mysql was running and other apps could use it properly, this app had a problem with that. After changing the data directory ownership and resetting the user's password, everything worked properly.

---

If will are stock with this error 1051 and you only want to delete the database and import this again do this steps and all gonna be just fine....

in Unix envoriment AS root:

• rm -rf /var/lib/mysql/YOUR_DATABASE;
• OPTIONAL -> mysql_upgrade --force
• mysqlcheck -uUSER -pPASS YOUR_DATABASE
• mysqladmin -uUSER -pPASS drop YOUR_DATABASE
• mysqladmin -uUSER -pPASS create YOUR_DATABASE
• mysql -uUSER -pPASS YOUR_DATABASE < IMPORT_FILE

Regards, Christus

---

I ran into this error after I created a table and deleted it, then wanted to create it again. In my case, I had a self-contained dump file so I dropped my schema, recreated it and imported tables and data using the dump file.

---

I was having this problem with one particular table. Reading the possible solutions i've did some steps like:

• Search for orphan files: didn't exist anyone;
• execute: show full tables in database;: didn't see the problematic one;
• execute: describe table;: returned table doesn't exist;
• execute: SELECT * FROM information_schema.TABLES WHERE TABLE_NAME='table';: returned Empty set;
• Search by the phpMyAdmin manually the query above: didn't exist;

And, after those steps, i check again with the show tables; and... vualá! the problematic table was gone. I could create it and drop it with the same problematic name with no problem, and i didn't have even to restart the server! Weird...

PHP function to check if a MySQL table exists

Yesterday I posted how to check if a MySQL table exists using show tables or the MySQL information schema. Today I am posting a simple PHP function I created which you can use to test if a table exists.

The PHP function below gets passed in a tablename and an optional database name. If the database name is not passed in then it retrieves it using the MySQL function SELECT DATABASE(). It then queries the MySQL information schema to see if the table exists and then returns either true or false.

function table_exists($tablename, $database = false) {

    if(!$database) {
        $res = mysql_query("SELECT DATABASE()");
        $database = mysql_result($res, 0);
    }

    $res = mysql_query("
        SELECT COUNT(*) AS count 
        FROM information_schema.tables 
        WHERE table_schema = '$database' 
        AND table_name = '$tablename'
    ");

    return mysql_result($res, 0) == 1;

}

The PHP MySQL functions are used in the above example. A database connection is assumed and there is no error checking, but you can modify it to utilise whatever database library / abstraction layer you are using in your project and improve how you see fit.

To use the function you'd do something like this:

if(table_exists('my_table_name')) {
    // do something
}
else {
    // do something else
}

and if you wanted to specify the database name as well (perhaps you are needing to query if the table exists in multiple databases other than the one you are currently connected to), you'd do this:

if(table_exists('my_table_name', 'my_database_name')) {
    // do something
}
else {
    // do something else
}

Check if a MySQL table exists

MySQL has a couple of ways (that I know of, there may be more) of working out if a table exists. This post looks at how to check if a table exists in the MySQL database.

Using show tables

The first way is using the "show tables" function. If your database (called "test" in this example) had three tables name "test1", "test2" and "another_test", running "show tables" would display this:

+------------------------+
| Tables_in_test         |
+------------------------+
| another_test           |
| test1                  |
| test2                  |
+------------------------+
3 rows in set (0.01 sec)

You can use show tables like this to see if a single table exists:

mysql> show tables like "test1";

which would return:

+------------------------+
| Tables_in_test (test1) |
+------------------------+
| test1                  |
+------------------------+
1 row in set (0.00 sec)

If you ran show tables on a table that didn't exist you would get this:

mysql> show tables like "test3";
Empty set (0.01 sec)

So that's one way of checking if a table exists in MySQL. You can use your programming language of choice to connect to the database, run a query like the above and then check if there are any rows to see if the table exists.
Note that you can also do e.g. "show tables like 'test%'" which using the above tables would return both test1 and test2 if you needed this for another purpose.

Using the information schema

From MySQL 5.0 there is an information schema database which contains the information about the databases. This database can be used to determine various information, including whether or not a table exists in a given database in MySQL.
The syntax for this purpose would look like this:

SELECT COUNT(*)
FROM information_schema.tables 
WHERE table_schema = '[database name]' 
AND table_name = '[table name]';

Using our examples above and checking to see if the "another_test" table exists we would do this:

SELECT COUNT(*)
FROM information_schema.tables 
WHERE table_schema = 'test' 
AND table_name = 'another_test';

This seems to me to be a better way of checking for a table's existence than the show tables method and is the one I prefer when using MySQL 5.0 or higher.

Related posts:

• Get a MySQL table structure from the INFORMATION_SCHEMA
• Show indexes for a table with MySQL
• Copy a table in MySQL with CREATE TABLE LIKE
• How to tell which storage engine a MySQL table uses
• Insert multiple records into MySQL with a single query
• PHP function to check if a MySQL table exists

Mysql: The SQL subquery does not return what I expect

I have this problem to solve: If two students A and B are friends, and A likes B but not vice-versa, remove the Likes entry.

For background, the Friend table has two columns, STU1 and STU2. If they are friends, then there will be an entry showing STU1, STU2 AND STU2, STU1.

In the Likes table, if Student A likes Student B, there will be an entry for STU1, STU2, but if Student B does not like Student A, there will NOT be an entry for STU2, STU1.

So, here is what I have tried. The problem is that it still leaves two rows in the Likes table that should be out of there. Any ideas on how to solve this on?

delete from Likes
where exists
    (select F.STU1, F.STU2 from Friend F
        where exists
        (select L.STU1, L.STU2 from Likes L, Friend F where
            F.STU1 = L.STU1 and F.STU2 = L.STU2)
        )
    and not exists
        (select L.STU1, L.STU2 from Likes L, Friend F where
            F.STU1 = L.STU2 and F.STU2 = L.STU1)

---

edit:

with onewayfriends as (
select f.* from friend f
left outer join likes l1 on l1.stu1=f.stu1 and l1.stu2=f.stu2
left outer join likes l2 on l2.stu1=f.stu2 and l2.stu2=f.stu1
where l1.stu1 is null or l2.stu1 is null)

delete l from likes l, onewayfriends f
where l.stu1 in (f.stu1, f.stu2) and l.stu2 in (f.stu1, f.stu2)

edit 2, since it's sqllite rewrite the cte as a nested query and the delete with join as a 'where row id in..'

delete from likes where rowid in (select l.rowid from
(
  select f.* from friend f
  left outer join likes l1 on l1.stu1=f.stu1 and l1.stu2=f.stu2
  left outer join likes l2 on l2.stu1=f.stu2 and l2.stu2=f.stu1
  where l1.stu1 is null or l2.stu1 is null
) f, likes l
where l.stu1 in (f.stu1, f.stu2) and l.stu2 in (f.stu1, f.stu2))