MySQL Bugs: #15709: init-connect does not work

Bug #15709	init-connect does not work
Submitted:	13 Dec 2005 9:38	Modified:	23 Dec 2005 11:33
Reporter:	Dmitry Koterov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.12a-max	OS:	Microsoft Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
Directive init-connect in my.cnf and --init-connect in command line do not work.

P.S.
As a result, I cannot use 
init-connect = "set names cp1251"

How to repeat:
To test it I prepared test table:

use mysql;
CREATE TABLE `test` (
  `id` int(11) NOT NULL auto_increment,
  `test` text NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 AUTO_INCREMENT=6 ;

Then I modify my.cnf:

init-connect = "insert into mysql.test set test=current_timestamp()"

or even:

init-connect = "insert into mysql.test set test=current_timestamp(); commit;"

When I call "insert into mysql.test set test=current_timestamp()" in MySQL console, it inserts one line in mysql.test. OK.

Then I restart MySQL server and do:

show variables like 'init%'

It says that init-connect is OK. Now I connect to the server (e.g. from PHP script) under root (permissions are enough!) and see that there is no new rows in mysql.test.

Thank you for a problem report. First of all, can you, please, try to upgrade your server to 4.1.15. Your version is really old.

Then, please, read the manual (http://dev.mysql.com/doc/refman/4.1/en/server-system-variables.html) carefully:

"Note that the content of init_connect  is not executed for users having the SUPER privilege; this is in case that content has been wrongly set (contains a wrong query, for example with a syntax error), thus making all connections fail. Not executing it for SUPER users enables those to open a connection and fix init_connect."

So, it simply does not work by design for, say, root user. For other users with the appropriate privileges on test table it should work. I've tested on latest 5.0.18-BK, but I see no reason for it to not work on new versions of 4.1.x too:

[openxs@Fedora 5.0]$ bin/mysqld_safe &
[1] 1859
[openxs@Fedora 5.0]$ Starting mysqld daemon with databases from /home/openxs/dbs/5.0/var

[openxs@Fedora 5.0]$ bin/mysql -uuser1 -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.18

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from test;
ERROR 1142 (42000): SELECT command denied to user 'user1'@'localhost' for table 'test'
mysql> exit
Bye
[openxs@Fedora 5.0]$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.18

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> grant select, insert on test to user1;
Query OK, 0 rows affected (0,02 sec)

mysql> exit
Bye
[openxs@Fedora 5.0]$ bin/mysql -uuser1 -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.18

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from test;
Empty set (0,00 sec)

mysql> exit
Bye
[openxs@Fedora 5.0]$ bin/mysqladmin -uroot shutdown
STOPPING server from pid file /home/openxs/dbs/5.0/var/Fedora.pid
051213 14:38:11  mysqld ended

[1]+  Done                    bin/mysqld_safe
[openxs@Fedora 5.0]$ bin/mysqld_safe --defaults-file=/home/openxs/dbs/5.0/my.cnf.old &
[1] 1917
[openxs@Fedora 5.0]$ Starting mysqld daemon with databases from /home/openxs/dbs/5.0/var

[openxs@Fedora 5.0]$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.18

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select * from test;
Empty set (0,00 sec)

mysql> exit
Bye
[openxs@Fedora 5.0]$ bin/mysql -uuser1 -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.18

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from test;
+----+---------------------+
| id | test                |
+----+---------------------+
|  6 | 2005-12-13 14:39:12 |
+----+---------------------+
1 row in set (0,00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.18    |
+-----------+
1 row in set (0,00 sec)

mysql> exit
Bye
[openxs@Fedora 5.0]$ cat my.cnf.old
[mysqld]
init_connect = 'insert into test.test(test) values (current_timestamp())'

So, please, check once more.

Yes, you are right! The problem was because of root user connect.

Actually, I try to set:

init-connect = "set names cp1251"

to force all connections (e.g. from PHP5 scripts) useing cp1251 encoding by default. Now I see that it works fine IF I connect under non-root. But under root it still returns wrong charset (latin1 by default).

PLEASE ANSWER: is there no other possibility to force cp1251 to be default client charset for all connection (INCLUDING connections under root)? 

P.S.
Please do not offer to recompile MySQL nor modify PHP scripts adding mysql_query("SET NAMES cp1251"). First is too non-universal; second is incorrrect when using "standard" popular scripts (e.g. phpBB) which must work under MySQL4.1 the same as under MySQL3 and MySQL4.0; a lot of other software has the same compatibility problem between MySQL 4.1 and MySQL3/4.

So, --init-connect works just as described, and this is not a bug.

As for your question: 

"Is there no other possibility to force cp1251 to be default
client charset for all connection (INCLUDING connections under root)?" 

to the ideas you already proposed I can add only one: get rid of SUPER priviledge for user "root", and use --init-connect. 

It is not a good idea to connect as root using anything but phpmyadmin, anyway.

Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 
http://www.mysql.com/downloads/

Additional info:

Because this PHP<->MySQL problem was too common, we implemented a workaround,
see --skip-character-set-client-handshake command-line option, available since MySQL-4.1.15