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 (1222222,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 |
| 1222222 | 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 |
| 1222222 | 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:\mysql>cd bin
C:\mysql\bin>mysqld-max --standalone --console
mysqld-max: ERROR: unknown variable 'innodb_buffer_pool_size = 150M'
C:\mysql\bin>mysqld-max --standalone --console
mysqld-max: ERROR: unknown variable 'innodb_buffer_pool_size = 150M'
C:\mysql\bin>mysqld-max --standalone --console
mysqld-max: ERROR: unknown variable 'innodb_additional_mem_pool_size = 25M'
C:\mysql\bin>mysqld-max --standalone --console
mysqld-max: ERROR: unknown variable 'innodb_log_files_in_group = 3'
C:\mysql\bin>mysqld-max --standalone --console
mysqld-max: ERROR: unknown variable 'innodb_log_file_size = 20M'
C:\mysql\bin>mysqld-max --standalone --console
mysqld-max: ERROR: unknown variable 'innodb_log_buffer_size = 15M'
C:\mysql\bin>mysqld-max --standalone --console
mysqld-max: ERROR: unknown variable 'innodb_file_io_threads = 4'
C:\mysql\bin>mysqld-max --standalone --console
mysqld-max: ERROR: unknown variable 'innodb_lock_wait_timeout = 50'
C:\mysql\bin>mysqld-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\bin>mysqld-max --standalone --console
InnoDB: The first specified data file C:\mysql\ibdata\ibdata1 did not exist:
InnoDB: a new database to be created!
020809 19:46:53 InnoDB: Setting file C:\mysql\ibdata\ibdata1 size to 100 MB
InnoDB: Database physically writes the file full: wait...
020809 19:47:12 InnoDB: Log file c:\mysql\ibdata\iblogs\ib_logfile0 did not
exist: new to be created
InnoDB: Setting log file c:\mysql\ibdata\iblogs\ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
020809 19:47:13 InnoDB: Log file c:\mysql\ibdata\iblogs\ib_logfile1 did not
exist: new to be created
InnoDB: Setting log file c:\mysql\ibdata\iblogs\ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
020809 19:47:16 InnoDB: Started
mysqld-max: ready for connections
OK FINALLY :)) After deleting my old ibdata files and logs its ok but a lot
of settings had to be placed as commentary .
Here is the my.ini file I use now .
My question is just :
Why did so much settings ( variables ) wern't recognised ??
Here is the ini file
#This File was made using the WinMySQLAdmin 1.4 Tool
#07.03.2002 23:24:07
#Uncomment or Add only the keys that you know how works.
#Read the MySQL Manual for instructions
[mysqld]
basedir=C:\mysql
#bind-address=192.168.0.1
datadir=C:\mysql\data
innodb_data_home_dir = C:\mysql\ibdata
#language=C:\mysql\share\your language directory
#slow query log#=
#tmpdir#=
#port=3306
#set-variable=key_buffer = 16M
# INNODB PARAMETER DEFINITION
# Data files must be able to
# hold your data and indexes
innodb_data_file_path =ibdata1:100M
# Set buffer pool size to 50 - 80
%
# of your computer's memory
#set-variable = innodb_buffer_pool_size = 150M
#set-variable = innodb_additional_mem_pool_size = 25M
innodb_log_group_home_dir = c:\mysql\ibdata\iblogs
# .._log_arch_dir must be the
same
# as .._log_group_home_dir
innodb_log_arch_dir = c:\mysql\ibdata\iblogs
innodb_log_archive = 0
#set-variable = innodb_log_files_in_group = 3
# Set the log file size to about
# 15 % of the buffer pool size
#set-variable = innodb_log_file_size = 20M
#set-variable = innodb_log_buffer_size = 15M
# Set ..flush_log_at_trx_commit
to
# 0 if you can afford losing
# a few last transactions
# N.B. Default is 0 Normally this
# is set to 1, meaning that at a
# transaction commit the log is flushed
# to disk, and the modifications made
# by the transaction become permanent,
# and survive a database crash. If you
# 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 row: a foreign key constraint fails
> mysql> DROP TABLE parent;
> Query OK, 0 rows affected (0.27 sec)
>
> mysql> DROP TABLE enfant
> -> ;
> Query OK, 0 rows affected (0.11 sec)
>
> mysql> CREATE TABLE parent (id INT NOT NULL AUTO_INCREMENT, nom CHAR(10),
> PRIMARY KEY (id)) TYPE=INNODB;
> Query OK, 0 rows affected (0.20 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.08 sec)
>
> mysql> INSERT INTO parent (id , nom) VALUES (1, 'alex');
> Query OK, 1 row affected (0.03 sec)
>
> mysql> INSERT INTO parent (nom) VALUES ('alex2');
> Query OK, 1 row affected (0.03 sec)
>
> mysql> INSERT INTO parent (nom) VALUES ('alex3');
> Query OK, 1 row affected (0.02 sec)
>
> mysql> INSERT INTO parent (nom) VALUES ('alex4');
> Query OK, 1 row affected (0.03 sec)
>
> mysql> INSERT INTO parent (nom) VALUES ('alex5');
> Query OK, 1 row affected (0.05 sec)
>
> mysql> SELECT * FROM parent
> -> ;
> +----+-------+
> | id | nom |
> +----+-------+
> | 1 | alex |
> | 2 | alex2 |
> | 3 | alex3 |
> | 4 | alex4 |
> | 5 | alex5 |
> +----+-------+
> 5 rows in set (0.01 sec)
>
> mysql> INSERT INTO ENFANT (id, parent_ID) VALUES (1,5);
> Query OK, 1 row affected (0.03 sec)
>
> mysql> INSERT INTO ENFANT (id, parent_ID) VALUES (1,6);
> ERROR 1216: Cannot add a child row: a foreign key constraint fails
> mysql> INSERT INTO ENFANT (id, parent_ID) VALUES (1,3);
> Query OK, 1 row affected (0.02 sec)
>
> mysql> INSERT INTO ENFANT (id, parent_ID) VALUES (4,6);
> ERROR 1216: Cannot add a child row: a foreign key constraint fails
> mysql> INSERT INTO ENFANT (id, parent_ID) VALUES (4,2);
> Query OK, 1 row affected (0.03 sec)
>
> mysql> INSERT INTO ENFANT (id, parent_ID) VALUES (6,2);
> Query OK, 1 row affected (0.03 sec)
>
> mysql> INSERT INTO ENFANT (id, parent_ID) VALUES (7,2);
> Query OK, 1 row affected (0.04 sec)
>
> mysql> INSERT INTO ENFANT (id, parent_ID) VALUES (456,2);
> Query OK, 1 row affected (0.03 sec)
>
> mysql> SELECT * FROM parent;
> +----+-------+
> | id | nom |
> +----+-------+
> | 1 | alex |
> | 2 | alex2 |
> | 3 | alex3 |
> | 4 | alex4 |
> | 5 | alex5 |
> +----+-------+
> 5 rows in set (0.00 sec)
>
> mysql> SELECT * FROM enfant;
> +------+-----------+
> | id | parent_id |
> +------+-----------+
> | 1 | 5 |
> | 1 | 3 |
> | 4 | 2 |
> | 6 | 2 |
> | 7 | 2 |
> | 456 | 2 |
> +------+-----------+
> 6 rows in set (0.00 sec)
>
> mysql> DELETE FROM ENFANT WHERE id = 2
> -> ;
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> SELECT * FROM enfant;
> +------+-----------+
> | id | parent_id |
> +------+-----------+
> | 1 | 5 |
> | 1 | 3 |
> | 4 | 2 |
> | 6 | 2 |
> | 7 | 2 |
> | 456 | 2 |
> +------+-----------+
> 6 rows in set (0.00 sec)
>
> mysql> DELETE FROM parent WHERE id = 2;
> ERROR 1217: Cannot delete a parent row: a foreign key constraint fails
>
> Well it wont work at all as you can see .
>
> What am I getting wrong ?
>
> mysql> DELETE FROM enfant WHERE ID = 7;
> Query OK, 1 row affected (0.02 sec)
>
> mysql> SELECT * FROM enfant;
> +------+-----------+
> | id | parent_id |
> +------+-----------+
> | 1 | 5 |
> | 1 | 3 |
> | 4 | 2 |
> | 6 | 2 |
> | 456 | 2 |
> +------+-----------+
> 5 rows in set (0.00 sec)
>
> mysql> SELECT * FROM parent;
> +----+-------+
> | id | nom |
> +----+-------+
> | 1 | alex |
> | 2 | alex2 |
> | 3 | alex3 |
> | 4 | alex4 |
> | 5 | alex5 |
> +----+-------+
> 5 rows in set (0.00 sec)
>
> mysql>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.375 / Virus Database: 210 - Release Date: 10.07.2002
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.375 / Virus Database: 210 - Release Date: 10.07.2002
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.375 / Virus Database: 210 - Release Date: 10.07.2002
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.375 / Virus Database: 210 - Release Date: 10.07.2002
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.375 / Virus Database: 210 - Release Date: 10.07.2002
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
