Category Archives: MySQL

MySQL sql_mode 说明(及处理一起 sql_mode 引发的问题) | Sean’s Notes

1. MySQL莫名变成了 Strict SQL Mode最近测试组那边反应数据库部分写入失败,app层提示是插入成功,但表里面里面没有产生数据,而两个写入操作的另外一个表有数据。因为 insert 失败在数据库层面是看不出来的,于是找php的同事看下错误信息: 1[Err] 1364 – Field `f_company_id` doesn’t have a default value

Source: MySQL sql_mode 说明(及处理一起 sql_mode 引发的问题) | Sean’s Notes

[程式泥沼中打滾] – phpMyAdmin 大型 MySQL 檔案匯入解決方案 @ 豚丸の硬いブログ :: 痞客邦 PIXNET ::

在 php 中有設定最大上傳檔案大小 如果想上傳較大的 *.sql 檔案 phpMyAdmin 會有錯誤跳出,不允許上傳檔案 在這種情況以 phpMyAdmin 內建的功能我們有兩種解決方案: (

Source: [程式泥沼中打滾] – phpMyAdmin 大型 MySQL 檔案匯入解決方案 @ 豚丸の硬いブログ :: 痞客邦 PIXNET ::

killing of MySQL Connections

Every so often I run into situation when I need to kill a lot of connections on MySQL server – for example hundreds of instances of some bad query is running making server unusable. Many people have special scripts which can take the user, source host or query as a parameter and perform the action. There is also a way to do it just using MySQL with a few commands:


mysql> select concat('KILL ',id,';') from information_schema.processlist where user='root' and Info is null;
+------------------------+
| concat('KILL ',id,';') |
+------------------------+
| KILL 3101; |
| KILL 2946; |
+------------------------+
2 rows in set (0.00 sec)
mysql> select concat('KILL ',id,';') from information_schema.processlist where user='root' and Info is null into outfile '/tmp/a.txt';
Query OK, 2 rows affected (0.00 sec)
mysql> source /tmp/a.txt;
Query OK, 0 rows affected (0.00 sec)