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.