[Plugin: Backup Scheduler] Feature Request – DROP TABLE IF EXISTS

  • Resolved wallyO

    (@wallyo)


    12 years, 6 months 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.

    https://www.ads-software.com/extend/plugins/backup-scheduler/

Viewing 15 replies - 1 through 15 (of 17 total)
  • Thread Starter wallyO

    (@wallyo)

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

    Plugin Author Sed Lex

    (@sedlex)

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

    Thread Starter wallyO

    (@wallyo)

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

    Plugin Author Sed Lex

    (@sedlex)

    Have you tested ?

    Thread Starter wallyO

    (@wallyo)

    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.

    Plugin Author Sed Lex

    (@sedlex)

    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).";";
    Thread Starter wallyO

    (@wallyo)

    [ 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', '', 'https://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);
    Plugin Author Sed Lex

    (@sedlex)

    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;
    Plugin Author Sed Lex

    (@sedlex)

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

    Plugin Author Sed Lex

    (@sedlex)

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

    Thread Starter wallyO

    (@wallyo)

    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.

    Plugin Author Sed Lex

    (@sedlex)

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

    Thread Starter wallyO

    (@wallyo)

    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.

    Thread Starter wallyO

    (@wallyo)

    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.

    Plugin Author Sed Lex

    (@sedlex)

    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 ?

Viewing 15 replies - 1 through 15 (of 17 total)
  • The topic ‘[Plugin: Backup Scheduler] Feature Request – DROP TABLE IF EXISTS’ is closed to new replies.