Обсуждение: Disruptive corruption of data during an update
[Using postgresql-8.4.8-0squeeze1 under GNU-linux/debian] Hi all! an entire table of my db has been corrupted as a consequence of this erroneous update command: -------- mydb=> update bibl set argomento='curatori', autore='DELLA VOLPE Galvano \ (con due capitoli aggiunti e note di) where n_prog=3285; mydb'> '; UPDATE 12845 mydb=> -------- Having an adequate back-up I think I'll be able to recover the data, but can someone explain what the hell happened? I realized the single quote was missing after the closing parenthesis and just wrote it plus a second semicolon ';' .... Regards, ennio -- [Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?// Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ] (°|°) Ricevo solo messaggi Content-Type: plain/text (no html o multipart). )=( !!! --> e-mail a mio nome via OE (M$) sono false e infette <-- !!!
On 29 March 2012 16:47, Ennio-Sr <nasr.laili@tin.it> wrote: > -------- > mydb=> update bibl set argomento='curatori', autore='DELLA VOLPE Galvano \ > (con due capitoli aggiunti e note di) where n_prog=3285; > mydb'> '; > > UPDATE 12845 > mydb=> > -------- > > Having an adequate back-up I think I'll be able to recover the data, but > can someone explain what the hell happened? Because of the single quote being at the wrong place, your query was processed as though not having a where clause at all, and the ENTIRE table got updated with the text: DELLA VOLPE Galvano \ (con due capitoli aggiunti e note di) where n_prog=3285; in the autore field (and similarly in the argomento field). What I typically do is to attach "where 1 = 0" to inhibit a query from making any changes, if I find an error in a multiline query and do not want to Control-C out of psql. Binand
* Binand Sethumadhavan <binand@gmx.net> [290312, 17:03]: > > Because of the single quote being at the wrong place, your query was > processed as though not having a where clause at all, and the ENTIRE > table got updated with the text: > [...] > in the autore field (and similarly in the argomento field). > Oh yes, I did realize that, but too late ;( > What I typically do is to attach "where 1 = 0" to inhibit a query from > making any changes, if I find an error in a multiline query and do not > want to Control-C out of psql. > > Binand Thanks for your suggestion, regards, ennio -- [Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?// Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ] (°|°) Ricevo solo messaggi Content-Type: plain/text (no html o multipart). )=( !!! --> e-mail a mio nome via OE (M$) sono false e infette <-- !!!
On Thursday, March 29, 2012 05:03:03 PM Binand Sethumadhavan wrote: > What I typically do is to attach "where 1 = 0" to inhibit a query from > making any changes, if I find an error in a multiline query and do not > want to Control-C out of psql. It is also a good idea to always always do a "begin" before doing any manual update, and only commit changes if it looks like it did the right thing.