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?
Thanks
Dimitre
Best Answer
Trying to Understand #1
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 valueI am currently do this for clients at my employer's company.
Trying to Understand #2
Unfortunately, no. You would have one of the following:
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
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:service mysql stop
tail -f /var/log/mysqld.log
service mysql start
Watch the output of Session1. If you see something like
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 schedulingUsing
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
imbed the loop like this:
Please see if that construct work for you.