Re: Can not add foreign key constraint
Hello, My thanks to everyone who helped on this issue. The index did it. Thanks. Dave. On 4/28/17, Johan De Meersman wrote: > That is quite different, as I suspected :-) > > Referential keys require an index on the target table that begins with the > referenced field, so you'll need to add one on user, as was specified in the > create table you originally posted. > > On 28 April 2017 01:21:39 CEST, David Mehler wrote: >>Hello, >> >> Here's the output of the command show create table virtual_users: >> >> create table virtual_users; >>+---+-+ >> | Table | Create Table >> >> >> >> >> >> >> | >>+---+-+ >> | virtual_users | CREATE TABLE `virtual_users` ( >> `id` int(11) NOT NULL AUTO_INCREMENT, >> `domain_id` int(11) NOT NULL, >> `user` varchar(40) NOT NULL, >> `password` varchar(128) NOT NULL, >> `quota` bigint(20) NOT NULL DEFAULT '0', >> `quota_messages` int(11) NOT NULL DEFAULT '0', >> PRIMARY KEY (`id`), >> UNIQUE KEY `UNIQUE_EMAIL` (`domain_id`,`user`), >> CONSTRAINT `virtual_users_ibfk_1` FOREIGN KEY (`domain_id`) >> REFERENCES `virtual_domains` (`id`) ON DELETE CASCADE >> ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 | >>+---+-+ >> 1 row in set (0.00 sec) >> >>Thanks. >>Dave. >> >>> >>> >>> On 4/26/17, Johan De Meersman wrote: >>>> >>>> I note that the innodb status says it couldn't find an index on the >>>> referenced column. Did the create statements come from your create >>>> scripts >>>> or from a show create table statement? I'm suspicious about the >>index on >>>> virtual_users(user). >>>> >>>> >>>> - Original Message - >>>>> From: "David Mehler" >>>>> To: "MySql" >>>>> Sent: Tuesday, 25 April, 2017 23:07:19 >>>>> Subject: Re: Can not add foreign key constraint >>>> >>>>> Hello, >>>>> >>>>> Tried recreating the virtual_users table didn't solve anything. >>Would >>>>> it be possible for anyone to check out my box directly? >>>>> >>>>> Thanks. >>>>> Dave. >>>> >>>> -- >>>> The bay-trees in our country are all wither'd >>>> And meteors fright the fixed stars of heaven; >>>> The pale-faced moon looks bloody on the earth >>>> And lean-look'd prophets whisper fearful change. >>>> These signs forerun the death or fall of kings. >>>> -- Wm. Shakespeare, "Richard II" >>>> >>> >>> Thanks. >>> Dave. >>> >> >>-- >>MySQL General Mailing List >>For list archives: http://lists.mysql.com/mysql >>To unsubscribe:http://lists.mysql.com/mysql > > -- > Sent from my Android device with K-9 Mail. Please excuse my brevity. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Can not add foreign key constraint
That is quite different, as I suspected :-) Referential keys require an index on the target table that begins with the referenced field, so you'll need to add one on user, as was specified in the create table you originally posted. On 28 April 2017 01:21:39 CEST, David Mehler wrote: >Hello, > > Here's the output of the command show create table virtual_users: > > create table virtual_users; >+---+-+ > | Table | Create Table > > > > > > > | >+---+-+ > | virtual_users | CREATE TABLE `virtual_users` ( > `id` int(11) NOT NULL AUTO_INCREMENT, > `domain_id` int(11) NOT NULL, > `user` varchar(40) NOT NULL, > `password` varchar(128) NOT NULL, > `quota` bigint(20) NOT NULL DEFAULT '0', > `quota_messages` int(11) NOT NULL DEFAULT '0', > PRIMARY KEY (`id`), > UNIQUE KEY `UNIQUE_EMAIL` (`domain_id`,`user`), > CONSTRAINT `virtual_users_ibfk_1` FOREIGN KEY (`domain_id`) > REFERENCES `virtual_domains` (`id`) ON DELETE CASCADE > ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 | >+---+-+ > 1 row in set (0.00 sec) > >Thanks. >Dave. > >> >> >> On 4/26/17, Johan De Meersman wrote: >>> >>> I note that the innodb status says it couldn't find an index on the >>> referenced column. Did the create statements come from your create >>> scripts >>> or from a show create table statement? I'm suspicious about the >index on >>> virtual_users(user). >>> >>> >>> - Original Message - >>>> From: "David Mehler" >>>> To: "MySql" >>>> Sent: Tuesday, 25 April, 2017 23:07:19 >>>> Subject: Re: Can not add foreign key constraint >>> >>>> Hello, >>>> >>>> Tried recreating the virtual_users table didn't solve anything. >Would >>>> it be possible for anyone to check out my box directly? >>>> >>>> Thanks. >>>> Dave. >>> >>> -- >>> The bay-trees in our country are all wither'd >>> And meteors fright the fixed stars of heaven; >>> The pale-faced moon looks bloody on the earth >>> And lean-look'd prophets whisper fearful change. >>> These signs forerun the death or fall of kings. >>> -- Wm. Shakespeare, "Richard II" >>> >> >> Thanks. >> Dave. >> > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe:http://lists.mysql.com/mysql -- Sent from my Android device with K-9 Mail. Please excuse my brevity. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Can not add foreign key constraint
Hello, Here's the output of the command show create table virtual_users: create table virtual_users; +---+-+ | Table | Create Table | +---+-+ | virtual_users | CREATE TABLE `virtual_users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `domain_id` int(11) NOT NULL, `user` varchar(40) NOT NULL, `password` varchar(128) NOT NULL, `quota` bigint(20) NOT NULL DEFAULT '0', `quota_messages` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `UNIQUE_EMAIL` (`domain_id`,`user`), CONSTRAINT `virtual_users_ibfk_1` FOREIGN KEY (`domain_id`) REFERENCES `virtual_domains` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 | +---+-+ 1 row in set (0.00 sec) Thanks. Dave. > > > On 4/26/17, Johan De Meersman wrote: >> >> I note that the innodb status says it couldn't find an index on the >> referenced column. Did the create statements come from your create >> scripts >> or from a show create table statement? I'm suspicious about the index on >> virtual_users(user). >> >> >> - Original Message - >>> From: "David Mehler" >>> To: "MySql" >>> Sent: Tuesday, 25 April, 2017 23:07:19 >>> Subject: Re: Can not add foreign key constraint >> >>> Hello, >>> >>> Tried recreating the virtual_users table didn't solve anything. Would >>> it be possible for anyone to check out my box directly? >>> >>> Thanks. >>> Dave. >> >> -- >> The bay-trees in our country are all wither'd >> And meteors fright the fixed stars of heaven; >> The pale-faced moon looks bloody on the earth >> And lean-look'd prophets whisper fearful change. >> These signs forerun the death or fall of kings. >> -- Wm. Shakespeare, "Richard II" >> > > Thanks. > Dave. > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Can not add foreign key constraint
Hello, Tried recreating the virtual_users table didn't solve anything. Would it be possible for anyone to check out my box directly? Thanks. Dave. On 4/24/17, Peter Brawley wrote: > On 4/24/2017 20:47, David Mehler wrote: >> Hello, >> >> Thanks. Here's the create statements for virtual_domains, >> virtual_users, and the one that isn't working lastauth: >> >> CREATE TABLE `virtual_domains` ( >>`id` int(11) NOT NULL auto_increment, >>`name` varchar(50) NOT NULL, >>PRIMARY KEY (`id`) >> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; >> >> CREATE TABLE `virtual_users` ( >>`id` int(11) NOT NULL auto_increment, >>`domain_id` int(11) NOT NULL, >>`user` varchar(40) NOT NULL, >>`password` varchar(128) NOT NULL, >>`quota` bigint(20) NOT NULL DEFAULT 256, >>`quota_messages` int(11) NOT NULL DEFAULT 0, >>PRIMARY KEY (`id`), >>UNIQUE KEY `user` (`user`), >>FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE >> CASCADE >> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; >> >> CREATE TABLE IF NOT EXISTS `lastauth` ( >> `user` varchar(40) NOT NULL, >> `remote_ip` varchar(18) NOT NULL, >> `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE >> CURRENT_TIMESTAMP, >> PRIMARY KEY (`user`), >> FOREIGN KEY (`user`) REFERENCES virtual_users(`user`) ON DELETE CASCADE >> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; > > Again, that works in 5.6 and 5.7, so if it doesn't work for you, there > is something wrong in one of your files. Did you try recreating > virtual_users? > > PB > > - > >> >> Thanks. >> Dave. >> >> >> On 4/24/17, Peter Brawley wrote: >>> On 4/24/2017 18:16, David Mehler wrote: Hello, I'm running Mysql 5.7.18. My virtual_users are working fine, it's the new table that isn't. Or am I missing something? >>> MySQL says it cannot find an index which the table's create statement >>> declares. If that's the create statement that created the table, the >>> table's corrupted. >>> >>> PB >>> >>> Thanks. Dave. On 4/24/17, Peter Brawley wrote: > On 4/24/2017 17:41, David Mehler wrote: >> Hello, >> >> Here's the output. I hope it helps. >> >> root@localhost [mail]> CREATE TABLE IF NOT EXISTS `lastauth` ( >>-> `user` varchar(40) NOT NULL, >>-> `remote_ip` varchar(18) NOT NULL, >>-> `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON >> UPDATE CURRENT_TIMESTAMP, >>-> PRIMARY KEY (`user`), >>-> FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE >> CASCADE >>-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; >> ERROR 1215 (HY000): Cannot add foreign key constraint >> root@localhost [mail]> show engine innodb status; >> ++--+--
Re: Can not add foreign key constraint
On 4/24/2017 20:47, David Mehler wrote: Hello, Thanks. Here's the create statements for virtual_domains, virtual_users, and the one that isn't working lastauth: CREATE TABLE `virtual_domains` ( `id` int(11) NOT NULL auto_increment, `name` varchar(50) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `virtual_users` ( `id` int(11) NOT NULL auto_increment, `domain_id` int(11) NOT NULL, `user` varchar(40) NOT NULL, `password` varchar(128) NOT NULL, `quota` bigint(20) NOT NULL DEFAULT 256, `quota_messages` int(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`), UNIQUE KEY `user` (`user`), FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `lastauth` ( `user` varchar(40) NOT NULL, `remote_ip` varchar(18) NOT NULL, `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`user`), FOREIGN KEY (`user`) REFERENCES virtual_users(`user`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Again, that works in 5.6 and 5.7, so if it doesn't work for you, there is something wrong in one of your files. Did you try recreating virtual_users? PB - Thanks. Dave. On 4/24/17, Peter Brawley wrote: On 4/24/2017 18:16, David Mehler wrote: Hello, I'm running Mysql 5.7.18. My virtual_users are working fine, it's the new table that isn't. Or am I missing something? MySQL says it cannot find an index which the table's create statement declares. If that's the create statement that created the table, the table's corrupted. PB Thanks. Dave. On 4/24/17, Peter Brawley wrote: On 4/24/2017 17:41, David Mehler wrote: Hello, Here's the output. I hope it helps. root@localhost [mail]> CREATE TABLE IF NOT EXISTS `lastauth` ( -> `user` varchar(40) NOT NULL, -> `remote_ip` varchar(18) NOT NULL, -> `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -> PRIMARY KEY (`user`), -> FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE CASCADE -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ERROR 1215 (HY000): Cannot add foreign key constraint root@localhost [mail]> show engine innodb status; ++--+--
Re: Can not add foreign key constraint
Hello, Thanks. Here's the create statements for virtual_domains, virtual_users, and the one that isn't working lastauth: CREATE TABLE `virtual_domains` ( `id` int(11) NOT NULL auto_increment, `name` varchar(50) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `virtual_users` ( `id` int(11) NOT NULL auto_increment, `domain_id` int(11) NOT NULL, `user` varchar(40) NOT NULL, `password` varchar(128) NOT NULL, `quota` bigint(20) NOT NULL DEFAULT 256, `quota_messages` int(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`), UNIQUE KEY `user` (`user`), FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `lastauth` ( `user` varchar(40) NOT NULL, `remote_ip` varchar(18) NOT NULL, `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`user`), FOREIGN KEY (`user`) REFERENCES virtual_users(`user`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Thanks. Dave. On 4/24/17, Peter Brawley wrote: > On 4/24/2017 18:16, David Mehler wrote: >> Hello, >> >> I'm running Mysql 5.7.18. >> >> My virtual_users are working fine, it's the new table that isn't. Or >> am I missing something? > > MySQL says it cannot find an index which the table's create statement > declares. If that's the create statement that created the table, the > table's corrupted. > > PB > > >> >> Thanks. >> Dave. >> >> >> On 4/24/17, Peter Brawley wrote: >>> On 4/24/2017 17:41, David Mehler wrote: Hello, Here's the output. I hope it helps. root@localhost [mail]> CREATE TABLE IF NOT EXISTS `lastauth` ( -> `user` varchar(40) NOT NULL, -> `remote_ip` varchar(18) NOT NULL, -> `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -> PRIMARY KEY (`user`), -> FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE CASCADE -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ERROR 1215 (HY000): Cannot add foreign key constraint root@localhost [mail]> show engine innodb status; ++--+--
Re: Can not add foreign key constraint
Hello, I'm running Mysql 5.7.18. My virtual_users are working fine, it's the new table that isn't. Or am I missing something? Thanks. Dave. On 4/24/17, Peter Brawley wrote: > On 4/24/2017 17:41, David Mehler wrote: >> Hello, >> >> Here's the output. I hope it helps. >> >> root@localhost [mail]> CREATE TABLE IF NOT EXISTS `lastauth` ( >> -> `user` varchar(40) NOT NULL, >> -> `remote_ip` varchar(18) NOT NULL, >> -> `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON >> UPDATE CURRENT_TIMESTAMP, >> -> PRIMARY KEY (`user`), >> -> FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE >> CASCADE >> -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; >> ERROR 1215 (HY000): Cannot add foreign key constraint >> root@localhost [mail]> show engine innodb status; >> ++--+--
Re: Can not add foreign key constraint
Hello, Here's the output. I hope it helps. root@localhost [mail]> CREATE TABLE IF NOT EXISTS `lastauth` ( -> `user` varchar(40) NOT NULL, -> `remote_ip` varchar(18) NOT NULL, -> `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -> PRIMARY KEY (`user`), -> FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE CASCADE -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ERROR 1215 (HY000): Cannot add foreign key constraint root@localhost [mail]> show engine innodb status; ++--+---
Re: Can not add foreign key constraint
On 4/24/2017 15:28, Peter Brawley wrote: On 4/24/2017 13:59, David Mehler wrote: Hello, root@localhost [mail]> show engine innodb_status; ERROR 1286 (42000): Unknown storage engine 'innodb_status' Well it's very unlikely InnoDB made that up, it's probably in one of your Create texts. Ah, an error cascade, as Shawn Green noticed, there's a typo in my suggested command, should be ... show engine innodb status; PB - PB - This is on a Mysql 5.7 setup. Thanks. Dave. On 4/24/17, Peter Brawley wrote: On 4/24/2017 12:28, David Mehler wrote: Hello, Here's the create table sand error message. root@localhost [(none)]> use mail; Database changed root@localhost [mail]> CREATE TABLE IF NOT EXISTS `lastauth` ( -> `user` varchar(40) NOT NULL, -> `remote_ip` varchar(18) NOT NULL, -> `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -> PRIMARY KEY (`user`), -> FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE CASCADE -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ERROR 1215 (HY000): Cannot add foreign key constraint For the table it's referencing here it is: CREATE TABLE `virtual_users` ( `id` int(11) NOT NULL auto_increment, `domain_id` int(11) NOT NULL, `user` varchar(40) NOT NULL, `password` varchar(32) NOT NULL, `quota` bigint(20) NOT NULL DEFAULT 256, `quota_messages` int(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`), UNIQUE KEY `user` (`user`), FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Hope this helps. Adding in a dummy Create Table for the missing referenced `virtual_domains`, we have ... drop table if exists lastauth, virtual_users, virtual_domains; CREATE TABLE virtual_domains ( id int PRIMARY KEY ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `virtual_users` ( `id` int(11) NOT NULL auto_increment, `domain_id` int(11) NOT NULL, `user` varchar(40) NOT NULL, `password` varchar(32) NOT NULL, `quota` bigint(20) NOT NULL DEFAULT 256, `quota_messages` int(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`), UNIQUE KEY (`user`), FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `lastauth` ( `user` varchar(40) NOT NULL, `remote_ip` varchar(18) NOT NULL, `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`user`), FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; which executes without error. To find out what's going awry in your setup, right after the error occurs execute ... show engine innodb_status; and search the result for LATEST FOREIGN KEY ERROR. PB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Can not add foreign key constraint
On 4/24/2017 2:10 PM, Peter Brawley wrote: On 4/24/2017 12:28, David Mehler wrote: ...snip Adding in a dummy Create Table for the missing referenced `virtual_domains`, we have ... drop table if exists lastauth, virtual_users, virtual_domains; CREATE TABLE virtual_domains ( id int PRIMARY KEY ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `virtual_users` ( `id` int(11) NOT NULL auto_increment, `domain_id` int(11) NOT NULL, `user` varchar(40) NOT NULL, `password` varchar(32) NOT NULL, `quota` bigint(20) NOT NULL DEFAULT 256, `quota_messages` int(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`), UNIQUE KEY (`user`), FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `lastauth` ( `user` varchar(40) NOT NULL, `remote_ip` varchar(18) NOT NULL, `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`user`), FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; which executes without error. To find out what's going awry in your setup, right after the error occurs execute ... show engine innodb_status; and search the result for LATEST FOREIGN KEY ERROR. PB Typo warning: "innodb status" is two words (no underscore) https://dev.mysql.com/doc/refman/5.6/en/show-engine.html -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Integrated Cloud Applications & Platform Services Office: Blountville, TN Become certified in MySQL! Visit https://www.mysql.com/certification/ for details. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Can not add foreign key constraint
Hello, Here's the engines I have: root@localhost [(none)]> show engines; ++-++--+--++ | Engine | Support | Comment | Transactions | XA | Savepoints | ++-++--+--++ | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES| | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | CSV| YES | CSV storage engine | NO | NO | NO | | ARCHIVE| YES | Archive storage engine | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | ++-++--+--++ 9 rows in set (0.00 sec) Not sure why I'm getting the error 1286. Thanks. Dave. On 4/24/17, Peter Brawley wrote: > On 4/24/2017 13:59, David Mehler wrote: >> Hello, >> >> root@localhost [mail]> show engine innodb_status; >> ERROR 1286 (42000): Unknown storage engine 'innodb_status' > > Well it's very unlikely InnoDB made that up, it's probably in one of > your Create texts. > > PB > > - > >> >> >> This is on a Mysql 5.7 setup. >> >> Thanks. >> Dave. >> >> >> >> On 4/24/17, Peter Brawley wrote: >>> On 4/24/2017 12:28, David Mehler wrote: Hello, Here's the create table sand error message. root@localhost [(none)]> use mail; Database changed root@localhost [mail]> CREATE TABLE IF NOT EXISTS `lastauth` ( -> `user` varchar(40) NOT NULL, -> `remote_ip` varchar(18) NOT NULL, -> `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -> PRIMARY KEY (`user`), -> FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE CASCADE -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ERROR 1215 (HY000): Cannot add foreign key constraint For the table it's referencing here it is: CREATE TABLE `virtual_users` ( `id` int(11) NOT NULL auto_increment, `domain_id` int(11) NOT NULL, `user` varchar(40) NOT NULL, `password` varchar(32) NOT NULL, `quota` bigint(20) NOT NULL DEFAULT 256, `quota_messages` int(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`), UNIQUE KEY `user` (`user`), FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Hope this helps. >>> Adding in a dummy Create Table for the missing referenced >>> `virtual_domains`, we have ... >>> >>> drop table if exists lastauth, virtual_users, virtual_domains; >>> >>> CREATE TABLE virtual_domains ( >>> >>> id int PRIMARY KEY >>> >>> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; >>> >>> CREATE TABLE `virtual_users` ( >>> >>> `id` int(11) NOT NULL auto_increment, >>> >>> `domain_id` int(11) NOT NULL, >>> >>> `user` varchar(40) NOT NULL, >>> >>> `password` varchar(32) NOT NULL, >>> >>> `quota` bigint(20) NOT NULL DEFAULT 256, >>> >>> `quota_messages` int(11) NOT NULL DEFAULT 0, >>> >>> PRIMARY KEY (`id`), >>> >>> UNIQUE KEY (`user`), >>> >>> FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE >>> CASCADE >>> >>> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; >>> >>> CREATE TABLE IF NOT EXISTS `lastauth` ( >>> >>> `user` varchar(40) NOT NULL, >>> >>> `remote_ip` varchar(18) NOT NULL, >>> >>> `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE >>> CURRENT_TIMESTAMP, >>> >>> PRIMARY KEY (`user`), >>> >>> FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE CASCADE >>> >>> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; >>> >>> >>> which executes without error. To find out what's going awry in your >>> setup, right after the error occurs execute ... >>> >>> show engine innodb_status; >>> >>> >>> and search the result for LATEST FOREIGN KEY ERROR. >>> >>> PB >>> >>>
Re: Can not add foreign key constraint
On 4/24/2017 13:59, David Mehler wrote: Hello, root@localhost [mail]> show engine innodb_status; ERROR 1286 (42000): Unknown storage engine 'innodb_status' Well it's very unlikely InnoDB made that up, it's probably in one of your Create texts. PB - This is on a Mysql 5.7 setup. Thanks. Dave. On 4/24/17, Peter Brawley wrote: On 4/24/2017 12:28, David Mehler wrote: Hello, Here's the create table sand error message. root@localhost [(none)]> use mail; Database changed root@localhost [mail]> CREATE TABLE IF NOT EXISTS `lastauth` ( -> `user` varchar(40) NOT NULL, -> `remote_ip` varchar(18) NOT NULL, -> `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -> PRIMARY KEY (`user`), -> FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE CASCADE -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ERROR 1215 (HY000): Cannot add foreign key constraint For the table it's referencing here it is: CREATE TABLE `virtual_users` ( `id` int(11) NOT NULL auto_increment, `domain_id` int(11) NOT NULL, `user` varchar(40) NOT NULL, `password` varchar(32) NOT NULL, `quota` bigint(20) NOT NULL DEFAULT 256, `quota_messages` int(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`), UNIQUE KEY `user` (`user`), FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Hope this helps. Adding in a dummy Create Table for the missing referenced `virtual_domains`, we have ... drop table if exists lastauth, virtual_users, virtual_domains; CREATE TABLE virtual_domains ( id int PRIMARY KEY ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `virtual_users` ( `id` int(11) NOT NULL auto_increment, `domain_id` int(11) NOT NULL, `user` varchar(40) NOT NULL, `password` varchar(32) NOT NULL, `quota` bigint(20) NOT NULL DEFAULT 256, `quota_messages` int(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`), UNIQUE KEY (`user`), FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `lastauth` ( `user` varchar(40) NOT NULL, `remote_ip` varchar(18) NOT NULL, `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`user`), FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; which executes without error. To find out what's going awry in your setup, right after the error occurs execute ... show engine innodb_status; and search the result for LATEST FOREIGN KEY ERROR. PB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Can not add foreign key constraint
Hello, root@localhost [mail]> show engine innodb_status; ERROR 1286 (42000): Unknown storage engine 'innodb_status' This is on a Mysql 5.7 setup. Thanks. Dave. On 4/24/17, Peter Brawley wrote: > On 4/24/2017 12:28, David Mehler wrote: >> Hello, >> >>Here's the create table sand error message. >> >>root@localhost [(none)]> use mail; >>Database changed >>root@localhost [mail]> CREATE TABLE IF NOT EXISTS `lastauth` ( >>-> `user` varchar(40) NOT NULL, >>-> `remote_ip` varchar(18) NOT NULL, >>-> `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON >>UPDATE CURRENT_TIMESTAMP, >>-> PRIMARY KEY (`user`), >>-> FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE >> CASCADE >>-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; >>ERROR 1215 (HY000): Cannot add foreign key constraint >> >> >>For the table it's referencing here it is: >> >>CREATE TABLE `virtual_users` ( >> `id` int(11) NOT NULL auto_increment, >> `domain_id` int(11) NOT NULL, >> `user` varchar(40) NOT NULL, >> `password` varchar(32) NOT NULL, >> `quota` bigint(20) NOT NULL DEFAULT 256, >> `quota_messages` int(11) NOT NULL DEFAULT 0, >> PRIMARY KEY (`id`), >> UNIQUE KEY `user` (`user`), >> FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE >> CASCADE >>) ENGINE=InnoDB DEFAULT CHARSET=utf8; >> >> Hope this helps. > > Adding in a dummy Create Table for the missing referenced > `virtual_domains`, we have ... > > drop table if exists lastauth, virtual_users, virtual_domains; > > CREATE TABLE virtual_domains ( > >id int PRIMARY KEY > > ) ENGINE=InnoDB DEFAULT CHARSET=utf8; > > CREATE TABLE `virtual_users` ( > >`id` int(11) NOT NULL auto_increment, > >`domain_id` int(11) NOT NULL, > >`user` varchar(40) NOT NULL, > >`password` varchar(32) NOT NULL, > >`quota` bigint(20) NOT NULL DEFAULT 256, > >`quota_messages` int(11) NOT NULL DEFAULT 0, > >PRIMARY KEY (`id`), > >UNIQUE KEY (`user`), > >FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE > > ) ENGINE=InnoDB DEFAULT CHARSET=utf8; > > CREATE TABLE IF NOT EXISTS `lastauth` ( > >`user` varchar(40) NOT NULL, > >`remote_ip` varchar(18) NOT NULL, > >`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE > CURRENT_TIMESTAMP, > >PRIMARY KEY (`user`), > >FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE CASCADE > > ) ENGINE=InnoDB DEFAULT CHARSET=utf8; > > > which executes without error. To find out what's going awry in your > setup, right after the error occurs execute ... > > show engine innodb_status; > > > and search the result for LATEST FOREIGN KEY ERROR. > > PB > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Can not add foreign key constraint
On 4/24/2017 12:28, David Mehler wrote: Hello, Here's the create table sand error message. root@localhost [(none)]> use mail; Database changed root@localhost [mail]> CREATE TABLE IF NOT EXISTS `lastauth` ( -> `user` varchar(40) NOT NULL, -> `remote_ip` varchar(18) NOT NULL, -> `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -> PRIMARY KEY (`user`), -> FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE CASCADE -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ERROR 1215 (HY000): Cannot add foreign key constraint For the table it's referencing here it is: CREATE TABLE `virtual_users` ( `id` int(11) NOT NULL auto_increment, `domain_id` int(11) NOT NULL, `user` varchar(40) NOT NULL, `password` varchar(32) NOT NULL, `quota` bigint(20) NOT NULL DEFAULT 256, `quota_messages` int(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`), UNIQUE KEY `user` (`user`), FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Hope this helps. Adding in a dummy Create Table for the missing referenced `virtual_domains`, we have ... drop table if exists lastauth, virtual_users, virtual_domains; CREATE TABLE virtual_domains ( id int PRIMARY KEY ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `virtual_users` ( `id` int(11) NOT NULL auto_increment, `domain_id` int(11) NOT NULL, `user` varchar(40) NOT NULL, `password` varchar(32) NOT NULL, `quota` bigint(20) NOT NULL DEFAULT 256, `quota_messages` int(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`), UNIQUE KEY (`user`), FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `lastauth` ( `user` varchar(40) NOT NULL, `remote_ip` varchar(18) NOT NULL, `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`user`), FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; which executes without error. To find out what's going awry in your setup, right after the error occurs execute ... show engine innodb_status; and search the result for LATEST FOREIGN KEY ERROR. PB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Can not add foreign key constraint
Hello, Here's the create table sand error message. root@localhost [(none)]> use mail; Database changed root@localhost [mail]> CREATE TABLE IF NOT EXISTS `lastauth` ( -> `user` varchar(40) NOT NULL, -> `remote_ip` varchar(18) NOT NULL, -> `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -> PRIMARY KEY (`user`), -> FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE CASCADE -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ERROR 1215 (HY000): Cannot add foreign key constraint For the table it's referencing here it is: CREATE TABLE `virtual_users` ( `id` int(11) NOT NULL auto_increment, `domain_id` int(11) NOT NULL, `user` varchar(40) NOT NULL, `password` varchar(32) NOT NULL, `quota` bigint(20) NOT NULL DEFAULT 256, `quota_messages` int(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`), UNIQUE KEY `user` (`user`), FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Hope this helps. Thanks. Dave. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
