WordPress.org

Ready to get started?Download WordPress

Forums

Backup Scheduler
[resolved] Feature Request - DROP TABLE IF EXISTS (18 posts)

  1. wallyO
    Member
    Posted 1 year ago #

    Hello Sed Lex,
    thank you for your very useful plugin.
    I have been experimenting with your plugin taking backups from my multi-site production site and restoring them to my development server.
    I have successfully moved both a complete multi-site install and separately one sub domain site to a single wordpress installation.
    I first run 2 find and replace to fix the domain name and the media path in the backup sql.
    In one of the experiments, I imported a single site backup into a default wordpress single site install but had the import fail because the tables already existed.
    I had to manually delete the tables that were the same names as those in the backup sql. Not all wordpress tables were in the backup sql, so this adds to the complexity of a restore, particularly for a restore of a single site to the multi-site production server.
    If the CREATE TABLE command were proceeded by the DROP TABLE IF EXISTS command, the import should run without error no matter whether the tables exist or not, and would be much more convenient.
    Perhaps I am missing something, but it seems to me that the backups would be much more user friendly to restore if they had this feature.

    http://wordpress.org/extend/plugins/backup-scheduler/

  2. wallyO
    Member
    Posted 1 year ago #

    WP-DBManager for instance has DROP TABLE IF EXISTS statement in it's backup sql so it can restore over existing tables.

  3. Sed Lex
    Member
    Plugin Author

    Posted 1 year ago #

    Ok ... this feature will be in the next version (I have just coded it)

  4. wallyO
    Member
    Posted 1 year ago #

    Thank you for your time and effort.
    I look forward to giving it a test run.

  5. Sed Lex
    Member
    Plugin Author

    Posted 1 year ago #

    Have you tested ?

  6. wallyO
    Member
    Posted 1 year ago #

    Just did,sorry to take so long.
    My first test failed.
    It appears the drop table succeeds, but before you can insert into a table that has been dropped you must have a Create table command to recreate the table.
    For instance a typical phpmyadmin export has the lines:

    DROP TABLE IF EXISTS'wp_commentmeta';
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE 'wp_commentmeta'

    Please nor I have replaced sql backticks with single quotes so I can post it.

  7. Sed Lex
    Member
    Plugin Author

    Posted 1 year ago #

    Errr ... Normally, the sql extract file shoudl have the two entries :
    - one for dropping the table if it exists ;
    - one for creating the new table

    You do not have them ?

    $entete .= "DROP TABLE IF EXISTS ".$table[0].";\n";
    $entete .= $wpdb->get_var("show create table ".$table[0], 1).";";
  8. wallyO
    Member
    Posted 1 year ago #

    [ Moderator Note: Please post code or markup snippets between backticks or use the code button. ]

    No I do not.
    Here is the start of a backup for a single site in a multisite install.

    -- -----------------------------
    -- CREATE wp_3_commentmeta
    -- -----------------------------
    DROP TABLE IF EXISTS wp_3_commentmeta;
    ;
    
    -- -----------------------------
    -- CREATE wp_3_comments
    -- -----------------------------
    DROP TABLE IF EXISTS wp_3_comments;
    ;
    
    -- -----------------------------
    -- CREATE wp_3_links
    -- -----------------------------
    DROP TABLE IF EXISTS wp_3_links;
    ;
    
    -- -----------------------------
    -- CREATE wp_3_options
    -- -----------------------------
    DROP TABLE IF EXISTS wp_3_options;
    ;
    
    -- -----------------------------
    -- CREATE wp_3_postmeta
    -- -----------------------------
    DROP TABLE IF EXISTS wp_3_postmeta;
    ;
    
    -- -----------------------------
    -- CREATE wp_3_posts
    -- -----------------------------
    DROP TABLE IF EXISTS wp_3_posts;
    ;
    
    -- -----------------------------
    -- CREATE wp_3_term_relationships
    -- -----------------------------
    DROP TABLE IF EXISTS wp_3_term_relationships;
    ;
    
    -- -----------------------------
    -- CREATE wp_3_term_taxonomy
    -- -----------------------------
    DROP TABLE IF EXISTS wp_3_term_taxonomy;
    ;
    
    -- -----------------------------
    -- CREATE wp_3_terms
    -- -----------------------------
    DROP TABLE IF EXISTS wp_3_terms;
    ;
    
    -- -----------------------------
    -- INSERT INTO wp_3_commentmeta
    -- -----------------------------
    
    -- -----------------------------
    -- INSERT INTO wp_3_comments
    -- -----------------------------
    
    INSERT INTO wp_3_comments VALUES(1, 1, 'Mr WordPress', '', 'http://theme.dev/', '', '2012-08-23 12:33:55', '2012-08-23 12:33:55', 'Hi, this is a comment.To delete a comment, just log in and view the post's comments. There you will have the option to edit or delete them.', 0, '1', '', '', 0, 0);
  9. Sed Lex
    Member
    Plugin Author

    Posted 1 year ago #

    Arffff ...

    I have just ran a backup and I have the following

    -- -----------------------------
    -- CREATE wp_commentmeta
    -- -----------------------------
    DROP TABLE IF EXISTS wp_commentmeta;
    CREATE TABLE 'wp_commentmeta' (
      'meta_id' bigint(20) unsigned NOT NULL auto_increment,
      'comment_id' bigint(20) unsigned NOT NULL default '0',
      'meta_key' varchar(255) default NULL,
      'meta_value' longtext,
      PRIMARY KEY  ('meta_id'),
      KEY 'comment_id' ('comment_id'),
      KEY 'meta_key' ('meta_key')
    ) ENGINE=MyISAM AUTO_INCREMENT=283 DEFAULT CHARSET=utf8;
  10. Sed Lex
    Member
    Plugin Author

    Posted 1 year ago #

    Obviously the code :
    $wpdb->get_var("show create table ".$table[0], 1)
    do not work on your installation ... I have to find out why !

  11. Sed Lex
    Member
    Plugin Author

    Posted 1 year ago #

    Does your installation run MySQL ? or it is an other database engine ?

  12. wallyO
    Member
    Posted 1 year ago #

    I just ran used backup scheduler on a single site wordpress installation on the same server and it created a good backup file as in your post Arffff ...
    The installation which is not creating good backup sql is a multisite installation on the same server. Server is Lamp with cPanel MySQL 5.0.92 PHP 5.2.17.
    The instalation is on a multi db (16 database), which is most likely the cause of the problem.
    I should have listed full environment in my first post. Sorry.

  13. Sed Lex
    Member
    Plugin Author

    Posted 1 year ago #

    Ok I will have a look based on this information.
    I swear I will find a solution

  14. wallyO
    Member
    Posted 1 year ago #

    I have just tested two versions of this multisite installation on my local development server. It is a xampplite server on windows.

    First was using the original single database version from before I migrated the data to the multi database. Backup scheduler created a good backup sql for a single site in the multisite install.

    Next I tested the same installation but this time with the multi database connection. Backup scheduler created broken sql as I posted above with missing create table sections.

    So that is two separate multi database installations giving problems, one on a Development and one on a Production server. I am using the multi-db plugin from wpmudev when I connect to the multi databases.

  15. wallyO
    Member
    Posted 1 year ago #

    Got it.
    The bug is with wpmudev multi-db.
    On line 512 of db.php replace the code

    } else if ( preg_match('/^\s*SHOW CREATE TABLE<code>?(\w+?)</code>?\s*/is', $query, $maybe) ) {

    with

    } else if ( preg_match('/^\s*SHOW CREATE TABLE<code>?(\w+)</code>?\s*/is', $query, $maybe) ) {

    Backup Scheduler is now fully functional on my multisite multi database installation.
    I regret your time I have wasted Sed Lex. The issue was never with your plugin.
    I will post the bug to wpmudev tomorrow.

  16. Sed Lex
    Member
    Plugin Author

    Posted 1 year ago #

    Ohh !
    I would not have found such bug : I would have believed that the error would have came from my plugin : thanks for identifying the bug

    Could you give me the ticket number to follow the resolution of the bug ?

  17. wallyO
    Member
    Posted 1 year ago #

  18. Sed Lex
    Member
    Plugin Author

    Posted 1 year ago #

    Wonderful ! I will have a close look to the resolution

Topic Closed

This topic has been closed to new replies.

About this Plugin

About this Topic