I have 3.8.2 with all modules, and have upgraded to 3.8.3.
All works except the current site details and settings. I created a new site, and everything works on it as well. When selecting the site in the system prior to the upgrade eg, looking for the details or setting screen, I get the following error:
MySource Error
[ASSERT EXCEPTION] DB Error: unknown error
SELECT ct.treeid as our_treeid, cl.minorid, pt.treeid as parent_treeid, a.assetid, a.name FROM sq_ast_lnk cl INNER JOIN sq_ast_lnk_tree ct ON cl.linkid = ct.linkid, sq_ast_lnk pl INNER JOIN sq_ast_lnk_tree pt ON pl.linkid = pt.linkid INNER JOIN sq_ast a ON a.assetid = pl.minorid WHERE cl.minorid IN (SELECT l.majorid FROM sq_ast_lnk l WHERE l.minorid = '40') AND ct.treeid LIKE pt.treeid || '%' AND pt.treeid <= ct.treeid AND pt.treeid IN (SELECT * FROM sq_get_lineage_treeids('40', 4)) ORDER BY cl.linkid, ct.treeid, pt.treeid [nativecode=ERROR: function sq_get_lineage_treeids("unknown", integer) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts.] (LINE 2773 IN [SYSTEM_ROOT]/core/include/asset_manager.inc) [SYS0270]
I take that back, I get this error when appling a design:
[ASSERT EXCEPTION] DB Error: unknown error
SELECT ct.treeid as our_treeid, cl.minorid, pt.treeid as parent_treeid, a.assetid, a.name FROM sq_ast_lnk cl INNER JOIN sq_ast_lnk_tree ct ON cl.linkid = ct.linkid, sq_ast_lnk pl INNER JOIN sq_ast_lnk_tree pt ON pl.linkid = pt.linkid INNER JOIN sq_ast a ON a.assetid = pl.minorid WHERE cl.minorid IN (SELECT l.majorid FROM sq_ast_lnk l WHERE l.minorid = '41') AND ct.treeid LIKE pt.treeid || '%' AND pt.treeid <= ct.treeid AND pt.treeid IN (SELECT * FROM sq_get_lineage_treeids('41', 4)) ORDER BY cl.linkid, ct.treeid, pt.treeid [nativecode=ERROR: function sq_get_lineage_treeids("unknown", integer) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts.] (LINE 2773 IN [SYSTEM_ROOT]/core/include/asset_manager.inc) [SYS0270]
So it seems to be the designs are at fault, when doing an upgrade are we supposed to remove all the designs and reload them?
Thanks for any advice to repair this site.
Mickey
Looks like you need to run step_02.php to create the new PostgreSQL functions.
This upgrade guide should get you sorted: Upgrading MySource Matrix version 3.8.2 to 3.8.3.
I ran the upgrade as the directions said. Now get this error:
MySource Error
[ASSERT EXCEPTION] DB Error: unknown error
(SELECT DISTINCT(ll.majorid), ll.type_code, ll.treeid FROM ( SELECT l.majorid, t.treeid, a.type_code, a.name FROM sq_ast_lnk_tree t INNER JOIN sq_ast_lnk l ON t.linkid = l.linkid INNER JOIN sq_ast a ON l.majorid = a.assetid WHERE t.treeid IN ( SELECT * FROM sq_get_parent_treeids('12', 4) ) AND a.type_code IN ( SELECT type_code FROM sq_ast_typ_inhd WHERE inhd_type_code IN ('user', 'user_group') )) ll UNION ALL SELECT sl.majorid, null, null as treeid FROM sq_shdw_ast_lnk sl WHERE sl.minorid = '12' )ORDER BY treeid [nativecode=ERROR: function sq_get_parent_treeids(character varying, integer, "unknown") does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. CONTEXT: PL/pgSQL function "sq_get_parent_treeids" line 6 at assignment] (LINE 2915 IN [SYSTEM_ROOT]/core/include/asset_manager.inc) [SYS0270]
Nothing works either from the _admin side or the public side.
Still looks like your sq_get_parents_treeids() PostgreSQL function is invalid.
You need to go into psql, and run:
db=# DROP FUNCTION sq_get_parent_treeids(VARCHAR, INT, TIMESTAMP);
You should get a "DROP FUNCTION" result. If you get an error, please post it here.
Then, run step_02.php again to recreate the function.
Here is the error:
Failed to execute SQL : SQL DROP FUNCTION sq_get_parent_treeids(VARCHAR, INT, TIMESTAMP); failed : ERROR: function sq_get_parent_treeids(character varying, integer, timestamp without time zone) does not exist
What happens if you run step_02.php?
[root@aetn matrix]# php install/step_02.php /home/websites/matrix
----------------------------------
<?php
define('SQ_CONF_DB_DSN', 'pgsql://matrix@unix()/mysource_matrix');
define('SQ_CONF_DB2_DSN', 'pgsql://matrix@unix()/mysource_matrix');
define('SQ_CONF_DB3_DSN', 'pgsql://matrix_secondary@unix()/mysource_matrix');
define('SQ_CONF_PEAR_PATH', SQ_SYSTEM_ROOT.'/php_includes');
$inc_dir = ini_get('include_path');
$inc_dir = (substr($inc_dir, 0, 2) == '.:') ? '.:'.SQ_CONF_PEAR_PATH.':'.substr($inc_dir, 2) : SQ_CONF_PEAR_PATH.':'.$inc_dir;
ini_set('include_path', $inc_dir);
define('SQ_CONF_BACKEND_SUFFIX', '_admin');
define('SQ_CONF_LIMBO_SUFFIX', '_edit');
define('SQ_CONF_NOCACHE_SUFFIX', 'nocache');
define('SQ_CONF_ASSET_TREE_BASE', 64);
define('SQ_CONF_ASSET_TREE_SIZE', 4);
define('SQ_CONF_ASSET_CACHE_SIZE_WEB', -1);
define('SQ_CONF_ASSET_CACHE_SIZE_CLI', -1);
define('SQ_CONF_ROLLBACK_ENABLED', '0');
define('SQ_CONF_DEBUG', '0');
define('SQ_CONF_SYSTEM_NAME', 'AETN');
define('SQ_CONF_SYSTEM_OWNER', 'AETN');
define('SQ_CONF_SYSTEM_ROOT_URLS', '192.168.61.132');
define('SQ_CONF_STATIC_ROOT_URL', '');
define('SQ_CONF_WEB_PATH_SEPARATOR', '');
define('SQ_CONF_STATIC_ROOT_HTTP', '1');
define('SQ_CONF_STATIC_ROOT_HTTPS', '0');
define('SQ_CONF_DEFAULT_EMAIL', 'yesItookthisout@mymail.org');
define('SQ_CONF_TECH_EMAIL', 'yesItookthisout@mymail.org');
define('SQ_CONF_MAX_LOGIN_ATTEMPTS', '3');
define('SQ_CONF_REFRESH_INTERVAL', 120);
define('SQ_CONF_LOCK_LENGTH', 600);
define('SQ_CONF_ASSET_MAP_ASSET_LIMIT', '50');
define('SQ_CONF_SEND_CACHEABLE_HEADER', '1');
define('SQ_CONF_SEND_LAST_MODIFIED_HEADER', '0');
define('SQ_CONF_SEND_NOT_MODIFIED_HEADER', '0');
define('SQ_CONF_ERRORS_HIDE_FRONTEND', '0');
define('SQ_CONF_DEFAULT_FRONTEND_LANGUAGE', 'en');
define('SQ_CONF_DEFAULT_BACKEND_LOCALE', 'en_AU');
define('SQ_CONF_DEFAULT_CHARACTER_SET', 'iso-8859-1');
define('SQ_CONF_ALLOW_IP_CHANGE', '1');
define('SQ_CONF_ALLOW_HTTP_LOGIN', '0');
define('SQ_CONF_ACCEPT_HTTP_CREDS', '0');
define('SQ_CONF_COMMIT_BUTTON_TEXT', 'Commit');
define('SQ_CONF_LOG_EXTENSION', '.log');
define('SQ_CONF_LOG_FILE_SYSTEM', 'system');
define('SQ_CONF_LOG_FILE_ERROR', 'error');
ini_set('error_log', SQ_LOG_PATH.'/'.SQ_CONF_LOG_FILE_ERROR.SQ_CONF_LOG_EXTENSION);
define('SQ_CONF_LOG_ERRORS', '1');
ini_set('log_errors', '1');
define('SQ_CONF_SESSION_GC_MAXLIFETIME', 604800);
ini_set('session.gc_maxlifetime', 604800);
define('SQ_CONF_EDITING_TIME', 3600);
?>
----------------------------------
------------------------------------
PGSQL STORED FUNCTIONS CREATED
sq_grant_access Function
sq_set_rollback_timestamp(TIMESTAMP) Function
sq_set_rollback_timestamp Function
sq_get_rollback_timestamp Function
sq_get_lineage_from_url Function
sq_get_parent_treeids(VARCHAR, INT, TIMESTAMP) Function
sq_get_parent_treeids(VARCHAR, INT) Function
sq_rb_get_parent_treeids(VARCHAR, INT) Function
sq_get_lineage_treeids(VARCHAR, INT, TIMESTAMP) Function
sq_get_lineage_treeids(VARCHAR, INT) Function
sq_rb_get_lineage_treeids(VARCHAR, INT) Function
atoi Function
asset_link_treeid_convert Function
------------------------------------
------------------------------------
TABLE CREATION COMPLETE
New Tables:
sq_rb_ast_role
------------------------------------
------------------------------------
INDEX CREATION COMPLETE
No Indexes added
------------------------------------
------------------------------------
SEQUENCE CREATION COMPLETE
------------------------------------
------------------------------------
TABLE CREATION COMPLETE
No new tables added
------------------------------------
------------------------------------
INDEX CREATION COMPLETE
No Indexes added
------------------------------------
------------------------------------
SEQUENCE CREATION COMPLETE
------------------------------------
------------------------------------
TABLE CREATION COMPLETE
No new tables added
------------------------------------
------------------------------------
INDEX CREATION COMPLETE
No Indexes added
------------------------------------
------------------------------------
SEQUENCE CREATION COMPLETE
------------------------------------
------------------------------------
TABLE CREATION COMPLETE
No new tables added
------------------------------------
------------------------------------
INDEX CREATION COMPLETE
No Indexes added
------------------------------------
------------------------------------
SEQUENCE CREATION COMPLETE
------------------------------------
------------------------------------
PGSQL STORED VIEWS CREATED
sq_vw_ast_lnk_minor View
sq_vw_ast_lnk_major View
sq_vw_ast_lnk_minor Rollback View
sq_vw_ast_lnk_major Rollback View
sq_vw_ast_role View
sq_vw_ast_perm View
sq_vw_ast_role Rollback View
sq_vw_ast_perm Rollback View
------------------------------------
±----------------------------------------------------------------------------------------------------------------------------------------------------+
| MySource Error |
|-----------------------------------------------------------------------------------------------------------------------------------------------------|
| [ASSERT EXCEPTION] DB Error: unknown error |
| SELECT sq_grant_access('matrix') [nativecode=ERROR: permission denied for relation users_tab_id_user_seq |
| CONTEXT: PL/pgSQL function "sq_grant_access" line 9 at execute statement] (LINE 553 IN [SYSTEM_ROOT]/core/lib/db_install/db_install.inc) [SYS0270] |
±----------------------------------------------------------------------------------------------------------------------------------------------------+
[root@aetn matrix]#
Just FYI - Came across the issue this morning. Dropping the function should work, those instructions appear ok. Perhaps your matrix user doesn't have permission. Have you changed your users between upgrades?
No I haven't I've only one user and that is root, so I haven't changed that at all.
did you "su matrix" from being root before running step_02.php?
No and I haven't created a matrix user either but to show you:
su: user matrix does not exist
Has to be something else.
Can anyone else figure this one out?
The sequence users_tab_id_user_seq doesnt look like one of ours. Do you have any other apps using the same database?
Also, can you go into psql and do:
\d
to show me your list of relations and their owner.
No other apps use this database. And in fact MSM is the only thing this box is used for period.
Here are the results to the /d command.
mysource_matrix-# \d
List of relations
Schema | Name | Type | Owner
--------±-------------------------±---------±---------
public | sq_ast | table | matrix
public | sq_ast_attr | table | matrix
public | sq_ast_attr_seq | sequence | matrix
public | sq_ast_attr_uniq_val | table | matrix
public | sq_ast_attr_val | table | matrix
public | sq_ast_edit_access | table | matrix
public | sq_ast_lnk | table | matrix
public | sq_ast_lnk_seq | sequence | matrix
public | sq_ast_lnk_tree | table | matrix
public | sq_ast_lookup | table | matrix
public | sq_ast_lookup_remap | table | matrix
public | sq_ast_lookup_value | table | matrix
public | sq_ast_mdata | table | matrix
public | sq_ast_mdata_dflt_val | table | matrix
public | sq_ast_mdata_val | table | matrix
public | sq_ast_path | table | matrix
public | sq_ast_perm | table | matrix
public | sq_ast_role | table | matrix
public | sq_ast_seq | sequence | matrix
public | sq_ast_typ | table | matrix
public | sq_ast_typ_inhd | table | matrix
public | sq_ast_url | table | matrix
public | sq_ast_url_seq | sequence | matrix
public | sq_ast_wflow | table | matrix
public | sq_bmail_queue | table | matrix
public | sq_cache | table | matrix
public | sq_cal_date_val | table | matrix
public | sq_file_vers_file | table | matrix
public | sq_file_vers_file_seq | sequence | matrix
public | sq_file_vers_history | table | matrix
public | sq_file_vers_lock | table | matrix
public | sq_hipo_job | table | matrix
public | sq_internal_msg | table | matrix
public | sq_internal_msg_seq | sequence | matrix
public | sq_lock | table | matrix
public | sq_patches | table | matrix
public | sq_pkg | table | matrix
public | sq_rb_ast | table | matrix
public | sq_rb_ast_attr_uniq_val | table | matrix
public | sq_rb_ast_attr_val | table | matrix
public | sq_rb_ast_edit_access | table | matrix
public | sq_rb_ast_lnk | table | matrix
public | sq_rb_ast_lnk_tree | table | matrix
public | sq_rb_ast_lookup | table | matrix
public | sq_rb_ast_lookup_remap | table | matrix
public | sq_rb_ast_lookup_value | table | matrix
public | sq_rb_ast_mdata | table | matrix
public | sq_rb_ast_mdata_dflt_val | table | matrix
public | sq_rb_ast_mdata_val | table | matrix
public | sq_rb_ast_path | table | matrix
public | sq_rb_ast_perm | table | matrix
public | sq_rb_ast_url | table | matrix
public | sq_rb_ast_wflow | table | matrix
public | sq_rb_cal_date_val | table | matrix
public | sq_rb_shdw_ast_lnk | table | matrix
public | sq_rb_trig | table | matrix
public | sq_rb_vw_ast_lnk_major | view | matrix
public | sq_rb_vw_ast_lnk_minor | view | matrix
public | sq_sch_idx | table | matrix
public | sq_shdw_ast_lnk | table | matrix
public | sq_thes_lnk | table | matrix
public | sq_thes_lnk_seq | sequence | matrix
public | sq_thes_lnk_tree | table | matrix
public | sq_thes_term | table | matrix
public | sq_thes_term_seq | sequence | matrix
public | sq_trig | table | matrix
public | sq_trig_hash | table | matrix
public | sq_trig_id_seq | sequence | matrix
public | sq_vw_ast_lnk_major | view | matrix
public | sq_vw_ast_lnk_minor | view | matrix
public | sq_vw_ast_perm | view | matrix
public | sq_vw_ast_role | view | matrix
public | users_tab | table | postgres
public | users_tab_id_user_seq | sequence | postgres
(74 rows)
There are two items in there that were not created by Matrix:
users_tab (table)
users_tab_id_user_seq (sequence)
Do the following at the psql prompt:
mysource_matrix-# DROP SEQUENCE users_tab_id_user_seq;
mysource_matrix-# DROP TABLE users_tab;They must have come from another piece of software.
Works fine now update is great.
Thanks AVI again you are the godsend
Avi,
I now get the following error when I commit changes to a page as long as indexing is on in the search manager.
[ASSERT EXCEPTION] DB Error: unknown error
INSERT INTO sq_sch_idx ( value, type_code, type, assetid, component, score ) VALUES ( '�6-12', 'page_standard', 'text', 736, 'contents', 1 ) [nativecode=ERROR: invalid byte sequence for encoding "UTF8": 0xa0] (LINE 697 IN [SYSTEM_ROOT]/packages/search/search_manager/search_manager.inc) [SYS0270]
Your database is using the wrong enconding method.
The easiest fix is to backup the database:
# pg_dump -Ft -v -U postgres -d database_name > database_name.tar
Drop the database:
# dropdb -U postgres database_name
Re-create the database with the right encoding:
# createdb -U postgres -O primary_user_name -E SQL_ASCII database_name
And then restore the data:
# pg_restore -Ft -v -U postgres -d database_name database_name.tar
Hope that makes sense!
[quote]Your database is using the wrong enconding method.
The easiest fix is to backup the database:
# pg_dump -Ft -v -U postgres -d database_name > database_name.tar
Drop the database:
# dropdb -U postgres database_name
Re-create the database with the right encoding:
# createdb -U postgres -O primary_user_name -E SQL_ASCII database_name
And then restore the data:
# pg_restore -Ft -v -U postgres -d database_name database_name.tar
Hope that makes sense!
[right][post="11370"]<{POST_SNAPBACK}>[/post][/right][/quote]
That fixed it. How do you imagine that our database was not using the correct encoding? We installed the default process with all modules.
Mickey
[quote]That fixed it. How do you imagine that our database was not using the correct encoding? We installed the default process with all modules.
[right][post=“11371”]<{POST_SNAPBACK}>[/post][/right][/quote]
PostgreSQL pulls its default enconding from the OS. Some fresh installations of PostgreSQL set the default enconding to UTF-8, while others set it to SQL_ASCII. Obviously your default is UTF-8.
I’ll check the installation instructions on the website to make sure we tell people to install the database with the correct encoding for Matrix.