Re: Can not add foreign key constraint

2017-04-28 Thread David Mehler 
Hello,

My thanks to everyone who helped on this issue. The index did it.

Thanks.
Dave.


On 4/28/17, Johan De Meersman <vegiv...@tuxera.be> wrote:
> That is quite different, as I suspected :-)
>
> Referential keys require an index on the target table that begins with the
> referenced field, so you'll need to add one on user, as was specified in the
> create table you originally posted.
>
> On 28 April 2017 01:21:39 CEST, David Mehler <dave.meh...@gmail.com> wrote:
>>Hello,
>>
>> Here's the output of the command show create table virtual_users:
>>
>>  create table virtual_users;
>>+---+-+
>> | Table | Create Table
>>
>>
>>
>>
>>
>>
>>  |
>>+---+-+
>> | virtual_users | 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 '0',
>>   `quota_messages` int(11) NOT NULL DEFAULT '0',
>>   PRIMARY KEY (`id`),
>>   UNIQUE KEY `UNIQUE_EMAIL` (`domain_id`,`user`),
>>   CONSTRAINT `virtual_users_ibfk_1` FOREIGN KEY (`domain_id`)
>> REFERENCES `virtual_domains` (`id`) ON DELETE CASCADE
>> ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |
>>+---+-+
>> 1 row in set (0.00 sec)
>>
>>Thanks.
>>Dave.
>>
>>>
>>>
>>> On 4/26/17, Johan De Meersman <vegiv...@tuxera.be> wrote:
>>>>
>>>> I note that the innodb status says it couldn't find an index on the
>>>> referenced column. Did the create statements come from your create
>>>> scripts
>>>> or from a show create table statement? I'm suspicious about the
>>index on
>>>> virtual_users(user).
>>>>
>>>>
>>>> - Original Message -
>>>>> From: "David Mehler" <dave.meh...@gmail.com>
>>>>> To: "MySql" <mysql@lists.mysql.com>
>>>>> Sent: Tuesday, 25 April, 2017 23:07:19
>>>>> Subject: 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.
>>>>
>>>> --
>>>> The bay-trees in our country are all wither'd
>>>> And meteors fright the fixed stars of heaven;
>>>> The pale-faced moon looks bloody on the earth
>>>> And lean-look'd prophets whisper fearful change.
>>>> These signs forerun the death or fall of kings.
>>>>   -- Wm. Shakespeare, "Richard II"
>>>>
>>>
>>> Thanks.
>>> Dave.
>>>
>>
>>--
>>MySQL General Mailing List
>>For list archives: http://lists.mysql.com/mysql
>>To unsubscribe:http://lists.mysql.com/mysql
>
> --
> Sent from my Android device with K-9 Mail. Please excuse my brevity.
>
> --
> 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

Re: Can not add foreign key constraint

2017-04-28 Thread Johan De Meersman 
That is quite different, as I suspected :-)

Referential keys require an index on the target table that begins with the 
referenced field, so you'll need to add one on user, as was specified in the 
create table you originally posted.

On 28 April 2017 01:21:39 CEST, David Mehler <dave.meh...@gmail.com> wrote:
>Hello,
>
> Here's the output of the command show create table virtual_users:
>
>  create table virtual_users;
>+---+-+
> | Table | Create Table
>
>
>
>
>
>
>  |
>+---+-+
> | virtual_users | 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 '0',
>   `quota_messages` int(11) NOT NULL DEFAULT '0',
>   PRIMARY KEY (`id`),
>   UNIQUE KEY `UNIQUE_EMAIL` (`domain_id`,`user`),
>   CONSTRAINT `virtual_users_ibfk_1` FOREIGN KEY (`domain_id`)
> REFERENCES `virtual_domains` (`id`) ON DELETE CASCADE
> ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |
>+---+-+
> 1 row in set (0.00 sec)
>
>Thanks.
>Dave.
>
>>
>>
>> On 4/26/17, Johan De Meersman <vegiv...@tuxera.be> wrote:
>>>
>>> I note that the innodb status says it couldn't find an index on the
>>> referenced column. Did the create statements come from your create
>>> scripts
>>> or from a show create table statement? I'm suspicious about the
>index on
>>> virtual_users(user).
>>>
>>>
>>> ----- Original Message -
>>>> From: "David Mehler" <dave.meh...@gmail.com>
>>>> To: "MySql" <mysql@lists.mysql.com>
>>>> Sent: Tuesday, 25 April, 2017 23:07:19
>>>> Subject: 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.
>>>
>>> --
>>> The bay-trees in our country are all wither'd
>>> And meteors fright the fixed stars of heaven;
>>> The pale-faced moon looks bloody on the earth
>>> And lean-look'd prophets whisper fearful change.
>>> These signs forerun the death or fall of kings.
>>>   -- Wm. Shakespeare, "Richard II"
>>>
>>
>> Thanks.
>> Dave.
>>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:http://lists.mysql.com/mysql

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Can not add foreign key constraint

