[MySQL]Unknown collation: 'utf8mb4_0900_ai_ci'

        最近測試MySQL Utilities遇到下列錯誤
mysql.connector.errors.DatabaseError: 1273 (HY000): Unknown collation: 'utf8mb4_0900_ai_ci'




        看到utf8mb4_0900_ai_ci這個就知道是MySQL 8.0.17以後才會出現的charsets，而我用的是MySQL 5.7的，所以出現這錯誤是正常的

[MySQL][ERROR] Could not use /var/log/mysql-slow-queries.log for logging (error 13)

        因為Zabbix最近有效能的問題，監控數千台有時會整個斷圖，想搞清出到底是Server不夠力還是DB本身不夠力，但是斷圖什監控項資料都沒了，都無法釐清當時狀況，很尷尬，所以另外裝了Percona Monitoring and Management (PMM)來監控Zabbix Server跟DB，PMM安裝可用docker實現無腦安裝很簡單，但設定上有些麻煩目前還搞不定，其中有項功能就是會自動幫你rotate slow log，但我發現只會幫你mv，不會幫你touch，意思就是更名後就沒有slow log，那就不會紀錄slow query囉，這就不易debug

        當slow log不存在時，touch一下，chown後，進mysql再flush slow logs，通常slow query就會正常紀錄了

touch /var/log/slow.log

chown mysql.mysql /var/log/slow.log

flush slow logs

        但這次竟然失敗，slow log依然是空的，我反覆試了幾次都如此，Error log有告知解法是要你重啟mysql，有沒有搞錯啊？DB是能隨便重啟的嗎？

[MySQL]預設innodb_log_file_size太小，對IO影響有多大呢？

       基本上innodb_log_file_size我從沒用過預設值，一般來說都要求要能夠撐住1小時的寫入量，同事管的一台MySQL有效能上的問題，請我幫忙看，iostat看了一下還滿忙的，IOPS在1000~3000左右，IO吞吐量介於25MB~80MB，幾乎全是寫IO為主，以SAS 15000轉RAID 10來說算忙碌的，但看起來IO還不至於撐不住，看了一下MySQL內讀寫比為8:2，比對OS的顯得不太合理，寫IO怎麼會那麼重，再看一下設定，innodb_log_file_size居然用預設值，只有5MB，2個也不過10M，算了一下適當的大小應該要1.4G，10M對1.4G這也差太多了，log file豈不是一直被替換，寫IO這麼忙應該是這個問題，請同事找時間先改成1G看看，那改了之後IO差多少呢？

       下圖是PMM的監控項，時間範圍涵蓋更改innodb_log_file_size的前後時間段，看中間的兩個圖分別是IO吞吐量跟IOPS，更改innodb_log_file_size後的數據讓我嚇了一跳，同樣的QPS下，IO變得一點都不忙碌了，也差太多

       那如何計算innodb_log_file_size的大小呢？可參考Calc_innodb_log_file_size.sh

[MySQL]初始化資料庫後，竟無法啟動，Error Log無錯誤訊息紀錄

      測試Percona 5.7.19-17，原本就已搭好Master了，就在Slave上安裝完Server後，準備初始化資料庫，我直接把Master的my.cnf拿來用，初始化資料庫成功，自定義的Error Log目錄下有產生對應的mysql_error.log，預設的Error Log路徑下沒有，到這裡一如往常沒有問題

      但接下來MySQL竟然啟動FAILED，怪的是mysql_error.log竟然沒有錯誤訊息，難道錯誤寫到預設Error Log下，一看還真的產生了一個，不過是空的，如下圖

[MySQL]MySQL 5.7 inet_aton參數不允許空字串

