Bug #14742 ALTER TABLE: unclear error message for a NULL value.
Submitted: 8 Nov 2005 13:14 Modified: 8 Nov 2005 17:07
Reporter: Kenneth Wagner Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Errors Severity:S4 (Feature request)
Version:5.0.15-nt OS:Windows (Windows XP Pro)
Assigned to: CPU Architecture:Any

[8 Nov 2005 13:14] Kenneth Wagner
Description:
Attempting to change a column via:

mysql> ALTER TABLE `qwiqit`.`item` MODIFY COLUMN `oz` DECIMAL(5,2) NOT NULL DEFAULT 0;

begets this error. Cannot modify the column to "NOT NULL".

ERROR 1265 (01000): Data truncated for column 'oz' at row 3

How to repeat:
Create a column in MySQL an leave NOT NULL unset.

See the "oz" column above. It was created without the "NOT NULL" setting.

It cannot be altered to "NOT NULL"

Suggested fix:
A more complete explanation with a hint as to how to set to "NOT NULL" 

or

Have it work.
[8 Nov 2005 14:10] Valeriy Kravchuk
I think, you missed NULL values that are in that column already. Let me expain you with small example:

mysql> use test
Database changed
mysql> create table t1 (c1 decimal(5,2));
Query OK, 0 rows affected (0.29 sec)

mysql> alter table t1 modify column c1 decimal(5,2) not null;
Query OK, 0 rows affected (0.25 sec)
Records: 0  Duplicates: 0  Warnings: 0

So, it works (for empty table...)!

mysql> create table t2 (c1 decimal(5,2));
Query OK, 0 rows affected (0.07 sec)

mysql> insert into t2 values (2.5), (null);
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> alter table t2 modify column c1 decimal(5,2) not null;
ERROR 1265 (01000): Data truncated for column 'c1' at row 2

Now it does not work. Why? Because you have NULL value at least in one of the rows:

mysql> select * from t2;
+------+
| c1   |
+------+
| 2.50 |
| NULL |
+------+
2 rows in set (0.02 sec)

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

I hope whese will help you to solve the problem in your case.
[8 Nov 2005 14:23] Kenneth Wagner
Yes. I found there were already "NULL" values in the column.

Would it not make more sense to have the error message show what would be truncated.

Example: Found NULL, will truncate at "N". 

Or something like that.
[8 Nov 2005 17:07] Valeriy Kravchuk
No, NULL is a special value, and it is not truncated really (nothing to truncate). 

So, I'd better call this "ERROR 1265 (01000): Data truncated for column 'c1' at row 2" error message incorrect (a bug) in case of ALTER TABLE trying to add NOT NULL constraints and column having NULL values.

Let's make a feature request out of this report. Please, change the error message in this case to a more meaningfull one.
[19 Oct 2006 15:27] J Rabbit
I've just made the same mistake and been confused by the error message until I found this bug report on google. It would be helpful to change the message to something more accurate.

Also, in the situation where a default value is given, should it still be an error if there are nulls present in the column - could the alter table statement not convert existing NULLs to the supplied default value?
[24 Jul 2007 15:33] ali sen
any suggestion if there a NULL values are already there ?
[4 Oct 2008 10:00] Konstantin Osipov
A suggestion for those who would like to get rid of NULL values with your ALTER:
use ALTER IGNORE.