hwilmer
2019-Apr-24 11:55 UTC
[CentOS] mariadb: How to delete foreign key constraint from non-existing table?
Hi, somehow phpmyadmin messed things up when I was trying to modify a table. The table disappeared, and now it's impossible to re-create it: MariaDB [time]> create table etikettend_metainfo (userID integer(6) unsigned, stationsnummer integer(4) unsigned, primary key (userID)); ERROR 1005 (HY000): Can't create table `time`.`etikettend_metainfo` (errno: 150 "Foreign key constraint is incorrectly formed") MariaDB [time]> show tables like 'etikettend%'; +------------------------------+ | Tables_in_time (etikettend%) | +------------------------------+ | etikettend_etikettentypen | | etikettend_stationen | +------------------------------+ 2 rows in set (0.001 sec) Since the table has vanished, I'm finding myself unable to remove the key constraints, and trying to disable them was also unsuccessful. It is not necessary to recover the vanished table because it had just been created and was still empty anyway. But how do I fix this?
Tony Mountifield
2019-Apr-24 17:04 UTC
[CentOS] mariadb: How to delete foreign key constraint from non-existing table?
In article <f7f6e8a9-5425-78f6-f49b-988ab125b7f6 at gc-24.de>, hwilmer <hw at gc-24.de> wrote:> > Hi, > > somehow phpmyadmin messed things up when I was trying to modify a table. > The table disappeared, and now it's impossible to re-create it: > > > MariaDB [time]> create table etikettend_metainfo (userID integer(6) > unsigned, stationsnummer integer(4) unsigned, primary key (userID)); > ERROR 1005 (HY000): Can't create table `time`.`etikettend_metainfo` > (errno: 150 "Foreign key constraint is incorrectly formed") > MariaDB [time]> show tables like 'etikettend%'; > +------------------------------+ > | Tables_in_time (etikettend%) | > +------------------------------+ > | etikettend_etikettentypen | > | etikettend_stationen | > +------------------------------+ > 2 rows in set (0.001 sec) > > > Since the table has vanished, I'm finding myself unable to remove the > key constraints, and trying to disable them was also unsuccessful. > > It is not necessary to recover the vanished table because it had just > been created and was still empty anyway. > > But how do I fix this?Have a look at the troubleshooting information at: https://mariadb.com/kb/en/library/innodb-troubleshooting-overview/ You might also find useful information in the MySQL documentation at: https://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html Cheers Tony -- Tony Mountifield Work: tony at softins.co.uk - http://www.softins.co.uk Play: tony at mountifield.org - http://tony.mountifield.org
hwilmer
2019-Apr-27 15:51 UTC
[CentOS] mariadb: How to delete foreign key constraint from non-existing table?
On 4/24/19 7:04 PM, Tony Mountifield wrote:> In article <f7f6e8a9-5425-78f6-f49b-988ab125b7f6 at gc-24.de>, > hwilmer <hw at gc-24.de> wrote: >> >> Hi, >> >> somehow phpmyadmin messed things up when I was trying to modify a table. >> The table disappeared, and now it's impossible to re-create it: >> >> >> MariaDB [time]> create table etikettend_metainfo (userID integer(6) >> unsigned, stationsnummer integer(4) unsigned, primary key (userID)); >> ERROR 1005 (HY000): Can't create table `time`.`etikettend_metainfo` >> (errno: 150 "Foreign key constraint is incorrectly formed") >> MariaDB [time]> show tables like 'etikettend%'; >> +------------------------------+ >> | Tables_in_time (etikettend%) | >> +------------------------------+ >> | etikettend_etikettentypen | >> | etikettend_stationen | >> +------------------------------+ >> 2 rows in set (0.001 sec) >> >> >> Since the table has vanished, I'm finding myself unable to remove the >> key constraints, and trying to disable them was also unsuccessful. >> >> It is not necessary to recover the vanished table because it had just >> been created and was still empty anyway. >> >> But how do I fix this? > > Have a look at the troubleshooting information at: > https://mariadb.com/kb/en/library/innodb-troubleshooting-overview/ > > You might also find useful information in the MySQL documentation at: > https://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.htmlThanks! None of all this mentions vanished tables still having constraints on them or how to drop contraints referring to tables that do not exist.