2017-04-27 Thread David Mehler 
Hello,

 Here's the output of the command show create table virtual_users:

  create table virtual_users;
 
+---+-+
 | Table | Create Table






  |
 
+---+-+
 | virtual_users | 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 '0',
   `quota_messages` int(11) NOT NULL DEFAULT '0',
   PRIMARY KEY (`id`),
   UNIQUE KEY `UNIQUE_EMAIL` (`domain_id`,`user`),
   CONSTRAINT `virtual_users_ibfk_1` FOREIGN KEY (`domain_id`)
 REFERENCES `virtual_domains` (`id`) ON DELETE CASCADE
 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |
 
+---+-+
 1 row in set (0.00 sec)

Thanks.
Dave.

>
>
> On 4/26/17, Johan De Meersman <vegiv...@tuxera.be> wrote:
>>
>> I note that the innodb status says it couldn't find an index on the
>> referenced column. Did the create statements come from your create
>> scripts
>> or from a show create table statement? I'm suspicious about the index on
>> virtual_users(user).
>>
>>
>> - Original Message -
>>> From: "David Mehler" <dave.meh...@gmail.com>
>>> To: "MySql" <mysql@lists.mysql.com>
>>> Sent: Tuesday, 25 April, 2017 23:07:19
>>> Subject: 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.
>>
>> --
>> The bay-trees in our country are all wither'd
>> And meteors fright the fixed stars of heaven;
>> The pale-faced moon looks bloody on the earth
>> And lean-look'd prophets whisper fearful change.
>> These signs forerun the death or fall of kings.
>>   -- Wm. Shakespeare, "Richard II"
>>
>
> Thanks.
> Dave.
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Can not add foreign key constraint

2017-04-25 Thread David Mehler 
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  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  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  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;
>>

Re: Can not add foreign key constraint

2017-04-24 Thread Peter Brawley 

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  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  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;

Re: Can not add foreign key constraint

2017-04-24 Thread David Mehler 
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;

Thanks.
Dave.


On 4/24/17, Peter Brawley  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  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;

Re: Can not add foreign key constraint

2017-04-24 Thread David Mehler 
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?

Thanks.
Dave.


On 4/24/17, Peter Brawley  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;
>>

Re: Can not add foreign key constraint

2017-04-24 Thread David Mehler 
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;

Re: Can not add foreign key constraint

2017-04-24 Thread Peter Brawley 

On 4/24/2017 15:28, Peter Brawley 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.


Ah, an error cascade, as Shawn Green noticed, there's a typo in my 
suggested command, should be ...


show engine innodb status;

PB

-



PB

-




This is on a Mysql 5.7 setup.

Thanks.
Dave.



On 4/24/17, Peter Brawley  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

Re: Can not add foreign key constraint

2017-04-24 Thread shawn l.green 



On 4/24/2017 2:10 PM, Peter Brawley wrote:

On 4/24/2017 12:28, David Mehler wrote:

...snip


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



Typo warning:   "innodb status" is two words (no underscore)

https://dev.mysql.com/doc/refman/5.6/en/show-engine.html

--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit https://www.mysql.com/certification/ 
for details.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Can not add foreign key constraint

2017-04-24 Thread David Mehler 
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  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  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

Re: Can not add foreign key constraint

2017-04-24 Thread Peter Brawley 

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  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

Re: Can not add foreign key constraint

2017-04-24 Thread David Mehler 
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  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

Re: Can not add foreign key constraint

2017-04-24 Thread Peter Brawley 

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

Re: Can not add foreign key constraint

2017-04-24 Thread David Mehler 
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.

  Thanks.
  Dave.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql