Restoring Snapshot with snapshot-installer breaks character encoding in content

When a backup is restored from Managed Backups using snapshot-installer.php, the site gets restored but the character encoding gets messed up. This happens on a fresh database and was tested more than once.

The current DB encoding on a source site is "utf8mb4_unicode_ci". That same encoding was used when creating a fresh database. It's also used in .sql files inside snapshot backup .zip file and the text in those files seems to be properly encoded (all the characters are fine). But something happens "on a way" during restoration process.

  • Predrag Dubajic

    Hi INTOUCH,

    Hope you're doing well.

    I was able to restore your backup on my local installation, and it looks like the backup files are not having issues with encoding, you can see from the below examples that German characters are properly rendered for me:

    As Adam mentioned in chat, Snapshot is not a migration tool so we can't guarantee the results and this seems to be related to your database configuration.

    It would be best to discuss this with your hosting provider so they can check the database and see what might be causing this.

    Best regards,
    Predrag

  • INTOUCH

    Hi Predrag!

    Snapshot is really a mess. I tried 15 hours to get this working with no success. Regardless on which host I am trying, it doesn't work and thats very disappointing for such an important plugin for keeping sites on the safe site.

    I never migrated with Snapshot to another host! I migrated/copied the site fully functionally (!) to a test environment, took a managed backup of the fully functionally on the test environment site and Snapshot failed there every time I tried. If you don't wipe the database, it fails on deploying on the restoring database state. If you wipe the database before deploying it success with messing up encondig and other things in the database related to this problem.

    What I have tried so far:
    - Restore via WordPress Dashboard. Snapshot caused errors: https://premium.wpmudev.org/forums/topic/cannot-restore-managed-backup/

    - Migrated the test site to our local Test Server and failed on the Restoring Database State:

    - Tried once again via our online test environment to restore the database via php installer. German character encoding problems. Don't know why thats happening.

    Please tell me, what we have to configure to get this thing running. Are there special parameter, mysql Versions, php version, php modules and parameter? How did you get this working?

    If there is no solution we have to cancel this backup plugin and we have to search for another backup plugin, but it would be great to have it all from one source, because the other wpmudev plugins are awesome. :frowning2:

  • Predrag Dubajic

    Hi INTOUCH,

    I was testing this on my local installation running with MAMP, without any special settings, I have PHP 7.1.8 selected and that's pretty much it.
    MySQL is 5.6.35 and uses Apache as web server.
    I just created an empty DB, visited snapshot-installer.php URL in my browser and after entering DB info the restore process was seamless.

    I see that James has forwarded your other thread to our dev guys so hopefully they will be able to figure out what's going on on your end.

    At the moment everything seems to be fine with the snapshot-installer and your backup file as it works fine for me (I did another restore just to be sure) so we would need to wait for dev guys to give us more info about your installation.

    Did you get any information from your host about the encoding issue?

    Best regards,
    Predrag

  • INTOUCH

    Ok, reporting back from the front. I figured out, why it is crashing at our Local Testserver: mysql had some strict modes active and didn't allow for Date Time the default 00-00-0000 00:00. After disabling this strict mode, snapshot installer was able to successfully deploying the site. But the encodings are still messed up on the Local Testserver:

    :confounded:

    Will try to deploy the site on another host for the last time and will report you back.

  • INTOUCH

    Finally we got it by modifying the snapshot-installer. :grinning:

    The problem was that snapshot sets no default for the database connection and uses some weired parameters from the server defaults. We added following line in the function query:

    mysqli_set_charset ( $this->_handle , 'utf8' );

    So the complete function looks like this and it worked!

    /**
    	 * Perform a query
    	 *
    	 * param string $sql SQL query to perform
    	 *
    	 * @return bool
    	 */
    	public function query ($sql) {
    		if (!$this->connect()) return false;
    
    /*
    // ---------------------------------------------------------
    // Collation fix for compatibility reasons with older MySQLs
    // ---------------------------------------------------------
    		// Try to clean up the collations
    		$sql = preg_replace('/\butf8mb4_unicode_520_ci\b/', 'utf8_unicode_ci', $sql);
    		// Now try to clean up charsets
    		$sql = preg_replace('/\bCHARSET=utf8mb4\b/', 'CHARSET=utf8', $sql);
    */
    
    		mysqli_set_charset ( $this->_handle , 'utf8' );
    
    		$result = mysqli_query($this->_handle, $sql);
    
    		return false !== $result;
    	}

    We recommend to set the database connection to UTF8, because WordPress uses UTF8 anyway.

    We needed tons of hours to figure this out, guys. Can we get some cookies? :joy: