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


Wednesday Jun 30, 2010

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

There is not much new content this week. Just summary of what was written before.




Summary.



In the summary of this part I'd want to recommend following:



Try to find a query which causes inconsistent behavior



Use log files:



  1. General query log

  2. Log files in your application

  3. MySQL Proxy or any other suitable proxy

  4. Others



Analyze what is wrong, then solve the problem using your findings



Below I place a list of methods which we studied in the first part.


Method #1: use output operator to output query in exactly same way in which RDBMS gets it.



Method #2: use general query log if you need to find which exact query causes wrong behavior of your application.



Method #3: after you found a query which causes the problem run it using command line client and analyze result.



Method #4: try to modify SQL in such a way what you get correct result. Use search engines to find a workaround.



Method #5: use EXPLAIN EXTENDED for finding how optimized (and executing) SQL query.



Method #6: convert DML queries to corresponding SELECT to examine which rows will be modified.



Method #7: repeat your scenario backward step-by-step until you found the problem query.



Method #8: always check result of the query! Use means of your connector or interface of interactive client.



Method #9: tune your application in such a way so it will write queries to log files itself.



Method #10: use MySQL Proxy or any other proxy.


Full text (which now is same as posted above) is here


Wednesday Jun 23, 2010

"Alternate methods for finding problem query.": chapter 5 of "Methods for searching errors in SQL application" just published

Translation of chapter 5 which describes alternate methods for searching problem query just published. It starts like:



Chapter 5. Alternate methods for finding problem query.



I already wrote about using of general query log requires resources.
Part of the problem can be solved if use new feature of MySQL 5.1:
online logging which allows to turn general query log to on or to off
without stopping the MySQL server.


Unfortunately this doesn't always work: you can have old
version of MySQL server which has not such a feature, general query log
can contain very large amount of information, so you can just miss the
problem in thousands of correct queries, you can have some other own
reason.



But what to do if you can not use general query log?


One of the methods is write log files using your application.
Add a code which will write queries which your application sends to
MySQL to the log file.


With this method you can tune how and what to write to the log
file. Would be good if you will write return value and error messages
in the same log file.



Method #9: tune your application in such a way so it will write queries to log files itself.



Yet another method is using proxy which will get queries and write them to the file.


One of most preffered variants is MySQL Proxy, because this is
scriptable proxy made for working with MySQL server. It uses MySQL
client-server protocol. You can write programs for MySQL Proxy using
Lua programming language.



Below you can see example how to write general query log using MySQL Proxy:


...


 Rest of the chapter is here


Wednesday Jun 16, 2010

Translation of Chapter 4. Miscellaneous. of "Methods for searching errors in SQL application" just published

This chapter starts as:



Chapter 4. Miscellaneous.



There are cases when wrong output is just symptom of wrong input made before.



For example, you start to receive wrong data at particular step of the
scenario. After analysis of the SELECT query (or queries) is clear what
queries are correct and return exactly same data which exists in the
table (or tables).



This means wrong data was inserted at the earlier step.



How to know when it happened?


Start from the step of the scenario which exists just before
step which does output, check every query as was described in earlier
chapters. If everything works correctly, examine earlier step, repeat
until you find the error.



We can examine example with list from Chapter 1 as example of such behavior.



Lets see at output one more time.


...


Really I could put here longer quotes, but I don't like how this interface prints code. So you can find rest of the chapter here.


Comments and translation corrections are welcome.

Wednesday Jun 09, 2010

Chapter 3 of "Methods for searching errors in SQL application" has been published

Translation of "Chapter 3. Wrong data in database or what to do with problem DML query." of  "Methods for searching errors in SQL application" just published.


 This is short chapter which discuss single method of dealing with mysterious DML query.


Chapter 3. Wrong data in database or what to do with problem DML query.




Problems with wrong data happens not only with SELECT queries like
cases we discussed in chapter 2, but in cases of querires which modify
data: DML queries.



Lets discuss example below




mysql> create table t1(f1 int);



Query OK, 0 rows affected (0.01 sec)



mysql> create table t2(f2 int);



Query OK, 0 rows affected (0.08 sec)



mysql> insert into t1 values(1);



Query OK, 1 row affected (0.01 sec)




mysql> select \* from t1;



+------+



| f1   |



+------+



|    1 | 



+------+



1 row in set (0.00 sec)




mysql> delete from t1, t2 using t1, t2;



Query OK, 0 rows affected (0.00 sec)




User can expect what DELETE query will remove all rows from tables
t1 and t2: "using t1, t2;" used and there is no WHERE clause. But as
you see this is not true.



Please pay attentsion for a row which says "0 rows affected". This means what 0 rows were removed! But why?


....


 Rest of the chapter is here


Comments and translation corrections are always welcome.

