DB errors when upgrading to 3.10.1.rc1

Previous topic

Developer

Next topic

Author	Message
Andrew Kelly	Thursday 17 July 2008 6:12:24 am Hi all, upgrading from 3.9.2 and currently using the RC for some dry runs and testing, while waiting for the stable 3.10.1 to come out. When using the database upgrade scripts I run into the standard errors that appear for us PostgreSQL about NULL values in a NOT NULL column, but I just mention that in passing as a friendly reminder that there are easy fixes to this, as I've mentioned (and patched) in the past. But I've got a new error that I'm finding a bit irritating, and I'm a bit lost as to whether it's an error in the update script or something wobbly in my database. I'm hopeful some of you can give me a hand here. The call to the DB that throws the error is: CREATE UNIQUE INDEX ezcontentobject_remote_id ON ezcontentobject (remote_id); which errors out with: ERROR: could not create unique index DETAIL: Table contains duplicated values. I've had a quick scan of my ezcontentobject table, and sure enough, I have several hundred duplicated remote_id values. So that raises for me the following questions: 1. How is this new index to be used? Does it really have to be unique? 2. How did I end up with duplicate values for various objects? I've never imported anything, so nothing I've ever done should have had any influence on those values. Can normal operation produce the occasional duplicate value for this field? 3. How is that particular value used by the system? Are they even used at all at this point? Can I safely alter values so that there are no more duplicates? Really appreciate any help here, and thank you in advance. Andy
André R.	Thursday 17 July 2008 11:23:45 am There should be a script included to fix that, anyway you can download it here if it wasn't included in rc: http://pubsvn.ez.no/nextgen/stable/3.10/update/common/scripts/3.10/fixobjectremoteid.php eZ Online Editor 5: http://projects.ez.no/ezoe \|\| eZJSCore (Ajax): http://projects.ez.no/ezjscore \|\| eZ Publish EE http://ez.no/eZPublish/eZ-Publish-Enterprise-Subscription @: http://twitter.com/andrerom
Andrew Kelly	Friday 18 July 2008 2:59:11 am Thanks André, much appreciated. Maybe there should be a brief note to that effect in the documentation? Sadly, like much of the scripting available, this one also seems to be built only for MySQL and fails without any kind of helpful information when used on a PostgreSQL installation. This line: SELECT remote_id, COUNT() cnt FROM ezcontentobject GROUP BY remote_id HAVING cnt > 1; won't even successfully complete from the command line (psql), and should be changed to SELECT remote_id, count() AS cnt FROM ezcontentobject GROUP BY remote_id HAVING count(*) > 1; This isn't enough to get things working, though, because if you jump 2 lines higher where the DB object is instantiated and follow that with a simple "who are you" query print( $db->databaseName() ); you'll be answered with "mysql" on my system, which is a bit odd since it's not even installed. Forgive my saying something critical here, but do you guys even have a QA for PostgreSQL support? I chose to deploy this product exactly because it claimed to use PG, but in several years of use, I think the only script which has ever run without my intervention has been runcronjobs.php. And even that won't obey the --quiet option any more. Andy

Author

Message

Andrew Kelly

Thursday 17 July 2008 6:12:24 am

Hi all,

upgrading from 3.9.2 and currently using the RC for some dry runs and testing, while waiting for the stable 3.10.1 to come out.

When using the database upgrade scripts I run into the standard errors that appear for us PostgreSQL about NULL values in a NOT NULL column, but I just mention that in passing as a friendly reminder that there are easy fixes to this, as I've mentioned (and patched) in the past.

But I've got a new error that I'm finding a bit irritating, and I'm a bit lost as to whether it's an error in the update script or something wobbly in my database. I'm hopeful some of you can give me a hand here.

The call to the DB that throws the error is:

CREATE UNIQUE INDEX ezcontentobject_remote_id ON ezcontentobject (remote_id);

which errors out with:
ERROR: could not create unique index
DETAIL: Table contains duplicated values.

I've had a quick scan of my ezcontentobject table, and sure enough, I have several hundred duplicated remote_id values.
So that raises for me the following questions:
1. How is this new index to be used? Does it really have to be unique?
2. How did I end up with duplicate values for various objects? I've never imported anything, so nothing I've ever done should have had any influence on those values. Can normal operation produce the occasional duplicate value for this field?
3. How is that particular value used by the system? Are they even used at all at this point? Can I safely alter values so that there are no more duplicates?

Really appreciate any help here, and thank you in advance.

Andy

André R.

Thursday 17 July 2008 11:23:45 am

There should be a script included to fix that, anyway you can download it here if it wasn't included in rc:
http://pubsvn.ez.no/nextgen/stable/3.10/update/common/scripts/3.10/fixobjectremoteid.php

eZ Online Editor 5: http://projects.ez.no/ezoe || eZJSCore (Ajax): http://projects.ez.no/ezjscore || eZ Publish EE http://ez.no/eZPublish/eZ-Publish-Enterprise-Subscription
@: http://twitter.com/andrerom

Andrew Kelly

Friday 18 July 2008 2:59:11 am

Thanks André, much appreciated. Maybe there should be a brief note to that effect in the documentation?

Sadly, like much of the scripting available, this one also seems to be built only for MySQL and fails without any kind of helpful information when used on a PostgreSQL installation.

This line:

SELECT remote_id, COUNT(*) cnt FROM ezcontentobject GROUP BY remote_id HAVING cnt > 1;

won't even successfully complete from the command line (psql), and should be changed to

SELECT remote_id, count(*) AS cnt FROM ezcontentobject GROUP BY remote_id HAVING count(*) > 1;

This isn't enough to get things working, though, because if you jump 2 lines higher where the DB object is instantiated and follow that with a simple "who are you" query

print( $db->databaseName() );

you'll be answered with "mysql" on my system, which is a bit odd since it's not even installed.

Forgive my saying something critical here, but do you guys even have a QA for PostgreSQL support? I chose to deploy this product exactly because it claimed to use PG, but in several years of use, I think the only script which has ever run without my intervention has been runcronjobs.php. And even that won't obey the --quiet option any more.

Andy