"Berman, Mikhail" <[EMAIL PROTECTED]> wrote on 08/31/2005 12:35:19 PM:
> Hi everyone, > > I am trying to load backup files into a database using a small script > where an actual load command is: > > /usr/local/bin/mysql -uxxxxx -pxxxx -f secdocs -v -e "source $dir2" > > and it is a part of loop that reads all files fro a directory. > > The load freezes at the same point, when at the end of loading data for > the same file mysql tries to unlock the table and freezes, see below > > [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> bin> mysqladmin -pxxxxxx proc > stat > +----+---------+-----------+---------+---------+------+-------+--------- > ---------+ > | Id | User | Host | db | Command | Time | State | Info > | > +----+---------+-----------+---------+---------+------+-------+--------- > ---------+ > | 1 | devuser | localhost | secdocs | Query | 265 | | UNLOCK > TABLES | > | 4 | root | localhost | | Query | 0 | | show > processlist | > +----+---------+-----------+---------+---------+------+-------+--------- > ---------+ > Uptime: 487 Threads: 2 Questions: 38105 Slow queries: 0 Opens: 14 > Flush tables: 1 Open tables: 1 Queries per second avg: 78.244 > > [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> bin> mysqladmin -pxxxx version > mysqladmin Ver 8.41 Distrib 4.1.9, for sun-solaris2.8 on sparc > Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB > This software comes with ABSOLUTELY NO WARRANTY. This is free software, > and you are welcome to modify and redistribute it under the GPL license > > Server version 4.1.9-standard > Protocol version 10 > Connection Localhost via UNIX socket > UNIX socket /tmp/mysql.sock > Uptime: 8 min 35 sec > > Threads: 2 Questions: 38106 Slow queries: 0 Opens: 14 Flush tables: > 1 Open tables: 1 Queries per second avg: 73.992 > > [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> bin> mysqladmin -pxxxxx proc > stat > +----+---------+-----------+---------+---------+------+-------+--------- > ---------+ > | Id | User | Host | db | Command | Time | State | Info > | > +----+---------+-----------+---------+---------+------+-------+--------- > ---------+ > | 1 | devuser | localhost | secdocs | Query | 495 | | UNLOCK > TABLES | > | 6 | root | localhost | | Query | 0 | | show > processlist | > +----+---------+-----------+---------+---------+------+-------+--------- > ---------+ > Uptime: 717 Threads: 2 Questions: 38108 Slow queries: 0 Opens: 14 > Flush tables: 1 Open tables: 1 Queries per second avg: 53.149 > [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> bin> > > Any suggestion how to deal with this situation are very much > appreciated. > > Mikhail Berman > You mention that your "source $dir2" command is part of a loop. Does that mean that you have a "LOCK TABLES" statement, a loop, then an "UNLOCK TABLES" statement? If you LOCK a table in a connection, you need to UNLOCK it during the same connection. It seems to me that you need to establish one connection at the start of your script, send SQL commands through the connection (possibly reading and spooling any SQL files you have written to disk) then close your connection. From first appearances, you are opening and closing several connections while trying to maintain a set of table locks between them all. That just will not work. Sorry. If I guessed wrong about how you are processing your data, please provide a more thorough outline of your process and I will try again. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
