最後更新: 2018-04-11

目錄

• perror
• [1] MySQL server has gone away
• [2] Unknown collation
• [3] Unknown character set
• [4] Innodb count(*) slow
• [5] Errcode: 24

---

perror

 

perror prints a description for a system error code or for a storage engine (table handler) error code.

i.e.

perror 2

OS error code   2:  No such file or directory
Win32 error code 2: The system cannot find the file specified.

 

---

[1] MySQL server has gone away

 

log:

import sql 時出現

Error in query (2006): MySQL server has gone away

原因:

wait_timeout 或 max_allowed_packet over 了

SHOW VARIABLES LIKE 'wait_timeout'

28800

SHOW VARIABLES LIKE 'max_allowed_packet'

# 1Mbyte
1048576

Fix it:

[1] my.cnf

[mysqld]
max_allowed_packet=8M

OR

[2] session

# you need the SUPER privilege for this operation

SET GLOBAL max_allowed_packet=1073741824;

 

---

utf8mb4 問題

 

utf8mb4 charset is a superset of utf8 that uses a maximum of four bytes per character and supports supplementary characters

MySQL 5.5 沒有 utf8mb4_unicode_520_ci, 要到 MySQL 5.6 才有

Unknown character set

#1115 - unknown character set: 'utf8mb4'

解決方案:

replace utf8mb4 to utf8

 

Unknown collation

Log:

ERROR 1273 (HY000) at line 25: Unknown collation: 'utf8mb4_unicode_520_ci'

解決:

Find and replace all utf8mb4_unicode_520_ci with utf8mb4_unicode_ci

sed -i 's/utf8mb4_unicode_520_ci/utf8mb4_unicode_ci/g' file.sql

 

 

---

[4] Innodb count(*) slow

 

mysql> select count(*) as num from viewers;

+---------+
| num     |
+---------+
| 7843264 |
+---------+
1 row in set (10.56 sec)

mysql> explain select count(*) as num from viewers \G

           id: 1
  select_type: SIMPLE
        table: viewers
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 7932251
        Extra: Using index
1 row in set (0.00 sec)

describe viewers;

show index from viewers;

show create table viewers;    # 看看 Table 的 Type (Innodb, MyISAM)

[原因]

Innodb tables do not cache the rowcount like MyISAM tables,

so mysql has to walk the entire table to get a count.

If you have a unique index, it should be able to scan that instead, which will be faster.

[Fix]

ALTER TABLE viewers ENGINE = MyISAM;

show index from viewers;

Cardinality: 7844682

Index cardinality refers to the uniqueness of values stored in a specified column within an index.

The query optimizer uses the index cardinality to generate an optimal query plan for a given query.

 

---

[5] Errcode: 24

 

# 執行 cmd 後出 error

Select count(*) From Delivery where (letter_id=9438) and (status!=0);

ERROR 23 (HY000): Out of resources when opening file './MyDB/Delivery#P#p8.MYD' (Errcode: 24)

mysqld.log

191029 11:08:07 [ERROR] /usr/libexec/mysqld: Can't open file: './MyDB/Delivery.frm' (errno: 24)

原因

errno: 24 simply means that too many files are open for the given process.

Checking

# Limit

mysql> SHOW VARIABLES LIKE 'open%';

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 2048  |
+------------------+-------+

# Currently open

mysql> SHOW GLOBAL STATUS LIKE 'Open_files';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_files    | 1840  |
+---------------+-------+

 * 因為有 "max_connections=301" 所以 18xx 已經爆

Fix

/etc/my.cnf

[mysqld_safe]             # [mysqld_safe] > [mysqld]
open_files_limit = 100000

 

---