MySQL Bugs: #36865: alter table problem on innodb tables

Bug #36865	alter table problem on innodb tables
Submitted:	21 May 2008 23:19	Modified:	22 Jun 2008 4:35
Reporter:	Sedat Onur ORAKOGLU	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S1 (Critical)
Version:	5.0.51a-community-nt (32bits)	OS:	Windows (Vista 64)
Assigned to:		CPU Architecture:	Any

Description:
##here is the problem

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 123
Server version: 5.0.51a-community-nt MySQL Community Edition (GPL)

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

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql> create table test.prm (
    -> id int unsigned not null auto_increment primary key,
    -> prm varchar(10)) engine=innodb;
Query OK, 0 rows affected (0.10 sec)

mysql> create table test.main (
    -> id int unsigned not null auto_increment primary key,
    -> prmid int unsigned default null,
    -> nan varchar(10),
    -> constraint main_prmid foreign key (prmid) references test.prm (id)
    -> ) engine=innodb;
Query OK, 0 rows affected (0.11 sec)

mysql> insert into test.prm (prm) values ("aa"),("bb"),("cc"),("dd");
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from test.prm;
+----+------+
| id | prm  |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
|  4 | dd   |
+----+------+
4 rows in set (0.00 sec)

mysql> insert into test.main (prmid,nan) values
    -> (null,"aaa"),(2,"bbb"),(null,"ccc"),(4,"ddd");
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from test.main;
+----+-------+------+
| id | prmid | nan  |
+----+-------+------+
|  1 |  NULL | aaa  |
|  2 |     2 | bbb  |
|  3 |  NULL | ccc  |
|  4 |     4 | ddd  |
+----+-------+------+
4 rows in set (0.00 sec)

mysql> update test.main set prmid=0 where prmid is null;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`test/main`, CONSTRAINT `main_prmid` FOREIGN KEY (`prmid`) REFERENCES `prm
` (`id`))

## there is no problem.
## but if we try to modify the column `prmid`

mysql> alter table test.main
    -> modify column prmid int unsigned not null default 4;
Query OK, 4 rows affected, 2 warnings (0.23 sec)
Records: 4  Duplicates: 0  Warnings: 2

mysql> select * from test.main;
+----+-------+------+
| id | prmid | nan  |
+----+-------+------+
|  1 |     0 | aaa  |
|  2 |     2 | bbb  |
|  3 |     0 | ccc  |
|  4 |     4 | ddd  |
+----+-------+------+
4 rows in set (0.00 sec)

## i see here 2 problems. 
##  1.the columns default value is 4 not 0; //
##  2.there is no record having id=0 on the `prm` table.

How to repeat:
always

Thank you for a problem report. 

What sql_mode value do you have? Look:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot test -P3308
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.0.60-enterprise-gpl-nt-log MySQL Enterprise Server (GPL)

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

mysql> create table test.prm (
    ->   id int unsigned not null auto_increment primary key,
    ->   prm varchar(10)) engine=innodb;
Query OK, 0 rows affected (0.50 sec)

mysql>
mysql> create table test.main (
    ->   id int unsigned not null auto_increment primary key,
    ->   prmid int unsigned default null,
    ->   nan varchar(10),
    ->   constraint main_prmid foreign key (prmid) references test.prm (id)
    -> ) engine=innodb;
Query OK, 0 rows affected (0.13 sec)

mysql>
mysql> insert into test.prm (prm) values ("aa"),("bb"),("cc"),("dd");
Query OK, 4 rows affected (0.08 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql>
mysql> select * from test.prm;
+----+------+
| id | prm  |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
|  4 | dd   |
+----+------+
4 rows in set (0.00 sec)

mysql>
mysql> insert into test.main (prmid,nan) values
    -> (null,"aaa"),(2,"bbb"),(null,"ccc"),(4,"ddd");
Query OK, 4 rows affected (0.08 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql>
mysql> select * from test.main;
+----+-------+------+
| id | prmid | nan  |
+----+-------+------+
|  1 |  NULL | aaa  |
|  2 |     2 | bbb  |
|  3 |  NULL | ccc  |
|  4 |     4 | ddd  |
+----+-------+------+
4 rows in set (0.00 sec)

mysql>
mysql> update test.main set prmid=0 where prmid is null;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`test/main`, CONSTRAINT `main_prmid` FOREIGN KEY (`prmid`) REFERENCES `prm
` (`id`))
mysql>
mysql> alter table test.main
    -> modify column prmid int unsigned not null default 4;
ERROR 1265 (01000): Data truncated for column 'prmid' at row 1
mysql> select @@sql_mode\G
*************************** 1. row ***************************
@@sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)

You will not get this problem in strict mode.

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".