Friday Mar 23, 2012

Story of success: MySQL Enterprise Backup (MEB) was successfully integrated with IBM Tivoli Storage Manager (TSM) via System Backup to Tape (SBT) interface.

Since version 3.6 MEB supports backups to tape through the SBT interface.

The officially supported tool for such backups to tape is Oracle Secure Backup (OSB).

But there are a lot of other Storage Managers. MEB allows to use them through the SBT interface. Since version 3.7 it also has option --sbt-environment which allows to pass environment variables, not needed by OSB, to third-party managers. At the same time MEB can not guarantee it would work with all of them.

This month we were contacted by a customer who wanted to use IBM Tivoli Storage Manager (TSM) with MEB. We could only say them same thing I wrote in previous paragraph: this solution is supposed to work, but you have to be pioneers of this technology. And they agreed.

They agreed to be the pioneers and so the story begins.

MEB requires following options to be specified by those who want to connect it to SBT interface:

--sbt-database-name: a name which should be handed over to SBT interface. This can be any name. Default, MySQL, works for most cases, so user is not required to specify this option.
--sbt-lib-path: path to SBT library. For TSM this library comes with "Data Protection for Oracle", which, in its turn, interfaces with Oracle Recovery Manager (RMAN), which uses SBT interface. So you need to install it even if you don't use Oracle.
--sbt-environment: environment for third-party manager. This option is not needed when you use OSB, but almost always necessary for third-party SBT managers. TSM requires variable TDPO_OPTFILE to be set and point to the TSM configuration file.
--backup-image=sbt:: path to the image. Prefix "sbt:" indicates that image should be sent through SBT interface

So full command in our case would look like:

./mysqlbackup --port=3307 --protocol=tcp --user=backup_user --password=foobar \
--backup-image=sbt:my-first-backup --sbt-lib-path=/usr/lib/libobk.so \
--sbt-environment="TDPO_OPTFILE=/path/to/my/tdpo.opt"
--backup-dir=/path/to/my/dir backup-to-image
And this command results in the following output log:

MySQL Enterprise Backup version 3.7.1 [2012/02/16]
Copyright (c) 2003, 2012, Oracle and/or its affiliates. All Rights Reserved.

INFO: Starting with following command line ... 
 ./mysqlbackup --port=3307 --protocol=tcp --user=backup_user
        --password=foobar --backup-image=sbt:my-first-backup
        --sbt-lib-path=/usr/lib/libobk.so
        --sbt-environment="TDPO_OPTFILE=/path/to/my/tdpo.opt"
        --backup-dir=/path/to/my/dir backup-to-image

sbt-environment: 'TDPO_OPTFILE=/path/to/my/tdpo.opt'
INFO: Got some server configuration information from running server.

IMPORTANT: Please check that mysqlbackup run completes successfully.
            At the end of a successful 'backup-to-image' run mysqlbackup
            prints "mysqlbackup completed OK!".

--------------------------------------------------------------------
                       Server Repository Options:
--------------------------------------------------------------------
  datadir                          =  /path/to/data
  innodb_data_home_dir             =  /path/to/data
  innodb_data_file_path            =  ibdata1:2048M;ibdata2:2048M;ibdata3:64M:autoextend:max:2048M
  innodb_log_group_home_dir        =  /path/to/data
  innodb_log_files_in_group        =  2
  innodb_log_file_size             =  268435456

--------------------------------------------------------------------
                       Backup Config Options:
--------------------------------------------------------------------
  datadir                          =  /path/to/my/dir/datadir
  innodb_data_home_dir             =  /path/to/my/dir/datadir
  innodb_data_file_path            =  ibdata1:2048M;ibdata2:2048M;ibdata3:64M:autoextend:max:2048M
  innodb_log_group_home_dir        =  /path/to/my/dir/datadir
  innodb_log_files_in_group        =  2
  innodb_log_file_size             =  268435456

