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
:
+---+
| 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
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
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 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
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
- 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
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
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
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
, 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
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
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
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
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
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);
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
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
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
: 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
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
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
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
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
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
'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
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;
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
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
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
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
*** 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
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
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
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
. 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
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
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
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 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
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
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
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
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
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
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
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
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
101 - 148 of 148 matches
Mail list logo
