MySQL Bugs: #110811: MyODBC returns Table 'table name' was not locked with LOCK TABLES

Bug #110811	MyODBC returns Table 'table name' was not locked with LOCK TABLES
Submitted:	26 Apr 2023 7:18	Modified:	2 Jun 2023 9:35
Reporter:	Jernej Pecjak	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	8.0.33	OS:	Windows (2019 server)
Assigned to:	MySQL Verification Team	CPU Architecture:	x86

Description:
When upgrading from myODBC 8.0.32 to 8.0.33 the mysql queries use like 3x times that they did on 8.0.32.

Apart from that I get many errors in my ASP named:

[MySQL][ODBC 8.0(w) Driver][mysqld-8.0.33]Table 'table_name' was not locked with LOCK TABLES

I don't use transactions and I never had the need to lock tables. This is clearly severe bug.

When I downgraded myODBC to 8.0.32, it is working again with no problem.

How to repeat:
Use select statements on high traffic site in classic ASP.

Hello,

Thank you for the bug report.
Could you please provide repeatable test case (Sample project, SQL file, etc. - please make it as private if you prefer) to confirm this issue at our end?

Regards,
Ashwini Patil

Unfortunately I can not send example as this bug happens because our site is high volume and traffic, I guess with one session it won't happen, needs multiple sessions open at once.

But there is definitely severe bug in myODBC 8.0.33 that was not there before and it presents as table is not locked...

Jernej,

Sorry to hear about the issues you had with the new version of Connector/ODBC.

We understand the issues might be hard to repeat in the test environment.
However, we still need more details in order to understand the nature of the problem.

For that reason we would like you to answer the following questions:

1. Does the problem come in the same part of the code?

2. What type of queries are associated with the error? (SELECT, INSERT, UPDATE, DELETE, other)

3. In ASP when running queries the locking type is normally specified (adLockOptimistic, adLockPessimistic, adLockReadOnly, etc). What lock type is normally used in your queries?

4. Can you send a few lines of ASP code around the query being run along with the context? (such as the parameters if any, etc)

5. What connection options is your ASP application using? (A connection string or DSN parameters would be helpful)

Thanks.

1. No, at some points, but they are somehow at the same part of the code, at the beginning of every web page I have code that reads one table that has many cells (variable table for my webpage). There is usually this error. Can be also on some other places, but mostly at the beginning of a page.

2. Mostly SELECT queries, but then again, I have mostly select queries on a page anyway.

3, 4, 5. I have not specified lock type. I use ADO.

Here is my connect string:

Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open  "Provider=MSDASQL;Driver={MySQL ODBC 8.0 Unicode Driver};server=xxx;uid=xxx;pwd=xxx;database=xxx;OPTION=16384;"

Here is the first query that has this problem when there is traffic on site:

SQLstmt = "SELECT a,b,c,d,e,f,g,h,i,j,k,l FROM table" 
Set RS = Conn.Execute(SQLStmt)

***

Something seems really off, as my page usually uses like 0.13 sec ASP time to generate the page. When using MyODBC 8.0.33 that goes to like 0.53 sec, that is 4x the time...

There are also this errors so I thing sessions get stuck somehow:

[MySQL][ODBC 8.0(w) Driver][mysqld-8.0.33]Query execution was interrupted, maximum statement execution time exceeded

I reverted back to 8.0.32 and instantly my pages open fast again and I have not encountered this error any longer. Something is really off in 8.0.33... I upgrade every version for 6 years and I have not encountered such severe problem with myODBC on a general level. Must be something you have done to 8.0.33, I was reading the log change document, but I can not find any indication you changed something that would matter in this case.

If I can be of any further assistance, I would gladly help.

Thanks for your feedback.
I still need to clarify a few things regarding locking:

> I have not specified lock type. I use ADO.

ADO does have locks implicitly or explicitly applied to records:

https://learn.microsoft.com/en-us/sql/ado/reference/ado-api/locktypeenum?view=sql-server-v...

The possible values are:

AdoEnums.LockType.BATCHOPTIMISTIC (adLockBatchOptimistic)
AdoEnums.LockType.OPTIMISTIC (adLockOptimistic)
AdoEnums.LockType.PESSIMISTIC (adLockPessimistic)
AdoEnums.LockType.READONLY (adLockReadOnly)
AdoEnums.LockType.UNSPECIFIED (adLockUnspecified)

Can you look into your code and find out if it is using any of the above constants?

Hm, I do not know about lock type, I do not set it anywhere, it has to be the default.

Do you know a way I could read the value using ASP somehow?

From what I read, the default locktypes are:

adLockReadOnly for forward-only, read-only recordsets
adLockPessimistic for other types of recordsets

I guess this is what I use.

The Connector/ODBC driver does not lock tables unless specifically directed by the user code. With ADO it can acquire locks for you. However, there are certain restrictions when using locked tables as described here in the online manual:

https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html#table-lock-acquisition

In particular, the error like yours could be caused by using table aliases as follows:

If a statement refer to a table by means of an alias, the table must be locked using that same alias. It does not work to lock the table without specifying the alias:

mysql> LOCK TABLE t READ;
mysql> SELECT * FROM t AS myalias;
ERROR 1100: Table 'myalias' was not locked with LOCK TABLES

Conversely, if a table is locked using an alias, an alias must be used in the statements:

mysql> LOCK TABLE t AS myalias READ;
mysql> SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> SELECT * FROM t AS myalias;

So, the above errors could be triggered by adding aliases to SELECT queries in your application, which might seem unrelated to the error it produces. Can you check if this is the case?

Also, looking through the general query log for "LOCK TABLE" statements could be helpful. Can you copy/paste such query here if you find it in the general query log? We need to have a full "LOCK TABLE" statement, which locks a specific table and a SELECT statement for the same table, which produces the error.

The problem is... I do not use lock tables statements. I never had any use for that...

I use aliases in select, yes, but LOCK TABLE, no.

And don't forget, on 8.0.32 or prior version of myODBC everything works 100%. On 8.0.33 suddenly the query execution time goes to 400% of time and this problems with locks arise.

You might not run LOCK statements directly in your code, but it does not mean ADO is not using them. The error you have suggests that this might be the case. So, we need to see what has been locked and how.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".