after a setup, I have this message
`Warning: the``options` table appears not to have a proper unique_options key. There are probably duplicate options in the table. There should be a unique index key on name, ownerid, and theme.`
I have a look on my database and I am unable to found multiple unique_options values.
But I far I could see, there are curious options, like :
`Page-Hitcounter_%`
on my albums, my pages, my news, and sometimes, on websites that are not mine.
Is it normal to have these values in the options table?
Can I remove without asking too many questions?
Zenphoto
Comments
when I have a look on a 1.4.4 database (options table) with phpMySQL, I have ability to sort values by unique_option key.
when I have a look on a 1.4.5 database (options table) this unique_option key doesn't exist.
is there an issue on the database modeling ?
with on a old 1.4.5 (migrate from older release of zenphoto), I haven't `unique_option` key in database.
Is this key added during migration process ?
Is it not always added and why ?
In fact, on my old 1.4.5, I have a lot of multiple values, maybe not checked because there is no `unique_option` key in database.
1- what are the values used by hitcounter plugin?
those in database options table?
those who are in hitcounter column of each table (album, image, page, news)?
in the 2nd case, can you think to make purge during migration process? should I do it myself?
2- in my dev site, there is no unique index key set with ownerid, name and theme.
when did this index key has been added in a previous release?
why did this index key hasn't been added during a migration process?
is there a tool to help me to solve multiple values and set this unique index key, or should I do it myself?
To 2. I have no idea and have to leave it to sbillard to answer. I don't think we have a tool for that as that actually should not happen.I did not see this on my test install which also was an upgraded one.
But the SQL to add this index will fail if there are any duplicates already in the database. Unfortunately there are all sorts of valid causes for SQL errors during setup, so they are typically not noted. That is why the above warning was added.
The only way I know of finding the duplicates is to sort the table by what would have been the index criteria and look. You will have to remove them manually before setup can make the proper index key.
I have 131 lines Page-Hitcounter_% in my options table, and they look like hitcounter value rather than option value
examples:
name: Page-Hitcounter-archive / value: 761
name: Page-Hitcounter-search / value: 558
name: Page-Hitcounter-PAGES:wp-contentthemesgalleryscriptstimthumbphp / value: 25
if hitcounter have to store values for specific objects (different from album, images, news and pages), it should be store them in plugin_storage table rather than options table
It seems to be an issue during setup:
- I deleted options table and I did a setup : options table is created, with default options, and with unique_option index key.
=> I checked database, and I had no multiple values
- Then, I deleted unique_option index key (to simulate a migration from older release) and did a setup again
=> unique_option index key is not created and I have multiple values in my options table (example: GD_FreeType_Path, 104 values) and in fact, default options values are stored twice at least.
because I did not have multiple values, it is not normal that unique_option index key was not recreated during setup and the default options were inserted twice.
maybe, you missed my previous message : It seems to be an issue on unique_option update and migration process
I suggest you add one more step to your testing. Run the update with the setup ?debug parameter. This will log all the sql queries and you will be able to see the failure.
The other way to find the duplicates is to try to create an index on `name`, `ownerid`, and `theme`. At least with phpMyAdmin version 4 you will get a failure noting which record is the duplicate. I do not know if it lists more than one, because I did create only one duplicate for testing.
I tried to do this (with debug mode) :
- I did a fresh install : options table is created, with default options, and with unique_option index key : OK
- I deleted options table and I did a setup again : options table is created, with default options, and with unique_option index key : OK
- I added some plugin and change some options
=> I checked database, and I had no multiple values in options table
- Then, I deleted unique_option index key (to simulate a migration from older release) and did a setup again
=> unique_option index key is not created because I have multiple values in my options table.
what I can see in my setup logs
`
Query ALTER TABLE 'zp_options' DROP INDEX 'unique_option' Failed. Error: Can't DROP 'unique_option'; check that column/key exists
Query ALTER TABLE 'zp_options' ADD UNIQUE 'unique_option' ('name', 'ownerid', 'theme') Failed. Error: Duplicate entry 'GD_FreeType_Path-0-' for key 'unique_option'
`
because I did not have multiple values before delete unique_option index key and setup, it is not normal that unique_option index key was not recreated during setup and the default options were inserted twice (not only GD_FreeType_Path-0- option).
If you do not have the index you MUST remove the duplicates and without doing ANYTHING else run setup or re-create the index. If you run anything from the zenphoto core with no unique key you will likely get duplicates re-inserted into the database.
The "last" entry of the duplicates will be the one that is saved if you use this process.