Wednesday May 26, 2010

Methods for searching errors in SQL application

Some time ago I wrote in Russian language guide for finding errors in SQL application.

To be honest I wrote it having personal aim to have a text which I can easily use refer in case of user questions about how to find particular thing. But this makes less sense having no English version. So now I started to translate it to English and publish. Introduction and first chapter are ready.

You can find it at http://sql-error.microbecal.com/en/index.html  Comments and corrections of mistakes are welcome here.

Wednesday Oct 14, 2009

Presentation from PHPConf 2009

October, 8 I did master-class about catching error in SQL application at PHPConf 2009. Who speaks Russian can get slides here: pdf and odp.

There were several things which were interesting for me during my talk.

First is interaction with audience of different educational (or better to say MySQL practice) level. I should make a note for feature how to solve moments when 1/3 of audience listens carefully and is just right for the talk, another 1/3 knows things I am talking at the moment already and last 1/3 needs explanations of basic things such as what is difference between table-level and row-level locks. Probably I should create list of things user should know before attend the talk.

It was surprising how many people don't use SHOW ENGINE INNODB STATUS and InnoDB Monitor! And I had very little information about these topics in my talk.

Last thing was my expectation about classroom with desks and people with laptops on it. I saw no desks and 1-2 laptops for more than 50 people. Next time I should create interactive program without need of laptop. Like quiz or so.

Friday Sep 18, 2009

How to raise error in your MySQL application

Recently I got this question twice. Although SIGNAL was implemented in version 6.0 (which is partially mysql-trunk now) this version is not stable yet, so users still need to use workaround.

Here it is. Create 2 procedures as following:

DROP PROCEDURE IF EXISTS raise_application_error;
DROP PROCEDURE IF EXISTS get_last_custom_error;
DROP TABLE IF EXISTS RAISE_ERROR;

DELIMITER $$
CREATE PROCEDURE raise_application_error(IN CODE INTEGER, IN MESSAGE VARCHAR(255)) SQL SECURITY INVOKER DETERMINISTIC
BEGIN
  CREATE TEMPORARY TABLE IF NOT EXISTS RAISE_ERROR(F1 INT NOT NULL);

  SELECT CODE, MESSAGE INTO @error_code, @error_message;
  INSERT INTO RAISE_ERROR VALUES(NULL);
END;
$$

CREATE PROCEDURE get_last_custom_error() SQL SECURITY INVOKER DETERMINISTIC
BEGIN
  SELECT @error_code, @error_message;
END;
$$
DELIMITER ;

You can use them as:

CALL raise_application_error(1234, 'Custom message');
CALL get_last_custom_error();


Example: table which stores only odd numbers.

DROP TABLE IF EXISTS ex1;
DROP TRIGGER IF EXISTS ex1_bi;
DROP TRIGGER IF EXISTS ex1_bu;

CREATE TABLE ex1(only_odd_numbers INT UNSIGNED);

DELIMITER $$

CREATE TRIGGER ex1_bi BEFORE INSERT ON ex1 FOR EACH ROW
BEGIN
  IF NEW.only_odd_numbers%2 != 0 THEN
    CALL raise_application_error(3001, 'Not odd number!');
  END IF;
END
$$

CREATE TRIGGER ex1_bu BEFORE UPDATE ON ex1 FOR EACH ROW
BEGIN
  IF NEW.only_odd_numbers%2 != 0 THEN
    CALL raise_application_error(3001, 'Not odd number!');
  END IF;
END
$$

DELIMITER ;



Usage:


mysql> INSERT INTO ex1 VALUES(2);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO ex1 VALUES(3);
ERROR 1048 (23000): Column 'F1' cannot be null
mysql> CALL get_last_custom_error();
+-------------+-----------------+
| @error_code | @error_message  |
+-------------+-----------------+
| 3001        | Not odd number! |
+-------------+-----------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT \* FROM ex1;
+------------------+
| only_odd_numbers |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

Tuesday Jun 02, 2009

Why there is not bug fix in GA version


Reporter of bug #44604 pointed to common problem: "Why don't backport bugfix to previous major version?" Especially if this version is GA.


This particular bug seems to be fixed by redesign of MySQL Optimizer which was dramatically improved in version 6.0


At the same time while some feature is improved in new version backport this improvement or even part of it into older one is not easy and can lead to instability or affect other applications. There is always balance between risk and effort to fix.


Same reasons apply for other bugs in similar state.


Of course this applies only to bugs with good workaround or to rare cases and should never apply to crashing bugs. While sometime is not easy to properly fix crashing bug in stable version. Example of such a bug were bug #37846 and bug #37847 Real fix for version 5.0 contained message in error log instead of crash while in 5.1 error doesn't occur at all.

About

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

Search

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