Wordpress update does not check if database structure/scheme on existing site is equal to how it would be on a new install

[mike_vl]

Description of bug
When trying to add a category I receive the error:

WordPress database error: [Duplicate entry ‘test’ for key ‘slug’] INSERT INTO wp_terms (name, slug, term_group) VALUES (‘Test’, ‘test’, 0)

What seems to be the cause of the problem?
My install does not allow a category (test) with the same slug as an existing tag (test). WordPress should allow this.

On further investigation: in wp_terms table, the field slug has a UNIQUE constraint. This was changed in WordPress 4.1 three years ago.

Duplicates are now prevented in WordPress code instead of in the database, but it seems like my site has skipped one or more database core updates.

In short
My install is up to date. But my database core structure/scheme is not up to date. wp_repair, wp_optimize etc. do not flag this.

Also setting WP_ALLOW_REPAIR in wp-config.php does not flag this as an issue.

I was able to fix this but potentially my database still has other undetected differences.

Questions

• Should WordPress check on update if a existing database structure/scheme matches how it should be if it were new install?
• Should WordPress offer (after backup disclaimer etc.) offer to repair/update the database structure to the latest version?

I submit this as a bug and not as a feature since I feel WP_ALLOW_REPAIR should detect if a WordPress table is setup correctly.

[dd32]

Hi @mike_vl
WordPress does include a mechanism to update the database tables, however, as you've possibly found out, not everything is "run again" - and in your case it sounds like there may be a step which was missed - or you've run into a bug.

The Repair mode is also only for "Table repair" which in the context of MySQL is for crashed & unoptimized tables, not out-of-date table schemas.
Adding table schema checks to repair.php isn't out of the question and could be useful to some.

Would you be able to check what db_version you have in your options table? For reference, in 4.8.2 it should be 38590.
If it's that value - then that means the upgrade process either failed, was skipped, or some other issue may have occured (like a query failing, and not being taken into consideration). If the value you have is lower, it indicates that the upgrade process isn't being run at all.

After you've seen the db_version and noted it for reply here, you can try to force an update to occur, which should hopefully fix it, by lowering the version in the table by 1 and visiting wp-admin (This step happens transparently during version updates). Hopefully db_delta() will kick in and change the indexes at that point.
I'd backup the database before running that though, purely in case it reveals a bug and we need to inspect the table schema's. You may also wish to enable WP_DEBUG to see any errors that occur during the update process if it's not successful.

For reference, upgrade.php and schema.php is where most of the code lives for this.

[mike_vl]

Hi, I've taken some time but finally tested the approach suggested above.

My db_version is 38590. There is a unique constraint on column slug in wp_terms.
As suggested I lower db_version by 1 to start db_delta().
I visit wp-admin and the database update screen is presented. I start the update, wait a bit and am returned to wp-admin dashboard.

I check the db_version in wp_options: it has been updated to 38590.

However the unique constraint is still present. Without any errors logged by wp_debug.

Since this trac another trac has been opened by a user with similar problem: https://core.trac.wordpress.org/ticket/42422

[mike_vl]

After some digging in upgrade.php I stumbled upon the function pre_schema_upgrade()

On line 2840 there is a check: $wp_current_db_version < 31351

If so the index is dropped & added:

$wpdb->query( "ALTER TABLE $wpdb->terms DROP INDEX slug, ADD INDEX slug(slug(191))" );

I lowered my db_version on development to 31350. After reloading wp-admin the upgrade was started and I verified that the unique constraint was removed.

Summing it up:
The current WordPress update process does not fully take into account the possibility of non-fatal errors when upgrading the database. This makes it possible for database schema differences to be created and endure in later production environment updates.

Failure to remove the unique constraint does not create a fatal error and goes unnoticed since there is no validation of the query result. Further updates do not seem to check the database schema integrity but check the db_version.

Alternatives to checking the against db_version:
Option 1. Check if all field parameters in use are equal to the field parameters in the current release.
(preferably for all fields of just for those which upgrade.php takes into account currently by checking the db_version)

Option 2. Remove the db_version check and always drop the index and add it in.
(if this would create too much overhead, a database table which logs this kind of update could help since an entry could be created on succes of the update)

Also I feel it would be valuable to include in core a feature which checks the database integrity on demand.

I would appreciate feedback & thoughts on this! If the community agrees this needs a fix I would love to cooperate on a solution.