Backup Image Path= sbt:my-first-backup
mysqlbackup: INFO: Unique generated backup id for this is 13297406400663200
120220 08:54:00 mysqlbackup: INFO: meb_sbt_session_open: MMS is 'Data Protection for Oracle: version 5.5.1.0'
120220 08:54:00 mysqlbackup: INFO: meb_sbt_session_open: MMS version '5.5.1.0'
mysqlbackup: INFO: Uses posix_fadvise() for performance optimization.
mysqlbackup: INFO: System tablespace file format is Antelope.
mysqlbackup: INFO: Found checkpoint at lsn 31668381.
mysqlbackup: INFO: Starting log scan from lsn 31668224.
120220  8:54:00 mysqlbackup: INFO: Copying log...
120220  8:54:00 mysqlbackup: INFO: Log copied, lsn 31668381.
          We wait 1 second before starting copying the data files...
120220  8:54:01 mysqlbackup: INFO: Copying /path/to/ibdata/ibdata1 (Antelope file format).
mysqlbackup: Progress in MB: 200 400 600 800 1000 1200 1400 1600 1800 2000
120220  8:55:30 mysqlbackup: INFO: Copying /path/to/ibdata/ibdata2 (Antelope file format).
mysqlbackup: Progress in MB: 200 400 600 800 1000 1200 1400 1600 1800 2000
120220  8:57:18 mysqlbackup: INFO: Copying /path/to/ibdata/ibdata3 (Antelope file format).
mysqlbackup: INFO: Preparing to lock tables: Connected to mysqld server.
120220 08:57:22 mysqlbackup: INFO: Starting to lock all the tables....
120220 08:57:22 mysqlbackup: INFO: All tables are locked and flushed to disk
mysqlbackup: INFO: Opening backup source directory '/path/to/data/'
120220 08:57:22 mysqlbackup: INFO: Starting to backup all files in subdirectories of '/path/to/data/'
mysqlbackup: INFO: Backing up the database directory 'mysql'
mysqlbackup: INFO: Backing up the database directory 'test'
mysqlbackup: INFO: Copying innodb data and logs during final stage ...
mysqlbackup: INFO: A copied database page was modified at 31668381.
          (This is the highest lsn found on page)
          Scanned log up to lsn 31670396.
          Was able to parse the log up to lsn 31670396.
          Maximum page number for a log record 328
120220 08:57:23 mysqlbackup: INFO: All tables unlocked
mysqlbackup: INFO: All MySQL tables were locked for 0.000 seconds
120220 08:59:01 mysqlbackup: INFO: meb_sbt_backup_close: blocks: 4162  size: 1048576  bytes: 4363985063
120220  8:59:01 mysqlbackup: INFO: Full backup completed!
mysqlbackup: INFO: MySQL binlog position: filename bin_mysql.001453, position 2105
mysqlbackup: WARNING: backup-image already closed
mysqlbackup: INFO: Backup image created successfully.:
           Image Path: 'sbt:my-first-backup'

-------------------------------------------------------------
   Parameters Summary
-------------------------------------------------------------
   Start LSN                  : 31668224
   End LSN                    : 31670396
-------------------------------------------------------------

mysqlbackup completed OK!
Backup successfully completed.

To restore it you should use same commands like you do for any other MEB image, but need to provide sbt* options as well:

$./mysqlbackup --backup-image=sbt:my-first-backup --sbt-lib-path=/usr/lib/libobk.so \
--sbt-environment="TDPO_OPTFILE=/path/to/my/tdpo.opt" --backup-dir=/path/to/my/dir image-to-backup-dir
Then apply log as usual:

$./mysqlbackup --backup-dir=/path/to/my/dir apply-log
Then stop mysqld and finally copy-back:

$./mysqlbackup --defaults-file=path/to/my.cnf --backup-dir=/path/to/my/dir copy-back

 

Disclaimer. This is only story of one success which can be useful for someone else. MEB is not regularly tested and not guaranteed to work with IBM TSM or any other third-party storage manager.

Thursday Apr 28, 2011

InnoDB Recovery forces

I just answered in yet another bug report where user experiences problem because OS crash damaged InnoDB tablespace. I wonder why users don't use InnoDB recovery. Looks like I need to create an entry in Domas-style:


IF YOUR TABLESPACE


IS CORRUPTED


TRY


FORCING INNODB RECOVERY


http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html

Warning and error information in stored procedures

