Originally sent by ivanov17 on the mailing list. I'm forwarding into tracker.
On 2023-11-21 03:43, John P. Rouillard wrote:
> Have you had a chance to test this?
Hello. Sorry for the long silence.
I have tested this and found the following two problems.
First, the 'roundup-admin initialise' command doesn't work properly in
this case. I got the same results using first the PGSERVICE environment
variable and then the rdbms_service configuration parameter.
$ roundup-admin initialise
Enter tracker home: core
Admin Password:
Confirm:
WARNING: The database is already initialised!
If you re-initialise it, you will lose all the data!
Erase it? Y/N: Y
2023-12-18 03:15:58,166 INFO DROP DATABASE ""
Traceback (most recent call last):
File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
line 106, in pg_command
cursor.execute(command)
psycopg2.errors.SyntaxError: zero-length delimited identifier at or near
""""
LINE 1: DROP DATABASE ""
^
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "/home/user/Projects/www/roundup_dev/env/bin/roundup-admin", line
8, in <module>
sys.exit(run())
File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/scripts/roundup_admin.py",
line 50, in run
sys.exit(tool.main())
File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/admin.py",
line 2230, in main
ret3D self.run_command(args)
File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/admin.py",
line 2079, in run_command
return self.do_initialise(self.tracker_home, args)
File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/admin.py",
line 1168, in do_initialise
tracker.nuke()
File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/instance.py",
line 213, in nuke
self.backend.db_nuke(self.config)
File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
line 64, in db_nuke
db_command(config, command)
File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
line 92, in db_command
if pg_command(cursor, command):
File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
line 118, in pg_command
raise RuntimeError(response)
RuntimeError: zero-length delimited identifier at or near """"
But what's even stranger is that I can't initialize the database when I
specify the database name, host, port, username and password in the
configuration file.
$ roundup-admin initialise
Enter tracker home: core
Admin Password:
Confirm:
WARNING: The database is already initialised!
If you re-initialise it, you will lose all the data!
Erase it? Y/N: Y
2023-12-18 19:31:01,239 INFO DROP DATABASE "roundup_dev"
Traceback (most recent call last):
File "/home/user/Projects/www/roundup_dev/env/bin/roundup-admin", line
8, in <module>
sys.exit(run())
File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/scripts/roundup_admin.py",
line 50, in run
sys.exit(tool.main())
File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/admin.py",
line 2230, in main
ret3D self.run_command(args)
File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/admin.py",
line 2079, in run_command
return self.do_initialise(self.tracker_home, args)
File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/admin.py",
line 1168, in do_initialise
tracker.nuke()
File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/instance.py",
line 213, in nuke
self.backend.db_nuke(self.config)
File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
line 64, in db_nuke
db_command(config, command)
File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
line 96, in db_command
raise RuntimeError('10 attempts to create database failed')
RuntimeError: 10 attempts to create database failed
My postgresql user is the owner of the selected database, but does not
have permission to create databases.
I think the problem is here, but I don't want to give them additional
permissions just to initially populate the database.
Maybe some kind of non-destructive initialization might be possible,
without recreating the entire database?
By the way, why a database and not a schema?
At the same time, when I directly create Roundup users, it works for me.
$ roundup-admin create user username="admin" roles="Admin"
Enter tracker home: core
2023-12-18 20:03:10,205 INFO open database via service 'roundup_dev'
2023-12-18 20:03:10,241 INFO commit
2023-12-18 20:03:10,241 DEBUG SQL "select nextval('_user_ids') from
dual" None
2023-12-18 20:03:10,242 DEBUG SQL 'select id from _user where
_username=%s and __retired__=%s' ('admin', 0)
2023-12-18 20:03:10,245 DEBUG addnode user1 {'username': 'admin',
'roles': 'Admin', 'password': None, 'address': None, 'realname': None,
'phone': None, 'organisation': None, 'alternate_addresses': None,
'queries': [], 'timezone': None}
2023-12-18 20:03:10,245 DEBUG SQL 'insert into _user
(_activity,_actor,_address,_alternate_addresses,_creation,_creator,_organisation,_password,_phone,_realname,_roles,_timezone,_username,id)
values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)' ('2023-12-18
16:03:10.245', 1, None, None, '2023-12-18 16:03:10.245', 1, None, None,
None, None, 'Admin', None, 'admin', '1')
2023-12-18 20:03:10,246 DEBUG addjournal user1 <Date
2023-12-18.16:03:10.246> 1 create {}
2023-12-18 20:03:10,246 DEBUG SQL 'insert into user__journal
(nodeid,date,tag,action,params) values (%s,%s,%s,%s,%s)' ('1',
'2023-12-18 16:03:10.246', '1', 'create', '{}')
1
2023-12-18 20:03:10,247 INFO commit
2023-12-18 20:03:10,248 INFO close
$ roundup-admin create user username="anonymous" roles="Anonymous"
Enter tracker home: core
2023-12-18 20:03:55,461 INFO open database via service 'roundup_dev'
2023-12-18 20:03:55,493 INFO commit
2023-12-18 20:03:55,494 DEBUG SQL "select nextval('_user_ids') from
dual" None
2023-12-18 20:03:55,495 DEBUG SQL 'select id from _user where
_username=%s and __retired__=%s' ('anonymous', 0)
2023-12-18 20:03:55,496 DEBUG addnode user2 {'username': 'anonymous',
'roles': 'Anonymous', 'password': None, 'address': None, 'realname':
None, 'phone': None, 'organisation': None, 'alternate_addresses': None,
'queries': [], 'timezone': None}
2023-12-18 20:03:55,496 DEBUG SQL 'insert into _user
(_activity,_actor,_address,_alternate_addresses,_creation,_creator,_organisation,_password,_phone,_realname,_roles,_timezone,_username,id)
values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)' ('2023-12-18
16:03:55.497', 1, None, None, '2023-12-18 16:03:55.497', 1, None, None,
None, None, 'Anonymous', None, 'anonymous', '2')
2023-12-18 20:03:55,497 DEBUG addjournal user2 <Date
2023-12-18.16:03:55.497> 1 create {}
2023-12-18 20:03:55,497 DEBUG SQL 'insert into user__journal
(nodeid,date,tag,action,params) values (%s,%s,%s,%s,%s)' ('2',
'2023-12-18 16:03:55.497', '1', 'create', '{}')
2
2023-12-18 20:03:55,497 INFO commit
2023-12-18 20:03:55,505 INFO close
roundup_dev=> select * from public._user;
_activity | _actor | _address | _alternate_addresses |
_creation | _creator | _organisation | _password | _phone |
_realname | _roles | _timezone | _username | id | __retired__
- - -------------------------+--------+----------+----------------------+---- ----------------------+----------+---------------+-----------+--------+---- --------+-----------+-----------+-----------+----+-------------
2023-12-18 16:03:10.245 | 1 | | |
2023-12-18 16:03:10.245 | 1 | | |
| | Admin | | admin | 1 | 0
2023-12-18 16:03:55.497 | 1 | | |
2023-12-18 16:03:55.497 | 1 | | |
| | Anonymous | | anonymous | 2 | 0
(2 rows)
Second, there is a problem with using a schema other than 'public'. I
can't perform database migration when I create scheme named by my
postgresql username and use it in search_path. I see that Roundup
expected me to use the 'public' schema.
$ roundup-admin migrate
Enter tracker home: core
2023-12-18 03:13:02,011 INFO open database via service 'roundup_dev'
2023-12-18 03:13:02,033 INFO rollback
2023-12-18 03:13:02,033 DEBUG SQL 'CREATE TABLE schema (schema TEXT)'
None
2023-12-18 03:13:02,044 DEBUG SQL 'CREATE TABLE dual (dummy integer)'
None
2023-12-18 03:13:02,046 DEBUG SQL 'insert into dual values (1)' None
2023-12-18 03:13:02,047 DEBUG SQL 'CREATE TABLE otks (otk_key
VARCHAR(255),\n otk_value TEXT, otk_time float)' None
2023-12-18 03:13:02,055 DEBUG SQL 'CREATE INDEX otks_key_idx ON
otks(otk_key)' None
2023-12-18 03:13:02,060 DEBUG SQL 'CREATE TABLE sessions (\n
session_key VARCHAR(255), session_time float,\n session_value
TEXT)' None
2023-12-18 03:13:02,065 DEBUG SQL 'CREATE INDEX sessions_key_idx ON\n
sessions(session_key)' None
2023-12-18 03:13:02,069 DEBUG SQL 'CREATE SEQUENCE ___textids_ids' None
2023-12-18 03:13:02,070 DEBUG SQL 'CREATE TABLE __textids (\n
_textid integer primary key, _class VARCHAR(255),\n _itemid
VARCHAR(255), _prop VARCHAR(255))' None
2023-12-18 03:13:02,079 DEBUG SQL 'CREATE TABLE __words (_word
VARCHAR(55),\n _textid integer)' None
2023-12-18 03:13:02,080 DEBUG SQL 'CREATE INDEX words_word_idx ON
__words(_word)' None
2023-12-18 03:13:02,087 DEBUG SQL 'CREATE INDEX words_by_id ON __words
(_textid)' None
2023-12-18 03:13:02,092 DEBUG SQL 'CREATE UNIQUE INDEX
__textids_by_props ON __textids (_class, _itemid, _prop)' None
2023-12-18 03:13:02,097 DEBUG SQL 'DELETE FROM otks' None
2023-12-18 03:13:02,097 DEBUG SQL 'ALTER TABLE otks DROP otk_value' None
2023-12-18 03:13:02,098 DEBUG SQL 'ALTER TABLE otks ADD otk_value TEXT'
None
2023-12-18 03:13:02,099 DEBUG SQL 'DELETE FROM sessions' None
2023-12-18 03:13:02,099 DEBUG SQL 'ALTER TABLE sessions DROP
session_value' None
2023-12-18 03:13:02,099 DEBUG SQL 'ALTER TABLE sessions ADD
session_value TEXT' None
2023-12-18 03:13:02,100 DEBUG SQL 'CREATE INDEX words_both_idx ON
public.__words\n USING btree (_word, _textid)' None
Traceback (most recent call last):
File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
line 217, in open_connection
self.load_dbschema()
File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/rdbms_common.py",
line 305, in load_dbschema
self.cursor.execute('select schema from schema')
psycopg2.errors.UndefinedTable: relation "schema" does not exist
LINE 1: select schema from schema
^
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "/home/user/Projects/www/roundup_dev/env/bin/roundup-admin", line
8, in <module>
sys.exit(run())
File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/scripts/roundup_admin.py",
line 50, in run
sys.exit(tool.main())
File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/admin.py",
line 2230, in main
ret3D self.run_command(args)
File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/admin.py",
line 2114, in run_command
self.db3D tracker.open(self.name)
File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/instance.py",
line 111, in open
'db': backend.Database(self.config, name)
File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/rdbms_common.py",
line 227, in __init__
self.open_connection()
File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
line 227, in open_connection
self.fix_version_3_tables()
File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
line 335, in fix_version_3_tables
self.sql('''CREATE INDEX words_both_idx ON public.__words
File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/rdbms_common.py",
line 266, in sql
cursor.execute(sql)
psycopg2.errors.UndefinedTable: relation "public.__words" does not exist
If I only use the 'public' schema for my database, the migration command
works well.
[elided signature] |