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 <peter.braw...@earthlink.net> 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
