Screwed up MySQL init_connect

Question

I executed an incorrect SQL command:

SET GLOBAL init_connect='SET CHARACTER SET=utf-8';

Now, when I try and run a script that connects to this database, I get the following error: _mysql_exceptions.OperationalError: (2006, 'MySQL server has gone away').

How would I reset the global init_connect values?

Answer 1

You need to place init_connect in /etc/my.cnf

[mysqld]
init_connect='SET CHARACTER SET=utf-8';

This will set the variable for you after the next mysql restart.

If your username has the SUPER privilege, you need not set the variable again dynamically since you already set globally earlier with SET GLOBAL init_connect='SET CHARACTER SET=utf-8';

As for 'MySQL server has gone away', the most common cause is timing out. Please set wait_timeout and interactive_timeout to something sufficiently large (default 28800 sec [8 hours]). Other reasons are specified in the MySQL Documentation.

UPDATE 2012-04-22 01:24 EDT

Since you are changing the character set to process multibyte character strings, you may need to make sure the MySQL Packet is bigger (at least triple whatever the configuration is now). I would bump it up to 256M

[mysqld]
init_connect='SET CHARACTER SET=utf-8';
max_allowed_packet=256M

I recommend this because sometimes Error 2006 : 'MySQL server has gone away' is caused by the MySQL Packet not being big enough. I wrote posts about this earlier:

• MySQL query 'going away' on executing INSERT ON DUPLICATE UPDATE statement with a 12524 character blob (Nov 22, 2011)
• Fixing MySQL errors recorded in error logs (Sep 25, 2011)
• MySQL server has gone away obstructing import of large dumps (Sep 01, 2011)