MySQL错误处理记录

MySQL日志时间的时区不对

# 查看当前时间
select now();

# 查看时区设置信息
show variables like "%time_zone%";

# 日志采用的时区log_timestamps，一般是UTC
SHOW global variables like 'log_timestamps';

# 临时设置为系统时间
set global log_timestamps=SYSTEM;

永久生效需要个性my.cnf文件，加上log_timestamps参数

[mysqld]
log_timestamps = SYSTEM

"select command denied to user root"

mysql 报错

"select command denied to user root" 

问题原因：一般在给指定用户配置指定库权限时，未开放对整个服务的select权限。 解决办法：找到mysql数据库下的user表，把对应的用户权限修改为'Y' 例如：select_priv

---

mysql 配置文件设置最大链接数max_connections不生效

1、临时设置最大链接数为1000，重启mysql后 会变成默认值151.

mysql -uuser -ppassword(命令行登录MySQL)
mysql>show variables like ‘max_connections’;(查可以看当前的最大连接数)
msyql>set global max_connections=1000;(设置最大连接数为1000，可以再次查看是否设置成功)
mysql>exit

2、永久性修改最大链接数为1000，需要修改配置文件 linux只要修改MySQL配置文件my.ini 或 my.cnf的参数max_connections，将其改为

max_connections=1000  # 然后重启MySQL即可

ubuntu 需要修改/etc/mysql/mysql.conf.d/mysqld.cnf ，将其改为

max_connections=1000，然后重启MySQL即可

3、奇怪的是重启完成之后，数据库中最大链接数变成了214，我当时以为设置写错了，换成了500，发现还是214，在换成200，最大链接数竟然变成了200。说明配置是生效了，还有其他问题。 解决办法

ulimit -n
1024

修改 ulimit 值，下面是永久生效

vi /etc/security/limit.conf
	root soft     nproc          1000000    
	root hard     nproc          1000000   
	root soft     nofile         1000000   
	root hard     nofile         1000000

临时生效的设置方式

ulimit -n 65535

打开文件最大的数量， 更改 MySQL 在 Linux 的最大文件描述符限制，编辑 /usr/lib/systemd/system/mysqld.service 文件，在文件最后添加: ubuntu16.04 下面位于：/lib/systemd/system/mysql.service,可以通过find 命令查找

LimitNOFILE=65535
LimitNPROC=65535

保存后，执行下面命令，使配置生效

systemctl daemon-reload
systemctl restart mysqld

---

Unknown table 'column_statistics' in information_schema

如果使用MySQL 8.0+版本提供的命令行工具mysqldump来导出低于8.0版本的MySQL数据库到SQL文件，会出现错误

Unknown table 'column_statistics' in information_schema

因为早期版本的MySQL数据库的information_schema数据库中没有名为COLUMN_STATISTICS的数据表。

解决问题的方法是，使用8.0以前版本MySQL附带的mysqldump工具，最好使用待备份的MySQL服务器版本对应版本号的mysqldump工具，mysqldump可以独立运行，并不依赖完整的MySQL安装包，比如在Windows中，可以直接从MySQL安装目录的bin目录中将mysqldump.exe复制到其他文件夹，甚至从一台电脑复制到另一台电脑，然后在CMD窗口中运行。

---

mysqldump: Couldn't execute 'show events': Access denied for user 'root'@'%' to database 'performance_schema' (1044)

MySQL 5.6.40

mysqldump: Couldn't execute 'show events': Access denied for user 'root'@'%' to database 'performance_schema' (1044)

在备份命令中加上如下参数 --skip-events

---

1146 - table 'performance_schema.session_variables' doesn't exist

mysql 5.6 通过Yum升级到5.7后，远程连接提示

1146 - table 'performance_schema.session_variables' doesn't exist

解决办法

mysql_upgrade -u root -p --force

---

“ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.”

mysql 5.6.45 新建索引报错

“ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.”

**原因：**由于MySQL的InnoDB引擎表索引字段长度的限制为767字节，因此对于多字节字符集的大字段或者多字段组合，创建索引时会出现该问题。

解决方法：将innodb_file_format相关配置改成下面值，然后重新创建该表。