　　話說把一個資料蒐集的DB從Percona 5.6升級到5.7，結果用LOAD DATA INFILE的入庫腳本幾乎全部失敗，顯示如下的錯誤
Incorrect string value: '''' for function inet_aton

　　因為來源資料有ip，所以用inet_aton轉換成整數存入，都運作好幾個月的腳本怎麼會升級後出錯呢？而且它都是顯示第一行出錯，我怎麼看第一行都是對的@@

　　後來嘗試了一下inet_aton在5.6與5.7的差異
　　各位猜猜看，下面哪個會出錯？
select inet_aton(null);
select inet_aton(0);
select inet_aton('');

[CentOS]升級MySQL後，mail命令竟然出錯了

　　升級mysql後，mail就寄不出去了?
send-mail: error while loading shared libraries: libmysqlclient.so.16: cannot open shared object file:
No such file or directory

[MySQL]Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126.

        最近遇到下面這個錯誤，該Table有近三百個欄位，因為這是為了分析來源文字檔所產生Table，來源文字檔欄位數及欄位長度不是我能控制的，還有愈來愈多的跡象

[MySQL]用SELECT INTO OUTFILE匯出資料成CSV

        一般在要在命令列模式下要將MySQL的資料匯出成CSV，我通常都是直接mysql -e "SQL Statement" > /tmp/output.csv，然後SQL Statement用CONCAT組成逗號分隔的字串出來

        後來看書上提到用SELECT INTO OUTFILE更快，於是測試了一下，寫了兩個Shell，主要的SQL Statement是一樣的，只是一個用CONCAT，一個用INTO OUTFILE

        第一個Shell，測試時間的方法如下圖，其中SQLCMD是為一般SELECT CONCAT

[SSIS][Error]ADO NET Source: Object reference not set to an instance of an object.

Description: System.NullReferenceException: Object reference not set to an instance of an object.  
Description: ADO NET Source failed the pre-execute phase and returned error code 0x80004003.

        突然有天，運行數月的SSIS排程失敗了，這SSIS主要是從MySQL匯入資料到SQL Server上，錯誤訊息如上，用的是MySQL ODBC Provider

        為什會有NullReferenceException的異常讓我百思不解？最後研究發現應該是網路的問題，詢問網管後才得知，公司前一天有被DDOS攻擊，防火牆有做調整，造成大資料量的傳輸會被封鎖，小資料量的傳輸就都沒有問題，DB間使用的網段加入白名單就解了，很瞎吧

     

[SSIS][MySQL Data Provider]Error: The value was too large to fit in the ADO NET Source.

        因為得從MySQL匯入資料到SQL Server上，參考Connecting to MySQL from SSIS此篇，設好ADO NET Source後，測試一下出現如下錯誤

[MySQL]ERROR 1010 (HY000): Error dropping database (can't rmdir './test/', errno: 17)

遇到Bug了，紀錄一下

Bug #62443 "DROP DATABASE test" fails with error 17

[MySQL]innodb_stats_on_metadata記得要關閉

        最近有個log DB量實在有點大，其中一個大Table每天新增三千萬筆資料，目前Table逼近100G，不得已只好拆成Partitioning Table，以方便清除歷史資料及查詢。

        以MySQL來說，Partitioning Table效能及操作方便性沒有Oracle及SQL Server來的好，分區太多會影響效能，若按天拆分，會有90個分區，因User要求保留三個月資料，後來與User商量的結果，保留近七天的熱資料在分區表上，七天以前的冷資料會每天拆成一個表，這樣要查資料都還調的到

        等我拆完表後，發現一個奇怪的現象，就是Response慢慢慢，比不拆前還慢

        show index慢
     

        查information_schema.PARTITIONS也慢

        

        連Explain也慢

        我還沒Query怎麼會慢成這樣，此時忽然想起之前有看到一篇Solving INFORMATION_SCHEMA slowness，關掉innodb_stats_on_metadata就OK囉

set global innodb_stats_on_metadata=0;

        記得My.ini也要加就是了，要不然重啟又啟用了

innodb_stats_on_metadata=0

        5.5版本預設是開啟的，用5.6就沒有這個問題， 因為5.6預設是關閉的！


        

[SQL Server][Oracle][MySQL][PostgreSQL] IN條件式寫法的差異

        一般找某個資料表的資料是否存在另一個資料表中，就語法上來說，可以用INNER JOIN，若條件式僅JOIN單一欄位，可以改成用IN或EXISTS；若是JOIN多個欄位，就無法用IN囉，這是在SQL Server的情況

        無意間看到在Oracle中，IN條件式的寫法裡竟然可以接受多個欄位呢，另外試了MySQL與PostgreSQL也都可以喔

[MySQL]log_queries_not_using_indexes與slow_query_log同時啟用會造成大量的slow query log

        話說管理的MySQL有啟用slow_query_log，然後發現每天的成長量非常的大 ，每天增加1G吧，量太大了對於分析slow query非常耗時，仔細分析又發現幾乎所有的Query紀錄進去了 ，這不太對啊，log_query_time設1秒，照理說超過1秒才會記錄，但幾乎全紀錄，毫秒微秒級的也記錄作什，看avg、95%跟前五名的都是毫秒級回應，收到這種分析幾乎沒什用

[MySQL]Account user_name@host_name does not exists but it still has privileges defined for following objects:database_name

        平常刪除帳號都用drop user，今天需要對某個DB刪除大量帳號，想說直接delete from mysql.user最快了，刪完用MySQL Workbench開啟來看是否刪乾淨了，結果發現帳號還在，只是前面多了"(!)"符號

        點擊某個帳號，出現下列訊息，好像告訴我沒有刪除乾淨

Account user_name@host_name does not exists but it still has privileges defined for following objects:database_name

       猛然想起mysql.db好像也要刪除，所以若不用drop user而直接清除user資料表，別忘記db資料表也要清喔，完整的刪除方式如下

DELETE FROM mysql.user WHERE user='phpmyadmin' and host='localhost';
DELETE FROM mysql.db   WHERE user='phpmyadmin' and host='localhost';
FLUSH PRIVILEGES;

       若用別的client工具似乎不會發現這個問題，我用Navicat打開是不會看到只刪user未刪db的帳號喔！

[MySQL]查看Binlog的內容

        Binlog是MySQL的日誌，通常用作Replication及備份使用，這麼重要的東西當然要了解它囉，預設安裝是不會啟用Binlog的，要啟用它要在my.ini檔加入下列系統參數，然後重啟MySQL生效

#還有其他相關參數就不列了
log-bin=/var/lib/mysql/mysql-bin #必要的，設定位置及檔案名稱
expire_logs_days=14                    #選擇性的，設定保留的期間
binlog-format=statement              #選擇性的，有三種模式，預設是statement，還有row及mixed

[MySQL]如何在Percona上安裝mysqlftppc-bigram做中文全文索引呢

為何不用Sphinx或Lucene呢？因為比起前面這兩個，這plugin安裝及使用上相對容易，幾百萬筆的資料查詢起來還可接受，M/S架構上裝在Slave運作也還OK，若資料量千萬筆就比較不建議用這個囉，還是用專業的Sphinx或Lucene吧

環境：CentOS 6.5 + Percona 5.5.40

安裝條件：
1.mysql-devel套件，安裝這個才有mysql_config
2.libicu-devel套件，要支援UNICODE，安裝這個才有icu-config

3.安裝mysqlftppc-bigram

4.相容MySQL 5.5與5.6，Engine只能用MyISAM喔

[MySQL]Memory Table的維護

       同事認為定期delete資料可以清除memory table空間？
       我們來看一下吧！

#建立memory table

create table mem (id int) engine = memory;

show table status like 'mem'\G

[MySQL]啟用innodb_file_per_table將共享表空間改成獨立表空間

        MySQL預設是共享表空間，而獨立表空間的優點是減少對表空間IO資源的爭用，容易管理及備份，回收空間容易，而本文是教你怎麼套用成獨立表空間

        可以看到innodb_file_per_table預設未開啟

mysql> show variables like 'innodb_file_per_table%';
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| innodb_file_per_table    | OFF      |
+--------------------------+----------+
1 rows in set (0.00 sec)

[MySQL]多種字符編碼同時顯示的問題

        目前字符集是utf8
            

show variables like '%char%';

        建立測試資料表

DROP TABLE IF EXISTS `unicode_test`;

CREATE TABLE `unicode_test` (

  `t1` int(11) NOT NULL AUTO_INCREMENT,

  `t2` varchar(64) NOT NULL,

  PRIMARY KEY (`t1`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

;

        插入一筆亂碼資料看看
insert into unicode_test (t2) values ('我是奇怪文嬗');

        果然顯示亂碼

select * from unicode_test;