MySQL Bugs: #8529: Third-level select subquery produces inconsistent results after a while

Bug #8529	Third-level select subquery produces inconsistent results after a while
Submitted:	15 Feb 2005 23:35	Modified:	30 Jun 2005 15:01
Reporter:	Alberto Garza	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.2	OS:	Linux (Linux 2.6)
Assigned to:		CPU Architecture:	Any

Description:
When using a query that has several subqueries and joins, after several uptime hours (usually more than 40 hrs.) it was found that its results were none or not what it was expected. Reboot fixes the problem. After several attempts to determine what exactly was wrong with the query, it was determined that the third-level subqueries (subqueries of subqueries of subqueries) were causing some problems with the cached indexes (at least that's what it seems). After these third-level subqueries were eliminated, the problem was fixed without having to reboot the mysql server. This problem has not been encountered in the development server even though they both production and development servers have the same mysql version. This seems to indicate that the problem arises when the same query is executed multiple times.

How to repeat:
Simply create a complex query that contains several levels of subqueries (i.e. selects inside selects) and execute such query many times for a long period of time (at least 40 hrs).

Hi!

Please print SHOW CREATE TABLE for all tables involved, and show the exact query, as well as EXPLAIN SELECT ... for that query.

What result was wrong, and in what way?

Are you using the MySQL query cache?

Regards,

Heikki

show create table

Attachment: agentbugs.sql (application/octet-stream, text), 2.63 KiB.

This is show create table, agentbugs.sql is really the query!

Attachment: tables.sql (application/octet-stream, text), 7.10 KiB.

explain select result

Attachment: explainagentbugs.txt (text/plain), 7.91 KiB.

I have added three files including the information requested.
The expected result is the true bug requests for an agent. The actual result was sometimes null and other times an inaccurate result. It was not consistent. Just after I added this bug, I also noticed that the two persons who had this problem were the most queried for. In other words, this query was executed more times for these two agents than any other agents and after a while the results were beginning to be inconsistent only for these two agents.
Here is some more information about query cache in my server:
mysql> show variables like '%query_cache%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| have_query_cache             | YES       |
| query_cache_limit            | 1048576   |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 209715200 |
| query_cache_type             | ON        |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+
6 rows in set (0.00 sec)

Thanks!

-Alberto

Hi

Can you please upload a copy of your my.cnf file too ?

Thanks

I have added the my.cnf file as requested.

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

No feedback was provided for this bug for over a month? I was the last one in "providing the feedback" where I mentioned that the requested file was included as requested. What else am I supposed to do?

Nothing. As you can see bug status is "Open" again

Is someone in mysql working on this bug?

Alberto, I tried reproducing your bug and got the following error when I executed the query:

b8529.agentcustombugstatus

Also, I had to remove all the constraints in your tables because the create statements would fail with error 150.  Can you please make sure that all the tables required to reproduce this bug are uploaded?

Thanks a lot

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