michaeljoy
commented
4 years ago The only way to get this to move forward was to remove duplicate primary keys already defined that you're trying to add constraints on as well, and invalid, or incorrect duplicate indexes w/ _unq_idx. Foreign key constraints were invalid in several tables and restricted dropping the invalid or incorrectly named indexes that are duplicate of the default primary key index.
Resulting DDL (please validate that this is correct, I believe there's perhaps some cascading delete / update FK's missing here as they had to be dropped).
create or replace table craft_blitz_caches
(
id int auto_increment
primary key,
siteId int not null,
uri varchar(255) not null,
expiryDate datetime null,
constraint craft_blitz_caches_siteId_uri_unq_idx
unique (siteId, uri),
constraint craft_blitz_caches_siteId_fk
foreign key (siteId) references craft_sites (id)
on update cascade on delete cascade
)
charset=utf8;
create or replace index craft_blitz_caches_expiryDate_idx
on craft_blitz_caches (expiryDate);
create or replace table craft_blitz_cachetags
(
cacheId int not null,
tag varchar(255) not null,
constraint craft_blitz_cachetags_cacheId_fk
foreign key (cacheId) references craft_blitz_caches (id)
on update cascade on delete cascade
)
charset=utf8;
create or replace index craft_blitz_cachetags_tag_idx
on craft_blitz_cachetags (tag);
create or replace table craft_blitz_driverdata
(
id int auto_increment
primary key,
driver varchar(255) not null,
data text null,
dateCreated datetime not null,
dateUpdated datetime not null,
uid char(36) default '0' not null
)
charset=utf8;
create or replace table craft_blitz_elementcaches
(
cacheId int not null,
elementId int not null,
constraint craft_blitz_elementcaches_cacheId_fk
foreign key (cacheId) references craft_blitz_caches (id)
on update cascade on delete cascade,
constraint craft_blitz_elementcaches_elementId_fk
foreign key (elementId) references craft_elements (id)
on update cascade on delete cascade
)
charset=utf8;
create or replace table craft_blitz_elementexpirydates
(
elementId int not null,
expiryDate datetime null
)
charset=utf8;
create or replace index craft_blitz_elementexpirydates_expiryDate_idx
on craft_blitz_elementexpirydates (expiryDate);
create or replace table craft_blitz_elementqueries
(
id int auto_increment
primary key,
`index` bigint not null,
type varchar(255) not null,
params text null,
constraint craft_blitz_elementqueries_index_unq_idx
unique (`index`)
)
charset=utf8;
create or replace index craft_blitz_elementqueries_type_idx
on craft_blitz_elementqueries (type);
create or replace table craft_blitz_elementquerycaches
(
cacheId int not null,
queryId int not null
)
charset=utf8;
create or replace index craft_blitz_elementquerycaches_queryId_fk
on craft_blitz_elementquerycaches (queryId);
create or replace table craft_blitz_elementquerysources
(
sourceId int null,
queryId int not null,
constraint craft_blitz_elementquerysources_sourceId_queryId_unq_idx
unique (sourceId, queryId),
constraint craft_blitz_elementquerysources_queryId_fk
foreign key (queryId) references craft_blitz_elementqueries (id)
on update cascade on delete cascade
)
charset=utf8;
create or replace table craft_blitz_recommendations
(
id int auto_increment
primary key,
`key` varchar(255) not null,
template varchar(255) null,
message text null,
info text null,
dateCreated datetime not null,
dateUpdated datetime not null,
uid char(36) default '0' not null,
constraint craft_blitz_recommendations_key_template_unq_idx
unique (`key`, template)
)
charset=utf8;
bencroker
mijewe
cavellblood
Describe the bug
Fatal schema migration failure updating Blitz plugin from craft cms UI.
MariaDB 10.4.8
Doesn't seem to be permissions related, tried running the alter table statement with a super user and got the same result.
Incorrect index name 'craft_blitz_elementexpirydates_elementId_unq_idx'
Current table DDL...
Error output from Craft...
To reproduce
Steps to reproduce the behaviour:
Expected behaviour
Updating to latest Blitz plugin version migrates successfully.
Screenshots
If applicable, add screenshots to help explain your problem.
Versions