Error Upgrading to 5.4

Permalink 9 users found helpful
trying to upgrade from 5.3.3.1 to 5.4 today...

did as read, downloaded 5.4 & uploaded the concrete directory over the existing one, went to the Mysite.com/index.php/tools/required/upgrade and upgraded.

it said that everything was successful, but now i can't edit any of my pages

every time i try to edit a content block i get the following error

Fatal error: Uncaught exception 'ADODB_Exception' with message 'mysql error: [1062: Duplicate entry '12-1' for key

'PRIMARY'] in EXECUTE("insert into CollectionVersionBlockStyles (cID, cvID, bID, arHandle, csrID) values ('1', '86', '12',

'Main', 0)") ' in /home4/fltconfe/public_html/trails/concrete/libraries/3rdparty/adodb/adodb-exceptions.inc.php:78 Stack

trace: #0 /home4/fltconfe/public_html/trails/concrete/libraries/3rdparty/adodb/adodb.inc.php(1037): adodb_throw('mysql',

'EXECUTE', 1062, 'Duplicate entry...', 'insert into Col...', false, Object(ADODB_mysql)) #1......

PLEASE HELP!
How do i fix this?

View Replies: View Best Answer
chunksmurray replied on at Permalink Reply
chunksmurray
I had a similar issue with my upgrade on a dev site. I had perviously applied styles to blocks using the 'Design' option, so block specific styles i guess. The upgrade to 5.4 seemed to strip the styles, and mess around with the entries in that table.

I ended up deleting all the rows from the table `collectionversionblockstyles` and i was able to work as usual.

I only had 4 blocks which had specific styles, so I admit this isn't great if you have a whole bunch.

Hopefully there is another way of sorting this?
chunksmurray replied on at Permalink Reply
chunksmurray
So you can ignore that last post! I'm trying to add a page now and I get that error as well.

Back to the drawing board.

*EDIT*

Seems that renaming the table `collectionversionblockstyles`to `_collectionversionblockstyles` and then running the upgrade script again works. I have been able to add and edit pages without the error.
stromberg replied on at Permalink Best Answer Reply
stromberg
Hi,

I had the same error with the exact same table after upgrading to 5.4.

I dumped the sql structure of my upgraded table ´collectionversionblockstyles´ and of a freshly installed test instance and compared them. Result: The new install had 4 primary keys in collectionversionblockstyles, the upgraded only 2.

