PubSub3 mysql schema error in master-slave setup (#20)

Activities

wojciech.kapcia@tigase.net opened 1 decade ago

Due Date
2014-08-28

While trying to load new PubSub3 schema to an mysql that has binary logging enabled (i.e. master-slave setup) there is an error

$ mysql -u tigase -p tigase < database/mysql-pubsub-schema-3.0.0.sql
ERROR 1418 (HY000) at line 227: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

Andrzej Wójcik (Tigase) commented 1 decade ago

Applied in changeset tigase-pubsub|commit:a74b145a3a639b5f2ded75998ef3e0072a25a462.

Andrzej Wójcik (Tigase) commented 1 decade ago

I think I fixed this issue by adding DETERMINISTIC to function definition but I'm not sure if it is 100% correct.

wojciech.kapcia@tigase.net commented 1 decade ago

(reopening)

mysql> source database/mysql-pubsub-schema-3.0.0.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.01 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.01 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.01 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 2 warnings (0.02 sec)

Query OK, 0 rows affected, 2 warnings (0.01 sec)

Query OK, 0 rows affected, 2 warnings (0.01 sec)

Query OK, 0 rows affected, 2 warnings (0.01 sec)

Query OK, 0 rows affected, 2 warnings (0.00 sec)

Query OK, 0 rows affected, 2 warnings (0.00 sec)

ERROR 1419 (HY000): 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)
ERROR 1419 (HY000): 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)
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Andrzej Wójcik (Tigase) commented 1 decade ago

I have found an article about this issue and it looks like this is related to security permissions on MySQL level, see http://www.logikdev.com/tag/log_bin_trust_function_creators/

So we have two solutions here, set variable in mysql.ini file or set variable in schema file. I do not know I setting variable would be enough and if it would be persistent. Maybe we should add line setting variable to schema file, but I'm not sure about this as it may have security implications.

wojciech.kapcia@tigase.net commented 1 decade ago

Given that we already require superuser privileges I'd go with requirement of loading PubSub3 schema using such account.

Type	Bug
Priority	Major
Assignee	wojciech.kapcia@tigase.net
RedmineID	1762
Spent time	1h 30m

Issue Votes (0)

Watchers (0)

Reference

tigase/_server/tigase-pubsub#20