Footer view errors with: "ORDER BY clause is not in SELECT list"

[mjshelley]

Hey guys,

I’m getting an error when trying to view the available footers in Pro/Footers in Pro 3.0.4.

The footers are not being listed, but they are still accessible for editing if you know the ID.

I’m getting the following error reported in the debug log:

[11-Oct-2019 08:41:36 UTC] WordPress database error Expression #1 of ORDER BY clause is not in SELECT list, references column 'skidsdoc.wp_posts.post_date' which is not in SELECT list; this is incompatible with DISTINCT for query 
			SELECT DISTINCT YEAR( post_date ) AS year, MONTH( post_date ) AS month
			FROM wp_posts
			WHERE post_type = 'attachment'
			ORDER BY post_date DESC
		 made by require('wp-blog-header.php'), wp, WP->main, WP->parse_request, do_action_ref_array('parse_request'), WP_Hook->do_action, WP_Hook->apply_filters, Cornerstone_App_Boot->detect_load, do_action('cornerstone_boot_app'), WP_Hook->do_action, WP_Hook->apply_filters, Cornerstone_App->load, Cornerstone_App->enqueue_scripts, Cornerstone_App->prime_editor, wp_editor, _WP_Editors::editor, do_action('media_buttons'), WP_Hook->do_action, WP_Hook->apply_filters, media_buttons, wp_enqueue_media
[11-Oct-2019 08:41:39 UTC] WordPress database error Unknown column 'cs_footer' in 'where clause' for query SELECT ID, post_title, post_name, post_modified, post_date  FROM wp_posts WHERE post_type = "cs_footer" made by require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), do_action('template_redirect'), WP_Hook->do_action, WP_Hook->apply_filters, Cornerstone_Router->endpoint, Cornerstone_Router->controllers, Cornerstone_Router->get_aggregate_response, Cornerstone_Controller_Adapter->__call, Cornerstone_Plugin_Base->component, Cornerstone_Plugin_Base->loadComponent, Cornerstone_Model_Footers_Entry->setup

I’m currently unable to modify the ONLY_FULL_GROUP_BY flag in our MySQL database ( I have a support call out with Digital Ocean regarding this)

Do you have an alternative fix for this?

Thanks,

Matthew

[mjshelley]

For anyone else experiencing this issue, a temp fix appears to be to add the following code to your WordPress installation:

//Database fix
add_action( 'init', 'mysqlfix',-1 );

function mysqlfix() {
    global $wpdb;
    # Removed ONLY_FULL_GROUP_BY
    $wpdb->set_sql_mode("SET SESSION sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'");
}

[lely]

Thank you for sharing the fixed that works for you Matthew!

Cheers.