#At file:///home/dlenev/src/bzr/mysql-6.1-bg41720/
2704 Dmitry Lenev 2009-02-03
Fix for bug #41720 "Foreign keys: wrong error message for
geometry reference" and similar problem for BLOB columns.
In --foreign-key-all-engines mode attempt to create foreign
key on column of one of GEOMETRY or BLOB types led to wrong
error message about different types of child and parent
columns (even altough these columns had the same type).
The problem occured due to discrepancy between char_length
attribute of Create_field describing GEOMETRY/BLOB columns
in the child table, which come from parser, and describing
columns in parent table, which are generated from Field
objects. The former have char_length equal to 0 in the most
cases while the latter has char_length set to the maximum
size of data which can be stored in column of this type.
This fix tries to avoid intrusive changes and instead of
trying to unify handling of Create_field for BLOB/GEOMETRY
columns changes Create_field::is_fk_compatible() to ignore
length attribute for BLOB/GEOMETRY columns. It is safe to
do so as length for such columns is fully defined by their
type code anyway.
Note that this fix only improves situation with error message.
It is still impossible to create foreign key containing BLOB/
GEOMETRY columns as it is impossible to create PRIMARY/UNIQUE
KEY with such columns.
modified:
mysql-test/r/foreign_key_all_engines.result
mysql-test/t/foreign_key_all_engines.test
sql/field.cc
per-file messages:
mysql-test/r/foreign_key_all_engines.result
Added test coverage for bug #41720 "Foreign keys: wrong error
message for geometry reference".
mysql-test/t/foreign_key_all_engines.test
Added test coverage for bug #41720 "Foreign keys: wrong error
message for geometry reference".
sql/field.cc
Create_field::is_fk_compatible():
As length of BLOB/GEOMETRY columns is fully defined by
their type code we can safely ignore it when checking if
two columns can correspond each other in a foreign key.
=== modified file 'mysql-test/r/foreign_key_all_engines.result'
--- a/mysql-test/r/foreign_key_all_engines.result 2009-01-30 13:41:12 +0000
+++ b/mysql-test/r/foreign_key_all_engines.result 2009-02-03 06:27:45 +0000
@@ -1672,6 +1672,29 @@ Error 1292 Incorrect date value: '2009-0
drop tables t1, t2;
set sql_mode= @old_sql_mode;
#
+# Test for bug #41720 "Foreign keys: wrong error message for geometry
+# reference". Also provides coverage for similar issue with BLOBs.
+#
+set @@rand_seed1=10000000,@@rand_seed2=1000000;
+drop tables if exists t1, t2;
+# Let us try to create foreign key on GEOMETRY column.
+# This attempt should fail since it is impossible to create PRIMARY
+# or UNIQUE key on such a column, but at least it should produce an
+# appropriate error.
+create table t1 (s1 geometry not null);
+create table t2 (s1 geometry references t1 (s1));
+ERROR 42000: Foreign key error: Constraint 'fk_t2_11f06': Parent columns don't correspond to a PRIMARY KEY or an UNIQUE constraint
+drop table t1;
+# We have same situation for BLOB types.
+create table t1 (s1 blob not null);
+create table t2 (s1 blob references t1 (s1));
+ERROR 42000: Foreign key error: Constraint 'fk_t2_46vh3': Parent columns don't correspond to a PRIMARY KEY or an UNIQUE constraint
+drop table t1;
+create table t1 (s1 tinyblob not null);
+create table t2 (s1 tinyblob references t1 (s1));
+ERROR 42000: Foreign key error: Constraint 'fk_t2_hu4cw': Parent columns don't correspond to a PRIMARY KEY or an UNIQUE constraint
+drop table t1;
+#
# Test for bug #41761 "Foreign keys: integrity breach if UPDATE and
# non-transactional+self-referencing" and some other aspects of
# handling of self-referencing FKs for non-transactional tables.
=== modified file 'mysql-test/t/foreign_key_all_engines.test'
--- a/mysql-test/t/foreign_key_all_engines.test 2009-01-30 13:41:12 +0000
+++ b/mysql-test/t/foreign_key_all_engines.test 2009-02-03 06:27:45 +0000
@@ -1538,6 +1538,33 @@ set sql_mode= @old_sql_mode;
--echo #
+--echo # Test for bug #41720 "Foreign keys: wrong error message for geometry
+--echo # reference". Also provides coverage for similar issue with BLOBs.
+--echo #
+set @@rand_seed1=10000000,@@rand_seed2=1000000;
+--disable_warnings
+drop tables if exists t1, t2;
+--enable_warnings
+--echo # Let us try to create foreign key on GEOMETRY column.
+--echo # This attempt should fail since it is impossible to create PRIMARY
+--echo # or UNIQUE key on such a column, but at least it should produce an
+--echo # appropriate error.
+create table t1 (s1 geometry not null);
+--error ER_FK_PARENT_KEY_NO_PK_OR_UNIQUE
+create table t2 (s1 geometry references t1 (s1));
+drop table t1;
+--echo # We have same situation for BLOB types.
+create table t1 (s1 blob not null);
+--error ER_FK_PARENT_KEY_NO_PK_OR_UNIQUE
+create table t2 (s1 blob references t1 (s1));
+drop table t1;
+create table t1 (s1 tinyblob not null);
+--error ER_FK_PARENT_KEY_NO_PK_OR_UNIQUE
+create table t2 (s1 tinyblob references t1 (s1));
+drop table t1;
+
+
+--echo #
--echo # Test for bug #41761 "Foreign keys: integrity breach if UPDATE and
--echo # non-transactional+self-referencing" and some other aspects of
--echo # handling of self-referencing FKs for non-transactional tables.
=== modified file 'sql/field.cc'
--- a/sql/field.cc 2009-01-28 11:54:58 +0000
+++ b/sql/field.cc 2009-02-03 06:27:45 +0000
@@ -10127,11 +10127,6 @@ bool Create_field::is_fk_compatible(Crea
/* String types for which charset and length matter.*/
case MYSQL_TYPE_STRING:
case MYSQL_TYPE_VAR_STRING:
- case MYSQL_TYPE_BLOB:
- case MYSQL_TYPE_TINY_BLOB:
- case MYSQL_TYPE_MEDIUM_BLOB:
- case MYSQL_TYPE_LONG_BLOB:
- case MYSQL_TYPE_GEOMETRY:
case MYSQL_TYPE_VARCHAR:
/*
General check below works even for BIT type which always
@@ -10140,6 +10135,16 @@ bool Create_field::is_fk_compatible(Crea
case MYSQL_TYPE_BIT:
return (charset == field->charset &&
char_length == field->char_length);
+ /*
+ For BLOB types length is defined by type code so the only thing
+ which matters is charset.
+ */
+ case MYSQL_TYPE_BLOB:
+ case MYSQL_TYPE_TINY_BLOB:
+ case MYSQL_TYPE_MEDIUM_BLOB:
+ case MYSQL_TYPE_LONG_BLOB:
+ case MYSQL_TYPE_GEOMETRY:
+ return (charset == field->charset);
/* Unsupported types. */
case MYSQL_TYPE_NULL:
case MYSQL_TYPE_TIMESTAMP:
| Thread |
|---|
| • bzr commit into mysql-6.1-fk branch (dlenev:2704) Bug#41720 | Dmitry Lenev | 3 Feb |
