Hi, I updated to latest dev today and got this on initial update:



    * warning: pg_query() [function.pg-query]: Query failed: ERROR: column "vid" contains null values in /var/www/sparcs/includes/database.pgsql.inc on line 139.
    * user warning: query: ALTER TABLE feedapi ALTER vid SET NOT NULL in /var/www/sparcs/includes/database.pgsql.inc on line 697.
    * warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "," LINE 1: UPDATE feedapi f, node n SET f.vid = n.vid WHERE n.nid = f.n... ^ in /var/www/sparcs/includes/database.pgsql.inc on line 139.
    * user warning: query: UPDATE feedapi f, node n SET f.vid = n.vid WHERE n.nid = f.nid in /var/www/sparcs/sites/all/modules/feedapi/feedapi.install on line 193.
    * warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "=" LINE 1: UPDATE feedapi vid = nid WHERE vid = 0 ^ in /var/www/sparcs/includes/database.pgsql.inc on line 139.
    * user warning: query: UPDATE feedapi vid = nid WHERE vid = 0 in /var/www/sparcs/sites/all/modules/feedapi/feedapi.install on line 194.
    * warning: pg_query() [function.pg-query]: Query failed: ERROR: column "vid" contains null values in /var/www/sparcs/includes/database.pgsql.inc on line 139.
    * user warning: query: ALTER TABLE feedapi ADD PRIMARY KEY (vid) in /var/www/sparcs/includes/database.pgsql.inc on line 767.

Then this:

The following queries were executed
feedapi module
Update #6105

    * ALTER TABLE {feedapi} ADD COLUMN vid int_unsigned
    * Failed: ALTER TABLE {feedapi} ALTER vid SET NOT NULL
    * ALTER TABLE {feedapi} DROP CONSTRAINT {feedapi}_pkey
    * Failed: ALTER TABLE {feedapi} ADD PRIMARY KEY (vid)
    * CREATE INDEX {feedapi}_nid_idx ON {feedapi} (nid)
CommentFileSizeAuthor
#13 update_hook_vid_2.patch1.89 KBaron novak
#10 update_hook_vid.patch1.85 KBaron novak
#9 update_hook_vid.patch1.2 KBaron novak
#4 update_hook_vid.patch1.18 KBaron novak

Comments

spydmobile’s picture

spydmobile commented

tried rerunning the update and nothing happened....

spydmobile’s picture

spydmobile commented

now my feed refresh does not work. wondering if its too late to downgrade to the previous dev ?

spydmobile’s picture

spydmobile commented

I am awaiting any assistance on this as my feed is broken and thats the primary function of the site ;-(

aron novak’s picture

aron novak
Primary language Hungarian
Location Hungary, Budapest
commented
Status: Active » Needs review
StatusFileSize
new update_hook_vid.patch1.18 KB

Can you try out this patch?
First, restore the original database, if you cannot do that, the upgrade will produce some bogus queries again (drop key, add column)

spydmobile’s picture

spydmobile commented

Hiya Aron,
One of my techs got it working again while I was away, he said he just deleted the feed data and put the old module back in place and it worked.
I dont yet do CVS or have a patching solution in place, so which release or dev should I download and manually patch by hand for this to help you fix the problems?
Franco

alex_b’s picture

alex_b commented
Status: Needs review » Needs work

#4: it should be possible to do the update in a single query.

aron novak’s picture

aron novak
Primary language Hungarian
Location Hungary, Budapest
commented
Status: Needs work » Needs review

#6: yes, it IS possible. I did it at first attempt, see the patch in #4, i actually remove a one-liner solution.
I wrote a one-liner in postgresql and i could not execute it under mysql.
I assume it does not worth to take more time with this, it seems that mysql and postgresql supports different style of subqueries at UPDATE statements.
(edit: the pg-version was: UPDATE feedapi SET vid = n.vid FROM (SELECT vid, nid FROM node n) n WHERE n.nid = feedapi.nid;)

alex_b’s picture

alex_b commented

#7: My thinking was that a single query update will scale better. The query should also use update_sql() instead of db_query() (this may affect other queries in the update hook, too).

aron novak’s picture

aron novak
Primary language Hungarian
Location Hungary, Budapest
commented
StatusFileSize
new update_hook_vid.patch1.2 KB

Right, update_sql() is important.

aron novak’s picture

aron novak
Primary language Hungarian
Location Hungary, Budapest
commented
StatusFileSize
new update_hook_vid.patch1.85 KB

There was a huge bug: vid cannot be a primary key as a standalone column, it has to be tied to nid in the primary key
Now the patch simply checks out

alex_b’s picture

alex_b commented

Any feedback from pgsql users? spydmobile?

summit’s picture

summit commented

Hi,

Please also put brackets around the database tables, my tables with prefixes are not updating correctly.
Also wrong on 1.7-beta.
So {feedapi} instead of feedapi etc.. please.
Thanks for considering this!
greetings,
Martijn

aron novak’s picture

aron novak
Primary language Hungarian
Location Hungary, Budapest
commented
StatusFileSize
new update_hook_vid_2.patch1.89 KB

Summit, thanks for the warning!

spydmobile’s picture

spydmobile commented

Sorry folks I have been away! Did this patch make it into beta 1?
F

aron novak’s picture

aron novak
Primary language Hungarian
Location Hungary, Budapest
commented

just fetch the beta, apply this patch and after that you can do the upgrade

alex_b’s picture

alex_b commented

Not sure whether we should test for mysql or pgsql in the if/else statement. We should test for the one whose notation is an exception to the SQL standard, I'd say.

Otherwise: let's get this into beta 2.

aron novak’s picture

aron novak
Primary language Hungarian
Location Hungary, Budapest
commented
Status: Needs review » Fixed

I tested the whole update process under pgsql. committed.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.