millions of records in the options table - duplicate options continuously created

avicarmi

version 1.5.8

noticed some weirdness with the admin and settings

looked at the database, millions of entries, some entries have only a few dups, some entries thousands of dups.

some dupes all have the same values, for some only the first (lowest id) value is different, and all the dups share the same value. and some dups have alternating values (for example 100 the next will be 200 then 100 again etc), and some which seem to be a counter seem to have ever-increasing values.

yet some settings are not duplicated all, but some have very low id, yet some have a very large id (as if the lower id get delete when a new one is created)

some settings have low id yet all the dups id's are in the millions

some dupes are created in pairs, where one has a null creator, while the other has a valid creator.

bottom line, no consistent pattern...

but the worse thing is that these dupes are continuously created, even in the middle of the night when no one should be using the site (very low traffic site)...

currently only nine are continuously created... admin_lastvisit% recapcha_% magic_max_%

SELECT * FROM zp_options ORDER BY id DESC

id ownerid name value theme creator
7974824 0 admin_lastvisit 1 zp-core/lib-auth.php
7974823 0 admin_lastvisit_timeframe 600 zp-core/lib-auth.php
7974822 0 reCaptcha_size normal zp-core/zp-extensions/reCaptcha.php
7974821 0 reCaptcha_type image zp-core/zp-extensions/reCaptcha.php
7974820 0 reCaptcha_theme light zp-core/zp-extensions/reCaptcha.php
7974819 0 magick_max_width 0 NULL
7974818 0 magick_max_height 0 NULL
7974817 0 magick_max_width 0 zp-core/lib-Imagick.php
7974816 0 magick_max_height 0 zp-core/lib-Imagick.php
7974815 0 admin_lastvisit 1 zp-core/lib-auth.php
7974814 0 admin_lastvisit_timeframe 600 zp-core/lib-auth.php
7974813 0 reCaptcha_size normal zp-core/zp-extensions/reCaptcha.php
7974812 0 reCaptcha_type image zp-core/zp-extensions/reCaptcha.php
7974811 0 reCaptcha_theme light zp-core/zp-extensions/reCaptcha.php
7974810 0 magick_max_width 0 NULL
7974809 0 magick_max_height 0 NULL
7974808 0 magick_max_width 0 zp-core/lib-Imagick.php
7974807 0 magick_max_height 0 zp-core/lib-Imagick.php
7974806 0 license_accepted 1.5.9a NULL
7974805 0 backup_compression 9 NULL
7974804 0 admin_lastvisit 1 zp-core/lib-auth.php
7974803 0 admin_lastvisit_timeframe 600 zp-core/lib-auth.php
7974802 0 reCaptcha_size normal zp-core/zp-extensions/reCaptcha.php
7974801 0 reCaptcha_type image zp-core/zp-extensions/reCaptcha.php
7974800 0 reCaptcha_theme light zp-core/zp-extensions/reCaptcha.php

avicarmi

oh, and looking at the database backups, seems that it started fairly recently, till June 6th backup size was constant at 1.24MB, then started to get bigger 1.4 1.7 1.9 2.8 till 56.4 on June 21... and no backups from 21 till today... where after I deleted all the dup it went back to 1.24MB and started to climb again...

acrylian

With "dupes" you mean duplicates? Zenphoto indeed keeps a lot options since you may want to reuse them if you re-enable for example a plugin again.

The larger the id the newer the entry. No creator (null) means usually this is a general admin option. We had changed some calls if I recall right to include the ower sometimes where it wasn't before. But that's been a while ago. That would apply to these example:

 7974819 0 magick_max_width 0 NULL
 7974818 0 magick_max_height 0 NULL
 7974817 0 magick_max_width 0 zp-core/lib-Imagick.php 
 7974816 0 magick_max_height 0 zp-core/lib-Imagick.php 

But you should have this set once and not several tiems.
Also options already set should not be recreated or reset (unless on ocasions you save options naturally). admin_lastvisit would be set often since it stores, well, the admin_lastvisit which naturally changes often but duplcates like you listed should not happen.

I would suspect that something might not right with your database if you have a lot of duplcates which results in the millions of entries in the options table. A usual options table of an install several years old may have perhaps round 2000 entries.

Try to use the repair tools you can access cia phpmyadmin. But sure to make a database backup before doing anything. Those tools of course will not remove dupicates.

acrylian

If it started recently perhaps something changed on your host. Or the database indeed went corrupt. It's technically a special text file and sometimes that happens (I gladly didn't encounter this).

Or something on the server changed if you didn't do anything on that date.

avicarmi

and the reason there is no backup was that the backup count was duplicated, but the dup had the default values of keep 5 and interval to 7 days (used to be 61 backups and 1 day interval), and apparently, whenever there is a dup option in the table, the last one is retrieved?

acrylian

apparently, whenever there is a dup option in the table, the last one is retrieved?

Rather the first found since there should only be one normally.

On the missing backups it may be a time out even since the table is surely quite large with millions of entries.

I just checked my local old test install and there is not even a duplocate with owner and without owner (which should be that way as the name of the option is the measure actually).

Check if thte unique keys are set correctly on teh table:

Id shoul be primary, and name, owerid, theme should be unique_option. That actually prevents such duplicates.

SubJunk

This happens to me from time to time too. I have a file saved on my computer called "Run this if options table is growing.txt". The query to run is:

ALTER IGNORE TABLE $tbl_options ADD UNIQUE `unique_option` (`name`, `ownerid`, `theme`)

I have no idea why that unique constraint slips off the table but it does happen and is likely a bug with the SQL driver itself, not zenphoto. zenphoto could make a workaround though by not relying on that constraint, and instead updating records if they exist.
Anyway I hope that command helps.

acrylian

s likely a bug with the SQL driver itself, not zenphoto.
zenphoto could make a workaround though by not relying on that constraint, and instead updating records if they exist.

It clearly is a bug on the database part

Actually we use the ON DUPLICATE KEY UPDATE command but that only works properly if the table column that need are set up as unique as required.

To check this we need an extra mysql query which surely is not a good performance. We really need to be able to rely on the database working correctly. If this happens constantly and that setting ges lost, better contact your host about that. This should not happen.

sbillard

Please note that if there are duplicates in the database table, setting the 'unique_option" key will fail. You should first resolve all the duplicates so only the correct entrys remain.