Hi-
I’m running Omeka with the Curatescape theme & plugins.
Just this week, I’ve experienced a new (and kinda alarming) problem. When I upload items and go back to edit the metadata fields, the fields are already pre-loaded with metadata from another file on the site.
No matter what I do from that point, (whether I delete those fields and replace with the correct ones for the new file, cancel and don’t make any changes to the file metdata at all, just delete the new file, etc.) whatever file is associated with the metadata that appeared on my new file is now GONE. The entire record (file and metadata) is erased, not just the image file.
Assuming my problem is related to the database and how it handles the “next” value, it’s a little unclear from the answers to @Lissmarie about how one should correct that. @jflatnes you said:
What needs to be updated is the “auto increment” value for your items table. As Daniele says, there’s an option to do this under the “Operations” tab when you’re viewing a table in phpMyAdmin. Under the heading “Table options” there should be an “AUTO_INCREMENT” option: this lets you set what ID will be used for the next item created. It needs to be set so it’s higher than the highest ID you have in the table
So my PHPmyAdmin does have an “AUTO_INCREMENT” field, so for the table om_items I should just change it to some random, much higher number, like this? (previously it was like “47” or something)
Hi @abcowan, I’ll defer to others on this – hopefully someone will jump in – but if I understand correctly, the issue is specific to files (as opposed to items). Is that correct? If so, you might want to look instead/also at the om_files table.
The auto_increment value should, I think, be one higher than the highest id value in use for a given table.
So if your highest item id number is 51, the auto_increment value for the om_items table should be 52. If the highest file id number is 134, the auto_increment value for the om_files table should be 135.
The issue you’re talking about, so far we’ve only ever seen reports of it from users whose sites are hosted on MariaDB, and specifically from people whose hosts upgraded or changed servers either from MySQL or from an older version of MariaDB (pre-10.2). That’s the conditions for the “auto increment” issue, which does sound like your description. If you’ve moved servers or had an upgrade from your host recently, that would track with this as well. It appears to be a bug in MariaDB, the database software.
Setting the AUTO_INCREMENT manually does fix the issue. The value should be, as @ebellempire says, 1 larger than the highest in-use value. Choosing a low value (like 1) when setting it will cause the server to automatically use the “correct” choice of the maximum value plus 1, so that’s often the simplest choice to make. If you’re going this route, I would do the “set AUTO_INCREMENT” operation on every table, not just the items or files tables.
You might decide to instead restore a backup of the database from before the issue arose. This would have the benefit of restoring your lost records, but also, restoring from a backup (a “dump” of the database) will avoid this problem going forward without the need to do the AUTO_INCREMENT setting; the bug seems to only occur when upgrading the database server “in-place.”
Thanks for your help. So, several curious outcomes from attempting to make these changes.
I currently have 47 items in Omeka/Curatescape. So I set my om_items table auto-increment value to 48 and clicked Save (or “Go” technically). When I click away from om_items and then return to it, the auto-increment value reads 52, not 48. Similarly, according to my Omeka dashboard I have 118 files, so I set auto increment in om_files to 119. When I return to it, however, it always reverts to 137.
I’m changing this in the correct place, right? (Imgur: The magic of the Internet)
Any ideas why this would be happening?
So bottom line is I am still having the same problem. The worse news is that when the site was set up by our partner, I didn’t have cPanel admin access, so backups weren’t turned on - thus I don’t think restoring the database from a backup is an option.
I’m kinda desperate to get this sorted out - the site (https://hillhistory.org) is focused on Black history in Pittsburgh and because it’s BHM the community partner is promoting the site daily on social media and we have a batch of material that needs to be published - and it definitely doesn’t help when images keep disappearing from the site…
Hi @abcowan, the numbers I chose for my example were based on ids for the public items and files on your site (51 and 134 respectively). And that doesn’t include private items, or files added out of sequence to older items, or items and files that were added and then deleted. (Once an id is used once it is never used again, so it’s not unusual to have a max id that is higher than your current total number of items or files.) So the numbers you entered (48 and 118) were lower than the highest previously-assigned id values. You can get the highest ids by sorting the tables in question in descending order by id. As @jflatnes mentioned, if you set it low it will correct automatically and it sounds like that’s what happened.
As @ebellempire said, it’s expected that if you set the increment value “too small” it will get automatically set to a number that’s big enough. Note that this “correct” number is one higher than the highest used ID, not one more than the number of records. For various normal reasons, including deleting records, the current highest ID can be larger than the number of items, files, etc. that you have. In other words, the behavior you’re mentioning where the number resets higher when you manually set it, that sounds correct.
You say you’re still having the same problem. Does that refer just to the numbers changing as you mentioned above, or is the underlying problem still happening (unexpected metadata already being loaded when you add an item or file)?
Okay, I went back through PHPmyAdmin and carefully set the AUTO_INCREMENT value 1 higher than the number for both om_files and om_items, then went and loaded a new image in - and I think it worked! I’ve never been so happy to see blank metadata fields!
I’ll still need to sort out what got deleted and reconstruct that, but at least I don’t feel like the site is slowly destroying itself every time I try to update it.
Thanks to you both for your patience with my forays into database settings. Fingers crossed that this stays fixed!
In my experience, once you set that AUTO_INCREMENT once, it does stay fixed.
If I were you, I would do the same thing (set AUTO_INCREMENT through Operations) for every Omeka table in your database. You can set them all to 1 and rely on it automatically picking the right setting. Just so you have certainty this won’t reoccur.
