Incorrect # of results returned

G'Day! We have a problem where the application talking to MySQL through
Connector/J asks for an increasing large result set, starting at 50 and
growing to 250, where there are 219 records in the table that meet the WHERE
criteria.

On every occasion we get back 50 results. Clearly we need to change the
setting for how many records to return on each occasion, but we do, and
apparently to no effect. In order to diagnose this we used an instance of
MySQL with logging turned on, so we have the logs of exactly what was sent
to the database. I can take that log and enter the relevant lines one at a
time into mysql directly and get the same results, on each occasion I get
back 50 records.

I'm running 4.0.12 on Solaris 8, however I tried it again with Solaris 9 and
with 4.0.11a and got the same thing.

How to reproduce:

Log entries showing queries executed in order:

SET OPTION SQL_SELECT_LIMIT=50;
SELECT jiveForum.forumID, jiveForum.modifiedDate FROM jiveForum WHERE
jiveForum.categoryID=1 ORDER BY jiveForum.modifiedDate DESC;
SET OPTION SQL_SELECT_LIMIT=DEFAULT;

SET OPTION SQL_SELECT_LIMIT=100;
SELECT jiveForum.forumID, jiveForum.modifiedDate FROM jiveForum WHERE
jiveForum.categoryID=1 ORDER BY jiveForum.modifiedDate DESC;
SET OPTION SQL_SELECT_LIMIT=DEFAULT;

SET OPTION SQL_SELECT_LIMIT=150;
SELECT jiveForum.forumID, jiveForum.modifiedDate FROM jiveForum WHERE
jiveForum.categoryID=1 ORDER BY jiveForum.modifiedDate DESC;
SET OPTION SQL_SELECT_LIMIT=DEFAULT;

SET OPTION SQL_SELECT_LIMIT=200;
SELECT jiveForum.forumID, jiveForum.modifiedDate FROM jiveForum WHERE
jiveForum.categoryID=1 ORDER BY jiveForum.modifiedDate DESC;
SET OPTION SQL_SELECT_LIMIT=DEFAULT;

SET OPTION SQL_SELECT_LIMIT=250;
SELECT jiveForum.forumID, jiveForum.modifiedDate FROM jiveForum WHERE
jiveForum.categoryID=1 ORDER BY jiveForum.modifiedDate DESC;
SET OPTION SQL_SELECT_LIMIT=DEFAULT;


What's worse here is that according to the logs each set of commands were
executed on different threads - for brevity and readability I have trimmed
the log entry to just the queries and added a ; at the end so I could
execute the queries directly in mysql.

If it's important the table is InnoDB and is setup as follows:

mysql> desc jiveForum;
+---------------------+---------------------+------+-----+-- -------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+---------------------+------+-----+-- -------+-------+
| forumID | bigint(20) | | PRI | 0 | |
| name | varchar(255) binary | | UNI | | |
| description | text | YES | | NULL | |
| modDefaultThreadVal | bigint(20) | | | 0 | |
| modMinThreadVal | bigint(20) | | | 0 | |
| modDefaultMsgVal | bigint(20) | | | 0 | |
| modMinMsgVal | bigint(20) | | | 0 | |
| creationDate | varchar(15) | | | | |
| modifiedDate | varchar(15) | | | | |
| categoryID | bigint(20) | | MUL | 1 | |
| categoryIndex | int(11) | | MUL | 0 | |
+---------------------+---------------------+------+-----+-- -------+-------+
11 rows in set (0.09 sec)

There are 219 records, all of which meet the search criteria specified in
the WHERE statements above.

So why am I only getting 50 rows back after my queries, and how do I fix it.

