New to the community? Start here

Can’t create a staging site.

When I try creating a staging site, it can’t be finished. And I get the following message: “Oops, the sync to staging has failed.”

Sat, 18 Apr 2020 5:01:09
  • Rupok
    • Ex Staff

    Hi Wayne,

    So sorry to hear the inconvenience. We have forwarded this to our Hosting Second Level Support team and they are investigating this. We will update you here as soon as we get anything from them. I’ll really appreciate your patience.

    Regards,
    Rupok

  • Kennedy
    • Site Builder, Child of Zeus

    Hello Wayne

    I hope you’re well. First of all, let me say I’m really sorry for the delay in response. The delay was caused by our various attempts to create your staging manually. To inform you, we use wp cli to help in the staging sync. However since the database is quite huge (8.6GB) for wp cli to handle, it encounters technological limitation failure. As a result it gives the following error:

    Warning: mysqli_query(): MySQL server has gone away in /var/web/staging/public_html/wp-includes/wp-db.php on line 2024 
Warning: mysqli_query(): Error reading result set's header in /var/web/staging/public_html/wp-includes/wp-db.php on line 2024
Warning: mysqli_real_connect(): (HY000/2002): Connection refused in /var/web/staging/public_html/wp-includes/wp-db.php on line 1626
Error: Error establishing a database connection.

    Please note the mysql service is running okay. We tried various ways of resolving this issue which failed owing to wp cli limitation issue. Consequently you will not be in a position to use the staging feature.

    If we can assist further, please do not hesitate to contact us.

    Kind Regards,
    Kennedy

  • Joe
    • Designer

    Hi, I’ve checked the database on phpmyadmin. The 8.6GB is the total sent and received traffic.

    I’ve downloaded the backup file and found out that my actual database is only about 600MB!

    In fact I did asked support about this doubt on database size that showing on wpmu hosting. But support come back to me saying that either devs or sls checked and confirmed that the database size showing on hosting is correct.

    To be honest, been very upset with the issues and support that i’m facing with wpmu hosting recently. This should not be the happened at all especially I did request for a double confirm from wpmu and devs or sls came back to tell me the wrong information.

  • Patrick Freitas
    • FLS

    Hi Wayne

    Sorry to hear you are having these problems recently.

    I’m afraid the staging wouldn’t work due to the limitation, for this size we suggest having a local copy using software like Local by Flywheel or XAMPP, but I pinged the hosting developers to check if there is no possibility of using staging.

    I’ve downloaded the backup file and found out that my actual database is only about 600MB!

    I’m afraid the size is correct, for example, only the subsite wp_X_options has a total of ~2mb each, currently, the network has 334 subsites, so only for the options table, it will use ~700MB., without counting all other tables like Posts or Plugins table.

    Let us know if you need any further help on this.
    Best Regards
    Patrick Freitas

  • Joe
    • Designer

    Hi Patrick,
    Thank you for verifying this. I’ve checked further on that database table and indeed each of the wp_option file is about 2mb.

    However, if the actual database is about 8.6GB, how can it be that the downloaded backup for the sqldatabase is only about 600MB? The backup is the backup file that i downloaded from the link that sent by wpmu for backup download.

    Or did I missed something?

  • Patrick Freitas
    • FLS

    Hi Wayne

    Hope you are doing well.

    I confirmed with Hosting developers, the best option for you would be using a local website copy for tests, there are awesome tools like Local ByFlywheel.

    About the database, the SQL file is not the installed database. When we download the SQL it creates the script to recreate all tables and insert the data back, so basically the downloaded file are multiple lines of commands and when we import back the file it runs those commands and creates the structure.

    Let us know if you need any further help on this.
    Best Regards
    Patrick Freitas

  • Joe
    • Designer

    Hi Patrick,

    Thanks for your reply. Previously ive downloaded the xampp for local server. But i’m encounterring some issue to migrate my production files and database to local computer.

    As for Local ByFlywheel, I cant seem to find it. Flywheel seems to be a hosting provider and its not an software for me to do a local server. Do you have the link and hopefully a full guide on how to have my production data and database all to my local comp?

  • Patrick Freitas
    • FLS

    Hi Wayne

    You can download the file on this page:
    https://localwp.com/

    It uses Admininer for the SQL, and you can import using the Adminer iterface:
    https://www.adminer.org/

    But, it is possible you will need to use the workbench to work on the database.
    https://dev.mysql.com/downloads/workbench/

    You can learn more about this one:
    https://localwp.com/community/t/how-can-i-connect-a-tool-such-as-mysql-workbench-to-my-local-sites-database/6390

    Unfortunately, the restoration would need to be done manually, however, you can try to migrate the website to the local site using the Shipper plugin, but keep in mind this might fails due to the database size.

    Best Regards
    Patrick Freitas

  • Joe
    • Designer

    Hi, refer back to my query about the sql file size that my downloaded sql file is 600mb but on wpmudev it showing my database is 9GB. Although that you suggested that “when we download the SQL it creates the script to recreate all tables and insert the data back, so basically the downloaded file are multiple lines of commands and when we import back the file it runs those commands and creates the structure.”.

    But i’m still have some doubt on it. I imported the 600mb sql file and the database only about 2GB.

  • Kennedy
    • Site Builder, Child of Zeus

    Hello Wayne

    I hope you’re well. Based on your concern on the space comparison between the dump file and the database size:

    When you run mysqldump, only data is dumped in SQL statements. The dump file is a text file. When you turn the internal data of a DB into text, there are many expansions as follows:

    1. Numbers that might be 4 bytes internally are written out as digits like 123456
2. Strings have quotes around them, and special characters in the strings may be escaped
3. There are commas between each column, and parentheses around the rows

    In addition, indexes (which occupy lots of space) are not copied into dump files which explains further why dump files occupy much less space.

    Indexes get rebuilt when the SQL is loaded into another server. Functioning MySQL databases use plenty of data storage for indexes. There’s a large ratio between your raw (exported) data size and the disk space your database uses. Moreover, the space size difference between the 2GB (on local machine) and 9GB (on wpmudev) is owing to the indexes that have been built up over period of time (in wpmudev). This is how mysql works by making the tradeoff of using lots of disk space to improve query performance using indexes.

    I hope this information has been helpful. If we can assist further, please do not hesitate to contact us.

    Kind Regards,
    Kennedy

  • Rupok
    • Ex Staff

    Hi Wayne,

    I don’t think there is any way to optimize or change the indexing process in terms of what you are looking for. But please have a look at these articles to get a better understanding of how you can optimize your database for WordPress:

    https://www.wpbeginner.com/plugins/optimize-your-wordpress-database-with-one-click/
    https://underconstructionpage.com/optimize-wordpress-database/

    But please note, we will strongly recommend you take a complete backup of your whole site including database so you can always revert back to the current state if anything goes wrong.

    Please let us know if you have any confusion or any further query. We will be very glad to assist further.

    Regards,
    Rupok