[wp-hackers] Possible Wordpress or MySQL bug on different Ubuntu versions?

[Justas Butkus]

It is clearly a MySQL related issue. But not necessarily related to some 
particular version - you might, just, have not experienced it up until now.

The issue is as following: if you provide a query to MySQL, where MySQL 
may not decide particular order for records returned, the result, where 
order is not specified, will be returned in *random* order.

Here I highlight *random*, because it is not fully random, and is 
decided by the way, rows are stored. And here "rows are stored" does not 
mean "in ascending order by primary key". It means - how records are 
stored in physical media, that is - disk drive, where MySQL writes data to.

Although your solution may be a bit hack'ish (as specification is 
ambiguous) - it is the correct solution. You must always clearly specify 
the order, in which you would like your rows to be returned.

Between consequent runs the rows shall be returned in the same (likely 
*random*) order, although that may not be true, as when MySQL reads rows 
from the disk, it may store that data in RAM. Since that point ordering 
may change the second time.

Why you have experienced it just after upgrading to some different OS 
version? It may be, that new versions ships different MySQL 
configuration file, which tells MySQL to store more data on RAM, and 
previously your records were always re-read from disk, and are now 
fetched from RAM.

Despite that, it is still an interesting issue, and may be worth a 
second look, as MySQL shall live to the promise, to return the same 
data, until it is changed. And by "same data" one may mean records order 
as well.


-- 
Regards,
Justas

On 2013.01.30 23:29, Roberto Sanchez wrote:
> Please excuse the long email, I wanted to include all relevant details.
>
> I've run across a really weird bug with WP_Query that seems to depend on
> the version of Ubuntu I'm using.
>
> I'm running Wordpress on a VPS that runs Ubuntu. The weird bug has to do
> with sorting. For reference, here are the arguments I'm passing to WP_Query:
>
> $query_args = array(
>      'post_type' => 'coupon',
>      'posts_per_page' => 10,
>      'paged' => $current_page,
>      'orderby' => 'meta_value_num',
>      'order' => 'DESC',
>      'meta_key' => 'score',
>      'post_status' => 'publish',
>      'tax_query' => array(
>          array(
>              'taxonomy' => 'merchant',
>              'field' => 'slug',
>              'terms' => $slug
>          )
>      ),
>      'meta_query' => array(
>          array(
>              'key' => 'startdate',
>              'value' => date( 'c' ),
>              'compare' => '<=',
>              'type' => 'DATETIME'
>          ),
>      )
> );
>
> I'm getting posts of post_type 'coupon', associated with a 'merchant'
> taxonomy, filtered to show only posts with a 'startdate' post meta that is
> before the current time, and ordering them by post meta 'score'.
>
> The query could return many posts with the same 'score', and here is where
> the problem starts. The query returns posts correctly ordered by 'score',
> but if I have for example 20 posts in a row with the same 'score', there's
> no guarantee it would return all those posts in the same order. Those 20
> posts with the same 'score', while correctly coming after posts with a
> higher 'score' but before posts with a 'lower' score, will come in complete
> random order among posts with the same 'score'. This is a problem because I
> am getting 10 posts per page, so with those 20 posts being randomized, I
> could load a duplicate post on a later page.
>
> The weird part about this bug is that it seems to depend on Ubuntu
> versions. My development site was running on 11.04, while my live site was
> running on 12.04. I was only having the sorting problem on the live site,
> and after ensuring every other setting was identical between the live site
> and the development site, I upgraded my development site to 12.04 from
> 11.04, and now I have the sorting problem on both sites. So it seems that
> this is not a problem on 11.04, but it is a problem on 12.04.
>
> I tried taking the MySQL query that wpdb runs, and running it directly from
> phpmyadmin, and the posts were returned in random order there, so it makes
> me think it's less a wordpress problem, more a MySQL problem. PHP, Apache,
> and MySQL were all upgraded when I upgraded my development VPS from Ubuntu
> 11.04 to 12.04.
>
> There is a workaround, however. I first thought to try ordering the posts
> by post ID as well, to see if that would ensure the posts get returned in
> the same order every time. I changed the WP_Query argument 'orderby' to
> this:
>
> 'orderby' => 'meta_value_num ID'
>
> This should order by 'score' first, then by post ID. However, trying this,
> it returned the posts in reverse 'score' order, and changing 'order' from
> 'DESC' to 'ASC' had no effect.
>
> I checked what query wpdb is using after adding ID to 'orderby', and the
> orderby clause looks like this:
>
> ORDER BY wp_postmeta.meta_value+0 ,wp_posts.ID DESC
>
> So I tried using a 'posts_orderby' filter to append wp_posts.ID DESC to the
> orderby clause, to have it looks like this:
>
> ORDERBY wp_postmeta.meta_value+0 DESC, wp_posts.ID DESC
>
> With this, it finally returns the post in correct 'score' order, and
> returns all posts in the same order each time the query is run.
>
> I'm looking for any insight as to why I should need to do this workaround
> when I'm running my Wordpress website on Ubuntu 12.04, and why I don't need
> it on 11.04.  Does anyone know any possible changes to MySQL that could
> have caused this? Maybe any changes to Wordpress from 3.5 to 3.5.1 could
> have caused this? I found a workaround, but it feels like a hack and I
> would rather know why I need it, to see if there's a more elegant solution
> possible.
>
> Thanks.