MySQL Bugs: #52493: backup hang when have dml query on that time

Bug #52493	backup hang when have dml query on that time
Submitted:	31 Mar 2010 6:38	Modified:	24 May 2010 17:53
Reporter:	patcharee sirichavalit	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.22-2.1.0.1	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
mysql hang everyday when i backup if on that time have dml query (update query) 

please let me know how to solve it 

How to repeat:
try to backup snapshot another time

Please, send the results of SHOW FULL PROCESSLIST at the moment of this hang. What exact mysqldump command line do you use?

|  8232026 | db | ip | NULL | Query   | 87160 | Waiting for release of readlock | UPDATE  table SET views = views + 1 WHERE day = NOW()

query like above and i use snapshot for backup

| Id       | User    | Host                | db   | Command | Time  | State                           | Info                                                                             
|  8232026 | db | ip | NULL | Query   | 87160 | Waiting for release of readlock | UPDATE  table SET views = views + 1 WHERE day = NOW()

query like above and i use snapshot for backup

What exact commands do you use at OS and MySQL level for backup? Is it possible that FLUSH TABLES WITH READ LOCK is among them?

i use 
FLUSH TABLES;
FLUSH TABLES WITH READ LOCK;
snap create xxxx;
UNLOCK TABLES;

may be  FLUSH TABLES WITH READ LOCK  is cause of deadlock

but i don't know how to solve it .

FLUSH TABLES WITH READ LOCK locks all tables from any changes. So, you should make your snapshot as fast as possible (I doubt that it takes 80000+ seconds) and then execute UNLOCK TABLES immediately if you do not want changes to blocked for a long time. So, please, review your backup script and check that UNLOCK TABLES really happens after the snapshot.

I do not think that we have any MySQL bug here. I'd suggest you to update that ages old MySQL version anyway (current one is 5.0.90).

8000+ is cause from mysql hang .

If mysql doesn't have any update query on that time , we will use only 2-3 second for snapshot .
and i doubt why update query can pass to mysql when i run FLUSH TABLES WITH READ LOCK;

do you have any command to make query didn't pass to mysql when i take backup .

is it a problem if i backup data to NAT ?

Either FLUSH TABLES ... hang until changes will be finished, or changes hang waiting for UNLOCK TABLES to happen. Please, enable general query log and check what exact statements and in what order your script(s) send to MySQL server.

If snapshot is performed fast, than it does not matter is it to NAS, SAN or local disk.

Hi ,
If i upgrade mysql to 5.1.45-1 ,it can solve this problem or not ?
and where to download it ?

i check my log and i found 

           45209 Query       FLUSH TABLES
                  45210 Connect     db@ip on
                  45210 Query       SET NAMES tis620
                  45210 Query       UPDATE  table  SET views = views + 1 WHERE day = NOW()
                                AND hour = HOUR(NOW()) 
                  45211 Connect     db@ip on
                  45211 Query       SET NAMES tis620
                  45211 Query       SELECT * FROM table WHERE id = 'aa'
                  45212 Connect     db@ip on
                  45212 Query       SET NAMES tis620
                  45212 Query       UPDATE  table SET views = views + 1 WHERE day = NOW()
                                AND hour = HOUR(NOW()) 
                  45213 Connect     db@ip on
                  45213 Query       SET NAMES tis620
                  45213 Query       UPDATE table SET views = views + 1 WHERE day = NOW()
                                AND hour = HOUR(NOW()) 
                  45214 Connect     db@ip on
                  45214 Query       SET NAMES tis620
                  45214 Query       SELECT xx AS t
                FROM
                        table 
                WHERE
                        filename = 'aa'
                  45215 Connect     db@ip on
                  45215 Query       SET NAMES tis620
                  45215 Query       UPDATE  table SET views = views + 1 WHERE day = NOW()
                                AND hour = HOUR(NOW()) 
                  45216 Connect     db1@ip on
                  45216 Query       SET NAMES tis620
                  45216 Init DB     db1
                  45216 Query       SELECT * FROM table WHERE id = 'bb8'
                  45217 Connect     db@ip on
                  45217 Query       SET NAMES tis620
                  45217 Query       SELECT * FROM table  WHERE id = 'cc'
 45209 Query       FLUSH TABLES WITH READ LOCK
                  45211 Query       SELECT * FROM table WHERE id = 'ww'
                  45216 Query       SELECT * FROM table WHERE  id = 'jj'
                  45214 Quit
                  45217 Query       SELECT * FROM table WHERE id = 'kk'
                  45217 Query       UPDATE  table SET views = views + 1 WHERE day = NOW()
                                AND hour = HOUR(NOW()) 
                  45218 Connect     db@ip on
                  45218 Query       SET NAMES tis620
                  45218 Query       UPDATE  table SET views = views + 1 WHERE day = NOW()
                                AND hour = HOUR(NOW()) 
                  45219 Connect     cabc@ip on
                  45219 Query       SET NAMES tis620
                  45219 Init DB      cabc

