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 <peter.braw...@earthlink.net> 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 <peter.braw...@earthlink.net> 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 <peter.braw...@earthlink.net> 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; >>>>>> +--------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ >>>>>> | Type | Name | Status >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> | >>>>>> +--------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ >>>>>> | InnoDB | | >>>>>> ===================================== >>>>>> 2017-04-24 18:38:55 0x8696fc400 INNODB MONITOR OUTPUT >>>>>> ===================================== >>>>>> Per second averages calculated from the last 25 seconds >>>>>> ----------------- >>>>>> BACKGROUND THREAD >>>>>> ----------------- >>>>>> srv_master_thread loops: 48 srv_active, 0 srv_shutdown, 78459 >>>>>> srv_idle >>>>>> srv_master_thread log flush and writes: 78507 >>>>>> ---------- >>>>>> SEMAPHORES >>>>>> ---------- >>>>>> OS WAIT ARRAY INFO: reservation count 104 >>>>>> OS WAIT ARRAY INFO: signal count 103 >>>>>> RW-shared spins 0, rounds 170, OS waits 85 >>>>>> RW-excl spins 0, rounds 0, OS waits 0 >>>>>> RW-sx spins 0, rounds 0, OS waits 0 >>>>>> Spin rounds per wait: 170.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx >>>>>> ------------------------ >>>>>> LATEST FOREIGN KEY ERROR >>>>>> ------------------------ >>>>>> 2017-04-24 18:38:35 0x8696fc400 Error in foreign key constraint of >>>>>> table mail/lastauth: >>>>>> FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE CASCADE >>>>>> ) ENGINE=InnoDB DEFAULT CHARSET=utf8: >>>>>> Cannot find an index in the referenced table where the >>>>>> referenced columns appear as the first columns, or column types >>>>>> in the table and the referenced table do not match for constraint. >>>>> But it finds the required index on versions 5.6 and 5.7 here. What's >>>>> your MySQL version? Did you try recreating the virtual_users table? >>>>> >>>>> 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
