2

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'

The above are the errors that I'm getting after importing my freshly dumped database from Bluehost (done through phpmyadmin, both with quick and with custom export methods).

Info Bluehost versions:
MySQL 5.5.42
PHP 5.4.24
Apache 2.2.31
phpMyAdmin 3.5.8.2

Local setup (no MAMP, XAMP, WAMP or LAMP) versions:
MySQL 5.5.25 (found through command line: mysql which version)
PHP 5.5.27
Apache 2.4.16
phpMyAdmin 4.5.1

Could it be related to incompatibility of versions? Or do you think the origin of this problem lies elsewhere? Also, once logged in to MySQL I cannot use mysqldump but if I'm not logged in, I can run this type of command:

mysqldump -u [USERNAME] -p [DBNAME] | gzip > [/path_to_file/DBNAME].sql.gz

Is that normal?

Anyways, I believe you can see how a newbee I am here so please bear with me.

2 Answers 2

4

It has to do with the max_allowed_packet on the MySQL server. It was set to 16M and when I upped it to 64M in my.cnf located in /private/etc/ (on Mac El Capitan) which was a copy of the my-huge.cnf. After stopping the MySQL server and Apache and restarting them made my site work locally again and the error messages went away.

1
  • and also run the mysql client with the --max_allowed_packet=64M option! Nov 3, 2016 at 8:30
0

Set the values for the variables character_set_client and collation_connection as follows at the begining of the dump file. If not exist add new

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHAR */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLL */;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.