我在亞洲新加坡地區的亞馬遜地區建立了一個中等執行個體.我用主密碼建立了我的主使用者.它與我本地PC上安裝的工作台正常工作/連接配接.什麼時候,我要在該執行個體上建立函數,它會向我顯示以下錯誤
ERROR 1418: 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)
在我的執行個體中,我的變量(log_bin_trust_function_creators)顯示為OFF.現在當我用變量改變時
SET GLOBAL log_bin_trust_function_creators = 1;
它給了我另一個錯誤
Error Code: 1227. Access denied; you need (at least one of) the SUPER privilege(s) for this operation
我不知道如何解決這個錯誤.
有誰可以幫忙???
解決方法:
您可以嘗試更改log_bin_trust_function_creators;但是,當您考慮meaning of that variable時,有一種替代方法似乎更合适：
It controls whether stored function creators can be trusted not to create stored functions that will cause unsafe events to be written to the binary log.
A setting of 0 also enforces the restriction that a function must be declared with the DETERMINISTIC characteristic, or with the READS SQL DATA or NO SQL characteristic. If the variable is set to 1, MySQL does not enforce these restrictions on stored function creation.
所有這個選項都假設您知道自己在做什麼,而不是通過使用CREATE語句中的三個特性中的一個來斷言…但如果您沒有正确聲明該函數,則可能會錯過關于潛在的優化.
misdeclaring a routine might affect results or affect performance
總而言之,這意味着最正确的方法是使用DETERMINISTIC或READS SQL DATA或NO SQL聲明存儲的函數,如果這些沒有正确描述函數的行為,那麼您的函數仍然可能導緻不安全的語句寫入二進制日志,因為these options也是“信任的”：
Assessment of the nature of a routine is based on the “honesty” of the creator: MySQL does not check that a routine declared DETERMINISTIC is free of statements that produce nondeterministic results.
好奇的一邊：精明的觀察者會注意到我從文檔的描述中省略了一些東西：
If set to 0 (the default), users are not permitted to create or alter stored functions unless they have the SUPER privilege in addition to the CREATE ROUTINE or ALTER ROUTINE privilege.
由于沒有人在RDS中獲得SUPER,并且假設這不是官方文檔中的錯誤,這似乎必須是AWS行為的AWS定制.
标簽：mysql,amazon-rds,mysql-5-5
來源： https://codeday.me/bug/20190805/1592443.html