Problem with the options table during update from 1.5.9 to 1.6.0

imagepet

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:

-- 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;

End of SQL-Statements

acrylian

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.

imagepet

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

imagepet

Solution #3 runs fine on my test system, the update to 1.6.0 was smooth.
i suggest setting the index manually to (name(95), ownerid, theme(95)) 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.

acrylian

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.

imagepet

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:

-- 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;

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.

acrylian

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.