Problems & solutions for server installation

[Martin Wunderlich]

Hi all,

I just tried to install the TinyTM server on a clean installation of PostgreSQL, with no previous installation of PO and ran into a number of problems. In case anyone else is in the same situation, I thought I'd summarise the problems and my solutions/workarounds. FYI, this was on Windows XP.

Cheers,

Martin

=======================================================================================================
Problem 1:  ERROR: null value in column "salt" violates not-null constraint
-----------------------
Root cause: gen_salt not working
-----------------------
Error msg:
-----------------------
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "tinytm_users_pk" for table "tinytm_users"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "tinytm_users_username_un" for table "tinytm_users"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "tinytm_users_email_un" for table "tinytm_users"

ERROR: null value in column "salt" violates not-null constraint
SQL state: 23502
-----------------------
Workaround:   Remove "not null" constraint from tinytm_users_email_un in
=====================================================================================================

Problem 2: language "plpgsql" not loaded
-----------------------
Root cause: The language has not been loaded.
-----------------------
Error msg:
-----------------------
ERROR: language "plpgsql" does not exist
SQL state: 42704
Hint: Use CREATE LANGUAGE to load the language into the database.
-----------------------
Solution: Execute SQl statement "CREATE LANGUAGE plpgsql" before running the "tinytm-create-procedures.sql" script
=====================================================================================================

Problem 3: Current PostgreSQL DB user is not in Tinytm user table
-----------------------
Error msg:
-----------------------
ERROR: tinytm_current_user_id: Could not determine user_id for current user "admin".
SQL state: P0001
Context: PL/pgSQL function "tinytm_new_segment" line 56 at assignment
PL/pgSQL function "tinytm_new_segment" line 9 at assignment
SQL statement "SELECT  tinytm_new_segment('en', 'de', 'This is a book', 'Dies ist ein Buch')"
PL/pgSQL function "inline_0" line 4 at perform
-----------------------
Solution: Run insert SQL for current user (i.e. the user that you're logged in with in PostgreSQL) to create a TinyTM user with the username of the PostgreSQL user. Alternatively, create a DB user for the TinyTM default user "sysadmin@tigerpond.com" before starting the server installation.
=====================================================================================================

Problem 4: relation "acs_magic_objects" does not exist when running tinytm-create-user.sql
-----------------------
Error msg:
-----------------------
ERROR: relation "acs_magic_objects" does not exist
SQL state: 42P01
-----------------------
Workaround: Remove the line "GRANT SELECT ON acs_magic_objects TO bbigboss;" from tinytm-create-user.sql
=====================================================================================================

Problem 5: When using the Word client, it complains that Macros are disabled.
-----------------------
Solutions: go to Tools>Macro>Security and set to "low" or "medium". Re-open the doc.
=====================================================================================================

Problem 6: Connection problem with the Word client when using a port other than the default port.
-----------------------
Workaround: Modify the Word macro and include the port in the connect string, e.g.:
ConnectString = "Provider=" + connProvider + ";DRIVER=" + connDriver + ";SERVER=" + connServer + ";PORT=" + connPort + ";DATABASE=" + connDatabase + ";UID=" + connUser + ";PWD=" + connPassword
-----------------------
Solution: I have sent an updated Word client to Frank where you can set the port for the server in the connection settings dialogue.
=====================================================================================================

[Yan Yu]

This is how I fixed the following error,

ERROR: null value in column "salt" violates not-null constraint
SQL state: 23502

On tinytm-create-standalone.sql,

- Before (missing a value in the insert statement)

insert into tinytm_users values (
    nextval('tinytm_users_seq'),
    'sysadmin@tigerpond.com',
    'System',
    'Administrator',
    '',
    sha1(gen_salt('md5'))
);

- After (added the user name test)

insert into tinytm_users values (
    nextval('tinytm_users_seq'),
    'test',  <-- added to fix the error
    'sysadmin@tigerpond.com',
    'System',
    'Administrator',
    '',
    sha1(gen_salt('md5'))
);

I think the problem is that the insert statement is missing a value.  The tinytm_users table has 7 columns.  The original insert statement only has 6 values.

Use the PostgreSQL account name you logged in as the added username (in this case 'test').  Otherwise, you will get an error when you run tinytm-create-procedures.sql.

RROR: tinytm_current_user_id: Could not determine user_id for current user "admin".
SQL state: P0001
Context: PL/pgSQL function "tinytm_new_segment" line 56 at assignment
PL/pgSQL function "tinytm_new_segment" line 9 at assignment
SQL statement "SELECT  tinytm_new_segment('en', 'de', 'This is a book', 'Dies ist ein Buch')"
PL/pgSQL function "inline_0" line 4 at PERFORM

Yan

[Anonymous]

LOOK @ THE SCEAMA!!

tinytm-create-standalone.sql - (fix user query) the missing username (2nd column) must be the db user used to admin the TMX db
tinytm-create-procedures.sql - must add language first plpgsql (to the postgres DB)

tinytm-create-user.sql REMOVE -GRANT SELECT ON acs_magic_objects TO ; if you don't use OPENACS

[karlmc15]

• Create a new default user. The funny sha1(gen_salt('md5')) creates a "salt" (=random data
• to mix with password) for cryptographic reasons. Then set the user's password. The password
• is "system". However, we don't want to store the plain-text password in the DB, so we only
• store a cryptographic hash value.
  INSERT INTO "tinytm_users" VALUES ('1', 'postgres', 'admin@localhost', 'aaa', 'cc', '3DB0CDB24602C50E75CAA3B9CE705B9D653D4BF2', 'B7AF7DC6EA42B79D1CABB50B1123D48B72BC7524');
  -user:postgres  pass: System hoac system
  /*
  //hoac lam bang tay roi thuc hien 2 query sau:
  userID=1; username= postgres, email..bat ky, pass=System:
  2 query:
  update tinytm_users set salt = sha1(gen_salt('md5'))
  where email = 'sysadmin@tigerpond.com';
  update tinytm_users set password = sha1('system' || salt)
  where email = 'sysadmin@tigerpond.com';
  */

[karlmc15]

ploblem2:

C:\Program Files\PostgreSQL\8.3\bin>createlang -U postgres -d tinyTM plpgsql

C:\Program Files\PostgreSQL\8.3\bin>