UpdraftPlus remove original table and not restore backup

Resolved

(@cawa-93)

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)

1 year, 6 months ago

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)

1 year, 6 months ago

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)

1 year, 6 months ago

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)

The topic ‘UpdraftPlus remove original table and not restore backup’ is closed to new replies.

Tags