Re: Lock Tables Question

[Paul McCullagh]

Hi David,

On Jun 5, 2007, at 3:55 PM, David T. Ashley wrote:

My only concern with GET_LOCK() is that lock is server-global  
rather than
database-global.  This makes attacks possible in a shared setting  
(some bad

person could disable your database code by going after your lock).

My solution is just to lock all tables in one statement.

The only question I have (and nobody has answered this) is how many  
tables I
can include in a single LOCK TABLE statement.  I thinking anything  
up to a

few thousand shouldn't be a problem.  What is the limit?


The size of a query is limited by the value of the  
"max_allowed_packet" system parameter. So if you want to add more  
tables to your LOCK TABLE statement, you should just be able to  
increase max_allowed_packet until it works.


Regards,

Paul



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Lock Tables Question

[David T. Ashley]

On 6/5/07, Baron Schwartz <[EMAIL PROTECTED]> wrote:


David T. Ashley wrote:
> There is no concept that I'm missing.  I understand what a transaction
is.
> But I just don't want to bothered.  My application is simple enough that
> bogarting the database until all necessary modifications have been made
and
> the tables are again consistent is good enough.
>
> Collisions are handled by serialization.  Period.  Somebody
wins.  Everyone
> else waits.  Works for me.

Then the simplest possible thing to do (besides using transactions, which
IMO would
actually be a LOT less bother!) is use GET_LOCK('database_name').  That
should handle
your requirement to make locks 'database-local.'

In my experience, using LOCK TABLES becomes a spaghetti problem that
begins to involve
more and more things until you are going through *serious* contortions.  I
would avoid
it at all costs.



My only concern with GET_LOCK() is that lock is server-global rather than
database-global.  This makes attacks possible in a shared setting (some bad
person could disable your database code by going after your lock).

My solution is just to lock all tables in one statement.

The only question I have (and nobody has answered this) is how many tables I
can include in a single LOCK TABLE statement.  I thinking anything up to a
few thousand shouldn't be a problem.  What is the limit?

Thanks, Dave.

Re: Lock Tables Question

[Baron Schwartz]

David T. Ashley wrote:

There is no concept that I'm missing.  I understand what a transaction is.
But I just don't want to bothered.  My application is simple enough that
bogarting the database until all necessary modifications have been made and
the tables are again consistent is good enough.

Collisions are handled by serialization.  Period.  Somebody wins.  Everyone
else waits.  Works for me.


Then the simplest possible thing to do (besides using transactions, which IMO would 
actually be a LOT less bother!) is use GET_LOCK('database_name').  That should handle 
your requirement to make locks 'database-local.'


In my experience, using LOCK TABLES becomes a spaghetti problem that begins to involve 
more and more things until you are going through *serious* contortions.  I would avoid 
it at all costs.


Baron

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Lock Tables Question

[David T. Ashley]

On 6/5/07, Brent Baisley <[EMAIL PROTECTED]> wrote:


I think you're missing the concept of a transaction in the database sense.
The idea behind a transaction is that you can perform multiple steps and if
you don't complete all steps, any changes are reversed. The reversal process
is handled by the database.
A good example is moving money from bank account A to B. You specify how
much to move and that amount is debited from account A and credited to
account B, 2 steps. If the first step happens, but not the second, then the
first step needs to be reversed.
Until the transaction is complete, anything querying the data needs to see
bank account in it's state before any transaction started, a type of
versioning.
You seem to be trying implement all this manually, which you would need to
do if you are using MyISAM based tables. But you may not need to use
transactions at all if your data does not have real time dependencies.



I knew somebody was going to say this.  Here is the relevant prose from my
original post.


One more note:  I'm sure that many of the skilled users on this list will be
tempted to advocate more sophisticated methods.  I appreciate all advice,
but I'm just looking for an easy way to serialize access to my database and
guarantee mutual exclusion.  Each operation I want to do would take at most
half a second, so another web process waiting that long won't make a
difference.  Simpler is easier for me.


There is no concept that I'm missing.  I understand what a transaction is.
But I just don't want to bothered.  My application is simple enough that
bogarting the database until all necessary modifications have been made and
the tables are again consistent is good enough.

Collisions are handled by serialization.  Period.  Somebody wins.  Everyone
else waits.  Works for me.

Re: Lock Tables Question

[Brent Baisley]

I think you're missing the concept of a transaction in the database sense.
The idea behind a transaction is that you can perform multiple steps and if
you don't complete all steps, any changes are reversed. The reversal process
is handled by the database.
A good example is moving money from bank account A to B. You specify how
much to move and that amount is debited from account A and credited to
account B, 2 steps. If the first step happens, but not the second, then the
first step needs to be reversed.
Until the transaction is complete, anything querying the data needs to see
bank account in it's state before any transaction started, a type of
versioning.
You seem to be trying implement all this manually, which you would need to
do if you are using MyISAM based tables. But you may not need to use
transactions at all if your data does not have real time dependencies.

On 6/4/07, David T. Ashley <[EMAIL PROTECTED]> wrote:


I decided to go with a simple paradigm for my web-based database.  Rather
than transactions, each process locks the entire database while it is
changing something, then unlocks it.  This just serializes access (all
other
processes will block until the one modifying the database has finished).

The method I was using is something like:

LOCK TABLE thistable, thattable, theothertable,
goshthislistcangetlongtable;
Do whatever is needed;
UNLOCK TABLES;

I probably botched the syntax above.

Rather than enumerate every table in the database (which could get to be a
long list), I wondered if it is possible to just lock one table, with the
gentleman's agreement that in order to modify the database, every process
must first lock that particular table.

For example:

#1)LOCK TABLE x;
#2)Make modifications to tables x, y, and z;
#3)UNLOCK TABLES;

Are there any race conditions in just using one table for this purpose?

For example, SQL guarantees that a given SQL statement is atomic.

But it is guaranteed that #2 will complete before #3 above?

If every process uses the same rule, can anything unexpected happen?

One more note:  I'm sure that many of the skilled users on this list will
be
tempted to advocate more sophisticated methods.  I appreciate all advice,
but I'm just looking for an easy way to serialize access to my database
and
guarantee mutual exclusion.  Each operation I want to do would take at
most
half a second, so another web process waiting that long won't make a
difference.  Simpler is easier for me.

Thanks.

Re: Lock Tables Question

[David T. Ashley]

Once you issue a LOCK TABLES command, you may not access any tables not
in the LOCK statement. You must lock *ALL* tables you will use, perform
your updates, and then UNLOCK TABLES.



I didn't know that.  I reviewed the documentation.  Thanks.

OK, then my only remaining question is how many tables I can lock in a
single SQL statement.  I'm guessing no practical limit (i.e. thousands).

What is the maximum length of a MySQL statement, anyway?

Thanks.

Re: Lock Tables Question

[Gerald L. Clark]

David T. Ashley wrote:

On 6/4/07, Jerry Schwartz <[EMAIL PROTECTED]> wrote:



