Andreas Steinmetz writes:
> It seems that MySQL 3.23.32 has an internal deadlock problem, causing the
> database to stop responding.
>
> Situation:
>
> 6 processes (each on a different system) connect to a mysql database and insert
> various system data into a set of MyISAM tables. Each process uses the user
> 'logdaemon' which has insert privilege to the required tables.
> Data is extacted by php scripts using the user 'logreader', which has select
> privilege to the required tables.
>
> It happens at least once a day that MySQL stops responding. First I did assume
> that missing locks could cause the problem, so I modified all accesses to the
> database to use read and write locks on the required tables. This didn't help.
>
> If you look at the attached output of the 'show processlist' command the most
> obvious thing to see is that 2 IDs are in state 'System Lock' on the same table.
> Killing the threads doesn't help. The thread state changes to 'killed' but
> that's it. Regular shutdown of MySQL is impossible, all MySQL PIDs must be sent
> the TERM signal.
>
> Note that even as MySQL is compiled with BDB support there are only MyISAM
> tables. All inserts are "regular" inserts, there is no 'insert delayed'. the
> amount of inserts/day of all tables of this database is about 10000. The amount
> of selects is currently much lower (less than 1000/day).
>
> Thus concurrent inserts and selects to the same table may easily cause a denial
> of service condition.
>
> The only perhaps non-standard thing I did here was not to set a default database
> but to address the tables directly (db.table) which may be, ahem, not usual but
> still is a valid way to access a table. I'm going now to change this, i.e. all
> connecting processes will set the database and see if the problem persists.
> Nevertheless this is a somewhat bad situation that should be resolved as soon
> as possible.
>
> As a side effect of this problem I had the database first set to replicate in
> Robin Round manner between three systems. Unfortunately, when the above
> situation happens and MySQL must be killed, replication is killed for good,
> too, as the slave thread of the killed MySQL database will just loop in a
> 'waiting to reconnect after a failed read' state after MySQL restart. Thus
> the current replication setup is already that there's just two shadow databases
> for the master that handles all reads and writes.
>
> This leads to an additional thought: What happens to replication after a power
> outage or a system crash? Yes, I' using a ups but it can happen, that these
> things fail, causing a home made power outage. It wouldn't be too good if a
> non-standard shutdown of a slave would mean to restart replication from scratch.
>
>
> Andreas Steinmetz
> D.O.M. Datenverarbeitung GmbH
> Id User Host db Command Time State Info
> 7 replicate 10.1.1.2 NULL Binlog Dump 78339 Waiting for
>update NULL
> 9 replicate 10.1.1.6 NULL Binlog Dump 78339 Waiting for
>update NULL
> 187 logdaemon 10.1.1.3 NULL Query 7015 System lock LOCK
>TABLES syslog.pcp WRITE
> 188 logdaemon 10.1.1.7 NULL Query 6963 Opening tables LOCK
>TABLES syslog.pcp WRI
> TE
> 189 logdaemon 10.1.1.6 NULL Query 6961 Opening tables LOCK
>TABLES syslog.pcp WRI
> TE
> 190 logdaemon 10.1.1.4 NULL Query 7017 System lock LOCK
>TABLES syslog.pcp WRITE
> 191 logdaemon 10.1.1.2 NULL Query 6961 Opening tables LOCK
>TABLES syslog.pcp WRI
> TE
> 192 logdaemon localhost NULL Query 6963 Opening tables LOCK
>TABLES syslog.pcp WR
> ITE
> 246 logreader 10.1.1.3 NULL Query 6465 Opening tables lock
>tables syslog.ipchain
> s read
> 247 logreader 10.1.1.3 NULL Query 6447 Opening tables lock
>tables syslog.ipchain
> s read
> 248 logreader 10.1.1.3 NULL Query 6230 Opening tables lock
>tables syslog.ipchain
> s read
> 249 logreader 10.1.1.3 NULL Query 5651 Opening tables lock
>tables syslog.ipchain
> s read
> 250 logreader 10.1.1.3 NULL Query 5640 Opening tables lock
>tables syslog.ipchain
> s read
> 251 logreader 10.1.1.3 NULL Query 4606 Opening tables lock
>tables syslog.ipchain
> s read
> 252 logreader 10.1.1.3 NULL Query 4493 Opening tables lock
>tables syslog.ipchain
> s read
> 253 logreader 10.1.1.3 NULL Query 3445 Opening tables lock
>tables syslog.ipchain
> s read
> 254 logreader 10.1.1.3 NULL Query 3145 Opening tables lock
>tables syslog.ipchain
> s read
> 255 logreader 10.1.1.3 NULL Query 2329 Opening tables lock
>tables syslog.ipchain
> s read
> 256 logreader 10.1.1.3 NULL Query 1332 Opening tables lock
>tables syslog.ipchain
> s read
> 257 logreader 10.1.1.3 NULL Query 1088 Opening tables lock
>tables syslog.ipchain
> s read
> 258 logreader 10.1.1.3 NULL Query 1087 Opening tables lock
>tables syslog.ipchain
> s read
> 259 logreader 10.1.1.3 NULL Query 823 Opening tables lock
>tables syslog.ipchains
> read
> 260 system localhost mysql Query 384 Opening tables SELECT User,
>Select_priv FRO
> M user where User = 'dbmaster'
> 263 dbmaster localhost NULL Query 0 NULL show processlist
HI!
Most probably processes are waiting for the slave to get updated.
To circumvent the problem, you should :
- use our binary (if possible)
- avoid LOCK TABLES, which truly is necessary only in some rare cases
Regards,
Sinisa
____ __ _____ _____ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaka, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|____
^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^
/*/ \*\ Developers Team
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
