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


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.

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