Best Regards, Bruce


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Incorrect # of results returned

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Bruce Dembecki wrote:
| G'Day! We have a problem where the application talking to MySQL through
| Connector/J asks for an increasing large result set, starting at 50 and
| growing to 250, where there are 219 records in the table that meet the
WHERE
| criteria.
|
| On every occasion we get back 50 results. Clearly we need to change the
| setting for how many records to return on each occasion, but we do, and
| apparently to no effect. In order to diagnose this we used an instance of
| MySQL with logging turned on, so we have the logs of exactly what was sent
| to the database. I can take that log and enter the relevant lines one at a
| time into mysql directly and get the same results, on each occasion I get
| back 50 records.
|
| I'm running 4.0.12 on Solaris 8, however I tried it again with Solaris
9 and
| with 4.0.11a and got the same thing.
|
| How to reproduce:
|
| Log entries showing queries executed in order:
|
| SET OPTION SQL_SELECT_LIMIT=50;
| SELECT jiveForum.forumID, jiveForum.modifiedDate FROM jiveForum WHERE
| jiveForum.categoryID=1 ORDER BY jiveForum.modifiedDate DESC;
| SET OPTION SQL_SELECT_LIMIT=DEFAULT;
|
| SET OPTION SQL_SELECT_LIMIT=100;
| SELECT jiveForum.forumID, jiveForum.modifiedDate FROM jiveForum WHERE
| jiveForum.categoryID=1 ORDER BY jiveForum.modifiedDate DESC;
| SET OPTION SQL_SELECT_LIMIT=DEFAULT;
|
| SET OPTION SQL_SELECT_LIMIT=150;
| SELECT jiveForum.forumID, jiveForum.modifiedDate FROM jiveForum WHERE
| jiveForum.categoryID=1 ORDER BY jiveForum.modifiedDate DESC;
| SET OPTION SQL_SELECT_LIMIT=DEFAULT;
|
| SET OPTION SQL_SELECT_LIMIT=200;
| SELECT jiveForum.forumID, jiveForum.modifiedDate FROM jiveForum WHERE
| jiveForum.categoryID=1 ORDER BY jiveForum.modifiedDate DESC;
| SET OPTION SQL_SELECT_LIMIT=DEFAULT;
|
| SET OPTION SQL_SELECT_LIMIT=250;
| SELECT jiveForum.forumID, jiveForum.modifiedDate FROM jiveForum WHERE
| jiveForum.categoryID=1 ORDER BY jiveForum.modifiedDate DESC;
| SET OPTION SQL_SELECT_LIMIT=DEFAULT;
|
|
| What's worse here is that according to the logs each set of commands were
| executed on different threads - for brevity and readability I have trimmed
| the log entry to just the queries and added a ; at the end so I could
| execute the queries directly in mysql.
|
| If it's important the table is InnoDB and is setup as follows:
|
| mysql> desc jiveForum;
|
+---------------------+---------------------+------+-----+-- -------+-------+
| | Field | Type | Null | Key | Default |
Extra |
|
+---------------------+---------------------+------+-----+-- -------+-------+
| | forumID | bigint(20) | | PRI | 0 |
|
| | name | varchar(255) binary | | UNI | |
|
| | description | text | YES | | NULL |
|
| | modDefaultThreadVal | bigint(20) | | | 0 |
|
| | modMinThreadVal | bigint(20) | | | 0 |
|
| | modDefaultMsgVal | bigint(20) | | | 0 |
|
| | modMinMsgVal | bigint(20) | | | 0 |
|
| | creationDate | varchar(15) | | | |
|
| | modifiedDate | varchar(15) | | | |
|
| | categoryID | bigint(20) | | MUL | 1 |
|
| | categoryIndex | int(11) | | MUL | 0 |
|
|
+---------------------+---------------------+------+-----+-- -------+-------+
| 11 rows in set (0.09 sec)
|
| There are 219 records, all of which meet the search criteria specified in
| the WHERE statements above.
|
| So why am I only getting 50 rows back after my queries, and how do I
fix it.
|
| Best Regards, Bruce

I've mocked this up on 4.0.12 (on Win32) and don't see this behavior. Is
it possible for you to test it on 4.0.12, or in-lieu of that provide a
dump of the table in question, so I can try it locally?

-Mark


- --
MySQL 2003 Users Conference -> http://www.mysql.com/events/uc2003/

For technical support contracts, visit https://order.mysql.com/?ref=mmma

~ __ ___ ___ ____ __
~ / |/ /_ __/ __/ __ \/ / Mark Matthews
~ / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java
~ /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA
~ <___/ www.mysql.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE+hNJAtvXNTca6JD8RAl/2AJ9UonDikpeI98V7/SIv6MfukjWM/wCf bwRW
oLIvq7iNrmn58gCWfwuZzzQ=
=ld4K
-----END PGP SIGNATURE-----


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Incorrect # of results returned

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Bruce Dembecki wrote:
| On 3/28/03 2:52 PM, "Mark Matthews" wrote:
|
|
|>I've mocked this up on 4.0.12 (on Win32) and don't see this behavior. Is
|>it possible for you to test it on 4.0.12, or in-lieu of that provide a
|>dump of the table in question, so I can try it locally?
|>
|>-Mark
|>
|>
|
| G'Day! I've included below the entire transcript of a mysql session
starting
| with a Status command to show version etc... Then I go step by step
through
| the sample I gave originally showing the 50 results each time, and
finally a
| count of the number of rows that match the criteria. I am also attaching a
| dump of the table as requested.
|
| This was a 4.0.12 problem, although we also see it in 4.0.11a, and in both
| cases under Solaris 8 and Solaris 9. The table is an InnoDB table.

