Hi there,

There are three orders that fail in the following:

* Update #6167 -- Failed: DELETE FROM {nodewords} WHERE type = 0

At that point type is still a varchar and thus the type value needs to be written between quotes: WHERE type = '0'.

* Update #6170 -- UPDATE {nodewords} SET id = CAST(id_old AS int)

The message I get is: Query failed: ERROR: invalid input syntax for integer: ""

I do not know how to fix this one. But going from string to int is not that straight forward, I would think. The trick in 6160 was run without error (surprisingly since you did not set the numbers between quotes...) and it could be that you need to fix that one first! (i.e. SET type = '1' ... instead of SET type = 1 ... since the type is still VARCHAR at that point.)

* Update #6170 -- ALTER TABLE {nodewords} ADD CONSTRAINT {nodewords}_nodewords_type_id_name_key UNIQUE (type,id,name)

This one tells me that there are duplicates. Since the type failed to be updated, it is likely that it would work otherwise.

Thank you.
Alexis

Comments

AlexisWilke’s picture

AlexisWilke commented

Okay, the type is an issue, but it has nothing to do with the failing CAST.

The problem is that PostgreSQL does not like the empty string! So you first need to update the column and any empty entry has to be set to something (i.e. '0').

UPDATE nodewords SET id = '0' WHERE id = '';

Thank you.
Alexis

AlexisWilke’s picture

AlexisWilke commented

Another one:

* Update #6167 -- db_query("SELECT COUNT(*) FROM {nodewords} WHERE type = '%d'", NODEWORDS_TYPE_TRACKER)

The type is still a string at this point, you need the quotes as shown here. (line 1270 as on Feb 22, 2010)

Thank you.
Alexis

Anonymous’s picture

Anonymous (not verified) commented
Status: Active » Postponed (maintainer needs more info)

Hello AlexisWilke, and thanks for reporting this.

May you report the database schema of the table after you executed those update functions?

AlexisWilke’s picture

AlexisWilke commented

There is nodewords, do you want another one too?

                                  Table "public.nodewords"
 Column  |         Type          |                        Modifiers                         
---------+-----------------------+----------------------------------------------------------
 mtid    | integer               | not null default nextval('nodewords_mtid_seq'::regclass)
 content | text                  | not null
 name    | character varying(32) | not null default ''::character varying
 type    | smallint_unsigned     | not null default 0
 id      | int_unsigned          | not null default 0
Indexes:
    "nodewords_pkey1" PRIMARY KEY, btree (mtid)
    "nodewords_nodewords_type_id_name_key" UNIQUE, btree (type, id, name)
    "nodewords_nodewords_name_idx" btree (substr(name::text, 1, 6))
    "nodewords_nodewords_type_id_idx" btree (type, id)

P.S. this is with or without my fixes, however, without my fixes the id column is all 0.

Anonymous’s picture

Anonymous (not verified) commented
Status: Postponed (maintainer needs more info) » Fixed

I have changed the update functions. Unfortunately, when the update 6170 has been already executed, there is nothing that can be fixed, as the field id will result 0 for each table row.

Thanks for your report, and your help.

AlexisWilke’s picture

AlexisWilke commented

Kiam,

I know, people should have backups just like me 8-)

Thank you for taking the time!
Alexis

Anonymous’s picture

Anonymous (not verified) commented

I apologize for the problem I caused.

I use MySQL on both the test site, and my site, and it seems that MySQL does automatic castings when PostgreSQL doesn't; in fact, on my test site MySQL didn't reported any errors.

AlexisWilke’s picture

AlexisWilke commented

kiam,

I know, I use MySQL a little bit too. But most everything I run with PostgreSQL which I trust more...

Now, if you have the time, having a test site with PostgreSQL would be a good idea. On my end, I very rarely test with MySQL, to tell you the truth...

Thank you.
Alexis

Status: Fixed » Closed (fixed)

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