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 Info 3.23.54-max-nt-log
have_innodb YES
innodb_additional_mem_pool_size 104857600
innodb_buffer_pool_size 1048576000
innodb_data_file_path ibdata1
innodb_data_home_dir
innodb_file_io_threads 4
innodb_force_recovery 0
innodb_thread_concurrency 8
innodb_flush_log_at_trx_commit 1
innodb_fast_shutdown ON
innodb_flush_method
innodb_lock_wait_timeout 50
innodb_log_arch_dir
innodb_log_archive OFF
innodb_log_buffer_size 1048576
innodb_log_file_size 5242880
innodb_log_files_in_group 2
innodb_log_group_home_dir .\
innodb_mirrored_log_groups 1
We have a large block of SQL statements (about 50) which we would like to
treat as
one transaction. The software works fine when running in a limited
environment,
but issues intermittent errors when running in production.
All the errors follow the pattern of "java.sql.SQLException: General error:
Table
'productsprovided' was not
locked with LOCK TABLES".
---------------------------------------------------------------------
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