Whatever you do, make sure that every bit of code that locks multiple
resources locks them in the same order. That's the only way to avoid
deadlocks.




Hi Jerry,

I really appreciate the good advice.

However, my original question is still unanswered.  My original question is
whether I can lock one table (as a gentleman's rule followed by all
processes) to serialize access to the database consisting of many tables.

LOCK TABLE x;
Manipulate many tables, perhaps not even including table x;
UNLOCK TABLES;

My question is really whether MySQL might do some strange optimizations ...
or somehow buffer the middle query so that it completes after the UNLOCK.

Thanks, Dave.

Once you issue a LOCK TABLES command, you may not access any tables not 
in the LOCK statement. You must lock *ALL* tables you will use, perform

your updates, and then UNLOCK TABLES.

--
Gerald L. Clark
Supplier Systems Corporation

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Lock Tables Question

[David T. Ashley]

On 6/4/07, Jerry Schwartz <[EMAIL PROTECTED]> wrote:


Whatever you do, make sure that every bit of code that locks multiple
resources locks them in the same order. That's the only way to avoid
deadlocks.



Hi Jerry,

I really appreciate the good advice.

However, my original question is still unanswered.  My original question is
whether I can lock one table (as a gentleman's rule followed by all
processes) to serialize access to the database consisting of many tables.

LOCK TABLE x;
Manipulate many tables, perhaps not even including table x;
UNLOCK TABLES;

My question is really whether MySQL might do some strange optimizations ...
or somehow buffer the middle query so that it completes after the UNLOCK.

Thanks, Dave.

RE: Lock Tables Question

[Jerry Schwartz]

Whatever you do, make sure that every bit of code that locks multiple
resources locks them in the same order. That's the only way to avoid
deadlocks.

Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com


