RE: Foreign keys vont delete on cascade

[Alexandre Zglav]

#   are willing to compromise this safety,
#and you are running small 
transactions,
#you may set this to 0 to reduce disk
#   i/o to the logs. The default value of
#   this parameter is 0.

innodb_flush_log_at_trx_commit=1
#set-variable = innodb_file_io_threads = 4
#set-variable = innodb_lock_wait_timeout = 50


#  Plus d'informations sur parametres INNODB
http://www.innodb.com/ibman.html
#  COnfiguration optimisée pour


[WinMySQLadmin]
Server=C:/mysql/bin/mysqld-nt.exe

THANKS HEIKKI :) !!




-Message d'origine-
De : Heikki Tuuri [mailto:[EMAIL PROTECTED]]
Envoyé : jeudi, 8. août 2002 08:17
À : [EMAIL PROTECTED]
Objet : Re: Foreign keys vont delete on cascade


Alexandre,

ON DELETE CASCADE only works starting from 3.23.50 and 4.0.2. The version
numbers are misleading because for the InnoDB subsystem it is the date of
release which determines how new the InnoDB version is. Thus, for InnoDB,
4.0.1 == 3.23.47.

Best regards,

Heikki Tuuri
Innobase Oy
---
Order technical MySQL/InnoDB support at https://order.mysql.com/
See http://www.innodb.com for the online manual and latest news on InnoDB

- Original Message -
From: Alexandre Zglav [EMAIL PROTECTED]
Newsgroups: mailing.database.mysql
Sent: Thursday, August 08, 2002 6:00 AM
Subject: Foreign keys vont delete on cascade



 Hi all ,

 Im trying to have innodb to work . I've made my way through the
 configuration process and passed the creation of tables with primarey key
 and foreign keys.

 Im am currently trying to do exactly what is done in the innodb manual so
 that i'm sure i'm doing it correct :)

 Here is a very simple log of what i've done .

 I am working with mysql 4.0.1-alpha-max on a win 2K platform .


 You will see that ( if every thing is done correctly ) my records in table
'
 enfant ' should be delete when I try to delete a record from table '
parent'
 , where the id of table parent is referenced in the enfant ' table ' 
 errrm well never mind lets get to that log .

 I hope you can help me . Its been a long time since ive been trying to get
 this stuff to work and I'd really appreciate a tip or two :)

 See ya all



 Wmysql CREATE DATABASE famille
 - ;
 Query OK, 1 row affected (0.01 sec)

 mysql CREATE TABLE parent (id INT NOT NULL, nom CHAR(10), PRIMARY KEY
(id))
 TYPE=INNODB;
 ERROR 1046: No Database Selected
 mysql use famille
 Database changed
 mysql CREATE TABLE parent (id INT NOT NULL, nom CHAR(10), PRIMARY KEY
(id))
 TYPE=INNODB;
 Query OK, 0 rows affected (0.09 sec)

 mysql CREATE TABLE enfant (id INT, parent_id INT, INDEX par_id
(parent_id),
 FOREIGN KEY (parent_id) REFERENCES parent(id) ON
  DELETE CASCADE) TYPE=INNODB;
 Query OK, 0 rows affected (0.70 sec)

 mysql INSERT INTO parent (id , nom) VALUES (1, 'alex');
 Query OK, 1 row affected (0.04 sec)

 mysql INSERT INTO parent (id , nom) VALUES (3, 'tonio');
 Query OK, 1 row affected (0.02 sec)

 mysql INSERT INTO parent (id , nom) VALUES (24, 'poulaga');
 Query OK, 1 row affected (0.02 sec)

 mysql INSERT INTO parent (id , nom) VALUES (22, 'hellscream');
 Query OK, 1 row affected (0.02 sec)

 mysql select * from parent;
 +++
 | id | nom|
 +++
 |  1 | alex   |
 |  3 | tonio  |
 | 22 | hellscream |
 | 24 | poulaga|
 +++
 4 rows in set (0.00 sec)

 mysql insert into enfant ( id, parent_id) VALUES (12, 223):
 - ;
 ERROR 1064: You have an error in your SQL syntax near ':' at line 1
 mysql insert into enfant ( id, parent_id) VALUES (12, 223);
 ERROR 1216: Cannot add a child row: a foreign key constraint fails
 mysql insert into enfant ( id, parent_id) VALUES (12, 3);
 Query OK, 1 row affected (0.04 sec)

 mysql YEEHAA;

 ERROR 1064: You have an error in your SQL syntax near 'YEEHAA' at line 1

 DOH !

 mysql select * from enfant;
 +--+---+
 | id   | parent_id |
 +--+---+
 |   12 | 3 |
 +--+---+
 1 row in set (0.00 sec)


 mysql insert into enfant ( id, parent_id) VALUES (4, 3);
 Query OK, 1 row affected (0.02 sec)

 mysql insert into enfant ( id, parent_id) VALUES (4, 22);
 Query OK, 1 row affected (0.02 sec)

 mysql select * from enfant;
 +--+---+
 | id   | parent_id |
 +--+---+
 |   12 | 3 |
 |   14 | 3 |
 |4 | 3 |
 |4 | 3 |
 |4 |22 |
 +--+---+
 5 rows in set (0.00 sec)

 mysql DELETE FROM parent WHERE ID = 3;
 ERROR 1217: Cannot delete a parent row: a foreign key constraint fails

 !!!


 mysql DELETE FROM parent WHERE nom = tonio;
 ERROR 1054: Unknown column 'tonio' in 'where clause'
 mysql DELETE FROM parent WHERE nom = 'tonio';
 ERROR 1217: Cannot delete a parent row: a foreign key constraint fails
 mysql DELETE FROM parent WHERE nom = 'tonio';
 ERROR 1217: Cannot delete a parent

