• The database backup feature is not working on my site. Over a year ago I have found the cause of the problem and a solution too:
    https://github.com/ithemes/iThemes-Security/issues/78

    Now I see the problem is still there. This makes me sad. It’s so easy to fix the problem but no one cares.

    What is even wore:
    When I enable the “Scheduled Database Backups” I get random error 500 while accessing the site (on frontend and backend too).

    I cannot do anyting more. Are you going to fix the problem or do we have to resign to use the feature?

    By the way: when I enable the recommended feature “Disable File Locking” I will get instant 500 error on all pages – frontend and backend too.

    https://wordpress.org/plugins/better-wp-security/

Viewing 13 replies - 1 through 13 (of 13 total)
  • Thread Starter vladax

    (@vladax)

    Solution #1: Skip the large tables

    1. Install the WP-Optimize plugin:
      https://wordpress.org/plugins/wp-optimize/
    2. Click on the WP-Optimize name on the left pane and then on TABLES on top.
    3. Write down the names of tables with more than 10 000 records if you are sure that you don’t need those tables in the backup. Large tables are usually lists of hits that you generally don’t need in the backup.
    4. Uninstall the WP-Optimize plugin or keep it – it is a handy plugin.
    5. Download from your site this file:
      wp-content/plugins/better-wp-security/modules/free/backup/class-itsec-backup.php
    6. Open the file in a text editor (not Notepad).
    7. Locate the line 134:
      if ( in_array( substr( $table[0], strlen( $wpdb->prefix ) ), $this->settings['exclude'] ) === false ) {
    8. Enter above this line a new line where wp_wassup is the name of the table you want to skip:
      if ($table[0] == "wp_wassup") continue;
    9. Repeat step 7 for other tables you want to skip.
    10. Upload the file from your computer to the server.

     
    Solution #2: Fix the memory overflow

    1. Download from your site this file:
      wp-content/plugins/better-wp-security/modules/free/backup/class-itsec-backup.php
    2. Open the file in a text editor (not Notepad).
    3. Locate the line 134:
      if ( in_array( substr( $table[0], strlen( $wpdb->prefix ) ), $this->settings['exclude'] ) === false ) {
    4. Locate the line 165:
      $return .= PHP_EOL . PHP_EOL;
    5. Change the code between those lines to:

      if ( in_array( substr( $table[0], strlen( $wpdb->prefix ) ), $this->settings[‘exclude’] ) === false ) {
      $from_result = 0;
      $num_results = $wpdb->get_var( ‘SELECT COUNT(*) FROM ‘ . $table[0] . ‘;’);
      while ($from_result < $num_results)
      {
      $result = $wpdb->get_results( ‘SELECT * FROM ‘ . $table[0] . ‘ LIMIT ‘ . $from_result . ‘, 1000;’, ARRAY_N );
      foreach ( $result as $row ) {
      $return .= ‘INSERT INTO `’ . $table[0] . ‘` VALUES(‘;
      for ( $j = 0; $j < $num_fields; $j ++ ) {
      $row[$j] = addslashes( $row[$j] );
      $row[$j] = preg_replace( ‘#’ . PHP_EOL . ‘#’, “\n”, $row[$j] );
      if ( isset( $row[$j] ) ) {
      $return .= ‘”‘ . $row[$j] . ‘”‘;
      } else {
      $return .= ‘””‘;
      }
      if ( $j < ( $num_fields – 1 ) ) {
      $return .= ‘,’;
      }
      }
      $return .= “);” . PHP_EOL;
      }
      $from_result += 1000;
      }
      }
      $return .= PHP_EOL . PHP_EOL;

    6. Upload the file from your computer to the server.

     
    Solution #3: Use another plugin
    Disable the Database Backup feature and use another plugin. There are many plugins with DB Backup feature. I can recommend the BackWPup Free:
    https://wordpress.org/plugins/backwpup/
     
    I prefer solution #3. The BackWPup plugin allows you to backup all the files from your site too.

    Thread Starter vladax

    (@vladax)

    Thanks @vladax, so it is a database table size issue and not a Multisite issue. Makes sense because Multisite and SlimStat surely increase table sizes.
    I taught the step from Better WP Security to iThemes Security would further increase the stability of this plugin, but this seems to be not happen – or this is left intentional to redirect to BackupBuddy?
    I think I will look for Solution #3 to have something automatic and reliable and still free.
    Do you use Multisite together with BackWPup Free?

    Thread Starter vladax

    (@vladax)

    Yes, the BackWPup works in Multisite environment but the plugin can only be used as super admin from the network admin administration.

    http://premium.wpmudev.org/blog/5-backup-solutions-for-wordpress-multisite-networks/

    Thx!

    I’m network admin and do care for all MS-Blogs, so this is no problem. I just would like to hear from someone who has experience with the WP MS + BackWPup solution, that it is fast and stable.

    I’m now testing Backup Scheduler.

    @vladax
    @solution #2: Fix the memory overflow

    I see what you are trying to do.
    Prevent all the table data from being loaded into $result at once.

    However it seems to me you are shifting the same issue to $return …

    You start with 1000 rows, then add another 1000 rows etc.
    At some point, let’s say after 7000 rows added to $return, adding the next 1000 rows to $return may lead to the same error message …

    But I could be wrong. If so please explain it to me.
    Why could $return variable store more data in memory than $result variable ?

    Perhaps because $result is an array variable, while $return is not …

    dwinden

    Solution 2 worked for me. Thanks.

    Thread Starter vladax

    (@vladax)

    Hi dwinden,

    I’m not a PHP expert so I’m not sure why it works. But it works.

    The fact is that if you read a long table at once you can get the memory overflow error.

    However, returning a long string should be safe.

    Hi Vladax,

    Ok, no worries.
    Like I already suspected, an array variable uses a LOT of memory in PHP …

    Read this interesting article:

    https://nikic.github.io/2011/12/12/How-big-are-PHP-arrays-really-Hint-BIG.html

    So it is no memory leak issue.
    Just not a good idea to read all table data at once into an array …
    So now I understand why solution #2 works.
    Thanks for your contribution to this issue. Well done

    dwinden

    hi, i installed “backWPup” but i have errors. ANYONE KNOWS WHAT I SHOULD DO?
    (Pueden responder en español tambien.)

    [19-ago-2015 13:33:42] 1. Try to backup database …
    [19-ago-2015 13:33:43] Conectado de base de datos jp000750_wordpre sur localhost
    [19-ago-2015 13:33:43] Backup database table “wp_commentmeta” with “0” records
    [19-ago-2015 13:33:43] Backup database table “wp_comments” with “2” records
    [19-ago-2015 13:33:43] Backup database table “wp_icl_string_translations” with “3386” records
    [19-ago-2015 13:33:43] Backup database table “wp_icl_strings” with “1700” records
    [19-ago-2015 13:33:43] Backup database table “wp_links” with “0” records
    [19-ago-2015 13:33:43] Backup database table “wp_options” with “201” records
    [19-ago-2015 13:33:43] Backup database table “wp_postmeta” with “1560” records
    [19-ago-2015 13:33:43] Backup database table “wp_posts” with “265” records
    [19-ago-2015 13:33:43] Backup database table “wp_term_relationships” with “119” records
    [19-ago-2015 13:33:43] Backup database table “wp_term_taxonomy” with “38” records
    [19-ago-2015 13:33:43] Backup database table “wp_terms” with “38” records
    [19-ago-2015 13:33:43] Backup database table “wp_usermeta” with “46” records
    [19-ago-2015 13:33:43] Backup database table “wp_users” with “2” records
    [19-ago-2015 13:33:43] Añadido respaldo de DB “jp000750_wordpre.sql” con 1,80 MB a la lista de ficheros a respaldar
    [19-ago-2015 13:33:43] Database backup done!
    [19-ago-2015 13:33:43] 1. Trying to make a list of folders to back up …
    [19-ago-2015 13:33:49] 732 folders to backup.
    [19-ago-2015 13:33:49] Restart will be executed now.
    [19-ago-2015 13:33:50] 1. Trying to generate a file with installed plugin names …
    [19-ago-2015 13:33:50] Añadido archivo de lista de plugins “M2C-DESIGN.pluginlist.2015-08-19.txt” con 1,06 kB a la lista de archivos de respaldo.
    [19-ago-2015 13:33:50] 1. Trying to generate a manifest file …
    [19-ago-2015 13:33:50] Added manifest.json file with 4,13 kB to backup file list.
    [19-ago-2015 13:33:50] 1. Trying to create backup archive …
    [19-ago-2015 13:33:50] Compressing files as PclZip. Please be patient, this may take a moment.
    [19-ago-2015 13:34:17] Restart after 28 seconds.
    [19-ago-2015 13:34:46] Restart after 28 seconds.
    [19-ago-2015 13:35:15] Restart after 28 seconds.
    [19-ago-2015 13:35:44] Restart after 28 seconds.
    [19-ago-2015 13:36:12] Restart after 28 seconds.
    [19-ago-2015 13:36:44] Restart after 31 seconds.
    [19-ago-2015 13:37:15] Restart after 30 seconds.
    [19-ago-2015 13:37:24] Creado fichero de respaldo.
    [19-ago-2015 13:37:24] Tamaño de archivo es 211,99 MB.
    [19-ago-2015 13:37:24] 8548 ficheros con 338,71 MB en Archivo
    [19-ago-2015 13:37:24] Restart will be executed now.
    [19-ago-2015 13:37:25] 1. Try to send backup with email …
    [19-ago-2015 13:37:25] Sending email to mcalvo1980@hotmail.com
    [19-Aug-2015 16:37:27] PHP Fatal error: Allowed memory size of 268435456 bytes exhausted (tried to allocate 222286831 bytes) in /home/jp000750/public_html/wp-content/plugins/backwpup/vendor/SwiftMailer/classes/Swift/Mime/SimpleMimeEntity.php on line 356 [19-ago-2015 13:37:27] ERROR: Allowed memory size of 268435456 bytes exhausted (tried to allocate 222286831 bytes)
    [19-ago-2015 13:37:27] Restart will be executed now.
    [19-ago-2015 13:37:27] 2. Try to send backup with email …
    [19-ago-2015 13:37:27] Sending email to mcalvo1980@hotmail.com
    [19-Aug-2015 16:37:28] PHP Fatal error: Allowed memory size of 268435456 bytes exhausted (tried to allocate 222286831 bytes) in /home/jp000750/public_html/wp-content/plugins/backwpup/vendor/SwiftMailer/classes/Swift/Mime/SimpleMimeEntity.php on line 356 [19-ago-2015 13:37:28] ERROR: Allowed memory size of 268435456 bytes exhausted (tried to allocate 222286831 bytes)
    [19-ago-2015 13:37:28] Restart will be executed now.
    [19-ago-2015 13:37:30] 3. Try to send backup with email …
    [19-ago-2015 13:37:37] Sending email to mcalvo1980@hotmail.com
    [19-Aug-2015 16:37:38] PHP Fatal error: Allowed memory size of 268435456 bytes exhausted (tried to allocate 222286831 bytes) in /home/jp000750/public_html/wp-content/plugins/backwpup/vendor/SwiftMailer/classes/Swift/Mime/SimpleMimeEntity.php on line 356 [19-ago-2015 13:37:38] ERROR: Allowed memory size of 268435456 bytes exhausted (tried to allocate 222286831 bytes)
    [19-ago-2015 13:37:38] Restart will be executed now.
    [19-ago-2015 13:37:38] ERROR: Etapa abortada: demasiados intentos !
    [19-ago-2015 13:37:38] ERROR: Job mit Fehlern beendet in 236 Sekunden. Sie müssen die Fehler für eine korrekte Ausführung beheben.

    Post your issue in the correct forum:

    https://wordpress.org/support/plugin/backwpup

    dwinden

Viewing 13 replies - 1 through 13 (of 13 total)
  • The topic ‘Database backup error, cause and solution’ is closed to new replies.