I'm trying to automate the execution of an application code at MySQL startup. The init-file server option seems handy and I need to know if mysqld waits for all the code in the init-file to complete before making the instance and the databases available.
I tried to set up a quick test case that doesn't seem to work as I expect:
create a simple test table and a procedure that just inserts a record
create table init_test_tab (x char); delimiter $$ drop procedure init_test $$ create procedure init_test(p char(1)) begin do sleep(300); insert into init_test_tab VALUES(p); END $$
call the procedure from an init-file:
$ cat /var/lib/mysql/mysql-init.sql call xxxx.init_test('y'); $ grep init /etc/mysql/my.cnf init-file = /var/lib/mysql/mysql-init.sql
Restart the server to verify the timings and check the content of the test table:
session 1 - the table is empty:
[Wed Nov 28 15:54:26 2012]> select * from init_test_tab; Empty set (0.00 sec)
session 2 - restart mysqld:
# time service mysql restart mysql stop/waiting mysql start/running, process 3031 real 0m10.116s user 0m0.024s sys 0m0.068s
Note that it takes just 10s to restart.
session 1 - connection lost (as expected):
[Wed Nov 28 15:54:33 2012]> select * from init_test_tab; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) ERROR: Can't connect to the server [Wed Nov 28 15:54:45 2012]> select * from init_test_tab; No connection. Trying to reconnect... Connection id: 2 Current database: xxxxx +------+ | x | +------+ | y | +------+ 1 row in set (0.01 sec) [Wed Nov 28 15:54:50 2012]>
Note that the procedure is invoked automatically (as expected) as the table is not empty anymore. But the insert should have been executed only after the sleep(300) call, not immediately (or, to be accurate, just in a few seconds).
So now I'm trying to understand:
- If I put a procedure call (or whatever application code) in the init-file, will it be executed as expected (in my test case the sleep call seems to be skipped)?
- Could I have some kind of logging of what gets executed (apart from modifying the code itself)?
- If the code in the init-file takes time, could that affect the availability of the MySQL instance right after the restart?