Re: Wrong sort order with ORDER BY

Test data
LPexportBug872280_CREATE%20test_items%20general.sql

Re: Wrong sort order with ORDER BY
Thank you for your bug report. I will try to reproduce your observations shortly.

Re: Wrong sort order with ORDER BY
Umm. I can't reproduce this behavior on another server running same version of MariaDB. This is weird.

Re: Wrong sort order with ORDER BY
Some extra information.

Correct order of items:
MariaDB [test2]> explain select * from test_items_g where item_name like "Aurora%" order by item_name;
--------------------------------------------------------------------------------+

--------------------------------------------------------------------------------+

--------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Incorrect order of items:
MariaDB [test2]> explain select * from test_items_g where item_name like "A%" order by item_name;
------------------------------------------------------------------------------------------+

------------------------------------------------------------------------------------------+

------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

One more incorrect order:
MariaDB [test2]> explain select * from test_items_g order by item_name;
-----------------------------------------------------------------------------+

-----------------------------------------------------------------------------+

-----------------------------------------------------------------------------+
1 row in set (0.00 sec)

One may notice that incorrect order only happens when "using filesort" is present.

Re: Wrong sort order with ORDER BY
... and same queries with 'force index(item_name)' added give correct results.

Re: Wrong sort order with ORDER BY
Guys, I really don't have an explanation for this, but the problem is now gone. And the only thing we changed is query_cache_size and query_cache_limit values. We decreased them from:

#query_cache_limit = 8M
#query_cache_size = 4096M

to

query_cache_limit = 1M
query_cache_size = 256M

and that's it.

Re: Wrong sort order with ORDER BY
Hello,

I was unable to repeat the problem using a 32-bit linux and the same MariaDB version.

My best guess is that you have suffered some transient corruption that got cured when you restarted the server in order to change your cache settings. Either the table got auto-repaired or the restart itself cleared up a corrupt result that was stored in the query cache.

If you keep the error logs from the server from before the latest restart, please review them for any suspicious error messages.

Otherwise, if this issue happens again, please reopen the bug and we will take it from there.

Re: Wrong sort order with ORDER BY
Philip, i have restarted the server several times before reporting the bug and after then. Nothing helped until I decreased query_cache_size. Even more: I tried to drop and recreate test database a few times and every time I got the same result. And there is nothing suspicious in the log.

Re: Wrong sort order with ORDER BY
Btw the problem is back again. I understood nothing. Is there anything I could do to be sure this is not a corrupted database but a problem with mysql? REPAIR TABLE doesn't help because it says the table is OK.

Re: Wrong sort order with ORDER BY
Hello,

In order for me to figure out what is going on, can you please answer the following questions. Apologies if I am overly verbose:

1. Does the issue appear on just a single machine, or you were able to repeat it on several machines?

2. Once the issue appears, it persists over restarts unless the cache value is changed, is that correct?

3. While you are observing the issue, are any other statements running on that table? In particular, does your workload include any ALTER or other DDL statements?

4. Can you try a different copy of mariadb on the server where the issue appears, for example MariaDB 5.3.1 beta? It is not so much about trying a different version, more about trying a different binary.

5. Can you reproduce the issue on a freshly-started server that has only executed the test data you provided, or the issue only appears if other activities have been performed on the server?

Thank you.

Re: Wrong sort order with ORDER BY
Hi Philip. First of all, this is a big production server with several thousands of active databases so I can't do too much experiments like changing binary of mysqld to another version. So there are my answers:

Q: 1. Does the issue appear on just a single machine, or you were able to repeat it on several machines?
A: At the moment I was able to repeat it only on a single server, but there are 3 instances of mysqld with different configs and the issue appears on every instance

Q: 2. Once the issue appears, it persists over restarts unless the cache value is changed, is that correct?
A: Yes it persists over restarts and I can't exlain this but now I can't find any relation between cache size and the issue. It exists even with query cache completely disabled.

Q: 3. While you are observing the issue, are any other statements running on that table? In particular, does your workload include any ALTER or other DDL statements?
A: I can see the issue on both heavily loaded mysqld instance and empty test instance with only one test database having single test table and only one client (me) connected to it

Q: 4. Can you try a different copy of mariadb on the server where the issue appears, for example MariaDB 5.3.1 beta? It is not so much about trying a different version, more about trying a different binary.
A: Unfortunatelly I can't until I build a binary myself in a different location. Or I can try set up another version of MariaDB in chroot'ed environment.

Q: 5. Can you reproduce the issue on a freshly-started server that has only executed the test data you provided, or the issue only appears if other activities have been performed on the server?
A: As I said, I can reproduce the issue on a freshly started server that has only the test data

Re: Wrong sort order with ORDER BY
Hi,

If you can, please do the following:

1. Reproduce the problem on a freshly started server that contains only the data that is required to reproduce the bug.

2. Once you have observed the wrong result, please use kill -9 to terminate the server instance;

3. Package the server's data and log directory and my.ini file in a ZIP file and attach it to this bug report.

If the wrong result is due to database corruption, this procedure may allow us to isolate the corruption and transport it to our debugging environment for further analysis.

Thank you.

Re: Wrong sort order with ORDER BY
Hi Philip

I just rsync'ed this database to another server running the same version of mariadb and I couldn't reproduce the problem with the same data there. So this is not a database corruption. Looks like my last resort is to try to run mariadb in chroot environment.

Re: Wrong sort order with ORDER BY
Philip, I am sorry but it was our fault. We had `max_sort_length = 20` on our my.cnf in every affected instance of mysqld. Looks like the bug is to be closed.

Re: Wrong sort order with ORDER BY
Yes, that would explain it. Please reopen the bug if there is anything else for us to check out.

Launchpad bug id: 872280