Cannot create table mysql(mariadb). Table is corrupted

Question

Some how, my database has gotten into a bad state. I previously had a table named live_stream. When I tried to drop a foreign key constraint, I got an error that mariadb could not rename #sql-26_e7a to live_stream. Now when I try to run the following statement, I get this error.

Can't create table live_stream (errno: 150 "Foreign key constraint is incorrectly formed")

CREATE TABLE live_stream (idbigint(20) NOT NULL PRIMARY KEY);

As you can see I don't have any foreign key constraints in the definition. If I try the exact same definition with a different table name, it works. If I try to drop the table, mariadb complains that live_stream doesn't exist. Its like the table or foreign key are stuck in a transaction or something like that.

I am using galara with maria db 10.3.

UPDATE

I believe the problem was introduced when a foreign key and unique index were given the same name. I recreated the scenario, and when I try to drop the index, mariadb prevents it.

* UPDATE 2 * Here is the output of SHOW ENGINE INNODB STATUS;

* UPDATE3 *

Here are the steps to reproduce.

create table tb1 ( id bigint null, constraint tb1_pk primary key (id) );

create table tb2 ( id bigint null, tb1_id bigint null, constraint tb2_pk primary key (id), constraint tb2_tb1_id_fk foreign key (tb1_id) references tb1 (id) );

ALTER TABLE tb2 ADD CONSTRAINT tb2_tb1_id_fk UNIQUE (tb1_id, tb1_id);

drop index tb2_tb1_id_fk on tb2;

The problem is that the unique constraint has the same name as the foreign key and references the same column twice.

CREATE OR REPLACE TABLE live_stream (id BIGINT NOT NULL PRIMARY KEY); — Satya, Oct 11 '19 at 03:59
can you try this command on command prompt to see the error: mysqlcheck -uroot -p — Satya, Oct 11 '19 at 04:48
Have you [tried to get more info about error](https://stackoverflow.com/a/18180299/5444623)? ie `SHOW ENGINE INNODB STATUS;` — PeterM, Oct 11 '19 at 04:53
Isn't it using [InnoDB](https://mariadb.com/kb/en/library/what-is-mariadb-galera-cluster/) as a storage engine? or XtraDB, which is fork of InnoDB. — PeterM, Oct 11 '19 at 06:28
@PeterM I have updated the description wit the output of SHOW ENGINE INNODB STATUS; — tanner burton, Oct 11 '19 at 14:00
@Satya here is the output when I check the table with `CHECK TABLE live_stream`; Table 'live_stream' doesn't exist — tanner burton, Oct 11 '19 at 14:03
Have you checked inside the 'MariaDB/data/dbname' folder if there's any file with `live_stream.*` name? Probably something like `live_stream.ibd` etc.? — FanoFN, Oct 12 '19 at 08:59
@tcadidot0 No there isn't. But I did find these files. ` -rw-rw---- 1 999 docker 45133 Oct 11 02:58 '#sql2-25-2.frm' -rw-rw---- 1 999 docker 98304 Oct 11 02:58 '#sql2-25-2.ibd'` — tanner burton, Oct 12 '19 at 17:45
What version? I get `ERROR 1060 (42S21): Duplicate column name 'tb1_id'` after the ALTER. — Rick James, Oct 17 '19 at 05:07

Cannot create table mysql(mariadb). Table is corrupted

0 Answers0