mysql 相关的错误总结

╰半橙微兮° 2021-09-22 16:36 307阅读 0赞

相当于一个错题本吧，以及使用笔记。

1，笔者MySQL 版本

## 错误一：插入记录时抛 ：\[Err\] 1055 - Expression \#1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information\_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql\_mode=only\_full\_group\_by ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzM4MjU5NTM5_size_16_color_FFFFFF_t_70][]

原因：未知

改错如下：

select version(), @@sql\_mode;  
SET sql\_mode=(SELECT REPLACE(@@sql\_mode,'ONLY\_FULL\_GROUP\_BY',''));

第一行是查看sql\_mode。

第二行是修改。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzM4MjU5NTM5_size_16_color_FFFFFF_t_70 1][]

或者：将@@global.sql\_mode的值去掉“only\_full\_group\_by”，重新设置一下 如下

select @@global.sql_mode;
    set @@global.sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

完美解决~

--------------------

## 错误二： ##

## \#\#\# Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLException: Access denied for user ''@'mc-PC' (using password: YES)\] with root cause ##

## java.sql.SQLException: Access denied for user ''@'mc-PC' (using password: YES) ##

原因可能类似：MySQL数据库权限问题，远程服务器不允许你的java程序访问它的数据库。所以，我们要对远程服务器进行设置，使它允许你进行连接。

解决办法：

1，网上有说打开本地3306端口的，关闭防火墙的。本人测试没有解决。

2，打开mysql控制台，输入：use mysql;

![20181226103532200.png][]

输入：show tables;

![20181226103607585.png][]

select host from user; 并修改访问权限update user set host ='%' where user ='root';

![20181226103706554.png][]

打开计算机的服务界面，重新启动mysql服务，远程服务器就允许程序连接数据库了。本人测试还是没有解决

3，修改配置文件的

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzM4MjU5NTM5_size_16_color_FFFFFF_t_70 2][]

结果添加进去反而服务启动不了了。

4、修改Java连接

jdbc:mysql://localhost:3306/，这个东西后面跟的是数据库的名字，8.0.X以上的版本还需在数据库名后面加上这些东西

?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true

唉！新版就是搞事情，本人一测果然抛错变了，

\#\#\# Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLNonTransientConnectionException: Public Key Retrieval is not allowed\] with root cause

java.sql.SQLNonTransientConnectionException: Public Key Retrieval is not allowed

## 错误三：密码问题 ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) ##