mysql>  show variables like '%innodb_file_format%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Barracuda |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Barracuda |
+--------------------------+-----------+

如果还不行，创建表时指定表的Row Format为Dynamic或Compressed，ROW_FORMAT=DYNAMIC或ROW_FORMAT=COMPRESSED

如果表已创建可用如下语句修改表的Row Format

alter table TABLE_NAME row_format=dynamic;	# dynamic格式
alter table TABLE_NAME row_format=compressed;	# compressed格式

执行以下SQL语句，确认Row_format值是不是DYNAMIC和COMPRESSED。

show table status like '[$Table_Name]'\G

---

Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.

MySQL 5.7.31

mysqldump 备份时提示

Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.

这是因为mysqldump默认是不备份事件表的，加上下面的参数即可

--events --ignore-table=mysql.event

---

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

mysql 5.7.24

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

修改my.ini

[mysqld]
innodb_large_prefix=1

---

--initialize specified but the data directory has files in it. Aborting.

---

Specified key was too long; max key length is 767 bytes

mysql 5.6.45

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

先看一下以下信息是否如下所示

show VARIABLES like '%innodb_large_prefix%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_large_prefix | ON    |
+---------------------+-------+
1 row in set (0.01 sec)


show VARIABLES like '%innodb_file%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Barracuda |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Barracuda |
| innodb_file_per_table    | ON        |
+--------------------------+-----------+
4 rows in set (0.00 sec)

如果不是，则通过以下命令临时调整

set global innodb_large_prefix=ON;
set global innodb_file_format=Barracuda;

另外还要修改为ROW_FORMAT=DYNAMIC 如果是新建表，则在创建语句后加上ROW_FORMAT=DYNAMIC

create table idx_length_test_02
(
  id int auto_increment primary key,
  name varchar(255)
) 
ROW_FORMAT=DYNAMIC default charset utf8mb4;

如果是执行ALTER时遇到的该问题，则通过以下命令调整

alter table <表名> row_format=dynamic;

---

ERROR 1292 (22007): Truncated incorrect DOUBLE value: ''

问题： 更新字段时，提示该错误

ERROR 1292 (22007): Truncated incorrect DOUBLE value: ''

可能是某个字段的值看起来像数字但实际是文本类型而没有用引号引起来

---

Duplicate entry '1233869' for key 'PRIMARY'

Duplicate entry '1233869' for key 'PRIMARY'

应该是自增主键1233869这一条信息插入未完成服务关闭事务未回滚，导致后面再次自增插入时插入重复， 解决办法1：手动将这个值+1跳过错误的那一条

ALTER TABLE `jscdb`.`core_logs` AUTO_INCREMENT = 1233870

---

mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table TABLE_NAME at row: 1791140

mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table TABLE_NAME at row: 1791140

在备份的命令中加上--max_allowed_packet=512M即可

---

服务器硬盘空间不足，重启失败，导致部分报如下错误需要修复。

2021-12-23 14:51:56 20105 [ERROR] /usr/sbin/mysqld: Table './pqs/core_logs' is marked as crashed and should be repaired
2021-12-23 14:51:58 20105 [ERROR] /usr/sbin/mysqld: Table './pqs/core_logs' is marked as crashed and should be repaired
2021-12-23 14:51:58 20105 [ERROR] /usr/sbin/mysqld: Table './pqs/core_logs' is marked as crashed and should be repaired
2021-12-23 14:51:58 20105 [ERROR] /usr/sbin/mysqld: Table './pqs/core_logs' is marked as crashed and should be repaired
2021-12-23 14:52:08 20105 [ERROR] /usr/sbin/mysqld: Table './mml_jsj/core_logs' is marked as crashed and should be repaired
2021-12-23 14:52:08 20105 [ERROR] /usr/sbin/mysqld: Table './mml_jsj/core_logs' is marked as crashed and should be repaired
2021-12-23 14:52:08 20105 [ERROR] /usr/sbin/mysqld: Table './mml_jsj/core_logs' is marked as crashed and should be repaired

# 检查一下表是否正常
check TABLE db.table

