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