UPD: Information in this blog entry is not true since version 5.6. Please read revisited blog about warning and error information in stored procedures.


I got this question second time this week, so I think it is worth writing a blog post about this topic.
Question was "Why I get error in EVENT which calls a STORED PROCEDURE which already has HANDLER?"
This changed in version 5.5 after fix of Bug #36185, Bug #5889, Bug #9857 and Bug #23032. Explanation is in this commit:


  The problem was in the way warnings/errors during stored routine execution
were handled. Prior to this patch the logic was as follows:
- when a warning/an error happens: if we're executing a stored routine,
and there is a handler for that warning/error, remember the handler,
ignore the warning/error and continue execution.
- after a stored routine instruction is executed: check for a remembered
handler and activate one (if any).
This logic caused several problems:
- if one instruction generates several warnings (errors) it's impossible
to choose the right handler -- a handler for the first generated
condition was chosen and remembered for activation.
- mess with handling conditions in scopes different from the current one.
- not putting generated warnings/errors into Warning Info (Diagnostic
Area) is against The Standard.
The patch changes the logic as follows:
- Diagnostic Area is cleared on the beginning of each statement that
either is able to generate warnings, or is able to work with tables.
- at the end of a stored routine instruction, Diagnostic Area is left
intact.
- Diagnostic Area is checked after each stored routine instruction. If
an instruction generates several condition, it's now possible to take a
look at all of them and determine an appropriate handler.

This means you will notice error which was last handled in stored routine after its execution.
In case of EVENT error would be written to the error log file.


Assume you have a table and two stored procedures:


create table t1(f1 int);
insert into t1 values (1), (2), (3), (4), (5);
\\d |
create procedure search_t1()
begin
declare done int default 0;
declare val int;
declare cur cursor for select f1 from t1;
declare continue handler for not found set done=1;
open cur;
repeat
fetch cur into val;
if not done then
select val;
end if;
until done end repeat;
select done;
end
|
create procedure call_search()
begin
call search_t1();
end
|

If you call call_search you will get a warning from search_t1:


mysql> \\W
Show warnings enabled.
mysql> call call_search()|
+------+
| val  |
+------+
|    1 |
+------+
1 row in set (0.33 sec)
...
Query OK, 0 rows affected, 1 warning (0.33 sec)
Error (Code 1329): No data - zero rows fetched, selected, or processed

This happens because Diagnostic Area now is not cleared after stored procedure execution. One of advantages of this fix, in addition to what it is compatible with SQL Standard now, is you can handle errors from stored routines called inside another routine.
Imagine you have same 2 routines, but defined as follow:


create procedure search_t1()
begin
    declare val int;
    declare cur cursor for select f1 from t1;
   
    open cur;
    repeat
        fetch cur into val;
        select val;
    until 0=1 end repeat;
end
|
create procedure call_search()
begin
    declare nf int default 0;
    declare continue handler for not found set nf=1;
    call search_t1();
    select nf;
end
|

Before version 5.5 call of call_search would fail:


mysql> call call_search()|
+------+
| val |

+------+
| 1 |
+------+
1 row in set (0.01 sec)
...
+------+
| val |
+------+
| 5 |
+------+
1 row in set (0.01 sec)

ERROR 1329 (02000): No data - zero rows fetched, selected, or processed


Pay attention no query after call of search_t1 was executed. Since version 5.5 same call would not fail:


mysql> call call_search();
+------+
| val  |
+------+
|    1 |
+------+
1 row in set (0.12 sec)
...
+------+
| val  |
+------+
|    5 |
+------+
1 row in set (0.12 sec)

+------+
| nf   |
+------+
|    1 |
+------+
1 row in set (0.12 sec)

Query OK, 0 rows affected, 1 warning (0.12 sec)


Of course this is just generic example and actually shows bad code practice, but real life can provide us not so generic errors

Wednesday Apr 20, 2011

From Collaborate 11: Diagram of actions which should be performed when error found.

This year I was speaking at Collaborate 11 about actions one need to perform when find her SQL application behaves wrongly.


New thing which was not presented in my earlier notes is simple how-to of how to deal with replication errors. You can find this in second presentation.


