[wp-trac] [WordPress Trac] #5622: Aborted upgrade can brake the WP DB

[WordPress Trac]

#5622: Aborted upgrade can brake the WP DB
----------------------------+-----------------------------------------------
 Reporter:  petitnuage      |       Owner:  anonymous                                                                                
     Type:  defect          |      Status:  new                                                                                      
 Priority:  low             |   Milestone:  2.6                                                                                      
Component:  Administration  |     Version:  2.3.2                                                                                    
 Severity:  major           |    Keywords:  upgrade, memory, crash, database, DB, corruption, term, category, SQL, INSERT, SELECT, ID
----------------------------+-----------------------------------------------
 On some circumstancies, the upgrade can fail. That may happen when the PHP
 cannot allocate more memory in the middle of an upgrade. That happens with
 some plugins installed on a huge blog with 8 MB of memory allocated to PHP
 scripts. (I understand very well plugins should be deactivated before
 upgrading.) However, I haven't made the buggy upgrade myself, I've just
 seen memory allocation issues after the buggy upgrade happened.

 Anyway, I had the following situation after the failed upgrade:


 1) 'db_version' set to '5183'.[[BR]]

 2) 'wp_post2cat' still filled with data.[[BR]]

 3) 'wp_categories' still filled with data.[[BR]]

 4) 'wp_term_taxonomy' correctly filled with data from the 'wp_categories'
 table.[[BR]]

 5) 'wp_term_relationships' defined, but empty.[[BR]]



 The above situation occurred after an upgrade to WordPress 2.3.2
 ('db_version' intended to become '6124').

 After a quick study of the upgrade_230() code:


 {{{
 $wpdb->query("INSERT INTO $wpdb->term_taxonomy (term_id, taxonomy,
 description, parent, count) VALUES ('$term_id', '$taxonomy',
 '$description', '$parent', '$count')");
 $tt_ids[$term_id][$taxonomy] = (int) $wpdb->insert_id;
 }}}


 It appears the $tt_ids cannot be filled in the previous INSERT command
 fails. The INSERT instruction fails, since the row is already filled.
 Later in the code the statement:


 {{{
 if ( empty($tt_id) )
         continue;
 }}}


 prevents the terms_relationship table to be updated.

 A better approach would be to avoid asserting the initial INSERTs have to
 succeed to be able to continue. The INSERT instruction does not have to
 succeed, especially when they have succeeded on a previously aborted
 upgrade and the table is already filled. It would be more interesting here
 to request the newly (or previously) inserted row IDin order to fill the
 $tt_ids tables by querying the DB again with a fully formed SELECT
 instruction.

 I believe the upgrade code should be updated to make it more crash-proof.
 After a failed upgrade, the only way to fix it is to come back to a
 previous backup (and assume the next upgrade won't crash because of a
 memory issu, while that should happen again and again), or remove manually
 the wrong tables.

-- 
Ticket URL: <http://trac.wordpress.org/ticket/5622>
WordPress Trac <http://trac.wordpress.org/>
WordPress blogging software