Ошибка 1051 mysql

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

In Managed Services, we have many customers, and as each has a different kind of config and environment, working on their environment is always fun and interesting. In this blog post, I will showcase an issue we faced when dropping a table and how it was resolved.

There was a ticket to drop a table in a client’s production environment (MySQL 5.7). The table had a # symbol at the beginning of the table’s name. I thought it was easy that we can use quotes or backtick symbols to specify the table to drop. But it did not work as I expected and I came to know why the customer created the ticket to drop the table.

The following example recreates the problem. It shows the table, but you are unable to see the structure and cannot drop it.

When checking the .ibd and .frm files, those are present and nothing wrong with those physical files.

I thought the problem was due to the # symbol and wanted to try creating a new table with the # symbol and drop it. The result was a surprise that we were able to create the table and drop it. But again, it failed to drop the table which was given by the client.

Here we noticed one thing — we created the table in uppercase and when showing the table it showed in lowercase. This gives us a clue to check lower_case_table_names and it was set to 1 (by default 0 in Unix). So we wanted to try to create a table in one value and drop it when it is set to another.

Lower_case_table_names values and their behavior:

Scenario One: Create table when lower_case_table_names=0 and drop when lower_case_table_names=1

Set lower_case_table_names=0 and created tables and one database with uppercase.

To change the value of lower_case_table_names to 1, I just changed the value in config and restarted the MySQL service. When lower_case_table_names is 1,  you can see the first drop table of #Table1_test2 is successful and it shows, but the drop is failed for #table1_test2  and not showing in the table list. This is due to case-insensitive, as wherever we use uppercase it will lookup in lowercase only. This is the reason dropping the table of  #Table1_test2 dropped the table #table1_test2.

We are unable to use Test_database since it was created in uppercase. So whatever the tables are inside the database will not be accessible. In short,  when lower_case_table_names=1 uppercase letters are on tables and databases they will be treated as lowercase letters.

Scenario Two: Create table when lower_case_table_names=1 and drop when lower_case_table_names=0

When we tried to create databases and tables in uppercase, it created those in lowercase only. Creation of #table1_test2 failed with error already exists, since the first created statement of #Table1_test2 was converted into lowercase and created table #table1_test2. The same happened when creating the table Table1_test3 was successful,  it was created as table1_test3 and failed when creating again the table table1_test3.

To change the value of lower_case_table_names from 1 to 0, I just changed the value in config and restarted the MySQL service.  We were able to drop the tables and database when lowercase_table_name=0, since the database and table were not created with uppercase.

Scenario one was the one that we faced in our client environment. The client long ago created the table when lower_case_table_names=0 and they changed it after some time to lower_case_table_names=1.  So we got approval to set lower_case_table_names=0 and dropped the table and reverted it back to 1. Since this change was not dynamic, we needed a server restart.

Conclusion

I am not suggesting using 1 or 0 in lower_case_table_names, since this is based on the application requirement. But before changing the value from 0 to 1, please check if there are any tables or databases with uppercase. If any, those need to be converted into lowercase, as otherwise those uppercase tables and databases will not be accessible.  When changing the value from 1 to 0, we did not face a problem, since the tables and databases are not allowed to be created in uppercase and those are created in lowercase only due to case-insensitivity. In MySQL 8.0 you won’t be able to change lower_case_table_names value after database instance creation, as the value of this variable affects definitions of data-dictionary tables and can’t be changed after the server is initialized.

To correctly drop a view, use the below syntax −

drop view yourViewName;

Let us first create a table −

mysql> create table DemoTable
-> (
-> Id int
-> );
Query OK, 0 rows affected (1.01 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable values(1001);
Query OK, 1 row affected (0.22 sec)

mysql> insert into DemoTable values(1002);
Query OK, 1 row affected (0.34 sec)

mysql> insert into DemoTable values(1003);
Query OK, 1 row affected (0.26 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

Output

This will produce the following output −

+------+
| Id   |
+------+
| 1001 |
| 1002 |
| 1003 |
+------+
3 rows in set (0.00 sec)

Let us first create a view −

mysql> create view view_DemoTable as select Id from DemoTable;
Query OK, 0 rows affected (0.23 sec)

We will now display the records of the view −

mysql> select *from view_DemoTable;

Output

This will produce the following output −

+------+
| Id   |
+------+
| 1001 |
| 1002 |
| 1003 |
+------+
3 rows in set (0.05 sec)

Following is the query to drop view −

mysql> drop view view_DemoTable;
Query OK, 0 rows affected (0.18 sec)

Now view is dropped successfully.