Upgraded table ´collectionversionblockstyles´:
CREATE TABLE IF NOT EXISTS `collectionversionblockstyles` (
  `bID` int(10) unsigned NOT NULL DEFAULT '1',
  `cID` int(10) unsigned NOT NULL DEFAULT '1',
  `cvID` int(10) unsigned NOT NULL DEFAULT '0',
  `arHandle` varchar(255) NOT NULL,
  `csrID` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`bID`,`cID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


´collectionversionblockstyles´ from a clean install:
CREATE TABLE IF NOT EXISTS `collectionversionblockstyles` (
  `cID` int(10) unsigned NOT NULL DEFAULT '0',
  `cvID` int(10) unsigned NOT NULL DEFAULT '0',
  `bID` int(10) unsigned NOT NULL DEFAULT '0',
  `arHandle` varchar(255) NOT NULL,
  `csrID` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`cID`,`cvID`,`bID`,`arHandle`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

You can change the structure of the upgraded table without dropping all data by executing the following SQL statement in your db:

ALTER TABLE `collectionversionblockstyles`
  DROP PRIMARY KEY,
   ADD PRIMARY KEY(
     `cID`,
     `cvID`,
     `bID`,
     `arHandle`);

I also changed the "DEFAULT '1'" part of `cID` and `bID` to '0' by hand via phpMyAdmin.

----------------<UPDATE 1>
jcd posted the appropriate SQL instructions for doing this (thanks!):
ALTER TABLE `collectionversionblockstyles`
MODIFY COLUMN `bID` INTEGER UNSIGNED NOT NULL DEFAULT 0,
MODIFY COLUMN `cID` INTEGER UNSIGNED NOT NULL DEFAULT 0;

----------------</UPDATE 1>

There were two more differences between my upgraded database and the clean install, regarding the tables `pagepaths` and `collectionversions`. I synchronized them by executing the following SQL:

ALTER TABLE `pagepaths` ADD KEY `cPath` (`cPath`(128));
ALTER TABLE `collectionversions` ADD KEY `cvName` (`cvName`(128));


----------------<UPDATE 2>
If you're running your c5 install on a linux server, chances are that your table names are written in so-called 'CamelCase' style and not in all-lowercase. That means that the sql statements from above will probably not work and you should try this version which ddrace kindly provided:
ALTER TABLE `CollectionVersionBlockStyles`
  DROP PRIMARY KEY, 
    ADD PRIMARY KEY( `cID`, `cvID`, `bID`, `arHandle`);
ALTER TABLE `CollectionVersionBlockStyles` 
  MODIFY COLUMN `bID` INTEGER UNSIGNED NOT NULL DEFAULT 0, 
  MODIFY COLUMN `cID` INTEGER UNSIGNED NOT NULL DEFAULT 0;
ALTER TABLE `PagePaths` 
  ADD KEY `cPath` (`cPath`(128)); 
ALTER TABLE `CollectionVersions` 
  ADD KEY `cvName` (`cvName`(128));

----------------</UPDATE 2>

HTH!
uliII replied on at Permalink Reply
Thank you very much, that solves my problem!
chameleon replied on at Permalink Reply
Thanks - Seems to be working so far!
Fingers crossed :)
janith replied on at Permalink Reply
janith
I'm having this issue as well and this appears to be just the help I need---but I'm fairly illiterate and still unable to resolve this debilitating (and really annoying) error.

I've managed to make the manual changes via phpMyAdmin, but don't know where go to "execute the SQL statements"? Any guidance you could provide would be most appreciated...
stromberg replied on at Permalink Reply 1 Attachment
stromberg
Janith, try this, please:

0) Open phpMyAdmin
1) select your database
2) in the right window pane, choose the tab "SQL" (see attached screenshot)
3) paste the SQL statements from my previous post (without the "<?php"-tags, of course :-) in the textarea field
4) click the OK button (see attached screenshot)

Cheers!
mmgalitz replied on at Permalink Reply
I upgraded to 5.4.0.5 from 5.4.0.3 and now get Page not found errors while trying to edit content blocks. Only if I try to use HTML, link or anchor. Any ideas on how to fix this.

I upgraded two sites.

The second site gave me a 500 error and a fixed message and then everything worked fine on the edit block.

Please help.
[email protected]
ebartan replied on at Permalink Reply
ebartan
thanks solvin my problem i didnt test my other concrete5 sites but i think so this answer solving all problems.
teddadore replied on at Permalink Reply
teddadore
Stormberg,

I've compared this error and your solution with what I am getting and they are nearly identical. Your upgraded table structure is also pretty much the same as mine.

I'm curious though if I also have to synchronize 'pagepaths' and 'collectionversion'. Everything else about your table structure is the same as mine, but I'm not sure what these mean. Thanks in advance!

Here is my complete error just in case:

Fatal error: Uncaught exception 'ADODB_Exception' with message 'mysql error: [1062: Duplicate entry '36-1039' for key 'PRIMARY'] in EXECUTE("insert into CollectionVersionBlockStyles (cID, cvID, bID, arHandle, csrID) values ('1039', '3', '36', 'Header Nav', 0)") ' in /home1/smackmel/public_html/concrete/libraries/3rdparty/adodb/adodb-exceptions.inc.php:78 Stack trace: #0 /home1/smackmel/public_html/concrete/libraries/3rdparty/adodb/adodb.inc.php(1037): adodb_throw('mysql', 'EXECUTE', 1062, 'Duplicate entry...', 'insert into Col...', false, Object(ADODB_mysql)) #1 /home1/smackmel/public_html/concrete/libraries/3rdparty/adodb/adodb.inc.php(993): ADOConnection->_Execute('insert into Col...') #2 /home1/smackmel/public_html/concrete/libraries/database.php(73): ADOConnection->Execute('insert into Col...', Array) #3 [internal function]: Database->__call(Array, Array) #4 /home1/smackmel/public_html/concrete/models/block.php(393): Database->Execute('Execute', Array) #5 /home1/smackmel/public_html/concrete/models/collection.php(82): in /home1/smackmel/public_html/concrete/libraries/3rdparty/adodb/adodb-exceptions.inc.php on line 78
abraamz replied on at Permalink Reply
abraamz
thank you for the solution
WebStudioEast replied on at Permalink Reply
Thanks Stromberg you saved my ass!
jcd replied on at Permalink Reply
jcd
stromberg:
"I also changed the "DEFAULT '1'" part of `cID` and `bID` to '0' by hand via phpMyAdmin."

If somebody still need it, sql for this:
ALTER TABLE `CollectionVersionBlockStyles`
MODIFY COLUMN `bID` INTEGER UNSIGNED NOT NULL DEFAULT 0,
MODIFY COLUMN `cID` INTEGER UNSIGNED NOT NULL DEFAULT 0;
caybar replied on at Permalink Reply
Must give credit when it's due. This is an important time-saving post. Thanks for your contribution. It saved us having to spend hours or days trying to figure out the source of the problem.
Lazza replied on at Permalink Reply
Lazza
Thank you very much! Your suggestions are great!
dcaryll replied on at Permalink Reply
dcaryll
I tried running these in phpmyadmin and have the following error message now:

<code> mysql error: [1054: Unknown column 'csrID' in 'field list'] in EXECUTE("select csrID from CollectionVersionBlockStyles where cID = '1' and cvID = '120' and arHandle = 'Header Nav' and bID = '1' LIMIT 1") </code>

Any help would be extremely appreciated. My site is down and I'm not sure what to do.

Thank you guys,
Dan
stromberg replied on at Permalink Reply
stromberg
That sounds as if your CollectionVersionBlockStyles table doesn't have a column "csrID" - which it should have. When does this error message occur?

What exactly did you do? Upgrade from 5.3 to 5.4.x, encountered the error from the start of this thread and then tried executing the sql statements that I posted? Please provide as much information as you can since it can help narrowing down the search for the reason of your problem.

If you can, please export your database (structure only, no data!!) via phpMyAdmin (choose formatting option: SQL) and attach it to your next posting, maybe I can find something when I compare it to my current database structure (I'm running 5.4.1.1).

Cheers,
stromberg
dcaryll replied on at Permalink Reply
dcaryll
Hi Stromberg,
I really appreciate you trying to help me out. I was very worried about my client's site being down so I restored it to 5.3.3.1. Do you think the best way to update the site is to update to 5.0, then to 5.4?
stromberg replied on at Permalink Reply
stromberg
Always good to have a backup :-)

What do you mean with first 5.0, then 5.4? That surely is a typo, right? :)

I would do the following: put a 1:1 copy of your client's website on your development platform and try to do the upgrade there (maybe 5.3.3.1-> 5.4.0-> 5.4.1.1). Doing it on another server/computer gives you enough time to find and fix errors - make notes during the process so that you can repeat the procedure for the real website.

I would assume that you'd be able to directly upgrade from 5.3.3.1 to 5.4.1.1, but I haven't yet been forced to try that, so be careful.
bjalexander replied on at Permalink Reply
bjalexander
A year later and you get another big thank you. Your answer saved me today.
ddrace replied on at Permalink Reply
ddrace
Worked like a charm! Thanks a million. I did have to CamelCase my table names, however. So this is what I ended up doing:

ALTER TABLE `CollectionVersionBlockStyles` DROP PRIMARY KEY, ADD PRIMARY KEY( `cID`, `cvID`, `bID`, `arHandle`);
ALTER TABLE `CollectionVersionBlockStyles` MODIFY COLUMN `bID` INTEGER UNSIGNED NOT NULL DEFAULT 0, MODIFY COLUMN `cID` INTEGER UNSIGNED NOT NULL DEFAULT 0;
ALTER TABLE `PagePaths` ADD KEY `cPath` (`cPath`(128)); ALTER TABLE `CollectionVersions` ADD KEY `cvName` (`cvName`(128));


Perfection. Now if we could just get this included in the next update...
stromberg replied on at Permalink Reply
stromberg
You're welcome! Although I'm a bit puzzled that this thread is still 'alive', I'm glad that my bug hunt has been (and obvoiusly still is) helpful for others.

I included your code snippet in my original answer, thanks for supplying that!

About the update inclusion: I have no idea how to submit this or how to notify the core team of this issue. Anyone?

Regards,
stromberg
bentvision replied on at Permalink Reply
bentvision
HEY!!!! I just want to say THANKS SO MUCH!! I have been fighting this problem for over a month now. After I ran your three scripts, my problem is solved.

I just found this thread, so I might post it again to point into here.

This was SUCH a big help!!!
stromberg replied on at Permalink Reply
stromberg
Great to hear that [and thumbs up for persevering!;-)]! I guess I must have been pretty lucky to find a solution back then.

And yes, help spread the word if you see others having the same kind of trouble!

Thanks and regards,
stromberg
audun replied on at Permalink Reply
Thanks! This was great and saved me from sleepless nights and a lot of work! I got this problem after upgrading from 5.3.3.1 to 5.4.0 and further. The Linux query was the solution.
great replied on at Permalink Reply
Is there a update coming out soon to fix this problem or to I have to do it manually?
golfguat replied on at Permalink Reply
FYI ... I experienced this same issue upgrading one of my old sites from 5.3.n to 5.4.1.1.

I tried the above procedure, but ultimately was forced to DROP the table entirely (manually, using MyAdmin) and then running the appropriate SQL above.

Also note: MySQL is case sensitive. I adjusted the SQL to read CollectionVersionBlockStyles (as opposed to it being in lowercase).
DeWebmakers replied on at Permalink Reply
DeWebmakers
The post from stromberg was a great help!
Thanks!
jelthure replied on at Permalink Reply
jelthure
the solution above worked for me too. :)
swanienufan replied on at Permalink Reply
This also worked for me.

Wow, almost a year since the original post and this still hasn't been addressed by the developers. What gives?
jmurray replied on at Permalink Reply
THANK YOU!

That bit of knowledge has just saved me about two weeks of work. I'll agree with all the others who've said it - this bug has been known for far too long and remains unresolved. It shouldn't be hard to integrate these simple SQL statements into the upgrade routine and save a lot of hardship for people who're upgrading from earlier versions.

I might even have a dig around later and see how hard it is... is there a process for submitting source patches if I get it done?
ecabrera21 replied on at Permalink Reply
Thank you very much!!! I had the same problem I was really desperate, you saved me!
MattWaters replied on at Permalink Reply
MattWaters
Hi folks--

We've noted the issue on the Downloads page and created a How-To here:

http://www.concrete5.org/documentation/how-tos/developers/fix-block...

Thanks for stromberg for sharing his solution with everyone.
johnasb replied on at Permalink Reply
Hi,

I tried applying this fix, using PHPMyAdmin at BlueHost and I get an error:

SQL query:
ALTER TABLE `PagePaths` ADD KEY `cPath` ( `cPath` ( 128 ) ) ;
MySQL said:
#1061 - Duplicate key name 'cPath'

Can anyone help (I am new to all this).

Thanks!
muzza replied on at Permalink Reply 1 Attachment
I have definitely experienced this error and have applied these database mods to the tables. Unfortunately I'm still getting errors about Pages not found - specifically when I try to go to the update page.

The site was being managed by my customer, however after they screwed up their updates (trying to upgrade, downgrade, upgrade again, etc, etc) I don't really know where we are at, and they didn't take a backup of the database prior to the upgrade. The system is reporting something like 5.4.2.2 but when I try and refresh the 5.4.2.2 files there are still 'Page not found' errors coming up.

I'm now trying strombergs plan, installing a fresh 5.4.2.2 and workout what the difference is in the database structure and just hope that it's not the data that is wrong.

I wish there was an easier way to identify what the problem was rather than just report 'Page not found' errors. They are really unhelpful. Frankly it's turned me off Concrete5 entirely, but I still need to fix this for my customer.

Can you believe we are still getting these errors and we're a good way into 2013 now... that's nearly 3 years and it's still causing problems for people.
muzza replied on at Permalink Reply 1 Attachment
I have definitely experienced this error and have applied these database mods to the tables. Unfortunately I'm still getting errors about Pages not found - specifically when I try to go to the update page.

The site was being managed by my customer, however after they screwed up their updates (trying to upgrade, downgrade, upgrade again, etc, etc) I don't really know where we are at, and they didn't take a backup of the database prior to the upgrade. The system is reporting something like 5.4.2.2 but when I try and refresh the 5.4.2.2 files there are still 'Page not found' errors coming up.

I'm now trying strombergs plan, installing a fresh 5.4.2.2 and workout what the difference is in the database structure and just hope that it's not the data that is wrong.

I wish there was an easier way to identify what the problem was rather than just report 'Page not found' errors. They are really unhelpful. Frankly it's turned me off Concrete5 entirely, but I still need to fix this for my customer.

Can you believe we are still getting these errors and we're a good way into 2013 now... that's nearly 3 years and it's still causing problems for people.
muzza replied on at Permalink Reply 1 Attachment
I have definitely experienced this error and have applied these database mods to the tables. Unfortunately I'm still getting errors about Pages not found - specifically when I try to go to the update page.

The site was being managed by my customer, however after they screwed up their updates (trying to upgrade, downgrade, upgrade again, etc, etc) I don't really know where we are at, and they didn't take a backup of the database prior to the upgrade. The system is reporting something like 5.4.2.2 but when I try and refresh the 5.4.2.2 files there are still 'Page not found' errors coming up.

I'm now trying strombergs plan, installing a fresh 5.4.2.2 and workout what the difference is in the database structure and just hope that it's not the data that is wrong.

I wish there was an easier way to identify what the problem was rather than just report 'Page not found' errors. They are really unhelpful. Frankly it's turned me off Concrete5 entirely, but I still need to fix this for my customer.

Can you believe we are still getting these errors and we're a good way into 2013 now... that's nearly 3 years and it's still causing problems for people.
muzza replied on at Permalink Reply
I have definitely experienced this error and have applied these database mods to the tables. Unfortunately I'm still getting errors about Pages not found - specifically when I try to go to the update page.

The site was being managed by my customer, however after they screwed up their updates (trying to upgrade, downgrade, upgrade again, etc, etc) I don't really know where we are at, and they didn't take a backup of the database prior to the upgrade. The system is reporting something like 5.4.2.2 but when I try and refresh the 5.4.2.2 files there are still 'Page not found' errors coming up.

I'm now trying strombergs plan, installing a fresh 5.4.2.2 and workout what the difference is in the database structure and just hope that it's not the data that is wrong.

I wish there was an easier way to identify what the problem was rather than just report 'Page not found' errors. They are really unhelpful. Frankly it's turned me off Concrete5 entirely, but I still need to fix this for my customer.

Can you believe we are still getting these errors and we're a good way into 2013 now... that's nearly 3 years and it's still causing problems for people.
muzza replied on at Permalink Reply
What's the problem - why can't I send replies.