setting up mysql db

[Nick Vazirianis]

setting up mysqldb new install

Confluence 7.12.0

Ubuntu 20.10

mysql Ver 8.0.23-0ubuntu0.20.10.1 for Linux on x86_64 ((Ubuntu))

after a while recreating tables it eventually crashes with java web page 

Setup Database Failed

The following error(s) occurred:

• Configuring database failed
• StatementCallback; uncategorized SQLException for SQL [CREATE TRIGGER denormalised_space_trigger_on_update AFTER UPDATE ON SPACES FOR EACH ROW sp: BEGIN DECLARE isServiceDisabled BOOL DEFAULT TRUE; CALL space_procedure_for_denormalised_permissions(isServiceDisabled); IF (isServiceDisabled) THEN LEAVE sp; END IF; IF (NEW.LOWERSPACEKEY = OLD.LOWERSPACEKEY) THEN LEAVE sp; END IF; INSERT INTO DENORMALISED_SPACE_CHANGE_LOG(SPACE_ID) VALUES (NEW.SPACEID); END;]; SQL state [HY000]; error code [1419]; You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable); nested exception is java.sql.SQLException: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

Setting up database instance failed. You can either try it again or or go back to the database selection page:

[Gonchik Tsymzhitov · Accepted solution]

Hi @Nick Vazirianis ,

Welcome to the community. 

I hope you follow the next KB

https://confluence.atlassian.com/doc/database-setup-for-mysql-128747.html

If I were you, I move the old confluence.cfg.xml into confluence.cfg.xml.bk, start from fresh configuration from UI with connecting into existing DB. Note: if you do new install, please, recreate database. 

[Nick Vazirianis]

Thanks @Gonchik Tsymzhitov for your reply I was about to try what you said but then I saw a post somewhere to change the log_bin_trust to on, so I tried that  and it seemed to fix it, later I will put it back to off.

So I just did this 

mysql> SET GLOBAL log_bin_trust_function_creators = 1
-> ;
Query OK, 0 rows affected (0.00 sec)

and to check it took effect

mysql> show variables like '%log_bin_trust_function_creators%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON |
+---------------------------------+-------+
1 row in set (0.00 sec)

[Gonchik Tsymzhitov]

@Nick Vazirianis how that variable related to the setup? do you multi-master RDBMS setup?

https://dba.stackexchange.com/questions/108316/use-of-log-bin-trust-function-creators-in-mysql

[Nick Vazirianis]

I'm not sure why that variable was being used. I don't think I had setup multi-master RDBMS but maybe there was something strange in my mysql config.

[Gonchik Tsymzhitov]

Do you have 1 instance of mysql ? or do you use replication master-slave, master-master ?

[Nick Vazirianis]

I only have just 1 instance of mysql  and no replication