Migrated a site with Snapshot - administrator locked out!

I used Snapshot to migrate a site to a new server. It almost worked well ... the site looked great, I was able to log in with the administrator account BUT then when I tried to click on "Dashboard" or even the profile page for that user, I hit an error:
`You do not have sufficient permissions to access this page.

It was the only administrator account and I couldn't do anything!

In the end I solved it, but think it might be a problem with Snapshot so wanted to post here in case it's a bug. Or in case I did something wrong so I can know for next time.

Process:
1) Backed up existing site with Snapshot.
2) Downloaded the zip file with FTP
3) Installed a fresh copy of Wordpress on the new server.
4) The only plugin I installed with it was Snapshot.
5) I uploaded the backup zip file to the new server in the /uploads/snapshots directory
6) I told Snapshot to scan for archives. It successfully found it.
7) I told Snapshot to restore the archive. Snapshot noted that the table prefix in the new database was different to the old database, and said it would automatically update all the tables to match the new prefix.
8) The site looked good. Correct posts, theme, etc. The administrator account could log in, but, as stated earlier, when I clicked on "Dashboard" or even the profile page for that user I received the error saying I didn't have permissions to view the page.

PROBLEM IDENTIFIED
At first I logged into the MySql server and tried to add a new administrator following these steps but that new user had exactly the same problem. Then I figured it out.

Six fields in the database had NOT been renamed with the correct table prefix. The original site had a table prefix of wp_tktb and my new site just had wp_. But it seems there are some fields that also include this prefix which is what caused all the problems.

These were the fields that hadn't be changed:
Options table (wp_options)
Field: *_user_roles
The field name was still set to wp_tktb_user_roles
I had to change it to wp_user_roles

User_meta table (wp_usermeta table)
Fields:
wp_tktb_capabilities (I changed it to wp_capabilities)
wp_tktb_user_level (I changed it to wp_user_level)
wp_tktb_dashboard_quick_press_last_post_id (I changed it to wp_dashboard.... I don't think this one was crucial, but I changed it anyway)
wp_tktb_user-settings (I changed it to wp_user-settings)
wp_tktb_user-settings-time (I changed it to wp_user-settings-time)

(I have attached a screenshot showing the old field names in the tables).

When I had made these changes, finally, the user had full permissions again and was able to use the site correctly.

I used Snapshot Version 2.4.1 for this migration. I realise it's not the most recent version, but I can't see any mention in the change log of this bug being fixed.

Perhaps I just did something wrong in the migration? Or maybe it's been fixed already in the latest version? But if not, hopefully this post helps.

  • Paul

    @Josh,

    Not sure what to advise. There is specific code within Snapshot for the restore process to update those wp_options and usermeta records with the new/correct table prefix.

    Not really sure why this would not take on your site. Any chance you can check your server error log to see if anything was reported?

    Although this could be a legetimate bug. Your original table name prefix 'wp_tktb_' was somewhat inclusive of the replacement 'wp_'. I'll need to do some testing. But this should work. Thanks for reporting.

  • Paul

    @Josh,

    Just ran some tests. On the wp_options table there is a small but effective bug in the looping logic. Causes some rows to be skipper.

    For the usermeta table I'm not seeing any issues. For my test I ran a backup on a site where the table prefix is 'wp_snapshot' then restored to a site where the table prefix is 'wp_'. Both a single not Multisite systems.

    I added some debug to the snapshot restore logic and here is the SQL lines. From what I can see these are correct. Still investigating.

    SELECT * FROM_snapshot_recover_wp_usermeta` WHERE meta_key like 'wp_snapshot%' LIMIT 0,20
    UPDATE _snapshot_recover_wp_usermeta SET meta_key='wp_capabilities' WHERE umeta_id=10
    UPDATE _snapshot_recover_wp_usermeta SET meta_key='wp_dashboard_quick_press_last_post_id' WHERE umeta_id=14
    UPDATE _snapshot_recover_wp_usermeta SET meta_key='wp_user_level' WHERE umeta_id=11
    `

  • Josh

    Ok, I've just done another restore. Just restoring a single site, same as last time.

    This time the administrator didn't get locked out ... which was good. BUT I had another major problem with the process!

    For this case, the original wordpress install was installed in a subfolder (although this folder was hidden from the URL, so the site was showing at the root domain). But on the new server I didn't install wordpress in the subfolder. The result was that the site didn't restore properly - the page content was there, but no theme at all (no stylesheet).

    I couldn't figure out how to fix this so in the end I deleted that whole installation and started again.

    This time I installed Wordpress in the same subfolder name as the previous installation - "health".

    But this restore didn't work either. The site displayed the text, but no stylesheet is available.

    On inspection I found that it had inserted TWO of that subfolder name into the URL for each file it was looking for (including the CSS file).
    e.g. http://www.domain.co.nz/health/health/wp-content/uploads/2014/05/oxygen_facial-309x194.jpg

    Here are some more details:

    In snapshot zip file the snapshot_manifest file says:
    WP_HOME:http://www.domain.co.nz
    WP_BLOG_NAME:Blog Name
    WP_BLOG_DOMAIN:www.domain.co.nz
    WP_SITEURL:http://www.domain.co.nz/health

    In the restoration screen under the heading "Restore Blog Options" it says:

    Information from Archive
    Site URL: http://www.domain.co.nz/health
    Database Name: watersda_wpb1
    Database Base Prefix: wts_
    Database Prefix: wts_
    Upload Path: wp-content/uploads

    Will be restored to
    Site URL: http://www.domain.co.nz/health
    Database Name: geektami_wor0158
    Database Base Prefix: wp_ovud_
    Database Prefix: wp_ovud_
    Upload Path: wp-content/uploads

    But after restoring it using Snapshot this is what the fields say in MySQL:
    options table:
    siteurl: http://www.watersdayspa.co.nz/health/health
    home: http://www.watersdayspa.co.nz/health

    In the posts table each post had /health/ in the guid field, when it shouldn't have been there.
    And each image referenced in that table have /health/health in it.

    I still haven't been able to solve this, and am looking to delete, and start again a second time.

    Any suggestions?

  • Paul

    @Josh,

    I just setup 2 new test sites. Both as sub-directory installs using the same sub-directory /health. I created some dummy users and content and files. Ran the snapshot on sub1.

    When I go to sub2 to perform the restore here is the snapshot details information (see attached image). Note I'm even using the same database prefixes. The restore finished and all is correct as far as I can tell.

    So I must be missing something on being able to recreate this under me test environments.

  • Josh

    Hi Paul. I've just run another migration of that same site. This time I didn't install it in the subfolder. I did a screen recording of the whole thing so you can see the setup and what I did.

    Original install: In subfolder /health/
    New install: in root folder

    The result was:
    * Site displayed correct correct text content and theme, and widget or custom post images.
    * Any images within pages didn't display, as they had "/health/" in the URL structure.
    * The login page didn't work because it included /health/ in the URL.
    * When I changed the site URL in the database login worked, but then gave me the same error as my first post in this support request - You do not have sufficient permissions to access this page.
    * The recording shows exactly what fields I changed to fix this permissions error.
    * However the images still don't display because I need to run a find and replace to remove /health/ from it.

    Here's the screen recording (11 mins): http://youtu.be/gP1Pezewmt0?hd=1

    It's a hidden video, and I'll take it down next week. But it will show you exactly what I did, and hopefully help identify where things are going wrong. Let me know if there is anything else you'd like from my end.

  • Paul

    @Josh.,

    Just getting back to this today. Have not had a chance to view your video but will after positing this comment.

    Yes, would love to try the restore using your actual archive. Please send it directly to paul [at] incsub.com

    Lastly, I posted an update to Snapshot last evening. This was to correct the small bug I found in the looping logic during the restore. Can you please make sure you upgrade to the latest version before running any other tests.

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.