According to the configuration file, MySQL limits the size of the data packets accepted by the Server. Sometimes large inserts and updates are limited by the max_allowed_packet parameter, causing write or update failures. (For example, import database, data table)

The following errors appear in the process of migrating or restoring the mysql database:

ERROR 1231 ( 42000 ): Variable ' time_zone ' can ' t be set to the value of ' NULL ' 
ERROR 1231 (42000): Variable ' sql_mode ' can ' t be set  to the value of  ' NULL ' 
ERROR 1231 ( 42000 ): Variable ' foreign_key_checks ' can ' t be set to the value of ' NULL ' 
ERROR 1231 (42000): Variable 'unique_checks ' can ' t be set  to the value of  ' NULL ' 
ERROR 1231 ( 42000 ): Variable ' character_set_client ' can ' t be set to the value of ' NULL '
Query OK, 0 rows affected (0.00 sec)
ERROR 1231 (42000): Variable ' collation_connection ' can ' t be set  to the value of  ' NULL ' 
ERROR 1231 ( 42000 ): Variable ' sql_notes ' can ' t be set to the value of ' NULL '

Solution:

Modify the mysql configuration file: max_allowed_packet=1024M

View the current configuration:

show VARIABLES like  ' %max_allowed_packet% ' ;

The results displayed are:

The above description shows that the current configuration is: 1M

Modification method

1. Modify the configuration file

You can edit my.cnf to modify (my.ini under windows), and modify it in the [mysqld] section or the mysql server configuration section.

If you can’t find my.cnf, you can pass

Look for the my.cnf file.
The file is under /etc/ under linux.

Restart mysql:

1. Start with service: service mysqld restart
2. Start with mysqld script: /etc/inint.d/mysqld restart

Check whether max_allowed_packet is successfully edited

Note: Setting this value too small will cause a single record to fail to write to the database after exceeding the limit, and subsequent record writing will also fail.

Laradock is a complete PHP local development environment provided by Docker

Error when connecting to MySQL in the framework

SQLSTATE[HY000] [2002] Connection refused

The main reason is that there is not enough understanding of the isolation mechanism of Docker containers. Each container is isolated. If there are interdependent services, it is necessary to perform display associations, such as using options --link.
In the same way, docker-composewhen using , the association between containers is similar to the following method:

# docker-compose.xml basic
version: '2'
services:
	
	...
	
    php:
        build: ./php
        
        ...
        
        links:
            - "mysql"
           
    mysql:
        build: ./mysql
        ports:
            - "3306:3306"
        environment:
            MYSQL_PASSWORD: root

Note that the key point is here: the code to test the connection to MySQL is actually running in the container corresponding to PHP, and the MySQL service is in its own container. When our host fills in 127.0.0.1, it actually corresponds to the PHP container. Inside, so it is impossible to find the corresponding service, which causes the above connection refused error.

So, how do you connect?

In fact, after the containers are associated, they can be connected by the container name.

In the above docker-compose.xmldocument, the container corresponds to the service name MySQL mysql, PHP container name is associated with it mysql, so the 127.0.0.1change mysqland then connect to.

# thinkphp project modify database.php file
'hostname'        => 'mysql',

# Laravel project modify .env file
DB_HOST=mysql

This is because the current database version is higher and some parameters need to be changed

Solution:

In the sql file

utf8mb4_0900_ai_ciReplace utf8_general_ci
utf8mb4withutf8

Run the SQL file again

Cause of error

The default value incompatibility problem caused by mysql5.7 version, the same problem may also occur in mysql8.0.

The problematic values ​​are:
NO_ZERO_IN_DATE
In strict mode, the date and month are not allowed to be zero.

NO_ZERO_DATE
Set this value, mysql database does not allow the insertion of a zero date, and inserting a zero date will throw an error instead of a warning.

ONLY_FULL_GROUP_BY
For the GROUP BY aggregation operation, if the column in the SELECT does not appear in the GROUP BY, then this SQL is illegal because the column is not in the GROUP BY clause.

Solution

method one

Execute select @@sql_mode, copy the value of the query and delete NO_ZERO_DATE, and then execute set sql_mode = ‘modify number’.

This method only works in the current session

Way two

Execute select @@global.sql_mode, copy the value of the query and delete NO_ZERO_DATE, and then execute set global sql_mode = ‘modify number’.

This method takes effect in the current service, and it becomes invalid after restarting the MySQL service

Method Three

In the mysql installation directory, open the my.ini or my.cnf file. Under wamp, SQL_MODE is not set in MySQL 5.7.

1. Found in my.ini file [mysqld]

2. If there is no SQL_MODE, add it, and modify it if it has

sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

or

sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Finally restart MySQL

service mysqld restart