Monday 11 January 2016

MTR Automation on MySQL Bootstrap options

This blog talks about how to write a MTR testcase for the system variables especially the static variables (mentioned below ). These static variables should only provide either from my.cnf file or from command prompt or if user do not provide, then MTR would take it as default values during the bootstrap mode.
in order to check/test effectiveness of these variables , one should provide same config file during the server startup time too.

This blog also talks about how to handle two different DB instances with different bootstrap parameters within the MTR testcase.

Here the system variables means, only the static variables,which remains constant during bootstrap (DB initialization) and server startup time.

The following are the sample static/bootstrap variables,the rest you can find from reference manual.

innodb_data_home_dir
innodb_undo_directory
innodb_undo_tablespaces

General approach :
The following is the way to pass from MTR command line prompt :

./mtr -{mysqld,bootstrap}=--innodb_undo_directory=/work/wl7806/mysql/mysql-test/DB1 -{mysqld,bootstrap}=--innodb_undo_tablespaces=5 sample.test

Assume a testcase sample.test has following lines:
SELECT @@innodb_undo_directory;
SELECT @@innodb_undo_tablespaces;

from the above approach , following are the consequence.

1.One has to add the above mentioned command, in the collection files for each of the MTR testcase, which is a very tedious job. And also one cannot provide hard-coded paths for any parameters.
2. MTR does not support *.opt file option from bootstrap mode.It only for server startup time.

To overcome this problem , here are the steps, to write a MTR testcase :

Steps to write a MTR testcase  :
1. create necessary folders
2. Stop the running default server. This will become no use.
3. Prepare new bootstrap command
4. Start the server with same system variables

Like wise , you can manage to create as many DB instances as you can in your test environment, you can kill and restart whatever DB instance server as you can , it will be like, you have the total control on your testcase.

Note:
1.MTR do not like these kind of testcases, but you can achieve your goal , by making suppress some warnings/errors/cleanups.
2.MTR does no create some default schema's like mysq,information_schema etc.

Following is the sample MTR testcase to check status variables.

Sample.test:
let $MYSQLD_BASEDIR= `select @@basedir`;

# Step 1: create custom datadir & undo files
--mkdir $MYSQL_TMP_DIR/innodb_undo_data_dir1
--mkdir $MYSQL_TMP_DIR/datadir1

let BOOTSTRAP_SQL=$MYSQL_TMP_DIR/boot.sql;

--echo # create bootstrap file
write_file $BOOTSTRAP_SQL;
CREATE DATABASE test;
EOF

# Set paths for --datadir & --innodb_undo_directory
let $MYSQLD_DATADIR1 = $MYSQL_TMP_DIR/datadir1/data;
let $MYSQLD_UNDO_DATADIR1 = $MYSQL_TMP_DIR/innodb_undo_data_dir1;

# Create a bootstrap file in temp location
--exec echo $MYSQLTEST_VARDIR/tmp/bootstrap.log

# Step2: Stop server & make it unuse
--source include/shutdown_mysqld.inc

# Step3: Prepare bootstrap command
let NEW_CMD = $MYSQLD --no-defaults  --initialize-insecure --lc_messages_dir=$MYSQL_SHAREDIR --innodb_undo_directory=$MYSQLD_UNDO_DATADIR1 --innodb_undo_tablespaces=5 --basedir=$MYSQLD_BASEDIR --datadir=$MYSQLD_DATADIR1 --init-file=$BOOTSTRAP_SQL --skip-grant-tables</dev/null>>$MYSQLTEST_VARDIR/tmp/bootstrap.log 2>&1;

# Run the bootstrap command
--exec $NEW_CMD

# Step4: Start the server 
--let $restart_parameters="restart: --innodb_undo_tablespaces=5
--innodb_undo_directory=$MYSQLD_UNDO_DATADIR1 --datadir=$MYSQLD_DATADIR1"
--source include/start_mysqld.inc

USE test;

# Check the values of static variables
SELECT @@innodb_undo_tablespaces;
SELECT @@innodb_undo_directory;
SELECT @@datadir