[ckan-discuss] Problem with new installation of CKAN

[Armand Brahaj]

Hello everyeone, 

I was wondering if someone can assist me in a
problem I am having. 

I installed a new instance by installing from
source, but when I want to init the db, I get a long error. 

(Running
on Debian Squeeze/Installing the latest version of CKAN from the
http://bitbucket.org/okfn/ckan + Posgtres 9.1) 

I am pasting it here,
hoping it is ok:
paster --plugin=ckan db init
Traceback (most recent
call last):
 File "/home/katalog/pyenv/bin/paster", line 9, in 

load_entry_point('PasteScript==1.7.3', 'console_scripts', 'paster')()

File
"/home/katalog/pyenv/lib/python2.7/site-packages/paste/script/command.py",
line 84, in run
 invoke(command, command_name, options, args[1:])
 File
"/home/katalog/pyenv/lib/python2.7/site-packages/paste/script/command.py",
line 123, in invoke
 exit_code = runner.run(args)
 File
"/home/katalog/pyenv/lib/python2.7/site-packages/paste/script/command.py",
line 218, in run
 result = self.command()
 File
"/home/katalog/pyenv/src/ckan/ckan/lib/cli.py", line 90, in command

model.repo.init_db()
 File
"/home/katalog/pyenv/src/ckan/ckan/model/__init__.py", line 75, in
init_db
 self.upgrade_db()
 File
"/home/katalog/pyenv/src/ckan/ckan/model/__init__.py", line 175, in
upgrade_db
 mig.upgrade(self.metadata.bind, self.migrate_repository,
version=version)
 File
"/home/katalog/pyenv/lib/python2.7/site-packages/migrate/versioning/api.py",
line 185, in upgrade
 return _migrate(url, repository, version,
upgrade=True, err=err, **opts)
 File "", line 2, in _migrate
 File
"/home/katalog/pyenv/lib/python2.7/site-packages/migrate/versioning/util/__init__.py",
line 160, in with_engine
 return f(*a, **kw)
 File
"/home/katalog/pyenv/lib/python2.7/site-packages/migrate/versioning/api.py",
line 364, in _migrate
 schema.runchange(ver, change, changeset.step)

File
"/home/katalog/pyenv/lib/python2.7/site-packages/migrate/versioning/schema.py",
line 83, in runchange
 change.run(self.engine, step)
 File
"/home/katalog/pyenv/lib/python2.7/site-packages/migrate/versioning/script/py.py",
line 140, in run
 script_func(engine)
 File
"/home/katalog/pyenv/src/ckan/ckan/migration/versions/039_add_expired_id_and_dates.py",
line 207, in upgrade
 migrate_engine.execute('begin; ' +
make_missing_revisions + update_schema + ' commit;')
 File
"/home/katalog/pyenv/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
line 1788, in execute
 return connection.execute(statement,
*multiparams, **params)
 File
"/home/katalog/pyenv/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
line 1191, in execute
 params)
 File
"/home/katalog/pyenv/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
line 1287, in _execute_text
 return self.__execute_context(context)

File
"/home/katalog/pyenv/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
line 1302, in __execute_context
 context.parameters[0],
context=context)
 File
"/home/katalog/pyenv/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
line 1401, in _cursor_execute
 context)
 File
"/home/katalog/pyenv/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
line 1394, in _cursor_execute
 context)
 File
