Menu
Trying to create functions on MySQL that runs on Google CloudSQL. These functions worked fine on another server (VPS- Godaddy).
Official documentation says that User Defined Functions are not supported, but I am trying to create a regular stored function and not a UDF.
Lookup Error - MySQL Database Error: 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)
Logged in using Toad
Username used TAdmin
Sample Function:
DELIMITER $$
DROP FUNCTION IF EXISTS `func_getEQId`$$
CREATE DEFINER=`TAdmin`@`%` FUNCTION `func_getEQId` (`pTopicId` INT(11))
RETURNS BIGINT(20)
READS SQL DATA DETERMINISTIC
BEGIN
DECLARE vQId bigint (20);
SELECT QId INTO vQId FROM quests WHERE...
RETURN vQId;
END$$
Don't want to disable binary logging
498
To add super privileges to MySQL database, the following is the syntax. mysql> GRANT SUPER ON *. * TO user@'localhost' IDENTIFIED BY 'passwordName'; After executing the above query, do not forget to end it with the following command.
The error arises when you try to import the function or trigger containing dangerous statements that make changes in the database. The error arises only if the binary logging option, which is required for the replication, is turned on for the MySQL server.
SUPER can be used to terminate other sessions or change how the server operates. Privileges granted for the mysql system database itself can be used to change passwords and other access privilege information: Passwords are stored encrypted, so a malicious user cannot simply read them to know the plain text password.
The binary log contains “events” that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows), unless row-based logging is used.
If using Cloud SQL, enable the log_bin_trust_function_creators by running:
gcloud sql instances patch [INSTANCE_NAME] --database-flags log_bin_trust_function_creators=on
More information in the Cloud SQL Documentation.
79
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
