On 26/04/2009 5:47 AM, Gene wrote: > Every now and again, we have a database that gets corrupt in the field (bad > coding on our end, not sqlite). >
Hi Gene, The obvious question: why not fix the bad code? What does 'PRAGMA integrity_check;' say about these corrupt databases? > When we get one of these corrupt databases, we recover what we can by get > one row at a time by rowid, like 'select * from mytable where rowid = 1' and > we inc the row number every time. Each row we successfully recover we > insert into a clean database. How do you know when to stop incrementing the row number? Does 'select min(rowid), max(rowid) from mytable' give you believable answers? What happens when you do 'select * from mytable' ? Approximately how many rows are there? How many 'select * from mytable where rowid = <some_number>' attempts fail, and for what reasons (previously deleted, some other result code(s))? Are the failures restricted to a relatively small range of rowids? > Works pretty well, except over time we've added more and more columns, each > one that has to be pulled and bound to get it into the new table. What is causing this "pull and bind" problem, the fact that some columns weren't present initially? or just the sheer number of columns i.e. you need to "pull and bind" all columns (not just the later additions)? In any case, please explain what you mean by "pulled" and "bound". > We tried > to do an 'attach' so we could do a 'select into' the clean database directly > from the corrupt one. But as soon as we attempt to 'attach' the corrupt > database, we understandable get a 'database is malformed' error. It's not quite so understandable why 'select * from mytable where rowid = 1' doesn't get an error. > Is there an easier way to pull the good records out of a corrupt database > and put them into a new one without binding each column by hand? Can you give us an example of a row or two of (a) what you get from the 'select * from mytable where rowid = <whatever>' (b) the insert statement that you need to do to insert that data into the clean database? Doesn't have to be real data -- e.g. assume 3 columns initially, now grown to 5. What rules/procedure/recipe do you follow when producing (b) from (a) by hand? Assuming that 'select * from mytable' doesn't work, and subject to understanding the pulling and binding by hand thing, I would have thought the solution would look something like this: Write a script that loops around doing 'select * from mytable where rowid = ?' on a connection to your corrupt database and doing 'insert into mytable values(?,?,?,?,? etc etc etc)' on a connection to your clean database. HTH, John _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
