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 <[email protected]> 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 <[email protected]> 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