【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 FUNCTION statement to be accepted, at least one of DETERMINISTIC, NOSQL, or READS SQL DATA must 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_creators to 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 the SUPER privilege in addition to the CREATE ROUTINE or ALTER ROUTINE privilege. A setting of0 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, MySQLdoes not enforce these restrictions on stored function creation. This variablealso applies to trigger creation. See Section19.7, “Binary Logging of Stored Programs”.
行在端点配置中禁用了数据库镜像传输功能。主服务器版本信息: Microsoft SQL 应该是要sp2 2005不需要SP2补丁，Sp1照样能用才对 一个是你版本
当启动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
改一下就好了，但也会......余下全文>>