Web lists-archives.com

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 <peter.brawley@xxxxxxxxxxxxx> 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.brawley@xxxxxxxxxxxxx> 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.brawley@xxxxxxxxxxxxx> 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