All Pro Sites settings disappeared

Hi,

I've just seen that all my Pro-Sites settings have disappeared!
(just Pro Sites level are here)
I've never had such a problem with Pro-Site and It was ok at the end of the week but today I discovered the problem.

It's a major issue of course.

I've just seen there are several threads about the same problem. I'm not using MultiDB.

Why did it happen? How can I prevent this from happening again?
Is it possible to get all settings back?
Could you help me please?

I granted support access.

Thanks!

Best Regards

    Michelle Shull

    Hey there, Arom!

    Did this happen after you updated ProSites? If not, were there any other events that happened at or around the same time your Pro Sites settings disappeared?

    The dev provided a failsafe, try adding this to your wp-config.php file before the line that tells you to stop editing:
    define('PSTS_DISABLE_UPGRADE', true);

    Do you happen to have a site backup from before when this issue occurred that you can restore?

    Let's see where this gets us, and we can call in for more help if we need it.

    Thanks, Arom!

    Arom77

    Hello @Michelle Shull

    Did this happen after you updated ProSites?

    No.

    If not, were there any other events that happened at or around the same time your Pro Sites settings disappeared?

    No, I don't think so. It was working OK at the end of the week and I haven't done anything since then (just publishing a post and some messages on the forum, that's all).

    The dev provided a failsafe.

    OK thanks I'll do this. (question: why is it not "included" for all Pro Sites users?)

    Do you happen to have a site backup from before when this issue occurred that you can restore?

    I have a backup of the entire multisite network (on the server side, using a VPS) but if I restore this it'll restore the entire network, including my clients sites, so I can't do that.
    I guess I can't restore only this, right?
    Could you help me restoring what needs to be restored?

    I'm really worried because this is a "core" feature of my multisite network and I saw there are several other threads speaking of a similar problem. Just want to 1/ fix it 2/ know how to prevent this from happening agan 3/ make sure i can handle it easily if it was to happen again.

    Thanks!

    Jude

    Omg @Arom77

    So sorry this happened to your thread. Here is a small tip about our system, the queue is ordered by the last response date. So if there is no response for a long time then it is on high priority but if you keep posting on it then it goes back to the end .. Anyway as I was scanning through our feed I saw your name and jumped in here. Appreciate your patience, you may already but you're one of our valued members.

    Anyway here goes:

    1) There are 3 tables that you should backup

    wp_pro_sites
    wp_pro_sites_signup_stats
    wp_pro_sites_daily_stats

    2) Pro Sites Options -> These are serialized and stored as

    psts_settings
    publishing_level

    Just copy this out of the DB and keep it elsewhere as a backup. Here is what is saved here ( Not the whole thing .. but will give you an idea)

    'base_country'             => 'US',
    			'currency'                 => 'USD',
    			'curr_symbol_position'     => 1,
    			'curr_decimal'             => 1,
    			'rebrand'                  => __( 'Pro Site', 'psts' ),
    			'lbl_signup'               => __( 'Pro Upgrade', 'psts' ),
    			'lbl_curr'                 => __( 'Your Account', 'psts' ),
    			'gateways_enabled'         => array( 'ProSites_Gateway_PayPalExpressPro' ),
    			'modules_enabled'          => array(),
    			'enabled_periods'          => array( 1, 3, 12 ),
    			'hide_adminmenu'           => 0,
    			'hide_adminbar'            => 0,
    			'hide_adminbar_super'      => 0,
    			'free_name'                => __( 'Free', 'psts' ),
    			'free_msg'                 => __( 'No thank you, I will continue with a basic site for now', 'psts' ),
    			'trial_level'              => 1,
    			'trial_days'               => get_site_option( "supporter_free_days" ),
    			'trial_message'            => __( 'You have DAYS days left in your LEVEL free trial. Checkout now to prevent losing LEVEL features »', 'psts' ),
    			'ga_ecommerce'             => 'none',
    			'signup_message'           => __( 'Would you like to upgrade this site to Pro?', 'psts' ),
    			'feature_message'          => __( 'Upgrade to LEVEL to access this feature »', 'psts' ),
    			'active_message'           => __( 'Your Pro Site privileges will expire on: DATE<br />Unless you have canceled your subscription or your site was upgraded via the Bulk Upgrades tool, your Pro Site privileges will automatically be renewed.', 'psts' ),
    			'success_subject'          => __( 'Thank you for becoming a Pro Site member!', 'psts' ),
    			'success_msg'              => __( "Thank you for becoming a Pro Site member!
             'pypl_site'                => 'US',
    			'pypl_currency'            => 'USD',
    			'pypl_status'              => 'test',
    			'pypl_enable_pro'          => 0,
    			'stripe_ssl'               => 0,
    			'mp_name'                  => __( 'Manual Payment', 'psts' ),
    			'mp_show_form'             => 0,
    			'mp_email'                 => get_site_option( "admin_email" ),
    			'pt_name'                  => __( 'Premium Themes', 'psts' ),
    			'pt_text'                  => __( 'Upgrade to LEVEL to activate this premium theme &raquo;', 'psts' ),
    			'ps_level'                 => 1,
    			'ps_email'                 => get_site_option( "admin_email" ),
    			'ps_name'                  => __( 'Premium Support', 'psts' ),
    			'ps_message'               => __( 'You can send us a priority direct email support request here if you need help with your site.', 'psts' ),
    			'ps_notice'                => __( 'To enable premium support, please upgrade to LEVEL &raquo;', 'psts' ),
    			'publishing_level'         => 1,
    			'publishing_message_posts' => __( 'To enable publishing posts, please upgrade to LEVEL &raquo;', 'psts' ),
    			'publishing_message_pages' => __( 'To enable publishing pages, please upgrade to LEVEL &raquo;', 'psts' ),
    			'quota_message'            => __( 'For SPACE of upload space, upgrade to LEVEL!', 'psts' ),
    			'quota_out_message'        => __( 'You are out of upload space! Please upgrade to LEVEL to enable SPACE of storage space.', 'psts' ),
    			'xmlrpc_level'             => 1,
    			'xmlrpc_message'           => __( 'To enable XML-RPC remote publishing please upgrade to LEVEL &raquo;', 'psts' ),
    			'bp_notice'                => __( 'Upgrade to LEVEL to access this feature &raquo;', 'psts' ),
    			'pp_name'                  => __( 'Premium Plugins', 'psts' ),
    			'ads_name'                 => __( 'Disable Ads', 'psts' ),
    			'ads_level'                => 1,
    			'ads_enable_blogs'         => 0,
    			'ads_count'                => 3,
    			'ads_before_page'          => 0,
    			'ads_after_page'           => 0,
    			'ads_before_post'          => 0,
    			'ads_after_post'           => 0,
    			'ads_themes'               => 0,
    			'bu_email'                 => get_site_option( "supporter_paypal_email" ),
    			'bu_status'                => 'test',
    			'bu_payment_type'          => 'recurring',
    			'bu_level'                 => 1,
    			'bu_credits_1'             => 10,
    			'bu_option_msg'            => __( 'Upgrade CREDITS sites to LEVEL for one year for only PRICE:', 'psts' ),
    			'bu_checkout_msg'          => __( 'You can upgrade multiple sites at a lower cost by purchasing Pro Site credits below. After purchasing your credits just come back to this page, search for your sites via the tool at the bottom of the page, and upgrade them to Pro Site status. Each site is upgraded for one year.', 'psts' ),
    			'bu_payment_msg'           => __( 'Depending on your payment method it may take just a few minutes (Credit Card or PayPal funds) or it may take several days (eCheck) for your Pro Site credits to become available.', 'psts' ),
    			'bu_name'                  => __( 'Bulk Upgrades', 'psts' ),
    			'bu_link_msg'              => __( 'Purchase credits to upgrade multiple sites for one discounted price!', 'psts' ),
    			'ptb_front_disable'        => 1,
    			'ptb_front_msg'            => __( 'This site is temporarily disabled until payment is received. Please check back later.', 'psts' ),
    			'ptb_checkout_msg'         => __( 'You must pay to enable your site.', 'psts' ),
    			'pq_level'                 => 1,
    			'pq_quotas'                => array(
    				'post' => array( 'quota' => 'unlimited' ),
    				'page' => array( 'quota' => 'unlimited' )
    			),
    			'uh_level'                 => 1,
    			'uh_message'               => __( 'To enable the embedding html, please upgrade to LEVEL &raquo;', 'psts' )
    		);

    Finally the prosites.php file has a couple of functions to completely restore the plugin

    restore_current_blog();
            init_vars() ;
           install() ;

    Use the above if you really somehow mess up big time . Please backup and keep them handy.

    Cheers
    Jude

    To other readers : This is somewhat advanced and if you are not sure of what to do then please don't try this yourself

    Arom77

    Hi @Jude

    It happened again. All Pro Sites settings lost

    Last time I have an idea about why this could have happened, but this time there is seriously nothing i could think about.
    It seems to be a problem for a lot of people and for years, is this impossible for you to fix that?
    That's really a major issue because obvisoulsy Pro Sites settings can be lost anytime, and you don't even know when it does!

    Best Regards

    Michelle Shull

    Hey there, Arom!

    I'm going to flag our second level support team here, since this has been an off and on problem for users for a while now, to see if we can find a definitive reason AND a definitive fix for why this keeps happening.

    Let's be proactive and get the second level team some log in details for your account so they can hop right in and look around.

    https://premium.wpmudev.org/contact/

    Subject: "Attn: Michelle Shull"
    -WordPress admin username
    -WordPress admin password
    -login url
    -cPanel/PHPMyAdmin credentials (username/password/cpanel login link)
    -FTP credentials (host/username/password)
    -link back to this thread for reference
    -any other relevant urls

    Select "I have a different question" for your topic - this and the subject line ensure that it gets assigned to me.

    Thanks, Arom!

    Hoang Ngo

    Hi @Arom77,

    Ugg, this is really a pain. For now, do you have any backup for Pro-sites or your current WordPress database, as we can extract the Pro-sites data and recover it on your site.

    For the reason why it can happen, this is various, it can be WordPress issue or non-WordPress issue. For now, Please add this code to your site using mu-plugins

    add_action('update_site_option_psts_settings', 'log_psts_settings_changed', 10, 3);
    function log_psts_settings_changed($option, $value, $old_value)
    {
        $log_file = __DIR__ . DIRECTORY_SEPARATOR . 'psts.log';
        if (!file_exists($log_file)) {
            $handle = fopen($log_file, 'w');
        } else {
            $handle = fopen($log_file, 'a');
        }
        $message = array(
            'date' => date('Y-m-d H:i:s'),
            'data' => $value,
            'before' => $old_value,
            'track' => debug_backtrace()
        );
        if ($handle) {
            fwrite($handle, json_encode($message));
            fclose($handle);
        }
    }

    As you said, the settings are gone, so this code will tracking, if any change affect to the ProSites settings, we will log it all, if the error happen again, we have clues to work on

    Best regards,
    Hoang

    Arom77

    Hi @Hoang Ngo

    All settings lost again during this week-end (((

    I've just installed the mu-plugin you asked for. As I'm not a developper, I really need your assistance on all this stuff. Could you please check if the plugin is correctly installed and so if it's going to track data when the problem occurs again?

    do you have any backup for Pro-sites or your current WordPress database, as we can extract the Pro-sites data and recover it on your site.

    As I said I'm not a developper so I'm not very easy on doing such things. As a result I put the settings back using back-office. If you could assist me also on this so I can back-up and restore what I need when the problem happens again... Thanks a lot.

    The major point would be that I know that the problem has occured as soon as it's occured. Because for now I don't!

    Best Regards

    Hoang Ngo

    Hi @Arom77,

    I hope you are well today.

    All settings lost again during this week-end (((

    Obs!!, I'm sorry about that.

    I've just installed the mu-plugin you asked for. As I'm not a developper, I really need your assistance on all this stuff. Could you please check if the plugin is correctly installed and so if it's going to track data when the problem occurs again?

    Your plugin setup is correct.

    As I said I'm not a developper so I'm not very easy on doing such things. As a result I put the settings back using back-office. If you could assist me also on this so I can back-up and restore what I need when the problem happens again... Thanks a lot.

    I saw you already installed Snapshot, you can create a snapshot backup with this database config
    1. Include selected database tables in this Snapshot, please check the cssn_pro_sites, cssn_pro_sites_daily_stats,cssn_pro_sites_signup_stats, cssn_sitemeta
    So later, when the settings gone, you can easily recover it

    Best regards,
    Hoang

    Hoang Ngo

    Hi @Arom77,

    I hope you are well today and I'm sorry for the delay.

    This is really weird, the logs don't show much information, everything seem fine. I checked apache logs, but I don't see any issues related to ProSites too.

    Just for clarification, you're losing the settings in WP-Admin->Pro Sites->Settings? or the actual blogs which are pro-sites(has paid)?

    But from now, I think, there are 2 reasons.
    1. The XML-RPC (as I saw it in the apache log, but it is less likely)
    2. Database crash, data corruption (more likely)

    I can't find the MySQL logs on the webmin, is there anyway you can send me that, usually it located in /var/logs/mysql

    Best regards,
    Hoang

    Arom77

    Hi @Hoang Ngo

    Actually there are not MySQL logs on my server because they don't exist by default. And if you activate it kills server performances.

    Anyway, I think I found the cause. I think it's due to virtual memory running out. I lowered the memory Wordfence request when scanning and hope it solves the problem. But if it's really the problem, how can we prevent Pro Sites to loose its settings when virtual memory runs out, or at least make it alert me that settings've just been lost?

    Best regards

    Hoang Ngo

    @Arom77,

    This seems a server issue, at this level, ProSites or even WordPress, can't do much. I think you will need to track what's the main reason causing the memory issues, and then we can fix it.

    Usually, when memory run out, some services will be terminated, example mysql server, or the mail server. For the log, you don't have to make it log every things, just need to log the errors.

    Best regards,
    Hoang

    Michelle Shull

    Hi, Arom!

    It sounds like you need a better server. I've got ProSites running on a shared server with pretty limited resources and I'm not seeing anything like what you experience. It may be time to find a new host that has the hardware to support the site you want to create. There's little we can do in the code of a plugin to compensate for a server that's so unstable. From the sounds of things, you'd be having issues at some point down the line with this host no matter what kind of site you were trying to run.

    Thanks for your question, please consider upgrading your host. (And I believe it will resolve your other issue here, as well.)

    Arom77

    Hi @Michelle Shull,

    No, the problem is not the server. 99% of the time I'm using less than 30% of my total memory.
    The problem is that from time to time "something" happens that causes a memory peak. I sure should find what causes that and I'm trying to find it but for the moment I haven't succeeded. This is not the server which is unstable, this is something (probably a WP plugin) that from time to time causes that.

    Anyway, when this happens, my site is just unavalaible during just a few seconds. The only real problem is that Pro Sites looses all its settings when it happens. That's the only plugin which looses its setting when this happens!

    How could you prevent Pro Sites to loose its settings each time a server has a minor issue (there are some other topics about this point).

    Thanks!

    Michelle Shull

    Hi, Arom!

    As Hoang explained, there's very little, if anything, we can do because the issue is happening on your server. Let me ask the developer if there is any more insight we can provide here, but until you find out why your memory is spiking, our hands are tied on what we can do. There's nothing to "fix" in ProSites, it's working like it should.

    Is there anything at all you can think of that could be a factor here? When you look at your plugins list, which ones stick out to you as being the most robust? (As in they write a lot of data to the database, or have the most features, or change the functionality of WordPress in a major way.)

    Thanks, Arom!

    Arom77

    Hi @Michelle Shull

    No, the issue is not happening on my server. The issue is that Pro Sites looses all its settings each time a server (not only mine, any server!) has a problem, even a very little one.

    I don't know about any other WP plugin that looses its settings each time a server has a problem!

    As I said, there are several other topics from other people here about the same problem.
    Everyone sees the same thing: each time a server has a problem :
    1/ Pro Sites looses all its settings,
    2/ It's the only plugin that looses its settings.
    3/ We have to set back all settings as soon as possible, because Pro Sites is a very important plugin for all of us.

    There has to be something that you can do to avoid this behaviour that no other plugin has. Pro Sites is crucial for all of us. Again, we are a lot of people here experiencing that and it didn't start yesterday.

    There's nothing to "fix" in ProSites, it's working like it should.

    Yes, it's working like it should... EXCEPT FOR THAT POINT.

    Thanks!

    Michelle Shull

    Hi, Arom!

    I've reported the issue to the developer and asked for his feedback with a flag and a note in our internal communication. I know Hoang has spent hours looking into this, and I trust what he says he's seeing, hopefully the dev will have some time to offer some feedback here.

    I'm also pinging @Jack Kitterhing, our Project Manager. He knows the code for every one of our hundreds of products just about as well as the devs do, perhaps he'll have some insight here as well.

    I understand that you're frustrated, we're trying to figure out what's going wrong here as fast as we can. Until I (or we) hear back from @Hoang Ngo, Jack, or the dev, what I can do is very limited. It's the weekend, so I'm not sure on the availability of any of the three at the moment.

    Thanks for your patience. I'm sorry if I've misunderstood anything here. We're trying to help as best we can.

    Arom77

    Hi @Michelle Shull

    I know, didn't mean to be rude, really! I know you try to help - and you do it well.
    And I know that's the week-end too. Sorry if I was rude ("pardon my french" ).

    I don't want it to be fixed "right now". If you say you have a solution and that it'll be fixed let's say in 2 or 3 months I'll be perfectly happy with it!

    Just don't want to stay forever in this situation where I can't trust Pro Sites on the long run.

    Cheers!

    Arom77

    Hi @Jack Kitterhing

    Sorry for the delay...

    I have server logs but don't know what to look at.
    I know the problem is related to RAM usage. From time to time it runs out of RAM so Pro Sites looses all its settings. But 99% of the time RAM usage is low.
    For example right here:
    Real memory 1.91 GB used, 6 GB total
    Virtual memory 202.84 MB used, 512 MB total

    Other infos:
    Processor information Intel(R) Xeon(R) CPU E5-1620 v2 @ 3.70GHz, 2 cores
    Operating system Debian Linux 6.0
    Webmin version 1.760
    Virtualmin version 4.18.gpl
    Kernel and CPU Linux 2.6.32-37-pve on i686
    Running processes 88
    CPU load averages 0.35 (1 min) 0.47 (5 mins) 0.57 (15 mins)
    CPU usage 15% user, 1% kernel, 0% IO, 84% idle
    Apache version 2.2.16

    Do you need other infos? Please advise.

    Thanks!
    Cheers

    Jude

    Hey @Arom77

    Just quickly checking in here .. Jack is no longer available on the forums. The stable version of Pro Sites is now 3.5.x.x which is almost a full rewrite of the version you had installed when you opened this ticket 3.4.x.x.

    Can you still confirm if this issue still persists ?

    Also if you are using a InnoDB instance can you simply lock the row psts_settings from getting overwritten as shown here. Do this once all the settings are final.

    http://stackoverflow.com/questions/14965004/prevent-database-table-row-from-delete-and-update

    Cheers
    Jude

    Arom77

    Hi @Jude

    The problem occured once again yesterday. My server went under attack so the server crashed, it went back after a minute and everything is normal now. Except I've just seen Pro Sites lost all its settings.

    It hasn't happened since my previous message, so the situation is definitely better, but is there really no way to prevent Pro Sites loosing settings ? It's the only plugin which does such a things on my differents sites.

    Thanks!

    Michael Bissett

    Hey @Arom77, Michael here!

    Inside of phpMyAdmin, when you're browsing a database, you'll want to check out the "Type" column, that'll indicate whether a table's using InnoDB or MyISAM.

    I've taken a peek at your sitemeta table (the table where Pro Site's settings are stored) is a MyISAM table, and have informed my colleague @Jude of this, so that he can advise further.

    Kind Regards,
    Michael

    Jude

    Hey @Arom77

    I too used the creds that @Michael Bissett shared and looks like something really strange is going on. There are 2 dozen psts_settings rows on the sitemeta table. It should be one ideally. See screenshots

    Here is what Im going to propose.

    Please take a backup first.
    I'll then clean these tables up for you.
    Then you enter the settings one last time
    I go ahead and set up a MySQL Trigger that prevents this new settings row from getting deleted.

    Let me know if this is acceptable and whenever you are ready

    Cheers
    Jude

    Arom77

    Hi @Jude

    Thanks a lot for your proposal!

    I've got an automatic backup of my whole site every day.
    I'm not sure how I could make a manual backup of only the database... Is there an "easy" way to do it?

    I go ahead and set up a MySQL Trigger that prevents this new settings row from getting deleted.

    Just to be sure: I'll be able to change my Pro Sites settings anytime once you did that?

    Cheers

    James Morris

    Hello Arom77,

    I sincerely apologize for the extreme delay in response here. Is this still something you are needing assistance with? If so, I'll be happy to move forward with helping you.

    Please visit the Contact page and complete the form with the following information:

    Subject: "Attn: James Morris"

    In the Message box, please provide the following:

    - link back to this thread for reference
    - any other relevant urls

    - Admin login:
    Admin username
    Admin password
    Login url

    - Hosting Control Panel Login
    Admin username
    Admin password
    Login url

    - FTP credentials
    host
    username
    password
    (and port if required)

    Best regards,

    James Morris

    Mahlamusa

    Hello Arom77.

    I hope you are having a great day so far.

    I will be working with you to help resolve this issue. I used the login details you have provided and have noticed that the has be a lot of duplicates for the 'psts_settings' field in the database. In order proceed, I first need to delete all the duplicates and leave only one as there should be only one in the database. Having more than one setting will result in a failure when getting the stored settings.

    I am not sure yet what causes the error but to solve it, I have to remove the duplicate settings first. Please create a backup of the current database especially the table 'cssn_sitemeta' which contains the Pro Sites settings along with a lot of other data.

    Please confirm that the current settings are as you want them to be then afterwards I will continue with the investigation and hopefully will find a solution soon.

    Thank you for your patience as we resolve this. Enjoy the rest of your day.

    Cheers,
    Mahlamusa

    Mahlamusa

    Hello Arom77,

    I am sorry for the late reply. I have gone through the process of deleting the duplicate pro sites settings and I have completed it today.

    I am not sure yet what causes the plugin to save duplicate settings as there should only be one 'psts_settings' in the sitemeta table and that should be updated with the newly updated value each time you save. I have tested this now and it is currently updating the only value I have left there.

    If I may ask, did you or anyone at some point restored a database backup for this site or do you / did you have a database backup plugin that deals with backing up and restoring databases? I ask this because if there was such a backup restore, that could be the start of the duplicated settings as the sitemeta table does not force unique values in its 'meta_key' column. This could result in a new duplicate being added each time this 'restore' happens.

    Now since your database has only one set of Pro Sites settings, we should try to wait and see what happens, but from what I have seen the settings are now updated instead of being duplicated. The reason why it is duplicated is that when the plugin tries to update one meta_key and find that the table has multiple meta_keys with the same name in the same column, it will fail.

    WordPress is designed to create a meta_key if updating an existing one fails. So if it fails because there are many with the same name, then it defaults to creating a new meta_key. The same thing applies if it tries to read, if it fails then it assumes there is nothing and this is when Pro Sites decides to store the default values because WordPress failed to get the meta_key. Pro Sites along with other plugins rely on core WordPress functions and if those functions are not reliable Pro Sites and other plugins will seem to be unreliable.

    Let us give this another shot and see how it goes. If it fails again then I will proceed further with investigating the problem and I will try other suggested solutions.

    I hope this helps. Please let me know if this happens so I can get to it right away.

    I hope you enjoy your day further.

    Cheers,
    Mahlamusa

    Arom77

    Hi Mahlamusa

    did you or anyone at some point restored a database backup for this site or do you / did you have a database backup plugin that deals with backing up and restoring databases

    There is a daily backup of the VPS. And when all Pro Sites settings disappear it only happens during the backup process (I mean this is the only moment when all Pro Sites settings may disappear).
    There has never been a database restore for this site.

    All Pro Sites settings disappeared again a few hours ago (when the VPS backup ran up) (I've already set them back again manually).

    Thanks!

    Cheers

    Mahlamusa

    Hi Arom77,

    I have done another extensive digging into this issue and now I can confirm that this issue is not related to Pro Sites, it is the process that does the backups that cause the errors.

    During my research I found that Webmin/Virtualmin uses the 'mysql_dump()' function to create a backup of the database. The 'mysql_dump()' function should be used in a way that it locks the database when backing it up, if it does not then data integrity will not be guaranteed. To create a backup using the mysql_dump() function then the database server should be stopped and in some cases a lock can be issued on the database to avoid writing and deleting while the backup is in progress. I think virtualmin/webmin does the backups while the database server is running and this is the cause for the issue at hand.

    If you can access the script that does the backup, you should verify that it has the line that stops the database server, lock the tables, create the backup, release locks and start database server again. You should have a line like this:

    mysqldump --user=root --password=yourrootsqlpassword --lock-all-tables

    That line will make sure all tables are locked and thus avoiding the read/write that causes the duplicate resulting in settings disappearing. This method is not recommended because your website will not be available when the backup is running since the database server should be stopped and tables locked, that means there will be no possible data updates in the database and the website will show database connection errors. I think that is why webmin/virtualmin does the backups the way it does now. This not recommended as your backups are running as long as 10 minutes for each site on your server, that will be a lot of inconvenience for your visitors.

    To learn how to create backups without interrupting the database server, you may want to follow this article

    I also found that Pro Sites is not the only plugin that suffers from this issue but a few others. I have attached screenshots showing duplicate settings for 2 other plugins. Please see the screenshots below:

    The screenshots above show that Pro Sites is not the only plugin having issues and that this issue is not caused by Pro Sites. Unfortunately server administration or optimization is beyond the scope of this support forum.

    I am truly sorry that we can't go deep into the core of your control panel to troubleshoot the problem, but that is the limitation of the support forum.

    I hope you understand.

    Please enjoy your day further.

    Cheers,
    Mahlamusa

    Arom77

    Hi Mahlamusa

    I'm working on this major issue (the problem happens almost every day).
    As far as I know Virtualmin does a "lock-tables". Could you explain to me why a "lock-all-tables" is required? Is it related to the way WordPress Multisite work? Or the way Pro Sites and some WPMUdev plugins work?

    I'm being told that this is never required and that it's dangerous to change the way Virtualmin does the back-up...

    Thanks!

    WP-Networks

    Arom77 are you using MyISAM and InnoDB tables together in the same database?

    Personally, I use only InnoDB and, when I'm being extra careful, I do mysqldump with these options
    --lock-all-tables --extended-insert=FALSE --complete-insert=TRUE
    which add some time to the process, especially import/restoration time - however, they also help ensure that I have good (as in restorable) backups... you should prob do some reading to learn a bit about this so that you can make some informed decisions about how to proceed (eg. perform your own backups w/o virtualmin; migrate to different control panel)... this stuff can get a bit complicated... for your next step you might try cleaning up the duplicate database entries and then use lock-all-tables option with mysqldump from command line and see if you are able to successfully make the backup and the restoration... and do some googling about the mysqldump options. Good luck

    Cheers, Max

    Mahlamusa

    Hello Arom77.

    I hope you are doing great today.

    I have recommended the --lock-all-tables to prevent updates/writes while the backup is in progress this is to prevent the possibility of duplicates.

    The functions that WordPress use to update a record are created so that when it fails to update a record, its either the data in the current record is the same as the one it should be updated with so if its not the same but it fails to update then it assumes the record does not exist and hence create a new record with the new data. This can happen if the table row is locked that is why I think locking the whole table is a best option to prevent this from happening. Unfortunately, that behavior is not unique to WPMU DEV plugin but all plugins that comply to WordPress standards.

    I'm being told that this is never required and that it's dangerous to change the way Virtualmin does the back-up

    I honestly understand this and the reason why they said it but if you may do if you are solving a problem or better ask the developers of virtual mean to check how they implemented the backup system. I would also recommend you try WP-Networks's suggestion (Thank you WP-Networks for that useful addition. )

    --lock-all-tables --extended-insert=FALSE --complete-insert=TRUE

    You should also check if you are not using different database engines on the same database because this may cause issues as MyISAM and InnoDB do not handle data in the same way. I recommend you use the same engine as that used by default WordPress tables for all extra tables you may have in the same database.

    If you are having issues with this, you may also try to do backups in another way. You may use any WordPress Backups plugin for that like Snapshot Pro, UpdraftPlus or any other WordPress backup plugin. These can help you backup your website files and database, then you can use Virtualmin's built in backup system to backup your accounts and emails. I think that will save you the trouble of having to deal with this issue now and then.

    I hope that helps you. Please enjoy the rest of your day

    Cheers,
    Mahlamusa

    Arom77

    Hi Mahlamusa

    I decided to convert all my tables of every sites on my VPS from MyISAM.
    Everything went right for every sites, except for creersonsite.net
    As you can see I get a 500 error. Actually I get it as soon as I type something into the SQL query area (but the query works OK though when I run it).
    As a result, all Pro Sites settings instantanely dissapeared. So I guess this is it. But do you have an idea about why the problem only happens with creersonsite? I have another WP Multisite on this VPS and no problem with it.

    Still need your help to figure this out.
    Thanks!

    Mahlamusa

    Hi Arom77,

    I hope you are doing great today and I am sorry it took me so long to respond to your last post, I must have missed the notification about your post, I am sorry about that.

    I have looked at one of your sites that looks like a multi site, the site 'creer-son-site' has Pro Sites settings on its database, I can confirm that this site does not have the duplicates in its 'prefix_sitemeta' table, that is why you are not having issues with it. Unfortunately, I am not familiar with your hosting control panel so it is not easier for me to compare the database tables using the database tools in your control panel.

    I think you have to clean the duplicates first then convert the database engine, I tried to do that for you but unfortunately there is a lot of duplicates from other plugins as well, I was only able to clean those related to Pro Sites and didn't want to break anything and I stopped the. I suggest you clean those duplicates then run the query to change the database engine. I have tried to run it but it returned no results, again I am not sure if it was successful or not and I am not sure how to check if it was successful due to my limited experience with your hosting control panel.

    I am really sorry that you are going through so much trouble with regards to this issue, I know it must be very hard for you to have to deal with this. I want you to know we are always here for you and we will try to help where we can.

    I hope you enjoy the rest of your day.

    Cheers,
    Mahlamusa

    Arom77

    Hi Mahlamusa

    I'm cleaning up the mess right now so I've other questions of course

    First one:
    I see some duplicates for nbt_plugin_version
    But none of them had 2.8.5 as a value (New Blog Templates is in version 2.8.5 at the moment). I deleted all duplicates and just keep one with 2.8.4 but I don't understand why there was no 2.8.5?

    Seconde one:
    There is a LOT of domainmap-flushed-rules-XXXX meta keys. What are they for? Do I need to keep them all or can I delete them ?
    Same fact and same question for _site_transient_user_activity_,
    _site_transient_timeout_user_activity_, _site_transient_timeout_wdp_un_changelog_ and a few others

    EDIT:
    All duplicates are deleted now.
    Is the bug expected to have gone away now? Or do I have other thing to do?

    Thanks

    Mahlamusa

    Hello Arom77,

    I hope you are doing great today, sorry for the delayed reply and thank you for your patience.

    First One: there should only be one 'nbt_plugin_version' entry with the latest version of the plugin.

    Second One: I am not sure which plugin is setting the '_site_transient_user_activity_' meta, it could be WordPress Core and a lot of other plugins so I can't be sure whether to delete or keep it. Ideally, if they are of the same user or blog_id and have the same values you should delete the oldest and keep the newest.

    Is the bug expected to have gone away now? Or do I have other thing to do?

    If you have cleared the duplicates and changed the database as recommended, then you should not have the same issue again. You should just finish your cleanup and create a backup of the database. You can use Snapshot to create an easy to restore database on the "Snapshot > Snapshots > Create Snapshot" page, in the options you should exclude files as your server is already creating backups of your files.

    I hope this helps. Please let us know if you have any further questions.

    Have a nice day. Cheers,
    Mahlamusa

    Arom77

    Hi Mahlamusa

    there should only be one 'nbt_plugin_version' entry with the latest version of the plugin.

    I deleted the duplicated and kept just one instance (but it's noted 2.8.4 and not 2.8.5 as it should). I guess I'll see what happens when this plugin is updated

    For the rest I've finished the cleanup. I'll keep you updated if the issue happens again. For now let's mark this topic as resolved

    Cheers and a big thanks for your help!