MySQL Dubug

MySQL配置

安装路径：/var/lib/mysql/
配置文件：/etc/mysql/my.cnf
日志文件：/var/log/mysql/error.log
相关命令：/usr/bin/
启动脚步：/etc/init.d/mysql [status|restart|start|stop]，也可以通过service mysql [status|restart|start|stop]

查看MySQL是否启动service mysql status，ps -aux|grep mysql，netstat -tan|grep 3306

修改最大连接数

vim /etc/mysql/my.cnf# 该组[mysqld]下添加，没有该组则自己创建，不在该组下添加会出现：mysql-systemd-start[50016]: my_print_defaults: [ERROR] Found option without preceding group in config file /etc/mysql/my.cnf at line 24![mysqld]max_connections=500/etc/init.d/mysql restart  #重启服务#进入mysqlshow variables like 'max_conn%';  #显示只有214

(2006, ‘MySQL server has gone away’)

原因一：使用MySQLdb，多线程高频率执行插入、更新操作
解决：

# 每次execute都重新连接，conn = MySQLdb.connect()cursor = conn.cursor()cursor.execute(sql_)cursor.close()conn.close()#或try:    conn.ping()  #对任何已经close的conn进行db操作，包括ping()都会出错except Exception, e:    print e    conn = MySQLdb.connect()    ...

原因二：show global status like "%uptime"; #查看mysql运行时间，运行时间过长会引发该问题，其次可以重启MySQL服务.
原因三：show variables like "%timeout"; #其中wait_timeout：mysql链接在无操作xxx秒后自动关闭
原因四：

show variables like "max_allowed_packet";  #sql语句太长，超多max_allowed_packet大小. set max_allowed_packet=1024*1024*16;# 或在配置文件中修改[mysqld]max_allowed_packet=10M

ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’

原因一：php标准配置是用’/tmp/mysql.sock’，而一些mysql安装方法将mysql.sock放在/var/lib/mysql.sock.
解决：修改配置文件，但是不要修改[mysqld]下的socket=/var/lib/mysql.sock，添加[mysql]下的socket=/tmp/mysql.sock. 或ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock
原因二：确认/var/lib/mysql下所有文件的权限都是mysql:mysql，
解决：如果不是chown -R mysql:mysql /var/lib/mysql

Warning: Data truncated for column…

set @rMax=12.34;select @rMax*0.99;  #-->12.216600000000000000000000000000update table replay set rewars = reward + @rMax * 0.99 where state1=@state1;#由decimal的小数位数所引起的精度问题

OperationalError: (1213, ‘Deadlock found when trying to get lock; try restarting transaction’)

死锁了，用show processlist; 然后kill，杀掉挂起的process.

gruop by报错this is incompatible with sql_mode=only_full_group_by

原因：对于GROUP BY聚合操作，如果在SELECT中的列没有在GROUP BY中出现，MySQL不知道返回该列的哪一个值
解决：any_value()，例：select any_value(goods_is), goods_name from tdb_goods group by goods_name;

ERROR:1096

不能更新过滤条件中包含自身的表

update user1 set over='a' where user1.user_name in (select b.user_name from user1 as a inner join user2 as b on a.user_name=b.user_name);  # 报错update user1 as a join (    select b.user_name from user1 as a inner join user2 as b on a.user_name=b.user_name    ) as b on a.user_name = b.user_name set a.over='a';   # 成功update user1 join user2 on user1.user_name=user2.user_name set user1.over=user2.over;  # 成功