MySQL Bugs: #35532: Foreign keys: errors with automatic constraint names

Bug #35532	Foreign keys: errors with automatic constraint names
Submitted:	24 Mar 2008 17:57	Modified:	25 Jun 2008 20:13
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	6.1.0-fk-debug	OS:	Linux (SUSE 10 \| 32-bit)
Assigned to:	Dmitry Lenev	CPU Architecture:	Any

Description:
I'm using mysql-6.1-fk.
I start mysqld with --foreign-key-all-engines=1.

MySQL automatically generates constraint names if I
don't specify any. It does not generate the same name
given the same database environment, which will lead
to problems with mysqltest and perhaps with replication.
It does not try to re-generate if there is already a
constraint with the same name.

I conclude that the generator isn't good enough.

How to repeat:
In the following, I start by creating 10,000 tables
so that combinations of 'fk_m_nnnn' names are all used.
Then I quit, and restart the server, and try to create
a table with a foreign key which will try to generate
an 'fk_m_nnnn' name automatically. It will fail.

delimiter //
use test//
drop database m//
create database m//
use m//
drop procedure p//
create table t (s1 int primary key)//
create procedure p ()
begin
  declare v int default 0;
  while v < 10000 do
    set @x = 'create table t';
    set @x = concat(@x,v);
    set @x = concat(@x,' (s1 int constraint fk_m_1_');
    set @x = concat(@x,v);
    set @x = concat(@x,' references t(s1))');
    prepare stmt1 from @x;
    execute stmt1;
    set v = v + 1;
    end while;
  end//
call p()//

/* now quit, stop server, restart server, and restart client */

use m
create table m (s1 int references t(s1));

The "create table m (s1 int references t(s1));" statement
results in an error message thus:

mysql> create table m (s1 int references t(s1));
ERROR 1672 (42000): Foreign key error: Constraint 'fk_m_1_8439': Duplicate constraint name

Thank you for the bug report. Notice starting the mysql client with -A option:

[miguel@mirador dbs]$ 6.1fk/bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.1.0-fk-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use m
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table m (s1 int references t(s1));
ERROR 1672 (42000): Foreign key error: Constraint 'fk_m_1_4729': Duplicate constraint name
mysql> exit
Bye
[miguel@mirador dbs]$ 6.1fk/bin/mysql -uroot -A
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 6.1.0-fk-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use m
Database changed
mysql> create table m (s1 int references t(s1));
Query OK, 0 rows affected (0.01 sec)

mysql>

Hello Peter!

As we have discussed during our meeting in Trondheim statement that server
"does not generate the same name given the same database environment,
which will lead to problems with mysqltest and perhaps with replication"
is not true.

Currently names for foreign keys are based on table name, current
connection id and value from generator of pseudo-random numbers.
By using @@pseudo_thread_id and @@rand_seed1, @@rand_seed2 variables
one can control two last parameters to get repeatable names in test suite.
Also there is no problem with replication since values for these variables
are automatically saved in binary log for statements generating foreign
key names.

Statement that "It does not try to re-generate if there is already a
constraint with the same name." is true. But the only realistic scenario
in which this could be a problem is when one creates a table and then does
thousands add/drop foreign key operations on this table (IMO, deliberately
choosing explicit names from the same name-space as generated names, as you
do in the above example, does not count as realistic).

We agreed that it will be enough to increase allowed range for the last,
random component of generated names by 3 orders to alleviate this problem.
We also agreed that in this case we should use more compact encoding which
will involve letters instead of digits and that we also can drop
"connection id" part from the generated name.

Fix for this bug was pushed into mysql-6.1-fk tree. Since this bug was reported
against tree which is not publicly available yet I am simply closing this bug.