Solve ERROR 1231 (42000): Variable’time_zone’ can’t

Leave a reply

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:

code show as below:
Copy code
show VARIABLES like  ' %max_allowed_packet% ' ;

The results displayed are:

code show as below:
+——————–+———+
| Variable_name | Value |
+———— ——–+———+
| max_allowed_packet | 1048576 |
+——————–+—- —–+

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.

code show as below:
max_allowed_packet = 20M

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

code show as below:
mysql –help | grep my.cnf

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.

Read More:

Leave a Reply

Your email address will not be published. Required fields are marked *