笔者找到了一个大神的连接，完美解决了我的问题，连接：[https://blog.csdn.net/sinat\_31057219/article/details/84402896][https_blog.csdn.net_sinat_31057219_article_details_84402896]

先开一个cmd窗口A：

//开启MySQL
    C:\WINDOWS\system32>net start mysql
    MySQL 服务正在启动 ...
    MySQL 服务已经启动成功。
    
    //登陆报错
    C:\WINDOWS\system32>mysql -uroot -p
    Enter password:
    ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
    
    //关闭MySQL
    C:\WINDOWS\system32>net stop mysql
    MySQL 服务正在停止.
    MySQL 服务已成功停止。
    
    //无密码启动MySQL服务
    C:\WINDOWS\system32>mysqld --console --skip-grant-tables --shared-memory
    2018-11-22T14:06:27.964267Z 0 [System] [MY-010116] [Server] 
    D:\mysql-8.0.13-winx64\bin\mysqld.exe (mysqld 8.0.13) starting as process 2972
    2018-11-22T14:06:30.981556Z 0 [Warning] [MY-010068] [Server] 
    CA certificate ca.pem is self signed.
    2018-11-22T14:06:31.006330Z 0 [System] [MY-010931] [Server] 
    D:\mysql-8.0.13-winx64\bin\mysqld.exe: ready for connections. 
    Version: '8.0.13'  socket: ''  port: 0  MySQL Community Server - GPL.
    2018-11-22T14:06:31.209604Z 0 [Warning] [MY-011311] [Server] 
    Plugin mysqlx reported: 'All I/O interfaces are disabled, X Protocol won't be accessible'

再开一个cmd窗口B：

//无密码【登陆】（密码处直接enter）
    C:\WINDOWS\system32>mysql -uroot -p
    Enter password:
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 7
    Server version: 8.0.13 MySQL Community Server - GPL
    
    Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    //-------------------------------------------------免密码登陆设置空密码-------------------------------------------------------
    //设置【空密码】
    mysql> UPDATE mysql.user SET authentication_string='' WHERE user='root' and host='localhost';
    Query OK, 1 row affected (0.08 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    //刷新
    mysql> flush privileges;
    Query OK, 0 rows affected (0.06 sec)
    
    //查询
    mysql> select host,user,plugin,authentication_string from mysql.user;
    +-----------+------------------+-----------------------+------------------------------------------------------------------------+
    | host      | user             | plugin                | authentication_string                                                  |
    +-----------+------------------+-----------------------+------------------------------------------------------------------------+
    | localhost | mysql.infoschema | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
    | localhost | mysql.session    | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
    | localhost | mysql.sys        | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
    | localhost | root             | caching_sha2_password |                                                                        |
    +-----------+------------------+-----------------------+------------------------------------------------------------------------+
    4 rows in set (0.00 sec)
    
    //---------------------------------------------设置加密的密码---------------------------------------------
    //以caching_sha2_password加密密码并设置
    mysql> ALTER user 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'admin';
    Query OK, 0 rows affected (0.17 sec)
    
    //刷新
    mysql> flush privileges;
    Query OK, 0 rows affected (0.01 sec)
    
    //查询
    mysql> select host,user,plugin,authentication_string from mysql.user;
    +-----------+------------------+-----------------------+------------------------------------------------------------------------+
    | host      | user             | plugin                | authentication_string                                                  |
    +-----------+------------------+-----------------------+------------------------------------------------------------------------+
    | localhost | mysql.infoschema | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
    | localhost | mysql.session    | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
    | localhost | mysql.sys        | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
    | localhost | root             | caching_sha2_password | $A$005$r/r8"We_EpPb9584lw2cALUsOsvkB/hHg1qUqocVxDMMkFQ8RyQcXASZoff5 |
    +-----------+------------------+-----------------------+------------------------------------------------------------------------+
    4 rows in set (0.00 sec)

关闭A/B cmd窗口，再开一个cmd 窗口C

//登陆成功
    C:\WINDOWS\system32>mysql -uroot -padmin
    mysql: [Warning] Using a password on the command line interface can be insecure.
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 10
    Server version: 8.0.13 MySQL Community Server - GPL
    
    Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    //修改加密方式
    mysql> ALTER user 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'admin';
    Query OK, 0 rows affected (0.12 sec)
    
    //刷新
    mysql> flush privileges;
    Query OK, 0 rows affected (0.03 sec)
    
    //查询
    mysql> select host,user,plugin,authentication_string from mysql.user;
    +-----------+------------------+-----------------------+------------------------------------------------------------------------+
    | host      | user             | plugin                | authentication_string                                                  |
    +-----------+------------------+-----------------------+------------------------------------------------------------------------+
    | localhost | mysql.infoschema | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
    | localhost | mysql.session    | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
    | localhost | mysql.sys        | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
    | localhost | root             | mysql_native_password | *4ACFE3202A5FF5CF467898FC58AAB1D615029441                              |
    +-----------+------------------+-----------------------+------------------------------------------------------------------------+
    4 rows in set (0.00 sec)
    
    mysql>

其他常用cmd命令

//---------------------------------------常用语句------------------------------------------------------------
    //修改加密规则
    mysql> UPDATE mysql.user SET plugin='mysql_native_password' WHERE user='root';
    
    //查询
    mysql> select host,user,plugin,authentication_string from mysql.user;
    
    //对密码进行加密
    mysql> update mysql.user set password=password('admin') where user='root' and host='localhost';  
    
    //刷新
    flush privileges;
    
    //退出
    mysql> exit

## 错误四：Navicat for MySQL连接mysql-8.0.13，出现1251- Client does not support authentication protocol 的错误，如何解决？ ##

![20181114163659347.png][]

这个问题是上面的错误三引起的，之所以出现这个错误 ，是因为mysql-8以上版本默认使用 caching\_sha2\_password 身份验证机制，但Navicat for MySQL版本较低，不支持这种验证，支持的是mysql\_native\_password，当然就无法连接。

解决方法

把mysql用户登录密码加密规则还原成mysql\_native\_password.

步骤：

1.登陆数据库  
![20181114164748254.png][]

2.查看用户信息

select host,user,plugin,authentication\_string from mysql.user;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzM4MjU5NTM5_size_16_color_FFFFFF_t_70 3][]

plugin一列，身份验证机制果然是**caching\_sha2\_password** 。

又发现竟然没有root用户，明明是用root用户登陆的呀！

3、更改加密方式，密码123456

ALTER USER 'root'@'localhost' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER;

![20181114165017409.png][]

4.更改密码，密码是123456

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

![20181114165058829.png][]

5.刷新权限：

FLUSH PRIVILEGES;

6.再次查看用户信息

select host,user,plugin,authentication\_string from mysql.user;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzM4MjU5NTM5_size_16_color_FFFFFF_t_70 4][]