+----------------------+-------+----------+-----------------------------------------------------------------+
| Table                | Op    | Msg_type | Msg_text                                                        |
+----------------------+-------+----------+-----------------------------------------------------------------+
| jsc_gw.core_logs_old | check | warning  | Table is marked as crashed                                      |
| jsc_gw.core_logs_old | check | error    | Size of datafile is: 21966299136         Should be: 21966448428 |
| jsc_gw.core_logs_old | check | error    | Corrupt                                                         |
+----------------------+-------+----------+-----------------------------------------------------------------+
3 rows in set (0.00 sec)

# 看上面的Msg_type显示有错误，执行repair命令来进行修复，修复完成会提示status OK
repair TABLE db.table;

+----------------------+--------+----------+----------------------------------------------------------+
| Table                | Op     | Msg_type | Msg_text                                                 |
+----------------------+--------+----------+----------------------------------------------------------+
| jsc_gw.core_logs_old | repair | info     | Found block that points outside data file at 21966296088 |
| jsc_gw.core_logs_old | repair | status   | OK                                                       |
+----------------------+--------+----------+----------------------------------------------------------+
2 rows in set (2 min 38.52 sec)

# 再次检查要修复的表，显示status OK
check table core_logs_old;
+----------------------+-------+----------+----------+
| Table                | Op    | Msg_type | Msg_text |
+----------------------+-------+----------+----------+
| jsc_gw.core_logs_old | check | status   | OK       |
+----------------------+-------+----------+----------+
1 row in set (24.40 sec)

---

ERROR 2026 (HY000): SSL connection error: unknown error numberMYSQL开启了SSL连接，命令行无法登录

mysql> SHOW VARIABLES LIKE "%ssl%";
+-------------------------------------+-----------------+
| Variable_name                       | Value           |
+-------------------------------------+-----------------+
| admin_ssl_ca                        |                 |
| admin_ssl_capath                    |                 |
| admin_ssl_cert                      |                 |
| admin_ssl_cipher                    |                 |
| admin_ssl_crl                       |                 |
| admin_ssl_crlpath                   |                 |
| admin_ssl_key                       |                 |
| have_openssl                        | YES             |
| have_ssl                            | YES             |
| mysqlx_ssl_ca                       |                 |

在命令行登录信息后面加上 --ssl-mode=DISABLED或 --ssl=0

---

mysql中count(*)和information_schema.tables结果值不同

导致统计信息不准确的原因是什么呢？其实是MySQL 8.0为了提高information_schema的查询效率，将视图tables和statistics里面的统计信息缓存起来，缓存过期时间由参数information_schema_stats_expiry决定，默认为86400s；

要查询准确的行数，还是要使用 count(*)来查询。批量的话可以用下面的方法来生成批量查询语句

# 将最后的mysql改成要查询的库名，执行后，将生成的查询语句保存下来执行查询即可。
SELECT CONCAT( 'SELECT "', TABLE_NAME, '", COUNT(*) FROM ', TABLE_SCHEMA, '.', TABLE_NAME)  EXEC_SQL FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'mysql';

