DB Error (Postgres)

(267 Scott Barnett) #1

I had a working Matrix installation connecting to MySQL. I read in your documentation that MySQL is going to be dropped which is fair enough, so I thought I'd start over with Postgres. I wiped all the folders and started over extracting from the tarballs again.


I'm having an error trying to connect to Postgres using MySource. A manual pg_connect() with the correct host/username/password/database works fine.



DB Error: connect failed when connecting to database.

Could not get database handler for dsn pgsql://username:password@postgres.domain.local/MySource





pg_hba.conf

host all all host.ip.address 255.255.255.255 md5

(267 Scott Barnett) #2

erk, sorry I copied and pasted the wrong error


DB Error: unknown error

Could not access database MySource





The postgres log file contains:



ERROR: cannot drop sequence testtable_id_seq because table testtable column id requires it

HINT: You may drop table testtable column id instead.



I ticked the box to empty the database. I had a test table in there to test the connection. I think this must be a bug in the script that empties the database.

(267 Scott Barnett) #3

More postgres error logs:


ERROR: permission denied: "pg_shadow" is a system catalog

ERROR: permission denied: "pg_attrdef" is a system catalog

ERROR: permission denied: "pg_constraint" is a system catalog

ERROR: permission denied: "pg_database" is a system catalog

ERROR: permission denied: "pg_description" is a system catalog

ERROR: permission denied: "pg_group" is a system catalog

ERROR: permission denied: "pg_proc" is a system catalog

ERROR: permission denied: "pg_rewrite" is a system catalog

ERROR: permission denied: "pg_statistic" is a system catalog

ERROR: permission denied: "pg_type" is a system catalog

ERROR: permission denied: "pg_attribute" is a system catalog

ERROR: permission denied: "pg_class" is a system catalog

ERROR: permission denied: "pg_inherits" is a system catalog

ERROR: permission denied: "pg_index" is a system catalog

ERROR: permission denied: "pg_operator" is a system catalog

ERROR: permission denied: "pg_opclass" is a system catalog

ERROR: permission denied: "pg_am" is a system catalog

ERROR: permission denied: "pg_amop" is a system catalog

ERROR: permission denied: "pg_amproc" is a system catalog

ERROR: permission denied: "pg_language" is a system catalog

ERROR: permission denied: "pg_largeobject" is a system catalog

ERROR: permission denied: "pg_aggregate" is a system catalog

ERROR: permission denied: "pg_trigger" is a system catalog

ERROR: permission denied: "pg_listener" is a system catalog

ERROR: permission denied: "pg_cast" is a system catalog

ERROR: permission denied: "pg_namespace" is a system catalog

ERROR: permission denied: "pg_conversion" is a system catalog

ERROR: permission denied: "pg_depend" is a system catalog



I deleted the database, created a new one, and unticked the "delete all tables" box. It works until a couple of steps later when it throws this error.



DB Error: unknown error

CREATE OR REPLACE FUNCTION sq_grant_access(character varying) RETURNS TEXT AS ' DECLARE user_name ALIAS FOR $1; table RECORD; tablename TEXT; BEGIN FOR table IN SELECT c.relname AS name FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN (''r'',''v'',''S'','''') AND n.nspname NOT IN (''pg_catalog'', ''pg_toast'') AND pg_catalog.pg_table_is_visible(c.oid) LOOP tablename=table.name; RAISE NOTICE ''tablename is %'', tablename; EXECUTE ''GRANT ALL ON '' || quote_ident(tablename) || '' TO '' || quote_ident(user_name::text); END LOOP; RETURN ''access granted.''; END; ' LANGUAGE plpgsql; [nativecode=ERROR: language "plpgsql" does not exist HINT: You need to use "createlang" to load the language into the database.]

(267 Scott Barnett) #4

I ran
createlang plpgsql --dbname=MySource --username=postgres --password



and then went back to the installation. It now throws:



[ASSERT EXCEPTION] DB Error: no such table

SELECT type_code, version, name, instantiable, allowed_access, parent_type, dir, customisation FROM sq_asset_type [nativecode=ERROR: relation "sq_asset_type" does not exist] (LINE 90 IN [SYSTEM_ROOT]/core/include/asset_manager.inc)



Ok, now I'm stumped :slight_smile:

(Avi Miller) #5

You need to restart the installation process completely. The first time you ran it, it created some of the tables and cache files, so the second time you ran it, it thought the first run was successful.


You need to drop the database, recreate it, add the PLpgSQL language and then run the installer. I would also delete the existing MySource Matrix folder and re-extract it from the archive.

(267 Scott Barnett) #6

I did an rm -Rf on the mysource folder and the installer's folder, then unpacked the tarballs again (started clean).


I'd created the MySource db and run createlang in advance.



Provided I didn't tick the "delete all tables" tickbox, it installs fine.

(267 Scott Barnett) #7

Yeah thanks AVI :slight_smile:


It wasn't creating any tables in the database, the db was totally clean. Once I pruned the folders and started over with them, it came good.



You might want to take a look at the script that deletes the tables. It was throwing an error because it couldn't handle the sequence on the test tables primary key.



Hope this helps :slight_smile:

(Marcus Nyeholt) #8

Good point, at the moment tables are read from the db then deleted, instead of going through the tables.xml files to be deleted.