History | Log In     View a printable version of the current page.  
Issue Details (XML | Word | Printable)

Key: OX-5033
Type: Bug Bug
Status: Closed Closed
Resolution: Duplicate
Priority: Critical Critical
Assignee: Unassigned-Adserver
Reporter: Michal Czosnyka
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
OpenX Ad Server

Postgres Db Schema Probems After Upgrade from 2.0.11 -> 2.8.2-rc25

Created: 03/Mar/09 11:42 AM   Updated: 11/Jan/10 06:22 PM
Component/s: OXP: Installation & Upgrade System
Affects Version/s: OpenX 2.6.4, OpenX 2.8.2
Fix Version/s: None
Security Level: Public (All users can see these issues)

Time Tracking:
Not Specified

Issue Links:
Duplicate
 


 Description  « Hide
After upgrading from 2.0.11 -> 2.6.4, editing advertiser fails: http://example.com/admin/advertiser-edit.php?clientid=2

An error occurred while accessing the database

Due to a problem with the database OpenX couldn't retrieve or store data. If this problem is reproducable it might be caused by a bug in OpenX. Please report the following information to the creators of OpenX. Also try to describe the actions that led to this error as clearly as possible.

Version: OpenX v2.6.4
PHP/DB: PHP 5.1.2 / PostgreSQL 8.3.1-
Page: /admin/advertiser-edit.php
Error: ERROR: column "lb_reporting" is of type boolean but expression is of type integer at character 284 HINT: You will need to rewrite or cast the expression.
Query:

UPDATE "phpads_clients" SET "agencyid" = 1 , "clientname" = 'Avnet' , "contact" = 'Avnet' , "email" = 'ads@supplyframe.com' , "report" = 'f' , "reportinterval" = 7 , "reportlastdate" = '2008-01-18' , "reportdeactivate" = 'f' , "comments" = '' , "updated" = '2009-02-27 18:54:02' , "lb_reporting" = 0 , "an_adnetwork_id" = 0 , "as_advertiser_id" = 0 , "account_id" = 13 , "advertiser_limitation" = 0 WHERE ( "phpads_clients"."clientid" = 2 )

$_POST:

Array
(
[clientid] => 2
[clientname] => Avnet
[contact] => Avnet
[email] => ads@supplyframe.com
[clientreportlastdate] => 2008-01-18
[clientreportprevious] => f
[clientreportinterval] => 7
[comments] =>
[submit] => Save Changes
)

$_GET:

Empty

It appears the upgrade db did not convert lb_reporting properly. After upgrade the client table looks like this:

CODE
Table "public.phpads_clients"
Column | Type | Modifiers
-------------------------------------------------------------------------------------------------------------------
clientid | integer | not null default nextval('phpads_clients_clientid_seq'::regclass)
clientname | character varying(255) | not null default ''::character varying
contact | character varying(255) |
email | character varying(64) |
report | boolean |
reportinterval | integer | default 7
reportlastdate | date |
reportdeactivate | boolean |
lb_reporting | boolean | not null default false
agencyid | integer | not null default 0
comments | text |
updated | timestamp without time zone |
an_adnetwork_id | integer |
as_advertiser_id | integer |
account_id | integer |
advertiser_limitation | smallint | not null default 0
Indexes:
"phpads_clients_pkey" PRIMARY KEY, btree (clientid)
"phpads_clients_account_id" UNIQUE, btree (account_id)

Running the following db script to convert the column type fixes the problem and the save works.

CODE
alter table phpads_clients alter column lb_reporting drop default;
alter table phpads_clients alter column lb_reporting type smallint using case when lb_reporting then 1 else 0 end;
alter table phpads_clients alter column lb_reporting set default 0;
alter table phpads_clients alter column lb_reporting set not null;

POST: http://forum.openx.org/index.php?showtopic=503425477



 All   Comments   Work Log   Change History   FishEye   Crucible   Builds      Sort Order: Ascending order - Click to sort in descending order
Change by Michal Czosnyka - 03/Mar/09 11:42 AM
Field Original Value New Value
Status Needs Verification [ 10000 ] Verified [ 10001 ]

Valery Masiutsin - 18/Mar/09 08:04 AM
MySQL has no boolean datatype, it's just an alias for the smallint (which is utterly wrong especially for the language with a static typisation such as SQL)

Change by matthieu.aubry - 11/Sep/09 05:55 PM
Assignee Andrew Hill [ andrew.hill ] Unassigned-Adserver [ unassigned-ox ]


Change by Lukasz Dybcio - 06/Oct/09 02:01 PM
Affects Version/s OpenX 2.8.2 [ 10910 ]
Priority Major [ 3 ] Critical [ 2 ]

Change by Lukasz Dybcio - 06/Oct/09 02:05 PM
Summary Postgres Db Schema Probems After Upgrade from 2.0.11 -> 2.6.4 Postgres Db Schema Probems After Upgrade from 2.0.11 -> 2.8.2-rc25

Change by Lukasz Dybcio - 06/Oct/09 02:24 PM
Link This issue is a duplicate of OX-4928 [ OX-4928 ]

Change by Joanna Mazgaj - 11/Jan/10 06:22 PM
Status Verified [ 10001 ] Closed [ 6 ]
Resolution Duplicate [ 3 ]