Automattic
Cannot create new post when HyperDB is activated
Hi there,
I just face an issue when we activate HyperDB plugin in our wordpress. I cannot create post an other taxomony related actions.
We tried different combination between wordpress version 6.0.2, 6.1 and HyperDB1.8 and 1.9 but the same error exists.
The full error is like below: [Thu Nov 10 02:56:39.541899 2022] [php:notice] [pid 319] [client 43.134.152.59:59479] WordPress database error Expression #1 of ORDER BY clause is not in SELECT list, references column 'wp_db.t.name' which is not in SELECT list; this is incompatible with DISTINCT for query \n\t\t\tSELECT DISTINCT t.term_id, tr.object_id\n\t\t\tFROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id\n\t\t\tWHERE tt.taxonomy IN ('category', 'post_tag', 'post_format') AND tr.object_id IN (7)\n\t\t\tORDER BY t.name ASC\n\t\t\t\n\t\t made by WP_Term_Query::get_terms
To enable hyperdb, we
- add db-config.php in root directory and only add below for the master node. 'write' => 1, 'read' => 1,
- add db.php in wp-content/
- define DB_CONFIG_FILE in wp-config.php file
The mysql version is 8.0.
I have run the SQL query to the database and it can be easily fixed with add t.name in the select or remove the distinct. However, I don't know how hyperDB can trigger WP_Term_Query::get_terms and generate this error?
Can you have a look and give us some guidance? Thank you.
This is caused by the newer version of MySQL, specifically that wpdb::set_sql_mode() isn't being called on the HyperDB connections.
A workaround would be to use a lower version of MySQL, or to configure MySQLs defaults to have a more "compatible" default connection mode, WordPress disables these modes.
The specific mode which is causing this failed query is ONLY_FULL_GROUP_BY, which is inferred by ANSI see https://core.trac.wordpress.org/changeset/47171
Thank you very much Dion. We solved the problem with disabling ONLY_FULL_GROUP_BY for SQL_MODE. For your reference, the NO_ZERO_DATE and NO_ZERO_IN_DATE are also needs to be disabled for some datetime format error if HyperDB is used if someone faced similar issue. Really appreciate it your fast response.
For your reference, the NO_ZERO_DATE and NO_ZERO_IN_DATE are also needs to be disabled
There's a few others too, see the above mentioned ones which WordPress disables. Your SQL host may have those others disabled already, or they may not be enabled by default, I'm not sure. Glad it helped!
