5

mysql几个常见错误

 3 years ago
source link: https://www.jianshu.com/p/64aaa783fd66
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

mysql几个常见错误

2020.11.05 17:24:32字数 339阅读 25

1 MySQL数据库远程连接很慢的解决方案

[mysqld]
skip-name-resolve

原因是由于mysql对连接的客户端进行DNS反向解析。

注意

在增加该配置参数后，mysql的授权表中的host字段就不能够使用域名而只能够使用 ip地址了，因为这是禁止了域名解析的结果。

2 MySQL远程连接不上

vim /etc/mysql/mysql.conf.d/mysqld.cnf

bind-address=127.0.0.1  139.196.197.138 0.0.0.1

msyql默认的bind-address是127.0.0.1

解决方法:bind-address后面增加远程访问IP地址或者禁掉。

3 乱码

查看配置是否字符集统一，不统一根据自行调整即可。

mysql> show variables like 'character_set_%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set

/etc/mysql/mysql.conf.d/mysqld.cnf
character-set-server=utf8

4 导入数据报错

1153 - Got a packet bigger than 'max_allowed_packet' bytes

MySQL默认读取执行的SQL文件最大为16M

1 临时解决方案：

set global max_allowed_packet = 210241024*10
show VARIABLES like ‘%max_allowed_packet%’;

2 更改配置项（my.cnf）

 [mysqld]
 max_allowed_packet=400M

5 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and

完整提示如下：

5 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

only_full_group_by的语义就是确定select target list中的所有列的值都是明确语义，在此模式下，target list中的值要么是来自于聚合函数（sum、avg、max等）的结果，要么是来自于group by list`中的表达式的值。

1 可以修改sql_mode

-- 查看SQL_MODE
SELECT @@sql_mode;
-- 修改SQL_MODE
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

如果是只查询某个字段出现可以使用any_value函数来抑制ONLY_FULL_GROUP_BY值被拒绝.


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK