List:General Discussion« Previous MessageNext Message »
From:Harald Fuchs Date:October 19 2005 7:47am
Subject: Re: ALTER TABLE - how to fix truncated data?
View as plain text  
In article <00d001c5d472$efc6a360$c802a8c0@martijnws>,
"Martijn Tonies" <m.tonies@stripped> writes:

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 |
>> +---------+------+-----------------------------------------------------+

> Could be me ... but isn't this a little too late?

> eg: AFTER you have lost your data?

> IMO, it should raise an error UNLESS you force it to truncate the data.

This would contradict the "MySQL design philosophy" (others call it
simply "gotcha") that the user should know what he's doing and the
DBMS tries its best to obey.  Consider this (version 4.1.14):

  CREATE TEMPORARY TABLE t1 (
    i TINYINT
  );

  INSERT INTO  t1 VALUES (42);

  SELECT * FROM t1;
  -- Ok, shows 42

  UPDATE t1 SET i = 4242;
  -- SHOW WARNINGS;

  SELECT * FROM t1;
  -- Oops, shows 127

Thread
ALTER TABLE - how to fix truncated data?Jim Seymour17 Oct
  • Re: ALTER TABLE - how to fix truncated data?Gleb Paharenko18 Oct
  • Re: ALTER TABLE - how to fix truncated data?Martijn Tonies18 Oct
    • Re: ALTER TABLE - how to fix truncated data?Gleb Paharenko18 Oct
  • Re: ALTER TABLE - how to fix truncated data?Martijn Tonies19 Oct
  • Re: ALTER TABLE - how to fix truncated data?Harald Fuchs19 Oct
  • Re: ALTER TABLE - how to fix truncated data?Martijn Tonies19 Oct
    • Re: ALTER TABLE - how to fix truncated data?Jigal van Hemert19 Oct
    • Re: ALTER TABLE - how to fix truncated data?Gleb Paharenko19 Oct
  • Re: ALTER TABLE - how to fix truncated data?Martijn Tonies19 Oct