RE: LOCK TABLES and multi table UPDATE
On 22-Jan-2004 Michael McTernan wrote: Hi there, Thanks for your quick response! Why all the locks, temp tables and updates? You can just do: SELECT CASE WHEN B.y IS NULL THEN A.x ELSE B.y END AS newX FROM A LEFT JOIN B ON A.x = B.x Spot on - many thanks! I wasn't aware of the CASE function, but I've certainly learnt something now :) Also look at SELECT COALESCE(B.y, A.x) ... Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LOCK TABLES and multi table UPDATE
Hi there, I've got a small issue which looks a little like a bug. I'm using MySQL 4.0.15-standard-log, on RedHat 9.0 with InnoDb tables. Essentially I have two tables, one table is a list of integers, while the second is a table of integer pairs. e.g. +---+ +-+-+ | A | | B.x | B.y | +---+ +-+-+ | 1 | | 1 | 5 | | 2 | | 3 | 6 | | 3 | +-+-+ | 4 | +---+ What I want to do is form a temporary table containing A, but then with each value in A that matches B.x substituted for B.y. e.g. the desired result from the example would be a new table: +---+ | T | +---+ | 5 | | 2 | | 6 | | 4 | +---+ Here is what I try executing to get this, from my live database: LOCK TABLES labelfiles AS labelfile READ, branchfiles AS bfile READ; DROP TEMPORARY TABLE IF EXISTS tmpLabelFiles; -- This creates table 'A' from some other table CREATE TEMPORARY TABLE tmpLabelFiles ( PRIMARY KEY (id) ) SELECT labelfile.fileid AS id FROM labelfiles AS labelfile WHERE labelfile.labelid=18; -- This performs the substitution for 'B.x' - 'B.y' UPDATE tmpLabelFiles AS tfile, branchfiles AS bfile SET tfile.id=bfile.replacementfileid WHERE tfile.id=bfile.branchfileid; UNLOCK TABLES; So far so good. Except that I get the following error when trying to execute the UPDATE: ERROR 1099: Table 'bfile' was locked with a READ lock and can't be updated If I lock 'bfile' with a WRITE lock it succeeds, but I'd prefer not to use a WRITE lock since other accesses to the table might be needed and this table can get quite large, and really, I'm not updating 'bfile' so should only need a READ lock, right? Has anyone else found this, and does anyone else know if there is an efficient work around? I've checked bugs.mysql.com and found nothing, is this a new bug? Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOCK TABLES and multi table UPDATE
Michael McTernan said: I'm using MySQL 4.0.15-standard-log, on RedHat 9.0 with InnoDb tables. Essentially I have two tables, one table is a list of integers, while the second is a table of integer pairs. e.g. +---+ +-+-+ | A | | B.x | B.y | +---+ +-+-+ | 1 | | 1 | 5 | | 2 | | 3 | 6 | | 3 | +-+-+ | 4 | +---+ What I want to do is form a temporary table containing A, but then with each value in A that matches B.x substituted for B.y. e.g. the desired result from the example would be a new table: +---+ | T | +---+ | 5 | | 2 | | 6 | | 4 | +---+ Why all the locks, temp tables and updates? You can just do: SELECT CASE WHEN B.y IS NULL THEN A.x ELSE B.y END AS newX FROM A LEFT JOIN B ON A.x = B.x Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: LOCK TABLES and multi table UPDATE
Hi there, Thanks for your quick response! Why all the locks, temp tables and updates? You can just do: SELECT CASE WHEN B.y IS NULL THEN A.x ELSE B.y END AS newX FROM A LEFT JOIN B ON A.x = B.x Spot on - many thanks! I wasn't aware of the CASE function, but I've certainly learnt something now :) Thanks, Mike -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: 22 January 2004 16:38 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: LOCK TABLES and multi table UPDATE Michael McTernan said: I'm using MySQL 4.0.15-standard-log, on RedHat 9.0 with InnoDb tables. Essentially I have two tables, one table is a list of integers, while the second is a table of integer pairs. e.g. +---+ +-+-+ | A | | B.x | B.y | +---+ +-+-+ | 1 | | 1 | 5 | | 2 | | 3 | 6 | | 3 | +-+-+ | 4 | +---+ What I want to do is form a temporary table containing A, but then with each value in A that matches B.x substituted for B.y. e.g. the desired result from the example would be a new table: +---+ | T | +---+ | 5 | | 2 | | 6 | | 4 | +---+ Why all the locks, temp tables and updates? You can just do: SELECT CASE WHEN B.y IS NULL THEN A.x ELSE B.y END AS newX FROM A LEFT JOIN B ON A.x = B.x Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lock tables in myisam
On Thu, Sep 18, 2003 at 03:00:17PM +1000, [EMAIL PROTECTED] wrote: Hi there i have a couple of projects which required fulltext searching so was unable to setup innodb on these. I was wondering if lock tables is a secure way to make the transaction on these tables and does this prevent being read upon aswell? Obtaining a WRITE lock on a MyISAM table prevents readers, yes. But you do have to put the necessary smarts into your code to properly implement a ROLLBACK if you need one. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 4 days, processed 188,913,819 queries (445/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lock tables in myisam
rollback works on myisam ? this is mysql4 anyway, sweet i'll give it a try. On Fri, 2003-09-19 at 08:01, Jeremy Zawodny wrote: On Thu, Sep 18, 2003 at 03:00:17PM +1000, [EMAIL PROTECTED] wrote: Hi there i have a couple of projects which required fulltext searching so was unable to setup innodb on these. I was wondering if lock tables is a secure way to make the transaction on these tables and does this prevent being read upon aswell? Obtaining a WRITE lock on a MyISAM table prevents readers, yes. But you do have to put the necessary smarts into your code to properly implement a ROLLBACK if you need one. Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Lock tables in myisam
- Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: electroteque [mailto:[EMAIL PROTECTED] --Sent: Wednesday, September 17, 2003 6:38 PM --To: [EMAIL PROTECTED] --Subject: Re: Lock tables in myisam -- --rollback works on myisam ? this is mysql4 anyway, sweet i'll give it a --try. No rollback does not work on myisam Jeremy was stating that you don't have to do what you suggested to implement a correct ROLLBACK in mySQL. Use INNODB. http://www.mysql.com/doc/en/COMMIT.html
Re: Lock tables in myisam
At 11:38 AM +1000 9/18/03, electroteque wrote: rollback works on myisam ? this is mysql4 anyway, sweet i'll give it a try. Rollback *doesn't* work with MyISAM, that's why Jeremy said you have to put the necessary logic in your application if you want to achieve the same effect. At least, that's what I think he said. :-) On Fri, 2003-09-19 at 08:01, Jeremy Zawodny wrote: On Thu, Sep 18, 2003 at 03:00:17PM +1000, [EMAIL PROTECTED] wrote: Hi there i have a couple of projects which required fulltext searching so was unable to setup innodb on these. I was wondering if lock tables is a secure way to make the transaction on these tables and does this prevent being read upon aswell? Obtaining a WRITE lock on a MyISAM table prevents readers, yes. But you do have to put the necessary smarts into your code to properly implement a ROLLBACK if you need one. Jeremy -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lock tables in myisam
Hi, No, ROLLBACK doesn't work with MyISAM. Jeremy meant that you can add logic/code between LOCK/UNLOCK to simulate ROLLBACK. e.g. queries that undo what you did if something goes wrong. Of course this won't cover you if mysqld dies, is killed, or you lose the connection etc. as real transactions would. Matt - Original Message - From: electroteque Sent: Wednesday, September 17, 2003 8:38 PM Subject: Re: Lock tables in myisam rollback works on myisam ? this is mysql4 anyway, sweet i'll give it a try. On Fri, 2003-09-19 at 08:01, Jeremy Zawodny wrote: On Thu, Sep 18, 2003 at 03:00:17PM +1000, [EMAIL PROTECTED] wrote: Hi there i have a couple of projects which required fulltext searching so was unable to setup innodb on these. I was wondering if lock tables is a secure way to make the transaction on these tables and does this prevent being read upon aswell? Obtaining a WRITE lock on a MyISAM table prevents readers, yes. But you do have to put the necessary smarts into your code to properly implement a ROLLBACK if you need one. Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Lock tables in myisam
No rollback does not work on myisam Jeremy was stating that you don't have to do what you suggested to implement a correct ROLLBACK in mySQL. Use INNODB. http://www.mysql.com/doc/en/COMMIT.html Hmm if you got my other post i am trying to simulate innodb in myisiam for projects that require fulltext search i have no choice. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lock tables in myisam
Righty, so if error unlock table hehe, i have found i need to produce my error first then do a rollback for an error to display in php as it wouldnt show a mysql error after a rollback, i guess i could add an unlock table in my trigger error function too. At 11:38 AM +1000 9/18/03, electroteque wrote: rollback works on myisam ? this is mysql4 anyway, sweet i'll give it a try. Rollback *doesn't* work with MyISAM, that's why Jeremy said you have to put the necessary logic in your application if you want to achieve the same effect. At least, that's what I think he said. :-) On Fri, 2003-09-19 at 08:01, Jeremy Zawodny wrote: On Thu, Sep 18, 2003 at 03:00:17PM +1000, [EMAIL PROTECTED] wrote: Hi there i have a couple of projects which required fulltext searching so was unable to setup innodb on these. I was wondering if lock tables is a secure way to make the transaction on these tables and does this prevent being read upon aswell? Obtaining a WRITE lock on a MyISAM table prevents readers, yes. But you do have to put the necessary smarts into your code to properly implement a ROLLBACK if you need one. Jeremy -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lock tables in myisam
On Thu, Sep 18, 2003 at 11:38:17AM +1000, electroteque wrote: rollback works on myisam ? this is mysql4 anyway, sweet i'll give it a try. No. That's what I meant about having to put extra smarts in your code. It needs to be able to undo its actions. On Fri, 2003-09-19 at 08:01, Jeremy Zawodny wrote: On Thu, Sep 18, 2003 at 03:00:17PM +1000, [EMAIL PROTECTED] wrote: Hi there i have a couple of projects which required fulltext searching so was unable to setup innodb on these. I was wondering if lock tables is a secure way to make the transaction on these tables and does this prevent being read upon aswell? Obtaining a WRITE lock on a MyISAM table prevents readers, yes. But you do have to put the necessary smarts into your code to properly implement a ROLLBACK if you need one. Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 4 days, processed 189,881,535 queries (440/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lock tables in myisam
Hi, No, ROLLBACK doesn't work with MyISAM. Jeremy meant that you can add logic/code between LOCK/UNLOCK to simulate ROLLBACK. e.g. queries that undo what you did if something goes wrong. Of course this won't cover you if mysqld dies, is killed, or you lose the connection etc. as real transactions would. Will it be worth my while then to do lock tables, on one of the projects we have about 6 people entering data pretty much at the same time people are searching/reading. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Lock tables in myisam
Hi there i have a couple of projects which required fulltext searching so was unable to setup innodb on these. I was wondering if lock tables is a secure way to make the transaction on these tables and does this prevent being read upon aswell? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: question about lock tables and unlock table
Hi Steven, Just one UNLOCK TABLES. :-) From http://www.mysql.com/doc/en/LOCK_TABLES.html LOCK TABLES locks tables for the current thread. UNLOCK TABLES releases any locks held by the current thread. All tables that are locked by the current thread are implicitly unlocked when the thread issues another LOCK TABLES, or when the connection to the server is closed. BTW, you can't do INSERTs when the table(s) are locked with a READ lock. ;-) Need a WRITE lock for that. Hope that helps. Matt - Original Message - From: Steven Wu Sent: Friday, September 05, 2003 4:57 PM Subject: question about lock tables and unlock table Hi Need some help here: if I have two tables, alertLog, videoLog needed to lock during some processing as: mysql LOCK TABLE alertLog READ, videoLog READ after two tables are lock and do some INSERT, mysql INSERT INTO alertLog(alert); mysql INSERT INTO videoLog(video); My question is DO I NEED ONE OR TWO UNLOCK TABLES to release the locking ? so Is the answer ? mysqlUNLOCK TABLES mysqlUNLOCK TABLES or just mysqlUNLOCK TABLES thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
question about lock tables and unlock table
Hi Need some help here: if I have two tables, alertLog, videoLog needed to lock during some processing as: mysql LOCK TABLE alertLog READ, videoLog READ after two tables are lock and do some INSERT, mysql INSERT INTO alertLog(alert); mysql INSERT INTO videoLog(video); My question is DO I NEED ONE OR TWO UNLOCK TABLES to release the locking ? so Is the answer ? mysqlUNLOCK TABLES mysqlUNLOCK TABLES or just mysqlUNLOCK TABLES thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Lock Tables - Manual Ambiguity
Hi All, The 3.23.54 Manual states in; Section 1.4.4.3 Transactions If you only obtain a read lock (as opposed to a write lock), then reads and inserts are still allowed to happen. The new inserted records will not be seen by any of the clients that have a READ lock until they release their read locks But in Section 6.7.2 Lock Tables/Unlock Tables Syntax If a thread obtains a READ lock on a table, that thread (and all other threads) can only read from the table. If a thread obtains a WRITE lock on a table, then only the thread holding the lock can READ from or WRITE to the table. What am I missing pls? Can I, with a READ Lock, still write to the table? Is the following assumption correct? READ Lock - Thread/Client with Lock, can write to locked Table, and is ONLY ONE who can until Lock released. Thanks in Advance [Filter: SQL MySQL] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lock Tables - Manual Ambiguity
K.L. [EMAIL PROTECTED] wrote: The 3.23.54 Manual states in; Section 1.4.4.3 Transactions If you only obtain a read lock (as opposed to a write lock), then reads and inserts are still allowed to happen. The new inserted records will not be seen by any of the clients that have a READ lock until they release their read locks But in Section 6.7.2 Lock Tables/Unlock Tables Syntax If a thread obtains a READ lock on a table, that thread (and all other threads) can only read from the table. If a thread obtains a WRITE lock on a table, then only the thread holding the lock can READ from or WRITE to the table. What am I missing pls? Can I, with a READ Lock, still write to the table? Only thread that obtains lock on the table can write to the table. Is the following assumption correct? READ Lock - Thread/Client with Lock, can write to locked Table, and is ONLY ONE who can until Lock released. Sure. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lock Tables - Manual Ambiguity
So, when client X has Read lock, the client Y cannot have Write lock, Egor? Iulian - Original Message - From: Egor Egorov [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, June 18, 2003 1:33 PM Subject: Re: Lock Tables - Manual Ambiguity K.L. [EMAIL PROTECTED] wrote: The 3.23.54 Manual states in; Section 1.4.4.3 Transactions If you only obtain a read lock (as opposed to a write lock), then reads and inserts are still allowed to happen. The new inserted records will not be seen by any of the clients that have a READ lock until they release their read locks But in Section 6.7.2 Lock Tables/Unlock Tables Syntax If a thread obtains a READ lock on a table, that thread (and all other threads) can only read from the table. If a thread obtains a WRITE lock on a table, then only the thread holding the lock can READ from or WRITE to the table. What am I missing pls? Can I, with a READ Lock, still write to the table? Only thread that obtains lock on the table can write to the table. Is the following assumption correct? READ Lock - Thread/Client with Lock, can write to locked Table, and is ONLY ONE who can until Lock released. Sure. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lock Tables - Manual Ambiguity
Primaria Falticeni [EMAIL PROTECTED] wrote: So, when client X has Read lock, the client Y cannot have Write lock, Egor? Yes, if client X obtaines read lock on the table client Y can't have write lock on this table until client X releases lock. Iulian - Original Message - From: Egor Egorov [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, June 18, 2003 1:33 PM Subject: Re: Lock Tables - Manual Ambiguity K.L. [EMAIL PROTECTED] wrote: The 3.23.54 Manual states in; Section 1.4.4.3 Transactions If you only obtain a read lock (as opposed to a write lock), then reads and inserts are still allowed to happen. The new inserted records will not be seen by any of the clients that have a READ lock until they release their read locks But in Section 6.7.2 Lock Tables/Unlock Tables Syntax If a thread obtains a READ lock on a table, that thread (and all other threads) can only read from the table. If a thread obtains a WRITE lock on a table, then only the thread holding the lock can READ from or WRITE to the table. What am I missing pls? Can I, with a READ Lock, still write to the table? Only thread that obtains lock on the table can write to the table. Is the following assumption correct? READ Lock - Thread/Client with Lock, can write to locked Table, and is ONLY ONE who can until Lock released. Sure. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOCK TABLES error , on a select without any update ?
Steff, I am carbon copying this to [EMAIL PROTECTED] so that people see the problem was probably found. - Original Message - From: [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, February 23, 2003 5:33 AM Subject: Re: LOCK TABLES error , on a select without any update ? Heikki, Please do NOT do any more research into this problem for the time being. I was finally able to capture a log when the problem occurred in production. In studying the log I discovered two things. 1) The new code to do away with locks has not made it into production. I mistakenly thought it was part of a release this past Thursday, it turns out it is scheduled to go into production this coming Thursday. 2) The log clearly shows where we are doing a lock/unlock on the same connection which we had reserved for exclusive use by the transaction. We need to do some more digging, but my initial guess is that under heavy load our connection manager was occasionally handing out a connection to more then one user. ... Once again much thanks. Bye Steff Best regards, Heikki Innobase Oy sql query - 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
Re: LOCK TABLES error , on a select without any update ?
Steff,
- Original Message -
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Cc: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED];
[EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Saturday, February 22, 2003 1:26 AM
Subject: Re: LOCK TABLES error , on a select without any update ?
Hi Heikki,
Thanks for picking up on this again. After the help from
you and Mark last week, we removed ALL instances of the
lock tables from our application. We used the idea Mark
provided for getting our next sequence number without using
any locks. In the past this was the only thing we where using
table locks for.
ok, good. Actually, with InnoDB you normally do not need table locks at all.
SELECT ... FOR UPDATE is what should be used to lock rows.
Then this definitely is a bug somewhere in your application/MySQL/JDBC
driver/Java/operating system/hardware. Some possibilities:
1. Your application calls MySQL client functions in a wrong way and causes
some confusion.
2. A bug in the JDBC driver of MySQL.
3. A bug in Java.
4. A bug in MySQL.
5. A bug in Windows 2000 (Service Pack 2).
6. A fault in the hardware.
You are the only one who has reported this bug. The error looks like it
would happen inside MySQL, which means it is probably a bug in MySQL or in
your OS/hardware. You could try installing the latest service packs of Win
2000 and VC++ 6.0. Also double check that your application really does not
use LOCK TABLES anywhere.
One important question: if you continue using the connection which threw the
error, does it throw other errors?
In this application we are not even doing anything with
begin/end transaction, we are totally dependent on each SQL
statement standing on its own. We do use a connection pool
and have modified our code to always set the autocommitt
level = 1 before handing any connections out of the pool.
Do you think we are missing some basic setting in our
MySql.ini file ?
No.
Again the thing which makes this so hard to debug is it is
intermittent and only in production, and not always on the
same table or SQL statement. :(
Do you know of any log files we can run which will only
log errors or exceptions?
Sorry, there is no logging of SQL errors only.
This might limit the volume of the
logs to something manageable and still let us see what
connection has the error.
Alternatively do you know what state the MySql
connection thinks it is in, in order for it to want to through this
kind of error? Can we test for the state from within our code ?
Thanks again for your patience and assistance. I really want to
be able to make MySql work for this application.
Hmm... I am forwarding this bug report to Monty. He can look from the code
what could possibly cause a wrong error:
java.sql.SQLException: General error: Table
'productsprovided' was not
locked with LOCK TABLES.
to be thrown. A quick look in the source code shows that in
/sql/sql_base.cpp, on about line 771 there is a test:
if (thd-locked_tables) { ... }
If a garbage value has come to thd-locked_tables, you will get the above
error. Memory corruption caused by MySQL or hardware could explain this
error. But then mysqld would probably crash, which it does not?
If you compile MySQL yourself from source, you can add a printf to
sql_base.cpp to determine if thd-locked_tables becomes non-NULL sometimes.
Thanks
Steff
You are welcome,
Heikki
Innobase Oy
sql query
Steff, We have our connection set to Autocommitt=1, and
No table locks are ever explicitly being done on this table
anyplace in any of our code modules. in MySQL you have to
do LOCK TABLES on EVERY table you use inside a LOCK
TABLES. You cannot lock just some table and use others
unlocked. I repeat that the error could be caused if your
application has a bug and uses the same connection to do the
SELECT as it has used to LOCK some other table. Are you
absolutely sure you do not mix connections in your
application? Did you have the general query log running at the
time of the error? If yes, can you check from it what was the
number of the connection that issued the query resulting in an
error, and did that same connection earlier issue a LOCK
TABLES? Regards, Heikki Innobase Oy sql query
--
Steff McGonagle
Envisage Information Systems, Inc.
Phone (315) 497-9202 x16
www.EnvisageSystems.com
www.Enveritus.com
...
Our MySql environment is as follows:
OS Platform: Windows 2000 Service Pack 2
Machine description:
Compiler : VC++ 6.0
Architecture : i686
Total Memory : 2097151 KB RAM
Server Info3.23.54-max-nt-log
have_innodbYES
innodb_additional_mem_pool_size104857600
innodb_buffer_pool_size1048576000
innodb_data_file_path ibdata1
innodb_data_home_dir
innodb_file_io_threads 4
innodb_force_recovery 0
innodb_thread_concurrency 8
Re: LOCK TABLES error , on a select without any update ?
Heikki, Thanks for the response. Just to be sure that we did not have a piece of code which we had forgotten about, I did a search of the Java classes involved in this application for the word LOCK. I did not find any instance where we are locking tables. If we want to update a set of rows, we are just doing a regular update, we are NOT using any of the Select... FOR UPDATE syntax. I will check to make sure we are running the latest OS service pack. We are not set-up to know if a connection continues to throw an error after the first instance of this lock error. However I doubt that it continues too, given that we use a pool of connections and this error happens infrequently. For example we have had 275 logins so far today, and we have had 17 instances of this error (6 on one web server, 11 on the other webserver (both use the same database server)). This mornings errors all happened within a transaction block (Autocommitt=0). The Java code requested a rollback after detecting the error. The failures on Friday where in a different section of code which is NOT in a transaction block (Autocommitt=1). In both cases the symptoms where the same, a report of a file being accessed without LOCK TABLES. I do not see any pattern with regards to the timing of the errors, though there are cases where both web servers are showing Table NOT LOCKED' errors at the same times. Of the 17 errors this morning, 4 different table names show up in the errors, though it is the same code (different data) being run every time. Given the simplicity of our SQL statements and the overall simplicity of our database schema, I find it really hard to believe that we are coming across a bug in MySql. The only thing we do within this program is look for records, delete records, and update records. No big multi table joins, no special creation / deletion of tables. We just are not that sophisticated. :) I am wondering of the error is really something else, such as a timeout, which is coming back with the wrong error message. The MySql instance is not reporting any errors or exceptions, and the computer has plenty of memory and has not shown any stability problems. CPU utilization on the Database server seems to run between 20 and 50%. The machines hosting the servlets seem to be running between 40% and 90% utilization. At the times the errors occurred today I would have to guess everything would have been running under 50% utilization given that the number of logins per hour was less then 10% of what we see on normal business days. Since the server loads are relatively light on the weekends, I might be able to turn on some logs without impacting performance to much. What logs do you think I should turn on? I am going to send the MySql report to you under a different cover. Perhaps their is something in it which will catch your eye. Thanks Steff On 22 Feb 2003 at 10:52, Heikki Tuuri wrote: From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED], [EMAIL PROTECTED] Copies to: [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED] Subject:Re: LOCK TABLES error , on a select without any update ? Date sent: Sat, 22 Feb 2003 10:52:08 +0200 Steff, - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Saturday, February 22, 2003 1:26 AM Subject: Re: LOCK TABLES error , on a select without any update ? Hi Heikki, Thanks for picking up on this again. After the help from you and Mark last week, we removed ALL instances of the lock tables from our application. We used the idea Mark provided for getting our next sequence number without using any locks. In the past this was the only thing we where using table locks for. ok, good. Actually, with InnoDB you normally do not need table locks at all. SELECT ... FOR UPDATE is what should be used to lock rows. Then this definitely is a bug somewhere in your application/MySQL/JDBC driver/Java/operating system/hardware. Some possibilities: 1. Your application calls MySQL client functions in a wrong way and causes some confusion. 2. A bug in the JDBC driver of MySQL. 3. A bug in Java. 4. A bug in MySQL. 5. A bug in Windows 2000 (Service Pack 2). 6. A fault in the hardware. You are the only one who has reported this bug. The error looks like it would happen inside MySQL, which means it is probably a bug in MySQL or in your OS/hardware. You could try installing the latest service packs of Win 2000 and VC++ 6.0. Also double check that your application really does not use LOCK TABLES anywhere. One important question: if you continue using the connection which threw
Re: LOCK TABLES error , on a select without any update ?
Steff, We have our connection set to Autocommitt=1, and No table locks are ever explicitly being done on this table anyplace in any of our code modules. in MySQL you have to do LOCK TABLES on EVERY table you use inside a LOCK TABLES. You cannot lock just some table and use others unlocked. I repeat that the error could be caused if your application has a bug and uses the same connection to do the SELECT as it has used to LOCK some other table. Are you absolutely sure you do not mix connections in your application? Did you have the general query log running at the time of the error? If yes, can you check from it what was the number of the connection that issued the query resulting in an error, and did that same connection earlier issue a LOCK TABLES? Regards, Heikki Innobase Oy sql query .. Subject: LOCK TABLES error , on a select without any update ? From: Steff.envisage1.com Date: Thu, 20 Feb 2003 17:25:43 -0500 Hi All, Once again we have run into a situation where our production database is throwing an error regarding the use of table locks for no apparent reason. We are running MySql on windows with InnoDb. We have a section of code which is working fine most of the time but occasionally will through an error complaining that a table is not locked. Based on the Java stack dump, the SQL statement which caused this error to appear was Select TransactionTypeID From transactionheader Where TransactionHeaderID =1234 The error reported was : java.sql.SQLException: General error: Table 'transactionheader' was not locked with LOCK TABLES We have our connection set to Autocommitt=1, and No table locks are ever explicitly being done on this table anyplace in any of our code modules. Will someone please help me understand why a select statement without an update would ever cause a tables not Locked error ? Thanks Steff -- Steff McGonagle Envisage Information Systems, Inc. Phone (315) 497-9202 x16 www.EnvisageSystems.com www.Enveritus.com ... Our SQL in this application follows the following pattern. (the following is an section from the MysQL log with just one instance of the application running): 030125 20:24:29 7 Query SET autocommit=0 7 Query BEGIN 7 Query Select * from accountsprovided Where ExternalID='I06'AND FinServProvID = 'C33' 7 Query UPDATE accountsprovided SET Enabled='Y' WHERE AccountID = 'CKCBSBF2994309' 7 Query Select * from account Where AccountID = 'CKCBSBF2994309' 7 Query UPDATE account SET PreLimit=1.00 WHERE AccountID = 'CKCBSBF2994309' 12 Query Lock Table Control Write 12 Query Select * From Control 12 Query Update Control set NextID = 6999244 12 Query Unlock Tables 7 Query INSERT INTO productsowned ( VestedValue, Quantity ) VALUES ( 7293.90, 7293.9) ... About another 40 lines of SQL following this same general pattern but using different tables. 7 Query Update cachestatus Set UpdatedOn = null, UpdatedBy = 'XMLWarehouseLoader' Where PrimaryID = 'CKCBSBF2994310' AND SecondaryID = 'CKCBSBF2994311' 7 Query COMMIT 7 Query SET autocommit=1 - 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
Re: LOCK TABLES error , on a select without any update ?
Hi Heikki, Thanks for picking up on this again. After the help from you and Mark last week, we removed ALL instances of the lock tables from our application. We used the idea Mark provided for getting our next sequence number without using any locks. In the past this was the only thing we where using table locks for. In this application we are not even doing anything with begin/end transaction, we are totally dependent on each SQL statement standing on its own. We do use a connection pool and have modified our code to always set the autocommitt level = 1 before handing any connections out of the pool. Do you think we are missing some basic setting in our MySql.ini file ? Again the thing which makes this so hard to debug is it is intermittent and only in production, and not always on the same table or SQL statement. :( Do you know of any log files we can run which will only log errors or exceptions? This might limit the volume of the logs to something manageable and still let us see what connection has the error. Alternatively do you know what state the MySql connection thinks it is in, in order for it to want to through this kind of error? Can we test for the state from within our code ? Thanks again for your patience and assistance. I really want to be able to make MySql work for this application. Thanks Steff Steff, We have our connection set to Autocommitt=1, and No table locks are ever explicitly being done on this table anyplace in any of our code modules. in MySQL you have to do LOCK TABLES on EVERY table you use inside a LOCK TABLES. You cannot lock just some table and use others unlocked. I repeat that the error could be caused if your application has a bug and uses the same connection to do the SELECT as it has used to LOCK some other table. Are you absolutely sure you do not mix connections in your application? Did you have the general query log running at the time of the error? If yes, can you check from it what was the number of the connection that issued the query resulting in an error, and did that same connection earlier issue a LOCK TABLES? Regards, Heikki Innobase Oy sql query -- Steff McGonagle Envisage Information Systems, Inc. Phone (315) 497-9202 x16 www.EnvisageSystems.com www.Enveritus.com - 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
LOCK TABLES error , on a select without any update ?
Hi All, Once again we have run into a situation where our production database is throwing an error regarding the use of table locks for no apparent reason. We are running MySql on windows with InnoDb. We have a section of code which is working fine most of the time but occasionally will through an error complaining that a table is not locked. Based on the Java stack dump, the SQL statement which caused this error to appear was Select TransactionTypeID From transactionheader Where TransactionHeaderID =1234 The error reported was : java.sql.SQLException: General error: Table 'transactionheader' was not locked with LOCK TABLES We have our connection set to Autocommitt=1, and No table locks are ever explicitly being done on this table anyplace in any of our code modules. Will someone please help me understand why a select statement without an update would ever cause a tables not Locked error ? Thanks Steff -- Steff McGonagle Envisage Information Systems, Inc. Phone (315) 497-9202 x16 www.EnvisageSystems.com www.Enveritus.com - 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
Alternative to LOCK TABLES in InnoDB?
Hi all. In my application I use generators generated manually. I have a table named table_POID with contains the last generator used for the table table. With table type MYISAM I use the follow secuence to obtain the next generator: LOCK TABLE table_POID; SELECT instanceNo FROM table_POID; UPDATE table_POID SET instanceNo = newInstanceNoValue; UNLOCK TABLES; But with table type InnoDB I have to use another connection to do this because the LOCK TABLE sentence commits the transacction. Is there any form to do it in MySQL with InnoDB? Thanks in advance. Iago Sineiro - 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
Lock Tables Query
Hi, As per a previous thread I have found that when you use lock Tables MySql will wait indefinitely for the lock - No timeout or error message. Therefore let me explain my question. Scenario: User 1 locks files for a long running job. (write lock that prevents any access to the files) User 2 logs on, then try's to lock or use these files but can't because user 1 already has the lock. (even a simple select * from xxx will wait forever) The program for user 2 will just appear to hang. How do I give feed back to user 2 to say something like: Unable to lock/use files after waiting 30 seconds, try again later or whatever. IE I would like the program to be able to return control after a specific amount of time if the lock is not satisfied in order to give feedback to the user, rather than the the program just wait and appearing to hang. Better still if there is a system variable or something I can check first to see if the file is locked - but I can't seem to find this in the docs. Most other databases I have used have a timeout value (like the record lock for innodb) so I am having trouble dealing with this scenario. Any Ideas. Thanks Clyde England - 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
Re: Lock Tables Query
I would investigate ways of writing that long running job so that it does not require locking a table for long periods of time. Use relative updates, break the procedure down into smaller lockable steps with pauses for other programs etc. Clyde wrote: Hi, As per a previous thread I have found that when you use lock Tables MySql will wait indefinitely for the lock - No timeout or error message. Therefore let me explain my question. Scenario: User 1 locks files for a long running job. (write lock that prevents any access to the files) User 2 logs on, then try's to lock or use these files but can't because user 1 already has the lock. (even a simple select * from xxx will wait forever) The program for user 2 will just appear to hang. How do I give feed back to user 2 to say something like: Unable to lock/use files after waiting 30 seconds, try again later or whatever. IE I would like the program to be able to return control after a specific amount of time if the lock is not satisfied in order to give feedback to the user, rather than the the program just wait and appearing to hang. Better still if there is a system variable or something I can check first to see if the file is locked - but I can't seem to find this in the docs. Most other databases I have used have a timeout value (like the record lock for innodb) so I am having trouble dealing with this scenario. Any Ideas. Thanks Clyde England - 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 - 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
Lock Tables Timeout value?
Hi, I have searched the docs but can't seem to find information on the time out value for Lock Tables (probably just me) When using Lock Tables how long does MySql wait before giving up if it can't get a lock? Is there any way of changing this time out value? Thanks Clyde England - 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
Re: Lock Tables Timeout value?
Gee, I hope there is no such thing as a timeout value for Lock Tables. :) If two of my programs decide that one of them needs to wait for the other, however long that may take, then I hope MySQL honors that chosen symbiosis. I hope it behaves like a Perl flock(): it just waits, and waits, and waits -- and that is how I want it. :) - Mark System Administrator Asarian-host.org --- If you were supposed to understand it, we wouldn't call it code. - FedEx - Original Message - From: Clyde [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, January 11, 2003 10:12 AM Subject: Lock Tables Timeout value? Hi, I have searched the docs but can't seem to find information on the time out value for Lock Tables (probably just me) When using Lock Tables how long does MySql wait before giving up if it can't get a lock? Is there any way of changing this time out value? Thanks Clyde England - 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
Re: Lock Tables Timeout value?
*** REPLY SEPARATOR *** On 11/01/2003 at 1:29 PM Mark wrote: Gee, I hope there is no such thing as a timeout value for Lock Tables. :) If two of my programs decide that one of them needs to wait for the other, however long that may take, then I hope MySQL honors that chosen symbiosis. I hope it behaves like a Perl flock(): it just waits, and waits, and waits -- and that is how I want it. :) H'mm. If this is so then how do you give feed back to a user when files are locked. Scenario: User 1 locks files for a long running job. User 2 logs on, then try's to lock files but can't because user 1 already has the lock. If there is no timeout value then the program for user 2 will just appear to hang. How do I give feed back to user 2 to say something like: Unable to lock files after waiting 60 seconds, try again later or whatever. IE I would like the program to be able to return control after a specific amount of time if the lock is not satisfied (I would think MySql would give an error message) in order to give feedback to the user, rather than the the program just wait and appearing to hang. This behavior occurs with record locks using InnoDB files. I would have thought a similar approach would have applied to File locks? Thanks Clyde England - 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
Re: Lock Tables Timeout value?
At 17:12 +0800 1/11/03, Clyde wrote: Hi, I have searched the docs but can't seem to find information on the time out value for Lock Tables (probably just me) When using Lock Tables how long does MySql wait before giving up if it can't get a lock? Forever. For table-level locks such as you acquire with LOCK TABLES, there is no timeout. This differs from (implicit) page- or row-level locking such as is performed by the BDB and InnoDB handlers. For such locking levels, it's possible to get deadlock. When the handler detects a deadlock, it aborts one of the deadlocking requests. Is there any way of changing this time out value? Thanks Clyde England - 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
LOCK TABLES + network problems - connections locks remain
Description: Take a client that connects to a remote database via a network connection. Next the client issues a LOCK TABLES command, some write others read. Now the network connection becomes unavailable. MySQL will never timeout the locks nor the clients and the tables will remain locked. How-To-Repeat: One could use a firewall between the client and the remote database to reproduce the problem. Fix: Timeout the locks if client has went away. Submitter-Id: submitter ID Originator: Organization: MySQL support: [none | licence | email support | extended email support ] Synopsis: Insufficient lock handling Severity: Priority: Category: mysql Class: Release: mysql-3.23.49 (Source distribution) Environment: System: Linux irc1 2.4.19-pre10 #1 Sat Jun 8 03:00:02 EEST 2002 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/2.95.4/specs gcc version 2.95.4 20011002 (Debian prerelease) Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Sep 26 22:02 /lib/libc.so.6 - libc-2.2.5.so -rwxr-xr-x1 root root 1153784 Sep 18 12:40 /lib/libc-2.2.5.so -rw-r--r--1 root root 2390970 Sep 18 12:41 /usr/lib/libc.a -rw-r--r--1 root root 178 Sep 18 12:41 /usr/lib/libc.so -rw-r--r--1 root root 726660 Mar 24 2002 /usr/lib/libc-client.so.2001 Configure command: ./configure --prefix=/usr --exec-prefix=/usr --libexecdir=/usr/sbin --datadir=/usr/share --sysconfdir=/etc/mysql --localstatedir=/var/lib/mysql --includedir=/usr/include --infodir=/usr/share/info --mandir=/usr/share/man --enable-shared --with-libwrap --enable-assembler --with-berkeley-db --with-innodb --enable-static --enable-shared --enable-local-infile --with-raid --enable-thread-safe-client --without-readline --with-unix-socket-path=/var/run/mysqld/mysqld.sock --with-mysqld-user=mysql --without-bench --with-client-ldflags=-lstdc++ --with-extra-charsets=all - 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
Lock Tables Bug
MYSQL Server: 3.23.49a-log MYSQL Client: 3.23.3 alpha MYSQLGUI: 1.7.5 Operating System: Windows NT, Windows 2000 When I place a table lock on a table with one computer and issue a query on another while the lock is in affect, the computer that issues the query halts execution of the query with no error message or trace. I have tired this both in by VB6 application and using the MYSQLGui. I have tried to test the data base for the presence of a lock by issuing the SHOW TABLE STATUS command. This aslo halted my MYSQLGUI. Is this a known bug or is there any way to determine if a table has been locked without encountering the bug? Computer 1 :LOCK TABLES Customer_IDs WRITE Computer 2: SELECT * FROM Customer_IDs Halted Program Computer 2: SHOW TABLE STATUS FROM Customer Halted Program Eric Cotting FGL Environmental 805-659-0910 - 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
Re: Lock Tables Bug
It is not a bug. As soon as Computer1 releases the lock, Computer2 will have its query processed. Never write a program that keeps a lock for more than a few miliseconds. Eric Cotting wrote: MYSQL Server: 3.23.49a-log MYSQL Client: 3.23.3 alpha MYSQLGUI: 1.7.5 Operating System: Windows NT, Windows 2000 When I place a table lock on a table with one computer and issue a query on another while the lock is in affect, the computer that issues the query halts execution of the query with no error message or trace. I have tired this both in by VB6 application and using the MYSQLGui. I have tried to test the data base for the presence of a lock by issuing the SHOW TABLE STATUS command. This aslo halted my MYSQLGUI. Is this a known bug or is there any way to determine if a table has been locked without encountering the bug? Computer 1 :LOCK TABLES Customer_IDs WRITE Computer 2: SELECT * FROM Customer_IDs Halted Program Computer 2: SHOW TABLE STATUS FROM Customer Halted Program Eric Cotting FGL Environmental 805-659-0910 - 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 - 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
[PATCH] LOCK TABLES missing a needed check in 4.0.1
Description: Any LOCK TABLES command (both READ and WRITE), executed from a non-root MySQL user, would fail, giving a 'select command denied' error message. This showed up as Bugzilla being unable to update a bug's state, since locking the necessary tables would fail every time. Unfortunately, bandwidth limitations prevent me from building a recent snapshot of the 4.0.x branch from BitKeeper sources, as Alexander Keremidarski [EMAIL PROTECTED] suggested in a private discussion. Thus, I am unable to check whether the problem is still present in recent versions of MySQL. The 'Web access to the MySQL BitKeeper repository' link in the '1.6.4 Useful MySQL-related links' section of the MySQL manual seems not to work: Error 503: Can't find project root. How-To-Repeat: With a 4.0.1 server and client, execute the following commands: Script started on Fri Jun 14 12:04:26 2002 Setting up interactive shell params.. [roam@straylight:p6 ~]$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.0.1-alpha SSL is not in use Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql create database locktest; Query OK, 1 row affected (0.02 sec) mysql grant all on locktest.* to 'lockt'@'localhost' identified by 'lockp'; Query OK, 0 rows affected (0.03 sec) mysql use locktest; Database changed mysql create table t(id integer auto_increment not null primary key); Query OK, 0 rows affected (0.06 sec) mysql insert into t values (); Query OK, 1 row affected (0.07 sec) mysql insert into t values (); Query OK, 1 row affected (0.04 sec) mysql select * from t; ++ | id | ++ | 1 | | 2 | ++ 2 rows in set (0.00 sec) mysql quit Bye [roam@straylight:p6 ~]$ mysql -u lockt -p locktest Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 to server version: 4.0.1-alpha SSL is not in use Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql select * from t; ++ | id | ++ | 1 | | 2 | ++ 2 rows in set (0.00 sec) mysql lock tables t write; ERROR 1142: select command denied to user: 'lockt@localhost' for table 't' mysql \q Bye [roam@straylight:p6 ~]$ exit exit Script done on Fri Jun 14 12:06:21 2002 The 'select command denied' was the one that should not have come up :) After applying the below fix, stopping, rebuilding, reinstalling and starting the server, and reconnecting to the same database: Script started on Fri Jun 14 12:12:48 2002 Setting up interactive shell params.. [roam@straylight:p6 ~]$ mysql -u lockt -p locktest Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.0.1-alpha SSL is not in use Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql select * from t; ++ | id | ++ | 1 | | 2 | ++ 2 rows in set (0.05 sec) mysql lock tables t write; Query OK, 0 rows affected (0.00 sec) mysql insert into t values (), (); Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql lock tables t read; Query OK, 0 rows affected (0.09 sec) mysql unlock tables; Query OK, 0 rows affected (0.01 sec) mysql select * from t; ++ | id | ++ | 1 | | 2 | | 3 | | 4 | ++ 4 rows in set (0.00 sec) mysql \q Bye [roam@straylight:p6 ~]$ exit exit Script done on Fri Jun 14 12:13:28 2002 As you can see, the fix allows the server to process the LOCK TABLES command successfully. Fix: The problem seems to be in sql/sql_parse.cc, in the mysql_execute_command() function. The processing of SQLCOM_LOCK_TABLES calls check_grant(), which calls table_hash_search(). It would seem that table_hash_search() attempts to search a hash that is only initialized by a check_table_access() invocation. All the other command processing blocks within mysql_execute_command() call check_table_access() before check_grant(); adding this call to the SQLCOM_LOCK_TABLES processing block fixes the problem. --- sql/sql_parse.cc.orig Thu Jun 13 17:47:19 2002 +++ sql/sql_parse.ccThu Jun 13 18:29:52 2002 @@ -2020,6 +2020,8 @@ } if (check_db_used(thd,tables) || end_active_trans(thd)) goto error; +if (check_table_access(thd, SELECT_ACL, tables)) + goto error; if (grant_option check_grant(thd,SELECT_ACL | INSERT_ACL | UPDATE_ACL | DELETE_ACL,tables)) goto error; thd-in_lock_tables=1; Submitter-Id: Originator:Peter Pentchev [EMAIL PROTECTED] Organization: MySQL support: none Synopsis: [PATCH] LOCK TABLES missing a needed check in 4.0.1 Severity: serious Priority: low Category: mysql Class: sw-bug
Re: (php thing) Bug #17126 Updated: mysql_pconnect() andmysql_query(LOCK TABLES...) (fwd)
At 14:29 +0200 5/10/02, Stian Skjelstad wrote: Hi I don't know if this is of any interrest etc, but atleast it is a small little issue from the big, big world. This is easily solved by using mysql_connect() rather than mysql_pconnect(). Then the connection won't stay open if the script dies or you forget to UNLOCK, and the MySQL server will automatically unlock. -- Forwarded message -- Date: 10 May 2002 08:41:19 - From: PHP Bug Database [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Bug #17126 Updated: mysql_pconnect() and mysql_query(LOCK TABLES...) ATTENTION! Do NOT reply to this email! To reply, use the web interface found at http://bugs.php.net/?id=17126edit=2 ID: 17126 Updated by: [EMAIL PROTECTED] Reported By: [EMAIL PROTECTED] -Status: Open +Status: Closed Bug Type: MySQL related Operating System: RedHat Linux 7.2 with updates PHP Version: 4.0CVS-2002-05-09 New Comment: PHP is not the appropriate place to do this. If you want auto-unlocking, bug the MySQL-people or learn to write proper code. Previous Comments: [2002-05-09 15:08:49] [EMAIL PROTECTED] This is a test-thing from one of my projects. The problem is the LOCK TABLES not beeing unlocked again.. This is a userproblem, but can sometimes be hard to track when you rerun the script, and hit another Apache-PID and tables still beeing locked.. maybe when a Link identifier is destroyed, or new database is selected, an UNLOCK TABLES should be ommited? testsource from project: ? unset ( $ROOT ); $ROOT = ../; include_once ( $ROOT.include/setup.php ); include_once ( $INCLUDE.mysql.php ); MySQL_DoConnect(); echo Locking...; mysql_query(LOCK TABLES Ansatt WRITE;); echo Done (.mysql_error().)br; ? - 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 - 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
MySQL 4.0.0-alpha lock tables fails for non-root users
Description: Locking tables seems to be broken for non-root mysql users, giving the following error: ERROR 1142: select command denied to user: 'foo@localhost' for table 'bog' Works fine for root. This is using the standard Linux mysql-4.0.1 alpha RPMs. How-To-Repeat: $ mysql -u foo test mysql create table bog (x char(1)); Query OK, 0 rows affected (0.08 sec) mysql lock tables bog write; ERROR 1142: select command denied to user: 'foo@localhost' for table 'bog' mysql lock tables bog read; ERROR 1142: select command denied to user: 'foo@localhost' for table 'bog' mysql drop table bog; Query OK, 0 rows affected (0.15 sec) Fix: Unknown. Submitter-Id: submitter ID Originator:Gavin Carr Organization: Open Fusion Pty Ltd (Australia) MySQL support: None Synopsis: MySQL 4.0.1-alpha lock tables fails for non-root users Severity: Priority: low Category: mysql Class: sw-bug Release: mysql-4.0.0-alpha (Official MySQL RPM) Environment: System: Linux calix.syd.ot 2.4.3-20mdk #1 Sun Apr 15 23:03:10 CEST 2001 i686 unknown Architecture: i686 Some paths: /opt/perl5.6/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i586-mandrake-linux/2.96/specs gcc version 2.96 2731 (Linux-Mandrake 8.0 2.96-0.48mdk) Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='gcc' CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Apr 29 2001 /lib/libc.so.6 - libc-2.2.2.so -rwxr-xr-x1 root root 1222404 May 3 2001 /lib/libc-2.2.2.so -rw-r--r--1 root root 26499142 May 3 2001 /usr/lib/libc.a -rw-r--r--1 root root 178 May 3 2001 /usr/lib/libc.so Configure command: ./configure --disable-shared --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --without-berkeley-db --with-innodb --enable-assembler --with-mysqld-user=mysql --with-unix-socket-path=/var/lib/mysql/mysql.sock --prefix=/ --with-extra-charsets=complex --exec-prefix=/usr --libexecdir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share --localstatedir=/var/lib/mysql --infodir=/usr/info --includedir=/usr/include --mandir=/usr/man --with-embedded-server '--with-comment=Official MySQL RPM' - 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
Re: permission to lock tables
Jeremy Zawodny wrote: On Sun, Jan 20, 2002 at 03:46:14AM +, Arne Mueller wrote: Hi All, I've just migrated from mysql-3.12 to 4.0.1 and despite a mysqld crash a few minutes ago there is a strange problem I've never had in mysql version 3: mysql lock table .develop.Pseq read; ERROR 1064: You have an error in your SQL syntax near '.Pseq read' at line 1 Any idea what's wrong here? How about LOCK TABLES: http://www.mysql.com/doc/L/O/LOCK_TABLES.html Jeremy Hello, Sorry, I realy did read this section in the manual, but somehow the answer must be in between the lines ... I cannot find it :-( . So, please be patient with the simple users and let me know the answer if you know it. Also sorry for the above (quoted) syntax error of the command. Here comes my problem: mysql lock table develop.Pseq write; ERROR 1142: select command denied to user: 'bmm@localhost' for table 'Pseq' User 'bmm' is allowed to connect to from localhost but only has 'process' and 'reload' privileges in the ,ysql.user table, the db table then allows this user everything with the 'develop' db. Root (who has all privileges in the mysql.user table is alowed to lock tables). thanks alot, Arne -- Arne Mueller Biomolecular Modelling Laboratory Imperial Cancer Research Fund 44 Lincoln's Inn Fields London WC2A 3PX, U.K. phone : +44-(0)207 2693405 | fax :+44-(0)207-269-3534 email : [EMAIL PROTECTED] | http://www.bmm.icnet.uk - 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
Re: permission to lock tables
I believe the correct command is LOCK TABLES with an 'S' at the end. your quoted command says LOCK TABLE without the 'S'. HTH At 04:15 PM 1/20/2002 +, Arne Mueller wrote: Jeremy Zawodny wrote: On Sun, Jan 20, 2002 at 03:46:14AM +, Arne Mueller wrote: Hi All, I've just migrated from mysql-3.12 to 4.0.1 and despite a mysqld crash a few minutes ago there is a strange problem I've never had in mysql version 3: mysql lock table .develop.Pseq read; ERROR 1064: You have an error in your SQL syntax near '.Pseq read' at line 1 Any idea what's wrong here? How about LOCK TABLES: http://www.mysql.com/doc/L/O/LOCK_TABLES.html Jeremy Hello, Sorry, I realy did read this section in the manual, but somehow the answer must be in between the lines ... I cannot find it :-( . So, please be patient with the simple users and let me know the answer if you know it. Also sorry for the above (quoted) syntax error of the command. Here comes my problem: mysql lock table develop.Pseq write; ERROR 1142: select command denied to user: 'bmm@localhost' for table 'Pseq' User 'bmm' is allowed to connect to from localhost but only has 'process' and 'reload' privileges in the ,ysql.user table, the db table then allows this user everything with the 'develop' db. Root (who has all privileges in the mysql.user table is alowed to lock tables). thanks alot, Arne -- Arne Mueller Biomolecular Modelling Laboratory Imperial Cancer Research Fund 44 Lincoln's Inn Fields London WC2A 3PX, U.K. phone : +44-(0)207 2693405 | fax :+44-(0)207-269-3534 email : [EMAIL PROTECTED] | http://www.bmm.icnet.uk - 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 Joseph Roth ( JB ) [EMAIL PROTECTED] _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com - 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
permission to lock tables
Hi All, I've just migrated from mysql-3.12 to 4.0.1 and despite a mysqld crash a few minutes ago there is a strange problem I've never had in mysql version 3: mysql lock table .develop.Pseq read; ERROR 1064: You have an error in your SQL syntax near '.Pseq read' at line 1 Any idea what's wrong here? thanks a lot for help, Arne user name is 'bmm', here's the ysql table: mysql select * from user where user = 'bmm' and Host = 'localhost'; +---+--+--+-+-+-+-+-+---+-+---+--+---++-+++ | Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | +---+--+--+-+-+-+-+-+---+-+---+--+---++-+++ | localhost | bmm | 006d987861784d38 | N | N | N | N | N | N | Y | N | Y| Y | N | N | N | N | +---+--+--+-+-+-+-+-+---+-+---+--+---++-+++ mysql select * from user where user = 'bmm'; ++--+--+-+-+-+-+-+---+-+---+--+---++-+++ | Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | ++--+--+-+-+-+-+-+---+-+---+--+---++-+++ | localhost | bmm | 006d987861784d38 | N | N | N | N | N | N | Y | N | Y| Y | N | N | N | N | | %.lif.icnet.uk | bmm | 006d987861784d38 | N | N | N | N | N | N | Y | N | Y| Y | N | N | N | N | | %.mimcluster | bmm | 006d987861784d38 | N | N | N | N | N | N | Y | N | N| N | N | N | N | N | ++--+--+-+-+-+-+-+---+-+---+--+---++-+++ mysql select * from host where Host = 'localhost'; +---++-+-+-+-+-+---++-+++ | Host | Db | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | +---++-+-+-+-+-+---++-+++ | localhost | % | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | +---++-+-+-+-+-+---++-+++ -- Arne Mueller Biomolecular Modelling Laboratory Imperial Cancer Research Fund 44 Lincoln's Inn Fields London WC2A 3PX, U.K. phone : +44-(0)207 2693405 | fax :+44-(0)207-269-3534 email : [EMAIL PROTECTED] | http://www.bmm.icnet.uk - 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
Re: permission to lock tables
On Sun, Jan 20, 2002 at 03:46:14AM +, Arne Mueller wrote: Hi All, I've just migrated from mysql-3.12 to 4.0.1 and despite a mysqld crash a few minutes ago there is a strange problem I've never had in mysql version 3: mysql lock table .develop.Pseq read; ERROR 1064: You have an error in your SQL syntax near '.Pseq read' at line 1 Any idea what's wrong here? How about LOCK TABLES: http://www.mysql.com/doc/L/O/LOCK_TABLES.html Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 17 days, processed 406,741,655 queries (272/sec. avg) - 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
Re: LOCK TABLES issue
I'll try posting this again, because nobody has replied to it. To put the problem more succinctly: I think there is a flaw in the mysql LOCK statement semantics, because if I use LOCK then all tables that are accessed while the LOCK is active must be locked for READ or WRITE access (or insert, but that doesn't help me). This is done I think to protect the application writer against accidently not getting locks that are required. What is needed is an explicit way to allow a particular table to participate while the LOCK statement is active, but without locking it. I've suggested that LOCK ... table NOLOCK would be an appropriate addition to the LOCK statement for these semantics--it allows a table to be explicitly left unlocked. More details are available in my original post, which is included here. I would appreciate comments on this. (If there's a better place to post this, can someone let me know that, too?). Thanks, Bob Bob Sidebotham wrote: I have an application with fairly typical locking requirements: 99% of the requests are read-only (with an exception that I will specify). Update requests are relatively rare. There's a half-dozen tables, and since the inter-relationships are a little complex. I found it easiest, given the performance constraints of my application, to simply lock ALL the tables for READ for the read requests, and to lock ALL of them for WRITE in the case of an update. I think this is a fine, time-tested, conservative locking strategy. It basically can't fail, and gives me adequate performance for my needs. I have ONE table which records access counts/times for each user for individual objects in the system. This table needs to be updated on every access. This table can be updated correctly without obtaining any locks. It is subject to being read at unpredictable times during any of the read-only requests. Since the access table can be read during any of the read-only requests, and since it can be read at any time during the transaction, I have to obtain at least a READ lock for this table along with the other locks (even though I don't really need a read-lock) because MySQL insists that if any tables are locked, then every table you wish to access must also be locked (I assume this feature is intended as a reasonable precaution against accidently forgetting to lock a table that must participate in a transaction). Unfortunately, to update this table I have to either upgrade to a WRITE lock or drop the lock altogether. It's obvious that upgrading to a WRITE lock will cause all my read-only operations to pileup on the WRITE lock. It's also possible for me to drop all the locks (and record the accesses at the very end of the transaction). Less obvious, but I think true, is that this *also* causes serialization, because MySQL must implicitly require all the READ locks on the table to be dropped before allowing me to update it (is this true? If it isn't true, it should be!). I cannot, by the way, use READ LOCAL because I want to use both UPDATE and REPLACE on the table. So I seem to be caught between a LOCK and a hard place, so to speak. What I would like to see would be something like: LOCK TABLES t1 READ, t2 READ, t3 NOLOCK; The semantics of this would be to explicitly recognize that t3 does not need to be locked and can therefore be read or written after this LOCK TABLES request (as opposed to any table that is not mentioned which cannot be read or written). NOLOCK would, of course, be incompatible with READ or WRITE locks, but would be compatible with other NOLOCK locks or with no lock at all, for both read and write operations. If anyone can suggest another way to do this, I'd appreciate it. Otherwise, is there any reaction to this proposal? Does anyone think this is useful functionality? Thanks, Bob Sidebotham - 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 - 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
RE: LOCK TABLES issue
You are using InnoDB or MyISAM??? If u r going for InnoDB, Refer to InnoDB Engine manual for the version 3.23.47 at www.innodb.com. Here you details of different LOCK types. You can select the appropriate lock type at row level for different possibilities. Hope this works!! Cheers :) Rama Raju -Original Message- From: Bob Sidebotham [mailto:[EMAIL PROTECTED]] Sent: Monday, January 07, 2002 2:00 PM To: [EMAIL PROTECTED] Subject: Re: LOCK TABLES issue I'll try posting this again, because nobody has replied to it. To put the problem more succinctly: I think there is a flaw in the mysql LOCK statement semantics, because if I use LOCK then all tables that are accessed while the LOCK is active must be locked for READ or WRITE access (or insert, but that doesn't help me). This is done I think to protect the application writer against accidently not getting locks that are required. What is needed is an explicit way to allow a particular table to participate while the LOCK statement is active, but without locking it. I've suggested that LOCK ... table NOLOCK would be an appropriate addition to the LOCK statement for these semantics--it allows a table to be explicitly left unlocked. More details are available in my original post, which is included here. I would appreciate comments on this. (If there's a better place to post this, can someone let me know that, too?). Thanks, Bob Bob Sidebotham wrote: I have an application with fairly typical locking requirements: 99% of the requests are read-only (with an exception that I will specify). Update requests are relatively rare. There's a half-dozen tables, and since the inter-relationships are a little complex. I found it easiest, given the performance constraints of my application, to simply lock ALL the tables for READ for the read requests, and to lock ALL of them for WRITE in the case of an update. I think this is a fine, time-tested, conservative locking strategy. It basically can't fail, and gives me adequate performance for my needs. I have ONE table which records access counts/times for each user for individual objects in the system. This table needs to be updated on every access. This table can be updated correctly without obtaining any locks. It is subject to being read at unpredictable times during any of the read-only requests. Since the access table can be read during any of the read-only requests, and since it can be read at any time during the transaction, I have to obtain at least a READ lock for this table along with the other locks (even though I don't really need a read-lock) because MySQL insists that if any tables are locked, then every table you wish to access must also be locked (I assume this feature is intended as a reasonable precaution against accidently forgetting to lock a table that must participate in a transaction). Unfortunately, to update this table I have to either upgrade to a WRITE lock or drop the lock altogether. It's obvious that upgrading to a WRITE lock will cause all my read-only operations to pileup on the WRITE lock. It's also possible for me to drop all the locks (and record the accesses at the very end of the transaction). Less obvious, but I think true, is that this *also* causes serialization, because MySQL must implicitly require all the READ locks on the table to be dropped before allowing me to update it (is this true? If it isn't true, it should be!). I cannot, by the way, use READ LOCAL because I want to use both UPDATE and REPLACE on the table. So I seem to be caught between a LOCK and a hard place, so to speak. What I would like to see would be something like: LOCK TABLES t1 READ, t2 READ, t3 NOLOCK; The semantics of this would be to explicitly recognize that t3 does not need to be locked and can therefore be read or written after this LOCK TABLES request (as opposed to any table that is not mentioned which cannot be read or written). NOLOCK would, of course, be incompatible with READ or WRITE locks, but would be compatible with other NOLOCK locks or with no lock at all, for both read and write operations. If anyone can suggest another way to do this, I'd appreciate it. Otherwise, is there any reaction to this proposal? Does anyone think this is useful functionality? Thanks, Bob Sidebotham - 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 - 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
mysqldump fails when using --lock-tables with thousands of tables
Description: When dumping a database with mysqldump using the --opt parameters, certain databases fail being dumped: /usr/bin/mysqldump: Got error: 1017: Can't find file: './db33862090/CKNPRJ.frm' (errno: 24) when using LOCK TABLES By replacing --opt with --add-drop-table --add-locks --all --extended-insert --quick (same as --opt except --lock-tables), these dumps do work fine. When viewing this process in show processlist or in the querylog, mysqldump tries to lock all tables with one LOCK TABLES before any table is being dumped. Locking 8000 tables at once simply has to fail, when only 1024 files can be opened in parallel: that's the problem. How-To-Repeat: Create a database with 8000 tables and try a mysqldump --opt with it. After a few seconds, the dump fails. Fix: Locking all tables at the same time is fine for most backup applications and protects from data inconsistencies like table x has been updated during dump, but not table y. However, when the dump always fails, no backup can be created. Solution: Create a Fallback for mysqldump by using a new option --relaxed: try to lock all tables for read and dump as usual. When this LOCK TABLES fails, spew out a warning and try to dump each table without any locks instead of aborting the whole dump. Depending on your situation, you like the consistency provided by locking all tables, but you'd also prefer to have a maybe-inconsistent backup than no backup at all. Since other people do need a completly consistent database and might want to manually fix it (by dropping tables and sorting out unneeded data) when such a problem occurs, this relaxed has to be an option. Written in pseudo-code: lock_tables_failed=0 sql(LOCK TABLES $table1 READ, $table2 READ, ..) if (--lock-tables) if ($?) then if ($cmdoption --relaxed) then $lock_tables_failed=1 warn (--lock-tables failed, ignoring locks in relaxed mode) else error (--lock-tables failed, Dump aborted) endif endif foreach $table (@all_tables) do dump ($table) done sql (UNLOCK TABLES) unless ($lock_tables_failed) This gives certain advantages: Those who want dumps no matter if the --lock-tables worked but prefer complete backups, can perform such a relaxed backup with --opt --relaxed. Who strictly wants the complete database-integrity offered by --lock-tables can use --opt as before, but also receives a much more detailed error description. Submitter-Id: submitter ID Originator:Anders Henke, [EMAIL PROTECTED] Organization: Schlund+Partner AG MySQL support: none Synopsis: mysqldump --lock-tables fails when trying to lock thousands of tables Severity: serious Priority: low Category: mysql Class: sw-bug Release: mysql-3.23.46 (Source distribution) Server: /usr/bin/mysqladmin Ver 8.23 Distrib 3.23.46, for pc-linux-gnu on i686 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 3.23.46-Max-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/run/mysqld/mysqld.sock Uptime: 24 days 18 hours 18 min 22 sec Threads: 1 Questions: 87659909 Slow queries: 2950 Opens: 9884754 Flush tables: 1 Open tables: 64 Queries per second avg: 40.972 Environment: System: Linux rdb19 2.4.13 #1 SMP Fri Nov 2 13:46:04 CET 2001 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/2.95.2/specs gcc version 2.95.2 2220 (Debian GNU/Linux) Compilation info: CC='gcc' CFLAGS='-O6 -fomit-frame-pointer' CXX='gcc' CXXFLAGS='-O6 -fomit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='-static' LIBC: lrwxrwxrwx1 root root 13 Sep 4 18:35 /lib/libc.so.6 - libc-2.1.3.so -rwxr-xr-x1 root root 888192 Jun 9 2001 /lib/libc-2.1.3.so -rw-r--r--1 root root 2090160 Jun 9 2001 /usr/lib/libc.a -rw-r--r--1 root root 178 Jun 9 2001 /usr/lib/libc.so Configure command: ./configure --prefix=/usr --libexecdir=/usr/sbin --localstatedir=/var/lib/mysql --enable-shared --without-perl --without-readline --without-docs --without-bench --with-mysqld-user=mysql --with-extra-charsets=all --enable-assembler --with-raid --with-mysqld-ldflags=-all
LOCK TABLES issue
I have an application with fairly typical locking requirements: 99% of the requests are read-only (with an exception that I will specify). Update requests are relatively rare. There's a half-dozen tables, and since the inter-relationships are a little complex. I found it easiest, given the performance constraints of my application, to simply lock ALL the tables for READ for the read requests, and to lock ALL of them for WRITE in the case of an update. I think this is a fine, time-tested, conservative locking strategy. It basically can't fail, and gives me adequate performance for my needs. I have ONE table which records access counts/times for each user for individual objects in the system. This table needs to be updated on every access. This table can be updated correctly without obtaining any locks. It is subject to being read at unpredictable times during any of the read-only requests. Since the access table can be read during any of the read-only requests, and since it can be read at any time during the transaction, I have to obtain at least a READ lock for this table along with the other locks (even though I don't really need a read-lock) because MySQL insists that if any tables are locked, then every table you wish to access must also be locked (I assume this feature is intended as a reasonable precaution against accidently forgetting to lock a table that must participate in a transaction). Unfortunately, to update this table I have to either upgrade to a WRITE lock or drop the lock altogether. It's obvious that upgrading to a WRITE lock will cause all my read-only operations to pileup on the WRITE lock. It's also possible for me to drop all the locks (and record the accesses at the very end of the transaction). Less obvious, but I think true, is that this *also* causes serialization, because MySQL must implicitly require all the READ locks on the table to be dropped before allowing me to update it (is this true? If it isn't true, it should be!). I cannot, by the way, use READ LOCAL because I want to use both UPDATE and REPLACE on the table. So I seem to be caught between a LOCK and a hard place, so to speak. What I would like to see would be something like: LOCK TABLES t1 READ, t2 READ, t3 NOLOCK; The semantics of this would be to explicitly recognize that t3 does not need to be locked and can therefore be read or written after this LOCK TABLES request (as opposed to any table that is not mentioned which cannot be read or written). NOLOCK would, of course, be incompatible with READ or WRITE locks, but would be compatible with other NOLOCK locks or with no lock at all, for both read and write operations. If anyone can suggest another way to do this, I'd appreciate it. Otherwise, is there any reaction to this proposal? Does anyone think this is useful functionality? Thanks, Bob Sidebotham - 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
Lock Tables
database Hi there Is there anyway to see what tables are locked and by who? I have a problem where tables are locked but I am not sure who or why they are. Thanks Warren ~ Warren van der Merwe Software Director PRT Trading (Pty) Ltd t/a RedTie Durban, South Africa Cell (+27-83) 262-9163 Office (+27-31) 767-0249 - 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