It seems that this can be repeated on Solaris, but not on Intel.

This is now filed in our bugs system as Bug #209. You can track the
progress of this bug at:

http://bugs.mysql.com/bug.php?id=209


- --
MySQL 2003 Users Conference -> http://www.mysql.com/events/uc2003/

For technical support contracts, visit https://order.mysql.com/?ref=mmma

~ __ ___ ___ ____ __
~ / |/ /_ __/ __/ __ \/ / Mark Matthews
~ / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java
~ /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA
~ <___/ www.mysql.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE+hODFtvXNTca6JD8RAkKVAJ9bgwM1JFkaAM9TMYalHHxxO2hHLgCe MqJm
mRHRbGrSgSVEIZa5jOiDk/M=
=+t7H
-----END PGP SIGNATURE-----


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Incorrect # of results returned

So this came to me in the middle of the night, so much so that I had to get
up and send this note (gosh, 3:00am on a Sunday morning and all I can think
about is MySQL bugs)...

It occurs to me the other thing I changed during our Friday morning
maintenance when we installed 4.0.12 was to turn on the Query Cache (OK, it
was always "on" I suppose, I gave it some memory so it became a factor). It
seems to me that it's possible that if you do a query with a SET OPTION
SQL_SELECT_LIMIT at say, 50 in our case, and then do another immediately
with the SAME query, but a SET OPTION SQL_SELECT_LIMIT of 100, the query
cache will kick in because the query is the same as far it as concerned, an=
d
will return the data from the cache, which happens to be 50 records.

I haven't tested it (it is 3:00am after all) but I expect if I configure my
MacOSX Laptop to use Query Cache that I can reproduce the bug on a non
Solaris MySQL.

This of course would make the Query Cache fundamentally incompatible with
SQL_SELECT_LIMIT, and likely not platform specific... We just got lucky tha=
t
your Solaris test machine has the Query Cache active and thus reproduced th=
e
bug, whereas Mark's Intel machines may not have had Query Cache enabled and
thus didn't reproduce the bug. I also suspect it would be true with any
table or database, not just ours.

This would also explain why we saw this behavior only after we did the
maintenance window on Friday, and could reproduce it in either 4.0.11 and
4.0.12, because in both instances from Friday onwards the Query Cache was
turned on, and before Friday Query Cache wasn't functioning and thus didn't
cause the problem.

Also if my theory is right then the title Bug=A0#209
SET OPTION SQL_SELECT_LIMIT not being honored on Solaris - would not be
strictly speaking a helpful pointer to the problem which is an
incompatibility between Query Cache and SQL_SELECT_LIMIT.

It occurs to me if Connector/J used LIMIT=3Dnn at the end of a SELECT instead
of a separate query setting and unsetting SQL_SELECT_LIMIT then "SELECT *
FROM table LIMIT 50" is a different query from "SELECT * FROM table LIMIT
100" and doesn't trigger the Query Cache... Of course this isn't a
Connector/J problem, and fixing Connector/J to work around the problem isn'=
t
as effective as fixing the problem (but adding LIMIT=3Dnn to queries where
needed is cheaper than adding a set and reset query before and after your
actual query - 1 query instead of 3 and so on).

Anyway, there's a pointer on where to look... of course I may be wrong, tha=
t
wouldn't be anything new either :-)

Best Regards, Bruce

On 3/28/03 3:54 PM, "Mark Matthews" wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>=20
> Bruce Dembecki wrote:
> | On 3/28/03 2:52 PM, "Mark Matthews" wrote:
> |
> |
> |>I've mocked this up on 4.0.12 (on Win32) and don't see this behavior. I=
s
> |>it possible for you to test it on 4.0.12, or in-lieu of that provide a
> |>dump of the table in question, so I can try it locally?
> |>
> |>-Mark
> |>
> |>
> |
> | G'Day! I've included below the entire transcript of a mysql session
> starting
> | with a Status command to show version etc... Then I go step by step
> through
> | the sample I gave originally showing the 50 results each time, and
> finally a
> | count of the number of rows that match the criteria. I am also attachin=
g a
> | dump of the table as requested.
> |
> | This was a 4.0.12 problem, although we also see it in 4.0.11a, and in b=
oth
> | cases under Solaris 8 and Solaris 9. The table is an InnoDB table.
>=20
> It seems that this can be repeated on Solaris, but not on Intel.
>=20
> This is now filed in our bugs system as Bug #209. You can track the
> progress of this bug at:
>=20
> http://bugs.mysql.com/bug.php?id=3D209
>=20
>=20
> - --
> MySQL 2003 Users Conference -> http://www.mysql.com/events/uc2003/
>=20
> For technical support contracts, visit https://order.mysql.com/?ref=3Dmmma
>=20
> ~ __ ___ ___ ____ __
> ~ / |/ /_ __/ __/ __ \/ / Mark Matthews
> ~ / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java
> ~ /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA
> ~ <___/ www.mysql.com
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.2.1 (MingW32)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>=20
> iD8DBQE+hODFtvXNTca6JD8RAkKVAJ9bgwM1JFkaAM9TMYalHHxxO2hHLgCe MqJm
> mRHRbGrSgSVEIZa5jOiDk/M=3D
> =3D+t7H
> -----END PGP SIGNATURE-----
>=20


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=3Dgcdmb-bugs@m.gmane.org

