Hi all,

My update from 1.5.9 to 1.6.0 fails because the index unique_options cannot be created.
My investigations have shown that I have multiple entries in this table that do not differ in the first 95 digits of the name column, but do differ behind it. All these entries start with “Page-Hitcounter-”. There are no multiple entries on the first 95 characters in the theme column.
The index unique_options is defined as (name, ownerid, theme) until 1.5.9, in 1.6.0 it is changed to (name(95), ownerid, theme(95)) (found in setup/index.php line 2234).

Imho, the following solutions are possible:
- Deleting the options table before the update, I assume it will be created again during the setup process and filled with default values as I have read in older forum dicussions. The adjustments to the theme options must be made again afterwards. How do that effects the hitcounter plugin? Are there other unwanted side effects?
- Delete all rows that start with “Page-Hitcounter-” before the update. I don't know what effect this has on the hitcounter plugin.
- Delete all multiple entries except the one with the lowest (or highest) id before the update. I have written a SQL statement for this, it is attached below.

What do you think is the safest or best solution? Solution 3: keep the lowest or highest id?
Any help or hint is highly appreciated.

Kind regards
imagepet


SQL-Statements:
<pre>[code]
-- delete all multiple entries on the options table (on the first 95 characters in name) except that one with the highest (lowest) id
-- you have to replace pref in the table names with your table prefix (3 times)
with
mtp as -- all multiple names on a single row including min and max id
(
select
substr(name, 1, 95) as mtp_namek
, min(id) as mtp_min_id
, max(id) as mtp_max_id
, count(*) as mtp_counter
from pref_options
where theme = "" -- when theme is not empty, multiples are ok: one entry per theme
group by
mtp_namek
having mtp_counter > 1
),
amp as -- all multiples with their ids
(
select
substr(name, 1, 95) as amp_namek
, id as amp_id
from pref_options
inner join mtp
on substr(zp_options.name, 1, 95) = mtp_namek
),
did as -- ids to delete
(
select
amp_id
from mtp
inner join amp
on mtp_namek = amp_namek
and mtp_min_id <> amp_id -- do not delete the lowest id
-- and mtp_max_id <> amp_id -- do not delete the highest id
)
delete
from pref_options
where id in (select amp_id from did)
;

commit;
[/code]</pre>

End of SQL-Statements

Those duplicates should not have been there in the first place naturally. I think all three ways you suggested would work but solution #3 cleaning up to keep the latest entries sounds like the best way. Since we increment ids automatically in all tables as usual, the highest should be the latest entry.

Otherwise necessary options will be recreated if missing via setup. The hitcounter will just be resetted and recreated as well.

Thanks for your answer, acrylian I'll go with solution #3 on my test system and will report my experiences here.

Solution #3 runs fine on my test system, the update to 1.6.0 was smooth.
i suggest setting the index manually to (name[b](95)[/b], ownerid, theme[b](95)[/b]) after cleaning up the table and before you run the setup to 1.6.0 to be sure to not get any new duplicates. Can be easily done in phpMyAdmin for example.

Thanks for the follow up. Glad it worked out for you. Although setup would set those indexes as well, it is probably indeed good idea to do manually first in your case.

As I mentioned above, the cleanup went well on my testsystem. But it failed on the db with my provider =:-o.
Reason: on my test system runs a MySQL, my provider runs a MariaDB, wich do not support deletion with cte (common table expression) as I posted above. So some new SQL-statements had to be written and I'm posting them here if someone else runs into the same problems as i did.


SQL-Statements:
<pre>[code]
-- you have to replace pref in the table names with your table prefix (about 20 times)

-- create a first temporary table
drop table if exists pref_dpl;

create table pref_dpl
(
dpl_namek varchar(95) not null
, dpl_min_id int unsigned not null
, dpl_max_id int unsigned not null
, dpl_counter int unsigned not null
)
engine=innodb default charset=utf8mb4 collate=utf8mb4_unicode_ci
;

-- then fill the table with all multiple names on a single row including min and max id
insert into pref_dpl
select
substr(name, 1, 95) as dpl_namek
, min(id) as dpl_min_id
, max(id) as dpl_max_id
, count(*) as dpl_counter
from pref_options
where theme = "" -- when theme is not empty, duplicates are ok: one entry per theme
group by
dpl_namek
having dpl_counter > 1
;

commit;

-- create a second temporary table
drop table if exists pref_ald;

create table pref_ald
(
ald_namek varchar(95) not null
, ald_id int unsigned not null
)
engine=innodb default charset=utf8mb4 collate=utf8mb4_unicode_ci
;

-- then fill the table with all multiple entries with their ids
insert into pref_ald
select
substr(name, 1, 95) as ald_namek
, id as ald_id
from pref_options
inner join pref_dpl
on substr(name, 1, 95) = dpl_namek
;

commit;

-- create a third temporary table
drop table if exists pref_did;

create table pref_did
(
did_id int unsigned not null
)
engine=innodb default charset=utf8mb4 collate=utf8mb4_unicode_ci
;

-- then fill the table with all ids to delete
insert into pref_did
select
ald_id as did_id
from pref_dpl
inner join pref_ald
on dpl_namek = ald_namek
-- and dpl_min_id <> ald_id -- do not delete the lowest id
and dpl_max_id <> ald_id -- do not delete the highest id
;

commit;

-- finally, delete the duplicates on the pref_options table
delete
from pref_options
where id in (select did_id from pref_did)
;

commit;

-- now, we are ready to alter the table to adjust the index in advance of the update to 1.6.0
alter table pref_options
drop index unique_option
, add unique unique_option (`name`(95), `ownerid`, `theme`(95)) using btree
;

-- housekeeping
drop table if exists pref_dpl;
drop table if exists pref_ald;
drop table if exists pref_did;[/code]</pre>
End of SQL-Statements

They run on my MySQL with my provider without any problem, now everything is fine, I am on 1.6.2 with 1.6.5 as target.

Thanks, that is surely helpful to others.

I have got a far memory that exporting a database backup using our own tool and then re-importing it also may fix this if the tables have been setup with the necessary unique fields meanwhile (e.g. have been emptied which the import also does) But I haven't tried this myself.