| Bug #10335 | Though foreign key constraint exists no error displayed while dropping database | ||
|---|---|---|---|
| Submitted: | 3 May 2005 14:02 | Modified: | 26 May 2005 14:14 |
| Reporter: | Disha | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.0.4 Beta/5.0.6 | OS: | Windows (Windows 2003) |
| Assigned to: | Marko Mäkelä | CPU Architecture: | Any |
[3 May 2005 14:04]
Disha
Command output
Attachment: Though foreign key constraint exists.txt (text/plain), 3.06 KiB.
[3 May 2005 14:21]
MySQL Verification Team
Verified with BK source server.
[3 May 2005 14:22]
Heikki Tuuri
Assigning this to Marko. --Heikki
[6 May 2005 13:25]
Marko Mäkelä
I can't repeat this with current BK snapshots of 4.1 or 5.0. Miguel, what exact commands did you use?
[6 May 2005 22:06]
MySQL Verification Team
Marko I did exactly how was reported. The below test is with the
latest BK on Slackware:
miguel@hegel:~/dbs/5.0$ bin/mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.6-beta-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> delimiter //
mysql> drop database if exists db1//
Query OK, 0 rows affected, 1 warning (0.04 sec)
mysql> create database db1//
Query OK, 1 row affected (0.02 sec)
mysql> use db1//
Database changed
mysql> drop table if exists course//
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create table course(
-> id int,
-> courcename char(100) not null,
-> duration int not null,
-> primary key (id)
-> ) engine=innodb//
Query OK, 0 rows affected (0.07 sec)
mysql> insert into course values(1,'system design',4),
-> (2,'database system',6),
-> (3,'operating system',16)//
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> drop table if exists students//
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create table students(id int not null,
-> name char(100) not null,
-> course_id int,
-> foreign key (course_id) references course(id)
-> on delete set null
-> ) engine=innodb//
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> insert into students values(1,'l',1),
-> (2,'m',1),
-> (3,'n',2),
-> (4,'o',3),
-> (5,'p',3),
-> (6,'q',1)//
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> drop database if exists db2//
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create database db2//
Query OK, 1 row affected (0.00 sec)
mysql> drop table if exists db2.trainers//
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create table db2.trainers(
-> id int not null,
-> name char(100) not null,
-> course_id int,
-> foreign key (course_id) references db1.course(id)
-> on delete set null
-> ) engine=innodb//
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> insert into db2.trainers values(1,'a',1),
-> (2,'b',1),
-> (3,'c',2),
-> (4,'d',3),
-> (5,'e',3),
-> (6,'f',1)//
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql>
mysql> select * from db2.trainers//
+----+------+-----------+
| id | name | course_id |
+----+------+-----------+
| 1 | a | 1 |
| 2 | b | 1 |
| 3 | c | 2 |
| 4 | d | 3 |
| 5 | e | 3 |
| 6 | f | 1 |
+----+------+-----------+
6 rows in set (0.01 sec)
mysql> delete from db1.course where id=1//
Query OK, 1 row affected (0.03 sec)
mysql> select * from db2.trainers//
+----+------+-----------+
| id | name | course_id |
+----+------+-----------+
| 1 | a | NULL |
| 2 | b | NULL |
| 3 | c | 2 |
| 4 | d | 3 |
| 5 | e | 3 |
| 6 | f | NULL |
+----+------+-----------+
6 rows in set (0.00 sec)
mysql> drop database if exists db1//
Query OK, 2 rows affected (0.02 sec)
[9 May 2005 7:35]
Marko Mäkelä
Miguel, I still keep getting "ERROR 1217: Cannot delete or update a parent row: a foreign key constraint fails" for the DROP DATABASE IF EXISTS db1. Table "course" will remain in db1, but db1.students will be dropped, and I wouldn't treat that as a bug, because DROP TABLE and DROP DATABASE are non-transactional commands.
[21 May 2005 6:32]
Heikki Tuuri
Miguel, what is your my.cnf like? Please use SHOW CREATE TABLE to check that the foreign key constraint really was created. Regards, Heikki
[21 May 2005 6:47]
Heikki Tuuri
Miguel,
I cannot repeat this with the 5.0 tree from last night.
Are you sure you have not set foreign_key_checks=0?
Please post your complete my.cnf.
Regards,
Heikki
heikki@hundin:~/mysql-5.0/client> ./mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.6-beta-debug-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> delimiter //
mysql>
mysql> create database db1//
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> use db1
Database changed
mysql>
mysql> create table course(
-> id int,
-> courcename char(100) not null,
-> duration int not null,
-> primary key (id)
-> ) engine=innodb//
,'b',1),
(3,'c',2),
(4,'d',3),
(5,'e',3),
(6,'f',1)//
delete from db1.course where id=1//
drop database if exists db1//
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> insert into course values(1,'system design',4),
-> (2,'database system',6),
-> (3,'operating system',16)//
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql>
mysql> create table students(id int not null,
-> name char(100) not null,
-> course_id int,
-> foreign key (course_id) references course(id)
-> on delete set null
-> ) engine=innodb//
Query OK, 0 rows affected (0.06 sec)
mysql>
mysql> insert into students values(1,'l',1),
-> (2,'m',1),
-> (3,'n',2),
-> (4,'o',3),
-> (5,'p',3),
-> (6,'q',1)//
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql>
mysql> create database db2//
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> create table db2.trainers(
-> id int not null,
-> name char(100) not null,
-> course_id int,
-> foreign key (course_id) references db1.course(id)
-> on delete set null
-> ) engine=innodb//
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql> insert into db2.trainers values(1,'a',1),
-> (2,'b',1),
-> (3,'c',2),
-> (4,'d',3),
-> (5,'e',3),
-> (6,'f',1)//
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql>
mysql> delete from db1.course where id=1//
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> drop database if exists db1//
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constrai
nt fails
mysql>
# Example mysql config file.
# Copy this file to c:\my.cnf to set global options
#
# One can use all long options that the program supports.
# Run the program with --help to get a list of available options
# This will be passed to all mysql clients
[client]
#default-character-set=utf8
socket=/home/heikki/bugsocket
#socket=/tmp/mysqld.heikki
port=3307
#password=my_password
#port=3306
#socket=MySQL
# Here is entries for some specific programs
# The following values assume you have at least 32M ram
# The MySQL server
[mysqld]
#sync-binlog=1
#innodb_support_xa=0
#log
#innodb_table_locks=0
#skip-safemalloc
#bdb-no-sync
#skip-innodb
server-id=1
#default-character-set=utf8
#lower_case_table_names=1
language=/home/heikki/mysql-5.0/sql/share/english
default-table-type=innodb
log-bin=binlog
set-variable = max_binlog_size=100M
port=3307
socket=/home/heikki/bugsocket
#skip-locking
basedir=/home/heikki
datadir=/home/heikki/data
#innodb_open_files=765
#set-variable = innodb_buffer_pool_awe_mem_mb=100
#set-variable = innodb_lock_wait_timeout=5
#innodb_force_recovery=5
#set-variable = innodb_thread_concurrency=8
#innodb_flush_method=fdatasync
#innodb_fast_shutdown=0
# Uncomment the following row if you move the MySQL distribution to another
# location
#basedir = d:/mysql/
set-variable = table_cache=256
#set-variable = sort_buffer_size=32M
#set-variable = myisam_sort_buffer_size=384M
#set-variable = read_buffer_size=32M
#set-variable = read_rnd_buffer_size=32M
#set-variable = tmp_table_size=384M
#set-variable = thread_cache=8
#set-variable = thread_concurrency=2
set-variable=key_buffer=35M
#set-variable=long_query_time=5
set-variable=max_allowed_packet=8M
#set-variable=low_priority_updates=1
#set-variable=query_cache_size=50M
#set-variable=query_cache_limit=2M
#set-variable=query_cache_type=1
# Uncomment the following if you are using InnoDB tables
#innodb_locks_unsafe_for_binlog
#innodb_file_per_table
#set-variable = innodb_force_recovery=6
innodb_data_home_dir = /home/heikki/data
#innodb_autoextend_increment = 20
innodb_data_file_path = ibdata1:10M:autoextend
#set-variable = innodb_mirrored_log_groups=1
innodb_log_group_home_dir = /home/heikki/data
#innodb_log_arch_dir = /home/heikki/data
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
set-variable = innodb_buffer_pool_size=100M
set-variable = innodb_additional_mem_pool_size=10M
set-variable = innodb_file_io_threads=4
# Set .._log_file_size to 25 % of buffer pool size
set-variable = innodb_log_file_size=125M
set-variable = innodb_log_files_in_group=2
set-variable = innodb_log_buffer_size=8M
#innodb_flush_log_at_trx_commit=2
set-variable = innodb_lock_wait_timeout=50
# InnoDB does a full purge and an insert buffer merge
# before a shutdown. May be slow hope to solve problem
#set-variable = innodb_fast_shutdown=0
#set-variable = lower_case_table_names=1
#innodb_lock_wait_timeout=5
#innodb_thread_concurrency=500
[mysqldump]
quick
set-variable = max_allowed_packet=16M
[mysql]
#default-character-set=utf8
no-auto-rehash
socket=/home/heikki/bugsocket
port=3307
[mysqladmin]
socket=/home/heikki/bugsocket
port=3307
[isamchk]
set-variable= key=16M
[client_fltk]
[22 May 2005 16:41]
MySQL Verification Team
Heikki,
Below the commands I did with show create table:
miguel@hegel:~/dbs/5.0$ bin/mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.7-beta-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> delimiter //
mysql> drop database if exists db1//
Query OK, 0 rows affected, 1 warning (0.05 sec)
mysql> create database db1//
Query OK, 1 row affected (0.00 sec)
mysql> use db1//
Database changed
mysql> drop table if exists course//
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create table course(
-> id int,
-> courcename char(100) not null,
-> duration int not null,
-> primary key (id)
-> ) engine=innodb//
Query OK, 0 rows affected (0.03 sec)
mysql> insert into course values(1,'system design',4),
-> (2,'database system',6),
-> (3,'operating system',16)//
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> drop table if exists students//
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create table students(id int not null,
-> name char(100) not null,
-> course_id int,
-> foreign key (course_id) references course(id)
-> on delete set null
-> ) engine=innodb//
Query OK, 0 rows affected (0.03 sec)
mysql> show create table students\G
*************************** 1. row ***************************
Table: students
Create Table: CREATE TABLE `students` (
`id` int(11) NOT NULL,
`name` char(100) NOT NULL,
`course_id` int(11) default NULL,
KEY `course_id` (`course_id`),
CONSTRAINT `students_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `course` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)
mysql> insert into students values(1,'l',1),
-> (2,'m',1),
-> (3,'n',2),
-> (4,'o',3),
-> (5,'p',3),
-> (6,'q',1)//
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> drop database if exists db2//
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create database db2//
Query OK, 1 row affected (0.00 sec)
mysql> drop table if exists db2.trainers//
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> create table db2.trainers(
-> id int not null,
-> name char(100) not null,
-> course_id int,
-> foreign key (course_id) references db1.course(id)
-> on delete set null
-> ) engine=innodb//
Query OK, 0 rows affected (0.02 sec)
mysql> show create table db2.trainers\G
*************************** 1. row ***************************
Table: trainers
Create Table: CREATE TABLE `trainers` (
`id` int(11) NOT NULL,
`name` char(100) NOT NULL,
`course_id` int(11) default NULL,
KEY `course_id` (`course_id`),
CONSTRAINT `trainers_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `db1`.`course` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)
mysql> insert into db2.trainers values(1,'a',1),
-> (2,'b',1),
-> (3,'c',2),
-> (4,'d',3),
-> (5,'e',3),
-> (6,'f',1)//
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql>
mysql> select * from db2.trainers//
+----+------+-----------+
| id | name | course_id |
+----+------+-----------+
| 1 | a | 1 |
| 2 | b | 1 |
| 3 | c | 2 |
| 4 | d | 3 |
| 5 | e | 3 |
| 6 | f | 1 |
+----+------+-----------+
6 rows in set (0.00 sec)
mysql> delete from db1.course where id=1//
Query OK, 1 row affected (0.04 sec)
mysql> select * from db2.trainers//
+----+------+-----------+
| id | name | course_id |
+----+------+-----------+
| 1 | a | NULL |
| 2 | b | NULL |
| 3 | c | 2 |
| 4 | d | 3 |
| 5 | e | 3 |
| 6 | f | NULL |
+----+------+-----------+
6 rows in set (0.00 sec)
mysql> drop database if exists db1//
Query OK, 2 rows affected (0.03 sec)
mysql>
[23 May 2005 16:25]
Heikki Tuuri
Miguel, what is your my.cnf like? Regards, Heikki
[26 May 2005 12:40]
Marko Mäkelä
Heikki was able to repeat this on Windows. This bug might exist on Windows only.
[26 May 2005 12:52]
Heikki Tuuri
Hi! I was able to repeat on Windows. This is a simple bug: the code only checks that the FIRST referencing table that is not the table itself, is from the same db. In Disha's example, the SECOND referencing table was from a different db! On Unix the ordering of the list apparently was different, and that is why we were not able to repeat on Unix. Thank you, Heikki
[26 May 2005 12:55]
Heikki Tuuri
Actually, on Unix, MySQL probably dropped the table 'students' first. On Windows, 'course'. --Heikki
[26 May 2005 14:14]
Marko Mäkelä
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.
If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information
about accessing the source trees is available at
http://www.mysql.com/doc/en/Installing_source_tree.html
[27 May 2005 18:06]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/25309
Description: If a table field is being referred as foreign key by multiple tables where one is from the same database and the other is from other database then when we try to delete the first database, no error is displayed and the database is deleted. How to repeat: 1. Start the MySQL client and connect to the database server. 2. Set the delimiter to // 3. Run the following commands to create and use a database. drop database if exists db1// create database db1// use db1// 4. Now using the following commands create two tables where the second table has a foreign key reference to the first table as follows: drop table if exists course// create table course( id int, courcename char(100) not null, duration int not null, primary key (id) ) engine=innodb// insert into course values(1,'system design',4), (2,'database system',6), (3,'operating system',16)// drop table if exists students// create table students(id int not null, name char(100) not null, course_id int, foreign key (course_id) references course(id) on delete set null ) engine=innodb// insert into students values(1,'l',1), (2,'m',1), (3,'n',2), (4,'o',3), (5,'p',3), (6,'q',1)// 5. Now crate another database and create another table in the database having foreign key reference to the table created in step 4 as follows: drop database if exists db2// create database db2// drop table if exists db2.trainers// create table db2.trainers( id int not null, name char(100) not null, course_id int, foreign key (course_id) references db1.course(id) on delete set null ) engine=innodb// insert into db2.trainers values(1,'a',1), (2,'b',1), (3,'c',2), (4,'d',3), (5,'e',3), (6,'f',1)// select * from db2.trainers// 6. To verify that the foreign key constraint is working delete a row form the course table and verify the contents of trainers table as follows: delete from db1.course where id=1// select * from db2.trainers// 7. Now try to drop database db1 as follows: drop database if exists db1// Expected Results: 1. Appropriate error should be displayed as foreign key constraint exists. Actual Results: 1. No error is displayed and the database is deleted without error. Additional information: In the above scenarios, if we do not create the table 'students' in db1 and then at the end try to drop database db1, then appropriate error is displayed. Please refer to the attached command output.