Ошибка 1051 mysql

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.

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

Just to add my solution as I had a similar problem.

TL;DR

• Recreate the table with the same foreign key specification but with a different name as previously held by the table .
• Drop the resulting table (will also drop original orphan foreign key)
• Recreate table with original or no foreign key

Detail

I ran into the nasty situation where an ALTER TABLE statement failed due to a foreign key not being dropped earlier. This led to some inconsistencies in the InnoDB data dictionary (probably due to http://bugs.mysql.com/bug.php?id=58215).

Related question here: https://stackoverflow.com/questions/16857451/error-in-foreign-key-constraint-on-a-droped-table

mysql> ALTER TABLE `visits` CHANGE COLUMN `variation_visitor_id` `variation_visitor_id` INT(11) NOT NULL  ;

Error on rename of ‘./db/#sql-482c_8448f’ to ‘./db/visits’ (errno: 150)

mysql> SHOW ENGINE INNODB STATUS;

Error in foreign key constraint of table db/visits:
 FOREIGN KEY (`variation_visitor_id`) REFERENCES `variations_visitors` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html 
for correct foreign key definitippon.

As I couldn’t recover the #sql-482c_8448f table to visits, I decided to reimport it from a backup done just before the alter. However this failed. On investigation:

• The constraint had been removed from INFORMATION_SCHEMA.TABLE_CONSTRAINTS and INFORMATION_SCHEMA.STATISTICS
• But the constraint was still visible in INFORMATION_SCHEMA.INNODB_SYS_FOREIGN;
• The table didn’t exist so I couldn’t drop the foreign key
• I couldn’t create the table without errors

SQL/Errors

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN WHERE ID='db/fk_visits_variations_visitors1';

+-----------------------------------+-----------+------------------------+--------+------+
| ID                                | FOR_NAME  | REF_NAME               | N_COLS | TYPE |
+-----------------------------------+-----------+------------------------+--------+------+
| db/fk_visits_variations_visitors1 | db/visits | db/variations_visitors |      1 |   48 |
+-----------------------------------+-----------+------------------------+--------+------+

Trying to recreate the table without the foreign key caused ane error 150

mysql>

SET UNIQUE_CHECKS = 0;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE `visits` (
  `id` INT(11) NOT NULL AUTO_INCREMENT  ,
  `variation_visitor_id` INT(11) NOT NULL  ,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET FOREIGN_KEY_CHECKS = 1;
SET UNIQUE_CHECKS = 1;

ERROR 1005 (HY000) at line 26: Can't create table 'db.visits' (errno: 150)

mysql> SHOW ENGINE INNODB STATUS;

Error in foreign key constraint of table db/visits:
there is no index in the table which would contain
the columns as the first columns, or the data types in the
table do not match the ones in the referenced table
or one of the ON ... SET NULL columns is declared NOT NULL. Constraint:
,
  CONSTRAINT "fk_visits_variations_visitors1" FOREIGN KEY ("variation_visitor_id") REFERENCES "variations_visitors" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION

Trying to create it with caused a error 121

mysql>

SET UNIQUE_CHECKS = 0;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE `visits` (
  `id` INT(11) NOT NULL AUTO_INCREMENT  ,
  `variation_visitor_id` INT(11) NOT NULL  ,
  PRIMARY KEY (`id`),
  KEY `fk_visits_variations_visitors1` (`variation_visitor_id`),
  CONSTRAINT `fk_visits_variations_visitors1` FOREIGN KEY (`variation_visitor_id`) REFERENCES `variations_visitors` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET FOREIGN_KEY_CHECKS = 1;
SET UNIQUE_CHECKS = 1;

ERROR 1005 (HY000) at line 26: Can't create table 'db.visits' (errno: 121)

mysql> SHOW ENGINE INNODB STATUS;

Error in foreign key constraint creation for table `db`.`visits`.
A foreign key constraint of name `db`.`fk_visits_variations_visitors1`
already exists. (Note that internally InnoDB adds 'databasename'
in front of the user-defined constraint name.)
Note that InnoDB's FOREIGN KEY system tables store
constraint names as case-insensitive, with the
MySQL standard latin1_swedish_ci collation. If you
create tables or databases whose names differ only in
> the character case, then collisions in constraint
names can occur. Workaround: name your constraints
explicitly with unique names.

Eventually I used a new foreign key name. I wasn’t expecting this to work but it allowed the table to be created.

mysql>

SET UNIQUE_CHECKS = 0;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE `visits` (
  `id` INT(11) NOT NULL AUTO_INCREMENT  ,
  `variation_visitor_id` INT(11) NOT NULL  ,
  PRIMARY KEY (`id`),
  KEY `fk_visits_variations_visitors2` (`variation_visitor_id`),
  CONSTRAINT `fk_visits_variations_visitors2` FOREIGN KEY (`variation_visitor_id`) REFERENCES `variations_visitors` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET FOREIGN_KEY_CHECKS = 1;
SET UNIQUE_CHECKS = 1;

Simply dropping the table after that removed the errant record in INFORMATION_SCHEMA.INNODB_SYS_FOREIGN, allowing an import with the original foreign key name.