giftcertified.blogg.se

#Mysql drop update#
#Mysql drop code#

I couldn't create the table without errors.

The table didn't exist so I couldn't drop the foreign key.

But the constraint was still visible in INFORMATION_SCHEMA.INNODB_SYS_FOREIGN.

The constraint had been removed from INFORMATION_SCHEMA.TABLE_CONSTRAINTS and INFORMATION_SCHEMA.STATISTICS.

Tables created with >= InnoDB-4.1.12, and such columns in old tablesĬannot be referenced by such columns in new tables.Īs I couldn't recover the #sql-482c_8448f table to visits, I decided to reimport it from a backup done just before the alter. Note that the internal storage type of ENUM and SET changed in In the table and the referenced table do not match for constraint. Referenced columns appear as the first columns, or column types

#Mysql drop update#

Related question here: mysql> ALTER TABLE `visits` CHANGE COLUMN `variation_visitor_id` `variation_visitor_id` INT(11) NOT NULL Įrror on rename of './db/#sql-482c_8448f' to './db/visits' (errno: 150) mysql> SHOW ENGINE INNODB STATUS Įrror in foreign key constraint of table db/visits:įOREIGN KEY (`variation_visitor_id`) REFERENCES `variations_visitors` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTIONĬannot find an index in the referenced table where the This led to some inconsistencies in the InnoDB data dictionary (probably due to ). I ran into the nasty situation where an ALTER TABLE statement failed due to a foreign key not being dropped earlier.

Recreate table with original or no foreign key.

Drop the resulting table (will also drop original orphan foreign key).

Recreate the table with the same foreign key specification but with a different name as previously held by the table.

Just to add my solution as I had a similar problem. I have dealt with this problem before in my post InnoDB table SELECT returns ERROR 2006 (HY000): MySQL server has gone away (after power outage)

Detailed steps from my StackOverflow post.

mysqldump all the data, triggers and stored procedures.

#Mysql drop code#

This will result in you changing the table name in your application code SUGGESTION #2 Use a different table name CREATE TABLE my_usertable (id INT AUTO_INCREMENT NOT NULL, username VARCHAR(255), group_id VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB Once you switch to InnoDB, it goes right back to the data dictionary, which is faulty on that one entry.ĭon't create the table with that name anymore. This sequence of events can be proved if you create the table using MyISAM. InnoDB says "I already have the tablespace_id of my_user registered". because mysqld thinks it is OK to create the table but then defers to the storage engine. Although my_m does not exist, you cannot run CREATE TABLE my_user.Since this is no my_m, the table cannot be dropped.

You cannot run DROP TABLE my_user because mysqld looks for the my_m first.The data dictionary still has an entry for that table. Somehow, you lost the my_m and my_user.ibd files.Constraint:ĬONSTRAINT "FK_CFBD431E285FAC6D" FOREIGN KEY ("group_id") REFERENCES "my_group" ("id") Table do not match the ones in the referenced table The columns as the first columns, or the data types in the There is no index in the table which would contain Thanks.Įxtra info: mysql> SHOW engine innodb STATUS ġ40703 15:15:09 Error in foreign key constraint of table my_db/my_user Also, restarted the mysql service, but no good.Īny ideas? Google has failed me so far. Tried # mysqladmin flush-tables and repeated the steps above but it wasn't helpful. Mysql> ERROR 1005 (HY000): Can't create table 'my_db.my_user' (errno: -1)

Mysql> CREATE TABLE my_user (id INT AUTO_INCREMENT NOT NULL, username VARCHAR(255), group_id VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB Mysql> ERROR 1051 (42S02): Unknown table 'my_user' I'm using these steps to create a table my_user, that already existed but somehow vanished from my database my_db: mysql> USE my_db