Bug #40125 | Serious error while updating a record from a table! You will over write all! | ||
---|---|---|---|
Submitted: | 18 Oct 2008 11:51 | Modified: | 20 Oct 2008 10:38 |
Reporter: | Alexandru Ciobanu | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: DML | Severity: | S1 (Critical) |
Version: | 5.0.51, 4.1, 5.0, 5.1, 6.0 bzr | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[18 Oct 2008 11:51]
Alexandru Ciobanu
[18 Oct 2008 12:31]
Valeriy Kravchuk
Thank you for a problem report. Please, try to repeat with a newer version, 5.0.67. In case of the same roblem, please, send the results of: SHOW CREATE TABLE planul_de_realizare; I can not get the result like that with my InnoDB tables on a newer version using mysql command line client.
[18 Oct 2008 13:09]
Martin Friebe
Is it possible that this is an error (or incompatibility) in the SQL? If the given query is all in ONE single line (assuming the new lines are inserted in the bug-report only) then ,cofinantare=1.#INF0000000000000e+000 , .... would be treated as ,cofinantare=1. followed by a comment. (# makes the rest of the line a comment). This would move the "where" clause into the comment, and hence update the whole table
[18 Oct 2008 17:14]
Alexandru Ciobanu
CREATE TABLE `planul_de_realizare` ( `id_plan` int(10) unsigned NOT NULL auto_increment, `id_ln` int(10) unsigned zerofill NOT NULL COMMENT 'ne relationam cu contractul', `anul` varchar(4) NOT NULL, `etapa_nr` tinyint(2) unsigned zerofill NOT NULL, `activitate_nr` tinyint(2) unsigned zerofill NOT NULL, `faza_nr` tinyint(2) unsigned zerofill NOT NULL, `descriere` text NOT NULL, `categoria` int(10) unsigned zerofill NOT NULL, `rol` varchar(2) NOT NULL, `termen` date NOT NULL, `rezultate` text NOT NULL, `finantare` int(10) unsigned zerofill NOT NULL, `cofinantare` int(10) unsigned zerofill NOT NULL, `data_in` date NOT NULL, `data_out` date NOT NULL, `avans` int(10) unsigned zerofill NOT NULL COMMENT 'daca s-a platit un avans', `doc2` varchar(20) NOT NULL COMMENT 'numarul documentului cu care s-a platit un avans', `data_doc2` date NOT NULL COMMENT 'data la care s-a platit un avans', `data_docum` date NOT NULL COMMENT 'data documentului (pentru documentele aditionale, se pot modifica aici campurile cu sume sau termenele)', `datatrn` datetime NOT NULL, `id_oper` int(10) unsigned NOT NULL, `sters` tinyint(1) unsigned NOT NULL, `statia` varchar(60) NOT NULL, `dataserver` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`id_plan`), KEY `Index_2` (`id_plan`,`id_ln`) ) ENGINE=InnoDB AUTO_INCREMENT=61504 DEFAULT CHARSET=latin1 The line I found in the log is: 180229 Query UPDATE planul_de_realizare SET etapa_nr=3.00000000000000000e+000 , activitate_nr=1.00000000000000000e+000 ,faza_nr=0.00000000000000000e+000 , descriere='Realizare model experimental' ,rol='P3' ,rezultate='model experimental ' , finantare=1.05600000000000000e+005 ,cofinantare=1.#INF0000000000000e+000 , categoria=4.90000000000000000e+001 ,datatrn='2008-10-14 14:58:21' , statia='Ver.3 EMS # Administrator IP:192.168.3.32 ' WHERE id_plan=46261 This command will overwrite all the records in the table! The SQL interpreter will not be able to see the WHERE clause, beacuse of the value 1.#INF0000000000000e+000 of "cofinantare" field !!!
[19 Oct 2008 8:07]
Sveta Smirnova
Thank you for the report. Verified as described. Test case: create table t1(f1 int, f2 int); insert into t1 values(1,2), (3,4), (5,6); update t1 set f1=7, f2=1.#INF0000000000000e+000 where f1=1; select * from t1; Result: create table t1(f1 int, f2 int); insert into t1 values(1,2), (3,4), (5,6); update t1 set f1=7, f2=1.#INF0000000000000e+000 where f1=1; select * from t1; f1 f2 7 1 7 1 7 1
[20 Oct 2008 10:38]
Sergei Golubchik
Not a bug, see the explanation given by Martin.