Quote:
Originally Posted by Lucas Malor
I get a look to my db (I have firmware 2.4): there's only one EventType=9 row, that is updated every time I search in dictionaries. There's also an ExtraData column, that seems empty. There's no related trigger under the Master Table. I have not found any dicionary word inside the db... maybe they are stored outside?
PS: searched strings can be associated to the event table row through the ID.
|
Why do you think the looked up term is stored somewhere? I know I said recently they were, but tshering corrected me on it. The search terms are stored, not dictionary look ups.
No, there isn't a useful key. The primary key is EventType plus ContentID. For EventType=9, ContentID is always blank. The row isn't updated, it is deleted and a new one is insert. You can tell that from the rowid. If the row was updated, this wouldn't change. Because of that, the rowid isn't useful elsewhere. It could be used, but it would be a pain
And the mention of the primary key tells me what I think has happened. In all the duplicates in my backup databases, the ContentID is NULL. That is allowing multiple rows in the database with the same EventType. I deleted all the EventType=9 rows the other night and the new has an empty string for the ContentID. There can only be one of those in the database. I would say that sometime in the distant past, a firmware version erroneously inserted the row with NULL ContentID. The code obviously doesn't handle this correctly and didn't delete the rows but was able to insert the new row. The new row was probably created from the memory copy of the row read from the database, and hence inherited the null ContentID.
This also explains the performance problem. I have a sneaking suspicion that the code is selecting all rows with EventType=9 expecting there to be only one. A cursor is opened on this and then, instead of just using the first row found, it reads all of them. That would be OK if there was only one row, but when there are several thousand, it takes time.
The fix is to delete all the rows or delete al except one and change its ContentID to an empty string. And I suspect the firmware has already been fixed to prevent this incorrect row being created in the first place.