Strange database behavior when adding an item

I just had a client report an issue that I was able to confirm and address it but I do not understand it enough to feel confident it won’t happen again.

So this site has 30 items. One of the existing items is called (let’s say) “EXISTING ITEM.” When adding a new item with the title “TEST,” Omeka returns to the admin items/browse page and displays a success message:

The item “EXISTING ITEM” was successfully added!

That item is identical to the previous “EXISTING ITEM” but has a new id (e.g. 31 instead of 1 or 30 or whatever – I actually don’t know for sure what it’s previous id was) and a new date_added value (today). As far as I can judge, no content data was actually lost.

I went into the database and repaired the tables, none of which were showing any status issues. That seems to have fixed it. I can no longer reproduce the error.

While I was in there, I noticed that omeka_sessions table was huge (I cross-checked with a couple other databases on this server and found it as high as 2.5 million). Based on this forum post, it appears to be a hosting issue more than Omeka but I figured I would mention it in case it provides clues about relevant server settings.

In general, this sounds like it probably has more to do with my database server than with Omeka, but again I’m not sure I understand what’s happened or why or how to prevent it. Any ideas?

It sounds like an issue with the auto-increment counter: a “new” item gets added that actually has the ID of an existing item, meaning it kind of combines the two.

You can check the auto-increment counter’s value with SHOW CREATE TABLE: the AUTO_INCREMENT counter will be listed on the last line. You can compare it to SELECT MAX(id) FROM omeka_items : if the maximum ID is larger than the AUTO_INCREMENT value, that’s a problem.

If it’s an auto-increment issue, I’ve seen this problem reported before, on as far as I’m aware only one host.

1 Like

AUTO_INCREMENT=37 and Max(id) is 36. That sounds right.

Hmm then it’s not the issue I’m familiar with (though it’s possible that it was and it’s just been “worked through” now: once the auto-increment goes beyond the range of old existing items it’s no longer a problem).

That could be the case. I added and deleted a handful of test items after repairing the database. And now that you explain the issue, I think I recently saw this same thing happen to an exhibit by another client on a different server (same host though).

I don’t know much beyond the basics when it comes to SQL. Is there anything I can do as a preventative measure to prevent this from happening? Like running a nightly cron to repair the tables or something?