error message editing/adding page attributes

Permalink
I can't figure this out and neither can the designer so, if you have ideas, please help! I didn't get any real replies last time and I'd really like to avoid yanking my themes and/or site because of this. My site domain ishttp://www.iquomma.com. Thanks!

Message from designer:
It seems to be something wrong with your database and custom page attributes. If you try editing any of the attributes (meta description, title, etc) it throws that error [SEE BELOW], not just the Background Image one. Even on the pages that you dont have the Clearview theme applied the error occurs, so it doesnt seem to be something happening because of Clearview. I would recommend posting that error on the forums and mention that you get an error when trying to edit/add any custom attributes.

ERROR MESSAGE:

"Fatal error: Uncaught exception 'ADODB_Exception' with message 'mysql error: [1062: Duplicate entry '72-8-1-214' for key ‘PRIMARY’] in EXECUTE (“UPDATE CollectionAttributeValues SET avid=214 WHERE cID=72 and cvID=8 and akID=1”) in home7/iquommac/public_html/concrete/libraries/3rdparty/adodb/adodb-exceptions.inc.php:78 Stack trace:#0

/home7/iquommac/public_html/concrete/libraries/3rdparty/adodb/adodb.inc.php(1037): adodb_throw('mysql', 'EXECUTE', 1062, 'Duplicate entry...', 'UPDATE Collecti...', false, Object(ADODB_mysql))
#1 /home7/iquommac/public_html/concrete/libraries/3rdparty/adodb/adodb.inc.php(1012): ADOConnection->Execute('UPDATE Collecti...', false)
#2 /home7/iquommac/public_html/concrete/libraries/3rdparty/adodb/adodb-lib.inc.php(174): ADOConnection->Execute('UPDATE Collecti...')
#3 /home7/iquommac/public_html/concrete/libraries/3rdparty/adodb/adodb.inc.php(1658): _adodb_replace(Object(ADODB_mysql), 'CollectionAttri...', Array, Array, false, false)
#4 /home7/iquommac/public_html/concrete/libraries/database.php( in /home7/iquommac/public_html/concrete/libraries/3rdparty/adodb/adodb-exceptions.inc.php on line 78"

View Replies:
jordanlev replied on at Permalink Reply
jordanlev
I'm not sure what the problem is, but if you're using a paid theme (which I believe the ClearView theme is), you get tech support -- go to the theme's marketplace page and click the "Support" link in the sidebar.
alligatorlegs replied on at Permalink Reply
As the designer says, this is a database problem not related to the theme. Anyone else have any ideas? Thanks much!
jordanlev replied on at Permalink Reply
jordanlev
I just ran into this problem when upgrading a site to 5.4.2. I have no clue why it happened, but here's how to solve it:

1) BACK UP YOUR DATABASE! Go to dashboard -> System & Maintenance -> Backup & Restore, click the "Run Backup" button, then click the "Download" button next to the new backup that was just created (double-check the "Date" if you have more than one backup in the list because new ones are not always added to the top or bottom as you'd expect them to be). If this is a live site, you'll probably want to delete the backup from the server after you've downloaded it to your own computer, as there are security risks involved with leaving database backup files on the server.

2) Make sure you backed up your database in step 1. Seriously, don't continue until you've done that otherwise you could permanently mess up your site without any way of restoring it!

3) Look at the error message you got and pick out the numbers in it, for example in the original question above it says "...Duplicate entry '72-8-1-214' for key..." -- this means the 4 numbers you want are 72, 8, 1, and 214. If you don't see 4 numbers like this in your error message, then unfortunately this solution won't work for you.

4) Log into your site's phpMyAdmin and navigate to your website database. Then click the "SQL" tab. Now paste the following into the query textbox:
SELECT * FROM CollectionAttributeValues WHERE cID=72 AND cvID=8 AND akID=1 AND avID=214

(make sure you replace the 4 numbers with your specific numbers from the error message as described in step 3)


5) Click the "Go" button to run that query. Make sure you get 1 and only 1 result -- it should say something like "Showing rows 0-0 (1 total, Query took 0.0004 sec)" at the top of the screen, and a little further down should have one result row with the 4 numbers from the query in it.

If this query didn't return any results then double-check the numbers in your query to make sure they match your error message.

If this query returns more than 1 result, STOP NOW AND DO NOT CONTINUE! This means you have put the query in incorrectly or incompletely and continuing with these steps will cause unrecoverable damage to your site!!! Just start over again from step 1 and be extra careful about the query you typed in.

6) Okay, if you've verified that 1 and only 1 result was returned in step 5, that means it's relatively safe to continue. In the results of the query from step 5, you will see a red "X" icon to the left of the 4 numbers. Click this red "X", and you should be prompted with a confirmation asking if you're sure you want to delete the record. Click the "OK" button.

7) That should be it -- go back to your site and try to edit the page's attributes again. Should work now.
maartenfb replied on at Permalink Reply 2 Attachments
maartenfb
How is this possible ????

SELECT * FROM CollectionAttributeValues WHERE cID=142 AND cvID=131 AND akID=2

142 131 2 67213
142 131 2 72006
142 131 2 72008
142 131 2 72011
142 131 2 72714
142 131 2 72716
142 131 2 72719
142 131 2 73431
142 131 2 73433
142 131 2 73436

UPDATE CollectionAttributeValues SET avID=74070 WHERE cID=142 and cvID=131 and akID=2

Error : Duplicate entry '142-131-2-74070' for key 1
jordanlev replied on at Permalink Reply
jordanlev
I believe that table has a compound primary key (or a compound unique index) on all four fields. Your UPDATE statement is attempting to set the same svID value on multiple records, which is not allowed if the table has been defined to only have unique sets of values. You will need to modify your UPDATE statement to also specify the avID value you want to update in the WHERE clause.
maartenfb replied on at Permalink Reply
maartenfb
Thanks for replying.
That update statement indeed doesn't make any sense at all.
I first thought my issue was related to this thread but
I suspect it's being caused by a plugin I installed today.
Restored a backup and everything 's back to normal.

Will post answer/fix from developer once he knows more.