After creating new post, need to reset post order

[beatricelucaci]

I have this problem after last night upgrade MariaDB 10.5 to 10.6
After creating new post and after saving old post , i need to reset post order and update…

I downgraded MariaDB to version 10.5 and now everything works.
The plugin is not compatible with MariaDB 10.6.

https://wordpress.org/support/topic/after-creating-new-post-need-to-reset-post-order/

[alexgw]

Hi is there a plan to address this issue? We use the plugin on many sites that are being impacted by this

[sebastiencyr]

I can confirm that the plugin has issues with MariaDB.

It seems that MariaDB evaluates the SELECT statement before the ORDER BY which gives an incorrect result for the subquery here in SCPO_Engine::refresh():

$wpdb->query( 'SET @row_number = 0;' );
$wpdb->query(
	"UPDATE $wpdb->posts as pt JOIN (

              SELECT ID, (@row_number:=@row_number + 1) AS `rank`
              FROM $wpdb->posts
              WHERE post_type = '$object' AND post_status IN ( 'publish', 'pending', 'draft', 'private', 'future' )
              ORDER BY menu_order ASC
            ) as pt2
            ON pt.id = pt2.id
            SET pt.menu_order = pt2.`rank`;"
);

Maybe do the subquery separate and use PHP for the row numbers for MariaDB?

////// update

I did this to fix the issue instead of simply commenting out the original and it works on both MySQL and MariaDB:

$current_orders = $wpdb->get_results( 
	$wpdb->prepare( "SELECT ID, menu_order FROM {$wpdb->posts} WHERE post_type = %s AND post_status IN ('publish', 'pending', 'draft', 'private', 'future') ORDER BY menu_order ASC", $object ) 
);

$subquery = "";

foreach ( $current_orders as $key => $current_order ) {
	if( ! $key ){
		$subquery .= $wpdb->prepare( "SELECT %s ID, %d `rank`", $current_order->ID, $key + 1 );
	}else{
		$subquery .= $wpdb->prepare( " UNION ALL SELECT %s, %d", $current_order->ID, $key + 1 );
	}
}

$wpdb->query( 'SET @row_number = 0;' );
$wpdb->query(
	"UPDATE $wpdb->posts as pt JOIN (
		$subquery
            ) as pt2
            ON pt.id = pt2.id
            SET pt.menu_order = pt2.`rank`;"
);

[alvasilakis]

Have the same issue do we know when this will be implemented ?

[simshaun]

This is still a problem.

We have MariaDB 10.6.4 on a client site and custom post orders are lost after creating or deleting an item.

Implementing the fix by @sebastiencyr solved the issue.

[cristianraiber]

cc @TeoAlex