RE: Foreign keys vont delete on cascade

[Alexandre Zglav]

OK IT WORKED FINE :)) !!!

I am finally going to be able to work correctly :))

Here is a log of what i did to test :


mysql create database fam2
- ;
Query OK, 1 row affected (0.00 sec)

mysql use fam2
Database changed
mysql create table CREATE TABLE parent (id INT NOT NULL, nom CHAR(10),
PRIMARY KEY (id)) TYPE=INNODB;
ERROR 1064: You have an error in your SQL syntax near 'CREATE TABLE parent
(id INT NOT NULL, nom CHAR(10), PRIMARY KEY (id))
TYPE=INNOD' at line 1
mysql CREATE TABLE parent (id INT NOT NULL, nom CHAR(10), PRIMARY KEY (id))
TYPE=INNODB;
Query OK, 0 rows affected (0.08 sec)

mysql CREATE TABLE enfant (id INT, parent_id INT, INDEX par_id (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent(id) ON

-  DELETE CASCADE) TYPE=INNODB;
Query OK, 0 rows affected (0.03 sec)

mysql INSERT INTO parent (id , nom) VALUES (1, 'alex');
Query OK, 1 row affected (0.03 sec)

mysql INSERT INTO parent (id , nom) VALUES (3, 'tonio');
Query OK, 1 row affected (0.03 sec)

mysql INSERT INTO parent (id , nom) VALUES (24, 'poulaga');
Query OK, 1 row affected (0.02 sec)

mysql INSERT INTO parent (id , nom) VALUES (22, 'hellscream');
Query OK, 1 row affected (0.03 sec)

mysql select * from parent;
+++
| id | nom|
+++
|  1 | alex   |
|  3 | tonio  |
| 22 | hellscream |
| 24 | poulaga|
+++
4 rows in set (0.02 sec)

mysql insert into enfant ( id, parent_id) VALUES (14, 3);
Query OK, 1 row affected (0.01 sec)

mysql insert into enfant ( id, parent_id) VALUES (14, 546);
ERROR 1216: Cannot add a child row: a foreign key constraint fails
mysql insert into enfant ( id, parent_id) VALUES (234,22);
Query OK, 1 row affected (0.03 sec)

mysql insert into enfant ( id, parent_id) VALUES (24,22);
Query OK, 1 row affected (0.40 sec)

mysql insert into enfant ( id, parent_id) VALUES (25,22);
Query OK, 1 row affected (0.01 sec)

mysql insert into enfant ( id, parent_id) VALUES (1234,22);
Query OK, 1 row affected (0.02 sec)

mysql insert into enfant ( id, parent_id) VALUES (1234,24);
Query OK, 1 row affected (0.02 sec)

mysql insert into enfant ( id, parent_id) VALUES (123444,24);
Query OK, 1 row affected (0.02 sec)

mysql insert into enfant ( id, parent_id) VALUES (1232323,24);
Query OK, 1 row affected (0.03 sec)

mysql insert into enfant ( id, parent_id) VALUES (122,24);
Query OK, 1 row affected (0.02 sec)

mysql SELECT * FROM ENFANT;
+-+---+
| id  | parent_id |
+-+---+
|  14 | 3 |
| 234 |22 |
|  24 |22 |
|  25 |22 |
|1234 |22 |
|1234 |24 |
|  123444 |24 |
| 1232323 |24 |
| 122 |24 |
+-+---+
9 rows in set (0.01 sec)

mysql DELETE FROM PARENT WHERE id=22;
Query OK, 1 row affected (0.07 sec)

mysql SELECT * FROM ENFANT;
+-+---+
| id  | parent_id |
+-+---+
|  14 | 3 |
|1234 |24 |
|  123444 |24 |
| 1232323 |24 |
| 122 |24 |
+-+---+
5 rows in set (0.00 sec)

mysql

On cascade delete was processed correctly .

Thats perfect.

Thanks again !






-Message d'origine-
De : Alexandre Zglav [mailto:[EMAIL PROTECTED]]
Envoyé : vendredi, 9. août 2002 19:53
À : Heikki Tuuri; [EMAIL PROTECTED]
Objet : RE: Foreign keys vont delete on cascade



OK thanks a lot for your help ! :)

