UpdraftPlus remove original table and not restore backup

  • I have a table that are described in backup file like below:

    # Table structure of table wp_sm_aa_article_views

    CREATE TABLE wp_sm_aa_article_views (
    id bigint unsigned NOT NULL AUTO_INCREMENT,
    view_date date NOT NULL DEFAULT (utc_date()),
    partner_id bigint unsigned DEFAULT NULL,
    channel_id bigint unsigned NOT NULL,
    style_id bigint unsigned NOT NULL,
    landing_query bigint unsigned DEFAULT NULL,
    custom_id bigint unsigned DEFAULT NULL,
    search_query bigint unsigned NOT NULL,
    terms varchar(255) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
    view_count bigint unsigned DEFAULT '1',
    unique_hash varchar(40) COLLATE utf8mb4_unicode_520_ci GENERATED ALWAYS AS (sha(concat(view_date,partner_id,channel_id,style_id,coalesce(landing_query,0),coalesce(custom_id,0),coalesce(search_query,0),coalesce(terms,0)))) STORED,
    PRIMARY KEY (id),
    UNIQUE KEY unique_view_segment (unique_hash)
    ) ENGINE=InnoDB AUTO_INCREMENT=6274 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci ;

    But if I try to restore backup with this table, I’m getting an error:

    0014.446 () An error (25) occurred: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MARY KEY (id),  UNIQUE KEY unique_view_segment (unique_hash)) ENGINE=InnoD' at line 1 - SQL query was (type=2): CREATE TABLE pg_sm_aa_article_views (  id bigint unsigned NOT NULL AUTO_INCREMENT,  view_date date NOT NULL DEFAULT (utc_date()),  partner_id bigint unsigned DEFAULT NULL,  channel_id bigint unsigned NOT NULL,  style_id bigint unsigned NOT NULL,  landing_query bigint unsigned DEFAULT NULL,  custom_id bigint unsigned DEFAULT NULL,  search_query bigint unsigned NOT NULL,  terms varchar(255) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,  view_count bigint unsigned DEFAULT '1',  unique_hash varchar(40) COLLATE utf8mb4_unicode_520_ci MARY KEY (id),  UNIQUE KEY unique_view_segment (unique_hash)) ENGINE=InnoDB AUTO_INCREMENT=6274 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci ;

    The problem SQL query with is used to restore table:

    create table pg_sm_aa_article_views
    (
        id            bigint unsigned not null auto_increment,
        view_date     date            not null                    default (utc_date()),
        partner_id    bigint unsigned                             default null,
        channel_id    bigint unsigned not null,
        style_id      bigint unsigned not null,
        landing_query bigint unsigned                             default null,
        custom_id     bigint unsigned                             default null,
        search_query  bigint unsigned not null,
        terms         varchar(255) collate utf8mb4_unicode_520_ci default null,
        view_count    bigint unsigned                             default '1',
        unique_hash   varchar(40) collate utf8mb4_unicode_520_ci MARY key (id), # <------ The piece are missed
        unique key unique_view_segment (unique_hash)
    ) engine = InnoDB
      auto_increment = 6274
      default charset = utf8mb4
      collate = utf8mb4_unicode_520_ci
Viewing 3 replies - 1 through 3 (of 3 total)
  • Plugin Support vupdraft

    (@vupdraft)

    Can you post your full restoration from your wp-content/updraft directory of your site using a tool such as pastebin.com

    Is the table created by a plugin, if so which is it?

    It looks like it might be a custom made table?

    Thread Starter Alex Kozack

    (@cawa-93)

    This is a custom table created like so:

    dbDelta(
        "CREATE TABLE {$table} (
            id bigint(9) UNSIGNED NOT NULL AUTO_INCREMENT,
            view_date date DEFAULT (UTC_DATE) NOT NULL,
            partner_id bigint(9) UNSIGNED DEFAULT NULL,
            channel_id bigint(9) UNSIGNED NOT NULL,
            style_id bigint(9) UNSIGNED NOT NULL,
            landing_query bigint(9) UNSIGNED DEFAULT NULL,
            custom_id bigint(9) UNSIGNED DEFAULT NULL,
            search_query bigint(9) UNSIGNED NOT NULL,
            terms varchar(255) DEFAULT NULL,
            view_count bigint(9) UNSIGNED DEFAULT 1,
            unique_hash VARCHAR(40) GENERATED ALWAYS AS (SHA1(CONCAT(view_date, partner_id, channel_id, style_id, coalesce(landing_query,0), coalesce(custom_id,0), coalesce(search_query,0), coalesce(terms,0)))) STORED,
            PRIMARY KEY  (id),
            UNIQUE INDEX unique_view_segment (unique_hash)
        ) $charsetCollate;",
    );
    Plugin Support vupdraft

    (@vupdraft)

    This SQL did not work for me. I tried through both php MyAdmin and a SQL validator and they both flagged issues.

    It’s likely that the issue is with your SQL syntax rather than the restore function of UpdraftPlus.

    I suspect if you removed this table, the backup and restore would work without issues.

    Can you export a copy of the table, drop it , take a backup of the site and then restore it and post the restoration log using a tool such as pastebin.com?

Viewing 3 replies - 1 through 3 (of 3 total)
  • You must be logged in to reply to this topic.

Tags