Slides are here: part 1 and part 2.


I also created PDF diagram which can be print and used as visual aid for cases when you meet an error. You can download it here or at the Conference website here (see file for session 410).


If you want to print it you should either use A3 sheet with 50% scale or A1 sheet with 100% scale.

Tuesday Mar 22, 2011

Spring saving time: watch your TIMESTAMPs

Every March we have a lot of bug reports and support issues about Daylight Saving Time changes.


General rule here is to remember 1 hour at March, 27 night will be missed in most of European countries: for example in Moscow after 01:59:59 next time will be 03:00:00. And yes, I am too late for America =)


MySQL handles such timestamps properly, therefore expect no such time and date will be inserted in STRICT mode and next valid date will be inserted for not existent time in forgiving mode. For Moscow '2011-03-27 03:00:00' will be inserted if you try to insert timestamps in '2011-03-27 02:00:01' - '2011-03-27 02:59:59' range.


You can see how Europe switch to DST at http://www.timeanddate.com/news/time/europe-starts-dst-2011.html Please note some countries such as Turkey have special rules for year 2011.


If you use timezones with special rules for this year make sure your operating system zoneinfo information is up to date, then reload MySQL time_zone\* tables using mysql_tzinfo_to_sql utility and finally restart MySQL server. You can read about mysql_tzinfo_to_sql at http://dev.mysql.com/doc/refman/5.5/en/mysql-tzinfo-to-sql.html



Have a nice spring!


Thursday Oct 21, 2010

Translation of "Methods for searching errors in SQL application" just finished

Translation of "Methods for searching errors in SQL application" just finished, epilogue is at http://sql-error.microbecal.com/en/concl.html


It contains list of methods which had been discussed.




Epilogue



Finally I'd like to repeat methods which we discussed. Unfortunately
there are several problems left. I will be glad to know your opinion
about what else to descuss. I will be waiting your notes at sveta_dot_smirnova_at_oracle_dot_com or sveta_at_js-client_dot_com



List of methods.


...


Rest of the text is here

Wednesday Oct 13, 2010

Translation of "Appendix. Methods of copying and moving of MySQL databases." of "Methods for searching errors in SQL application" just published

Translation of appendix about methods of copying and moving MySQL databases just published. This is just short overview of possible methods and does not pretend to be detailed guide. It starts as:




Appendix. Methods of copying and moving of MySQL databases.



In this application I'd like to shortly discuss general methods of backup and moving of mySQL databases.



Easier and recommended way of data moving is mysqldump utility. You can copy data with help of following command:





$mysqldump dbname [tblname ...] >dump.sql


...


and continues here


Saturday Sep 25, 2010

Translation of Summary of Part 4 of "Methods for searching errors in SQL application" just published


Translation of summary of last part, "Techniques, used for debugging of Production applications" just published. This is almost end, only appendix about backup techniques and epilogue left.




Summary.



In the last chapter we discussed methods of testing problems which can happen only on production server. Lets repeat them:


Method #25: if something unexpected happens check error log first.


Method #26: turn InnoDB Monitor to on to have information about all InnoDB transactions in the error log file.


Method #27: use slow query log to find all slow queries.


Method #28: use MySQL Sandbox for fast and convenient testing of your application using several versions of MySQL server.


Method #29: use part of data when work with queries which return wrong results from huge tables.


Original text is here

Tuesday Sep 14, 2010

Translation of "Chapter 11. Techniques, used for debugging of Production applications" of "Methods for searching errors in SQL application" just published.


English translation of first and only chapter of last part 4 "Techniques, used for debugging of Production applications" just published.



Chapter 11. Techniques, used for debugging of Production applications.



Unfortunately is not always possible to find error while testing. Often they only happen when high load.


How do you know about such problems?


One of most important sources of information about problems is error
log file. There you can find information such as server crashes,
connection errors (if option log-warnings=2 turned on), about options
which were specified in the configuration file, but had not turned on
because error and some others. There is a rule for working with error
log file: if something unexpected happens check error log first. Error
log file also contains information about server errors which are not
accessible for clients. Therefore is better to have it always turned on
even if you do logging at application level.


...


Rest of the chapter is here.


