after a setup, I have this message
[b]Warning:[/b] the``options table appears not to have a proper [i]unique_options[/i] key. There are probably duplicate options in the table. There should be a unique index key on [i]name[/i], [i]ownerid[/i], and [i]theme[/i].`
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?
other question : is it a way to found duplicate options, maybe with a specific sql request ?
other question :
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 a fresh install of 1.4.5, I have unique_option key.
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.
in fact, there are 2 questions in my topic:
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?
1- Each item has a hitcount that is stored in their table, those for rss are in pluginstorage. Page-Hitcounter% is an option from the hitcounter plugin and correct.
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.
The unique options is (would be) added by setup. This was added in 1.2.7. (For your information, if you look at the code towards the end of setup/index.php you will find the updates to the database structure. All are grouped and labeled by the release in which they were added.)
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.
@acrylian:
Quote:Page-Hitcounter% is an option from the hitcounter plugin and correct.
what is the full list of options for this plugin ?
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
@sbillard
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.
Options are unique as well. but the % in "page_hitcounter-%" is actually a placeholder of a database query (hitcounter.php: 26). So there are meant to be several because this is the hitcount for static theme pages (non item pages). Probably plugin_storage would be better but I think the plugin handling is just older than the existence of that extra table.
@sbillard
maybe, you missed my previous message : It seems to be an issue on unique_option update and migration process
Not an issue with the update, a problem with the database. SQL will not make a unique set if there are duplicates in the database. If the unique set is not present then it is possible to create duplicate entries. There is only one solution--remove the duplicates.
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.
hi
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 [b]no multiple values[/b] 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 [b]not created[/b] because I have [b]multiple values[/b] 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).
As said. If you have the unique index you cannot get duplicate options. If you do not have that index you can and probably will get duplicate options. If the index is not there setup will try to created it. If there are duplicate options the creation will fail.
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.
I took a look at the backup-restore code. I believe that you can resolve this problem by backing up your database, dropping the database table, running setup to restore it, then running a restore. I have not verified this, though.
The "last" entry of the duplicates will be the one that is saved if you use this process.
A example SQL statement to delete all multiple entries except that one with the highest/lowest id is mentioned in my post Problem with the options table during update from 1.5.9 to 1.6.0. Acrylian recommends not to delete the highest id.