Re: ALTER TABLE - how to fix truncated data?
Gleb, 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 | +-+--+-+ 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. 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ALTER TABLE - how to fix truncated data?
In article [EMAIL PROTECTED], Martijn Tonies [EMAIL PROTECTED] 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ALTER TABLE - how to fix truncated data?
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): 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 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 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, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ALTER TABLE - how to fix truncated data?
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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ALTER TABLE - how to fix truncated data?
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ALTER TABLE - how to fix truncated data?
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: http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html 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 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, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ALTER TABLE - how to fix truncated data?
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 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ALTER TABLE - how to fix truncated data?
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, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ALTER TABLE - how to fix truncated data?
Hello. 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 | +-+--+-+ mysql select 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, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ALTER TABLE - how to fix truncated data?
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 -- I started using something better than the standard back when IBM advertised OS/2 Warp on TV. As Linux matured I made the transition from OS/2 v4 to Linux. You don't have to accept less than you deserve. Use the Power of the Penguin Registered Linux user #316735 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