I just downloaded the latest version ( 4.0.2) and installed it .

I hoped I could keep the settings of my ini file ( my.ini) but I got the
following error when trying to start in standalone console :


Microsoft Windows 2000 [Version 5.00.2195]
(C) Copyright 1985-2000 Microsoft Corp.

C:\cd mysql

C:\mysqlcd bin

C:\mysql\binmysqld-max --standalone --console
mysqld-max: ERROR: unknown variable 'innodb_buffer_pool_size = 150M'

C:\mysql\binmysqld-max --standalone --console
mysqld-max: ERROR: unknown variable 'innodb_buffer_pool_size = 150M'

C:\mysql\binmysqld-max --standalone --console
mysqld-max: ERROR: unknown variable 'innodb_additional_mem_pool_size = 25M'

C:\mysql\binmysqld-max --standalone --console
mysqld-max: ERROR: unknown variable 'innodb_log_files_in_group = 3'

C:\mysql\binmysqld-max --standalone --console
mysqld-max: ERROR: unknown variable 'innodb_log_file_size = 20M'

C:\mysql\binmysqld-max --standalone --console
mysqld-max: ERROR: unknown variable 'innodb_log_buffer_size = 15M'

C:\mysql\binmysqld-max --standalone --console
mysqld-max: ERROR: unknown variable 'innodb_file_io_threads = 4'

C:\mysql\binmysqld-max --standalone --console
mysqld-max: ERROR: unknown variable 'innodb_lock_wait_timeout = 50'

C:\mysql\binmysqld-max --standalone --console
InnoDB: Error: log file c:\mysql\ibdata\iblogs\ib_logfile0 is of different
size
InnoDB: than specified in the .cnf file!
020809 19:46:16  Can't init databases

C:\mysql\binmysqld-max --standalone --console
InnoDB: The first specified data file C:\mysql\ibdata\ibdata1 did