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 <[email protected]> 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 <[email protected]> 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
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
