[#MDEV-2696] LP:1014750 - InnoDB weird Forgein Key Name handling

[MDEV-2696] LP:1014750 - InnoDB weird Forgein Key Name handling Created: 2012-06-18 Updated: 2012-10-04 Resolved: 2012-10-04
Status:	Closed
Project:	MariaDB Server
Component/s:	None
Affects Version/s:	None
Fix Version/s:	None

Type:

Bug

Reporter:

Konstantin Stephan (Inactive)

Assignee:

Unassigned

Resolution:

Not a Bug

Votes:

Labels:

Launchpad

Attachments:

LPexportBug1014750.xml

LPexportBug1014750_my.cnf

Description

The followring DML is always processed right by Percona-XtraDB

ALTER TABLE `t1` ADD CONSTRAINT FOREIGN KEY (`col1`) REFERENCES `t2` (`col1`),
ADD CONSTRAINT FOREIGN KEY (`col2`) REFERENCES `t3` (`col1`),
ADD CONSTRAINT FOREIGN KEY (`col3`) REFERENCES `t4` (`col1`);

The same behaviour could be reproduced using a DDL.

But this engine becomes very strange when i like to give the f-keys custom-names like fk_col1 and so on.
Sometimes it helps to remove the underscore sometimes it helps to rename the f-key and so on.

So after about 2 hours i messed up finding any pattern so by now no f-key has a custom-name.

I think some kind of stristr is running an the aliases

Comments

Comment by Elena Stepanova [ 2012-06-18 ]

Re: InnoDB weird Forgein Key Name handling
Hi Konstantin,

Please

define what you mean by "engine becomes very strange" – quote the error you are getting, or, if there is no error, describe the behavior you observe;
provide the exact statement you are trying to execute which causes the error;
provide SHOW CREATE TABLE output for all existing tables involved in the statement;
describe the environment you are working on.

Please also note that you mentioned Percona Server, but created a bug in MariaDB bugtracker. Please point at the precise version of MariaDB/Percona/MySQL server that you are using.

Thank you.

Comment by Konstantin Stephan (Inactive) [ 2012-06-18 ]

Re: InnoDB weird Forgein Key Name handling
Hi Elena,

sometimes it throws error 1005 errno 121 and sometimes it throws error 1005 errno 150.
I had solved it in some cases by renaming the forgein key but in the end i let innodb choose one alone.
(fk_language_id -> fklanguageid or language)
That is what i mean with "engine becomes very strange".

I'm using 5.5.24-MariaDB-mariadb1~precise-log as MariaDB server and InnoDB version 1.1.8-24.1 with linux 3.3.6 (x86_64).
My cpu is AMD Phenom(tm) II X4 945 with 8gb ram.

The referenced column was the primary one in any cases.
I had checked the datatypes more than one time.

Also any needed indices exists.

One case was the followring:

table 1

CREATE TABLE `country` (
`id` int(11) NOT NULL,
`alias` varchar(64) NOT NULL,
`language_id` int(11) NOT NULL,
`name_i18n_item_id` bigint(20) NOT NULL,
`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`shortcode` varchar(3) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_language_id` (`language_id`),
KEY `idx_name_i18n_item_id` (`name_i18n_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

table 2

CREATE TABLE `language` (
`id` int(11) NOT NULL,
`short_code` varchar(64) NOT NULL,
`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`name_i18n_item_id` bigint(20) NOT NULL,
`alias` varchar(64) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_name_i18n_item_id` (`name_i18n_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

tried to add the constraint with

ALTER TABLE `country` ADD CONSTRAINT `fk_language_id` FOREIGN KEY (`language_id`) REFERENCES `language` (`id`);

this may not reproduce the issure at all but i had executed about 1k lines of sql.
The table that causes the error seems to change randomly over the time.
Also i added SET FOREIGN_KEY_CHECKS = 0; at the head of my sql but without any effect.

Can you move this ticket to the right point?

Comment by Elena Stepanova [ 2012-06-18 ]

Re: InnoDB weird Forgein Key Name handling
Hi Konstantin,

error 121 is "duplicate key", it might happen, for example, if you already have an identical constraint.
error 150 is "Foreign key constraint is incorrectly formed", it might happen, for example, if the referenced table doesn't have a key on the column you are trying to use (possibly the reason is different).

Try to run SHOW WARNINGS right after you got the error to see more details about the problem (especially when you are getting 150).

If you are sure there was no duplicate names or misformed constraints, and you can reliably reproduce the problem with 1K lines of SQL, please do so and attach the SQL and the resulting error to this report. If the SQL contains sensitive data, you can upload it to our private FTP, only MariaDB developers will be able to read it.

Comment by Konstantin Stephan (Inactive) [ 2012-06-18 ]

Re: InnoDB weird Forgein Key Name handling
Hi Elena,

here is a snippet that shows the behaviour without 1k lines but i'm now sure if that matters

CREATE DATABASE IF NOT EXISTS `foobar`;
use foobar;

DROP TABLE IF EXISTS `country_odd`;

CREATE TABLE `country_odd` (
`id` int(11) NOT NULL,
`alias` varchar(64) NOT NULL,
`language_id` int(11) NOT NULL,
`name_i18n_item_id` bigint(20) NOT NULL,
`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`shortcode` varchar(3) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_language_id` (`language_id`),
KEY `idx_name_i18n_item_id` (`name_i18n_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE DATABASE IF NOT EXISTS `foobar_2`;
use foobar_2;

DROP TABLE IF EXISTS `foobar`.`language_odd`;

CREATE TABLE `foobar`.`language_odd` (
`id` int(11) NOT NULL,
`short_code` varchar(64) NOT NULL,
`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`name_i18n_item_id` bigint(20) NOT NULL,
`alias` varchar(64) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_name_i18n_item_id` (`name_i18n_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `foobar`.`country_odd` ADD CONSTRAINT `fk_language_id` FOREIGN KEY (`language_id`) REFERENCES `foobar_2`.`language` (`id`);

Changing the last line to:

ALTER TABLE `foobar_2`.`country_odd` ADD CONSTRAINT `fk_language_id` FOREIGN KEY (`language_id`) REFERENCES `foobar_2`.`language` (`id`);

produce exactly the same error.

the (2) sql snippet(s) below fails with 121 withour the alias `fk_language_id` everything is fine.
Sorry i had forgotten that i use more than one database.

But that shouldn't be a problem at all or? But this seems to be problem after some research

Comment by Konstantin Stephan (Inactive) [ 2012-06-18 ]

Re: InnoDB weird Forgein Key Name handling
Ups some db names are wrong here is a corrected version that does the same

CREATE DATABASE IF NOT EXISTS `foobar`;
use foobar;

DROP TABLE IF EXISTS `country_odd`;

CREATE DATABASE IF NOT EXISTS `foobar_2`;
use foobar_2;

DROP TABLE IF EXISTS `language_odd`;

CREATE TABLE `language_odd` (
`id` int(11) NOT NULL,
`short_code` varchar(64) NOT NULL,
`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`name_i18n_item_id` bigint(20) NOT NULL,
`alias` varchar(64) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_name_i18n_item_id` (`name_i18n_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `foobar`.`country_odd` ADD CONSTRAINT `fk_language_id` FOREIGN KEY (`language_id`) REFERENCES `foobar_2`.`language_odd` (`id`);

Comment by Elena Stepanova [ 2012-06-18 ]

Re: InnoDB weird Forgein Key Name handling
Konstantin,

Please paste this snippet into your client program, so all statements are executed, copy the entire output starting from CREATE DATABASE IF NOT EXISTS `foobar` and up to and including the error you are receiving, also execute SHOW WARNINGS and copy its output to, and paste the whole output here.

Thanks.

Comment by Konstantin Stephan (Inactive) [ 2012-06-19 ]

Re: InnoDB weird Forgein Key Name handling
This is what i got:

MariaDB [foobar_2]> ALTER TABLE `foobar`.`country_odd` ADD CONSTRAINT `fk_language_id` FOREIGN KEY (`language_id`) REFERENCES `foobar_2`.`language_odd` (`id`);
ERROR 1005 (HY000): Can't create table 'foobar.#sql-692_24' (errno: 121)
MariaDB [foobar_2]>
MariaDB [foobar_2]> SHOW WARNINGS;
---------------------------------------------------------------

Level

Code

Message

---------------------------------------------------------------

Error

1005

Can't create table 'foobar.#sql-692_24' (errno: 121)

---------------------------------------------------------------
1 row in set (0.00 sec)

MariaDB [foobar_2]>

client: mysql Ver 15.1 Distrib 5.5.24-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Comment by Elena Stepanova [ 2012-06-19 ]

Re: InnoDB weird Forgein Key Name handling
Konstantin,

In comment #5 you pasted a set of statements which supposedly reproduces the problem in your environment. Once again, please copy-paste the ENTIRE OUTPUT starting from CREATE DATABASE, and up to the error message, as my previous comment said. If the short test from comment #5 does not work for you any longer and to reproduce the problem you need 1K statements as you wrote before, so be it – copy-paste it all and attach to this report as a file, but please do exactly that. Don't cut anything, don't make excerpts and don't replace any fragments with a verbal explanation.

Thank you.

Comment by Konstantin Stephan (Inactive) [ 2012-06-19 ]

Re: InnoDB weird Forgein Key Name handling
Okay here is the hole output:

Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 37
Server version: 5.5.24-MariaDB-mariadb1~precise-log mariadb.org binary distribution

This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS `foobar`;
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [(none)]> use foobar;
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
MariaDB [foobar]>
MariaDB [foobar]> DROP TABLE IF EXISTS `country_odd`;
Query OK, 0 rows affected (0.08 sec)

MariaDB [foobar]>
MariaDB [foobar]> CREATE TABLE `country_odd` (
-> `id` int(11) NOT NULL,
-> `alias` varchar(64) NOT NULL,
-> `language_id` int(11) NOT NULL,
-> `name_i18n_item_id` bigint(20) NOT NULL,
-> `created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
-> `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> `shortcode` varchar(3) NOT NULL,
-> PRIMARY KEY (`id`),
-> KEY `idx_language_id` (`language_id`),
-> KEY `idx_name_i18n_item_id` (`name_i18n_item_id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.32 sec)

MariaDB [foobar]>
MariaDB [foobar]> CREATE DATABASE IF NOT EXISTS `foobar_2`;
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [foobar]> use foobar_2;
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
MariaDB [foobar_2]>
MariaDB [foobar_2]> DROP TABLE IF EXISTS `language_odd`;
Query OK, 0 rows affected (0.07 sec)

MariaDB [foobar_2]>
MariaDB [foobar_2]> CREATE TABLE `language_odd` (
-> `id` int(11) NOT NULL,
-> `short_code` varchar(64) NOT NULL,
-> `created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
-> `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> `name_i18n_item_id` bigint(20) NOT NULL,
-> `alias` varchar(64) NOT NULL,
-> PRIMARY KEY (`id`),
-> KEY `idx_name_i18n_item_id` (`name_i18n_item_id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.29 sec)

MariaDB [foobar_2]>
MariaDB [foobar_2]> ALTER TABLE `foobar`.`country_odd` ADD CONSTRAINT `fk_language_id` FOREIGN KEY (`language_id`) REFERENCES `foobar_2`.`language_odd` (`id`);
ERROR 1005 (HY000): Can't create table 'foobar.#sql-692_25' (errno: 121)
MariaDB [foobar_2]>
MariaDB [foobar_2]> SHOW WARNINGS;
---------------------------------------------------------------

Level

Code

Message

---------------------------------------------------------------

Error

1005

Can't create table 'foobar.#sql-692_25' (errno: 121)

---------------------------------------------------------------
1 row in set (0.00 sec)

MariaDB [foobar_2]>

Comment by Konstantin Stephan (Inactive) [ 2012-06-19 ]

Re: InnoDB weird Forgein Key Name handling
After the CREATE DATABASE IF NOT EXISTS statements the followring warning were generated:

------------------------------------------------------------

Level

Code

Message

------------------------------------------------------------

Note

1007

Can't create database 'foobar_2'; database exists

------------------------------------------------------------
1 row in set (0.00 sec)

Comment by Elena Stepanova [ 2012-06-19 ]

Re: InnoDB weird Forgein Key Name handling
Konstantin,

Thank you.

When you are getting the error, are there any messages in the server error log?

Do you have general log turned on? If you don't and if possible, could you please turn it on (set global general_log=ON), repeat the same scenario which causes the problem, turn the log back OFF, and attach the contents of the general log to the report? The location of the general log can be found or modified in general_log_file variable.

Please also attach your my.cnf file.

Do you happen to have replication which involves this server?

Comment by Konstantin Stephan (Inactive) [ 2012-06-19 ]

Re: InnoDB weird Forgein Key Name handling
Hi Elena,

i have done what you said

As you could see in my.cnf the server writes a binlog but no one is reading it.
Also i had changed really nothing in this cfg-file.

so here is the general log

/usr/sbin/mysqld, Version: 5.5.24-MariaDB-mariadb1~precise-log (mariadb.org binary distribution). started with:
Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock
Time Id Command Argument
120619 13:10:48 42 Query show variables like "%general%"
120619 13:10:51 42 Query CREATE DATABASE IF NOT EXISTS `foobar`
42 Query SHOW WARNINGS
42 Query SELECT DATABASE()
42 Init DB foobar
42 Query show databases
42 Query show tables
42 Field List autocfg_entry
42 Field List autocfg_setup
42 Field List country
42 Field List country_odd
42 Field List email_verify_codes
42 Field List feature_feature_matrix
42 Field List feature_group
42 Field List features
42 Field List i18n_item
42 Field List i18n_item_group
42 Field List language
42 Field List language_odd
42 Field List login_source
42 Field List password_recover_codes
42 Field List proxy
42 Field List states
42 Field List user
42 Field List user_autocfg_entry_matrix
42 Field List user_features
42 Field List user_proxy_matrix
42 Field List user_virus_report_matrix
42 Field List virus_report
42 Query DROP TABLE IF EXISTS `country_odd`
42 Query CREATE TABLE `country_odd` (
`id` int(11) NOT NULL,
`alias` varchar(64) NOT NULL,
`language_id` int(11) NOT NULL,
`name_i18n_item_id` bigint(20) NOT NULL,
`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`shortcode` varchar(3) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_language_id` (`language_id`),
KEY `idx_name_i18n_item_id` (`name_i18n_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
42 Query CREATE DATABASE IF NOT EXISTS `foobar_2`
42 Query SELECT DATABASE()
42 Init DB foobar_2
42 Query show databases
42 Query show tables
42 Field List language_odd
42 Query DROP TABLE IF EXISTS `language_odd`
42 Query CREATE TABLE `language_odd` (
`id` int(11) NOT NULL,
`short_code` varchar(64) NOT NULL,
`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`name_i18n_item_id` bigint(20) NOT NULL,
`alias` varchar(64) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_name_i18n_item_id` (`name_i18n_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
120619 13:10:52 42 Query ALTER TABLE `foobar`.`country_odd` ADD CONSTRAINT `fk_language_id` FOREIGN KEY (`language_id`) REFERENCES `foobar_2`.`language_odd` (`id`)
42 Query SHOW WARNINGS
120619 13:11:02 42 Query set global general_log=off

Comment by Konstantin Stephan (Inactive) [ 2012-06-19 ]

Hi Elena,

i have done what you said

As you could see in my.cnf the server writes a binlog but no one is reading it.
Also i had changed really nothing in this cfg-file.

so here is the general log

Comment by Elena Stepanova [ 2012-06-19 ]

Re: InnoDB weird Forgein Key Name handling
Konstantin,

Thank you. As I can see from the general log output, foobar has quite a few tables. To get the error you are getting, it would be enough to have constraint named `fk_language_id` on any table in the schema (for example, on the `country` table).
It would explain why a small change in the name solves the problem.

An easy way to check it is execute

SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA='foobar'

and see which names you have there.

Comment by Konstantin Stephan (Inactive) [ 2012-06-19 ]

Re: InnoDB weird Forgein Key Name handling
Oh then i belive you can close this bug report.
I don't know that such names have to be unique over the hole schema.

But is it possible to change the error in cases like that for example to 'constraint name allready exists' ?

So thanks for your time i'll going ahead with this project and may give you an account when it's done.

Comment by Rasmus Johansson (Inactive) [ 2012-06-19 ]

Launchpad bug id: 1014750

Comment by Elena Stepanova [ 2012-06-19 ]

Re: InnoDB weird Forgein Key Name handling
Hi Konstantin,

The limitation about uniqueness of constraint names across all tables in the database is described in the manual (http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html):

"If the CONSTRAINT symbol clause is given, the symbol value must be unique in the database. If the clause is not given, InnoDB creates the name automatically."

For a more verbose message, you can always file a feature request, either here or in MySQL database, but I must warn you that previous ones didn't have much success so far, as it's considered a low-priority task (since error 121 is already 'duplicate key', which one can find out by running perror 121), see for example http://bugs.mysql.com/bug.php?id=13431.

Generated at Thu Feb 08 06:43:36 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.

[MDEV-2696] LP:1014750 - InnoDB weird Forgein Key Name handling Created: 2012-06-18 Updated: 2012-10-04 Resolved: 2012-10-04