by schadecp » Wed May 08, 2019 8:16 pm
I am having an odd issue with a complicated database running on Windows 7 with version 4.1.6 of open office.
I noticed some extra rows on a data entry form that replicated previous rows in the form. Because the control was a grid that is linked to a table with a unique primary key, there should never be duplicates. The rows always appeared at the end of the list.
I figured it was something with the logic, so I examined the table. It had 40 actual records, with primary key values 1 through 40. But the record counter said there were 58 records, and there were 18 extra records appended to the end which were copies of records 1-18. I queried using a simple "Select * from TABLE" and got the same 58 records. Interestingly, the 18 at the end could not be selected and the row pointer would not go to any of them. I queried using a count function, e.g. "Select count(X) from Table" where X exists on every record, and I got the correct answer of 40.
I thought the table might be corrupt, so I created a copy and inserted records from the original table. Same issue. New table had 18 phantom records. I wrote macro code to regenerate the table by hand and copy every field in every record, figuring that copying the table definition might have copied whatever was corrupt. Nope! Still 58 records.
I took the SQL that copied and regenerated the table and dropped one line from it. 39 records in the table with no phantoms. I went to an old version of the database and checked the comparable table. When I opened the table in question, the record counter reported 58 records. Interesting coincidence. I moved to the end of the table. It has 161 lines with no phantom records.
I deleted all but 40 records from the table and re-opened it. 58 records with 18 phantom records.
I generated the table using SQL in a new empty database. The table was not visible on the list of tables (had to close and reopen the database to see it). However, I could run a query against it. A simple select returned 40 records. Closed and reopened the database. Table now visible with 58 records. The query that had returned 40 records now returned 58.
I've attached the simple database and the generating SQL. The records are actual records from a production database, but I have replaced all text fields with random strings of the same length.
Is this a harmless but annoying bug or something that is going to bite me in some unpredictable way in the future? And what's with the number 58?
- Attachments
-
cleancopySQL.txt
- This is SQL to generate the 40 record table in the database.
- (28.88 KiB) Downloaded 35 times
-
OneTableTest.odb
- This is a database illustrating the issue.
- (18.44 KiB) Downloaded 37 times
Last edited by
schadecp on Thu May 09, 2019 8:05 pm, edited 1 time in total.
Open Office 4.1.6 on Windows 7