why FLUSH TABLES WITH READ LOCK still have update query ?
and i think it's make mysql hang .
so is it your bug ? and  please let me know how to solve it .

Thank you for the feedback.

Having UPDATE in the log doesn't say if it was run successfully: general log just contains statements which were sent to server, not information about how and when they finished. So this is still not a bug.

Hi All,

I don't quite sure about it's not bug because
mysql hang after i backup and on that time mysql run update query .
So if it not bug , why mysql hang after snapshot backup that have update query on that time .

fyi ,
mysql snapshot backup  not hang if on that time don't have update query .

Thank you for the feedback.

> I don't quite sure about it's not bug because
> mysql hang after i backup and on that time mysql run update query .

Do you run update query and backup same time? Do you wait when FLUSH TABLES and FLUSH TABLES WITH READ LOCK return?

Please send us full output of SHOW FULL PORCESSLIST in time when MySQL hangs, not just portion of it.

> So if it not bug , why mysql hang after snapshot backup that have update query on that
> time .

Does every query hangs or only that particular update query?

Please also note you are using very old version of MySQL 5.0.22 while we had fixed bugs related to concurrent FLUSH TABLES. Please upgrade to current version 5.0.90 and check if problem still exists.

Do you run update query and backup same time? Do you wait when FLUSH TABLES and FLUSH TABLES WITH READ LOCK return?

it depend on web application . if some user click web application on that time , query will update to mysql .

Please send us full output of SHOW FULL PORCESSLIST in time when MySQL hangs, not just portion of it.

Please let me know your email .

Does every query hangs or only that particular update query?

only update query that run on backup time.
If  user update query on another time , mysql not hang.

Thank you for the feedback,

>> Does every query hangs or only that particular update query?

>only update query that run on backup time.
>If  user update query on another time , mysql not hang.

This means this is still not a bug: UPDATE query has to wait when backup finishes.

This means this is still not a bug: UPDATE query has to wait when backup finishes.

why after  unlock table , update query still wait   forever ?

we have to kill mysql  every time if have query update when backup
 because update query still not finish and it make mysql slow and die ....

Thank you for the feedback.

> why after  unlock table , update query still wait   forever ?

Sorry, didn't understand UPDATE waits forever.

But this still can be fixed: looks like duplicate of bug #43230 fixed in version 5.0.82. Please upgrade to current version 5.0.90, try with it and inform us about results.

update forever mean it still wait to update although backup finished and mysql already unlock .

Please let me know it's bug or not .
If it's bug , we will upgrade to solve it .
but if it's not bug ,please let me know how to solve it .

Thank you for the feedback,

If UPDATE hangs forever after backup finishes and unlock tables this is a bug.

If UPDATE hangs even after UNLOCK TABLES was successfully executed (from the same connection that did FLUSH ... WITH READ LOCK!), this indeed sounds more like a bug. 

But in any case nobody will fix it in 5.0.22. Please, upgrade to the recent version, 5.0.90. In case of the same problem with it, please, execute:

mysqladmin -uroot -p debug

and send the error log content.

where to download it ?