# 生成的结果
SELECT "columns_priv", COUNT(*) FROM mysql.columns_priv
SELECT "db", COUNT(*) FROM mysql.db
SELECT "engine_cost", COUNT(*) FROM mysql.engine_cost
SELECT "event", COUNT(*) FROM mysql.event
SELECT "func", COUNT(*) FROM mysql.func
SELECT "general_log", COUNT(*) FROM mysql.general_log
SELECT "gtid_executed", COUNT(*) FROM mysql.gtid_executed
SELECT "help_category", COUNT(*) FROM mysql.help_category
SELECT "help_keyword", COUNT(*) FROM mysql.help_keyword
SELECT "help_relation", COUNT(*) FROM mysql.help_relation
SELECT "help_topic", COUNT(*) FROM mysql.help_topic
SELECT "innodb_index_stats", COUNT(*) FROM mysql.innodb_index_stats
SELECT "innodb_table_stats", COUNT(*) FROM mysql.innodb_table_stats
SELECT "ndb_binlog_index", COUNT(*) FROM mysql.ndb_binlog_index
SELECT "plugin", COUNT(*) FROM mysql.plugin
SELECT "proc", COUNT(*) FROM mysql.proc
SELECT "procs_priv", COUNT(*) FROM mysql.procs_priv
SELECT "proxies_priv", COUNT(*) FROM mysql.proxies_priv
SELECT "server_cost", COUNT(*) FROM mysql.server_cost
SELECT "servers", COUNT(*) FROM mysql.servers
SELECT "slave_master_info", COUNT(*) FROM mysql.slave_master_info
SELECT "slave_relay_log_info", COUNT(*) FROM mysql.slave_relay_log_info
SELECT "slave_worker_info", COUNT(*) FROM mysql.slave_worker_info
SELECT "slow_log", COUNT(*) FROM mysql.slow_log
SELECT "tables_priv", COUNT(*) FROM mysql.tables_priv
SELECT "time_zone", COUNT(*) FROM mysql.time_zone
SELECT "time_zone_leap_second", COUNT(*) FROM mysql.time_zone_leap_second
SELECT "time_zone_name", COUNT(*) FROM mysql.time_zone_name
SELECT "time_zone_transition", COUNT(*) FROM mysql.time_zone_transition
SELECT "time_zone_transition_type", COUNT(*) FROM mysql.time_zone_transition_type
SELECT "user", COUNT(*) FROM mysql.user

---

mysql federated 引用其它库的表时，做主从同步时，只能在源库更新这张表，不能在引用的位置更新；因为在引用位置更新时就已经对源库进行了操作，从库去同步时源库已经被修改，再做同样的操作会导致从库崩溃，陷入无限重启失败的情况。

临时解决办法，在主库备份该表完整数据，在从库使用replicate_ignore_db=DBNAME临时忽略对于该引用库的同步操作。等从库可以正常启动后再去修改配置文件去掉该配置并重启从库，然后在主库删除引用库的整个库（为了保证数据的完整），再导入之前备份的数据。

---

MySQL server version for the right syntax to use near 'REMOTE

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REMOTE
) ENGINE = FEDERATED CHARACTER SET = utf8 COLLATE = utf8_general_ci COMME' at line 12

这个错误是MySQL语法错误，具体的错误消息是指向 REMOTE 关键字。

在你的SQL语句中，有一行如下定义：

PRIMARY KEY (`ID`) USING REMOTE

这里的 USING REMOTE 是不合法的。MySQL中，对于主键（PRIMARY KEY）的定义，没有 USING REMOTE 这样的关键字或者选项。所以，当MySQL解析到这一部分时，会抛出语法错误。

正确的主键定义方式通常如下：

PRIMARY KEY (`ID`)

---

ERROR 1045 (28000): Access denied for user 'app'@'192.168.x.x' (using password: YES)

CREATE USER 'app'@'%' IDENTIFIED BY 'pass';
grant all privileges on *.* to 'app'@'%' with grant option;

# 具体权限 
GRANT Alter, Alter Routine, Create, Create Routine, Create Temporary Tables, Create View, Delete, Drop, Event, Execute, Grant Option, Index, Insert, Lock Tables, References, Select, Show View, Trigger, Update ON `zentao`.* TO `zentao`@`192.168.200.%`;

---

ERROR: mysqld failed while attempting to check config command was: "mysqld --verbose --help --log-bin-index=/tmp/tmp.ci5irEMp9E"

使用docker-compose运行mysql 5.6 。重启容器后无法正常启动。

解决方法，修改yaml文件，增加ulimits配置。

services:

  db:
    image: mysql:5.6.45
    container_name: mysql
    privileged: true

    # 增加ulimits配置，
    ulimits:
      nofile: # Fix memory leak issue on some systems when LimitCORE=infinity (containerd)
        soft: 1048576
        hard: 1048576
    deploy:
      resources:
        limits:
          memory: 6G
    restart: always
    # environment:
    #   MYSQL_ROOT_PASSWORD: jiaparts
    volumes:
      - /jpdata/mysql/data:/var/lib/mysql
      - /jpdata/mysql/conf:/etc/mysql/conf.d:rw
      - /jpdata/mysql/logs:/var/log/mysql
    ports:
      - 3306:3306