Hi Shawn, My mistake, I should have been more precise. Let me try to clarify situation, here, The files represented by the "$dir2" variable in the script are the files created by mysqldump, see below. So, "Unlock Tables" command is the one immediately before "/*!40000 ALTER TABLE `IC_CHANGES_REASON` ENABLE KEYS */" [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> work> cat secdocs.IC_CHANGES_REASON.mysqldump.CURRENT -- MySQL dump 10.9 -- -- Host: saruman Database: secdocs -- ------------------------------------------------------ -- Server version 4.1.10a-standard-log /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE="NO_AUTO_VALUE_ON_ZERO,MYSQL323" */; -- -- Table structure for table `IC_CHANGES_REASON` -- DROP TABLE IF EXISTS `IC_CHANGES_REASON`; CREATE TABLE `IC_CHANGES_REASON` ( `ic_changes_reason_key` int(10) unsigned NOT NULL auto_increment, `ic_changes_qualifer_fkey` int(10) default NULL, `ic_reason_fkey` int(10) default NULL, PRIMARY KEY (`ic_changes_reason_key`), UNIQUE KEY `icchredx` (`ic_changes_qualifer_fkey`,`ic_reason_fkey`) ) TYPE=MyISAM; -- -- Dumping data for table `IC_CHANGES_REASON` --
/*!40000 ALTER TABLE `IC_CHANGES_REASON` DISABLE KEYS */; LOCK TABLES `IC_CHANGES_REASON` WRITE; UNLOCK TABLES; /*!40000 ALTER TABLE `IC_CHANGES_REASON` ENABLE KEYS */; /*!40101 SET [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> */; /*!40014 SET [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> */; /*!40014 SET [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> */; Regards, Mikhail Berman ________________________________ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 31, 2005 12:44 PM To: Berman, Mikhail Cc: mysql@lists.mysql.com Subject: Re: Loading database files fails on "UNLOCK TABLES" statement "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
