【MySQL案例】ERROR 1418，mysqlerror1418

【MySQL案例】ERROR 1418，mysqlerror1418

1.1.1. ERROR 1418

【環境描述】

mysql5.0.67

【問題描述】

建立預存程序的時候遇到ERROR 1418報錯。

# 建立函數的SQL語句

CREATE FUNCTION `xxx`( num01 int ) RETURNSint(11)

begin

       declare mm  int default 0;

       if  (xx) then

                set mm = num01*num01*10;

       elseif  xxx then

                set mm = 10;  

       else

                case num01

                        when  30 then set mm = 111;

                        when  31 then set mm = 222;

                end case;      

       end if;

       return mm;

end

 

# 報錯資訊

# ERROR 1418 (HY000) at line xxxxx: Thisfunction has none of DETERMINISTIC, NO SQL, or READS SQL DATA in itsdeclaration and binary logging is enabled (you *might* want to use the lesssafe log_bin_trust_function_creators variable)

 

【解決方案】

關閉關閉binary log或者修改參數。

此處採用修改參數的方法：

set globallog_bin_trust_function_creators=1;

 

【報錯原因】

By default, for a CREATE FUNCTIONstatement to be accepted, at least one of DETERMINISTIC, NOSQL, or READS SQL DATAmust be specified explicitly. Otherwise an error occurs:

ERROR1418 (HY000): This function has none of DETERMINISTIC, NO SQL,

or READSSQL DATA in its declaration and binary logging is enabled

(you*might* want to use the less safe log_bin_trust_function_creators

variable)

If you set log_bin_trust_function_creatorsto 1, the requirement that functions be

deterministic or not modify data is dropped.

 

【參考資料】

Command-LineFormat --log-bin-trust-function-creators

Option-FileFormat log-bin-trust-function-creators

SystemVariable Name log_bin_trust_function_creators

VariableScope Global

DynamicVariable Yes

PermittedValues

Type boolean

Default FALSE

 

This variable applieswhen binary logging is enabled. It controls whether stored function creators canbe trusted not to create stored functions that will cause unsafe events to bewritten to the binary log. If set to 0 (the default), users are not permittedto create or alter stored functions unless they have theSUPER privilege in addition to theCREATE ROUTINE orALTER ROUTINE privilege. A setting of0 also enforces the restriction that a function must be declared with theDETERMINISTIC characteristic, or with theREADS SQL DATA orNO SQL characteristic. If the variable is set to 1, MySQLdoes not enforce these restrictions on stored function creation. This variablealso applies to trigger creation. SeeSection19.7, “Binary Logging of Stored Programs”.

SQL server 2005資料庫鏡像串連問題，鏡像資料庫設定到開始鏡像，然後出現網路連接錯誤1418，是什問題？

行在端點配置中禁用了資料庫鏡像傳輸功能。主伺服器版本資訊: Microsoft SQL 應該是要sp2 2005不需要SP2補丁，Sp1照樣能用才對 一個是你版本
 
救命MySQL Error: 2002

當啟動mysql是出現ERROR 2002：的解決方案：
1. cd /usr/bin/
2. sudo ./mysql_install_db
啟動mysql。
#mysql -u root -pERROR 2002 (HY000):Can't connect to local MySQL server隨即上網找尋答案根據大家提供的方法我逐一嘗試
方案1.
1.#ps -A|grep mysql 顯示類似：1829 ? 00:00:00 mysqld_safe
1876 ? 00:00:31 mysqld
2.#kill -9 1829
3.#kill -9 1876
4.#/etc/init.d/mysql restart
5.#mysql -u root -p 他的麻煩解決了,我的還沒解決!
繼續找方案2
先查看 /etc/rc.d/init.d/mysqld status 看看m y s q l 是否已經啟動.
另外看看是不是許可權問題.
------------------------------------------------------------------------------------
[root@localhost beinan]#chown -R mysql:mysql /var/lib/mysql
[root@localhost beinan]# /etc/init.d/mysqld start
啟動 MySQL： [ 確定 ]
[root@localhost lib]# mysqladmin -uroot password '123456'
[root@localhost lib]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.1.11

Type 'help;' or '\h' for help. Type '\c' to clear the buffe他的也解決了,我的麻煩還在繼續,依然繼續尋找
方案3
問題解決了，竟然是max_connections=1000 他說太多了，然後改成500也說多，無奈刪之問題解決了。還是不行
方案4 /var/lib/mysql 所有檔案許可權 改成mysql.mysql 不行不行方案5
摘要：解決不能通過mysql.sock串連MySQL問題 這個問題主要提示是，不能通過 '/tmp/mysql.sock'連到伺服器，而php標準配置正是用過'/tmp/mysql.sock'，但是一些mysql安裝方法將 mysql.sock放在/var/lib/mysql.sock或者其他的什麼地方，你可以通過修改/etc/my.cnf檔案來修正它，開啟檔案，可 以看到如下的東東：
[mysqld]
socket=/var/lib/mysql.sock
改一下就好了，但也會......餘下全文>>