mysql 使用过程的错误记录（二）

一：mysql 错误记录

1.[ERROR] /usr/local/mysql/bin/mysqld: Sort aborted: Query execution was interrupted

处理过程：

动态调整参数：

show variables like "%buffer_size%";

| read_buffer_size | 8388608 |

| read_rnd_buffer_size | 8388608 |

| sort_buffer_size | 8388608 |

以上参数加大到 8M

2.一般是查询数据过大过多导致中断（order by 操作）

通过查询语句发现

ORDER BY ff.CreateDateTime DESC LIMIT 1

导致

检查 CreateDateTime 是否增加了索引

查看索引：

show index from DoctorFriends;

没有建立索引

创建索引

create index doctorfriend_CreateDateTime on DoctorFriends (CreateDateTime);

3.mysql 8.0 复制账号报错

ERROR 1410 (42000): You are not allowed to create a user with GRANT

格式已经发生变化

CREATE USER 'repl'@'192.168.10.12' IDENTIFIED BY 'repl123';

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.10.12';

flush privileges;

4.建表分区错误

ERROR 1178 (42000): The storage engine for the table doesn't support native partitioning

CREATE TABLE trb3 (id INT, name VARCHAR(50), purchased DATE) PARTITION BY RANGE( YEAR(purchased) ) ( PARTITION p0 VALUES LESS THAN (1990), PARTITION p1 VALUES LESS THAN (1995), PARTITION p2 VALUES LESS THAN (2000), PARTITION p3 VALUES LESS THAN (2005) );

5. Slave_IO_State: Reconnecting after a failed registration on master

6.InnoDB: Error: Table "mysql"."innodb_table_stats" not found.

7.mysqldump: unknown variable 'auto-increment-increment=2'

注释掉/etc/my.cnf 中 [mysqldump] 这节

8.ERROR 1010 (HY000): Error dropping database (can't rmdir './t1', errno: 39)

cd /data/mysqldata/data

rm -rf t1

drop database t1;

9.create table t1(id int);

ERROR 1050 (42S01): Table '`t1`.`t1`' already exists

a.备份数据库

b.删除目录

c.重新导入数据库

10.through socket '/var/lib/mysql/mysql.sock' (2) (localhost)

创建软连接

ln -s /data/mysqldata/mysql.sock /var/lib/mysql/

chown -R mysql.mysql mysql/

11.ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column

如果启用了only_full_group_by SQL模式(在默认情况下是这样)，那么MySQL就会拒绝选择列表、条件或顺序列表引用的查询，这些查询将引用组中未命名的非聚合列，而不是在功能上依赖于它们

查看模式

select @@sql_mode;

only_full_group_by说明：

only_full_group_by ：使用这个就是使用和oracle一样的group 规则, select的列都要在group中,或者本身是聚合列(SUM,AVG,MAX,MIN) 才行，其实这个配置目前个人感觉和distinct差不多的，所以去掉就好

解决：

执行以下两个命令：

set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

Q1 which is not functionally dependent on columns in GROUP BY clause

this is incompatible with sql_mode=only_full_group_by

查看模式

select @@sql_mode;

only_full_group_by：使用这个就是使用和oracle一样的group 规则, select的列都要在group中,或者本身是聚合列(SUM,AVG,MAX,MIN) 才行，其实这个配置目前个人感觉和distinct差不多的，所以去掉就好

show variables like "sql_mode";

临时解决：

SET sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

写入配置文件

/etc/my.cnf

sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

12.ERROR 1582 (42000): Incorrect parameter count in the call to native function 'isnull'

错误原因：

mysql识别不了ISNULL，支持IFNULL

13.ERROR 1227 (42000) at line 18: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

去掉首部和尾部相关的配置参数

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;

SET @@SESSION.SQL_LOG_BIN= 0;

--

-- GTID state at the beginning of the backup

--

SET @@GLOBAL.GTID_PURGED='d93e4321-53f5-11eb-be07-0c42a1fe4663:1-17396276';

--

-- Current Database: `fanbook_live`

--