Thread: A Haunted Database
Here's a mystery I hope someone can solve for me. We are entering blocks of HTML into a table called bodyparts. We use PHP3 to break up these blocks into several chunks to keep the length below the maximum. When the end user calls up the section, the "bodyparts" are extracted and re-assembled. The output pages work fine . . . for a while. We set up the output pages during the day, check them for accuracy and go to bed thinking we have done a great job. Then , in the middle of the night, something happens and when we awake, we find the HTML has been scrambled like so many breakfast eggs. Not all sections are scrambled. In fact it is the same sections every single time. So we re-enter the data, check it, assume we are done, and then the same thing happens the next day. To gather some empirical evidence, I ran pg_dump at 7pm on the offending table. I check the output pages at midnight the same evening, and they all were good. When I got back in front of the computer at 9am, the pages were scrambled again. I ran pg_dump a second time to a separate file. The file sizes were different (insert scary music here). No one had touched the database or the pages. I reloaded the data and everything is back to normal. But I suspect it will happen again tonight and I am afraid. Does anyone know what inhuman entity might be causing this to occur?
I know that PostgreSQL does not support logging of all transactions in a separate file. Unfortunately, I need this feature. As we all know, the documentation is rather incomplete and unorganized (can't blame anybody, I prefer programming to documenting as well =:-) ). That's why I hope to get some help here: What would be the best way to implement it? - triggers (if yes, how efficiently?) ??? I would be most grateful for a short example. Regards Horst
Do you have any automated program accessing the database overnight? IE a malfunctioning backup or vacuum script? You might also want to do a diff -C1 first_dump second_dump to see what is actually being changed. At 11:40 AM 4/8/00, Robert Cleveland wrote: >Here's a mystery I hope someone can solve for me. > >We are entering blocks of HTML into a table called bodyparts. We use PHP3 to >break up these blocks into several chunks to keep the length below the >maximum. When the end user calls up the section, the "bodyparts" are >extracted and re-assembled. > >The output pages work fine . . . for a while. We set up the output pages >during the day, check them for accuracy and go to bed thinking we have done >a great job. Then , in the middle of the night, something happens and when >we awake, we find the HTML has been scrambled like so many breakfast eggs. >Not all sections are scrambled. In fact it is the same sections every single >time. So we re-enter the data, check it, assume we are done, and then the >same thing happens the next day. > >To gather some empirical evidence, I ran pg_dump at 7pm on the offending >table. I check the output pages at midnight the same evening, and they all >were good. When I got back in front of the computer at 9am, the pages were >scrambled again. I ran pg_dump a second time to a separate file. The file >sizes were different (insert scary music here). No one had touched the >database or the pages. > >I reloaded the data and everything is back to normal. But I suspect it will >happen again tonight and I am afraid. Does anyone know what inhuman entity >might be causing this to occur? > >
Robert Cleveland wrote: > > ... Then , in the middle of the night, something happens and when > we awake, we find the HTML has been scrambled like so many breakfast eggs. > ... Does anyone know what inhuman entity > might be causing this to occur? Hmmm...hard not to yell, "It's GREMLINS!!!"... I'd put a trigger on the table to log the date/time of any changes to the server log. That might tell you if it's getting corrupted via SQL or via file system access. Or you might just shutdown the server and see if it still gets corrupted (verifying your assertion that nobody is touching the db). Regards, Ed Loehr
Thanks! Turning off the nightly vacuum script did the trick. Now . . . any idea why vacuum would be so damaging? It certainly appears, at least for me, that the routine is more trouble than it is worth. Is it a malfunction that can be overwritten or a bug or something else? Again many thanks. I can sleep without fear now Rob >Do you have any automated program accessing the database overnight? IE a >malfunctioning backup or vacuum script? You might also want to do a diff >-C1 first_dump second_dump to see what is actually being changed. > >At 11:40 AM 4/8/00, Robert Cleveland wrote: >>Here's a mystery I hope someone can solve for me. >> >>We are entering blocks of HTML into a table called bodyparts. We use PHP3 to >>break up these blocks into several chunks to keep the length below the >>maximum. When the end user calls up the section, the "bodyparts" are >>extracted and re-assembled. >> >>The output pages work fine . . . for a while. We set up the output pages >>during the day, check them for accuracy and go to bed thinking we have done >>a great job. Then , in the middle of the night, something happens and when >>we awake, we find the HTML has been scrambled like so many breakfast eggs. >>Not all sections are scrambled. In fact it is the same sections every single >>time. So we re-enter the data, check it, assume we are done, and then the >>same thing happens the next day. >> >>To gather some empirical evidence, I ran pg_dump at 7pm on the offending >>table. I check the output pages at midnight the same evening, and they all >>were good. When I got back in front of the computer at 9am, the pages were >>scrambled again. I ran pg_dump a second time to a separate file. The file >>sizes were different (insert scary music here). No one had touched the >>database or the pages. >> >>I reloaded the data and everything is back to normal. But I suspect it will >>happen again tonight and I am afraid. Does anyone know what inhuman entity >>might be causing this to occur? >> >>
> Thanks! Turning off the nightly vacuum script did the trick. Now . . . any > idea why vacuum would be so damaging? It certainly appears, at least for me, > that the routine is more trouble than it is worth. Is it a malfunction that > can be overwritten or a bug or something else? > > Again many thanks. I can sleep without fear now > > Rob > > > > >Do you have any automated program accessing the database overnight? IE a > >malfunctioning backup or vacuum script? You might also want to do a diff > >-C1 first_dump second_dump to see what is actually being changed. > > > >At 11:40 AM 4/8/00, Robert Cleveland wrote: > >>Here's a mystery I hope someone can solve for me. > >> > >>We are entering blocks of HTML into a table called bodyparts. We use PHP3 > to > >>break up these blocks into several chunks to keep the length below the > >>maximum. When the end user calls up the section, the "bodyparts" are > >>extracted and re-assembled. How small is the "chunk"? If you have an index on it, it must be much smaller than 8k. I recommend lower than 2k. P.S. Can you give me the dump file so that I could dig into the problem. I think pg_dump -t the_table_you_have_problems is probably enough. -- Tatsuo Ishii
Vacuuming is sort of necessary at the moment, because if you don't vacuum, postgres won't use your indexes. :( This is supposedly going to be fixed in the 7.x series (7.5 I think I heard) but I've never heard of a vacuum corrupting a normally working database in the 4 or 5 months I've been reading the GENERAL list (or at least I don't remember it...) Can you post your vacuum script? Maybe it's doing something besides the vacuum and that's what's corrupting your database. Other than that, the only thing I can think off is that the vacuum is scanning the fields of your table and is changing ones that have a specific pattern. That would be a VERY bad bug, so you would think it would have cropped up before. BTW: What version are you using? We use 6.5.3 here, and haven't had any problems. At 11:56 AM 4/9/00, Robert Cleveland wrote: >Thanks! Turning off the nightly vacuum script did the trick. Now . . . any >idea why vacuum would be so damaging? It certainly appears, at least for me, >that the routine is more trouble than it is worth. Is it a malfunction that >can be overwritten or a bug or something else? > >Again many thanks. I can sleep without fear now > >Rob > > > > >Do you have any automated program accessing the database overnight? IE a > >malfunctioning backup or vacuum script? You might also want to do a diff > >-C1 first_dump second_dump to see what is actually being changed. > > > >At 11:40 AM 4/8/00, Robert Cleveland wrote: > >>Here's a mystery I hope someone can solve for me. > >> > >>We are entering blocks of HTML into a table called bodyparts. We use PHP3 >to > >>break up these blocks into several chunks to keep the length below the > >>maximum. When the end user calls up the section, the "bodyparts" are > >>extracted and re-assembled. > >> > >>The output pages work fine . . . for a while. We set up the output pages > >>during the day, check them for accuracy and go to bed thinking we have >done > >>a great job. Then , in the middle of the night, something happens and when > >>we awake, we find the HTML has been scrambled like so many breakfast eggs. > >>Not all sections are scrambled. In fact it is the same sections every >single > >>time. So we re-enter the data, check it, assume we are done, and then the > >>same thing happens the next day. > >> > >>To gather some empirical evidence, I ran pg_dump at 7pm on the offending > >>table. I check the output pages at midnight the same evening, and they all > >>were good. When I got back in front of the computer at 9am, the pages were > >>scrambled again. I ran pg_dump a second time to a separate file. The file > >>sizes were different (insert scary music here). No one had touched the > >>database or the pages. > >> > >>I reloaded the data and everything is back to normal. But I suspect it >will > >>happen again tonight and I am afraid. Does anyone know what inhuman entity > >>might be causing this to occur? > >> > >>