Wednesday Sep 01, 2010

Translation of Summary of Part 3 of "Methods for searching errors in SQL application" just published

Not much new this time: just summary of part 3 published and fixed mistake in chapter 10 (thanks, Shane!).

Summary.

In the third part we discussed methods of application debugging in cases when query plays secondary role in the problem.

I'd like to bring your attention we only discussed most frequent cases while MySQL server has a lot of parameters which of them can affect application. Analyze parameters which you use. One of the methods is run problematic query using MySQL server running with option --no-defaults and examine if results are different for MySQL server run with parameter which you use. If results are different analyze why parameter affects it and solve the problem.

...

Rest of the chapter is here.

Wednesday Aug 11, 2010

Translation of "Chapter 10. Lost connection to MySQL server during query." of "Methods for searching errors in SQL application" just published.

This chapter is about possible reasons of "Lost connection to MySQL server" error not discussed in previous one.



Chapter 10. Lost connection to MySQL server during query



You can see error "Lost connection to MySQL server" not only because
too small connect_timeout, but because other reasons too. In this
chapter we discuss these reasons.




$php phpconf2009_4.php

string(44) "Lost connection to MySQL server during query"


Most likely error log will show what happened:


...


Rest of the chapter is here


Tuesday Aug 03, 2010

Translation of "Chapter 9. Misterious vanishing of the server." of "Methods for searching errors in SQL application" just published

In this chapter we discuss timeouts which can lead to "MySQL server has gone away" and "Lost connection to MySQL server" errors.


 Extract here is short, because there are a lot of code quotes which I can not properly format here.




Chapter 9. Misterious vanishing of the server.



Often this looks like:




$php phpconf2009_3.php

string(26) "MySQL server has gone away"


Code:


...


Rest of the chapter is here

Wednesday Jul 21, 2010

Translation of "Chapter 8. Large amount of data." of "Methods for searching errors in SQL application" just published

I started translation of Part 3 "Other cases". In this part I mostly describe how wrong settings can lead to misterious errors. First chapter in this part about large amount of data.



Part 3. Other cases.



Chapter 8. Large amount of data.



There are cases when query is just symptom of wrong behavior, but true reason is wrong settings.



One of the cases is too small max_allowed_packet for data sent. MySQL
server variable max_allowed_packet defines maximum possible amount of
data which MySQL server can receive or send. Amount of
max_allowed_packet is specified in bytes.



Error usually looks like:





$mysql51 test <phpconf2009_1.sql

ERROR 1153 (08S01) at line 33: Got a packet bigger than 'max_allowed_packet' bytes


...


Rest of the chapter is here


Wednesday Jul 14, 2010

Translation of "Chapter 7. Concurrent transactions. " of "Methods for searching errors in SQL application" just published.

This is short chapter which gives some clue what to do with concurrent transactions.



Chapter 7. Concurrent transactions.



Yet another frequent example of similar problem is "Lock wait timeout
exceeded" error while you use InnoDB tables. Most often running query
SHOW ENGINE INNODB STATUS is enough to find the problem, because it
will show last transactions. But output of this command does not
contain information about all queries in the same transaction, but only
about last one. What to do if SHOW ENGINE INNODB STATUS doesn't provide
all information?


...


 Rest of the chapter is here

Wednesday Jul 07, 2010

Translation of "Chapter 6. Locks and deadlocks." of "Methods for searching errors in SQL application" just published.

This is new part which contains information about what to do if problem is repeatable only when queries run concurrently.




Chapter 6. Locks and deadlocks.



In the last part we discussed how to find cause of the problem in case
if it is always repeatable. But there are cases when problem occurs
only under particular circumstances.



For example, such easy query can run long enough:





mysql> select \* from t;

+-----+

| a   |

+-----+

|   0 |

| 256 |

+-----+

2 rows in set (3 min 18.71 sec)


...


Rest of the chapter is here


About

Working blog of Sveta Smirnova - MySQL Senior Principal Support Engineer working in Bugs Analysis Support Group

Search

Categories
Archives
« February 2015
SunMonTueWedThuFriSat
1
2
3
4
5
6
7
8
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
       
       
Today