问题解决，可以连接上了！

总结：这次通过解决问题，慢慢的了解了数据库的登陆机制和密码机制，有所收获。

## 错误5：mysql连接报java.math.BigInteger cannot be cast to java.lang.Long异常，怎么解决 ##

引起这个问题是我用的MySQL版本是8.0.13，而我在项目中用的版本是5.6.36，将pom文件版本更改，

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

ok不抛这个问题了

特别注意：因为你更改了MySQL的版本，那么对应的jdbc连接也要一同更改。否则抛出：

Loading class \`com.mysql.jdbc.Driver'. This is deprecated. The new driver class is \`com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.  
按照最新按照最新官方提示支持将com.mysql.jdbc.Driver  改为  com.mysql.cj.jdbc.Driver，将driver class更改即可

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

## 错误6：java.sql.SQLException: 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. ##

我们只需要在访问数据库的Url后面加上以下的语句即可：

?serverTimezone=GMT%2B8  
比如，我原来的url是：jdbc:mysql://localhost:3306/xiaoma  
修改为：jdbc:mysql://localhost:3306/xiaoma?serverTimezone=GMT%2B8

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzM4MjU5NTM5_size_16_color_FFFFFF_t_70]: /images/20210920/081993d8349c49e7a7aac030dd84fd13.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzM4MjU5NTM5_size_16_color_FFFFFF_t_70 1]: /images/20210920/46e5315dca544e36881fa00af6137b6f.png
[20181226103532200.png]: /images/20210920/50dd096743bd4f798d4ba490fcdc98db.png
[20181226103607585.png]: /images/20210920/087c175a9fbb4e3ba0ad2127a301c9fb.png
[20181226103706554.png]: /images/20210920/3e4da51aaa5a41fbb1006b8a4062d3c7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzM4MjU5NTM5_size_16_color_FFFFFF_t_70 2]: /images/20210920/ce9e27ab99a141b08eae7c8bdcb33c93.png
[https_blog.csdn.net_sinat_31057219_article_details_84402896]: https://blog.csdn.net/sinat_31057219/article/details/84402896
[20181114163659347.png]: /images/20210920/ea8a18093605479588ce196e452b2997.png
[20181114164748254.png]: /images/20210920/0fbb421b3541409e8fc80e68eed3e411.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzM4MjU5NTM5_size_16_color_FFFFFF_t_70 3]: /images/20210920/78dcac8c42994f1cba2f6721004987a0.png
[20181114165017409.png]: /images/20210920/dbcda7158051480e96a9a227c389196f.png
[20181114165058829.png]: /images/20210920/793814885e4841d4a8bf84beef664e1b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzM4MjU5NTM5_size_16_color_FFFFFF_t_70 4]: /images/20210920/12a109232f6a4a1bb4749f6ca4def187.png