Restoring snapshot fails on wp_options table

We try to migrate a single site out of a Multisite install to its own install. But when we try to restore the snapshot on the single site it crashes while restoring the wp_options table. It throws a 500 internal server error in the browser console.

In debug.log we found this error:

[01-Sep-2017 22:34:19 UTC] WordPress database error Duplicate entry '1838221' for key 'PRIMARY' for query
INSERT INTO _snapshot_recover_wp_options VALUES ('1838221', '_wc_session_expires_f7cd96731fadcde35229abc77e1b9609', '1451411071', 'no') made by do_action('wp_ajax_snapshot_restore_ajax'), WP_Hook->do_action, WP_Hook->apply_filters, call_user_func_array, WPMUDEVSnapshot->snapshot_ajax_restore_proc, WPMUDEVSnapshot->snapshot_ajax_restore_table, Snapshot_Model_Database_Backup->restore_databases

We did run a plugin conflict test without a positive result.

  • Ivan

    Hi Rob !

    Regarding the error - it happens because your backup includes two entries with the same key but options table ables to save only unique key for each entry. It's not a standard situation. Actually, this issue doesn't prevent for restoring Snapshot. I noticed that you options table has more than 490 000 entries in the backup. Is it OK? Unfortunately, our code was not optimized for restoring very huge tables like this one. I added some changes for resolving this issue and I could restore this Snapshot. If you want to implement this changes for another site, please, open {wordpress_folder}/wp-content/plugins/snapshot/snapshot.php file via FTP and replace this line ( approximately 5530th line )

    $sql_str = $wpdb->prepare( "SELECT * FROM " . $table_set['table_name_restore'] . " LIMIT %d,%d", $limit_start, $limit_end );
    to
    $esc_replacements = array_map( 'esc_sql', array_keys( $replacement_strs ) );
    $where = "WHERE option_value like '%%" . implode( "%%' OR option_value like '%%", $esc_replacements ) . "%%'";
    $sql_str = $wpdb->prepare( "SELECT * FROM " . $table_set['table_name_restore'] . " {$where} LIMIT %d,%d", $limit_start, $limit_end );

    Also, I'll ping our developer to implement this code to the next version of the plugin.

    Best regards,
    Ivan.

  • Dimitris

    Hello there Rob,

    I'm glad that you've managed to sort that out with Ivan ! :slight_smile:

    In order to restore this into another installation, you should first:
    1 - install Snapshot to new server
    2 - download this snapshot file from old server and upload it in new server in wp-content/uploads/snapshots folder
    3 - go to wp-admin under Snapshot -> Import and click on the Import button

    So now the snapshot will be visible in the new WP installation.

    But restoring it, will most probably cause the same restoration issues you were getting before, so you should also make the changes mentioned by Ivan in this previous reply, in Snapshot's files.
    https://premium.wpmudev.org/forums/topic/restoring-snapshot-fails-on-wp_options-table#post-1275621

    Let us know if further assistance is required on this!
    Warm regards,
    Dimitris

  • Dimitris

    Hello Rob,

    hope you're doing good today! :slight_smile:

    Ivan used the server credentials shared in the chat session (FTP details) in order to make the aforementioned changes in Snapshot plugin's files, so that the restoration process doesn't fail.

    If you want to restore the same snapshot into another WP installation, then these changes in Snapshot plugin's files should be made again, as instructed in latest reply by Ivan.

    Warm regards,
    Dimitris

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.