Bug #13778 If FOREIGN_KEY_CHECKS=0, one can create inconsistent FOREIGN KEYs
Submitted: 5 Oct 2005 18:41 Modified: 18 Jun 2010 2:01
Reporter: Ricardo Proni Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:5.0/4.1.15 OS:Windows (Windows XP Professional SP2 Engl)
Assigned to: CPU Architecture:Any

[5 Oct 2005 18:41] Ricardo Proni
Description:
MySQL: 5.0.13 RC.
I´ve created the table below (described in "TABLE") in my database.
I´ve tried to change, in Query Browser, the size of the column 'nom_classificacao_fiscal' to 1024, and I got the error describe below as "ERROR1".
Then, my table is no more. If I try to create it again, I got the error (in INNODB STATUS) described in "ERROR2".

----------
TABLE:
CREATE TABLE `CLASSIFICACAO_FISCAL` (
  `cod_classificacao_fiscal` char(10) NOT NULL default '0000.00.00',
  `nom_classificacao_fiscal` varchar(2048) NOT NULL default '',
  `qtd_aliquota` int(10) unsigned NOT NULL default '0',
  `nom_logon_inc` varchar(255) NOT NULL default 'master',
  `nom_logon_alt` varchar(255) NOT NULL default 'master',
  `nom_logon_exc` varchar(255) default NULL,
  `cod_ip_inc` char(15) NOT NULL default '127.0.0.1',
  `cod_ip_alt` char(15) NOT NULL default '127.0.0.1',
  `cod_ip_exc` char(15) default NULL,
  `dat_inc` datetime NOT NULL default '0000-00-00 00:00:00',
  `dat_alt` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `dat_exc` datetime default NULL,
  PRIMARY KEY  (`cod_classificacao_fiscal`),
  KEY `ix_classificacao_fiscal_01` (`nom_logon_inc`),
  KEY `ix_classificacao_fiscal_02` (`nom_logon_alt`),
  KEY `ix_classificacao_fiscal_03` (`nom_logon_exc`),
  CONSTRAINT `fk_classificacao_fiscal_01` FOREIGN KEY (`nom_logon_inc`) REFERENCES `ADMINISTRADOR` (`nom_logon`),
  CONSTRAINT `fk_classificacao_fiscal_02` FOREIGN KEY (`nom_logon_alt`) REFERENCES `ADMINISTRADOR` (`nom_logon`),
  CONSTRAINT `fk_classificacao_fiscal_03` FOREIGN KEY (`nom_logon_exc`) REFERENCES `ADMINISTRADOR` (`nom_logon`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

----------
ERROR1:
"Error while executing query. ALTER TABLE `Mensor`.`CLASSIFICACAO_FISCAL` MODIFY COLUMN `nom_classificacao_fiscal` VARCHAR(1024) CHARACTER SET latin1_swedish_ci NOT NULL DEFAULT ";

MySQL Error Number 1025
Error on rename of '.\mensor\#sql-fc_3' to '.\mensor\CLASSIFICAO_FISCAL'
(errno: 150)

----------
ERROR2:
051005 15:29:04 Error in foreign key constraint creation for table `men
ificacao_fiscal`.
A foreign key constraint of name `mensor/fk_classificacao_fiscal_01`
already exists. (Note that internally InnoDB adds 'databasename/'
in front of the user-defined constraint name).
Note that InnoDB's FOREIGN KEY system tables store
constraint names as case-insensitive, with the
MySQL standard latin1_swedish_ci collation. If you
create tables or databases whose names differ only in
the character case, then collisions in constraint
names can occur. Workaround: name your constraints
explicitly with unique names.
------------

How to repeat:
 - Create the table as in "Description";
 - Alter the size of the column 'nom_classificacao_fiscal' from 2048 to 1024;

Suggested fix:
No idea.
[6 Oct 2005 12:07] Ricardo Proni
Error screen

Attachment: bug.JPG (image/jpeg, text), 133.94 KiB.

[6 Oct 2005 13:33] MySQL Verification Team
Thank you for the bug report.

This isn't a QB bug, instead InnoDB deletes the *.frm table and when
the alter table fails only exists on dictionary data. Repeatable
with mysql client using the source provide:

c:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.13-rc-nt

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

mysql> source c:/scripts/Mensor_v5.sql
ERROR 1008 (HY000): Can't drop database 'mensor'; database doesn't exist
Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

<cut>

mysql> show tables like "classificacao_fiscal";
+-----------------------------------------+
| Tables_in_mensor (classificacao_fiscal) |
+-----------------------------------------+
| classificacao_fiscal                    |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE `mensor`.`classificacao_fiscal` MODIFY COLUMN
    -> `nom_classificacao_fiscal` VARCHAR(1024)
    -> CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '';
ERROR 1025 (HY000): Error on rename of '.\mensor\#sql-c60_1' to '.\mensor\classificacao_fiscal' (errno: 150)
mysql> show tables like "classificacao_fiscal";
Empty set (0.00 sec)

mysql>
[6 Oct 2005 14:11] Heikki Tuuri
Hmm... does MySQL delete the .frm file before it is sure the rename succeeded?

Regards,

Heikki
[24 Oct 2005 19:26] Ricardo Proni
Does MySQL went into Production with this bug?
[3 Nov 2005 23:59] [ name withheld ]
I just saw this behavior on 5.0.15, luckily on a non-production database.  This is very disheartening as we just made the decision to switch to MySQL from PostgreSQL... but, if a simple ALTER can trash a table, we may revise that decision.
[4 Nov 2005 15:05] Heikki Tuuri
Hi!

I assume that this bug is not present in 4.1?

Regards,

Heikki
[4 Nov 2005 15:34] MySQL Verification Team
Heikki,

It happens also on 4.1. I modified the original script for to create the table
changing the varchar column to text, the original script has a varchar
column > 255. Then:

mysql> use mensor
Database changed
mysql> show tables like "classificacao_fiscal";
+-----------------------------------------+
| Tables_in_mensor (classificacao_fiscal) |
+-----------------------------------------+
| classificacao_fiscal                    |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE `mensor`.`classificacao_fiscal` MODIFY COLUMN
    -> `nom_classificacao_fiscal` VARCHAR(255)
    -> CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '';
ERROR 1025 (HY000): Error on rename of '.\mensor\#sql-300_2' to '.\mensor\classificacao_fiscal' (errno: 150)
mysql> show tables like "classificacao_fiscal";
Empty set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.15-nt |
+-----------+
1 row in set (0.00 sec)

mysql>
[4 Nov 2005 15:36] Sergei Golubchik
Of course MySQL does not remove frm before rename succeeds.
renaming in ALTER works like this:

1. rename the table to temp. name
2. rename newly created table to the table name
3. delete temp table from the step. 1

on any failure everything is restored.
[9 Nov 2005 12:22] Osku Salerma
I can't reproduce this on latest 5.0:

mysql> \. mens.sql
...
mysql> use Mensor;
Database changed
mysql> show tables like "CLASSIFICACAO_FISCAL";
+-----------------------------------------+
| Tables_in_Mensor (CLASSIFICACAO_FISCAL) |
+-----------------------------------------+
| CLASSIFICACAO_FISCAL                    |
+-----------------------------------------+
1 row in set (0.00 sec)
mysql> ALTER TABLE CLASSIFICACAO_FISCAL MODIFY COLUMN nom_classificacao_fiscal VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '';
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show tables like "CLASSIFICACAO_FISCAL";
+-----------------------------------------+
| Tables_in_Mensor (CLASSIFICACAO_FISCAL) |
+-----------------------------------------+
| CLASSIFICACAO_FISCAL                    |
+-----------------------------------------+
1 row in set (0.00 sec)

Could this be some kind of case-sensitivity bug that only affects windows?
[9 Nov 2005 20:44] Heikki Tuuri
Osku,

please test the exact sequence of statements that Miguel showed.

Though I guess you are right, and this is a table name case problem that occurs on Windows only. It is dangerous to use different cases to access a table, you may provoke MySQL bugs that way.

I may check to tomorrow in my Windows build what MySQL does.

Regards,

Heikki
[10 Nov 2005 8:02] Osku Salerma
I can't use Miguel's statements because of case-sensitivity issues:

mysql> ALTER TABLE `mensor`.`classificacao_fiscal` MODIFY COLUMN `nom_classificacao_fiscal` VARCHAR(1024) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '';
ERROR 1146 (42S02): Table 'mensor.classificacao_fiscal' doesn't exist

If I use the correct case:

mysql> ALTER TABLE `Mensor`.`CLASSIFICACAO_FISCAL` MODIFY COLUMN `nom_classificacao_fiscal` VARCHAR(1024) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '';
Query OK, 0 rows affected (0.07 sec)
[12 Nov 2005 2:57] Heikki Tuuri
Ricardo,

how were you able to create these tables?

CREATE TABLE `PRODUTO` (
...
  `cod_classificacao_fiscal` int(10) unsigned NOT NULL default '1',
...
CONSTRAINT `fk_produto_15` FOREIGN KEY (`cod_classificacao_fiscal`) REFERENCES `classificacao_fiscal` (`cod_classificacao_fiscal`),
...

CREATE TABLE `CLASSIFICACAO_FISCAL` (
  `cod_classificacao_fiscal` char(10) NOT NULL default '0000.00.00',

Look, an integer column is referencing a character column! That is not allowed.

If we SET FOREIGN_KEY_CHECKS=0, InnoDB DOES allow creation of such nonsensical foreign key reference:

mysql> set foreign_key_checks=0;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t11 (a int primary key, b int, foreign key (b) references t1
0(a)) type = innodb;
Query OK, 0 rows affected, 1 warning (0.23 sec)

mysql> create table t10(a char(10) primary key, b varchar(20)) type = innodb;
Query OK, 0 rows affected, 1 warning (0.16 sec)

mysql> set foreign_key_checks=1;
Query OK, 0 rows affected (0.00 sec)

This should be fixed: in the latter CREATE TABLE we should ALWAYS check the compatibility of types.

Regards,

Heikki
[12 Nov 2005 3:17] Heikki Tuuri
Hi!

I am able to repeat the bug with 5.0.10a without any case changes in the table name.

mysql> set foreign_key_checks=1;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table t10 modify column b varchar(10);
ERROR 1025 (HY000): Error on rename of '.\test\#sql-8ec_3' to '.\test\t10' (errn
o: 150)
mysql> show tables like 't10';
Empty set (0.03 sec)

The reason for the bug is that after the ALTER fails because of bad data type in the foreign key constraint, InnoDB does not allow renaming the old table "#sql-8ec_3" back to t10, because it notices that also then table t10 would have a wrong data type in the foreign key constraint!

innodb_table_monitor tells that the temp table is left there:

--------------------------------------
TABLE: name test/#sql2-8ec-3, id 0 33, columns 6, indexes 1, appr.rows 0
  COLUMNS: a: DATA_CHAR prtype 2 len 10 prec 0; b: DATA_VARCHAR prtype 1 len 20
prec 0; DB_ROW_ID: DATA_SYS prtype 8 len 6 prec 0; DB_TRX_ID: DATA_SYS prtype 8
len 6 prec 0; DB_ROLL_PTR: DATA_SYS prtype 8 len 7 prec 0;
  INDEX: name PRIMARY, id 0 48, fields 1/4, type 3
   root page 3, appr.key vals 0, leaf pages 1, size pages 1
   FIELDS:  a DB_TRX_ID DB_ROLL_PTR b
--------------------------------------

Thus, the table was not dropped. It just was renamed to a temp table. And InnoDB does not allow renaming that temp table back to t10.

Regards,

Heikki
[12 Nov 2005 3:25] Heikki Tuuri
Hi!

I am able to reproduce the bug also on a Linux version of 5.0.15. I wonder how Osku was not able to repeat it?

Regards,

Heikki

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.15-rc-log

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

mysql> set foreign_key_checks=0;
Query OK, 0 rows affected (0.06 sec)

mysql> create table t11 (a int primary key, b int, foreign key (b) references
    -> t10(a)) type = innodb;
Query OK, 0 rows affected, 1 warning (0.08 sec)

mysql> create table t10(a char(10) primary key, b varchar(20)) type = innodb;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> set foreign_key_checks=1;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table t10 modify column b varchar(10);
ERROR 1025 (HY000): Error on rename of './test/#sql-3467_1' to './test/t10' (err
no: 150)
mysql>
[13 Nov 2005 17:09] Heikki Tuuri
Hi!

Marko introduced this bug to 4.1.12 and 5.0.5 when he fixed bug #9802. He removed ALL type checks if FOREIGN_KEY_CHECKS=0, while he should only have removed the charset check!

Osku, please change the code so that the name of the parameter check_types is changed to check_charsets and ship the parameter down to the function below, where it should only affect charset checking.

Note that even after this fix, one can bump into the original problem of this bug report. If misuses FOREIGN_KEY_CHECKS=0 to create foreign key constraints on non-matching charsets, he will in ALTER TABLE get his table renamed to a temp table. To fix that, one should in the 'rollback' phase in a failing ALTER switch off the charset checking, so that MySQL can rename the temp table back to the original table name. Osku, please study if this is easy to do. Can you easily deduce if MySQL is 'rolling back' a failing ALTER?

Regards,

Heikki

/*****************************************************************
Returns TRUE if two types are equal for comparison purposes. */

ibool
cmp_types_are_equal(
/*================*/
                                /* out: TRUE if the types are considered
                                equal in comparisons */
        dtype_t*        type1,  /* in: type 1 */
        dtype_t*        type2)  /* in: type 2 */
{
        if (dtype_is_non_binary_string_type(type1->mtype, type1->prtype)
            && dtype_is_non_binary_string_type(type2->mtype, type2->prtype)) {

                /* Both are non-binary string types: they can be compared if
                and only if the charset-collation is the same */

                if (dtype_get_charset_coll(type1->prtype)
                                == dtype_get_charset_coll(type2->prtype)) {
                        return(TRUE);
                }

                return(FALSE);
        }

        if (dtype_is_binary_string_type(type1->mtype, type1->prtype)
            && dtype_is_binary_string_type(type2->mtype, type2->prtype)) {

                /* Both are binary string types: they can be compared */

                return(TRUE);
        }

        if (type1->mtype != type2->mtype) {

                return(FALSE);
        }

        if (type1->mtype == DATA_INT
            && (type1->prtype & DATA_UNSIGNED)
                != (type2->prtype & DATA_UNSIGNED)) {

                /* The storage format of an unsigned integer is different
                from a signed integer: in a signed integer we OR
                0x8000... to the value of positive integers. */

                return(FALSE);
        }

        if (type1->mtype == DATA_INT && type1->len != type2->len) {

                return(FALSE);
        }

        return(TRUE);
}
[14 Nov 2005 9:01] Osku Salerma
I have analyzed why I was not able to reproduce the failure. It's a case-sensitivity issue as I guessed:

CREATE TABLE `PRODUTO` (
...
CONSTRAINT `fk_produto_15` FOREIGN KEY (`cod_classificacao_fiscal`) REFERENCES `classificacao_fiscal` (`cod_classificacao_fiscal`)
...

CREATE TABLE `CLASSIFICACAO_FISCAL` (
...

The FK reference is to a lowercase table name while the table is created with an uppercase name, and this somehow masks the reported bug.
[14 Nov 2005 10:29] Heikki Tuuri
Osku,

now I see :). On Unix, table names are case-sensitive. If you give the table name in wrong case in the FOREIGN KEY clause, InnoDB assumes that you are referencing a non-existent table. And if FOREIGN_KEY_CHECKS=0, the creation succeeds :). Thus on Linux, you were testing an entirely different thing. The tables never referenced each other.

Beck to the original bug:

On about line 3825 of row0mysql.c:

"
                err = dict_load_foreigns(new_name, trx->check_foreigns);

                if (row_is_mysql_tmp_table_name(old_name)) {

                        /* MySQL is doing an ALTER TABLE command and it
                        renames the created temporary table to the name
                        of the original table. In the ALTER TABLE we maybe
                        created some FOREIGN KEY constraints for the temporary
                        table. But we want to load also the foreign key
                        constraint definitions for the original table name. */
"

I think we should make the second parameter of  dict_load_foreigns() TRUE unless row_is_mysql_tmp_table_name(old_name) is true. We only want to switch off some of the type checking in an ALTER, not in a RENAME.

On about line 2125:

"
        err = dict_create_foreign_constraints(trx, sql_string, name,
                reject_fks);

        if (err == DB_SUCCESS) {
                /* Check that also referencing constraints are ok */
                err = dict_load_foreigns(name, trx->check_foreigns);
        }
"

I think the second parameter of dict_load_foreigns() should be TRUE.

We only want to switch off some of the type checking in an ALTER, not in a CREATE.

When you have fixed this, test that one can still do an ALTER of latin1 to utf8 when FOREIGN_KEY_CHECKS=0 (like in the description of the bug that Marko fixed). But a CREATE or RENAME where latin1 references utf8 should fail always. Also an ALTER of CHAR to INT should fail always. We only wanted to relax the checks temporarily if FOREIGN_KEY_CHECKS=0 for char set conversions, not for more radical type conversions.

Regards,

Heikki
[14 Nov 2005 10:39] Heikki Tuuri
Hi!

This bug is more serious than just type checking. Marko's patch switched off ALL checks in dict_foreign_add_to_cache() in dict0dict.c. One can create in many ways inconsistent foreign keys then.

dict0dict.c:
"
        if (for_in_cache->referenced_table == NULL && ref_table) {
                dict_index_t*   types_idx;
                if (check_types) {
                        types_idx = for_in_cache->foreign_index;
                } else {
                        types_idx = NULL;
                }
                index = dict_foreign_find_index(ref_table,
                        (const char**) for_in_cache->referenced_col_names,
                        for_in_cache->n_fields,
                        types_idx);
"

Regards,

Heikki
[14 Nov 2005 17:00] Heikki Tuuri
Hi!

Osku has a patch for blocking in appropriate CREATE TABLE when FOREIGN_KEY_CHECKS=0.

If FOREIGN_KEY_CHECKS=0, only non-matching charsets in an ALTER are accepted, no other inconsistency. We accept non-matching charsets in order to ease a table conversion latin1 -> UTF-8. Note that if the user forgets to convert all columns to matching charsets, then he is still facing the original problem of this bug report: a table will end up as a temp table in a failing ALTER.

Osku will next study if we can make the 'rollback phase' in a failing ALTER to always succeed, so that the in a failing ALTER the original table would not be left as a temp table.

Regards,

Heikki
[15 Nov 2005 9:13] Osku Salerma
I found the place where the failing ALTER is undone. Line 3763 in sql_table.cc in 5.0:

  if (mysql_rename_table(old_db_type,db,table_name,db,old_name))
  {
    error=1;
    VOID(quick_rm_table(new_db_type,new_db,tmp_name));
  }
  else if (mysql_rename_table(new_db_type,new_db,tmp_name,new_db,
			      new_alias))
  {						// Try to get everything back
    error=1;
    VOID(quick_rm_table(new_db_type,new_db,new_alias));
    VOID(quick_rm_table(new_db_type,new_db,tmp_name));
    VOID(mysql_rename_table(old_db_type,db,old_name,db,alias));
  }

It's the last branch, and the last statement is the one that we would like to always succeed. I don't see a way to identify in InnoDB code whether we're doing that rename or e.g. the rename of the preceding if-statement (both rename tmp-tables to something else), so if we really want to do this we would probably need to add a flag parameter to mysql_rename_table and handler::rename_table that it calls, something like "bool check_charsets". It seems a pretty big kludge to me, but then, the whole approach of being able to turn off any part of constraint checking is, so maybe it's just more of the same.

Should I do this, or is there a better way, or should we just ignore the whole issue?
[15 Nov 2005 14:14] Osku Salerma
I committed my patch to our 5.0 tree and will port it to 4.1 and 5.1 as well. We decided not to fix the issue where renaming the original table from a temp name back fails since it's a minor issue and somewhat easy to recover from (for example, SELECT * FROM `#sql2-2f00-2` INTO OUTFILE 'filename').
[23 Nov 2005 14:22] Jon Stephens
Need to know specific 3-part version numbers of all versions to which this fix is pushed (not just committed). Otherwise, we don't know in which changelog(s) we should document the fix.

Thanks!
[15 Dec 2005 11:32] Alexander Ivanov
Fixed in 4.1.17, 5.0.18
[18 Dec 2005 4:48] Paul DuBois
Noted in 4.1.17, 5.0.18 changelogs.
[22 Feb 2008 10:51] suchita shastry
can one add constraint while creating a table?
[5 May 2010 15:11] Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[6 May 2010 1:38] Paul DuBois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[28 May 2010 6:12] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:40] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 7:08] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[29 May 2010 2:42] Paul DuBois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[17 Jun 2010 12:18] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:05] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:45] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)