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

MTR test Automation Techniques

In this blog i am going to talk about couple of techniques how to automate certain MTR testcases,

these techniques are really underlying inside the suites and they never got exposed much to the test members. In case he/she wants to make use of these techniques for writing MTR testcase. One should really invest time and search from the existing suites.


My effort here is to bring up such techniques and consolidate them as a single reference for the end user.I will also keep updating this blog whenever i find something interesting.

The Viewers also can keep posting to this blog.

Technique 1:


The following sample code describes how to pass a value from MTR to perl

#Set the value of the variable in MTR
let  x=100;

#Get the value into perl & print
perl;

my $var1 = $ENV{'x'};
print "var1 = $var1 \n";

EOF

Technique 2:


The following sample code describes how to pass a value vice versa (Perl to MTR).Here it is not straight forward like above mentioned, hence store the value into a temp file and use source command to bring into MTR, and finally cleanup the temp file.

#Set the value of variable in the perl
perl;
 my $dir = $ENV{'MYSQLTEST_VARDIR'};
 open ( OUTPUT, ">$dir/tmp/tar.inc") ;
 print OUTPUT "let \$stat = 100;\n";
 close (OUTPUT);
EOF

#Get the value of the variable from to MTR, from perl
--source  $MYSQLTEST_VARDIR/tmp/tar.inc

#Print the value in MTR
--echo $stat

#Remove the temp file
--remove_file $MYSQLTEST_VARDIR/tmp/tar.inc

Technique 3:

Assume that, there are 2 DATADIR locations old and new are existing, in case user wants to star the DB server with new DATADIR location and do some testing during MTR test execution. Here is the sample code

1. Stop the server which was running with old DATADIR
2. Restrat the DB server with new DATADIR

Following sample code describes how to start from new location.

#Stop server with old location
-- exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
-- shutdown_server 30
-- source include/wait_until_disconnected.inc

#Restart the DB server from new location
-- exec echo "restart: --datadir=$MYSQL_TMP_DIR/mysqld.new/data/ " > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
-- enable_reconnect
-- source include/wait_until_connected_again.inc

Note: all DATA files should exist under new location otherwise DB startup will fail.

Remote & Transportable tablespace usage scenario.

Remote Tablespace:
In my previous blog ,we talked about the how to create a Remote tablespace . You may be thinking that, how this feature is really helpful for the user, just by mentioning the absolute path along with Create statement.

Assume a situation like, You had already created a table and it was grown to so big enough, now you wanted move that tablespace to some other location from the default location. due to various reasons like no disk space or high performance devices like SDD.

steps to move the tablespace file:
In the following example the default and new location are assumed as follows.

Default MySQL DATADIR is : /work/default/mysql/db1/
New location is : '/work/new/mysql/test'

1) stop the DB server
2) create an isl file (text file) with the same name as table and with extension called 'isl' (tab1.isl), mention the new location of the tab1.ibd file as
 '/work/new/mysql/test/tab1.ibd'
Note: Put  this file in the same location where the tab1.frm file lies.(default location).
3) move the tab1.ibd file to new location /work/new/mysql/test/ , from the old location
4) Restart the DB server
5) Now the table is ready for DML operations.
Note: Move the tablespace file tab1.ibd file cautiously as it is user responsibility of placing the right version of the file.

Conclusion:
Many DB's architectures are like that , it will not allow user to move the user tablespace files alone, freely to new location once the DB instance and tablespace files have been created, because, the biggest problem is recovery,
and also the tablespace files are tightly coupled with DB instance.Even if the files are moved, DBA's has to modify the system tables accordingly.which is not advisable at all.

will talk about the usage of transportable tablespace later.......

Remote Tablespace & Transportable tablespace of MySQL

Before talk about the Remote & transportable tablespace. Lets talk about the MySQL DB tablespace concepts.

MySQL consists of 2 kinds of tablespaces, system tablespace and user tablespace.

The system tablespace works same way as any other databases. one can ADD/DROP as many as DATAFILES from the system tablespace.

But the user tablespace architecture is entire different from other databases. Each user table would associate with one DATAFILE and One Format file, and the datafile will have the extension of .ibd and the format file will have the extension of .frm. The frm file would contain the structure of the table.And these files will be located in the default MySQL DATADIR location.

The following example will clearly explain you about the ibd and frm files.

When user execute the following DDL , it would create 2 files in the default DATADIR location of the DB instance.

CREATE TABLE tab(c1 int) ;
tab.frm -----> This is a format file (definition file)
tab.ibd -----> This is a datafile

Remote tablespace:
When user wants to create a table in different location than the Default location.User will have to mention the DDL with the location (absolute path) .

CREATE TABLE tab(c1 int) DATA DIRECTORY '/x/y';

Transportable tablespace:
Moving or copying the tablespace files (data file)  from one location to other location.

Note : Assuming innodb_file_per_table=ON (Default).

Now lets talk about how flexible these user tablespace files.

* The ibd file can be created either in the Default location or in the remote location on the same machine.
* Move the ibd files from old location to the new location,when the disk is full.
* Move/copy From One Database instance to another database instance when the DB servers are UP and running.
* Supports recovery from media.
* Faster than the traditional backup methods like logical & physical backup methods.
* Alter table Import tablespace.
* Alter table discard tablespace.
* Great flexibility and user friendly for moving/copying the tables.

Conclusion:
Remote tablespace and transportable tablepsace are the one of the greatest features of MySQL, supported in 5.6. I strongly believe that, this feature would give great advantage to the users during DB administration.

Will talk about more on how the remote & transportable tablespace usage  in my next blog.