Re: Lock Tables Question
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
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. BEGIN 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. END 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
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
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
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 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 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.
RE: Lock Tables Question
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
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
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
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
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: 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`
--
!DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd;
html xmlns=http://www.w3.org/1999/xhtml; xml:lang=en lang=en
dir=ltr
head
link rel=icon href=./favicon.ico type=image/x-icon /
link rel=shortcut icon href=./favicon.ico type=image/x-icon /
titlephpMyAdmin/title
meta http-equiv=Content-Type content=text/html; charset=utf-8 /
link rel=stylesheet type=text/css
href=./css/phpmyadmin.css.php?token=7c73a56802fc2e39fe41180d721amp;js_frame=right
/
link rel=stylesheet type=text/css
href=./css/print.css?token=7c73a56802fc2ee8b4239fe721 media=print /
script type=text/javascript language=javascript
// ![CDATA[
// Updates the title of the frameset if possible (ns4 does not allow
this)
if (typeof(parent.document) != 'undefined'
typeof(parent.document) != 'unknown'
typeof(parent.document.title) == 'string') {
parent.document.title = 'www.lazarusalliance.com / localhost /
horsewiki / archive | phpMyAdmin 2.9.0.2';
SQL query:
-- 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` --
I have not found a good source to understand what the problem might be.
It does not help that I am such a greenhorn with databases either. I
would appreciate any assistance.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOCK TABLES
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` -- !DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd; html xmlns=http://www.w3.org/1999/xhtml; xml:lang=en lang=en dir=ltr head link rel=icon href=./favicon.ico type=image/x-icon / link rel=shortcut icon href=./favicon.ico type=image/x-icon / titlephpMyAdmin/title meta http-equiv=Content-Type content=text/html; charset=utf-8 / link rel=stylesheet type=text/css href=./css/phpmyadmin.css.php?token=7c73a56802fc2e39fe41180d721amp;js_frame=right /
Re: LOCK TABLES
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]
mailto:[EMAIL PROTECTED]
To: mysql@lists.mysql.com 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`
--
!DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd;
html xmlns=http://www.w3.org/1999/xhtml; xml:lang=en lang=en
dir=ltr
head
link rel=icon href=./favicon.ico type=image/x-icon /
link rel=shortcut icon href=./favicon.ico type=image/x-icon /
titlephpMyAdmin/title
meta http-equiv=Content-Type content=text/html; charset=utf-8 /
link rel=stylesheet type=text/css
href=./css/phpmyadmin.css.php?token=7c73a56802fc2e39fe41180d721amp;js_frame=right
/
link rel=stylesheet type=text/css
href=./css/print.css?token=7c73a56802fc2ee8b4239fe721 media=print /
script type=text/javascript language=javascript
// ![CDATA[
// Updates the title of the frameset if possible (ns4 does not allow
this)
if (typeof(parent.document) != 'undefined'
typeof(parent.document) != 'unknown'
typeof(parent.document.title) == 'string') {
parent.document.title = 'www.lazarusalliance.com / localhost /
horsewiki / archive | phpMyAdmin 2.9.0.2';
SQL query:
-- 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` --
I have not found a good source to understand what the problem might be.
It does not help that I am such a greenhorn with databases either. I
would appreciate any assistance.
Michael
--
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
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` -- !DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd; html xmlns=http://www.w3.org/1999/xhtml; xml:lang=en lang=en dir=ltr head link rel=icon href=./favicon.ico type=image/x-icon / link rel=shortcut icon href=./favicon.ico type=image/x-icon / titlephpMyAdmin/title meta http-equiv=Content-Type content=text/html; charset=utf-8
Re: LOCK TABLES
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` -- !DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd; html xmlns=http://www.w3.org/1999/xhtml; xml:lang=en lang=en dir=ltr head link rel=icon href=./favicon.ico type=image/x-icon
Re: LOCK TABLES
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`
--
!DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd;
html xmlns=http://www.w3.org/1999/xhtml; xml:lang=en lang=en
dir=ltr
head
link rel=icon href=./favicon.ico type=image/x-icon /
link rel=shortcut icon href=./favicon.ico type=image/x-icon /
titlephpMyAdmin/title
meta http-equiv=Content-Type content=text/html; charset=utf-8 /
link rel=stylesheet type=text/css
href=./css/phpmyadmin.css.php?token=7c73a56802fc2e39fe41180d721amp;js_frame=right
/
link rel=stylesheet type=text/css
href=./css/print.css?token=7c73a56802fc2ee8b4239fe721 media=print /
script type=text/javascript language=javascript
// ![CDATA[
// Updates the title of the frameset if possible (ns4 does not allow
this)
if (typeof(parent.document) != 'undefined'
typeof(parent.document) != 'unknown'
typeof(parent.document.title) == 'string') {
parent.document.title = 'www.lazarusalliance.com / localhost /
horsewiki / archive | phpMyAdmin 2.9.0.2';
SQL query:
-- 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` --
I have not found a good source to understand what the problem might be.
It does not help that I am such a greenhorn with databases either. I
would appreciate any assistance.
Michael
--
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
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`
--
!DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd;
html xmlns=http://www.w3.org/1999/xhtml; xml:lang=en lang=en
dir=ltr
head
link rel=icon href=./favicon.ico type=image/x-icon /
link rel=shortcut icon href=./favicon.ico
type=image/x-icon /
titlephpMyAdmin/title
meta http-equiv=Content-Type content=text/html;
charset=utf-8 /
link rel=stylesheet type=text/css
href=./css/phpmyadmin.css.php?token=7c73a56802fc2e39fe41180d721amp;js_frame=right
/
link rel=stylesheet type=text/css
href=./css/print.css?token=7c73a56802fc2ee8b4239fe721 media=print /
script type=text/javascript language=javascript
// ![CDATA[
// Updates the title of the frameset if possible (ns4 does not allow
this)
if (typeof(parent.document) != 'undefined'
typeof(parent.document) != 'unknown'
typeof(parent.document.title) == 'string') {
parent.document.title = 'www.lazarusalliance.com / localhost /
horsewiki / archive | phpMyAdmin 2.9.0.2';
SQL query:
-- 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` --
I have not found a good source to understand what the problem might be.
It does not help that I am such a greenhorn with databases either. I
would appreciate any assistance.
Michael
--
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:
Re: LOCK TABLES
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`
--
!DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd;
html xmlns=http://www.w3.org/1999/xhtml; xml:lang=en lang=en
dir=ltr
head
link rel=icon href=./favicon.ico type=image/x-icon /
link rel=shortcut icon href=./favicon.ico
type=image/x-icon /
titlephpMyAdmin/title
meta http-equiv=Content-Type content=text/html;
charset=utf-8 /
link rel=stylesheet type=text/css
href=./css/phpmyadmin.css.php?token=7c73a56802fc2e39fe41180d721amp;js_frame=right
/
link rel=stylesheet type=text/css
href=./css/print.css?token=7c73a56802fc2ee8b4239fe721 media=print /
script type=text/javascript language=javascript
// ![CDATA[
// Updates the title of the frameset if possible (ns4 does not allow
this)
if (typeof(parent.document) != 'undefined'
typeof(parent.document) != 'unknown'
typeof(parent.document.title) == 'string') {
parent.document.title = 'www.lazarusalliance.com / localhost /
horsewiki / archive | phpMyAdmin 2.9.0.2';
SQL query:
-- 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` --
I have not found a good source
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`
--
!DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd;
html xmlns=http://www.w3.org/1999/xhtml; xml:lang=en lang=en
dir=ltr
head
link rel=icon href=./favicon.ico type=image/x-icon /
link rel=shortcut icon href=./favicon.ico
type=image/x-icon /
titlephpMyAdmin/title
meta http-equiv=Content-Type content=text/html;
charset=utf-8 /
link rel=stylesheet type=text/css
href=./css/phpmyadmin.css.php?token=7c73a56802fc2e39fe41180d721amp;js_frame=right
/
link rel=stylesheet type=text/css
href=./css/print.css?token=7c73a56802fc2ee8b4239fe721
media=print /
script type=text/javascript language=javascript
// ![CDATA[
// Updates the title of the frameset if possible (ns4 does not
allow
this)
if (typeof(parent.document) != 'undefined'
typeof(parent.document) != 'unknown'
typeof(parent.document.title) == 'string') {
parent.document.title = 'www.lazarusalliance.com /
localhost /
horsewiki / archive | phpMyAdmin 2.9.0.2';
SQL
Re: Re: LOCK TABLES
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` -- !DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd; html xmlns=http://www.w3.org/1999/xhtml; xml:lang=en lang=en dir=ltr head link rel=icon href=./favicon.ico type=image/x-icon / link rel=shortcut icon href=./favicon.ico type=image/x-icon / titlephpMyAdmin/title meta http-equiv=Content-Type content=text/html; charset=utf-8 / link rel=stylesheet type=text/css href=./css/phpmyadmin.css.php?token=7c73a56802fc2e39fe41180d721amp;js_frame=right / link rel=stylesheet type=text/css href=./css/print.css?token=7c73a56802fc2ee8b4239fe721 media=print
Re: lock tables and sql cache
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
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
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. http://dev.mysql.com/doc/mysql/en/LOCK_TABLES.html 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 permission and 4.0.15a
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 and multi table UPDATE
On 22-Jan-2004 Michael McTernan wrote: Hi there, Thanks for your quick response! Why all the locks, temp tables and updates? You can just do: SELECT CASE WHEN B.y IS NULL THEN A.x ELSE B.y END AS newX FROM A LEFT JOIN B ON A.x = B.x Spot on - many thanks! I wasn't aware of the CASE function, but I've certainly learnt something now :) Also look at SELECT COALESCE(B.y, A.x) ... Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOCK TABLES and multi table UPDATE
Michael McTernan said: I'm using MySQL 4.0.15-standard-log, on RedHat 9.0 with InnoDb tables. Essentially I have two tables, one table is a list of integers, while the second is a table of integer pairs. e.g. +---+ +-+-+ | A | | B.x | B.y | +---+ +-+-+ | 1 | | 1 | 5 | | 2 | | 3 | 6 | | 3 | +-+-+ | 4 | +---+ What I want to do is form a temporary table containing A, but then with each value in A that matches B.x substituted for B.y. e.g. the desired result from the example would be a new table: +---+ | T | +---+ | 5 | | 2 | | 6 | | 4 | +---+ Why all the locks, temp tables and updates? You can just do: SELECT CASE WHEN B.y IS NULL THEN A.x ELSE B.y END AS newX FROM A LEFT JOIN B ON A.x = B.x Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: LOCK TABLES and multi table UPDATE
Hi there, Thanks for your quick response! Why all the locks, temp tables and updates? You can just do: SELECT CASE WHEN B.y IS NULL THEN A.x ELSE B.y END AS newX FROM A LEFT JOIN B ON A.x = B.x Spot on - many thanks! I wasn't aware of the CASE function, but I've certainly learnt something now :) Thanks, Mike -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: 22 January 2004 16:38 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: LOCK TABLES and multi table UPDATE Michael McTernan said: I'm using MySQL 4.0.15-standard-log, on RedHat 9.0 with InnoDb tables. Essentially I have two tables, one table is a list of integers, while the second is a table of integer pairs. e.g. +---+ +-+-+ | A | | B.x | B.y | +---+ +-+-+ | 1 | | 1 | 5 | | 2 | | 3 | 6 | | 3 | +-+-+ | 4 | +---+ What I want to do is form a temporary table containing A, but then with each value in A that matches B.x substituted for B.y. e.g. the desired result from the example would be a new table: +---+ | T | +---+ | 5 | | 2 | | 6 | | 4 | +---+ Why all the locks, temp tables and updates? You can just do: SELECT CASE WHEN B.y IS NULL THEN A.x ELSE B.y END AS newX FROM A LEFT JOIN B ON A.x = B.x Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lock tables in myisam
On Thu, Sep 18, 2003 at 03:00:17PM +1000, [EMAIL PROTECTED] wrote: Hi there i have a couple of projects which required fulltext searching so was unable to setup innodb on these. I was wondering if lock tables is a secure way to make the transaction on these tables and does this prevent being read upon aswell? Obtaining a WRITE lock on a MyISAM table prevents readers, yes. But you do have to put the necessary smarts into your code to properly implement a ROLLBACK if you need one. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 4 days, processed 188,913,819 queries (445/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lock tables in myisam
rollback works on myisam ? this is mysql4 anyway, sweet i'll give it a try. On Fri, 2003-09-19 at 08:01, Jeremy Zawodny wrote: On Thu, Sep 18, 2003 at 03:00:17PM +1000, [EMAIL PROTECTED] wrote: Hi there i have a couple of projects which required fulltext searching so was unable to setup innodb on these. I was wondering if lock tables is a secure way to make the transaction on these tables and does this prevent being read upon aswell? Obtaining a WRITE lock on a MyISAM table prevents readers, yes. But you do have to put the necessary smarts into your code to properly implement a ROLLBACK if you need one. Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Lock tables in myisam
- Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: electroteque [mailto:[EMAIL PROTECTED] --Sent: Wednesday, September 17, 2003 6:38 PM --To: [EMAIL PROTECTED] --Subject: Re: Lock tables in myisam -- --rollback works on myisam ? this is mysql4 anyway, sweet i'll give it a --try. No rollback does not work on myisam Jeremy was stating that you don't have to do what you suggested to implement a correct ROLLBACK in mySQL. Use INNODB. http://www.mysql.com/doc/en/COMMIT.html
Re: Lock tables in myisam
At 11:38 AM +1000 9/18/03, electroteque wrote: rollback works on myisam ? this is mysql4 anyway, sweet i'll give it a try. Rollback *doesn't* work with MyISAM, that's why Jeremy said you have to put the necessary logic in your application if you want to achieve the same effect. At least, that's what I think he said. :-) On Fri, 2003-09-19 at 08:01, Jeremy Zawodny wrote: On Thu, Sep 18, 2003 at 03:00:17PM +1000, [EMAIL PROTECTED] wrote: Hi there i have a couple of projects which required fulltext searching so was unable to setup innodb on these. I was wondering if lock tables is a secure way to make the transaction on these tables and does this prevent being read upon aswell? Obtaining a WRITE lock on a MyISAM table prevents readers, yes. But you do have to put the necessary smarts into your code to properly implement a ROLLBACK if you need one. Jeremy -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lock tables in myisam
Hi, No, ROLLBACK doesn't work with MyISAM. Jeremy meant that you can add logic/code between LOCK/UNLOCK to simulate ROLLBACK. e.g. queries that undo what you did if something goes wrong. Of course this won't cover you if mysqld dies, is killed, or you lose the connection etc. as real transactions would. Matt - Original Message - From: electroteque Sent: Wednesday, September 17, 2003 8:38 PM Subject: Re: Lock tables in myisam rollback works on myisam ? this is mysql4 anyway, sweet i'll give it a try. On Fri, 2003-09-19 at 08:01, Jeremy Zawodny wrote: On Thu, Sep 18, 2003 at 03:00:17PM +1000, [EMAIL PROTECTED] wrote: Hi there i have a couple of projects which required fulltext searching so was unable to setup innodb on these. I was wondering if lock tables is a secure way to make the transaction on these tables and does this prevent being read upon aswell? Obtaining a WRITE lock on a MyISAM table prevents readers, yes. But you do have to put the necessary smarts into your code to properly implement a ROLLBACK if you need one. Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Lock tables in myisam
No rollback does not work on myisam Jeremy was stating that you don't have to do what you suggested to implement a correct ROLLBACK in mySQL. Use INNODB. http://www.mysql.com/doc/en/COMMIT.html Hmm if you got my other post i am trying to simulate innodb in myisiam for projects that require fulltext search i have no choice. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lock tables in myisam
Righty, so if error unlock table hehe, i have found i need to produce my error first then do a rollback for an error to display in php as it wouldnt show a mysql error after a rollback, i guess i could add an unlock table in my trigger error function too. At 11:38 AM +1000 9/18/03, electroteque wrote: rollback works on myisam ? this is mysql4 anyway, sweet i'll give it a try. Rollback *doesn't* work with MyISAM, that's why Jeremy said you have to put the necessary logic in your application if you want to achieve the same effect. At least, that's what I think he said. :-) On Fri, 2003-09-19 at 08:01, Jeremy Zawodny wrote: On Thu, Sep 18, 2003 at 03:00:17PM +1000, [EMAIL PROTECTED] wrote: Hi there i have a couple of projects which required fulltext searching so was unable to setup innodb on these. I was wondering if lock tables is a secure way to make the transaction on these tables and does this prevent being read upon aswell? Obtaining a WRITE lock on a MyISAM table prevents readers, yes. But you do have to put the necessary smarts into your code to properly implement a ROLLBACK if you need one. Jeremy -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lock tables in myisam
On Thu, Sep 18, 2003 at 11:38:17AM +1000, electroteque wrote: rollback works on myisam ? this is mysql4 anyway, sweet i'll give it a try. No. That's what I meant about having to put extra smarts in your code. It needs to be able to undo its actions. On Fri, 2003-09-19 at 08:01, Jeremy Zawodny wrote: On Thu, Sep 18, 2003 at 03:00:17PM +1000, [EMAIL PROTECTED] wrote: Hi there i have a couple of projects which required fulltext searching so was unable to setup innodb on these. I was wondering if lock tables is a secure way to make the transaction on these tables and does this prevent being read upon aswell? Obtaining a WRITE lock on a MyISAM table prevents readers, yes. But you do have to put the necessary smarts into your code to properly implement a ROLLBACK if you need one. Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 4 days, processed 189,881,535 queries (440/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lock tables in myisam
Hi, No, ROLLBACK doesn't work with MyISAM. Jeremy meant that you can add logic/code between LOCK/UNLOCK to simulate ROLLBACK. e.g. queries that undo what you did if something goes wrong. Of course this won't cover you if mysqld dies, is killed, or you lose the connection etc. as real transactions would. Will it be worth my while then to do lock tables, on one of the projects we have about 6 people entering data pretty much at the same time people are searching/reading. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lock Tables - Manual Ambiguity
K.L. [EMAIL PROTECTED] wrote: The 3.23.54 Manual states in; Section 1.4.4.3 Transactions If you only obtain a read lock (as opposed to a write lock), then reads and inserts are still allowed to happen. The new inserted records will not be seen by any of the clients that have a READ lock until they release their read locks But in Section 6.7.2 Lock Tables/Unlock Tables Syntax If a thread obtains a READ lock on a table, that thread (and all other threads) can only read from the table. If a thread obtains a WRITE lock on a table, then only the thread holding the lock can READ from or WRITE to the table. What am I missing pls? Can I, with a READ Lock, still write to the table? Only thread that obtains lock on the table can write to the table. Is the following assumption correct? READ Lock - Thread/Client with Lock, can write to locked Table, and is ONLY ONE who can until Lock released. Sure. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lock Tables - Manual Ambiguity
So, when client X has Read lock, the client Y cannot have Write lock, Egor? Iulian - Original Message - From: Egor Egorov [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, June 18, 2003 1:33 PM Subject: Re: Lock Tables - Manual Ambiguity K.L. [EMAIL PROTECTED] wrote: The 3.23.54 Manual states in; Section 1.4.4.3 Transactions If you only obtain a read lock (as opposed to a write lock), then reads and inserts are still allowed to happen. The new inserted records will not be seen by any of the clients that have a READ lock until they release their read locks But in Section 6.7.2 Lock Tables/Unlock Tables Syntax If a thread obtains a READ lock on a table, that thread (and all other threads) can only read from the table. If a thread obtains a WRITE lock on a table, then only the thread holding the lock can READ from or WRITE to the table. What am I missing pls? Can I, with a READ Lock, still write to the table? Only thread that obtains lock on the table can write to the table. Is the following assumption correct? READ Lock - Thread/Client with Lock, can write to locked Table, and is ONLY ONE who can until Lock released. Sure. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lock Tables - Manual Ambiguity
Primaria Falticeni [EMAIL PROTECTED] wrote: So, when client X has Read lock, the client Y cannot have Write lock, Egor? Yes, if client X obtaines read lock on the table client Y can't have write lock on this table until client X releases lock. Iulian - Original Message - From: Egor Egorov [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, June 18, 2003 1:33 PM Subject: Re: Lock Tables - Manual Ambiguity K.L. [EMAIL PROTECTED] wrote: The 3.23.54 Manual states in; Section 1.4.4.3 Transactions If you only obtain a read lock (as opposed to a write lock), then reads and inserts are still allowed to happen. The new inserted records will not be seen by any of the clients that have a READ lock until they release their read locks But in Section 6.7.2 Lock Tables/Unlock Tables Syntax If a thread obtains a READ lock on a table, that thread (and all other threads) can only read from the table. If a thread obtains a WRITE lock on a table, then only the thread holding the lock can READ from or WRITE to the table. What am I missing pls? Can I, with a READ Lock, still write to the table? Only thread that obtains lock on the table can write to the table. Is the following assumption correct? READ Lock - Thread/Client with Lock, can write to locked Table, and is ONLY ONE who can until Lock released. Sure. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOCK TABLES error , on a select without any update ?
Steff, I am carbon copying this to [EMAIL PROTECTED] so that people see the problem was probably found. - Original Message - From: [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, February 23, 2003 5:33 AM Subject: Re: LOCK TABLES error , on a select without any update ? Heikki, Please do NOT do any more research into this problem for the time being. I was finally able to capture a log when the problem occurred in production. In studying the log I discovered two things. 1) The new code to do away with locks has not made it into production. I mistakenly thought it was part of a release this past Thursday, it turns out it is scheduled to go into production this coming Thursday. 2) The log clearly shows where we are doing a lock/unlock on the same connection which we had reserved for exclusive use by the transaction. We need to do some more digging, but my initial guess is that under heavy load our connection manager was occasionally handing out a connection to more then one user. ... Once again much thanks. Bye Steff Best regards, Heikki Innobase Oy sql query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: LOCK TABLES error , on a select without any update ?
Steff,
- Original Message -
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Cc: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED];
[EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Saturday, February 22, 2003 1:26 AM
Subject: Re: LOCK TABLES error , on a select without any update ?
Hi Heikki,
Thanks for picking up on this again. After the help from
you and Mark last week, we removed ALL instances of the
lock tables from our application. We used the idea Mark
provided for getting our next sequence number without using
any locks. In the past this was the only thing we where using
table locks for.
ok, good. Actually, with InnoDB you normally do not need table locks at all.
SELECT ... FOR UPDATE is what should be used to lock rows.
Then this definitely is a bug somewhere in your application/MySQL/JDBC
driver/Java/operating system/hardware. Some possibilities:
1. Your application calls MySQL client functions in a wrong way and causes
some confusion.
2. A bug in the JDBC driver of MySQL.
3. A bug in Java.
4. A bug in MySQL.
5. A bug in Windows 2000 (Service Pack 2).
6. A fault in the hardware.
You are the only one who has reported this bug. The error looks like it
would happen inside MySQL, which means it is probably a bug in MySQL or in
your OS/hardware. You could try installing the latest service packs of Win
2000 and VC++ 6.0. Also double check that your application really does not
use LOCK TABLES anywhere.
One important question: if you continue using the connection which threw the
error, does it throw other errors?
In this application we are not even doing anything with
begin/end transaction, we are totally dependent on each SQL
statement standing on its own. We do use a connection pool
and have modified our code to always set the autocommitt
level = 1 before handing any connections out of the pool.
Do you think we are missing some basic setting in our
MySql.ini file ?
No.
Again the thing which makes this so hard to debug is it is
intermittent and only in production, and not always on the
same table or SQL statement. :(
Do you know of any log files we can run which will only
log errors or exceptions?
Sorry, there is no logging of SQL errors only.
This might limit the volume of the
logs to something manageable and still let us see what
connection has the error.
Alternatively do you know what state the MySql
connection thinks it is in, in order for it to want to through this
kind of error? Can we test for the state from within our code ?
Thanks again for your patience and assistance. I really want to
be able to make MySql work for this application.
Hmm... I am forwarding this bug report to Monty. He can look from the code
what could possibly cause a wrong error:
java.sql.SQLException: General error: Table
'productsprovided' was not
locked with LOCK TABLES.
to be thrown. A quick look in the source code shows that in
/sql/sql_base.cpp, on about line 771 there is a test:
if (thd-locked_tables) { ... }
If a garbage value has come to thd-locked_tables, you will get the above
error. Memory corruption caused by MySQL or hardware could explain this
error. But then mysqld would probably crash, which it does not?
If you compile MySQL yourself from source, you can add a printf to
sql_base.cpp to determine if thd-locked_tables becomes non-NULL sometimes.
Thanks
Steff
You are welcome,
Heikki
Innobase Oy
sql query
Steff, We have our connection set to Autocommitt=1, and
No table locks are ever explicitly being done on this table
anyplace in any of our code modules. in MySQL you have to
do LOCK TABLES on EVERY table you use inside a LOCK
TABLES. You cannot lock just some table and use others
unlocked. I repeat that the error could be caused if your
application has a bug and uses the same connection to do the
SELECT as it has used to LOCK some other table. Are you
absolutely sure you do not mix connections in your
application? Did you have the general query log running at the
time of the error? If yes, can you check from it what was the
number of the connection that issued the query resulting in an
error, and did that same connection earlier issue a LOCK
TABLES? Regards, Heikki Innobase Oy sql query
--
Steff McGonagle
Envisage Information Systems, Inc.
Phone (315) 497-9202 x16
www.EnvisageSystems.com
www.Enveritus.com
...
Our MySql environment is as follows:
OS Platform: Windows 2000 Service Pack 2
Machine description:
Compiler : VC++ 6.0
Architecture : i686
Total Memory : 2097151 KB RAM
Server Info3.23.54-max-nt-log
have_innodbYES
innodb_additional_mem_pool_size104857600
innodb_buffer_pool_size1048576000
innodb_data_file_path ibdata1
innodb_data_home_dir
innodb_file_io_threads 4
innodb_force_recovery 0
innodb_thread_concurrency 8
Re: LOCK TABLES error , on a select without any update ?
Heikki, Thanks for the response. Just to be sure that we did not have a piece of code which we had forgotten about, I did a search of the Java classes involved in this application for the word LOCK. I did not find any instance where we are locking tables. If we want to update a set of rows, we are just doing a regular update, we are NOT using any of the Select... FOR UPDATE syntax. I will check to make sure we are running the latest OS service pack. We are not set-up to know if a connection continues to throw an error after the first instance of this lock error. However I doubt that it continues too, given that we use a pool of connections and this error happens infrequently. For example we have had 275 logins so far today, and we have had 17 instances of this error (6 on one web server, 11 on the other webserver (both use the same database server)). This mornings errors all happened within a transaction block (Autocommitt=0). The Java code requested a rollback after detecting the error. The failures on Friday where in a different section of code which is NOT in a transaction block (Autocommitt=1). In both cases the symptoms where the same, a report of a file being accessed without LOCK TABLES. I do not see any pattern with regards to the timing of the errors, though there are cases where both web servers are showing Table NOT LOCKED' errors at the same times. Of the 17 errors this morning, 4 different table names show up in the errors, though it is the same code (different data) being run every time. Given the simplicity of our SQL statements and the overall simplicity of our database schema, I find it really hard to believe that we are coming across a bug in MySql. The only thing we do within this program is look for records, delete records, and update records. No big multi table joins, no special creation / deletion of tables. We just are not that sophisticated. :) I am wondering of the error is really something else, such as a timeout, which is coming back with the wrong error message. The MySql instance is not reporting any errors or exceptions, and the computer has plenty of memory and has not shown any stability problems. CPU utilization on the Database server seems to run between 20 and 50%. The machines hosting the servlets seem to be running between 40% and 90% utilization. At the times the errors occurred today I would have to guess everything would have been running under 50% utilization given that the number of logins per hour was less then 10% of what we see on normal business days. Since the server loads are relatively light on the weekends, I might be able to turn on some logs without impacting performance to much. What logs do you think I should turn on? I am going to send the MySql report to you under a different cover. Perhaps their is something in it which will catch your eye. Thanks Steff On 22 Feb 2003 at 10:52, Heikki Tuuri wrote: From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED], [EMAIL PROTECTED] Copies to: [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED] Subject:Re: LOCK TABLES error , on a select without any update ? Date sent: Sat, 22 Feb 2003 10:52:08 +0200 Steff, - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Saturday, February 22, 2003 1:26 AM Subject: Re: LOCK TABLES error , on a select without any update ? Hi Heikki, Thanks for picking up on this again. After the help from you and Mark last week, we removed ALL instances of the lock tables from our application. We used the idea Mark provided for getting our next sequence number without using any locks. In the past this was the only thing we where using table locks for. ok, good. Actually, with InnoDB you normally do not need table locks at all. SELECT ... FOR UPDATE is what should be used to lock rows. Then this definitely is a bug somewhere in your application/MySQL/JDBC driver/Java/operating system/hardware. Some possibilities: 1. Your application calls MySQL client functions in a wrong way and causes some confusion. 2. A bug in the JDBC driver of MySQL. 3. A bug in Java. 4. A bug in MySQL. 5. A bug in Windows 2000 (Service Pack 2). 6. A fault in the hardware. You are the only one who has reported this bug. The error looks like it would happen inside MySQL, which means it is probably a bug in MySQL or in your OS/hardware. You could try installing the latest service packs of Win 2000 and VC++ 6.0. Also double check that your application really does not use LOCK TABLES anywhere. One important question: if you continue using the connection which threw
Re: LOCK TABLES error , on a select without any update ?
Steff, We have our connection set to Autocommitt=1, and No table locks are ever explicitly being done on this table anyplace in any of our code modules. in MySQL you have to do LOCK TABLES on EVERY table you use inside a LOCK TABLES. You cannot lock just some table and use others unlocked. I repeat that the error could be caused if your application has a bug and uses the same connection to do the SELECT as it has used to LOCK some other table. Are you absolutely sure you do not mix connections in your application? Did you have the general query log running at the time of the error? If yes, can you check from it what was the number of the connection that issued the query resulting in an error, and did that same connection earlier issue a LOCK TABLES? Regards, Heikki Innobase Oy sql query .. Subject: LOCK TABLES error , on a select without any update ? From: Steff.envisage1.com Date: Thu, 20 Feb 2003 17:25:43 -0500 Hi All, Once again we have run into a situation where our production database is throwing an error regarding the use of table locks for no apparent reason. We are running MySql on windows with InnoDb. We have a section of code which is working fine most of the time but occasionally will through an error complaining that a table is not locked. Based on the Java stack dump, the SQL statement which caused this error to appear was Select TransactionTypeID From transactionheader Where TransactionHeaderID =1234 The error reported was : java.sql.SQLException: General error: Table 'transactionheader' was not locked with LOCK TABLES We have our connection set to Autocommitt=1, and No table locks are ever explicitly being done on this table anyplace in any of our code modules. Will someone please help me understand why a select statement without an update would ever cause a tables not Locked error ? Thanks Steff -- Steff McGonagle Envisage Information Systems, Inc. Phone (315) 497-9202 x16 www.EnvisageSystems.com www.Enveritus.com ... Our SQL in this application follows the following pattern. (the following is an section from the MysQL log with just one instance of the application running): 030125 20:24:29 7 Query SET autocommit=0 7 Query BEGIN 7 Query Select * from accountsprovided Where ExternalID='I06'AND FinServProvID = 'C33' 7 Query UPDATE accountsprovided SET Enabled='Y' WHERE AccountID = 'CKCBSBF2994309' 7 Query Select * from account Where AccountID = 'CKCBSBF2994309' 7 Query UPDATE account SET PreLimit=1.00 WHERE AccountID = 'CKCBSBF2994309' 12 Query Lock Table Control Write 12 Query Select * From Control 12 Query Update Control set NextID = 6999244 12 Query Unlock Tables 7 Query INSERT INTO productsowned ( VestedValue, Quantity ) VALUES ( 7293.90, 7293.9) ... About another 40 lines of SQL following this same general pattern but using different tables. 7 Query Update cachestatus Set UpdatedOn = null, UpdatedBy = 'XMLWarehouseLoader' Where PrimaryID = 'CKCBSBF2994310' AND SecondaryID = 'CKCBSBF2994311' 7 Query COMMIT 7 Query SET autocommit=1 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: LOCK TABLES error , on a select without any update ?
Hi Heikki, Thanks for picking up on this again. After the help from you and Mark last week, we removed ALL instances of the lock tables from our application. We used the idea Mark provided for getting our next sequence number without using any locks. In the past this was the only thing we where using table locks for. In this application we are not even doing anything with begin/end transaction, we are totally dependent on each SQL statement standing on its own. We do use a connection pool and have modified our code to always set the autocommitt level = 1 before handing any connections out of the pool. Do you think we are missing some basic setting in our MySql.ini file ? Again the thing which makes this so hard to debug is it is intermittent and only in production, and not always on the same table or SQL statement. :( Do you know of any log files we can run which will only log errors or exceptions? This might limit the volume of the logs to something manageable and still let us see what connection has the error. Alternatively do you know what state the MySql connection thinks it is in, in order for it to want to through this kind of error? Can we test for the state from within our code ? Thanks again for your patience and assistance. I really want to be able to make MySql work for this application. Thanks Steff Steff, We have our connection set to Autocommitt=1, and No table locks are ever explicitly being done on this table anyplace in any of our code modules. in MySQL you have to do LOCK TABLES on EVERY table you use inside a LOCK TABLES. You cannot lock just some table and use others unlocked. I repeat that the error could be caused if your application has a bug and uses the same connection to do the SELECT as it has used to LOCK some other table. Are you absolutely sure you do not mix connections in your application? Did you have the general query log running at the time of the error? If yes, can you check from it what was the number of the connection that issued the query resulting in an error, and did that same connection earlier issue a LOCK TABLES? Regards, Heikki Innobase Oy sql query -- Steff McGonagle Envisage Information Systems, Inc. Phone (315) 497-9202 x16 www.EnvisageSystems.com www.Enveritus.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Lock Tables Query
I would investigate ways of writing that long running job so that it does not require locking a table for long periods of time. Use relative updates, break the procedure down into smaller lockable steps with pauses for other programs etc. Clyde wrote: Hi, As per a previous thread I have found that when you use lock Tables MySql will wait indefinitely for the lock - No timeout or error message. Therefore let me explain my question. Scenario: User 1 locks files for a long running job. (write lock that prevents any access to the files) User 2 logs on, then try's to lock or use these files but can't because user 1 already has the lock. (even a simple select * from xxx will wait forever) The program for user 2 will just appear to hang. How do I give feed back to user 2 to say something like: Unable to lock/use files after waiting 30 seconds, try again later or whatever. IE I would like the program to be able to return control after a specific amount of time if the lock is not satisfied in order to give feedback to the user, rather than the the program just wait and appearing to hang. Better still if there is a system variable or something I can check first to see if the file is locked - but I can't seem to find this in the docs. Most other databases I have used have a timeout value (like the record lock for innodb) so I am having trouble dealing with this scenario. Any Ideas. Thanks Clyde England - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Lock Tables Timeout value?
Gee, I hope there is no such thing as a timeout value for Lock Tables. :) If two of my programs decide that one of them needs to wait for the other, however long that may take, then I hope MySQL honors that chosen symbiosis. I hope it behaves like a Perl flock(): it just waits, and waits, and waits -- and that is how I want it. :) - Mark System Administrator Asarian-host.org --- If you were supposed to understand it, we wouldn't call it code. - FedEx - Original Message - From: Clyde [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, January 11, 2003 10:12 AM Subject: Lock Tables Timeout value? Hi, I have searched the docs but can't seem to find information on the time out value for Lock Tables (probably just me) When using Lock Tables how long does MySql wait before giving up if it can't get a lock? Is there any way of changing this time out value? Thanks Clyde England - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Lock Tables Timeout value?
*** REPLY SEPARATOR *** On 11/01/2003 at 1:29 PM Mark wrote: Gee, I hope there is no such thing as a timeout value for Lock Tables. :) If two of my programs decide that one of them needs to wait for the other, however long that may take, then I hope MySQL honors that chosen symbiosis. I hope it behaves like a Perl flock(): it just waits, and waits, and waits -- and that is how I want it. :) H'mm. If this is so then how do you give feed back to a user when files are locked. Scenario: User 1 locks files for a long running job. User 2 logs on, then try's to lock files but can't because user 1 already has the lock. If there is no timeout value then the program for user 2 will just appear to hang. How do I give feed back to user 2 to say something like: Unable to lock files after waiting 60 seconds, try again later or whatever. IE I would like the program to be able to return control after a specific amount of time if the lock is not satisfied (I would think MySql would give an error message) in order to give feedback to the user, rather than the the program just wait and appearing to hang. This behavior occurs with record locks using InnoDB files. I would have thought a similar approach would have applied to File locks? Thanks Clyde England - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Lock Tables Timeout value?
At 17:12 +0800 1/11/03, Clyde wrote: Hi, I have searched the docs but can't seem to find information on the time out value for Lock Tables (probably just me) When using Lock Tables how long does MySql wait before giving up if it can't get a lock? Forever. For table-level locks such as you acquire with LOCK TABLES, there is no timeout. This differs from (implicit) page- or row-level locking such as is performed by the BDB and InnoDB handlers. For such locking levels, it's possible to get deadlock. When the handler detects a deadlock, it aborts one of the deadlocking requests. Is there any way of changing this time out value? Thanks Clyde England - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Lock Tables Bug
It is not a bug. As soon as Computer1 releases the lock, Computer2 will have its query processed. Never write a program that keeps a lock for more than a few miliseconds. Eric Cotting wrote: MYSQL Server: 3.23.49a-log MYSQL Client: 3.23.3 alpha MYSQLGUI: 1.7.5 Operating System: Windows NT, Windows 2000 When I place a table lock on a table with one computer and issue a query on another while the lock is in affect, the computer that issues the query halts execution of the query with no error message or trace. I have tired this both in by VB6 application and using the MYSQLGui. I have tried to test the data base for the presence of a lock by issuing the SHOW TABLE STATUS command. This aslo halted my MYSQLGUI. Is this a known bug or is there any way to determine if a table has been locked without encountering the bug? Computer 1 :LOCK TABLES Customer_IDs WRITE Computer 2: SELECT * FROM Customer_IDs Halted Program Computer 2: SHOW TABLE STATUS FROM Customer Halted Program Eric Cotting FGL Environmental 805-659-0910 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: LOCK TABLES issue
I'll try posting this again, because nobody has replied to it. To put the problem more succinctly: I think there is a flaw in the mysql LOCK statement semantics, because if I use LOCK then all tables that are accessed while the LOCK is active must be locked for READ or WRITE access (or insert, but that doesn't help me). This is done I think to protect the application writer against accidently not getting locks that are required. What is needed is an explicit way to allow a particular table to participate while the LOCK statement is active, but without locking it. I've suggested that LOCK ... table NOLOCK would be an appropriate addition to the LOCK statement for these semantics--it allows a table to be explicitly left unlocked. More details are available in my original post, which is included here. I would appreciate comments on this. (If there's a better place to post this, can someone let me know that, too?). Thanks, Bob Bob Sidebotham wrote: I have an application with fairly typical locking requirements: 99% of the requests are read-only (with an exception that I will specify). Update requests are relatively rare. There's a half-dozen tables, and since the inter-relationships are a little complex. I found it easiest, given the performance constraints of my application, to simply lock ALL the tables for READ for the read requests, and to lock ALL of them for WRITE in the case of an update. I think this is a fine, time-tested, conservative locking strategy. It basically can't fail, and gives me adequate performance for my needs. I have ONE table which records access counts/times for each user for individual objects in the system. This table needs to be updated on every access. This table can be updated correctly without obtaining any locks. It is subject to being read at unpredictable times during any of the read-only requests. Since the access table can be read during any of the read-only requests, and since it can be read at any time during the transaction, I have to obtain at least a READ lock for this table along with the other locks (even though I don't really need a read-lock) because MySQL insists that if any tables are locked, then every table you wish to access must also be locked (I assume this feature is intended as a reasonable precaution against accidently forgetting to lock a table that must participate in a transaction). Unfortunately, to update this table I have to either upgrade to a WRITE lock or drop the lock altogether. It's obvious that upgrading to a WRITE lock will cause all my read-only operations to pileup on the WRITE lock. It's also possible for me to drop all the locks (and record the accesses at the very end of the transaction). Less obvious, but I think true, is that this *also* causes serialization, because MySQL must implicitly require all the READ locks on the table to be dropped before allowing me to update it (is this true? If it isn't true, it should be!). I cannot, by the way, use READ LOCAL because I want to use both UPDATE and REPLACE on the table. So I seem to be caught between a LOCK and a hard place, so to speak. What I would like to see would be something like: LOCK TABLES t1 READ, t2 READ, t3 NOLOCK; The semantics of this would be to explicitly recognize that t3 does not need to be locked and can therefore be read or written after this LOCK TABLES request (as opposed to any table that is not mentioned which cannot be read or written). NOLOCK would, of course, be incompatible with READ or WRITE locks, but would be compatible with other NOLOCK locks or with no lock at all, for both read and write operations. If anyone can suggest another way to do this, I'd appreciate it. Otherwise, is there any reaction to this proposal? Does anyone think this is useful functionality? Thanks, Bob Sidebotham - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: LOCK TABLES issue
You are using InnoDB or MyISAM??? If u r going for InnoDB, Refer to InnoDB Engine manual for the version 3.23.47 at www.innodb.com. Here you details of different LOCK types. You can select the appropriate lock type at row level for different possibilities. Hope this works!! Cheers :) Rama Raju -Original Message- From: Bob Sidebotham [mailto:[EMAIL PROTECTED]] Sent: Monday, January 07, 2002 2:00 PM To: [EMAIL PROTECTED] Subject: Re: LOCK TABLES issue I'll try posting this again, because nobody has replied to it. To put the problem more succinctly: I think there is a flaw in the mysql LOCK statement semantics, because if I use LOCK then all tables that are accessed while the LOCK is active must be locked for READ or WRITE access (or insert, but that doesn't help me). This is done I think to protect the application writer against accidently not getting locks that are required. What is needed is an explicit way to allow a particular table to participate while the LOCK statement is active, but without locking it. I've suggested that LOCK ... table NOLOCK would be an appropriate addition to the LOCK statement for these semantics--it allows a table to be explicitly left unlocked. More details are available in my original post, which is included here. I would appreciate comments on this. (If there's a better place to post this, can someone let me know that, too?). Thanks, Bob Bob Sidebotham wrote: I have an application with fairly typical locking requirements: 99% of the requests are read-only (with an exception that I will specify). Update requests are relatively rare. There's a half-dozen tables, and since the inter-relationships are a little complex. I found it easiest, given the performance constraints of my application, to simply lock ALL the tables for READ for the read requests, and to lock ALL of them for WRITE in the case of an update. I think this is a fine, time-tested, conservative locking strategy. It basically can't fail, and gives me adequate performance for my needs. I have ONE table which records access counts/times for each user for individual objects in the system. This table needs to be updated on every access. This table can be updated correctly without obtaining any locks. It is subject to being read at unpredictable times during any of the read-only requests. Since the access table can be read during any of the read-only requests, and since it can be read at any time during the transaction, I have to obtain at least a READ lock for this table along with the other locks (even though I don't really need a read-lock) because MySQL insists that if any tables are locked, then every table you wish to access must also be locked (I assume this feature is intended as a reasonable precaution against accidently forgetting to lock a table that must participate in a transaction). Unfortunately, to update this table I have to either upgrade to a WRITE lock or drop the lock altogether. It's obvious that upgrading to a WRITE lock will cause all my read-only operations to pileup on the WRITE lock. It's also possible for me to drop all the locks (and record the accesses at the very end of the transaction). Less obvious, but I think true, is that this *also* causes serialization, because MySQL must implicitly require all the READ locks on the table to be dropped before allowing me to update it (is this true? If it isn't true, it should be!). I cannot, by the way, use READ LOCAL because I want to use both UPDATE and REPLACE on the table. So I seem to be caught between a LOCK and a hard place, so to speak. What I would like to see would be something like: LOCK TABLES t1 READ, t2 READ, t3 NOLOCK; The semantics of this would be to explicitly recognize that t3 does not need to be locked and can therefore be read or written after this LOCK TABLES request (as opposed to any table that is not mentioned which cannot be read or written). NOLOCK would, of course, be incompatible with READ or WRITE locks, but would be compatible with other NOLOCK locks or with no lock at all, for both read and write operations. If anyone can suggest another way to do this, I'd appreciate it. Otherwise, is there any reaction to this proposal? Does anyone think this is useful functionality? Thanks, Bob Sidebotham - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL
