GVKun编程网logo

Docker 之 MySql5.7 中 only_full_group_by 的问题解决（docker mysql volume）

18

对于想了解Docker之MySql5.7中only_full_group_by的问题解决的读者，本文将提供新的信息，我们将详细介绍dockermysqlvolume，并且为您提供关于Docker中修改

对于想了解Docker 之 MySql5.7 中 only_full_group_by 的问题解决的读者，本文将提供新的信息，我们将详细介绍docker mysql volume，并且为您提供关于Docker 中修改Mysql 数据库的 only_full_group_by 模式、Linux服务器MySQL5.7版本only_full_group_by问题、mac os下 mysql7 ONLY_FULL_GROUP_BY 问题 临时解决方法、Mac 的mysql5.7没有配置文件，如何解决only_full_group_by 问题的有价值信息。

本文目录一览：

Docker 之 MySql5.7 中 only_full_group_by 的问题解决（docker mysql volume）

Docker 之 MySql5.7 中 only_full_group_by 的问题解决（docker mysql volume）

    MySql 的镜像，默认情况下，MySql5.7 中的 sql_mode 含有 only_full_group_by，group by 语句有时候会报错。通过手动修改 sql_mode，那么如果删除容器或者新建容器，就会导致我们手动设置的 sql_mode 失效，所以自己制作一个基于 mysql 镜像的镜像，解决 sql_mode 含有 only_full_group_by 的问题。

运行容器

    List-1

mjduan@mjduan:/opt % docker run -d -p 3306:3306 -e MYSQL_ALLOW_EMPTY_PASSWORD --name mysql1 mysql:5.7.9
5514c31a4e0bc524cee3cdcb962ac73b4fdeb1b5b32d70fa2840e9029b203a8c

进入容器安装 vim

    List-2

mjduan@mjduan:/opt % docker exec -ti mysql1 /bin/bash
#在容器内执行如下命令
root@44504961189a:/opt % apt-get update
......
root@44504961189a:/opt % apt-get install vim
......

    在容器内，/etc/mysql/conf.d/ 下，新建.cnf 文件，将配置写入到里面，我们新建 custom.cnf 文件，写入如下内容:

    List-3

root@44504961189a:/# more /etc/mysql/conf.d/custom.cnf
[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

    退出容器，用 docker commit 命令由容器制作镜像，

    List-4

mjduan@mjduan:/opt % docker commit mysql1 mysql_custom:1.0

    之后 docker images 就可以看到镜像 mysql_custom:1.0 了。

     mysql_custom:1.0 就是我们需要的，用它来启动容器，如下 List-5

    List-5

mjduan@mjduan:/opt % docker run -d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=新密码 --name mysql_mjduan2 mysql_custom:1.0
44504961189a45442a6b33e5945778b73bc3dd058ab9e794c56b0bbfc3e603bf

    之后进入容器 mysql_mjduan2，用 mysql 命令进入 mysql 命令行时就会提示要 root 密码了。再次查看 sql_mode，就会看到 sql_mode 没有 only_full_group_by 了。

    注意，不要让别人拿到你的镜像，否则通过 docker inspect 命令就可以看到你设置的 root 密码。不过可以在创建容器的时候用 MYSQL_ROOT_PASSWORD 来设置新的 root 密码。

注:

    也可以直接用 Dockerfile 基于 mysql 的镜像进行制作。

 

Docker 中修改Mysql 数据库的 only_full_group_by 模式

Docker 中修改Mysql 数据库的 only_full_group_by 模式

执行查询，更新等 sql 时，会出现下面的提示：

[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

产生的原因是：5.7版本的数据库设置了 only_full_group_by 模式
解决方式只有一种，只有一种，只有一种，修改配置文件，但是在docker中的数据库会比普通的难改一些，需要多出几步。

docker images : 列出本地镜像
docker exec -it MysqL-sucai bash 进入MysqL 镜像
现在就进入容器的命令行模式
MysqL -u -root -p 登录进入MysqL
cat /etc/MysqL/MysqL.conf.d/MysqLd.cnf 查看MysqL容器的配置文件
在最后加入一行命令，需要使用vi命令，可能虚拟机中没有装，可以按一下方式进行安装：安装Vi:apt-get install vim，如果提示：Unable to locate package vim，则需要敲：apt-get update。等更新完毕以后再敲命令： apt-get install vim
vi /etc/MysqL/MysqL.conf.d/MysqLd.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

在这里插入图片描述

退出docker：exit
sudo docker stop ??? ，指定容器名字，关闭 MysqL
sudo docker start ??? ，指定容器名字，启动 MysqL

Linux服务器MySQL5.7版本only_full_group_by问题

Linux服务器MySQL5.7版本only_full_group_by问题

mysql5.7的版本设置了 mysql sql_mode=only_full_group_by 这一属性，所以导致了错误。

查看mysql版本

select version();

查看sql_mode

select @@global.sql_mode;

查询出来的值为：

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

基本可以确定问题only_full_group_by引起，在此推荐修改配置文件，这样即使MySQL服务器重启也没问题：

1、在linux找到文件

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

2、末尾添加如下语句

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

3、重新启动MySQL服务

service msyql restart


 

mac os下 mysql7 ONLY_FULL_GROUP_BY 问题 临时解决方法

mac os下 mysql7 ONLY_FULL_GROUP_BY 问题 临时解决方法

解决办法就是把ONLY_FULL_GROUP_BY去掉...

步骤

操作以下步骤的时候, 先把数据库服务停

clipboard.png

找到配置文件示例

clipboard.png

// 我的处理方法是比较安全的, 尽量避免使用命令
// 输入命令,打开文件夹,复制my-default.cnf到桌面
mao$ sudo open /usr/local/mysql/support-files/

将文件重命名为 my.cnf, 然后拷贝到etc/目录下, 重新启动mysql就可以了

 For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It''s a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M


# 认真看, 这里配置不包含ONLY_FULL_GROUP_BY
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

Mac 的mysql5.7没有配置文件，如何解决only_full_group_by 问题

Mac 的mysql5.7没有配置文件，如何解决only_full_group_by 问题

图片描述

数据库版本是5.7.19，在写语句的时候，只要涉及ORDER BY,就会报错，

ERROR 1055 (42000): Expression #7 of SELECT list is not in GROUP BY clause and contains nonaggregated column ''postscan.verifyDelayLog.auditor'' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

这个时候，百度发现，是因为这个版本的mysql 数据库默认开启了 sql_mode 字段的only_full_group_by 属性。这个属性是在你写语句时，当你 ORDER BY 的字段不在select 的字段当中，都会报错。

sql_mode 属性是在mysql数据库，event表中，
图片描述
点进去，可以看到默认属性是

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

解决这个问题，就需要修改这个字段的属性。

方法一
在navicat 里面直接针对event表进行修改

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 '';

这样就能去掉only_full_group_by 字段。但弊端是这个方法是针对session的，也就是说每次连接mysql 前，都需要set一次。

方法二
修改配置mysql的文件
mac在安装这个版本的mysql的时候，我没有发现mysql 的配置文件。不管是
/etc 下还是 mysql 的support-files 下都没有

图片描述

这个时候，可以自己在etc目录下创建一个

关闭mysql

sudo vim /etc/my.cnf

然后往文件里写这个配置就ok。

# Example MySQL config file for medium systems.  
  #  
  # This is for a system with little memory (32M - 64M) where MySQL plays  
  # an important part, or systems up to 128M where MySQL is used together with  
  # other programs (such as a web server)  
  #  
  # MySQL programs look for option files in a set of  
  # locations which depend on the deployment platform.  
  # You can copy this option file to one of those  
  # locations. For information about these locations, see:  
  # http://dev.mysql.com/doc/mysql/en/option-files.html  
  #  
  # In this file, you can use all long options that a program supports.  
  # If you want to know which options a program supports, run the program  
  # with the "--help" option.  
  # The following options will be passed to all MySQL clients  
  [client]
  default-character-set=utf8
  #password   = your_password  
  port        = 3306  
  socket      = /tmp/mysql.sock   
  # Here follows entries for some specific programs  
  # The MySQL server  
  [mysqld]
  character-set-server=utf8
  init_connect=''SET NAMES utf8
  port        = 3306  
  socket      = /tmp/mysql.sock  
  skip-external-locking  
  key_buffer_size = 16M  
  max_allowed_packet = 1M  
  table_open_cache = 64  
  sort_buffer_size = 512K  
  net_buffer_length = 8K  
  read_buffer_size = 256K  
  read_rnd_buffer_size = 512K  
  myisam_sort_buffer_size = 8M  
  character-set-server=utf8
  sql_mode=''NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT,ANSI_QUOTES''  
  init_connect=''SET NAMES utf8'' 
# Don''t listen on a TCP/IP port at all. This can be a security enhancement,  
# if all processes that need to connect to mysqld run on the same host.  
# All interaction with mysqld must be made via Unix sockets or named pipes.  
# Note that using this option without enabling named pipes on Windows  
# (via the "enable-named-pipe" option) will render mysqld useless!  
#   
#skip-networking  

  # Replication Master Server (default)  
  # binary logging is required for replication  
  log-bin=mysql-bin  

    # binary logging format - mixed recommended  
    binlog_format=mixed  

      # required unique id between 1 and 2^32 - 1  
      # defaults to 1 if master-host is not set  
      # but will not function as a master if omitted  
      server-id   = 1  

    # Replication Slave (comment out master section to use this)  
    #  
    # To configure this host as a replication slave, you can choose between  
    # two methods :  
    #  
    # 1) Use the CHANGE MASTER TO command (fully described in our manual) -  
    #    the syntax is:  
    #  
    #    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,  
    #    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;  
    #  
    #    where you replace <host>, <user>, <password> by quoted strings and  
    #    <port> by the master''s port number (3306 by default).  
    #  
    #    Example:  
    #  
    #    CHANGE MASTER TO MASTER_HOST=''125.564.12.1'', MASTER_PORT=3306,  
    #    MASTER_USER=''joe'', MASTER_PASSWORD=''secret'';  
    #  
    # OR  
    #  
    # 2) Set the variables below. However, in case you choose this method, then  
    #    start replication for the first time (even unsuccessfully, for example  
    #    if you mistyped the password in master-password and the slave fails to  
    #    connect), the slave will create a master.info file, and any later  
    #    change in this file to the variables'' values below will be ignored and  
    #    overridden by the content of the master.info file, unless you shutdown  
    #    the slave server, delete master.info and restart the slaver server.  
    #    For that reason, you may want to leave the lines below untouched  
    #    (commented) and instead use CHANGE MASTER TO (see above)  
    #  
    # required unique id between 2 and 2^32 - 1  
    # (and different from the master)  
    # defaults to 2 if master-host is set  
    # but will not function as a slave if omitted  
    #server-id       = 2  
    #  
    # The replication master for this slave - required  
    #master-host     =   <hostname>  
    #  
    # The username the slave will use for authentication when connecting  
    # to the master - required  
    #master-user     =   <username>  
    #  
    # The password the slave will authenticate with when connecting to  
    # the master - required  
    #master-password =   <password>  
    #  
    # The port the master is listening on.  
    # optional - defaults to 3306  
    #master-port     =  <port>  
    #  
    # binary logging - not required for slaves, but recommended  
    #log-bin=mysql-bin  

      # Uncomment the following if you are using InnoDB tables  
      #innodb_data_home_dir = /usr/local/mysql/data  
      #innodb_data_file_path = ibdata1:10M:autoextend  
      #innodb_log_group_home_dir = /usr/local/mysql/data  
      # You can set .._buffer_pool_size up to 50 - 80 %  
      # of RAM but beware of setting memory usage too high  
      #innodb_buffer_pool_size = 16M  
      #innodb_additional_mem_pool_size = 2M  
      # Set .._log_file_size to 25 % of buffer pool size  
      #innodb_log_file_size = 5M  
      #innodb_log_buffer_size = 8M  
      #innodb_flush_log_at_trx_commit = 1  
      #innodb_lock_wait_timeout = 50  

        [mysqldump]  
        quick  
        max_allowed_packet = 16M  

          [mysql]  
          no-auto-rehash  
          # Remove the next comment character if you are not familiar with SQL  
          #safe-updates  
          default-character-set=utf8   

        [myisamchk]  
        key_buffer_size = 20M  
        sort_buffer_size = 20M  
        read_buffer = 2M  
        write_buffer = 2M  

          [mysqlhotcopy]  
          interactive-timeout

今天的关于Docker 之 MySql5.7 中 only_full_group_by 的问题解决和docker mysql volume的分享已经结束，谢谢您的关注，如果想了解更多关于Docker 中修改Mysql 数据库的 only_full_group_by 模式、Linux服务器MySQL5.7版本only_full_group_by问题、mac os下 mysql7 ONLY_FULL_GROUP_BY 问题 临时解决方法、Mac 的mysql5.7没有配置文件，如何解决only_full_group_by 问题的相关知识，请在本站进行查询。

本文标签：

上一篇Zeroc Ice 学习笔记 --Docker 部署服务（docker部署odoo）

下一篇使用 Docker 部署 Django + MySQL 8 开发环境（docker部署mysql8.0）

相关文章