> -Original Message-
> From: David T. Ashley [mailto:[EMAIL PROTECTED]
> Sent: Monday, June 04, 2007 3:54 PM
> To: mysql@lists.mysql.com
> Subject: Re: Lock Tables Question
>
> On 6/4/07, Gerald L. Clark <[EMAIL PROTECTED]> wrote:
> >
> > David T. Ashley wrote:
>
> >>
> >> LOCK TABLE thistable, thattable, theothertable,
> >> goshthislistcangetlongtable;
> >> Do whatever is needed;
> >> UNLOCK TABLES;
>
> >You could use a string lock for this.
>
> Thanks for the suggestion.  It looks logically correct.
>
> I'd like to stay away from a string lock if possible because
> other database
> users could interfere with it (it is server global, and not
> tied to the
> database).
>
> My original question is still of interest to me ...
>
> Thanks.
>




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Lock Tables Question

[David T. Ashley]

On 6/4/07, Gerald L. Clark <[EMAIL PROTECTED]> wrote:


David T. Ashley wrote:




LOCK TABLE thistable, thattable, theothertable,
goshthislistcangetlongtable;
Do whatever is needed;
UNLOCK TABLES;



You could use a string lock for this.


Thanks for the suggestion.  It looks logically correct.

I'd like to stay away from a string lock if possible because other database
users could interfere with it (it is server global, and not tied to the
database).

My original question is still of interest to me ...

Thanks.

Re: Lock Tables Question

[Gerald L. Clark]

David T. Ashley wrote:

I decided to go with a simple paradigm for my web-based database.  Rather
than transactions, each process locks the entire database while it is
changing something, then unlocks it.  This just serializes access (all 
other

processes will block until the one modifying the database has finished).

The method I was using is something like:

LOCK TABLE thistable, thattable, theothertable, 
goshthislistcangetlongtable;

Do whatever is needed;
UNLOCK TABLES;

I probably botched the syntax above.

Rather than enumerate every table in the database (which could get to be a
long list), I wondered if it is possible to just lock one table, with the
gentleman's agreement that in order to modify the database, every process
must first lock that particular table.

You could use a string lock for this.

--
Gerald L. Clark
Supplier Systems Corporation

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: LOCK TABLES

[mdpeters]

I execute using root permissions. I successfully upgraded mediawiki to 
the latest mediawiki-1.8.2 version for grins. I ran php -cli 
./maintenance/update.php without trouble.



Jerry Schwartz wrote:


I wonder if this is a permissions problem.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341


 


-Original Message-
From: mdpeters [mailto:[EMAIL PROTECTED]
Sent: Monday, October 16, 2006 9:19 PM
To: Dan Buettner
Cc: mysql@lists.mysql.com
Subject: Re: LOCK TABLES

I tried mv archive.frm .archive.frm first. Then I ran
mysqldump again.
It moves past archive and onto another table. I did this 6
times, each
time moving the next one it complained about until I stopped and put
them all back.

Dan Buettner wrote:

   


Try looking in the filesystem for the file(s) called
 


"archive" in the
   


database directory, and move them somewhere else (or if you know you
don't need them, delete them).

If it is/was a MyISAM table, or just an errant file, this
 


should work.
   


If it is/was InnoDB, this will be trickier ...

Dan


On 10/16/06, mdpeters <[EMAIL PROTECTED]> wrote:

 


As you can see, it is clearly showing up but I cannot do anything
with it.

mysql> show tables;
+-+
| Tables_in_horsewiki |
+-+
| archive |
| categorylinks   |
| externallinks   |
| hitcounter  |
| horse_archive   |
| horse_categorylinks |
| horse_externallinks |
| horse_hitcounter|
| horse_image |
| horse_imagelinks|
| horse_interwiki |
| horse_ipblocks  |
| horse_job   |
| horse_logging   |
| horse_math  |
| horse_objectcache   |
| horse_oldimage  |
| horse_page  |
| horse_pagelinks |
| horse_querycache|
| horse_recentchanges |
| horse_revision  |
| horse_searchindex   |
| horse_site_stats|
| horse_templatelinks |
| horse_text  |
| horse_trackbacks|
| horse_transcache|
| horse_user  |
| horse_user_groups   |
| horse_user_newtalk  |
| horse_validate  |
| horse_watchlist |
| image   |
| imagelinks  |
| interwiki   |
| ipblocks|
| job |
| logging |
| math|
| objectcache |
| oldimage|
| page|
| pagelinks   |
| querycache  |
| recentchanges   |
| revision|
| searchindex |
| site_stats  |
| templatelinks   |
| text|
| trackbacks  |
| transcache  |
| user|
| user_groups |
| user_newtalk|
| validate|
| watchlist   |
+-+
58 rows in set (0.00 sec)

mysql> SELECT * FROM archive LIMIT 1;
ERROR 1146 (42S02): Table 'horsewiki.archive' doesn't exist

mysql> drop table archive;
ERROR 1051 (42S02): Unknown table 'archive'




Dan Buettner wrote:

   


Michael, is the 'archive' table present in your
 


database?  e.g., if
   


you do a 'LIST TABLES', does it show up?  What happens
 


if you do a
   


"SELECT * FROM archive LIMIT 1" ?

I'd hazard a guess that you may have a table definition
 


file with no
   


actual table data files, if you're on MyISAM tables.

If you don't need the archive table, can you DROP it
 


successfully?
   


Dan


On 10/16/06, mdpeters <[EMAIL PROTECTED]> wrote:

 


I am having a devil of a time moving a database from
   


one server to
   


another. My database is one that is in production to support the
mediawiki wiki application. This is a Solaris Sparc 10 system
   


using the
   


mysql-max-5.0.20a-solaris10-sparc version. My database name is
horsewiki.

I execute this:
# mysqldump --user root --password=password horsewiki >
   


horsewiki.sql
   


and get this:
mysqldump: Got error: 1146: Table 'horsewiki.archive' doesn't
   


exist when
   


using LOCK TABLES

I have tried using phpMyAdmin-2.9.0.2. It seems to let
   


me export the
   


database to an SQL file. When I attempt to import it on
   


the other
   


system, I get this:

Error

There seems to be an error in your SQL query. The MySQL
   


server error
   


output below, if there is any, may also help you in
   


diagnosing the
   


problem

ERROR: Unknown Punctuation String @ 494
STR: />
SQL: -- phpMyAdmin SQL Dump
-- version 2.9.0.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Oct 16, 2006 at 10:00 AM
-- Server version: 5.0.20
-- PHP Version: 5.1.6
--
-- Database: `horsewiki`
--
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd";>
http://www.w3.org/1999/xhtml";
   


xml:lang=&

RE: LOCK TABLES

[Jerry Schwartz]

I wonder if this is a permissions problem.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341


> -Original Message-
> From: mdpeters [mailto:[EMAIL PROTECTED]
> Sent: Monday, October 16, 2006 9:19 PM
> To: Dan Buettner
> Cc: mysql@lists.mysql.com
> Subject: Re: LOCK TABLES
>
> I tried mv archive.frm .archive.frm first. Then I ran
> mysqldump again.
> It moves past archive and onto another table. I did this 6
> times, each
> time moving the next one it complained about until I stopped and put
> them all back.
>
> Dan Buettner wrote:
>
> > Try looking in the filesystem for the file(s) called
> "archive" in the
> > database directory, and move them somewhere else (or if you know you
> > don't need them, delete them).
> >
> > If it is/was a MyISAM table, or just an errant file, this
> should work.
> > If it is/was InnoDB, this will be trickier ...
> >
> > Dan
> >
> >
> > On 10/16/06, mdpeters <[EMAIL PROTECTED]> wrote:
> >
> >> As you can see, it is clearly showing up but I cannot do anything
> >> with it.
> >>
> >> mysql> show tables;
> >> +-+
> >> | Tables_in_horsewiki |
> >> +-+
> >> | archive |
> >> | categorylinks   |
> >> | externallinks   |
> >> | hitcounter  |
> >> | horse_archive   |
> >> | horse_categorylinks |
> >> | horse_externallinks |
> >> | horse_hitcounter|
> >> | horse_image |
> >> | horse_imagelinks|
> >> | horse_interwiki |
> >> | horse_ipblocks  |
> >> | horse_job   |
> >> | horse_logging   |
> >> | horse_math  |
> >> | horse_objectcache   |
> >> | horse_oldimage  |
> >> | horse_page  |
> >> | horse_pagelinks |
> >> | horse_querycache|
> >> | horse_recentchanges |
> >> | horse_revision  |
> >> | horse_searchindex   |
> >> | horse_site_stats|
> >> | horse_templatelinks |
> >> | horse_text  |
> >> | horse_trackbacks|
> >> | horse_transcache|
> >> | horse_user  |
> >> | horse_user_groups   |
> >> | horse_user_newtalk  |
> >> | horse_validate  |
> >> | horse_watchlist |
> >> | image   |
> >> | imagelinks  |
> >> | interwiki   |
> >> | ipblocks|
> >> | job |
> >> | logging |
> >> | math|
> >> | objectcache |
> >> | oldimage|
> >> | page|
> >> | pagelinks   |
> >> | querycache  |
> >> | recentchanges   |
> >> | revision|
> >> | searchindex |
> >> | site_stats  |
> >> | templatelinks   |
> >> | text|
> >> | trackbacks  |
> >> | transcache  |
> >> | user|
> >> | user_groups |
> >> | user_newtalk|
> >> | validate|
> >> | watchlist   |
> >> +-+
> >> 58 rows in set (0.00 sec)
> >>
> >> mysql> SELECT * FROM archive LIMIT 1;
> >> ERROR 1146 (42S02): Table 'horsewiki.archive' doesn't exist
> >>
> >> mysql> drop table archive;
> >> ERROR 1051 (42S02): Unknown table 'archive'
> >>
> >>
> >>
> >>
> >> Dan Buettner wrote:
> >>
> >> > Michael, is the 'archive' table present in your
> database?  e.g., if
> >> > you do a 'LIST TABLES', does it show up?  What happens
> if you do a
> >> > "SELECT * FROM archive LIMIT 1" ?
> >> >
> >> > I'd hazard a guess that you may have a table definition
> file with no
> >> > actual table data files, if you're on MyISAM tables.
> >> >
> >> > If you don't need the archive table, can you DROP it
> successfully?
> >> >
> >> > Dan
> >> >
> >> >
> >> > On 10/16/06, mdpeters <[EMAIL PROTECTED]> wrote:
> >> >
> >> >> I am having a devil of a time moving a database from
> one server to
> >> >> another. My database

Re: LOCK TABLES

[mdpeters]

I tried this first to no avail.

mysqldump --user root --password=password --skip-lock-tables horsewiki > 
horsewiki.sql
mysqldump: mysqldump: Couldn't execute 'show create table `archive`': 
Table 'horsewiki.archive' doesn't exist (1146)


I'll try the update next.


Visolve DB Team wrote:


Hi
 
From the analysis of other sources,
 
The error may be due to:
 
1. MediaWiki was updated from an older version without updating the 
database. so to update the database, you can use either the 
maintenance script "maintenance/update.php" via the command line, or 
the web installer (rename

LocalSettings.php, then go to the wiki).
2.  --opt is enabled by default with mysqldump, and part of what it 
does it lock tables. So try the backup without lock tables, by adding 
*--skip-lock-tables.*
 
Thanks

ViSolve DB Team.
 
- Original Message -
From: "mdpeters" <[EMAIL PROTECTED] 
>

To: mailto:mysql@lists.mysql.com>>
Sent: Monday, October 16, 2006 9:14 PM
Subject: LOCK TABLES

>I am having a devil of a time moving a database from one server to
> another. My database is one that is in production to support the
> mediawiki wiki application. This is a Solaris Sparc 10 system using the
> mysql-max-5.0.20a-solaris10-sparc version. My database name is 
horsewiki.

>
> I execute this:
> # mysqldump --user root --password=password horsewiki > horsewiki.sql
> and get this:
> mysqldump: Got error: 1146: Table 'horsewiki.archive' doesn't exist 
when

> using LOCK TABLES
>
> I have tried using phpMyAdmin-2.9.0.2. It seems to let me export the
> database to an SQL file. When I attempt to import it on the other
> system, I get this:
> 
> Error
>
> There seems to be an error in your SQL query. The MySQL server error
> output below, if there is any, may also help you in diagnosing the 
problem

>
> ERROR: Unknown Punctuation String @ 494
> STR: />
> SQL: -- phpMyAdmin SQL Dump
> -- version 2.9.0.2
> -- http://www.phpmyadmin.net
> --
> -- Host: localhost
> -- Generation Time: Oct 16, 2006 at 10:00 AM
> -- Server version: 5.0.20
> -- PHP Version: 5.1.6
> --
> -- Database: `horsewiki`
> --
> "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd";>
> http://www.w3.org/1999/xhtml"; xml:lang="en" lang="en"
> dir="ltr">
> 
>
>
>phpMyAdmin
>
>> 
href="./css/phpmyadmin.css.php?token=7c73a56802fc2e39fe41180d721&js_frame=right" 


> />
> href="./css/print.css?token=7c73a56802fc2ee8b4239fe721" media="print" />
>

Re: LOCK TABLES

[mdpeters]

mysqldump --user root --password=password horsewiki > horsewiki.sql



Dan Buettner wrote:


Hmmm, sounds like something's pretty abnormal here.  Any idea what may
have been done here?

I wonder if you could step around this with a call to mysqldump that
doesn't explicitly lock tables ... what is the commad you're running
again?

Dan


On 10/16/06, mdpeters <[EMAIL PROTECTED]> wrote:


I tried mv archive.frm .archive.frm first. Then I ran mysqldump again.
It moves past archive and onto another table. I did this 6 times, each
time moving the next one it complained about until I stopped and put
them all back.

Dan Buettner wrote:

> Try looking in the filesystem for the file(s) called "archive" in the
> database directory, and move them somewhere else (or if you know you
> don't need them, delete them).
>
> If it is/was a MyISAM table, or just an errant file, this should work.
> If it is/was InnoDB, this will be trickier ...
>
> Dan
>
>
> On 10/16/06, mdpeters <[EMAIL PROTECTED]> wrote:
>
>> As you can see, it is clearly showing up but I cannot do anything
>> with it.
>>
>> mysql> show tables;
>> +-+
>> | Tables_in_horsewiki |
>> +-+
>> | archive |
>> | categorylinks   |
>> | externallinks   |
>> | hitcounter  |
>> | horse_archive   |
>> | horse_categorylinks |
>> | horse_externallinks |
>> | horse_hitcounter|
>> | horse_image |
>> | horse_imagelinks|
>> | horse_interwiki |
>> | horse_ipblocks  |
>> | horse_job   |
>> | horse_logging   |
>> | horse_math  |
>> | horse_objectcache   |
>> | horse_oldimage  |
>> | horse_page  |
>> | horse_pagelinks |
>> | horse_querycache|
>> | horse_recentchanges |
>> | horse_revision  |
>> | horse_searchindex   |
>> | horse_site_stats|
>> | horse_templatelinks |
>> | horse_text  |
>> | horse_trackbacks|
>> | horse_transcache|
>> | horse_user  |
>> | horse_user_groups   |
>> | horse_user_newtalk  |
>> | horse_validate  |
>> | horse_watchlist |
>> | image   |
>> | imagelinks  |
>> | interwiki   |
>> | ipblocks|
>> | job |
>> | logging |
>> | math|
>> | objectcache |
>> | oldimage|
>> | page|
>> | pagelinks   |
>> | querycache  |
>> | recentchanges   |
>> | revision|
>> | searchindex |
>> | site_stats  |
>> | templatelinks   |
>> | text|
>> | trackbacks  |
>> | transcache  |
>> | user|
>> | user_groups |
>> | user_newtalk|
>> | validate|
>> | watchlist   |
>> +-+
>> 58 rows in set (0.00 sec)
>>
>> mysql> SELECT * FROM archive LIMIT 1;
>> ERROR 1146 (42S02): Table 'horsewiki.archive' doesn't exist
>>
>> mysql> drop table archive;
>> ERROR 1051 (42S02): Unknown table 'archive'
>>
>>
>>
>>
>> Dan Buettner wrote:
>>
>> > Michael, is the 'archive' table present in your database?  e.g., if
>> > you do a 'LIST TABLES', does it show up?  What happens if you do a
>> > "SELECT * FROM archive LIMIT 1" ?
>> >
>> > I'd hazard a guess that you may have a table definition file 
with no

>> > actual table data files, if you're on MyISAM tables.
>> >
>> > If you don't need the archive table, can you DROP it successfully?
>> >
>> > Dan
>> >
>> >
>> > On 10/16/06, mdpeters <[EMAIL PROTECTED]> wrote:
>> >
>> >> I am having a devil of a time moving a database from one server to
>> >> another. My database is one that is in production to support the
>> >> mediawiki wiki application. This is a Solaris Sparc 10 system
>> using the
>> >> mysql-max-5.0.20a-solaris10-sparc version. My database name is
>> >> horsewiki.
>> >>
>> >> I execute this:
>> >> # mysqldump --user root --password=password horsewiki > 
horsewiki.sql

>> >> and get this:
>> >> mysqldump: Got error: 1146: Table 'horsewiki.archive' doesn't
>> exist when
>> >> using LOCK TABLES
>> >>
>> >> I have tried using phpMyAdmin-2.9.0.2. It seems to let me 
export the

>> >> database to an SQL file. When I attempt to import it on the other
>> >> system, I get this:
>> >> 
>> >> Error
>> >>
>> >> There seems to be an error in your SQL query. The MySQL server 
error

>> >> output below, if there is any, may also help you in diagnosing the
>> >> problem
>> >>
>> >> ERROR: Unknown Punctuation String @ 494
>> >> STR: />
>> >> SQL: -- phpMyAdmin SQL Dump
>> >> -- version 2.9.0.2
>> >> -- http://www.phpmyadmin.net
>> >> --
>> >> -- Host: localhost
>> >> -- Generation Time: Oct 16, 2006 at 10:00 AM
>> >> -- Server version: 5.0.20
>> >> -- PHP Version: 5.1.6
>> >> --
>> >> -- Database: `horsewiki`
>> >> --
>> >> > >> "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd";>
>> >> http://www.w3.org/1999/xhtml"; xml:lang="en" lang="en"
>> >> dir="ltr">
>> >> 
>> >> 
>> >> > >> type="image/x-ico

Re: LOCK TABLES

[Visolve DB Team]

Hi

>From the analysis of other sources,

The error may be due to:

1. MediaWiki was updated from an older version without updating the database. 
so to update the database, you can use either the maintenance script 
"maintenance/update.php" via the command line, or the web installer (rename 
LocalSettings.php, then go to the wiki). 

2.  --opt is enabled by default with mysqldump, and part of what it does it 
lock tables. So try the backup without lock tables, by adding 
--skip-lock-tables.

Thanks
ViSolve DB Team.

- Original Message - 
From: "mdpeters" <[EMAIL PROTECTED]>
To: 
Sent: Monday, October 16, 2006 9:14 PM
Subject: LOCK TABLES


>I am having a devil of a time moving a database from one server to 
> another. My database is one that is in production to support the 
> mediawiki wiki application. This is a Solaris Sparc 10 system using the 
> mysql-max-5.0.20a-solaris10-sparc version. My database name is horsewiki.
> 
> I execute this:
> # mysqldump --user root --password=password horsewiki > horsewiki.sql
> and get this:
> mysqldump: Got error: 1146: Table 'horsewiki.archive' doesn't exist when 
> using LOCK TABLES
> 
> I have tried using phpMyAdmin-2.9.0.2. It seems to let me export the 
> database to an SQL file. When I attempt to import it on the other 
> system, I get this:
> 
> Error
> 
> There seems to be an error in your SQL query. The MySQL server error 
> output below, if there is any, may also help you in diagnosing the problem
> 
> ERROR: Unknown Punctuation String @ 494
> STR: />
> SQL: -- phpMyAdmin SQL Dump
> -- version 2.9.0.2
> -- http://www.phpmyadmin.net
> -- 
> -- Host: localhost
> -- Generation Time: Oct 16, 2006 at 10:00 AM
> -- Server version: 5.0.20
> -- PHP Version: 5.1.6
> -- 
> -- Database: `horsewiki`
> -- 
> "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd";>
> http://www.w3.org/1999/xhtml"; xml:lang="en" lang="en" 
> dir="ltr">
> 
>
>
>phpMyAdmin
>
> href="./css/phpmyadmin.css.php?token=7c73a56802fc2e39fe41180d721&js_frame=right"
>  
> />
> href="./css/print.css?token=7c73a56802fc2ee8b4239fe721" media="print" />
>

Re: Re: LOCK TABLES

[Dan Buettner]

Hmmm, sounds like something's pretty abnormal here.  Any idea what may
have been done here?

I wonder if you could step around this with a call to mysqldump that
doesn't explicitly lock tables ... what is the commad you're running
again?

Dan


On 10/16/06, mdpeters <[EMAIL PROTECTED]> wrote:

I tried mv archive.frm .archive.frm first. Then I ran mysqldump again.
It moves past archive and onto another table. I did this 6 times, each
time moving the next one it complained about until I stopped and put
them all back.

Dan Buettner wrote:

> Try looking in the filesystem for the file(s) called "archive" in the
> database directory, and move them somewhere else (or if you know you
> don't need them, delete them).
>
> If it is/was a MyISAM table, or just an errant file, this should work.
> If it is/was InnoDB, this will be trickier ...
>
> Dan
>
>
> On 10/16/06, mdpeters <[EMAIL PROTECTED]> wrote:
>
>> As you can see, it is clearly showing up but I cannot do anything
>> with it.
>>
>> mysql> show tables;
>> +-+
>> | Tables_in_horsewiki |
>> +-+
>> | archive |
>> | categorylinks   |
>> | externallinks   |
>> | hitcounter  |
>> | horse_archive   |
>> | horse_categorylinks |
>> | horse_externallinks |
>> | horse_hitcounter|
>> | horse_image |
>> | horse_imagelinks|
>> | horse_interwiki |
>> | horse_ipblocks  |
>> | horse_job   |
>> | horse_logging   |
>> | horse_math  |
>> | horse_objectcache   |
>> | horse_oldimage  |
>> | horse_page  |
>> | horse_pagelinks |
>> | horse_querycache|
>> | horse_recentchanges |
>> | horse_revision  |
>> | horse_searchindex   |
>> | horse_site_stats|
>> | horse_templatelinks |
>> | horse_text  |
>> | horse_trackbacks|
>> | horse_transcache|
>> | horse_user  |
>> | horse_user_groups   |
>> | horse_user_newtalk  |
>> | horse_validate  |
>> | horse_watchlist |
>> | image   |
>> | imagelinks  |
>> | interwiki   |
>> | ipblocks|
>> | job |
>> | logging |
>> | math|
>> | objectcache |
>> | oldimage|
>> | page|
>> | pagelinks   |
>> | querycache  |
>> | recentchanges   |
>> | revision|
>> | searchindex |
>> | site_stats  |
>> | templatelinks   |
>> | text|
>> | trackbacks  |
>> | transcache  |
>> | user|
>> | user_groups |
>> | user_newtalk|
>> | validate|
>> | watchlist   |
>> +-+
>> 58 rows in set (0.00 sec)
>>
>> mysql> SELECT * FROM archive LIMIT 1;
>> ERROR 1146 (42S02): Table 'horsewiki.archive' doesn't exist
>>
>> mysql> drop table archive;
>> ERROR 1051 (42S02): Unknown table 'archive'
>>
>>
>>
>>
>> Dan Buettner wrote:
>>
>> > Michael, is the 'archive' table present in your database?  e.g., if
>> > you do a 'LIST TABLES', does it show up?  What happens if you do a
>> > "SELECT * FROM archive LIMIT 1" ?
>> >
>> > I'd hazard a guess that you may have a table definition file with no
>> > actual table data files, if you're on MyISAM tables.
>> >
>> > If you don't need the archive table, can you DROP it successfully?
>> >
>> > Dan
>> >
>> >
>> > On 10/16/06, mdpeters <[EMAIL PROTECTED]> wrote:
>> >
>> >> I am having a devil of a time moving a database from one server to
>> >> another. My database is one that is in production to support the
>> >> mediawiki wiki application. This is a Solaris Sparc 10 system
>> using the
>> >> mysql-max-5.0.20a-solaris10-sparc version. My database name is
>> >> horsewiki.
>> >>
>> >> I execute this:
>> >> # mysqldump --user root --password=password horsewiki > horsewiki.sql
>> >> and get this:
>> >> mysqldump: Got error: 1146: Table 'horsewiki.archive' doesn't
>> exist when
>> >> using LOCK TABLES
>> >>
>> >> I have tried using phpMyAdmin-2.9.0.2. It seems to let me export the
>> >> database to an SQL file. When I attempt to import it on the other
>> >> system, I get this:
>> >> 
>> >> Error
>> >>
>> >> There seems to be an error in your SQL query. The MySQL server error
>> >> output below, if there is any, may also help you in diagnosing the
>> >> problem
>> >>
>> >> ERROR: Unknown Punctuation String @ 494
>> >> STR: />
>> >> SQL: -- phpMyAdmin SQL Dump
>> >> -- version 2.9.0.2
>> >> -- http://www.phpmyadmin.net
>> >> --
>> >> -- Host: localhost
>> >> -- Generation Time: Oct 16, 2006 at 10:00 AM
>> >> -- Server version: 5.0.20
>> >> -- PHP Version: 5.1.6
>> >> --
>> >> -- Database: `horsewiki`
>> >> --
>> >> > >> "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd";>
>> >> http://www.w3.org/1999/xhtml"; xml:lang="en" lang="en"
>> >> dir="ltr">
>> >> 
>> >> 
>> >> > >> type="image/x-icon" />
>> >> phpMyAdmin
>> >> 
>> >> > >>
>> 
href="./css/phpmyadmin.css.php?token=7c73a568

Re: LOCK TABLES

[mdpeters]

I tried mv archive.frm .archive.frm first. Then I ran mysqldump again. 
It moves past archive and onto another table. I did this 6 times, each 
time moving the next one it complained about until I stopped and put 
them all back.


Dan Buettner wrote:


Try looking in the filesystem for the file(s) called "archive" in the
database directory, and move them somewhere else (or if you know you
don't need them, delete them).

If it is/was a MyISAM table, or just an errant file, this should work.
If it is/was InnoDB, this will be trickier ...

Dan


On 10/16/06, mdpeters <[EMAIL PROTECTED]> wrote:

As you can see, it is clearly showing up but I cannot do anything 
with it.


mysql> show tables;
+-+
| Tables_in_horsewiki |
+-+
| archive |
| categorylinks   |
| externallinks   |
| hitcounter  |
| horse_archive   |
| horse_categorylinks |
| horse_externallinks |
| horse_hitcounter|
| horse_image |
| horse_imagelinks|
| horse_interwiki |
| horse_ipblocks  |
| horse_job   |
| horse_logging   |
| horse_math  |
| horse_objectcache   |
| horse_oldimage  |
| horse_page  |
| horse_pagelinks |
| horse_querycache|
| horse_recentchanges |
| horse_revision  |
| horse_searchindex   |
| horse_site_stats|
| horse_templatelinks |
| horse_text  |
| horse_trackbacks|
| horse_transcache|
| horse_user  |
| horse_user_groups   |
| horse_user_newtalk  |
| horse_validate  |
| horse_watchlist |
| image   |
| imagelinks  |
| interwiki   |
| ipblocks|
| job |
| logging |
| math|
| objectcache |
| oldimage|
| page|
| pagelinks   |
| querycache  |
| recentchanges   |
| revision|
| searchindex |
| site_stats  |
| templatelinks   |
| text|
| trackbacks  |
| transcache  |
| user|
| user_groups |
| user_newtalk|
| validate|
| watchlist   |
+-+
58 rows in set (0.00 sec)

mysql> SELECT * FROM archive LIMIT 1;
ERROR 1146 (42S02): Table 'horsewiki.archive' doesn't exist

mysql> drop table archive;
ERROR 1051 (42S02): Unknown table 'archive'




Dan Buettner wrote:

> Michael, is the 'archive' table present in your database?  e.g., if
> you do a 'LIST TABLES', does it show up?  What happens if you do a
> "SELECT * FROM archive LIMIT 1" ?
>
> I'd hazard a guess that you may have a table definition file with no
> actual table data files, if you're on MyISAM tables.
>
> If you don't need the archive table, can you DROP it successfully?
>
> Dan
>
>
> On 10/16/06, mdpeters <[EMAIL PROTECTED]> wrote:
>
>> I am having a devil of a time moving a database from one server to
>> another. My database is one that is in production to support the
>> mediawiki wiki application. This is a Solaris Sparc 10 system 
using the

>> mysql-max-5.0.20a-solaris10-sparc version. My database name is
>> horsewiki.
>>
>> I execute this:
>> # mysqldump --user root --password=password horsewiki > horsewiki.sql
>> and get this:
>> mysqldump: Got error: 1146: Table 'horsewiki.archive' doesn't 
exist when

>> using LOCK TABLES
>>
>> I have tried using phpMyAdmin-2.9.0.2. It seems to let me export the
>> database to an SQL file. When I attempt to import it on the other
>> system, I get this:
>> 
>> Error
>>
>> There seems to be an error in your SQL query. The MySQL server error
>> output below, if there is any, may also help you in diagnosing the
>> problem
>>
>> ERROR: Unknown Punctuation String @ 494
>> STR: />
>> SQL: -- phpMyAdmin SQL Dump
>> -- version 2.9.0.2
>> -- http://www.phpmyadmin.net
>> --
>> -- Host: localhost
>> -- Generation Time: Oct 16, 2006 at 10:00 AM
>> -- Server version: 5.0.20
>> -- PHP Version: 5.1.6
>> --
>> -- Database: `horsewiki`
>> --
>> > "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd";>
>> http://www.w3.org/1999/xhtml"; xml:lang="en" lang="en"
>> dir="ltr">
>> 
>> 
>> > type="image/x-icon" />
>> phpMyAdmin
>> 
>> >> 
href="./css/phpmyadmin.css.php?token=7c73a56802fc2e39fe41180d721&js_frame=right" 


>>
>> />
>> >> href="./css/print.css?token=7c73a56802fc2ee8b4239fe721" 
media="print" />

>> 

Re: LOCK TABLES

[Dan Buettner]

Try looking in the filesystem for the file(s) called "archive" in the
database directory, and move them somewhere else (or if you know you
don't need them, delete them).

If it is/was a MyISAM table, or just an errant file, this should work.
If it is/was InnoDB, this will be trickier ...

Dan


On 10/16/06, mdpeters <[EMAIL PROTECTED]> wrote:

As you can see, it is clearly showing up but I cannot do anything with it.

mysql> show tables;
+-+
| Tables_in_horsewiki |
+-+
| archive |
| categorylinks   |
| externallinks   |
| hitcounter  |
| horse_archive   |
| horse_categorylinks |
| horse_externallinks |
| horse_hitcounter|
| horse_image |
| horse_imagelinks|
| horse_interwiki |
| horse_ipblocks  |
| horse_job   |
| horse_logging   |
| horse_math  |
| horse_objectcache   |
| horse_oldimage  |
| horse_page  |
| horse_pagelinks |
| horse_querycache|
| horse_recentchanges |
| horse_revision  |
| horse_searchindex   |
| horse_site_stats|
| horse_templatelinks |
| horse_text  |
| horse_trackbacks|
| horse_transcache|
| horse_user  |
| horse_user_groups   |
| horse_user_newtalk  |
| horse_validate  |
| horse_watchlist |
| image   |
| imagelinks  |
| interwiki   |
| ipblocks|
| job |
| logging |
| math|
| objectcache |
| oldimage|
| page|
| pagelinks   |
| querycache  |
| recentchanges   |
| revision|
| searchindex |
| site_stats  |
| templatelinks   |
| text|
| trackbacks  |
| transcache  |
| user|
| user_groups |
| user_newtalk|
| validate|
| watchlist   |
+-+
58 rows in set (0.00 sec)

mysql> SELECT * FROM archive LIMIT 1;
ERROR 1146 (42S02): Table 'horsewiki.archive' doesn't exist

mysql> drop table archive;
ERROR 1051 (42S02): Unknown table 'archive'




Dan Buettner wrote:

> Michael, is the 'archive' table present in your database?  e.g., if
> you do a 'LIST TABLES', does it show up?  What happens if you do a
> "SELECT * FROM archive LIMIT 1" ?
>
> I'd hazard a guess that you may have a table definition file with no
> actual table data files, if you're on MyISAM tables.
>
> If you don't need the archive table, can you DROP it successfully?
>
> Dan
>
>
> On 10/16/06, mdpeters <[EMAIL PROTECTED]> wrote:
>
>> I am having a devil of a time moving a database from one server to
>> another. My database is one that is in production to support the
>> mediawiki wiki application. This is a Solaris Sparc 10 system using the
>> mysql-max-5.0.20a-solaris10-sparc version. My database name is
>> horsewiki.
>>
>> I execute this:
>> # mysqldump --user root --password=password horsewiki > horsewiki.sql
>> and get this:
>> mysqldump: Got error: 1146: Table 'horsewiki.archive' doesn't exist when
>> using LOCK TABLES
>>
>> I have tried using phpMyAdmin-2.9.0.2. It seems to let me export the
>> database to an SQL file. When I attempt to import it on the other
>> system, I get this:
>> 
>> Error
>>
>> There seems to be an error in your SQL query. The MySQL server error
>> output below, if there is any, may also help you in diagnosing the
>> problem
>>
>> ERROR: Unknown Punctuation String @ 494
>> STR: />
>> SQL: -- phpMyAdmin SQL Dump
>> -- version 2.9.0.2
>> -- http://www.phpmyadmin.net
>> --
>> -- Host: localhost
>> -- Generation Time: Oct 16, 2006 at 10:00 AM
>> -- Server version: 5.0.20
>> -- PHP Version: 5.1.6
>> --
>> -- Database: `horsewiki`
>> --
>> > "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd";>
>> http://www.w3.org/1999/xhtml"; xml:lang="en" lang="en"
>> dir="ltr">
>> 
>> 
>> > type="image/x-icon" />
>> phpMyAdmin
>> 
>> > 
href="./css/phpmyadmin.css.php?token=7c73a56802fc2e39fe41180d721&js_frame=right"
>>
>> />
>> > href="./css/print.css?token=7c73a56802fc2ee8b4239fe721" media="print" />
>> 

Re: LOCK TABLES

[mdpeters]

As you can see, it is clearly showing up but I cannot do anything with it.

mysql> show tables;
+-+
| Tables_in_horsewiki |
+-+
| archive |
| categorylinks   |
| externallinks   |
| hitcounter  |
| horse_archive   |
| horse_categorylinks |
| horse_externallinks |
| horse_hitcounter|
| horse_image |
| horse_imagelinks|
| horse_interwiki |
| horse_ipblocks  |
| horse_job   |
| horse_logging   |
| horse_math  |
| horse_objectcache   |
| horse_oldimage  |
| horse_page  |
| horse_pagelinks |
| horse_querycache|
| horse_recentchanges |
| horse_revision  |
| horse_searchindex   |
| horse_site_stats|
| horse_templatelinks |
| horse_text  |
| horse_trackbacks|
| horse_transcache|
| horse_user  |
| horse_user_groups   |
| horse_user_newtalk  |
| horse_validate  |
| horse_watchlist |
| image   |
| imagelinks  |
| interwiki   |
| ipblocks|
| job |
| logging |
| math|
| objectcache |
| oldimage|
| page|
| pagelinks   |
| querycache  |
| recentchanges   |
| revision|
| searchindex |
| site_stats  |
| templatelinks   |
| text|
| trackbacks  |
| transcache  |
| user|
| user_groups |
| user_newtalk|
| validate|
| watchlist   |
+-+
58 rows in set (0.00 sec)

mysql> SELECT * FROM archive LIMIT 1;
ERROR 1146 (42S02): Table 'horsewiki.archive' doesn't exist

mysql> drop table archive;
ERROR 1051 (42S02): Unknown table 'archive'




Dan Buettner wrote:


Michael, is the 'archive' table present in your database?  e.g., if
you do a 'LIST TABLES', does it show up?  What happens if you do a
"SELECT * FROM archive LIMIT 1" ?

I'd hazard a guess that you may have a table definition file with no
actual table data files, if you're on MyISAM tables.

If you don't need the archive table, can you DROP it successfully?

Dan


On 10/16/06, mdpeters <[EMAIL PROTECTED]> wrote:


I am having a devil of a time moving a database from one server to
another. My database is one that is in production to support the
mediawiki wiki application. This is a Solaris Sparc 10 system using the
mysql-max-5.0.20a-solaris10-sparc version. My database name is 
horsewiki.


I execute this:
# mysqldump --user root --password=password horsewiki > horsewiki.sql
and get this:
mysqldump: Got error: 1146: Table 'horsewiki.archive' doesn't exist when
using LOCK TABLES

I have tried using phpMyAdmin-2.9.0.2. It seems to let me export the
database to an SQL file. When I attempt to import it on the other
system, I get this:

Error

There seems to be an error in your SQL query. The MySQL server error
output below, if there is any, may also help you in diagnosing the 
problem


ERROR: Unknown Punctuation String @ 494
STR: />
SQL: -- phpMyAdmin SQL Dump
-- version 2.9.0.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Oct 16, 2006 at 10:00 AM
-- Server version: 5.0.20
-- PHP Version: 5.1.6
--
-- Database: `horsewiki`
--
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd";>
http://www.w3.org/1999/xhtml"; xml:lang="en" lang="en"
dir="ltr">


type="image/x-icon" />

phpMyAdmin


href="./css/phpmyadmin.css.php?token=7c73a56802fc2e39fe41180d721&js_frame=right" 


/>

Re: LOCK TABLES

[Dan Buettner]

Michael, is the 'archive' table present in your database?  e.g., if
you do a 'LIST TABLES', does it show up?  What happens if you do a
"SELECT * FROM archive LIMIT 1" ?

I'd hazard a guess that you may have a table definition file with no
actual table data files, if you're on MyISAM tables.

If you don't need the archive table, can you DROP it successfully?

Dan


On 10/16/06, mdpeters <[EMAIL PROTECTED]> wrote:

I am having a devil of a time moving a database from one server to
another. My database is one that is in production to support the
mediawiki wiki application. This is a Solaris Sparc 10 system using the
mysql-max-5.0.20a-solaris10-sparc version. My database name is horsewiki.

I execute this:
# mysqldump --user root --password=password horsewiki > horsewiki.sql
and get this:
mysqldump: Got error: 1146: Table 'horsewiki.archive' doesn't exist when
using LOCK TABLES

I have tried using phpMyAdmin-2.9.0.2. It seems to let me export the
database to an SQL file. When I attempt to import it on the other
system, I get this:

Error

There seems to be an error in your SQL query. The MySQL server error
output below, if there is any, may also help you in diagnosing the problem

ERROR: Unknown Punctuation String @ 494
STR: />
SQL: -- phpMyAdmin SQL Dump
-- version 2.9.0.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Oct 16, 2006 at 10:00 AM
-- Server version: 5.0.20
-- PHP Version: 5.1.6
--
-- Database: `horsewiki`
--
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd";>
http://www.w3.org/1999/xhtml"; xml:lang="en" lang="en"
dir="ltr">



phpMyAdmin

Re: lock tables and sql cache

[Gleb Paharenko]

Hello.



I think it is a weird behavior. I've reported a bug:



  http://bugs.mysql.com/bug.php?id=9511





  



Bob O'Neill <[EMAIL PROTECTED]> wrote:

> If I try to read table 'b' after locking table 'a', I expect to get

> the error message "Table 'b' was not locked with LOCK TABLES". 

> However, if my query that accesses table b is stored in the query

> cache, I don't get the error.  This causes a problem in the following

> scenario:

> 

> User 1:

> 

> LOCK TABLES a

> SELECT SQL_CACHE COUNT(*) FROM b

> (assume it was already cached)

> 

> User 2:

> 

> INSERT b VALUES('value');

> SELECT SQL_CACHE COUNT(*) FROM b

> (the SELECT puts the query back into the cache)

> 

> User 1:

> 

> SELECT SQL_CACHE COUNT(*) FROM b

> (now he gets a different result)

> UNLOCK TABLES

> 

> User 1 thinks that everything he's doing is safe inside of an

> "emulated transaction".  But the data in table b has changed between

> the LOCK and the UNLOCK, and User 1 isn't notified that he is doing

> anything wrong.

> 

> I think an appropriate fix would be to force User 1 to lock table b

> even though the results of that query are stored in the query cache. 

> Is this possible?

> 

> Thanks,

> -Bob

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [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

[Benjamin Arai]

You only need to lock whene you are going to run a query that contains
a series of actions and they all have to happen at the same time.  As
for single queries, they are already atomic, so you don't need to put
and locks around them.


On Mon, 11 Oct 2004 11:14:36 +0100, Melanie Courtot <[EMAIL PROTECTED]> wrote:
> Hi,
> I'm a bit confused by the lock mechanism under mysql.
> When user A does an update on table 1, the table is automatically locked
> by mysql?that means at the same time user B won't be able to modify the
> same row?
> Or do I have to specify the lock for each query?
> And what about temporary tables?
> If anybody has a simple explanation or a link on a doc
> thanks,
> Melanie
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 


-- 
Benjamin Arai
http://www.cs.ucr.edu/~barai
[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 permission and 4.0.15a

[Michael Stassen]

Fernando Monteiro wrote:
Hello, Michael,
Version 4.0.15 comes after version 4.0.2 (15 > 2), so the version is 
not a problem here.

(...)
I asked them to give me the "lock tables" permission, but they 
answered this permission
option isn't available on versions prior to 4.0.2. They also told me 
they would have to
give me a high elevated privilege to issue these commands.

It was a typo, sorry... The right version should be 4.0.20.
Thank you for you explanation about the lock table permission !!
Regards,
Fernando Monteiro
You're welcome.
If you look at the quote I included from the manual, the LOCK TABLES 
privilege has been available since version 4.0.2, so they are wrong if they 
told you 4.0.20.  The next sentence in the manual after the one I quoted is 
"In MySQL 3.23, you must have SELECT, INSERT, DELETE, and UPDATE privileges 
for the tables."  In other words, the LOCK TABLES global privilege was added 
in 4.0.2 to further restrict who could lock tables.  So, again, the version 
of mysql is not a problem -- the ability to lock tables has been in mysql 
for a long time.  In fact, prior to 4.0.2 you would most likely have been 
able to lock tables already, as I imagine you do have SELECT, INSERT, 
DELETE, and UPDATE privileges on your own tables.

They can give you the ability to lock tables in 4.0.15, if they choose to. 
If they are your tables, I'm not sure why they are reluctant to do so.  The 
only reason I can think of would be if they have already given you SELECT 
permission on tables which are not yours, which they don't want you to lock. 
 Now that you are armed with the truth, perhaps you can get them to tell 
you their real reasons...

Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: LOCK TABLES permission and 4.0.15a

[Michael Stassen]

Version 4.0.15 comes after version 4.0.2 (15 > 2), so the version is not a 
problem here.

From the manual: "As of MySQL 4.0.2, to use LOCK TABLES you must have the 
global LOCK TABLES privilege and a SELECT privilege for the involved 
tables." 

The LOCK TABLES privilege may seem elevated as it is global, but you can 
only lock tables for which you have the SELECT privilege.

Michael
Fernando Monteiro wrote:
Hello,
My ISP is using the old  version 4.0.15a and have no early plans to upgrade it.
I'm trying to issue LOCK/UNLOCK TABLES commands at my ISP's MySQL, and I'm getting an
"access denied" error.
I asked them to give me the "lock tables" permission, but they answered this permission
option isn't available on versions prior to 4.0.2. They also told me they would have to
give me a high elevated previledge to issue these commands.
Is it true ? A plain user with full database (but no grant all) access cannot lock a 
table ?
Could someone please point me a solution ??
Thank you very much,
Fernando Monteiro



--
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

[dread]

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]

RE: LOCK TABLES and multi table UPDATE

[Michael McTernan]

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 and multi table UPDATE

[Jochem van Dieten]

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 in myisam

[daniel]

> 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]

Re: Lock tables in myisam

[Jeremy Zawodny]

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

[daniel]

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

[daniel]

>
> 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

[Matt W]

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

[Paul DuBois]

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

[Dathan Vance Pattishall]

 
 
- 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

[electroteque]

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

[Jeremy Zawodny]

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 - Manual Ambiguity

[Egor Egorov]

"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 - Manual Ambiguity

[Primaria Falticeni]

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

[Egor Egorov]

"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 ?

[Heikki Tuuri]

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]

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 lik

Re: LOCK TABLES error , on a select without any update ?

[Heikki Tuuri]

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-m

Re: LOCK TABLES error , on a select without any update ?

[Steff]

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

Re: LOCK TABLES error , on a select without any update ?

[Heikki Tuuri]

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 Query

[gerald_clark]

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

Re: Lock Tables Timeout value?

[Paul DuBois]

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

Re: Lock Tables Timeout value?

[Clyde]

*** 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?

[Mark]

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 Bug

[Gerald Clark]

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

Re: LOCK TABLES issue

[Bob Sidebotham]

Thanks, Rama,

I'm using MyISAM, in part because I want to use full text indexing, when
binary search mode stabilizes in 4.0.1. I don't believe that full text
indexing is supported by innodb. I'm also a little reluctant to go to
innodb because of the added complexity of the system.

Bob

"Ramaraju.R.V" wrote:
> 
> 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)

RE: LOCK TABLES issue

[Ramaraju.R.V]

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 u

Re: LOCK TABLES issue

[Bob Sidebotham]

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