My bad. I was renaming some columns in a table. I incorrectly set the
type to decimal(4,2) and the data was truncated/hosed. Is there a way to
recover the data. I tried a system backup from yesterday. That changed
nothing. I have already set the column type back to the correct
settings. I am running MySQL v5.0.13 on a Debian Etch(Testing) box.
TIA,
Jim Seymour

Hello.

I don't think that it is possible to recover the data from the new
table, because ALTER operation creates a new table and fills it with
data (and truncates the data). But why did the system backup not help
you? Could you describe the situation more in detail. If you haven't
flushed you binary logs, you may want to make an attempt to recover the
data from them.

Jim Seymour wrote:
My bad. I was renaming some columns in a table. I incorrectly set the
type to decimal(4,2) and the data was truncated/hosed. Is there a way to
recover the data. I tried a system backup from yesterday. That changed
nothing. I have already set the column type back to the correct
settings. I am running MySQL v5.0.13 on a Debian Etch(Testing) box.

TIA,

Jim Seymour

Gleb,

I don't think that it is possible to recover the data from the new
table, because ALTER operation creates a new table and fills it with
data (and truncates the data). But why did the system backup not help
you? Could you describe the situation more in detail. If you haven't
flushed you binary logs, you may want to make an attempt to recover the
data from them.

As a sidenote - shouldn't MySQL raise an error when data gets truncated?

If the truncation happens silently, chances are you'll never find out until
someone does a report or whatever?!

With regards,

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

Hello.

As a sidenote - shouldn't MySQL raise an error when data gets >truncated?

MySQL raises a warning after such ALTER operation. See:

mysqlcreate table dt(a decimal(4,1));
Query K, 0 rows affected (0.01 sec)

mysqlinsert into dt set a=999.1;
Query K, 1 row affected (0.00 sec)

mysqlalter table dt change a a decimal(2,1);
Query K, 1 row affected, 1 warning (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 1

mysqlshow warnings;

| Level | Code | Message |

| Warning | 1264 | of range value adjusted for column 'a' at row 1 |

mysqlselect version();
++
| version() |
++
| 5.0.13-rc-debug-log |
++

Martijn Tonies wrote:

As a sidenote - shouldn't MySQL raise an error when data gets truncated?

If the truncation happens silently, chances are you'll never find out until
someone does a report or whatever?!

With regards,

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

Gleb,

As a sidenote - shouldn't MySQL raise an error when data gets
>truncated?
>
MySQL raises a warning after such ALTER operation. See:

mysqlcreate table dt(a decimal(4,1));
Query K, 0 rows affected (0.01 sec)

mysqlinsert into dt set a=999.1;
Query K, 1 row affected (0.00 sec)

mysqlalter table dt change a a decimal(2,1);
Query K, 1 row affected, 1 warning (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 1

mysqlshow warnings;

| Level | Code | Message |

| Warning | 1264 | 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?

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

With regards,

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

In article <00d001c5d472$efc6a360$c802a8c0@martijnws>,
"Martijn Tonies" <m.tonies (AT) upscene (DOT) comwrites:

mysqlinsert into dt set a=999.1;
>Query K, 1 row affected (0.00 sec)
>
mysqlalter table dt change a a decimal(2,1);
>Query K, 1 row affected, 1 warning (0.02 sec)
>Records: 1 Duplicates: 0 Warnings: 1
>
mysqlshow warnings;
>
>| Level | Code | Message |
>
>| Warning | 1264 | 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?

IM, 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 TEMPRARY TABLE t1 (
i TINYINT
);

INSERT INT t1 VALUES (42);

SELECT * FRM t1;
-- , shows 42

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

SELECT * FRM t1;
-- , shows 127

mysqlinsert into dt set a=999.1;
>Query K, 1 row affected (0.00 sec)
>>
mysqlalter table dt change a a decimal(2,1);
>Query K, 1 row affected, 1 warning (0.02 sec)
>Records: 1 Duplicates: 0 Warnings: 1
>>
mysqlshow warnings;
>>

>| Level | Code | Message
|
>>

>| Warning | 1264 | 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?

IM, 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

Guess they want to sell support contracts, ey?

Seriously, if you give a user enough rope to hang themselves
AND hand them a chair to stand on, better make sure you
have a way to deal with the corpses.

CREATE TEMPRARY TABLE t1 (
i TINYINT
);

INSERT INT t1 VALUES (42);

SELECT * FRM t1;
-- , shows 42

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

SELECT * FRM t1;
-- , shows 127

Yeah, total rubbish.

Do this in your application - by accident - and explain your
boss that the totals are wrong or that he had an input value
of "4242" and got "127" in return. Rubbish.

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

With regards,

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

Martijn Tonies wrote:
| Warning | 1264 | 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?
IM, 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 NT 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_IGNRE_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.

As a sidenote - shouldn't MySQL raise an error when data gets
>truncated?
>
MySQL raises a warning after such ALTER operation. See:

mysqlcreate table dt(a decimal(4,1));
Query K, 0 rows affected (0.01 sec)

mysqlinsert into dt set a=999.1;
Query K, 1 row affected (0.00 sec)

mysqlalter table dt change a a decimal(2,1);
Query K, 1 row affected, 1 warning (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 1

mysqlshow warnings;

| Level | Code | Message |

| Warning | 1264 | of range value adjusted for column 'a' at row 1 |

btw, it seems MySQL CAN do errors, check:

Jigal notified me of this.

With regards,

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

Hello.

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

MySQL 5.0 has this ability. Check STRICT_ALL_TABLES and
STRICT_TRANS_TABLES SQL modes at:


>CREATE TEMPRARY TABLE t1 (
>i TINYINT
>);
>>
>INSERT INT t1 VALUES (42);
>>
>SELECT * FRM t1;
>-- , shows 42
>>
>UPDATE t1 SET i = 4242;
>-- SHW WARNINGS;
>>
>SELECT * FRM t1;
>-- , shows 127

Yeah, total rubbish.

Do this in your application - by accident - and explain your
boss that the totals are wrong or that he had an input value
of "4242" and got "127" in return. Rubbish.

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

With regards,

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