Restoring Missing Database Indexes and AUTO_INCREMENT Settings

Omeka Classic Troubleshooting
1

I’m troubleshooting an issue with an Omeka Classic installation that appears to have some problems with its database schema. The site has been migrated between servers a few times, and I suspect some of the database definitions may have been lost during one of those migrations.

The issue first came up when the user tried to save a Simple Page. The error logs showed the following error:

Column 'id' cannot be null

This was occurring when Omeka attempted to insert a record into the om_search_texts table.

While investigating, I found that the om_options table was missing its primary key, unique index, and AUTO_INCREMENT setting. I was able to repair that table, but further investigation revealed similar issues elsewhere.

The om_search_texts table was also missing its AUTO_INCREMENT setting and all indexes. I ran a database-wide check and found 31 Omeka tables with no indexes, including om_items, om_plugins, om_simple_pages_pages, and om_sessions.

At this point, I’ve stopped making changes to avoid accidentally damaging the database or existing content.

My question is whether Omeka Classic has a tool, script, or recommended procedure for checking an existing database against the expected schema and restoring missing primary keys, indexes, and AUTO_INCREMENT settings.

Ideally, we’d like to repair the existing database without having to rebuild the entire installation or risk losing content.

If there’s a reference schema for the installed Omeka version, or a recommended approach for comparing and repairing the database structure, I’d appreciate any guidance.

Thanks for your help.

2

That’s an interesting problem for sure…

I don’t think we’ve had this come up so there’s not a procedure or tool.

The schema as installed is indicated by files in the application/schema folder in the Omeka Classic code (that link is to the 3.2.2 version’s; you’d want to use the version corresponding to this site, and note these files expect you to replace %PREFIX% with your desired table prefix if you’re going to actually run them).

You could proceed by ALTER-ing the tables to bring them into alignment. Another possibility would be to create new tables using the CREATE TABLE statements in those files, then bring the existing data over from the tables you have by using INSERT … SELECT statements.