From: Martijn Tonies
Date: October 19 2005 12:35pm
Subject: Re: ALTER TABLE - how to fix truncated data?
List-Archive: http://lists.mysql.com/mysql/190569
Message-Id: <02db01c5d4a9$9968f040$c802a8c0@martijnws>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit


> > As a sidenote - shouldn't MySQL raise an error when data gets
>truncated?
>
> MySQL raises a warning after such ALTER operation. See:
>
> mysql> create table dt(a decimal(4,1));
> Query OK, 0 rows affected (0.01 sec)
>
> mysql> insert into dt set a=999.1;
> Query OK, 1 row affected (0.00 sec)
>
> mysql> alter table dt change a a decimal(2,1);
> Query OK, 1 row affected, 1 warning (0.02 sec)
> Records: 1  Duplicates: 0  Warnings: 1
>
> mysql> show warnings;
> +---------+------+-----------------------------------------------------+
> | Level   | Code | Message                                             |
> +---------+------+-----------------------------------------------------+
> | Warning | 1264 | Out of range value adjusted for column 'a' at row 1 |
> +---------+------+-----------------------------------------------------+

btw, it seems MySQL CAN do errors, check:
http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

Jigal notified me of this.

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com