[wp-trac] [WordPress Trac] #42381: Wordpress update does not check if database structure/scheme on existing site is equal to how it would be on a new install

[WordPress Trac]

#42381: Wordpress update does not check if database structure/scheme on existing
site is equal to how it would be on a new install
-------------------------------+------------------------------
 Reporter:  mike_vl            |       Owner:
     Type:  defect (bug)       |      Status:  new
 Priority:  normal             |   Milestone:  Awaiting Review
Component:  Database           |     Version:  4.8.2
 Severity:  normal             |  Resolution:
 Keywords:  reporter-feedback  |     Focuses:
-------------------------------+------------------------------

Comment (by 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
 production environments.

 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.

--
Ticket URL: <https://core.trac.wordpress.org/ticket/42381#comment:3>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform