# Divers
### Problèmes de démarrage de POSTGRESQL
```
chown -R postgres:postgres /var/lib/postgresql
chown -R postgres:postgres /etc/postgresql
chown -R postgres:postgres /var/log/postgresql
```
### Problèmes de LOGO
voir les infos dans les logs du postgresql
```
select id,content_type,lo_id from stored_files;
ALTER LARGE OBJECT 131963 OWNER to cyclos;
```
### Corriger les droits POSTGRESQL
```
for tbl in `psql -qAt -c "SELECT proname FROM pg_proc WHERE pronamespace::regnamespace::text = 'public';" cyclos4` ; do psql -c "alter function \"$tbl\" owner to cyclos" cyclos4 ; done
```
```
for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" cyclos4` ; do psql -c "alter table \"$tbl\" owner to cyclos" cyclos4 ; done
```
```
for tbl in `psql -qAt -c "select sequence_name from information_schema.sequences where sequence_schema = 'public';" cyclos4` ; do psql -c "alter sequence \"$tbl\" owner to cyclos" cyclos4 ; done
```
```
for tbl in `psql -qAt -c "select table_name from information_schema.views where table_schema = 'public';" cyclos4` ; do psql -c "alter view \"$tbl\" owner to cyclos" cyclos4 ; done
```
```
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO cyclos;
GRANT ALL PRIVILEGES ON database cyclos4 TO cyclos;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO cyclos;
ALTER DATABASE cyclos4 OWNER to cyclos;
```
### Pb de réinitialisation de la license cyclos :
```
update application set license_key = null, license_data = null, local_license_data = null;
update password_types set avoid_obvious = false where id = 1;
update configurations set required_address =false;
```
### Restore la base SQL cyclos :
Copier le dump sql dans le /tmp
```
su - postgres
psql
cyclos4# drop database cyclos4;
cyclos4# create database cyclos4;
cyclos4# exit
psql -d cyclos4 -f /tmp/cyclos4-202101280300.sql
psql -d cyclos4
cyclos4#delete from database_lock;
```
### Problème de démarrage de Cyclos suite à un arrêt inoppiné :
```
[2021-12-28 17:32:13] [info] ******************************************************************************
[2021-12-28 17:32:13] [info] It seems there is another Cyclos instance connected to this database.
[2021-12-28 17:32:13] [info] If you are sure that is not the case (i.e., the server had crashed),
[2021-12-28 17:32:13] [info] run the following command in the database, then restart Cyclos:
[2021-12-28 17:32:13] [info] delete from database_lock;
[2021-12-28 17:32:13] [info] ******************************************************************************
```
### Purger la base du bugs des balances :
```
delete from public.closed_account_balances where date > '2021-09-21' ;
```
### Changer l'url par défaut :
```
cyclos4=# select id,root_url from configurations;
cyclos4=# update configurations set root_url='https://cyclostest.guigeek.org' where id=1;
UPDATE 1
cyclos4=# update configurations set root_url='https://cyclostest.guigeek.org' where id=2;
UPDATE 1
cyclos4=# update configurations set root_url='https://cyclostest.guigeek.org' where id=5;
UPDATE 1
cyclos4=# update configurations set root_url='https://cyclostest.guigeek.org' where id=6;
UPDATE 1
cyclos4=# update configurations set root_url='https://cyclostest.guigeek.org' where id=7;
```
### Purge database :
```
delete from accountbalances where accountid in (select id from accounts where user_id in (select id from users where status ='PURGED'));
delete from accounts where user_id in (select id from users where status ='PURGED');
delete from userstatuslogs where user_id in (select id from users where status ='PURGED');
delete from messagestousers where user_id in (select id from users where status ='PURGED');
delete from adhistorylogs;
delete from notifiedadinterests;
delete from ads where owner_id in (select id from users where status ='PURGED');
delete from users where status = 'PURGED';
delete from entitypropertylogs;
delete from entity_logs;
```
### Changer le mot de passe et le réinitialiser à 1234 :
```
update passwords set value='$2a$10$yM.uw9jC7C1DrRGUhqUc3eSR6FCJH0.HdDt3CJs8YL56iATHcXH7.' where id=1;
```
### Débloquer un utilisateur :
```
update passwords set blocked_until = NULL where id=1;
```
Configuration
System -> Configurations
Expression régulière pour l'identifiant de connexion
^\[\w\.@-_\]*$
Champs d'adresse obligatoire -> 0 Groupes possibles pour l'inscription publique
cyclostest
global
```
-> Thèmes
-> Default new frontend theme
```
florain
```
-> logos
```
le vert #d7da23
le gris #70706f
on peut ajouter les couleurs dominantes des billets:
\#a9dbf6
\#54bcb4
\#e46b9d
\#f38e3c
\#9bc43e
### Comment voir la liste des fonctions PGSQL :
```
SELECT proname,
proowner::regrole
FROM pg_proc
WHERE pronamespace::regnamespace::text = 'public';
```
Functions PGSQL
```
fn_index_custom_field_value | cyclos
fn_create_geography | cyclos
fn_index_ad | cyclos
fn_index_user | cyclos
add_days | cyclos
add_interval | cyclos
fn_index_address | cyclos
balance_sum | cyclos
get_addresses_tsvector | cyclos
insert_tasks_reindex | cyclos
last_login | cyclos
name_hierarchy | cyclos
order_hierarchy | cyclos
reindex_network | cyclos
strip_html_tags | cyclos
to_geography | cyclos
unix_timestamp | cyclos
reserved_amount_diff | cyclos
account_balance | cyclos
balance_diff | cyclos
current_account_status | cyclos
close_account_balances | cyclos
rebuild_closed_account_balances | cyclos
update_account_status | cyclos
account_status_from_closing | cyclos
fix_inconsistent_account_balances | cyclos
```
### Code SQL pour corriger le owner des functions
```
ALTER FUNCTION fn_index_custom_field_value OWNER TO cyclos;
ALTER FUNCTION fn_create_geography OWNER TO cyclos;
ALTER FUNCTION fn_index_ad OWNER TO cyclos;
ALTER FUNCTION fn_index_user OWNER TO cyclos;
ALTER FUNCTION add_days OWNER TO cyclos;
ALTER FUNCTION add_interval OWNER TO cyclos;
ALTER FUNCTION fn_index_address OWNER TO cyclos;
ALTER FUNCTION balance_sum OWNER TO cyclos;
ALTER FUNCTION get_addresses_tsvector OWNER TO cyclos;
ALTER FUNCTION insert_tasks_reindex OWNER TO cyclos;
ALTER FUNCTION last_login OWNER TO cyclos;
ALTER FUNCTION name_hierarchy OWNER TO cyclos;
ALTER FUNCTION order_hierarchy OWNER TO cyclos;
ALTER FUNCTION reindex_network OWNER TO cyclos;
ALTER FUNCTION strip_html_tags OWNER TO cyclos;
ALTER FUNCTION to_geography OWNER TO cyclos;
ALTER FUNCTION unix_timestamp OWNER TO cyclos;
ALTER FUNCTION reserved_amount_diff OWNER TO cyclos;
ALTER FUNCTION account_balance OWNER TO cyclos;
ALTER FUNCTION balance_diff OWNER TO cyclos;
ALTER FUNCTION current_account_status OWNER TO cyclos;
ALTER FUNCTION close_account_balances OWNER TO cyclos;
ALTER FUNCTION rebuild_closed_account_balances OWNER TO cyclos;
ALTER FUNCTION update_account_status OWNER TO cyclos;
ALTER FUNCTION account_status_from_closing OWNER TO cyclos;
ALTER FUNCTION fix_inconsistent_account_balances OWNER TO cyclos;
ALTER FUNCTION last_account_closing OWNER TO cyclos;
ALTER FUNCTION update_balance OWNER TO cyclos;
ALTER FUNCTION close_balances OWNER TO cyclos;
ALTER FUNCTION last_closed_account_status OWNER TO cyclos;
ALTER FUNCTION account_status_from_cache OWNER TO cyclos;
ALTER FUNCTION insert_missing_balances OWNER TO cyclos;
ALTER FUNCTION inconsistent_closed_accounts OWNER TO cyclos;
ALTER FUNCTION last_closed_account_balances OWNER TO cyclos;
ALTER FUNCTION dirty_account_balances_diffs OWNER TO cyclos;
ALTER FUNCTION cached_account_status OWNER TO cyclos;
ALTER FUNCTION account_status_diffs_since_closing OWNER TO cyclos;
ALTER FUNCTION closed_account_status OWNER TO cyclos;
ALTER FUNCTION insert_missing_account_balances OWNER TO cyclos;
ALTER FUNCTION fix_inconsistent_account_balances OWNER TO cyclos;
ALTER FUNCTION fn_index_translation_message OWNER TO cyclos;
ALTER FUNCTION fn_update_last_balance_closing_date OWNER TO cyclos;
ALTER FUNCTION account_status OWNER TO cyclos;
```