from the same connection that did FLUSH ... WITH READ LOCK!  <=== what's you mean ?
because my script is  
flush table  
flush table with read lock 
exit
after that we snapshot by OS
after finished snapshot we connect to mysql again  and unlock table 

is it correct ?

You can download it from http://downloads.mysql.com/archives.php?p=mysql-5.0

from the same connection that did FLUSH ... WITH READ LOCK!  <=== what's you mean ?
because my script is  
flush table  
flush table with read lock 
exit
after that we snapshot by OS
after finished snapshot we connect to mysql again  and unlock table 

is it correct ?

You can download 5.0.90 from archives, http://downloads.mysql.com/archives.php?p=mysql-5.0&v=5.0.90.

from the same connection that did FLUSH ... WITH READ LOCK!  <=== what's it mean ?
because my script is
flush table
flush table with read lock
exit
after that we snapshot by OS
after finished snapshot we connect to mysql again  and unlock table 

please let me know step above is  correct or not

Steps above are correct.

please clarification this   message  
 ===>  from the same connection that did FLUSH ... WITH READ LOCK!  

and why you didn't recommend me to upgrade to 5.1.4.5 ?

Thank you for the feedback.

> please clarification this   message  
>  ===>  from the same connection that did FLUSH ... WITH READ LOCK!  

You should i single connection:

flush table  
flush table with read lock 
DO NOT EXIT
WAIT after that we snapshot by OS
IN SAME CONNECTION after finished snapshot unlock table 

If you exits in the middle FLUSH TABLES releases lock, so results are unpredictable.

> and why you didn't recommend me to upgrade to 5.1.4.5 ?

Either to 5.0.90 or to 5.1.45

ok ,so i will try to change script  first 

and let you know result again

mysql have pause  command or not 
like oracle use " ! " to pause session

Please let me know  how to do for "DO NOT EXIT  from mysql " but  can run snapshot by OS

Leave mysql client open and run snapshot in another terminal session.

As you are on Linux you can also use system command of mysql command line client to execute any OS level command or shell script, like this:

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.38 sec)

mysql> system who am i
openxs   ttys000  15 бер 17:19 
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

Just use your command(s) to do a snapshot instead of "who am i" above.

Thank you for all 

I will change my script and let you know result soon.

Hi All,

I already implement new script as you recommend .

Please wait result around 2 weeks.

Thank you for the update.

We will wait result.

Hi all,

we still face of same problem .

i found many query state Waiting for table and Waiting for release of readlock after backup finished.

please help us to solve this problem .

Hi all,

any update ?

my backup finished but still not unlock table.

why?

Please, upload your new script. I want to make sure it contains UNLOCK TABLES statement.

mysql> FLUSH TABLES;
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

mysql> system snap.sh

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> UNLOCK TABLE;
Query OK, 0 rows affected (0.00 sec)

mysql> exit

it will do like above when that day don't have update query .

If have update query ,it will not run unlock table.

Thank you for the feedback.

I just tried actions as you described and no query locks for me. Please try with new version 5.0.90 and inform us if problem still exists in your environment.

Hi All,

Mysql have any problem with snapshot ?

Thank you for the feedback.

MySQL has no known problem with snapshot. If you insist your problem is MySQL bug we need you to test with latest version and help us to repeat problem on our side.

i try to install 5.0.9 64 bit

but i have one question

if we copy database from 32 bit to 64 bit ,it will can use feature of 64 bit ?

Thank you for the feedback.

Yes, it will use feature of 64-bit if you copy data directory.

Hi 

what it mean ==>  if you copy data directory.

for more info
i have 2 server 
one is production that have problem (32 bit)
second is test server (install 5.0.9 64 bit )
so i copy data from first server to second server 
and i start mysql on second sever 
so mysql on second server  can use all 64 bit feature  ?

Yes.

็Hi  Sveta Smirnova,

Yes. <=== mean ?

This is answer on your question:

> so mysql on second server  can use all 64 bit feature  ?

Yes.

Hi All,

Do you know how to import xml to mysql ?
If yes,please let me know how to do .

We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.