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.

lg

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

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.

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.

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.

Launchpad bug id: 1014750