Re: Can not add foreign key constraint

[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

[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: DATETIME vs CHAR for "timestamp"

[SSC_perl]

> On Apr 14, 2017, at 1:07 PM, shawn l.green  wrote:
> 
> That all depends. Do you...

Hi Shawn,

I thought I had replied to your response, but it looks like I didn’t.  
Thank you for your email.  It was a thorough response and the links were very 
helpful, as well.  I’ve settled on both DATE and DATETIME, depending on whether 
the time is needed or not, which means I’ll have to change some of my code, but 
that will only strengthen the script in the long run.

Thanks again,
Frank
https://www.surfshopcart.com
--
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

[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

[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

[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

[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

[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

[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

[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

[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

[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

Re: Can not add foreign key constraints

[Peter Brawley]

On 4/24/2017 9:18, David Mehler wrote:

Hello,

  I'm trying to add a table to an existing database. I'm wanting it to get
  one of it's fields from an already existing table. I've done this
  before in this database. This works:

  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;

  This does not:

  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;

  Can anyone spot the situation?


Let's see the CREATE TABLE statement for the referenced table, and the 
error message.


PB

-



  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 constraints

[Reindl Harald]

Am 24.04.2017 um 16:18 schrieb David Mehler:

  I'm trying to add a table to an existing database. I'm wanting it to get
  one of it's fields from an already existing table. I've done this
  before in this database. This works:

  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;

  This does not:

  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;

  Can anyone spot the situation?


wouldn't it be cool if you post the errors you get from the start?

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

Can not add foreign key constraints

[David Mehler]

Hello,

 I'm trying to add a table to an existing database. I'm wanting it to get
 one of it's fields from an already existing table. I've done this
 before in this database. This works:

 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;

 This does not:

 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;

 Can anyone spot the situation?

 Thanks.
 Dave.

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