List:General Discussion« Previous MessageNext Message »
From:Jigal van Hemert Date:October 19 2005 10:24am
Subject:Re: ALTER TABLE - how to fix truncated data?
View as plain text  
Martijn Tonies wrote:
>>>>| 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):

> Yeah yeah ... so the MySQL design philosophy is that users
> never make mistakes...

Even in more serious cases MySQL silently "modifies" data and structures:
A large database with an INTEGER column with NULL-'values' allowed was 
modified to include this field in the PRIMARY key. The column definition 
was automagically modified to NOT NULL and all NULL-'values' where 
converted to 0 (zero).
Yeah, emmm, well,.... we actually used the NULLs as "no value" (like it 
is supposed to be used AFAIK) and there was no way anymore to 
distinguish between NULL and 0. Luckily this was done on a test database 
and we only had to spend half an hour or so to restore the table from a 
backup.

It would have been very nice to know of this action before it was 
completed, to say the least.

> If a value doesn't fit (in the "domain" of "tinyint"), an exception
> should be raised. Plain and simple.

I fully agree. Maybe an option SQL_IGNORE_WARNINGS or something along 
those lines should be introduced to force the execution of such queries. 
At least most users will be prevented from shooting themselves in the 
foot unless they specificly specify to do so.

Regards, Jigal.
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