Re: Incorrect # of results returned (SQL_SELECT_LIMIT and QCache)

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Bruce Dembecki wrote:
| So this came to me in the middle of the night, so much so that I had
to get
| up and send this note (gosh, 3:00am on a Sunday morning and all I can
think
| about is MySQL bugs)...
|
| It occurs to me the other thing I changed during our Friday morning
| maintenance when we installed 4.0.12 was to turn on the Query Cache
(OK, it
| was always "on" I suppose, I gave it some memory so it became a
factor). It
| seems to me that it's possible that if you do a query with a SET OPTION
| SQL_SELECT_LIMIT at say, 50 in our case, and then do another immediately
| with the SAME query, but a SET OPTION SQL_SELECT_LIMIT of 100, the query
| cache will kick in because the query is the same as far it as
concerned, and
| will return the data from the cache, which happens to be 50 records.
|
| I haven't tested it (it is 3:00am after all) but I expect if I
configure my
| MacOSX Laptop to use Query Cache that I can reproduce the bug on a non
| Solaris MySQL.
|
| This of course would make the Query Cache fundamentally incompatible with
| SQL_SELECT_LIMIT, and likely not platform specific... We just got
lucky that
| your Solaris test machine has the Query Cache active and thus
reproduced the
| bug, whereas Mark's Intel machines may not have had Query Cache
enabled and
| thus didn't reproduce the bug. I also suspect it would be true with any
| table or database, not just ours.

It would be quite a bit slower for the JDBC driver to do it, as in some
cases it's not as simple as just tacking 'LIMIT nn' to the end of the
query...There would be parsing involved.

It would also be nice to get the query cache to recognize this
particular setting, because the query cache would still be usable...if
the result set in the cache contained enough rows to satisfy the current
~ limit, you could use the data in the cache, thus avoiding going back to
MySQL for _every_ query of this sort.

-Mark


- --
MySQL 2003 Users Conference -> http://www.mysql.com/events/uc2003/

For technical support contracts, visit https://order.mysql.com/?ref=mmma

~ __ ___ ___ ____ __
~ / |/ /_ __/ __/ __ \/ / Mark Matthews
~ / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java
~ /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA
~ <___/ www.mysql.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE+hvKntvXNTca6JD8RAvypAJ9dG4Bb/M0vzUG6YCW/5ygRvPMLOQCd EPhX
q9tLQnKL7GkthuK6PL0ypVE=
=NIfO
-----END PGP SIGNATURE-----


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Incorrect # of results returned

Hi,

> So this came to me in the middle of the night, so much so that I had to g=
et
> up and send this note (gosh, 3:00am on a Sunday morning and all I can thi=
nk
> about is MySQL bugs)...
>=20
> It occurs to me the other thing I changed during our Friday morning
> maintenance when we installed 4.0.12 was to turn on the Query Cache (OK, =
it
> was always "on" I suppose, I gave it some memory so it became a factor).

Yep, it is indeed related to the query cache (the Solaris box I was able to
reproduce it on is a busy production server which has query cache on). I
turned QC on my MacOS X desktop now and voila, 50 results regardless of the
SQL_SELECT_LIMIT variable.

I'll update the bug report accordingly.


Rgds,
Indrek

--=20
MySQL Users Conference and Expo: http://www.mysql.com/events/uc2003/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Indrek Siitan
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, The Support Guy
/_/ /_/\_, /___/\___\_\___/ Uuem=F5isa, Haapsalu, Estonia
<___/ www.mysql.com


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=3Dgcdmb-bugs@m.gmane.org