Does mysqld wait for init-script code to complete before opening the instance?

Question

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:

1. 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)?
2. Could I have some kind of logging of what gets executed (apart from modifying the code itself)?
3. If the code in the init-file takes time, could that affect the availability of the MySQL instance right after the restart?

Thanks Dimitre

Answer 1

Trying to Understand #1

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)?

Yes, it will. In the past, I have recommended using init-file to create dedicated keycaches for MyISAM tables:

• Aug 28, 2012 : MySQL InnoDB Index in swap (See Suggestion #3)
• Mar 20, 2012 : optimal table design mysql with primay key and varchar value

I am currently do this for clients at my employer's company.

Trying to Understand #2

Could I have some kind of logging of what gets executed (apart from modifying the code itself)?

Unfortunately, no. You would have one of the following:

1. Write changes to some MyISAM table to mark stages
2. Run something like SELECT 'DEBUG#1;' ... SELECT 'DEBUG#2;' to see if it echoes to the error file. Place these SELECT queries in different places within your code.

Trying to Understand #3

If the code in the init-file takes time, could that ? affect the availability of the MySQL instance right after the restart

Just from the example you posted in the question, they answer would still be no. However, in order for to be sure, you will have to let mysqld tell you this. Assuming /var/log/mysqld.log is your error log, here is how to check:

• Open an SSH seesion (Session1) to DB Server
• Open an SSH seesion (Session2) to DB Server
• In Session1, service mysql stop
• In Session1, tail -f /var/log/mysqld.log
• In Session2, service mysql start

Watch the output of Session1. If you see something like

121018 17:59:09 [Note] /usr/sbin/mysqld: ready for connections.

in under 5 minutes (300 seconds), this then confirms that the answer is no, the availability of the MySQL instance right after the restart is not affected.

Why does SLEEP() not work ???

As to why the SLEEP function seems to be skipped, here is my best guess:

Way back in MySQL 5.0.12, SLEEP was getting inserted into the query cache via some optimization. It could be possible that the Stored Procedure's internal code is being optimized and mysqld decided not to let SLEEP interfere with the formation of the EXPLAIN plan and its execution.

The patch for it was applied when back in MySQL 5.0. Could that patch have been missed when going from MySQL 5.0 to MySQL 5.1 and beyond? That's one possibility. Another possibility is that SLEEP is optimized away in spite of the patch.

Some use SLEEP to imbed mutexing, perform stress testing, or fancy job scheduling

• https://stackoverflow.com/q/4284524/491757
• https://stackoverflow.com/q/949950/491757
• http://forums.mysql.com/read.php?98,242758,242792#msg-242792
• http://rpbouman.blogspot.com/2005/10/scheduling-procedure-execution-in.html

Using SLEEP does not always perform as expected.

UPDATE 2012年11月27日 15:50 EDT

If you really want to control the SLEEP function, DO NOT USE THE FUNCTION !!!

Create a loop that checks for the passing of time

Instead of your code

delimiter $$
drop procedure init_test $$
create procedure init_test(p char(1))
begin
 do sleep(300);
 insert into init_test_tab VALUES(p);
END
$$

imbed the loop like this:

delimiter $$
drop procedure init_test $$
create procedure init_test(p char(1))
begin
 declare dt_now,dt_then datetime;
 declare x int;
 set dt_now = now();
 set dt_then = dt_now + interval 300 second;
 while dt_now < dt_then do
 set dt_now = now();
 end while;
 insert into init_test_tab VALUES(p);
END
$$

Please see if that construct work for you.

• Dimitre Radoulov: Hi @RolandoMySQLDBA, thanks for replying! Do you have an idea why the sleep call seems to be skipped in the test case I posted?
• Dimitre Radoulov: Unfortunately the output from the statements in the init-file don't appear in the error log.
• Dimitre Radoulov: Note that the sleep seems to be skipped only when invoking the procedure using the init-file. It works just fine When I invoke the procedure interactively (using the mysql cli).
• RolandoMySQLDBA: As far as calling the SLEEPfunction goes, I would expect mysqld to handle certain operations differently when performing internal/intrinsic functionality such as 1) Triggers, 2) Slave Threading, 3) Event Scheduling. Calling SLEEP has no useful purpose. In fact, I have never seen SLEEP replicated to a Slave although SLEEP can be caught in a slow query log.
• Dimitre Radoulov: Rephrasing the question: why calling a procedure via init-script behaves differently compared to manual invocation?