MySQL Bugs: #31135: alter table x alter column y set default m does not pass default value to engine

Bug #31135	alter table x alter column y set default m does not pass default value to engine
Submitted:	21 Sep 2007 15:59	Modified:	12 Sep 2008 8:09
Reporter:	Jan Lindström	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0, 5.1	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Problem is the same for all storage engines implementing foreign keys i.e:

mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table t0;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t0(a int not null, primary key (a)) engine=innodb;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> create table t1(a int not null default 2, foreign key (a) references t0(a) on update set default on delete set default) engine=innodb;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> insert into t0 values (1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into t1 values (1);
Query OK, 1 row affected (0.00 sec)

--
-- Information about default change is not passed to storage engine!
--

mysql> alter table t1 alter column a set default 3;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> update t0 set a = 5 where a = 1;
Query OK, 1 row affected (0.37 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t0;
+---+
| a |
+---+
| 2 | 
| 5 | 
+---+
2 rows in set (0.00 sec)

mysql> select * from t1;
+---+
| a |
+---+
| 1 | 
+---+
1 row in set (0.00 sec)

How to repeat:
drop table t1;
drop table t0;
create table t0(a int not null, primary key (a)) engine=innodb;
create table t1(a int not null default 2, foreign key (a) references t0(a) on update set default on delete set default) engine=innodb;
insert into t0 values (1),(2);
insert into t1 values (1);
alter table t1 alter column a set default 3;
update t0 set a = 5 where a = 1;
select * from t0;
select * from t1;

Suggested fix:
Add a new handler function to pass information about column default value change.

Thank you for the bug report. How you was able to create the table t1 on your
sample, in the current source server I got:

mysql> create table t1(a int not null default 2, foreign key (a) references t0(a) on update set
    -> default on delete set default) engine=innodb;
ERROR 1005 (HY000): Can't create table 'test.t1' (errno: 150)

I did not do anything special, I have few days old clone of 5.1.23 directly from bitkeeper and I have build it using ./BUILD/compile-pentium-debug. I retried the example, and still the same results:

mysql> drop table t1;
Query OK, 0 rows affected (0.01 sec)

mysql> drop table t0;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t0(a int not null, primary key (a)) engine=innodb;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> create table t1(a int not null default 2, foreign key (a) references
    -> t0(a) on update set default on delete set default) engine=innodb;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> insert into t0 values (1),(2);
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into t1 values (1);
Query OK, 1 row affected (0.00 sec)

mysql> alter table t1 alter column a set default 3;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> update t0 set a = 5 where a = 1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t0;
+---+
| a |
+---+
| 2 | 
| 5 | 
+---+
2 rows in set (0.00 sec)

mysql> select * from t1;
+---+
| a |
+---+
| 1 | 
+---+
1 row in set (0.00 sec)

It seems that for some reason innodb was disabled and MySQL naturally changed storage engine to myisam. However, bug described is still there e.g. if you use soliddb storage engine. I can disable support for set default referential action but that's only a workaround. I rather would see a interface which MySQL would use to inform change of attribute default value's.

Thank you for the report.

There are warnings in your output:

mysql> create table t0(a int not null, primary key (a)) engine=innodb;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> create table t1(a int not null default 2, foreign key (a) references
    -> t0(a) on update set default on delete set default) engine=innodb;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

Seems you haven't turned on support of InnoDB. For MyISAM ON UPDATE clause would not work. Please issue SHOW WARNINGS and SHOW INNODB STATUS to be sure engine InnoDB is supported in your environment.

Fine, lets change the how to repeat to:

drop table t1;
drop table t0;
create table t0(a int not null, primary key (a)) engine=soliddb;
create table t1(a int not null default 2, foreign key (a) references t0(a) on update set default on delete set default) engine=soliddb;
insert into t0 values (1),(2);
insert into t1 values (1);
alter table t1 alter column a set default 3;
update t0 set a = 5 where a = 1;
select * from t0;
select * from t1;

Now if I do not return a error in ha_soliddb::create if foreign key referential action is set default. I will see exactly the same results as in MyISAM. This is because information about alter table t1 alter column a set default 3 is not informed to soliddb storage engine in any way. Thus, soliddb storage engine thinks that default value for the column a is still 2. Finally, this information lost results inconsistent database state. Therefore, this is a critical bug.

Thank you for the feedback.

Which version of MySQL with soliddb support you use? With 5.0.37 I still have same error:

create table t1(a int not null default 2, foreign key (a) references t0(a) on update set default on delete set default) engine=soliddb' failed: 1005: Can't create table './test/t1.frm' (errno: 150)

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".