#At file:///home/dlenev/src/bzr/mysql-6.1-bg35527/
2678 Dmitry Lenev 2008-06-27
Fix for bug #35527 "Foreign keys: crash if self-reference in
create ... as select"
Execution of CREATE TABLE ... SELECT which contained foreign key
definitions led to server crash due to failing debug assertion
or errors about illegal constraint names for production builds.
The first problem was caused by the fact that table list
elements corresponding to the parent tables of foreign
key constraints were added to both global table list and
outer select of the statement but were not ready/intended
for opening tables (didn't have lock_type properly set).
The second problem occured because code implementing CREATE
TABLE ... SELECT statement didn't call code generating names
for constraints without explicit names.
Full solution for both this problems will be implemented on one
of the the later milestones of WL#148 "Foreign keys". This patch
is a simple workaround which prevents server from asserting and
emitting confusing errors.
modified:
mysql-test/r/foreign_key.result
mysql-test/r/foreign_key_all_engines.result
mysql-test/t/foreign_key.test
mysql-test/t/foreign_key_all_engines.test
sql/sql_insert.cc
sql/sql_lex.cc
sql/sql_lex.h
sql/sql_yacc.yy
per-file messages:
mysql-test/r/foreign_key.result
Added test case for bug #35527 "Foreign keys: crash if
self-reference in create ... as select".
mysql-test/r/foreign_key_all_engines.result
Added test case for bug #35527 "Foreign keys: crash if
self-reference in create ... as select".
mysql-test/t/foreign_key.test
Added test case for bug #35527 "Foreign keys: crash if
self-reference in create ... as select".
mysql-test/t/foreign_key_all_engines.test
Added test case for bug #35527 "Foreign keys: crash if
self-reference in create ... as select".
sql/sql_insert.cc
create_table_from_items():
In --foreign-key-all-engines mode empty list of foreign keys
to be created in order to avoid problems until we have reached
milestone at which proper support of foreign keys is added
for CREATE TABLE ... SELECT statement.
sql/sql_lex.cc
Introduced LEX::unlink_all_tables_except_first() method which
allows to exclude all tables except the first one from the
statement's global table list and outer select.
sql/sql_lex.h
Introduced LEX::unlink_all_tables_except_first() method which
allows to exclude all tables except the first one from the
statement's global table list and outer select.
sql/sql_yacc.yy
Unlink tables which are added for foreign key constraints from
global table list and outer select of CREATE TABLE and CREATE
TABLE SELECT statements.
=== modified file 'mysql-test/r/foreign_key.result'
--- a/mysql-test/r/foreign_key.result 2008-06-23 07:14:00 +0000
+++ b/mysql-test/r/foreign_key.result 2008-06-27 05:11:56 +0000
@@ -102,3 +102,12 @@ select @@foreign_key_all_engines;
select @@global.foreign_key_all_engines;
@@global.foreign_key_all_engines
0
+drop tables if exists t1, t2, t3;
+create table t1 (s1 int primary key);
+create table t2 (s1 int primary key);
+create table t3 (s1 int references t1(s1)) as select s1 from t2;
+drop table t3;
+create table t3 (s1 int references t1(s1)) as select s1 from t1;
+drop table t3;
+create table t3 (s1 int primary key references t3(s1)) as select s1 from t1;
+drop tables t1, t2, t3;
=== modified file 'mysql-test/r/foreign_key_all_engines.result'
--- a/mysql-test/r/foreign_key_all_engines.result 2008-06-25 20:11:52 +0000
+++ b/mysql-test/r/foreign_key_all_engines.result 2008-06-27 05:11:56 +0000
@@ -419,3 +419,12 @@ Warning 1674 Foreign key warning: Constr
drop table t2;
create table t2 (fk int, constraint c1 foreign key (fk) references t1 (pk));
drop tables t1, t2;
+drop tables if exists t1, t2, t3;
+create table t1 (s1 int primary key);
+create table t2 (s1 int primary key);
+create table t3 (s1 int references t1(s1)) as select s1 from t2;
+drop table t3;
+create table t3 (s1 int references t1(s1)) as select s1 from t1;
+drop table t3;
+create table t3 (s1 int primary key references t3(s1)) as select s1 from t1;
+drop tables t1, t2, t3;
=== modified file 'mysql-test/t/foreign_key.test'
--- a/mysql-test/t/foreign_key.test 2008-06-23 07:14:00 +0000
+++ b/mysql-test/t/foreign_key.test 2008-06-27 05:11:56 +0000
@@ -165,3 +165,22 @@ show variables like 'foreign_key_all_eng
select * from information_schema.global_variables where variable_name='foreign_key_all_engines';
select @@foreign_key_all_engines;
select @@global.foreign_key_all_engines;
+
+
+#
+# Additional test for bug #35527 "Foreign keys: crash if self-reference
+# in create ... as select". Statements below should not crash or produce
+# errors about wrong constraint names in both --foreign-key-all-engines=0|1
+# modes.
+#
+--disable_warnings
+drop tables if exists t1, t2, t3;
+--enable_warnings
+create table t1 (s1 int primary key);
+create table t2 (s1 int primary key);
+create table t3 (s1 int references t1(s1)) as select s1 from t2;
+drop table t3;
+create table t3 (s1 int references t1(s1)) as select s1 from t1;
+drop table t3;
+create table t3 (s1 int primary key references t3(s1)) as select s1 from t1;
+drop tables t1, t2, t3;
=== modified file 'mysql-test/t/foreign_key_all_engines.test'
--- a/mysql-test/t/foreign_key_all_engines.test 2008-06-25 20:11:52 +0000
+++ b/mysql-test/t/foreign_key_all_engines.test 2008-06-27 05:11:56 +0000
@@ -359,3 +359,22 @@ drop table t2;
# Specifying name in CONSTRAINT clause only should not emit warning.
create table t2 (fk int, constraint c1 foreign key (fk) references t1 (pk));
drop tables t1, t2;
+
+
+#
+# Test for bug #35527 "Foreign keys: crash if self-reference in create ...
+# as select".
+#
+--disable_warnings
+drop tables if exists t1, t2, t3;
+--enable_warnings
+create table t1 (s1 int primary key);
+create table t2 (s1 int primary key);
+# Statements below should not crash or produce errors about
+# illegal constraint names.
+create table t3 (s1 int references t1(s1)) as select s1 from t2;
+drop table t3;
+create table t3 (s1 int references t1(s1)) as select s1 from t1;
+drop table t3;
+create table t3 (s1 int primary key references t3(s1)) as select s1 from t1;
+drop tables t1, t2, t3;
=== modified file 'sql/sql_insert.cc'
--- a/sql/sql_insert.cc 2008-06-11 15:37:09 +0000
+++ b/sql/sql_insert.cc 2008-06-27 05:11:56 +0000
@@ -3380,6 +3380,14 @@ static TABLE *create_table_from_items(TH
DBUG_RETURN(0);
}
+ /*
+ This is a temporary work-around which should go away once appropriate
+ "DDL checks and changes" milestone of WL#148 "Foreign keys"is
+ implemented.
+ */
+ if (opt_fk_all_engines)
+ alter_info->foreign_key_list.empty();
+
tmp_table.alias= 0;
tmp_table.timestamp_field= 0;
tmp_table.s= &share;
=== modified file 'sql/sql_lex.cc'
--- a/sql/sql_lex.cc 2008-06-23 07:14:00 +0000
+++ b/sql/sql_lex.cc 2008-06-27 05:11:56 +0000
@@ -2768,6 +2768,31 @@ void st_lex::link_first_table_back(TABLE
}
+/**
+ Unlink all tables except the first one from the global table list
+ and outer select.
+
+ @note We assume that the first table in outer select and global
+ list exist and are the same.
+
+ @return Head of the list with unlinked tables.
+*/
+
+TABLE_LIST *st_lex::unlink_all_tables_except_first()
+{
+ TABLE_LIST *first_table= select_lex.get_table_list();
+ TABLE_LIST *rest_tables= first_table->next_local;
+ /* Remove tables from outer select. */
+ select_lex.table_list.elements= 1;
+ select_lex.table_list.next= (uchar**) &(first_table->next_local);
+ first_table->next_local= 0;
+ /* And from global table list. */
+ DBUG_ASSERT(first_table == query_tables);
+ first_table->next_global= 0;
+ query_tables_last= &(first_table->next_global);
+ return rest_tables;
+}
+
/*
cleanup lex for case when we open table by table for processing
=== modified file 'sql/sql_lex.h'
--- a/sql/sql_lex.h 2008-06-23 07:14:00 +0000
+++ b/sql/sql_lex.h 2008-06-27 05:11:56 +0000
@@ -1793,6 +1793,7 @@ typedef struct st_lex : public Query_tab
TABLE_LIST *unlink_first_table(bool *link_to_local);
void link_first_table_back(TABLE_LIST *first, bool link_to_local);
void first_lists_tables_same();
+ TABLE_LIST *unlink_all_tables_except_first();
bool can_be_merged();
bool can_use_merged();
=== modified file 'sql/sql_yacc.yy'
--- a/sql/sql_yacc.yy 2008-06-25 19:52:25 +0000
+++ b/sql/sql_yacc.yy 2008-06-27 05:11:56 +0000
@@ -3773,7 +3773,7 @@ create2:
;
create2a:
- field_list ')' opt_create_table_options
+ create_field_list ')' opt_create_table_options
opt_partitioning
create3 {}
| opt_partitioning
@@ -4709,6 +4709,19 @@ udf_type:
| REAL {$$ = (int) REAL_RESULT; }
| DECIMAL_SYM {$$ = (int) DECIMAL_RESULT; }
| INT_SYM {$$ = (int) INT_RESULT; }
+ ;
+
+
+create_field_list:
+ field_list
+ {
+ /*
+ Until one of the later milestones of WL#148 "Foreign key" is
+ implemented we should parent tables for foreign key constraints
+ from the table lists.
+ */
+ Lex->unlink_all_tables_except_first();
+ }
;
field_list:
| Thread |
|---|
| • bzr commit into mysql-6.1-fk branch (dlenev:2678) Bug#35527, WL#148 | Dmitry Lenev | 27 Jun |
