MySQL 常见错误

MYSQL ERROR 1862 (HY000): Your password has expired.

密码过期，需重新修改下密码即可：

# 修改原密码（继续使用原密码也可以的）,输入命令：
SET PASSWORD for root@localhost = PASSWORD('123456');
# 修改当前用户密码
SET PASSWORD = PASSWORD('root');

# 修改root密码永不过期，输入命令：
alter user 'root'@'localhost' password expire never;
# 或者
SET GLOBAL default_password_lifetime = 0;  # -- never 全局用户
# 或者
ALTER USER 'root'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY; # -- 90 days

配置文件中修改：

[mysqld]
default_password_lifetime=0

ERROR 1206 (HY000): The total number of locks exceeds the lock table size

https://blog.csdn.net/github_36326955/article/details/54970808

InnoDB 表执行大批量数据的更新，插入，删除操作时会出现这个问题，需要调整 InnoDB 全局的 innodb_buffer_pool_size 的值来解决这个问题，并且重启 mysql 服务。

默认的 innodb_buffer_pool_size=8M

mysql> show variables like "%_buffer%";
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| bulk_insert_buffer_size             | 8388608        |
| innodb_buffer_pool_chunk_size       | 8388608        |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 8388608        |
| innodb_change_buffer_max_size       | 25             |
| innodb_change_buffering             | all            |
| innodb_log_buffer_size              | 1048576        |
| innodb_sort_buffer_size             | 1048576        |
| join_buffer_size                    | 262144         |
| key_buffer_size                     | 8388608        |
| myisam_sort_buffer_size             | 159383552      |
| net_buffer_length                   | 16384          |
| preload_buffer_size                 | 32768          |
| read_buffer_size                    | 65536          |
| read_rnd_buffer_size                | 262144         |
| sort_buffer_size                    | 262144         |
| sql_buffer_result                   | OFF            |
+-------------------------------------+----------------+
24 rows in set

修改 innodb_buffer_pool_size 的值为 3G（3*1024*1024*1024）：

SET GLOBAL innodb_buffer_pool_size=67108864;

1114 - The table 'xxxx' is full

这是由于内存表的大小超过了规定的范围。

[mysqld]
## 内存表容量，默认为 16777216（字节，16M）
max_heap_table_size=1024M
## 临时表容量
tmp_table_size=1024M

MySQL 8.0版本连接报错：Could not create connection to database server.

https://blog.csdn.net/qq_22076345/article/details/81952035

MySQL8.0 版本需要更换驱动为 com.mysql.cj.jdbc.Driver，之前的 com.mysql.jdbc.Driver 已经不能在MySQL 8.0 版本使用了，官方文档链接：https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-api-changes.html

另外 mysql-connector-java 也推荐更新到8.0的版本（https://dev.mysql.com/downloads/connector/j/）。

综上修改以下两点：

1. 更新 mysql-connector-java 版本：

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.11</version>
    </dependency>

2. 更换驱动：

    <property name="driver" value="com.mysql.cj.jdbc.Driver"/>

报时区错误

Failed to execute goal org.mybatis.generator:mybatis-generator-maven-plugin:1.3.5:generate (default-cli) on project songci-serv: The server time zone value 'Öйú±ê׼ʱ¼ä' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support. -> [Help 1]

解决方法执行命令给 MySQL 服务器设置时区为东八区，这个是 com.mysql.cj.jdbc.Driver 需要指定的：

mysql> set global time_zone='+8:00';
Query OK, 0 rows affected

或者在数据库连接配置中加上 serverTimezone=GMT%2B8（代表东八区，或者 serverTimezone=Asia/Shanghai），如下：

connectionURL="jdbc:mysql://127.0.0.1:3306/songci?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8"