"/home/katalog/pyenv/lib/python2.7/site-packages/sqlalchemy/engine/default.py",
line 299, in do_execute
 cursor.execute(statement,
parameters)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) syntax
error at or near "over"
LINE 87: ...n_id, timestamp, lead(timestamp, 1,
'9999-12-31') over (part...
 ^
 'begin; nn-- make sure all tables have
an entry in the revision_tablenninsert into revision values
('96eace64-870a-44a6-9764-9a37687b88e8' , '2011-11-17T13:01:27.330092',
'admin', 'Admin: make sure every object has a row in a revision table',
'active');nninsert into package_tag_revision
(id,package_id,tag_id,revision_id,state,continuity_id) select
id,package_id,tag_id, '96eace64-870a-44a6-9764-9a37687b88e8' ,state, id
from package_tag where package_tag.id not in (select id from
package_tag_revision);nninsert into resource_revision
(id,resource_group_id,url,format,description,position,revision_id,hash,state,extras,continuity_id)
select id,resource_group_id,url,format,description,position,
'96eace64-870a-44a6-9764-9a37687b88e8' ,hash,state,extras, id from
resource where resource.id not in (select id from
resource_revision);nninsert into group_extra_revision
(id,group_id,key,value,state,revision_id,continuity_id) select
id,group_id,key,value,state, '96eace64-870a-44a6-9764-9a37687b88e8' , id
from group_extra where group_extra.id not in (select id from
group_extra_revision);nninsert into resource_group_revision
(id,package_id,label,sort_order,extras,state,revision_id,continuity_id)
select id,package_id,label,sort_order,extras,state,
'96eace64-870a-44a6-9764-9a37687b88e8', id from resource_group where
resource_group.id not in (select id from
resource_group_revision);nninsert into package_extra_revision
(id,package_id,key,value,revision_id,state,continuity_id) select
id,package_id,key,value, '96eace64-870a-44a6-9764-9a37687b88e8',state,
id from package_extra where package_extra.id not in (select id from
package_extra_revision);nninsert into package_relationship_revision
(id,subject_package_id,object_package_id,type,comment,revision_id,state,continuity_id)
select id,subject_package_id,object_package_id,type,comment,
'96eace64-870a-44a6-9764-9a37687b88e8',state, id from
package_relationship where package_relationship.id not in (select id
from package_relationship_revision);n ninsert into group_revision
(id,name,title,description,created,state,revision_id,continuity_id)
select id,name,title,description,created,state,
'96eace64-870a-44a6-9764-9a37687b88e8', id from "group" where "group".id
not in (select id from group_revision);nninsert into package_revision
(id,name,title,url,notes,license_id,revision_id,version,author,author_email,maintainer,maintainer_email,state,continuity_id)
select id,name,title,url,notes,license_id,
'96eace64-870a-44a6-9764-9a37687b88e8',version,author,author_email,maintainer,maintainer_email,state,
id from package where package.id not in (select id from
package_revision);nnnALTER TABLE package_revisionntADD COLUMN expired_id
text,ntADD COLUMN revision_timestamp timestamp without time zone,ntADD
COLUMN expired_timestamp timestamp without time zone,ntADD COLUMN
current boolean;nnALTER TABLE package_extra_revisionntADD COLUMN
expired_id text,ntADD COLUMN revision_timestamp timestamp without time
zone,ntADD COLUMN expired_timestamp timestamp without time zone,ntADD
COLUMN current boolean;nnALTER TABLE group_revisionntADD COLUMN
expired_id text,ntADD COLUMN revision_timestamp timestamp without time
zone,ntADD COLUMN expired_timestamp timestamp without time zone,ntADD
COLUMN current boolean;nnALTER TABLE group_extra_revisionntADD COLUMN
expired_id text,ntADD COLUMN revision_timestamp timestamp without time
zone,ntADD COLUMN expired_timestamp timestamp without time zone,ntADD
COLUMN current boolean;nnnALTER TABLE package_group_revisionntADD COLUMN
expired_id text,ntADD COLUMN revision_timestamp timestamp without time
zone,ntADD COLUMN expired_timestamp timestamp without time zone,ntADD
COLUMN current boolean;nnALTER TABLE package_tag_revisionntADD COLUMN
expired_id text,ntADD COLUMN revision_timestamp timestamp without time
zone,ntADD COLUMN expired_timestamp timestamp without time zone,ntADD
COLUMN current boolean;nnALTER TABLE resource_group_revisionntADD COLUMN
expired_id text,ntADD COLUMN revision_timestamp timestamp without time
zone,ntADD COLUMN expired_timestamp timestamp without time zone,ntADD
COLUMN current boolean;nnALTER TABLE resource_revisionntADD COLUMN
expired_id text,ntADD COLUMN revision_timestamp timestamp without time
zone,ntADD COLUMN expired_timestamp timestamp without time zone,ntADD
COLUMN current boolean;nnALTER TABLE package_relationship_revision ntADD
COLUMN expired_id text,ntADD COLUMN revision_timestamp timestamp without
time zone,ntADD COLUMN expired_timestamp timestamp without time
zone,ntADD COLUMN current boolean;nnALTER TABLE revisionntADD COLUMN
approved_timestamp timestamp without time zone;nncreate table
tmp_expired_id(id text, revision_id text, revision_timestamp timestamp,
expired_timestamp timestamp, expired_id text);ncreate index id_exp on
tmp_expired_id(id, revision_id);nn--package revisionntruncate
tmp_expired_id;ninsert into tmp_expired_id select pr.id, revision_id,
timestamp, lead(timestamp, 1, '9999-12-31') over (partition by pr.id
order by timestamp), lead(pr.revision_id) over (partition by pr.id order
by timestamp) from package_revision pr join revision r on pr.revision_id
= r.id;nupdate package_revision pr set revision_timestamp = (select
revision_timestamp from tmp_expired_id tmp where tmp.revision_id =
pr.revision_id and tmp.id = pr.id),n expired_timestamp = (select
expired_timestamp from tmp_expired_id tmp where tmp.revision_id =
pr.revision_id and tmp.id = pr.id),n expired_id = (select expired_id
from tmp_expired_id tmp where tmp.revision_id = pr.revision_id and
tmp.id = pr.id);nupdate package_revision set current = '1' where
expired_timestamp = '9999-12-31';nncreate index idx_package_period on
package_revision(revision_timestamp, expired_timestamp, id);ncreate
index idx_package_current on package_revision(current);nn--package extra
revision ntruncate tmp_expired_id;ninsert into tmp_expired_id select
pr.id, revision_id, timestamp, lead(timestamp, 1, '9999-12-31') over
(partition by pr.id order by timestamp), lead(pr.revision_id) over
(partition by pr.id order by timestamp) from package_extra_revision pr
join revision r on pr.revision_id = r.id;nupdate package_extra_revision
pr set revision_timestamp = (select revision_timestamp from
tmp_expired_id tmp where tmp.revision_id = pr.revision_id and tmp.id =
pr.id),n expired_timestamp = (select expired_timestamp from
tmp_expired_id tmp where tmp.revision_id = pr.revision_id and tmp.id =
pr.id),n expired_id = (select expired_id from tmp_expired_id tmp where
tmp.revision_id = pr.revision_id and tmp.id = pr.id);nupdate
package_extra_revision set current = '1' where expired_timestamp =
'9999-12-31';nncreate index idx_package_extra_period on
package_extra_revision(revision_timestamp, expired_timestamp,
id);ncreate index idx_package_extra_period_package on
package_extra_revision(revision_timestamp, expired_timestamp,
package_id);ncreate index idx_package_extra_current on
package_extra_revision(current);nn--package group revisionntruncate
tmp_expired_id;ninsert into tmp_expired_id select pr.id, revision_id,
timestamp, lead(timestamp, 1, '9999-12-31') over (partition by pr.id
order by timestamp), lead(pr.revision_id) over (partition by pr.id order
by timestamp) from package_group_revision pr join revision r on
pr.revision_id = r.id;nupdate package_group_revision pr set
revision_timestamp = (select revision_timestamp from tmp_expired_id tmp
where tmp.revision_id = pr.revision_id and tmp.id = pr.id),n
expired_timestamp = (select expired_timestamp from tmp_expired_id tmp
where tmp.revision_id = pr.revision_id and tmp.id = pr.id),n expired_id
= (select expired_id from tmp_expired_id tmp where tmp.revision_id =
pr.revision_id and tmp.id = pr.id);nupdate package_group_revision set
current = '1' where expired_timestamp = '9999-12-31';nncreate index
idx_package_group_period_package_group on
package_group_revision(revision_timestamp, expired_timestamp,
package_id, group_id);ncreate index idx_package_group_current on
package_group_revision(current);nnn-- package_tagsntruncate
tmp_expired_id;ninsert into tmp_expired_id select pr.id, revision_id,
timestamp, lead(timestamp, 1, '9999-12-31') over (partition by pr.id
order by timestamp), lead(pr.revision_id) over (partition by pr.id order
by timestamp) from package_tag_revision pr join revision r on
pr.revision_id = r.id;nupdate package_tag_revision pr set
revision_timestamp = (select revision_timestamp from tmp_expired_id tmp
where tmp.revision_id = pr.revision_id and tmp.id = pr.id),n
expired_timestamp = (select expired_timestamp from tmp_expired_id tmp
where tmp.revision_id = pr.revision_id and tmp.id = pr.id),n expired_id
= (select expired_id from tmp_expired_id tmp where tmp.revision_id =
pr.revision_id and tmp.id = pr.id);nupdate package_tag_revision set
current = '1' where expired_timestamp = '9999-12-31';nncreate index
idx_period_package_tag on package_tag_revision(revision_timestamp,
expired_timestamp, package_id, tag_id);ncreate index
idx_package_tag_current on package_tag_revision(current);nn-- package
relationshipntruncate tmp_expired_id;ninsert into tmp_expired_id select
pr.id, revision_id, timestamp, lead(timestamp, 1, '9999-12-31') over
(partition by pr.id order by timestamp), lead(pr.revision_id) over
(partition by pr.id order by timestamp) from
package_relationship_revision pr join revision r on pr.revision_id =
r.id;nupdate package_relationship_revision pr set revision_timestamp =
(select revision_timestamp from tmp_expired_id tmp where tmp.revision_id
= pr.revision_id and tmp.id = pr.id),n expired_timestamp = (select
expired_timestamp from tmp_expired_id tmp where tmp.revision_id =
pr.revision_id and tmp.id = pr.id),n expired_id = (select expired_id
from tmp_expired_id tmp where tmp.revision_id = pr.revision_id and
tmp.id = pr.id);nupdate package_relationship_revision set current = '1'
where expired_timestamp = '9999-12-31';nncreate index
idx_period_package_relationship on
package_relationship_revision(revision_timestamp, expired_timestamp,
object_package_id, subject_package_id);ncreate index
idx_package_relationship_current on
package_relationship_revision(current);nn-- resource revisionntruncate
tmp_expired_id;ninsert into tmp_expired_id select pr.id, revision_id,
timestamp, lead(timestamp, 1, '9999-12-31') over (partition by pr.id
order by timestamp), lead(pr.revision_id) over (partition by pr.id order
by timestamp) from resource_revision pr join revision r on
pr.revision_id = r.id;nupdate resource_revision pr set
revision_timestamp = (select revision_timestamp from tmp_expired_id tmp
where tmp.revision_id = pr.revision_id and tmp.id = pr.id),n
expired_timestamp = (select expired_timestamp from tmp_expired_id tmp
where tmp.revision_id = pr.revision_id and tmp.id = pr.id),n expired_id
= (select expired_id from tmp_expired_id tmp where tmp.revision_id =
pr.revision_id and tmp.id = pr.id);nupdate resource_revision set current
= '1' where expired_timestamp = '9999-12-31';nncreate index
idx_resource_period on resource_revision(revision_timestamp,
expired_timestamp, id);ncreate index idx_resource_period_resource_group
on resource_revision(revision_timestamp, expired_timestamp,
resource_group_id);ncreate index idx_resource_current on
resource_revision(current);nn-- resource group revision;ntruncate
tmp_expired_id;ninsert into tmp_expired_id select pr.id, revision_id,
timestamp, lead(timestamp, 1, '9999-12-31') over (partition by pr.id
order by timestamp), lead(pr.revision_id) over (partition by pr.id order
by timestamp) from resource_group_revision pr join revision r on
pr.revision_id = r.id;nupdate resource_group_revision pr set
revision_timestamp = (select revision_timestamp from tmp_expired_id tmp
where tmp.revision_id = pr.revision_id and tmp.id = pr.id),n
expired_timestamp = (select expired_timestamp from tmp_expired_id tmp
where tmp.revision_id = pr.revision_id and tmp.id = pr.id),n expired_id
= (select expired_id from tmp_expired_id tmp where tmp.revision_id =
pr.revision_id and tmp.id = pr.id);nupdate resource_group_revision set
current = '1' where expired_timestamp = '9999-12-31';nncreate index
idx_resource_group_period on resource_group_revision(revision_timestamp,
expired_timestamp, id);ncreate index idx_resource_group_period_package
on resource_group_revision(revision_timestamp, expired_timestamp,
package_id);ncreate index idx_resource_group_current on
resource_group_revision(current);nn--group revision;ntruncate
tmp_expired_id;ninsert into tmp_expired_id select pr.id, revision_id,
timestamp, lead(timestamp, 1, '9999-12-31') over (partition by pr.id
order by timestamp), lead(pr.revision_id) over (partition by pr.id order
by timestamp) from group_revision pr join revision r on pr.revision_id =
r.id;nupdate group_revision pr set revision_timestamp = (select
revision_timestamp from tmp_expired_id tmp where tmp.revision_id =
pr.revision_id and tmp.id = pr.id),n expired_timestamp = (select
expired_timestamp from tmp_expired_id tmp where tmp.revision_id =
pr.revision_id and tmp.id = pr.id),n expired_id = (select expired_id
from tmp_expired_id tmp where tmp.revision_id = pr.revision_id and
tmp.id = pr.id);nupdate group_revision set current = '1' where
expired_timestamp = '9999-12-31';nncreate index idx_group_period on
group_revision(revision_timestamp, expired_timestamp, id);ncreate index
idx_group_current on group_revision(current);nn--group extra revision
ntruncate tmp_expired_id;ninsert into tmp_expired_id select pr.id,
revision_id, timestamp, lead(timestamp, 1, '9999-12-31') over (partition
by pr.id order by timestamp), lead(pr.revision_id) over (partition by
pr.id order by timestamp) from group_extra_revision pr join revision r
on pr.revision_id = r.id;nupdate group_extra_revision pr set
revision_timestamp = (select revision_timestamp from tmp_expired_id tmp
where tmp.revision_id = pr.revision_id and tmp.id = pr.id),n
expired_timestamp = (select expired_timestamp from tmp_expired_id tmp
where tmp.revision_id = pr.revision_id and tmp.id = pr.id),n expired_id
= (select expired_id from tmp_expired_id tmp where tmp.revision_id =
pr.revision_id and tmp.id = pr.id);nupdate group_extra_revision set
current = '1' where expired_timestamp = '9999-12-31';nncreate index
idx_group_extra_period on group_extra_revision(revision_timestamp,
expired_timestamp, id);ncreate index idx_group_extra_period_group on
group_extra_revision(revision_timestamp, expired_timestamp,
group_id);ncreate index idx_group_extra_current on
group_extra_revision(current);nndrop table tmp_expired_id;nn-- change
state of revision tablesnnupdate revision set approved_timestamp =
timestamp;n commit;' {} 

Thank you for the assistance, 

Armand 

  
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.okfn.org/pipermail/ckan-discuss/attachments/20111117/3ec7b536/attachment-0001.htm>