__group__ ticket summary owner _component _version priority severity milestone type _status workflow _created modified _description _reporter Slated for Next Release 60096 Remove back-compat for database servers that don't support utf8mb4 johnbillion Database trunk normal normal 6.6 task (blessed) assigned has-patch 2023-12-18T19:45:03Z 2024-02-28T00:31:19Z Since [57173] the minimum supported version of MySQL is 5.5.5, which means the utf8mb4 charset is supported by all supported database servers. The back-compat code for handling servers that don't support it can therefore be removed, along with related tests and health checks. johnbillion Tickets Awaiting Review 43664 $wpdb->get_results fails in specific cases with non-latin charaters in where clause Database 4.9.4 normal normal Awaiting Review defect (bug) new 2018-03-30T08:37:47Z 2018-04-08T19:09:08Z "Let's say we have user with display name 'Алексей'; {{{$wpdb->get_results(""SELECT user_login FROM $wpdb->users where `display_name`='Алексей' "", ARRAY_A);}}} executes normally {{{$wpdb->get_results(""SELECT user_login AS 'russian person' FROM $wpdb->users where `display_name`='Алексей' "", ARRAY_A);}}} executes normally as well but {{{$wpdb->get_results(""SELECT user_login AS 'person from Russia' FROM $wpdb->users where `display_name`='Алексей' "", ARRAY_A);}}} returns empty array and results in error: [table Russia.doesn't exist] SHOW FULL COLUMNS FROM `Russia` That means 'from' in 'person from Russia' somehow gets in sql It's an obscure enough situation, but might signify that something is wrong with wpdb query handling" altert Tickets Awaiting Review 46923 $wpdb->get_var() — empty sting becomes a NULL Database 5.1.1 normal normal Awaiting Review defect (bug) new 2019-04-14T15:47:24Z 2019-04-15T17:07:42Z "Because of the following condition, we get `NULL`, even if a real field value is an empty string! {{{#!php __wakeup() #1 phar:///usr/share/wp-cli.phar/vendor/wp-cli/search-replace-command/src/WP_CLI/SearchReplacer.php(86): unserialize() #2 phar:///usr/share/wp-cli.phar/vendor/wp-cli/search-replace-command/src/WP_CLI/SearchReplacer.php(57): WP_CLI\SearchReplacer->run_recursively() #3 phar:///usr/share/wp-cli.phar/vendor/wp-cli/search-replace-command/src/Search_Replace_Command.php(580): WP_CLI\SearchReplacer->run() #4 phar:///usr/share/wp-cli.phar/vendor/wp-cli/search-replace-command/src/Search_Replace_Command.php(400): Search_Replace_Command->php_handle_col() #5 [internal function]: Search_Replace_Command->__invoke() #6 phar:///usr/share/wp-cli.phar/vendor/wp-cli/wp-cli/php/WP_CLI/Dispatcher/CommandFactory.php(100): call_user_func() #7 [internal function]: WP_CLI\Dispatcher\CommandFactory::WP_CLI\Dispatcher\{closure}() #8 phar:///usr/share/wp-cli.phar/vendor/wp-cli/wp-cli/php/WP_CLI/Dispatcher/Subcommand.php(491): call_user_func() #9 phar:///usr/share/wp-cli.phar/vendor/wp-cli/wp-cli/php/WP_CLI/Runner.php(417): WP_CLI\Dispatcher\Subcommand->invoke() #10 phar:///usr/share/wp-cli.phar/vendor/wp-cli/wp-cli/php/WP_CLI/Runner.php(440): WP_CLI\Runner->run_command() #11 phar:///usr/share/wp-cli.phar/vendor/wp-cli/wp-cli/php/WP_CLI/Runner.php(1237): WP_CLI\Runner->run_command_and_exit() #12 phar:///usr/share/wp-cli.phar/vendor/wp-cli/wp-cli/php/WP_CLI/Bootstrap/LaunchRunner.php(28): WP_CLI\Runner->start() #13 phar:///usr/share/wp-cli.phar/vendor/wp-cli/wp-cli/php/bootstrap.php(78): WP_CLI\Bootstrap\LaunchRunner->process() #14 phar:///usr/share/wp-cli.phar/vendor/wp-cli/wp-cli/php/wp-cli.php(27): WP_CLI\bootstrap() #15 phar:///usr/share/wp-cli.phar/php/boot-phar.php(11): include('...') #16 /usr/share/wp-cli.phar(4): include('...') #17 {main} }}} More details - - WordPress version - 6.4.2 - WP CLI command - {{{wp search-replace foo bar --regex --skip-plugins --skip-themes}}} - The database needs to have a certain format of serialized data to reproduce this but I'm not exactly sure what that format is. " vikram6 Tickets Awaiting Review 60505 Column names in wpdb->insert aren't sanitized. Database 6.4.3 normal normal Awaiting Review defect (bug) new 2024-02-12T13:14:50Z 2024-02-12T21:40:45Z "Column names in wpdb->insert aren't sanitized, although the documentation says: ""Data to insert (in column => value pairs). Both $data columns and $data values should be “raw” (neither should be SQL escaped)."" Steps To Reproduce: {{{#!php insert(""wp_posts"", array(""id`) VALUES (sleep(1)); -- "" => 0)); }}} This will sleep one second. The WordPress security team has reviewed this and asked to open a public ticket. " kazet Tickets Awaiting Review 58559 Database error bug in wordpress current version Database normal normal Awaiting Review defect (bug) new 2023-06-16T13:10:15Z 2023-07-03T17:13:01Z "{{{ 2023-06-16 8:52:53 47 [Warning] Aborted connection 47 to db: 'wordpressdb' user: 'wpuser' host: 'localhost' (Got an error reading communication packets) 2023-06-16 8:52:53 46 [Warning] Aborted connection 46 to db: 'wordpressdb' user: 'wpuser' host: 'localhost' (Got an error writing communication packets) }}} I am constantly seeing read and write errors in database due to wordpress in the current version. I have been running wordpress for years. Upon checking the logs the read errors last query is {{{ SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND (wp_posts.post_author = 2) AND ((wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish'))) ORDER BY wp_posts.post_date DESC LIMIT 0, 32 }}} The write error last query is {{{ SELECT DISTINCT post_author FROM wp_posts WHERE post_type = 'post' AND post_status = 'publish' LIMIT 2 }}} Both queries execute fine manually. The problem is not related to timeouts or max allowed packets as both execute manually. I noticed they are both increasing the aborted clients value in mysql. There is also no quit sent at the end of the mysql connections. It seems to be a bug within wordpress. I have literally gone through everything else possible to solve this bug." zoddshop63 Tickets Awaiting Review 53958 Database: wp_postmeta - meta_key index Database 5.8 normal major Awaiting Review defect (bug) new 2021-08-19T09:38:58Z 2021-08-25T14:15:20Z "The index on the **wp_postmeta** table for the meta_key column is set to **VARCHAR(191)**, While the column is **VARCHAR(255)**. This has a huge performance impact on queries that use the wp_postmeta table. In testing i noticed a performance increase on large websites with millions of records of several 100% when removing the index size on the column. This seems like a very obvious improvement and the issue has surely been raised before? So if there is a reason this cannot be altered without breaking something else I would like to know what that is. If you require any more information, let me know." wishe Tickets Awaiting Review 57385 Disable foreign key checks when dropping tables inside wp_uninitialize_site() function johnjamesjacoby* Database normal normal Awaiting Review defect (bug) accepted dev-feedback 2022-12-24T15:01:09Z 2024-01-12T18:07:05Z "By default when removing the tables from a subsite, if the table has foreign key constraint it wont drop the table resulting in tables not being removed. so this line should be changed from this to {{{ foreach ( (array) $drop_tables as $table ) { $wpdb->query( ""DROP TABLE IF EXISTS `$table`"" ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared } }}} {{{#!php query('SET FOREIGN_KEY_CHECKS=0;'); foreach ( (array) $drop_tables as $table ) { $wpdb->query( ""DROP TABLE IF EXISTS `$table`"" ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared } $wpdb->query('SET FOREIGN_KEY_CHECKS=1;'); }}} or any other better alternative." naveen17797 Tickets Awaiting Review 60362 "Emojis cause ""Updating failed. Could not update post in the database."" error" Database normal normal Awaiting Review defect (bug) new has-patch 2024-01-28T01:59:48Z 2024-02-08T03:44:35Z "Hi guys I recently discovered what I think is a bug, 'confirmed' by the folks at Brainstorm Force. As of 26 January, I was suddenly unable to update certain posts and pages in WordPress. When I tried to save, I received the ""Updating failed"" error, but had made no backend or other changes to spontaneously create this error. I use Gutenberg. I stumbled across a GitHub thread about someone with a lightning emoji in a post with the same problem. With this in mind, I removed all emojis from the pages and posts that could not save and sure enough, all functionality was restored to normal after doing so. I verified this several times by re-introducing emoijis, which reintroduced the error. I've reported this to my host, Brainstorm Force (Astra theme devs) and now here. I hope it is of some assistance. Regards Mike liquidaudio.com.au" mikefitzman Tickets Awaiting Review 40953 Empty values are handled inconsistently between wpdb->get_results() and wpdb->get_col() Database 0.71 normal normal Awaiting Review defect (bug) new dev-feedback 2017-06-08T17:18:39Z 2017-06-08T17:51:19Z "As outlined and discussed yesterday in Slack [https://wordpress.slack.com/archives/C02RQBWTW/p1496821047585923 here], [https://wordpress.slack.com/archives/C02RQBWTW/p1496844209230845 here], and [https://wordpress.slack.com/archives/C02RQBWTW/p1496850305693715 here], `wpdb` treats empty stored values differently in the `get_results()` and `get_col()` methods. This is because of the use of `get_var()` inside of `get_col()`, which defaults to null for empty values. For example, let's say you're running a query like `SELECT rate FROM sometable` through `get_results()`. With the default parameters and empty values for the column, you'd get something like the following: {{{ array(2) { [0]=> object(stdClass)#1734 (1) { [""rate""]=> string(0) """" } [1]=> object(stdClass)#1735 (1) { [""rate""]=> string(0) """" } } }}} If you ran that same query through `get_col()`, you'd instead get an array of `null` values: {{{ array(4) { [0]=> NULL [1]=> NULL } }}} This seems oddly inconsistent. And writing tests for the workaround is annoying in that creates the need to understand the core workaround in the future. Now, this code goes all the way back to [112], so changing the default behavior is not even on the table. Some solutions brainstormed with @boonebgorges and @johnjamesjacoby include: * A global flag to check against, i.e. `wpdb_get_col_force_strings( true );` * A global flag in the form of a constant * A settable `wpdb`-level flag * A new argument for `get_col()` to selectively change the behavior. The global flag ideas are attractive because they cover the entire DB stack: whether you're using the abstraction layers like `get_posts()`, `WP_Query`, or any of the other query classes, it ''just works'' all the way down the line. The settable `wpdb` flag is attractive only if you're really working with direct queries like we are in our custom table query classes. The same goes for a new argument in `get_col()`, though both could be implemented higher up the stack in the form of arguments or filters. I think a good first step here would be to try to benchmark performance for all of the listed options, just to see what we're looking at. The global flag choices seem like they could be the least impactful. In the short term, our workaround for AffiliateWP will probably be to create a wrapper for `get_results()` that simply plucks the values out so we can maintain consistency, but I'm not a big fan of writing and maintaining core workarounds in perpetuity. Whichever way we go in core, this is something that we should probably address. Who knows how many workarounds there are currently in the wild to fix this." DrewAPicture Tickets Awaiting Review 55907 Failed to insert log entry: value too long Database 5.9.3 normal normal Awaiting Review defect (bug) new 2022-06-03T06:30:35Z 2022-06-03T06:30:35Z "Error log shows the following message: `Failed to insert log entry: WordPress database error: Processing the value for the following field failed: [COLUMN]. The supplied value may be too long or contains invalid data.` There's a chance this happens when the message contains an ampersand character (see [https://core.trac.wordpress.org/ticket/32315#comment:97 here]). Regardless, if a value cannot be inserted into the database, it should at least be written to the error log, as this provides context for an otherwise generic message. This should be the case with ANY log insert failure, not just long ones. Another helpful thing would be to output the call stack. In this particular case, the ""Failed to insert log entry"" doesn't seem to be written by WordPress core, and the absence of context makes it very difficult to isolate the cause. Seeing the field value and call stack would be of great help." galbaras Tickets Awaiting Review 52210 File URL gets saved even when upload fails Database 5.6 normal major Awaiting Review defect (bug) new 2021-01-03T17:12:42Z 2021-02-09T17:49:59Z "**Description** ''A File URL reference gets saved into the **wp_posts** table even when upload fails. '' I recognized the issue when I uploaded a attachment into my WordPress Site. Steps I performed: **1. Uploaded a file that was not allowed (e.g a file with .svg ext)** ''The upload failed because I didn't had the ALLOW_UNFILTERED_UPLOADS Constant set to true.'' **2. Changed the ALLOW_UNFILTERED_UPLOADS constant to true and re-uplaoded the file.** ''The file upload worked this time.'' **3. Tried to open the attachment** ''I realized that the URL was altert by a filename**-1**.ext.'' **Things I checked:** I checked the Changesets and found this Change Log: https://core.trac.wordpress.org/changeset/46822 **Conclusion** I assume there is a Bug which saves the upload attempt into regardless if its completed successfully or not. " kkroeger93 Tickets Awaiting Review 58286 "Growing database due to unnecessary transients containing ""g_url_details_response_""" Database 6.2 normal critical Awaiting Review defect (bug) new 2023-05-10T13:44:03Z 2023-05-11T21:04:39Z "I created 2 external links in the block editor. This created 2 different transients in the database in wp_options, that have a different dynamic part at the end: {{{ _site_transient_g_url_details_response_ab62baf1c770821f259d255001f7a9b9 _site_transient_g_url_details_response_f6ce245428f26d29c355a44ceb3b9bf3 }}} This transient contains ""g_url_details_response_"" and is produced by file class-wp-rest-url-details-controller.php The value of such created transient is entire html from URL to which I link. I only want to add some external link in gutenberg editor. Why on earth is necessary to parse the entire html from URL to which I link? What if I will have for example 20 external affiliate links on one page and I will have 1000 such pages. This will unnecessarily bloat the database with useless and unnecessary data! How can I prevent the creation of transients whose name part contains ""g_url_details_response_""? I tryed it using this filter: {{{ apply_filters( ""pre_set_site_transient_{$transient}"", mixed $value, string $transient ) }}} But it needs full transient name, which is created dynamically using this function {{{ private function build_cache_key_for_url( $url ) { return 'g_url_details_response_' . md5( $url ); } }}} from file class-wp-rest-url-details-controller.php So please remove these pointless transients from wordpress or give users a way to prevent them from being created! Thank you for any reply, david " davidki Tickets Awaiting Review 54836 "Huge error logs filled with ""WordPress database error Illegal mix of collations"" errors caused by spammers" Database normal critical Awaiting Review defect (bug) new 2022-01-16T19:12:41Z 2023-05-21T11:02:05Z "This has been going on for quite a long time. By that, it has now been years. Every few weeks to a month we have to delete our error log as it builds to become an incredibly huge file on the server. So far, there has been no resolution to this anywhere. We have tried the forums and so many other places which have recommended starting a ticket as clearly, we aren't alone in experiencing this. There has to be a way of preventing this. This causes huge stability issues and consumes a LOT of space on the host. Therefore, I feel this has earned the privilege of being considered ""critical"". I filed under ""database"" although this may be better under something else as it seems to be some kind of injection attack using search engines and fake links that try to load content into the blog via error or search results, etc. Here is (believe it or not) a very small sample of what the error logs hold every time... (see attachment, as this would be far too much to paste into the post)" jh20001 Tickets Awaiting Review 50789 Improve WPDB logic around information_schema Database 5.5 normal normal Awaiting Review defect (bug) new 2020-07-27T17:43:37Z 2021-06-11T16:29:40Z "WooCommerce uses a query on `information_schema.table_constraints` to determine whether it needs to add a foreign key constraint to one of its tables. {{{ SELECT COUNT(*) AS fk_count FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = '' AND CONSTRAINT_NAME = 'fk_wp_12345_wc_download_log_permission_id' AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = 'wp_12345_wc_download_log' }}} `$wpdb->get_table_from_query` returns `information_schema` which is not a table, it is a database containing the table `TABLE_CONSTRAINTS`. So it seems there is room to improve this method to extract the table name in cases where it is preceded by a database name. However, this is not the goal of this ticket. In the specific case of `information_schema` tables, we're more interested in the table referenced in the `WHERE` clause. My use case involves a `wpdb` drop-in, WordPress.com's hyperdb, which was the original source of this method when it was added in [30345]. We use hyperdb to map queries to database servers using table names as map keys. Given a table like `wp_12345_posts` we connect to the right database. The database `information_schema` exists in all database servers. When WooCommerce queries the table `table_constraints` it's looking for information about the table `wp_12345_wc_download_log`. To route the query to the appropriate database server, we are interested in this table name. I would propose adding this before the first `preg_match` in `get_table_from_query`: {{{ // SELECT FROM information_schema.* WHERE TABLE_NAME = 'wp_12345_foo' if ( preg_match('/^\s*' . 'SELECT.*?\s+FROM\s+`?information_schema`?\.' . '.*\s+TABLE_NAME\s*=\s*[""\']([\w-]+)[""\']/is', $q, $maybe) ) return $maybe[1]; }}} This returns `wp_12345_wc_download_log` which allows us to route the query to the correct database server. I am able to patch WordPress.com's drop-in to check this pattern before calling the parent method in core's `wpdb`. So there is no need to rush on our behalf. Does anyone know of a use case that relies on the existing implementation? It might be argued that a caller of this function would expect the return to be `TABLE_CONSTRAINTS` in this case. However, I was unable to find any tickets requesting a fix for the current behavior. I believe the best fix would be to return the table name from the `WHERE` clause." andy Tickets Awaiting Review 56260 In case of missing primary key on wp_posts, Media uploader gives false feedback indicating folder permissions are missing, where in fact database structure is malformed. Database normal minor Awaiting Review defect (bug) new 2022-07-20T15:59:51Z 2022-10-26T15:43:29Z "Hi community, Files won't upload to the media library and a misleading feedback is presented to the user (indicating there is an uploads/months/day permissions issue), where in fact the file is written successfully to disk – but WP can't write to the wp_posts database. I stumbled upon this as part of #56259 whereby wp_posts missing primary key + A.I. attributes are not picked up by WordPress (even in its repair.php functionality). The same happens with failed media uploads. The UI reports the failure to write to a folder. Whereas the AJAX response is an unhandled full-HTML page. The UI error is a mismatch from the server response. Steps to reproduce:— • Break AutoIncrement and Primary Key for wp_posts (can happen through user misfortune or third-party plugin or failed migration). • Upload media • Failure notice reports disk permissions failure, whereas the issue is in fact the database structure issue. Forgive haste on this write-up." harryfear Tickets Awaiting Review 44973 Issues with $wpdb->get_col_info Database 4.9.8 normal normal Awaiting Review defect (bug) new has-patch 2018-09-20T21:37:49Z 2018-09-23T14:50:24Z "When attempting to get certain column information using $wpdb->get_col_info I get the following error notice: Notice: Undefined property: stdClass::$primary_key in ..../wp-includes/wp-db.php on line 3211 Similar for 'not_null' and several of the other options. Name, type, etc. work just fine. It looks like not all the properties are set for all the options listed in the docs - https://codex.wordpress.org/Class_Reference/wpdb#Getting_Column_Information" drocks13 Tickets Awaiting Review 59974 MAX_JOIN_SIZE wp-blog-header.php Database 6.3.2 normal minor Awaiting Review defect (bug) new 2023-11-27T21:24:05Z 2023-11-27T21:24:05Z "seeing this error in the error logs for a site with over 100k posts. seems to be non-critical. {{{ [27-Nov-2023 15:42:16 America/New_York] WordPress database error The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay for query SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) WHERE 1=1 AND ( wp_term_relationships.term_taxonomy_id IN (2,197,213,214,215,216,217,218,219,220,225,226,227,228,229) AND wp_posts.ID NOT IN ( SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id IN (31926,31927,31928,31929,31930,31931,31932) ) ) AND ((wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish'))) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 24 made by require('/wordpress-versions/6.3.2/wp-blog-header.php'), wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts [27-Nov-2023 15:43:10 America/New_York] WordPress database error The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay for query SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) WHERE 1=1 AND ( wp_term_relationships.term_taxonomy_id IN (3,221,222,231,232,233,234,235,236,237,239,241,243,244,245,246,247,248,249,250,273,276,277,281,282,287,397,398) AND wp_posts.ID NOT IN ( SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id IN (31926,31927,31928,31929,31930,31931,31932) ) ) AND ((wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish'))) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 24 made by require('/wordpress-versions/6.3.2/wp-blog-header.php'), wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts }}} " wkeithgardner Tickets Awaiting Review 60503 MySQL VALUES function deprecated in MySQL 8 Database normal normal Awaiting Review defect (bug) new 2024-02-12T12:19:23Z 2024-02-12T22:19:14Z "e.g. for add_option SQL query (but issue happens in other places too, but not many) {{{#!php ""INSERT INTO `$wpdb->options` (`option_name`, `option_value`, `autoload`) VALUES (%s, %s, %s) ON DUPLICATE KEY UPDATE `option_name` = VALUES(`option_name`), `option_value` = VALUES(`option_value`), `autoload` = VALUES(`autoload`)"" }}} we get the SQL logs spammed with: >'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead Since WP 6.4 minimum SQL is 8+, so this deprecated syntax isn't necessary anymore, since the new syntax is supported in all supported SQL versions. This should have been fixed in WP 6.4, but at least now with WP 6.5 release, as it's easy to fix and avoid having a performance penalty from the deprecation notice handling/reporting. " kkmuffme Tickets Awaiting Review 54834 Mysql gone away when importing DB on create wp_posts indexes Database normal blocker Awaiting Review defect (bug) new 2022-01-16T13:10:48Z 2023-02-17T00:10:51Z "Already imported the table and its data. When I execute the following: ALTER TABLE `wp_posts` ADD PRIMARY KEY (`ID`), ADD KEY `post_name` (`post_name`(191)), ADD KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`), ADD KEY `post_parent` (`post_parent`), ADD KEY `post_author` (`post_author`); I have 2013 error (in console) or 2006 (in phpmyadmin) immediately." jasmines Tickets Awaiting Review 58965 No luck running WP when running PHP 8.2.8 + mysql Ver 15.1 Database 6.2.2 normal normal Awaiting Review defect (bug) new 2023-08-02T22:29:45Z 2023-08-02T23:00:52Z "Welp, cannot figure out how to get the latest version of WP installed...when I load it I get this error message: Your PHP installation appears to be missing the MySQL extension which is required by WordPress. Please check that the mysqli PHP extension is installed and enabled. I am using php-fpm and can see I have enabled: mysqlnd, pdo, pdo_mysql Obv can't run mysqli anymore...so I am not even sure why I am seeing this mysqli error? Anyone know how to work around it? Hopefully, it's just something dumb on my part but I am a bit restricted on the server this is running. " boyz2men Tickets Awaiting Review 49482 Non-english slug can sometimes cause an error in WordPress query Database 5.3.2 normal normal Awaiting Review defect (bug) new 2020-02-20T08:49:16Z 2020-02-20T11:00:26Z "The error is {{{ Warning: vsprintf(): Too few arguments in wp-includes/wp-db.php on line 1397 }}} I found the root cause that if the encoded slug contains the {{{%f}}} or {{{%[0-9]f}}}. it will be converted to capital F (wp-db.php:1359) and will not be properly escaped (wp-db.php:1361) I found a ticket here #49140 but it has been closed." panjmp Tickets Awaiting Review 57283 Postmeta - Performance Improvement - Woocommerce Database 6.1.1 normal critical Awaiting Review defect (bug) new 2022-12-06T13:16:30Z 2022-12-06T14:42:36Z "Hi Team, Struggling since almost a month to find the solution for slow search in woocoomerce. Tried verious different things like speed up index mysql. Changing mysql version from 5.7 to 8.x. Updated index - added high performance keys for post meta table. When backend team search for orders from woocommerce-orders tab - it takes almost 3 min to get the results. Looking for inputs from wordpress community and help for the same." kapil4sam Tickets Awaiting Review 53033 Remove comments, ping, to_ping columns from posts table Database normal normal Awaiting Review defect (bug) new 2021-04-14T22:10:47Z 2021-04-15T11:36:50Z The wp posts table still holds old columns that do not fit the future. The post table should be cleaned, the pings and comments features moved to meta and child custom post types. cacaflow Tickets Awaiting Review 41278 SQL_CALC_FOUND_ROWS slow query Database 4.8 normal normal Awaiting Review defect (bug) new 2017-07-09T17:12:10Z 2017-07-19T18:41:46Z "In the logs of slow queries when searching for steel, the following types of logs appear: {{{ SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND (((wp_posts.post_title LIKE '%donald%') OR (wp_posts.post_excerpt LIKE '%donald%') OR (wp_posts.post_content LIKE ‘%donald%’)) AND ((wp_posts.post_title LIKE '%trump%') OR (wp_posts.post_excerpt LIKE ‘%trump%’) OR (wp_posts.post_content LIKE '%trump%'))) AND wp_posts.post_type IN ('post', 'page', 'attachment') AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled' OR wp_posts.post_author = 186 AND wp_posts.post_status = 'private') ORDER BY (CASE WHEN wp_posts.post_title LIKE ‘%donald trump%' THEN 1 WHEN wp_posts.post_title LIKE ‘%donald%’ AND wp_posts.post_title LIKE '%trump%' THEN 2 WHEN wp_posts.post_title LIKE '%donald%' OR wp_posts.post_title LIKE '%trump%' THEN 3 WHEN wp_posts.post_excerpt LIKE '%donald trump%' THEN 4 WHEN wp_posts.post_content LIKE '%donald trump%' THEN 5 ELSE 6 END), wp_posts.post_date DESC LIMIT 0, 30; }}} How can I eliminate it?" lubimow Tickets Awaiting Review 51486 The add_option function should not be able to update existing rows in the database. Database 2.9 normal normal Awaiting Review defect (bug) new needs-unit-tests 2020-10-09T01:12:29Z 2022-06-09T12:18:11Z "In certain edge cases, `add_option` is able to update existing option values. This should not be possible. If SQL is executed which instructs ""Insert XYZ"" into database, then the insert should fail if the key already exists in the database, unless the SQL specifies that an UPDATE should happen. The `add_option` function does check first to see if an option exists before attempting to add it to the database. In the overwhelming majority of cases, this works fine because when the option exists the function will return early before attempting to insert into the database. However, consider a scenario where an object cache is being used. And further consider that the object cache may report incorrectly. If the object cache tells `add_option` that the value doesn't exist in the database (but in reality it does!), then `add_option` continues on and attempts to insert the supposedly new option. In that particular circumstance, the SQL query, shown below, is executed which will insert the new row. And in the event that the option already exists, it will be overwritten. The issue here is that `add_option` shouldn't be able to update the existing value. The query should fail. Why is the `ON DUPLICATE KEY UPDATE` clause included here? {{{#!php query( $wpdb->prepare( ""INSERT INTO `$wpdb->options` (`option_name`, `option_value`, `autoload`) VALUES (%s, %s, %s) ON DUPLICATE KEY UPDATE `option_name` = VALUES(`option_name`), `option_value` = VALUES(`option_value`), `autoload` = VALUES(`autoload`) "", $option, $serialized_value, $autoload ) ); }}} I realize this is an edge case. If the object cache being used gives bad information to `add_option` then really its the object caching plugin at fault. However, I can't understand a possible circumstance where `add_option` should ever UPDATE an existing option. The SQL here should not include `ON DUPLICATE KEY UPDATE`. Am I missing something? Is there a good reason for that clause? What would happen if that clause were removed?" khag7 Tickets Awaiting Review 39273 Updating to 4.7 can break serialized data because $wpdb->determine_charset now forces utf8 when DB_CHARSET is set to utf8mb4 Database 4.7 normal normal Awaiting Review defect (bug) new dev-feedback 2016-12-14T05:05:58Z 2019-03-15T02:23:09Z "I've seen this happen several times now. `DB_CHARSET` is defined as `utf8mb4` and the columns in the database are set to `utf8mb4_unicode_ci`. However, the changes in 38581 are now forcing `utf8` causing the serialized array lengths to change when the data is queried and breaking them because the charsets don't match. I can't say for certain because I'm debugging this issue on a site that isn't mine, but I don't think anyone explicitly set `DB_CHARSET` to `utf8mb4` in `wp-config.php`. It appears that at one point, WordPress set that define and created (or updated) those tables to `utf8mb4_unicode_ci`. Deleting the `DB_CHARSET` define fixes the issue but that doesn't seem like an ideal solution for users who update and end up with a broken site. I'll admit this is a bit over my head, so I'm hoping someone smarter than me might be able to chime in with some more info :) I'm definitely available to keep the conversation going. Thanks! " justinbusa Tickets Awaiting Review 30795 Upgrade bug affecting indexing Database 2.1 normal normal Awaiting Review defect (bug) reopened 2014-12-20T16:29:49Z 2019-03-22T13:46:01Z "Every time WordPress is updated I was getting an entry in my error_log as follows: WordPress database error Duplicate key name 'type_status_date' for query ALTER TABLE wp_posts ADD KEY type_status_date (post_type,post_status,post_date,ID) made by wp_upgrade, make_db_current_silent, dbDelta After some heavy back tracking through the code, this is coming from a mismatch between the reported indexing of my database and what WordPress is expecting. in particular to the 'post_status' part. It seems WordPress expects the 'SubPart to be NULL, only in my main site is was '1'. So, my index was reporting as: KEY type_status_date (post_type,post_status(1),post_date,ID) Rather than: KEY type_status_date (post_type,post_status,post_date,ID) This means the upgrade code is called on every update and fails on every update also. I've managed to force through a fixed on my own site by dropping the index and recreating it using SQL in PHPMyAdmin. Googling for the above error message it would seem I'm not the only one affected." MattyRob Tickets Awaiting Review 59836 Using the pre_get_table_charset filter ensures wpdb->col_meta never gets populated Database 6.3.3 normal normal Awaiting Review defect (bug) new 2023-11-08T14:04:37Z 2023-11-08T19:20:09Z "I have a non-core table with a binary column that I use for selects and deletes (it's an efficient way to store UUIDs). I've noticed that whenever I attempt to do select from it, WP does a relatively expensive SHOW FULL COLUMNS FROM query, which seems to be uncachable. I've been attempting to use the pre_get_table_charset filter to get around this, but this then caused problems when get_col_charset() runs in the same call. It seems that inside get_table_charset() there is the line $this->col_meta[ $tablekey ] = $columns; which appears to be the only place that col_meta is ever populated. But it wont be populated for that table if you use the hook, so if something like this then runs: {{{#!php // If still no column information, return the table charset. if ( empty( $this->col_meta[ $tablekey ] ) ) { return $this->table_charset[ $tablekey ]; } }}} as it does in get_col_charset() then things just break. col_meta for that table is not populated and neither is table_charset. I'm too stupid to work out the best way to fix this I'm afraid. " c0ntax Tickets Awaiting Review 43589 WPDB Update change NULL values to acceptable values Database normal normal Awaiting Review defect (bug) new has-patch 2018-03-20T19:43:32Z 2019-01-17T01:19:44Z "Hello, I'm creating a plugin in which i need to create a new SQL table, that I connect to REST API. My table has several fields that can't be NULL and some that can be NULL. If i do the update via the wpdb object (cf. below), NULL fields are updated with a NOT NULL value that I've never typed (a float that can't be NULL become 0.00000, a date -0001-00-00, ....). The expected behaviour would be to not update the line because the request is not OK. If I allow NULL values on these fields, values are NULL. It's like if somewhere the value was changed to be accepted by the sql engine. NB: the generated request is OK, when i access to the wpdb->last_query value. --------- code example --------- global $wpdb; $wpdb->update($table, $fields, $where, $fieldsformats, $whereformats); where $fields = array(""mynotnulldate""=> null, ""mynotnullfloat"" => null); Dorian " dorianrd Tickets Awaiting Review 53320 Why delete_metadata and update_metadata slow performance? Database normal normal Awaiting Review defect (bug) new 2021-06-02T11:11:06Z 2021-06-02T11:11:06Z "I’ve been doing some investigating on the overall performance of a local WordPress with WooCommerce build and something doesn’t seem to be making sense, I’m not sure if I’m missing something or if it’s an oversight in the development. WooCommerce has ways of checking if meta keys and values need to be updated, for example update_post_meta in class-wc-order-data-store-cpt.php The plugin also has a method update_or_delete_post_meta for handling the meta of posts integration with the WordPress Core. Here’s where the confusion comes in, 1. [delete_metadata](https://github.com/WordPress/WordPress/blob/21cf92796123e98bcc9cc7981d80472977673fab/wp-includes/meta.php#L335) Why does delete_metadata by default query to see if it exists before running the DELETE operation? I understand the DELETE operation is expensive but in this instance, we know the keys that are going to be removed. I understand we could hook into the delete_{$meta_type}_metadata filter, return false and run the query ourselves but this doesn’t future proof things. I propose an additional filter and/or argument that allows you to bypass the initial lookup. 2. [update_metadata](https://github.com/WordPress/WordPress/blob/21cf92796123e98bcc9cc7981d80472977673fab/wp-includes/meta.php#L159) There is a similar problem here too. [Every update queries the database](https://github.com/WordPress/WordPress/blob/21cf92796123e98bcc9cc7981d80472977673fab/wp-includes/meta.php#L220) to see if a row already exists and if so, then use the add_metadata. If we know the meta key 100% exists, why go to the expense of then querying the database again? Once again, I appreciate this is added as a fail-safe to ensure the data gets stored but this leaves the developer with no “official” way of updating the meta without having to do a lookup first. Again, we can hook into update_{$meta_type}_metadata but this has the same problem as before.. breaking future changes. I propose the same as above for this method too. Has anybody else experienced this or found any solutions to excessive SQL lookups (other than relying on cache)?" cjj25 Tickets Awaiting Review 57072 WordPress 6.1 uses wrong database collation Database 6.1 normal normal Awaiting Review defect (bug) new 2022-11-11T10:35:00Z 2022-11-11T10:35:00Z "After upgrade to WordPress 6.1 some plugins started reporting ""WordPress database error Illegal mix of collations (utf8mb4_unicode_520_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT)"". See: https://wordpress.org/support/topic/database-error-after-upgrade-to-wordpress-6-1/ It seems that WordPress 6.1 wrongly detects database collation. This is probably related to #54841. Database section of the Site Health page (/wp-admin/site-health.php?tab=debug) displays: {{{ Database charset utf8mb4 Database collation utf8mb4_unicode_520_ci }}} Eariler versions displayed correct one: {{{ Database charset utf8mb4 Database collation utf8mb4_unicode_ci }}} Database collation should be utf8mb4_unicode_ci per server and database configuration: {{{ MariaDB [(none)]> SHOW VARIABLES LIKE '%_server' ; +----------------------+--------------------+ | Variable_name | Value | +----------------------+--------------------+ | character_set_server | utf8mb4 | | collation_server | utf8mb4_unicode_ci | +----------------------+--------------------+ 2 rows in set (0.002 sec) MariaDB [(none)]> USE wordpress; Database changed MariaDB [wordpress]> SELECT @@character_set_database, @@collation_database; +--------------------------+----------------------+ | @@character_set_database | @@collation_database | +--------------------------+----------------------+ | utf8mb4 | utf8mb4_unicode_ci | +--------------------------+----------------------+ 1 row in set (0.000 sec) }}} Server version: 10.6.10-MariaDB-log Having {{{define('DB_COLLATE', '');}}} in wp-config.php. Using {{{define( 'DB_COLLATE', 'utf8mb4_unicode_ci' );}}} did not fix the issue, nor changed collation information in Database section of the Site Health page." Krstarica Tickets Awaiting Review 58133 WordPress database error Illegal mix of collations Database 6.1.1 normal major Awaiting Review defect (bug) reopened 2023-04-15T00:27:53Z 2023-04-24T14:31:43Z "Please see attached WordPress database error. I have run query in local database to check if those post exists in wp_posts table. But they do not exist anywhere in database, site-content and codebase. Site Info PHP version 8.0.25 (Supports 64bit values) Extension mysqli Server version 5.7.12 Database charset utf8mb4 Database collation utf8mb4_unicode_520_ci " varinupadhyay Tickets Awaiting Review 41302 WordPress not stopping queries which have reached limit and preventing users to load WordPress until query times out Database 4.8 normal normal Awaiting Review defect (bug) new 2017-07-13T07:28:09Z 2017-07-14T07:58:38Z "[[Image(Pasted image at 2017_07_13 10_14 AM.png)]] I have problem with WordPress mysql `max_questions=600, max_updates=600` limit {{{ User 'id1561843_wp_ac6601cd835d82e22d89d11a3ea228a0' has exceeded the 'max_queries_per_hour' resource (current value: 500) }}} After {{{ wp-db.php:830 }}} comes timeout. I suspect that that after limit is reached {{{ public function set_charset( $dbh, $charset = null, $collate = null ) { }}} throws and error. After that WordPress gets stuck on loading something. And you cannot make any other queries on other browser windows. Proper script should just kill the query after error that limit has been exceeded." juslintek Tickets Awaiting Review 42381 Wordpress update does not check if database structure/scheme on existing site is equal to how it would be on a new install Database 4.8.2 normal normal Awaiting Review defect (bug) new dev-feedback 2017-10-30T18:19:44Z 2017-11-11T18:19:31Z "'''Description of bug''' When trying to add a category I receive the error: WordPress database error: [Duplicate entry ‘test’ for key ‘slug’] INSERT INTO wp_terms (name, slug, term_group) VALUES (‘Test’, ‘test’, 0) '''What seems to be the cause of the problem?''' My install does not allow a category (test) with the same slug as an existing tag (test). WordPress should allow this. On further investigation: in wp_terms table, the field slug has a UNIQUE constraint. This was changed in WordPress 4.1 [https://core.trac.wordpress.org/ticket/22023/ three years ago]. Duplicates are now prevented in WordPress code instead of in the database, but it seems like my site has skipped one or more database core updates. '''In short''' My install is up to date. But my database core structure/scheme is not up to date. wp_repair, wp_optimize etc. do not flag this. Also setting WP_ALLOW_REPAIR in wp-config.php does not flag this as an issue. I was able to fix this but potentially my database still has other undetected differences. Questions - Should WordPress check on update if a existing database structure/scheme matches how it should be if it were new install? - Should WordPress offer (after backup disclaimer etc.) offer to repair/update the database structure to the latest version? I submit this as a bug and not as a feature since I feel WP_ALLOW_REPAIR should detect if a WordPress table is setup correctly." mike_vl Tickets Awaiting Review 49785 Wrong result / duplicates in query when using MariaDB Database 5.4 normal normal Awaiting Review defect (bug) new 2020-04-02T20:57:31Z 2020-04-03T11:45:56Z "The page parent selector uses a rest api endpoint that results in the following query: First 100 pages: {{{ SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.ID NOT IN (51915) AND wp_posts.post_parent NOT IN (51915) AND wp_posts.post_type = 'page' AND ((wp_posts.post_status = 'publish')) ORDER BY wp_posts.menu_order ASC LIMIT 0,100 }}} Next group of 100 pages: {{{ SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.ID NOT IN (51915) AND wp_posts.post_parent NOT IN (51915) AND wp_posts.post_type = 'page' AND ((wp_posts.post_status = 'publish')) ORDER BY wp_posts.menu_order ASC LIMIT 100,100 }}} etc.. In a site we've got a total of 128 pages that match this query. Resulting total is correct, however, there is 17 duplicates. This means that we're missing pages in the resulting page parent select input, and instead we have duplicates. Running query directly in database gives the same, incorrect, result. Replicating query as wp_query gives the same, incorrect, result. I've tested this with no plugins activated, twentytwenty theme. Same incorrect result. MySQL 5.5.5 - 10.1.44-MariaDB Ubuntu 18.04.1 WordPress 5.4 TwentyTwenty 1.2 I'm also getting one duplicate in my local duplicate (local by flywheel 3.3.0) using built in MySQL 5.7.23" Kim.Bertelsen Tickets Awaiting Review 59481 dbDelta do useless request when type case is not the same between query and describe result Database 6.3 normal normal Awaiting Review defect (bug) new has-patch 2023-09-27T22:17:19Z 2023-10-17T22:40:55Z "While debugging why a call to `dbDelta` was not working, I discovered in the function output (an array of performed changes) that it was doing some useless changes: ""Changed type of wp_table.field from varchar(255) to VARCHAR(255)"". Indeed, on my MySQL 8.0.31 Windows x64 version (installed by Wampserver), if I do ""DESCRIBE wp_table"", the ""Type"" column of the result is always lowercase. If somehow, you wrote your ""CREATE TABLE"" statements with a different case for varchar columns, dbDelta will detect a change. It does it only for varchar. If I change my varchar to be lowercase in the SQL I use for dbDelta, it doesn't try to change the type. {{{#!php prefix.'table` ( `object_id` INT(10) NOT NULL, `group_term_slug` VARCHAR(255) NOT NULL ) '.$wpdb->get_charset_collate().';'; $result = dbDelta($sql); // Will contain both ""Created table wp_table"" and ""Changed type of wp_table.field from varchar(255) to VARCHAR(255)"" $sql = 'CREATE TABLE `'.$wpdb->prefix.'table` ( `object_id` INT(10) NOT NULL, `group_term_slug` varchar(255) NOT NULL ) '.$wpdb->get_charset_collate().';'; $result = dbDelta($sql); // Will NOT contain ""Changed type of wp_table.field from varchar(255) to VARCHAR(255)"", only ""Created table wp_table"" }}} " tristanleboss Tickets Awaiting Review 46932 dbDelta does not account for MySQL keywords being used as key names Database normal minor Awaiting Review defect (bug) new 2019-04-15T12:14:29Z 2019-04-16T00:46:51Z "This, is a bug, but it is an edge case. I realize that. In processing data coming from a third party, my plugin sets up a table in MySQL that uses the field name `key`. MySQL allows this because it is wrapped in back-ticks. The field does not cause a problem in the WordPress code, but I also index on that field and that does cause a problem. {{{ KEY `i_key` (`key`) }}} This causes a small problem with `dbDelta()`. The regex starting at line 2650 will not properly identify the index. Thus, the next line, 2676, will try to use the array element `$index_type['index_type']` which does not exist. This throws a warning in PHP. There are two possible solutions I see. 1. The simple solution is to add a check for an empty array above 2767. This won't solve the problem but it will stop the warning. 2. The more complete solution would be to change the regex to exclude the keyword matches if they are inside of backticks. This will solve the problem properly. There may be other solutions, those are just the ones I have identified. Cheers! :) =C= " CalEvans Tickets Awaiting Review 46452 dbDelta given the same PRIMARY KEY and normal KEY produces no error, reports table created successfully, but the table is not created Database 5.1 normal trivial Awaiting Review defect (bug) new 2019-03-09T00:07:34Z 2019-03-09T00:07:34Z "I spent about 45 minutes trying to find out why one of several dbDelta functions was not creating a table. It wasn't giving a warning, and in fact was saying ""Created table wp_dtl_exam_resultmeta"". But the table was not created. I eventually found the problem which was that I had a key called ""meta_id"" and ""answer_id"" and simply had put the wrong primary key, which should have been meta_id. But I had: PRIMARY KEY (answer_id), KEY answer_id (answer_id) This gives a success message without creating a table - which doesn't seem right. Of course this isn't a problem with WordPress, it is a problem with my code. But it seems a check is missing from dbDelta, which is what I want to report. Here is the full code to reproduce the problem, just put it in a plugin or functions.php: {{{#!php wp_dtl_exam_results * -> wp_dtl_exam_resultmeta */ function example_database_creation_for_wordpress_trac() { global $wpdb; require_once( ABSPATH . 'wp-admin/includes/upgrade.php' ); $charset_collate = $wpdb->get_charset_collate(); $table_name = $wpdb->prefix . 'dtl_exam_resultmeta'; // MY ERROR IS: PRIMARY KEY (answer_id), // IT SHOULD BE: PRIMARY KEY (meta_id), // But the dbDelta function doesn't notice that, and says it created a table when it didn't. $sql = <<'; echo 'The table creation SQL:' . ""\n\n""; echo $sql; echo ""\n -- \n""; // This should give some warning or at least say table creation failed. It doesn't. It says the table was created. echo 'dbDelta results: ' . ""\n\n""; if ( $result ) foreach( $result as $m ) echo $m . ""\n""; else echo '(no results from dbDelta)'; echo ""\n -- \n""; // This should show the table name by checking information_schema, but it's an empty array echo 'Table query from INFORMATION_SCHEMA, this should show the table name: ' . ""\n\n""; $table_exists = $wpdb->get_results( ""SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '"". DB_NAME .""' AND TABLE_NAME = '"". $table_name .""';"" ); var_dump( $table_exists ); echo ''; exit; } example_database_creation_for_wordpress_trac(); }}} " radgh Tickets Awaiting Review 46146 dbDelta not parsing enum correctly Database normal normal Awaiting Review defect (bug) new 2019-01-30T08:41:12Z 2022-11-17T19:28:49Z "Given an enum definition such as: {{{ enum('a', 'b') }}} The dbDelta function parses it as: {{{ enum('a', }}} Which causes a change to be detected on every execution. I'm also afraid other cases with spaces (like strings with spaces in enum values, etc.) will cause similar problem since the regular expressions in the dbDelta function seem to cover only some particular cases." janjakes Tickets Awaiting Review 46145 dbDelta runs same updates on every execution Database normal normal Awaiting Review defect (bug) new 2019-01-30T08:37:30Z 2019-01-30T08:37:30Z "Given a SQL column with a definition such as: {{{ created_at TIMESTAMP NULL }}} The dbDelta detects the following changes: {{{ Changed type of table_name.created_at from timestamp to TIMESTAMP }}} But then executes a query using part of the original SQL: {{{ ALTER TABLE table_name CHANGE COLUMN `created_at` created_at TIMESTAMP NULL }}} Which leads to the same changes being detected again on the next run. This applies not only to case of the type but to many other cases such as ""int"" vs. ""int(11)"", ""bigint"" vs. ""bigint(20)"", etc. This will actually happen in every case when the given SQL differs from the exact ""expected"" form. " janjakes Tickets Awaiting Review 36924 dbDelta(): Support more than one whitespace between field name and its type definition Database 4.5.2 normal normal Awaiting Review defect (bug) new 2016-05-23T22:42:46Z 2018-08-10T00:39:26Z "dbDelta() fails to remove multiple spaces between field name and field type definition in ALTER / CREATE statements. In result some table definitions may lead to constant ALTER statements to be executed which may easily crash MySQL server. Compare: == Correct {{{#!php $sql = ""CREATE TABLE some_table ( id bigint(20) NOT NULL KEY AUTO_INCREMENT, test varchar(100) NOT NULL, );""; require_once( ABSPATH . 'wp-admin/includes/upgrade.php' ); dbDelta($sql); }}} vs == Wrong, due to multiple spaces between 'test' and 'varchar' ALTER query is executed {{{#!php $sql = ""CREATE TABLE some_table ( id bigint(20) NOT NULL KEY AUTO_INCREMENT, test varchar(100) NOT NULL, );""; require_once( ABSPATH . 'wp-admin/includes/upgrade.php' ); dbDelta($sql); }}} == Possible fix: - in dbDelta() call trim() on $tablefield->Type, - or remove extra spaces during preg_match: replace: {{{#!php preg_match(""|"".$tablefield->Field."" ([^ ]*( unsigned)?)|i"", $cfields[strtolower($tablefield->Field)], $matches); }}} with: {{{#!php preg_match(""|"".$tablefield->Field.""\s+([^ ]*( unsigned)?)|i"", $cfields[strtolower($tablefield->Field)], $matches); }}}" matt_fw Tickets Awaiting Review 57149 get_table_from_query() doesn't properly handle a prepared escape_like() table name Database normal normal Awaiting Review defect (bug) new 2022-11-18T15:15:43Z 2023-01-04T19:33:26Z "A common way to check if a table exists is to run a query like the following, the same way that `maybe_create_table()` currently works: {{{#!php prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table_name ) ); }}} This results in the prepared query for a table name such as `wp_my_custom_table` to end up being: {{{ SHOW TABLES LIKE 'wp\\_my\\_custom\\_table' }}} While the above isn't necessarily a proper query, it is still valid. The problem is that `$wpdb->get_table_from_query()` ends up returning the table name with extra slashes, e.g. `wp\_my\_custom\_table`. This specifically breaks HyperDB which is attempting to use that table name to determine the dataset to use." prettyboymp Tickets Awaiting Review 51350 mysql nosql feature request Database normal normal Awaiting Review defect (bug) new 2020-09-18T16:51:58Z 2023-04-20T12:51:47Z WordPress cover 37% of the web, with plugins sites go heavy and NoSQL is best in performance and nowadays all hosting platform supports MySQL 8 if it upgrades it will be great. nabi009 Tickets Awaiting Review 46739 post attributes order not working correctly Database 5.1 normal normal Awaiting Review defect (bug) new 2019-03-30T18:28:38Z 2019-03-30T18:28:38Z "I set up the post attributes order with the code attached to be modified within the editor. add_post_type_support('post', array('page-attributes')); The post attributes section with the order field shows up but unfortunately does not save any values and does not show up the value set in the database. Please find more information here: https://stackoverflow.com/questions/55433707/wordpress-post-custom-order-not-working-showing-up-but-not-saving" nikischin Tickets Awaiting Review 41469 test_charset_switched_to_utf8() is not tested on Travis CI Database 4.7.1 normal normal Awaiting Review defect (bug) new 2017-07-28T00:26:11Z 2017-07-28T02:09:13Z "The `Tests_DB::test_charset_switched_to_utf8()` test (introduced in #37982) is skipped on Travis, because the test gets skipped when the environment's database driver supports utf8mb4. This makes the test useless. What sort of environment does not support utf8mb4? Presumably an older MySQL version. We should look into adding a build to Travis with a specific environment that allows this test to run. Related: #30462" johnbillion Tickets Awaiting Review 46625 username and password automatically change to dexter Database 4.9.1 normal normal Awaiting Review defect (bug) new 2019-03-24T07:32:46Z 2019-03-24T10:18:13Z "Dear sir, username and password automatically change to dexter " ram181 Tickets Awaiting Review 57000 wp_usermeta table is emptied because of a wrong query being executed Database 6.0.3 normal normal Awaiting Review defect (bug) new 2022-11-04T12:49:28Z 2022-11-04T12:49:28Z "On one website we encounter a very strange error. This SQL query is executed by the `delete_meta` function: {{{ DELETE FROM wp_usermeta WHERE umeta_id IN( umeta_id,user_id,meta_key,meta_value ) }}} This query empties the wp_usermeta table completely. We have tried to reproduce the error in a local development environment, but it is very hard to do so. In the `delete_meta` function the `\wpdb::check_safe_collation` and `\wpdb::get_table_charset` methods are called, in which the query `SHOW FULL COLUMNS FROM wp_usermeta` is executed. Apparently, in some occasions the next query fails, but the `\wpdb::$last_result` is not flushed, which then still contains the result of the `SHOW FULL COLUMNS FROM wp_usermeta` query (the column names). The result is returned to the `$meta_ids` variable in the `delete_metadata` and is then used in the delete query. The back trace of the function call is: shutdown_action_hook, do_action('shutdown'), WP_Hook->do_action, WP_Hook->apply_filters, ITSEC_Lib_User_Activity->update_last_seen, delete_user_meta, delete_meta I am reporting the issue here because I believe it is not related to the iThemes Security plugin but to WordPress itself. The only possible reason this happens I can think of, it that `\wpdb::$ready` is false, because in that case `\wpdb::query` returns false without flushing the last result: {{{ public function query( $query ) { if ( ! $this->ready ) { $this->check_current_query = true; return false; } // ... } }}}" jannesmannes Tickets Awaiting Review 49363 wpdb::query() does not detect LOAD DATA as an insert statement type Database 5.3.2 normal normal Awaiting Review defect (bug) new has-patch 2020-02-05T02:41:19Z 2020-02-05T02:41:19Z "The regular expression used to choose what return type a query should have does not include `LOAD DATA`. A query beginning with these tokens thus incorrectly returns `0` for the number of affected rows. The following patch would correct the issue: {{{#!patch --- a/wp-includes/wp-db.php +++ b/wp-includes/wp-db.php @@ -1973,7 +1973,7 @@ if ( preg_match( '/^\s*(create|alter|truncate|drop)\s/i', $query ) ) { $return_val = $this->result; - } elseif ( preg_match( '/^\s*(insert|delete|update|replace)\s/i', $query ) ) { + } elseif ( preg_match( '/^\s*(insert|delete|update|replace|load\s+data)\s/i', $query ) ) { if ( $this->use_mysqli ) { $this->rows_affected = mysqli_affected_rows( $this->dbh ); } else { }}} " lev0 Tickets Awaiting Review 47818 $wpdb should update ->insert_id also when `LAST_INSERT_ID(...)` is found Database normal normal Awaiting Review enhancement new 2019-08-02T06:44:28Z 2019-08-02T06:44:28Z "It is possible to set the insert id pointer manually by in including `LAST_INSERT_ID(column)` anywhere in the statement. `$wpdb` however does not update it's internal `insert_id`, it does this only when the statement is 'insert' or 'replace'. This makes it harder to get the ID of an updated row. https://stackoverflow.com/a/46335863" cpiber Tickets Awaiting Review 55202 A proper (error) message should be show when mysql setting autocommit=0 Database 5.9 normal normal Awaiting Review enhancement new 2022-02-19T16:35:11Z 2022-02-19T20:03:07Z "A proper (error) message should be show when mysql setting autocommit=0 Somewhere is the text: [https://make.wordpress.org/core/handbook/contribute/design-decisions/#some-esoteric-mysql-settings-are-not-supported Some esoteric MySQL settings are not supported] But why not add a simple error message? (Just because it takes tooo long to find out why the installer will not work without this message). " Luuk34 Tickets Awaiting Review 41944 Add %u support to wpdb->prepare Database 4.8.2 normal normal Awaiting Review enhancement new 2017-09-21T13:30:31Z 2017-09-21T15:30:20Z "MySQL unsigned int: 4bn [[BR]] PHP %d signed int: 2bn[[BR]] [[BR]] Maybe the standard should be %u not %d or at the very least support both while internally using %u for all key lookups?[[BR]] [[BR]] Especially since much of the WP Core specifically sets ID fields as bigint unsigned not null auto_increment.[[BR]] [[BR]] If a site never has more than 2bn records, and MOST will NEVER get that big, it is a non-issue but shouldn't the standard be consistent between the DB engine and the language processor (PHP)?" charlestonsw Tickets Awaiting Review 60254 Add filter to class-wpdb.php#query that allows to return data from filter return value Database trunk normal normal Awaiting Review enhancement new 2024-01-15T07:35:21Z 2024-01-15T07:35:21Z "Similar to #2721, but allows not only changing the SQL query, but also the return value. In our case, we want to cache some SQL queries from third-party plugins that refuse to implement an object cache. Here is an example of a plugin which runs a SQL query multiple times without using a cache mechanism: [[Image(https://i.imgur.com/qAF0eI5.png)]] I have prepared a suggestion for this, but I do not know if this covers all cases from `$wpdb` usage or needs some adjustments: https://gist.github.com/matzeeable/4473915b225aa1550d5da5b9b0b19b16/revisions" mguenter Tickets Awaiting Review 46210 Add helpers for default/empty datetime value Database normal normal Awaiting Review enhancement new has-patch 2019-02-07T22:37:30Z 2023-02-24T20:17:08Z "Typing `0000-00-00 00:00:00` is error prone, and tedious. There are a few dozen usages of it sprinkled through-out WordPress. Hundreds of plugins also. I offer up the idea of 2 helper functions: * `__return_empty_datetime()` that simply returns `0000-00-00 00:00:00` * `is_empty_datetime( $datetime = '' )` to check if a variable is `empty()` or `0000-00-00 00:00:00`" johnjamesjacoby Tickets Awaiting Review 59106 Add secondary index on `wp_posts` table to improve media queries performance Database 6.4 normal normal Awaiting Review enhancement new 2023-08-15T11:49:34Z 2023-08-15T11:50:28Z "I've been recently been involved in migrating a large site database to MySQL 8.0. Upon testing the site, we've noticed that the MySQL 8 queries related to the Media library seem to take much longer than on MariaDB. Debugging the issue, it seems that MySQL 8, due to its Cost optimiser https://dev.mysql.com/doc/refman/8.0/en/cost-model.html, seems to prefer to drop the type_status_date index and to a full table scan to retrieve the results. Doing a FORCE INDEX(type_status_date) on the media query, like {{{ SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts FORCE INDEX(type_status_date) WHERE 1=1 AND wp_posts.post_type = 'attachment' AND ((wp_posts.post_status = 'inherit' OR wp_posts.post_status = 'private')) ORDER BY wp_posts.post_date DESC LIMIT 0, 20 }}} seems to improve the response time. However, a second alternative seems also to show better results, specifically targeting only the post_type, post_date and ID, something like {{{ create index idx_type_date on wp_posts(post_type, post_date, ID); }}} Running the explain analyze on the queries with each index, we seem to get a better cost number when the new index is used, 142 vs 744, which would indicate a better performance at the MySQL level, this being confirmed in our tests on a large database {{{ EXPLAIN ANALYZE SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts FORCE INDEX(type_status_date) WHERE 1=1 AND wp_posts.post_type = 'attachment' AND ((wp_posts.post_status = 'inherit' OR wp_posts.post_status = 'private')) ORDER BY wp_posts.post_date DESC LIMIT 0, 20 -> Limit: 20 row(s) (no early end due to SQL_CALC_FOUND_ROWS) (cost=744 rows=20) (actual time=12..13.1 rows=20 loops=1) -> Sort: wp_posts.post_date DESC (cost=744 rows=3620) (actual time=12..12.8 rows=7407 loops=1) -> Filter: ((wp_posts.post_type = 'attachment') and ((wp_posts.post_status = 'inherit') or (wp_posts.post_status = 'private'))) (cost=744 rows=3620) (actual time=0.0505..8.37 rows=7407 loops=1) -> Index range scan on wp_posts using type_status_date over (post_type = 'attachment' AND post_status = 'inherit') OR (post_type = 'attachment' AND post_status = 'private') (cost=744 rows=3620) (actual time=0.0459..5.63 rows=7407 loops=1) }}} {{{ EXPLAIN ANALYZE SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts FORCE INDEX(idx_type_date) WHERE 1=1 AND wp_posts.post_type = 'attachment' AND ((wp_posts.post_status = 'inherit' OR wp_posts.post_status = 'private')) ORDER BY wp_posts.post_date DESC LIMIT 0, 20 -> Limit: 20 row(s) (no early end due to SQL_CALC_FOUND_ROWS) (cost=142 rows=20) (actual time=0.214..15.3 rows=20 loops=1) -> Filter: ((wp_posts.post_status = 'inherit') or (wp_posts.post_status = 'private')) (cost=142 rows=688) (actual time=0.211..15 rows=7407 loops=1) -> Index lookup on wp_posts using idx_type_date (post_type='attachment') (reverse) (cost=142 rows=3619) (actual time=0.207..13.4 rows=7407 loops=1) }}} To replicate this, we've installed latest WordPress version and MySQL 8, and create the following post_status count structure: {{{ mysql> select post_status, count(*) from wp_posts group by post_status; +-------------+----------+ | post_status | count(*) | +-------------+----------+ | inherit | 7407 | | draft | 1 | | publish | 23 | | auto-draft | 1 | +-------------+----------+ 4 rows in set (0.01 sec) }}} Can this be considered as a core improvement to the wp_posts table that would directly benefit the Media related queries? Basically, if MySQL 8 decides that the new index is faster, specially for Media search queries, it will simply pick that on up as opposed to using the original index or dropping it fully if it considers its much more efficient to do a full table scan. From our database, a search query for term `test` shows an improvement of 0.5 seconds of this basic search media query: {{{ MySQL []> SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts FORCE INDEX(type_status_date) WHERE 1=1 AND wp_posts.post_type = 'attachment' AND ((wp_posts.post_status = 'inherit' OR wp_posts.post_status = 'private')) and post_title like ""%test%"" ORDER BY wp_posts.post_date DESC LIMIT 0, 1; +---------+ | ID | +---------+ | xxxxxxx | +---------+ 1 row in set, 1 warning (3.867 sec) MySQL []> SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts FORCE INDEX(idx_type_date) WHERE 1=1 AND wp_posts.post_type = 'attachment' AND ((wp_posts.post_status = 'inherit' OR wppp_posts.post_status = 'private')) and post_title like ""%test%"" ORDER BY wp_posts.post_date DESC LIMIT 0, 1; +---------+ | ID | +---------+ | xxxxxxx | +---------+ 1 row in set, 1 warning (3.244 sec) }}} Thanks for considering this." ovidiul Tickets Awaiting Review 59269 Add support for adding metadata in bulk Database normal normal Awaiting Review enhancement new has-patch 2023-09-01T22:39:01Z 2023-09-01T23:43:31Z "Related: #34848 This ticket aims to introduce functions for bulk inserting metadata in a manner that's more performant than calling the `add_*_meta()` functions multiple times per object. This is particularly beneficial during large imports or data generation where `add_*_meta()` is called many times for a large number of users, posts, comments, or terms. This can be achieved by inserting all the metadata for the object in one INSERT query and only deleting the object's meta cache once. The existing filters and actions that are triggered when calling the `add_*_meta()` functions individually should still all be fired as expected, once for each meta key. The performance benefit should grow as the number of rows to insert increases, because both the number of database queries and the number of cache deletions for the object should remain constant (`O(1)`) rather than increasing correspondingly to the number of rows (`O(n)`). That said, there does appear to be a sweet spot after which the performance of one large INSERT query starts to degrade, although according to the PHPBench tests I've added it still remains overall more performant than multiple queries. Some more testing is needed across different payload sizes and database versions." johnbillion Tickets Awaiting Review 54627 Adding More Timestamp columns to database Database normal normal Awaiting Review enhancement new 2021-12-14T23:54:04Z 2021-12-14T23:54:04Z "I would love to see 'created_at' and 'updated_at' as new columns to some of the default tables such as users, users_meta, etc. Mysql can autofill these columns automatically, and it is very useful information to know when rows are added or updated. I could also see 'created_by' and 'updated_by' as possible columns as well, so it is possible to know when the table row was created or updated by a particular User ID." bhubbard Tickets Awaiting Review 45354 Adding index for meta_key and post_id in postmeta Database normal normal Awaiting Review enhancement new dev-feedback 2018-11-14T21:43:31Z 2023-12-19T22:49:54Z "We should add an index on post_id and meta_key as queries that contain these columns are incredibly common (even in loop in core on every page load basically). An index will improve query speed up to 100% even when only 1000 posts are in db. Test it yourself: {{{ ALTER TABLE `wp_postmeta` ADD INDEX `wp_postmeta_key_id` (`meta_key`,`post_id`); }}} " DuckDagobert Tickets Awaiting Review 52527 Better RAM handling in wp-db.php Database 5.6.1 normal normal Awaiting Review enhancement new 2021-02-15T11:26:08Z 2021-02-15T11:26:08Z "Hello! I have to start by apologizing if I am in the wrong place, or if I am structuring this ticket badly. I don't have much experience here inside trac. I stumbled upon a fatal error when trying to load the WooCommerce status page. The system runs out of memory on line 2022 in wp-db.php. While the actual bug most certainly lies elsewhere, I got some hints from my host that the solution on line 2022 is not the best. I have no clue about this, but I thought I at least could submit a ticket and let the community decide! To paraphrase a translation, they say: wp-includes/wp-db.php line 2022 is a bad solution because of a database call that loads all results into RAM instead of iterating them one by one. But independent of this the error seems to be caused by a very large SQL-query that returns way too much data. Probably a bug somewhere. This is not a ticket to handle the bug that returns way too much data, but it's a ticket to flag the possible issue of bad RAM handling in wp-db.php. " Dekadinious Tickets Awaiting Review 56259 Database Repair doesn't check for missing primary keys / A.I. Database normal minor Awaiting Review enhancement new 2022-07-20T15:55:41Z 2023-03-09T10:44:21Z "Hi amazing WP community, This is the second time I've come across this issue in the wild. The built-in DB repair functionality (repair.php) doesn't attempt to verify or repair missing primary key / autoincrement attributes of the core wp_posts table (and possibly other important core tables). This affects fringe/edge cases where this core table has been corrupted due to third-party plugins or other user misfortune. We just need add some SQL to check for this health database attribute, such as: {{{ SELECT COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'wp_posts' AND CONSTRAINT_NAME = 'PRIMARY'; }}} {{{ SELECT COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'wp_options' AND CONSTRAINT_NAME = 'PRIMARY'; }}} (Adapted from source: https://stackoverflow.com/a/52288527/8800423) These should each return 1 result each. As part of 'repair', we could have auto-fix SQL to re-enable the auto-increment / primary key. There is obviously sometimes a 'market' for that fix: https://webshop.mijnpress.nl/shop/plugins/wpdb-wp-core-database-sql-index-primary-keys-fix-script/ (commercial; no association) There are quite a few edge/fringe cases recorded that this could help: • https://wordpress.stackexchange.com/questions/353713/wordpress-database-problem-wp-posts-primary-key • https://wordpress.stackexchange.com/questions/237076/resetting-auto-increment-primary-key • https://www.alexgeorgiou.gr/repair-auto_increment-primary-key-wordpress-mysql/ • https://wordpress.stackexchange.com/questions/329162/is-it-possible-to-fix-repair-all-index-primary-key-unique-and-all-other-stru • https://wordpress.stackexchange.com/questions/225641/wordpress-database-lost-auto-increment Additionally, in a sense this is a core functionality break if these database attributes are not present because posts will fail to create in the DB. Perhaps the detection and fix shouldn't be on advanced user’s manual use of repair.php. In which case, running a regular check with mosts WP requests would be non-performant. So perhaps this could be included as a CRON'd/infrequent health check within the WP Site Health scope? N.B. The popular non-core ""WP-DBManager"" plugin does not (at latest version 2.80.8) check for (let alone repair) this issue either." harryfear Tickets Awaiting Review 41956 Errno in $wpdb Database normal normal Awaiting Review enhancement new has-patch 2017-09-22T13:30:45Z 2018-08-17T12:09:42Z "Since I use referential integrity in my plugin, there will be situation that the referential integrity will be violated. But, unfortunately there is not a proper way to identify the reason of the violation in $wpdb->last_error. It is only text, and could differ in diffenrent languages I assume. Since Mysqli contains also the errno, would it be possible to also present the errno as part of the result. In that case I could check the errorno to be 1451 for referential integrity violation. In my opinion a usefull enhancement of the WPDB class. If any help needed, just contact me" LeonN1960 Tickets Awaiting Review 39748 Function to insert or update DB value Database normal normal Awaiting Review enhancement new 2017-01-31T10:00:01Z 2017-10-29T17:06:57Z "Hello. I have been searching for long and couldnt found a good solution to the simple problem: lets say,in my plugin, I create a new table. Then I want to have a function, that updates a row in table, which's column (named EventId) equals i.e. 734 (if such row not exists, then create it). for that, I use my custom-created function: {{{#!php function UPDATE_OR_INSERT($tablename, $NewArray, $WhereArray){ global $wpdb; $arrayNames= array_keys($WhereArray); //convert array to STRING $o=''; $i=1; foreach ($WhereArray as $key=>$value){ $o .= $key . ' = \''. $value .'\''; if ($i != count($WhereArray)) { $o .=' AND '; $i++;} } //check if already exist $CheckIfExists = $wpdb->get_var(""SELECT "".$arrayNames[0]."" FROM "".$tablename."" WHERE "".$o); if (!empty($CheckIfExists)) { return $wpdb->update($tablename, $NewArray, $WhereArray );} else { return $wpdb->insert($tablename, array_merge($NewArray, $WhereArray) ); } } }}} //usage: {{{ UPDATE_OR_INSERT('my_table', array('new_page_content'=>'blabla'), array('EventId'=>734)); }}} it's good if there was built-in function that replaces this custom method" tazotodua Tickets Awaiting Review 43019 Hook to validate post form data before save Database normal major Awaiting Review enhancement new has-patch 2018-01-04T13:23:07Z 2021-10-28T03:26:41Z "There doesn't seem to be a hook available to validate form data before a post is saved. In my case I'm considering using JavaScript to validate the post title, content and meta but would prefer to do this validation server side. Can we add a hook?" henry.wright Tickets Awaiting Review 46179 Make fetch_array accessible in $wpdb Database normal normal Awaiting Review enhancement new 2019-02-04T09:38:25Z 2019-02-04T09:38:25Z "Hi, I want to suggest a change in $wpdb. I'd like to see a possibility to use fetch_array by myself rather than taking the result of a query as an array or object. Let me explain why. WordPress doesn't scale very well when you handle large amounts of data from the database. For example, if you want to display a list of all posts and you have like 5000 of them. You'd use WP_Query for that. Which is fine, I guess, for a limited number, but slow in this use case. So you'd query yourself. {{{ SELECT * FROM wp_posts WHERE post_status = 'publish' AND post_type = 'post' ORDER BY post_title ASC }}} If you give that to $wpdb, you'd receive an array with all 5600 posts and all the jazz attached to it. It takes 5 seconds and takes about 500 MB of PHP memory. I have now added an own class and established a second database connection. With that, I run the query above but the result remains in mysql. Instead of {{{#!php fetch_array($results)) { // do stuff } }}} And I only need 30 MB of PHP RAM and 0.4s of time. This saves a lot of resources because you are able to get rid of a lot of PHP overhead. See, the MySQL result is in memory either way (because you did run the query) but you don't have to store everything from the result in PHP memory as well. You process them one at a time and only store (or output or whatever you need to do) what you actually need. I know this is nothing you'd usually do but there are other use-cases where an accessible fetch_array would come in handy in the interest of performance optimization - for example if your plugin utilizes a cronjob which has to handle large amounts of data. I don't assume this would break anything compatibility-wise as it is an additional method inside this class, so it would be a very nice addition. All you'd need is to add a new return method to $wpdb->query that returns the MySQL resource and a new method that exposes a fetch_array function to the public accepting said resource. Thank you a lot for reading my proposal and at least considering it :) -alduin" alduinwf Tickets Awaiting Review 44292 Make use of protected process_fields Database normal normal Awaiting Review enhancement new 2018-06-01T12:23:58Z 2018-06-01T12:23:58Z "I have written an 'upsert' routine and thought it would be nice to have this functionality in WordPress core. Searching for results in this ticket https://core.trac.wordpress.org/ticket/22196. Not having a method for it, is okay. I already have my own implementation. However I need some field processing like the same way has been done for the insert and update routines. Unfortunately the method for that (process_fields) has been made protected. Is it desirable to make that method public or should I copy the logic of those routines to be able to use it?" andizer Tickets Awaiting Review 46565 Mixed engine in tables, could bring to major WP failure! Also there is a small fix that could avoid that. Database 4.9.8 normal normal Awaiting Review enhancement new 2019-03-19T18:49:44Z 2019-03-20T07:40:01Z "''First of all ... after MySQL decided to make InnoDB the default engine ... many of us have mixed database with both InnoDB and MyisAM labeled tables. If a plugin add a new table now (it happened to us with both WPML and Woocommerce) the new table will be mostly InnoDB but old table could be MyIsAM. That can bring to JOIN failures, that in our case made us lost primary key or foreign key links, but will definitely cause also tables **permanent locks** (due to plugin/software crash), and that took finally to the main problem: **data (rows) inserted with ID = 0** THAT alone should be advertised a lot around plugin developers, that should check if their plugin tables are equal to the current-default-engine and if they are not, they should: ""alter TABLENAME engine = current-default-engine"".'' **Straight to the point:** Data with ID = 0 actually can make crash WordPress because of this: wp-includes/post.php:4867 Please note that this code will make WordPress go into a recursive infinite loop (causing ""fatal error memory exhausted"") if the ID = 0. I know that ID = 0 is an abnormal situation, but believe me, if having locked tables makes this happen, you want to add a handbreak to that. **This is a small fix, but you surely can do better:** {{{ // Start the search by looking at immediate children. if ( isset( $children[ $page_id ] ) ) { // Always start at the end of the stack in order to preserve original `$pages` order. $to_look = array_reverse( $children[ $page_id ] ); while ( $to_look ) { $p = array_pop( $to_look ); if (!array_key_exists($p->ID,$pidx)) { $pidx[$p->ID]=true; $page_list[] = $p; if ( isset( $children[ $p->ID ] ) ) { foreach ( array_reverse( $children[ $p->ID ] ) as $child ) { // Append to the `$to_look` stack to descend the tree. $to_look[] = $child; } } } } } }}} " Nokao Tickets Awaiting Review 58826 Please add a default post relations table Database normal critical Awaiting Review enhancement new 2023-07-17T22:10:52Z 2023-07-17T22:10:52Z "As I am sure you know, people have bee using wordpress for a lot of things other than a simple blogging platform. And while your structure allows for basic functionality and relationships, There is a lot of room for improvement. As people continue to add more and more custom post types and related meta, the default posts/post meta tables will quickly become filled and make web queries slow. In addition, the taxonomy system is very limiting, as that is not the only way one would want to group like items. The most common way, would be POST relationships. If a Class has many students, and students have many classes, Displaying an intermediary page between the two is very difficult with the current set up. Therefore, I'd like to request an extension to the database structure as described below in the link below. At minimum the 1st option should be considered as an alternative to archive pages with taxonomies. [https://imgur.com/a/L9q4bAf] If I type domain.exp/articles/for/baseball where baseball is a sports custom post type and arcticles is just one of several custom post types, I expect a query select * from articles where hobby_slug = 'baseball' Currently everyone expects me to make the sport CPT a taxonomy, but if the sport has its own custom fields, and taxonomies already attached, that does not work. This system would be usable in a thousand different use cases and would make your platform more versatile. USERS, comments, posts and custom post types are all Models. Make it easier to establish any relation between them please. " jcc5018 Tickets Awaiting Review 44386 Problem with utf8mb4_unicode_ci collation for arabic content Database 4.9.6 normal major Awaiting Review enhancement new has-patch 2018-06-17T06:52:44Z 2018-06-17T07:00:57Z "I see that since version 4.6, WordPress uses utf8mb4_unicode_ci as the default collation. I see this in the determine_charset function in the /wp-includes/wp-db.php file (CMIIW). In my experience, it looks like utf8mb4_unicode_ci has problems with content that uses arabic letters. Example: I created a tag with the name: {{{ #!span style=""font-size: 28pt"" ٱللَّهِ }}} And I created another tag with the name: {{{ #!span style=""font-size: 28pt"" ٱللَّهُ }}} Then when I do a tag search (via wp-admin), with keyword: {{{ #!span style=""font-size: 28pt"" ٱللَّهُ }}} the search results that appear are: {{{ #!span style=""font-size: 28pt"" ٱللَّهِ }}} and {{{ #!span style=""font-size: 28pt"" ٱللَّهُ }}} tags. Whereas it should appear only tag: {{{ #!span style=""font-size: 28pt"" ٱللَّهُ }}} according to the search keyword. This becomes a problem when a post wants to use the tag {{{ #!span style=""font-size: 28pt"" ٱللَّهُ }}} , but can not be due to existing tag {{{ #!span style=""font-size: 28pt"" ٱللَّهِ }}} My guess is not a bug from WordPress, but a bug from MySQL. For information, perhaps this link is a related issue: [https://bugs.mysql.com/bug.php?id=76218] (CMIIW)." array064 Tickets Awaiting Review 54669 Remove ONLY_FULL_GROUP_BY from incompatible wpdb modes Database 3.9 normal normal Awaiting Review enhancement new needs-unit-tests 2021-12-20T12:56:22Z 2022-06-15T20:52:56Z "as GROUP BY is non-deterministic otherwise, ONLY_FULL_GROUP_BY ensures this is not the case. which is why this is enabled by default since MySQL 8 I guess it was disabled originally, as some queries would have to be updated in WP core. I think however, this should be done now, as otherwise we're accumulating more and more technical debt." malthert Tickets Awaiting Review 54324 Rename Table Function - maybe_rename_table() Database normal normal Awaiting Review enhancement new has-patch 2021-10-26T17:22:22Z 2023-01-09T16:23:27Z "I would like to see a helper function to rename tables, similar to maybe_create_table() in /wp-admin/install-helper.php. Having a maybe_delete_table() would be very useful as well." bhubbard Tickets Awaiting Review 54560 Return null from wpdb::get_results when query fails Database normal normal Awaiting Review enhancement new has-patch 2021-12-02T17:14:55Z 2021-12-02T17:14:55Z "It's not currently possible to know if a query failed internal to `wpdb::get_results()` since the result of `$wpdb->query()` isn't checked. This diff introduces a `null` return from `wpdb::get_results()` when a boolean `false` is returned from `wpdb::query()`." jeffstieler Tickets Awaiting Review 58180 Specify the REGEXP format for meta_query Database 6.2 normal trivial Awaiting Review enhancement new needs-docs 2023-04-24T05:47:20Z 2023-08-01T11:33:44Z "Because of the fact that `meta_query` parameter with `['compare' => 'REGEXP', 'key' => 'foo', 'value' => '^bar']` working as substitution to raw SQL-request like `... foo.meta_key REGEXP ^bar ...` please, specify this explicitly in the [https://developer.wordpress.org/reference/classes/wp_meta_query/ documentation] - especially the fact that this REGEXP should not be wrapped in `/`" letraceursnork Tickets Awaiting Review 47994 The additions of a new wp_posts table columns called post_level and post_patriarch Database normal normal Awaiting Review enhancement new 2019-09-06T23:29:48Z 2019-09-06T23:29:48Z "Many user are looking for an easy solution to sort table by page children. I understand their are functions like get_children, pre_get_posts, get_posts, wp_query. But if I wanted to extract the children directly from the db table wp_posts, their is no easy way. So I am suggest the Core team to add in an additional column: post_level. This is already noticed with the post list table's html row/td as a class (level-0) so it should be in the table. {{{ //Get all 1st generations of post based on 2 parents who are cousins SELECT p.* FROM $wpdb->posts p WHERE p.post_level = 1 AND p.post_parent = 123 OR p.post_parent = 345) //Get all 2nd generation pages of parent post id 1 SELECT p.* FROM $wpdb->posts p WHERE p.post_level = 2 AND p.post_patriarch = 1 }}} post_level depends on what level of Parent (0), Child (1) or Grandchild(2) or GreatGrandChild (3) a page is within the Hierarchy. post_patriarch (or lack of a better word) is always the level 0 parent ID This should also be consider for other tables as well(e.g menu_level) I hope this make sense. " samjco Tickets Awaiting Review 41054 Use sargable date filtering where possible Database normal normal Awaiting Review enhancement new 2017-06-15T07:54:13Z 2017-06-15T12:13:22Z "Currently, many queries generated by WP use post_date in a non-sargable fashion, namely by filtering based on the output of a MySQL function taking post_date as a parameter. These can be easily rewritten to use the index on post_date without, to my eyes, breaking anything to boost performance. Here's an example: {{{ MariaDB [blog]> EXPLAIN SELECT * FROM blog.wp_posts WHERE YEAR(post_date) = 2017; +------+-------------+----------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+----------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | wp_posts | ALL | NULL | NULL | NULL | NULL | 2684 | Using where | +------+-------------+----------+------+---------------+------+---------+------+------+-------------+ }}} vs {{{ MariaDB [blog]> EXPLAIN SELECT * FROM blog.wp_posts WHERE post_date >= ""2017-01-01"" AND post_date < ""2018-01-01""; +------+-------------+----------+-------+---------------+-----------+---------+------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+----------+-------+---------------+-----------+---------+------+------+-----------------------+ | 1 | SIMPLE | wp_posts | range | post_date | post_date | 8 | NULL | 262 | Using index condition | +------+-------------+----------+-------+---------------+-----------+---------+------+------+-----------------------+ }}} This optimization can be applied to any comparison between post_date and an already known parameter (from query_var). The only time it wouldn't be possible would be when comparing a portion of the date of two different posts (e.g. WHERE MONTH(x.post_date) == MONTH(y.post_date)) It's not much, but it's something. " ComputerGuru Tickets Awaiting Review 40150 VARCHAR/TEXT inefficiencies Database 4.7.3 normal normal Awaiting Review enhancement new 2017-03-14T00:45:10Z 2017-03-14T00:45:10Z "From conversation with Matty Cohen, a selection of structural improvement suggestions, for performance and future-proofing. Note that these aspects have recently been applied to WooCommerce already, resulting in a much optimised/modernised default table structure. Current some of the TEXT and VARCHAR use in WP Core appear to be based on old information - that is, restrictions and behaviour from MySQL versions long ago. This has consequences for performance. First, VARCHAR. In MySQL 4.0, VARCHAR used to be restricted to 255 max. In MySQL 4.1 character sets such as UTF8 were introduced and MySQL 5.1 supports VARCHARs up to 64K-1 in byte length. Thus, any occurrence of VARCHAR(255) indicates some old style logic that needs to be reviewed. Why not just set the maximum length possible? Well... A VARCHAR is subject to the character set it's in, for UTF8 this means either 3 or 4 (utf8mb4) bytes per character can be used. So if one specifies VARCHAR(50) CHARSET utf8mb4, the actual byte length of the stored string can be up to 200 bytes. In stored row format, MySQL uses 1 byte for VARCHAR length when possible (depending on the column definition), and up to 2 bytes if necessary. So, specifying VARCHAR(255) unnecessarily means that the server has to use a 2 byte length in the stored row. This may be viewed as nitpicking in the margin, however storage efficiency affects the number of rows that can fit on a data page and thus the amount of I/O required to manage a certain amount of rows. It all adds up, so having little unnecessary inefficiencies will cost - particularly for larger sites. Best practice is to set VARCHAR to the maximum necessary, not the maximum possible - otherwise, as per the above, the maximum possible is about 16000 for utf8mb4, not 255 - and nobody would propose setting it to 16000, would they? But it's not much different, in stored row space a VARCHAR(255) requires a 2 byte length indicator just like VARCHAR(16000) would. So please review VARCHAR columns and set their definition to the maximum actually necessary, this is very unlikely to come out as 255. If 255, why not 300? Or rather 200? Or 60? Setting a proper number indicates that thought and data analysis has gone into the design. 255 looks sloppy. On to TEXT. TEXT (and LONGTEXT) columns are handled different in MySQL/MariaDB. First, a recap of some facts related to TEXT columns. The db server often needs to create a temporary table while processing a query. MEMORY tables cannot contain TEXT type columns, thus the temporary table created will be a disk-based one. Admittedly this will likely remain in the disk cache and never actually touch a disk, however it goes through file I/O functions and thus causes overhead - unnecessarily. Queries will be slower. InnoDB can store a TEXT column on a separate page, and only retrieve it when necessary (this also means that using SELECT * is needlessly inefficient - it's almost always better to specify only the columns that are required - this also makes code maintenance easier). A TEXT column can contain up to 64k-1 in byte length (4G for LONGTEXT). So essentially a TEXT column can store the same amount of data as a VARCHAR column (since MySQL 5.0), and we know that VARCHAR offers us benefits in terms of server behaviour. Thus, any instance of TEXT should be carefully reviewed and generally the outcome is to change to an appropriate VARCHAR. Using LONGTEXT is ok, if necessary. If the amount of data is not going to exceed say 16KB character length, using LONGTEXT is not warranted and again VARCHAR (not TEXT) is the most suitable column type. Applications don't need to care, so the db definition can be altered without any application impact. Summary: Particularly when combined with the best practice of not using SELECT *, using appropriately defined VARCHAR columns (rather than VARCHAR(255) or TEXT) can have a measurable and even significant performance impact on application environments. It is a worthwhile effort." arjenlentz Tickets Awaiting Review 55938 WP prefix on installation - instructions to be indicated Database normal minor Awaiting Review enhancement new 2022-06-07T12:48:50Z 2022-06-07T14:37:01Z "Following a bad experience, changing the database prefix from ""wp_"" to ""SAI"" for security. I found out that this is giving Woocommerce a problem with creating tables in the database. This problem is solved, but I create this ticket to give the idea during the next Update, to indicate how to change the prefix. I had put SAI instead of wp_ forgetting the _ and capitalizing, hence the problem I had encountered." valentindu62 Tickets Awaiting Review 42703 WP_Query: Swap parsing date into own method Database normal normal Awaiting Review enhancement new 2017-11-26T17:32:44Z 2019-01-16T06:50:09Z "The WP_Query::get_posts also parses the date query. This is modular enough to swap it to its own method at last. A proof of concept has been made and all tests are still green. Some details and todos: - see https://github.com/ScreamingDev/wordpress-develop-1/commit/6bb681e8caf4df8ba4811690ee19f12ad1755371 - Check if the whole logic can be shifted to WP_Date_Query::get_sql Hint: The long run is about enabling WordPress to support multiple tables (per ""post-type""). To achieve this some enhancements to the current wpdb and WP_Query class are needed. This issues covers a small bit of it by refurbishing the source." screamingdev Tickets Awaiting Review 41281 attachment_url_to_postid results in very slow query joemcgill* Database 4.9 normal normal Awaiting Review enhancement accepted 2017-07-10T07:53:25Z 2021-04-30T13:17:51Z "`attachment_url_to_postid` throws query like this: {{{ # wp-includes/media.php ll.3922 $sql = $wpdb->prepare( ""SELECT post_id FROM $wpdb->postmeta WHERE meta_key = '_wp_attached_file' AND meta_value = %s"", $path ); }}} But wp_postmeta table has index only of `meta_key`, this may cause file sort. == Why this is problem == Let's assume that you have 20,000 posts and each post has 10 attachments in your WP news site. MySQL filters rows with `meta_key` but still remain 200,000 possible rows. This causes file sort. `attachment_url_to_postid` is used not only in admin screen, but also in public area(e.g. AMP Plugin https://wordpress.org/plugins/amp/ ). Now twitter changes URL for mobile device if AMP version is available. So, MySQL CPU raises up to 100% by `attachment_url_to_postid` if site traffic is high, == Solution == To avoid slow query, I suggest adding another index. {{{ ALTER TABLE $wpdb->postmeta ADD INDEX meta_key_meta_value (meta_key(191), meta_value (64)); }}}" Takahashi_Fumiki Tickets Awaiting Review 8924 autoload should be boolean in add_option() Database lowest normal Awaiting Review enhancement reopened 2009-01-22T23:51:48Z 2019-03-15T00:30:00Z "Regarding: CREATE TABLE `wp_options` (`autoload` varchar(20) NOT NULL DEFAULT 'yes', Why not boolean binary, or one character long? " jidanni Tickets Awaiting Review 40357 dbDelta can't change primary keys, create AUTO_INCREMENT columns and doesn't make significant index changes Database 4.8 normal normal Awaiting Review enhancement new needs-unit-tests 2017-04-04T17:20:04Z 2017-04-04T18:21:02Z "dbDelta has three inter-related issues which center around changing indexes. 1) It isn't possible to change which column is the primary key 2) It isn't possible to add a new AUTO_INCREMENT column 2b) It isn't possible to modify an existing AUTO_INCREMENT to no longer be AUTO_INCREMENT 3) Indices with the same name are not dropped/re-created, even when the index definition is changed significantly. == Use case == A client had been tracking inventory in a custom table where the product ID was the primary key. When he opened a new location, we added a location column, and wanted to be able to track how many of each product was in each location. 1. A table's purpose is being expanded, or otherwise doesn't meet the needs of the data. Since the primary key is unique, we needed to add a new key column and change which column was designated as the primary key. 2. A table was originally defined without an AUTO_INCREMENT column and the need for such a column arises. The new column we wanted to add and use for the key was simply an AUTO_INCREMENT integer column. In testing we defined the new column and also defined a new UNIQUE index (so, not changing the primary key yet). Since dbDelta adds new columns before adding the new indices, and in separate ALTER TABLE statements, MySQL refuses to add a new AUTO_INCREMENT column without an index. The solution is to add the new column without the AUTO_INCREMENT designation, then add the UNIQUE index, then alter the table to use AUTO_INCREMENT. 3. A primary (or other key) could be significantly and intentionally altered, significantly changing how queries are run. I understand that WP doesn't want to drop and recreate indices when changing the sub-part of an index (see: https://core.trac.wordpress.org/ticket/34870#comment:21) However I think that it should change the index, if the definition is significantly altered. In the use case above, we could've changed the primary key to be `PRIMARY KEY(productId,location)` instead of adding a new column and switching the index to that column. In other use cases, changing from a BTREE to FULLTEXT index would change which types of queries need to a full table scan. == This Patch == This patch does the following: 1. You can now add a new AUTO_INCREMENT column to an existing table When a new AUTO_INCREMENT column is added to an existing table, the column creation is done in two parts. First the column is created as a non-AUTO_INCREMENT column, and a separate `ALTER TABLE` statement is set to run after index creation to enable AUTO_INCREMENT. Note: The CREATE TABLE statement given to dbDelta must provide the required indexes that MySQL expects. 2. You can now modify a column with AUTO_INCREMENT to no longer use AUTO_INCREMENT 3. You can change which column is the primary key 4. Significant index definitions cause an index to be dropped and re-created The cases that cause an index to be dropped and re-created are: * An index which wasn't UNIQUE, but now is or vice-versa * An index which changes index type (eg. FULLTEXT => BTREE) * An index which contains a different number of columns * An index which contains a different column order * An index which contains different columns Note: Changing the index sub-part or no longer defining the index in the table does not cause it to be dropped. == Other notes == 1. I've tried to use WP coding standards and comment my code well. I'd love feedback if there are things I can do better. 2. I've included a file, test.php which takes 13 table definitions, takes them two at a time, and converts between each possible combination. To run it, put it in the root WordPress directory and run `php ./test.php`. 3. Also, the dbDelta phpunit tests still pass." stuporglue Tickets Awaiting Review 49654 maybe_drop_table Database normal normal Awaiting Review enhancement new 2020-03-16T17:37:38Z 2023-04-07T15:21:11Z We currently have a function for `maybe_create_table`, I was looking for one to `maybe_drop_table`. This would be a very helpful function. bhubbard Tickets Awaiting Review 46576 short-circuit filter for dbDelta Database normal normal Awaiting Review enhancement new 2019-03-20T15:32:29Z 2019-03-20T15:32:29Z "`dbDelta` function can cause strange things on the large-scale WordPress installations. When you have large amounts of data, you don't really want to schema changes apply directly on the production. (at least we don't do that) So, I recommend adding new short-circuit filter to avoid that. (I know, returning an empty array with `dbdelta_queries` might be a workaround but not ideal for this job.)" m_uysl Tickets Awaiting Review 58871 support uca14.0.0 collation in database where available Database 6.3 normal normal Awaiting Review enhancement new has-patch 2023-07-20T23:36:30Z 2023-09-25T00:17:50Z "Like #32105 supporting a modern collation is a good idea. MariaDB added this in 10.10.1 (ref: https://jira.mariadb.org/browse/MDEV-27009). As the current default is case insensitive, we'll follow the same insensitivity and use accent insensitive. So of the collations available, use uca1400_ai_ci. ref: https://mariadb.com/kb/en/supported-character-sets-and-collations/#collations" danielblack Tickets Awaiting Review 48285 wp-config-sample.php should default to `utf8mb4` instead of `utf8` character set Database 5.3 normal minor Awaiting Review enhancement assigned has-patch 2019-10-11T03:50:07Z 2022-09-27T22:57:02Z "MySQL's `utf8` character encoding is not a correct implementation of the standard and doesn't work with 4-byte characters, which includes many emoji. `utf8mb4` is the corrected implementation. See https://medium.com/@adamhooper/in-mysql-never-use-utf8-use-utf8mb4-11761243e434 or just google ""mysql utf8 vs utf8mb4"" It would seem wise for `wp-config-sample.php` to default then to `utf8mb4` instead of `utf8` so that new installations have the improved character set." bchecketts Tickets Awaiting Review 46287 Add insert_ignore() method to wpdb to avoid unecessary boilerplate code Database normal normal Awaiting Review feature request new has-patch 2019-02-20T09:43:09Z 2019-02-20T10:44:33Z It's a very useful query, and easy to add to the core. Its usage would be less error prone, and code a lot DRYer if it were officially supported. lev0 Tickets Awaiting Review 58694 Enhancement in wordpress Database API Database 6.2.2 normal normal Awaiting Review feature request reopened 2023-07-01T12:11:08Z 2023-08-30T17:51:17Z "Hi im a wordpress developer and freelancer i also do wordpress Database optimization for my customers and I have a very smart suggestion to optimize the database structure of Beloved WordPress! issue: Keep in mind that every plugin and theme installed in WordPress will inject some data (options/post-metas/ etc) to the wpdb and when you remove them their data will remain (unused) inside user wp database with no clue or indication of what template or plugin the information was for! My suggestion: you can simply add an extra Argument for the core functions for adding options or metas (update_option or update_post_meta/update_user_meta) this Arg will add that plugin or theme base name to the added option or meta added like this -> update_option(""myopt"", ""myvalue"", $autoload, ""wooslider"") which the 4th arg is the name of my example plugin and its REQUIRED type ALSO the name of the plg/theme can be auto inserted from an PHP constant -> (define('WOODMART_CORE', __FILE__); ) NOTE: By using this new system, whenever a person wants to manually optimize the database, she/he can easily find out which information is not needed and quickly query and delete them, Also, the plugins that perform database cleaning are much easier and more efficient, fully automatically find out which plugins no longer exist and which information is related to them and can be deleted without any trouble! See the pic plz... https://mdezign.ir/wp-content/uploads/2023/07/aasdsadasdasd.jpg Consider this as a Great New development in order to optimize and speed up all WordPress sites! Thanks a lot. " mdesignfa Tickets Awaiting Review 54070 "Increase CPT ""machine name"" limit from its current maximum of 20 characters." Database 2.1 normal normal Awaiting Review feature request new dev-feedback 2021-09-03T13:30:04Z 2022-10-04T18:08:03Z "In order to accommodate proper namespacing for Custom Post Types (as well as, potentially, transliteration from non-Latin alphabets), 20 characters is often not enough. Custom Taxonomies currently have a machine name limit of 32 characters. It would be nice to see CPTs have at least that many, although 64 characters would be even better." taupecat Tickets Awaiting Review 54235 Script for handling metadata in wp_postmeta Database normal normal Awaiting Review feature request new needs-docs 2021-10-08T12:49:11Z 2021-10-08T13:47:16Z "Hello, Since wp_postmeta table gets so big, and most users just upload and delete and change themes/media sizes and reload and change again, etc. sooo often …could someone at WordPress just give a customizable function to handle data in that table? Specifically for images … I´m using a filter to prevent storing exif and iptc metadata in the same meta key for sizes (_wp_attachment_metadata): add_filter( ‘wp_read_image_metadata’, ‘__return_false’ ); but I had to delete manually all data that was previously added by default …and the same for unused images´ sizes. I guess wp-includes/media.php points something about this but I have no idea about unserializing, query for specific data, filtering results, deleting it, serializing again and restoring to db …I´m affraid this tasks have to be done manually by users with no code knowledge … Hope someone could write a custom php script for this. Thanks, Julia " julia77 Tickets Awaiting Review 59281 Support for Database Cursor Database normal normal Awaiting Review feature request new 2023-09-04T19:34:08Z 2023-09-27T13:47:35Z Is it possible to do a cursor query using the `$wpdb` instance or could it be added, which will return a generator (yield)? heera Tickets Awaiting Review 51762 Unix authentication support via unix_socket Database normal normal Awaiting Review feature request new 2020-11-12T12:15:50Z 2020-11-12T12:15:50Z "Hi, It seems WordPress supports unix_socket for the database, but not unix authentication - it would greatly increase the security of deployments (one less password is always better) and accelerate deploying speed, as you can just use the unix user for authentication. More info here: https://mariadb.com/kb/en/authentication-from-mariadb-104/ Cheers!" biztems Candidates for Closure 37678 Add an action hook for plugins to do database upgrades on Database normal normal Awaiting Review defect (bug) new dev-feedback 2016-08-16T18:10:59Z 2019-04-18T21:27:24Z "Currently, plugins that have custom database tables usually hook into `admin_init` and pray for rain when comes to their database alterations. This pollutes the `admin_init` hook namespace quite a bit, particularly with database actions that are almost always necessary in order for plugins to actually work correctly. It would be nice if WordPress core had a dedicated action hook meant for executing database alterations & upgrades, no different than there being one for `plugins_loaded`, `template_redirect`, et all... I'm conflicted on where exactly this hook should be. I'm 95% sure it belongs somewhere after `admin_init` so that admin area plugins have a chance to hook everything in. I'm also 95% certain I don't want this as part of the plugin activation sequence, because it's becoming more common to deploy plugins via WP CLI, version control, or some other deployment process, and activation hooks aren't usually ran that way." johnjamesjacoby Candidates for Closure 40418 ID columns in multisite database tables should be unsigned flixos90 Database 3.0 normal major Awaiting Review defect (bug) assigned dev-feedback 2017-04-12T02:00:28Z 2018-09-11T16:06:41Z "See #8751 for ye'olde single-site effort to normalize the respective object ID columns. All multisite ID columns are `bigint(20)`, but none of them are `unsigned` which has 2 unintended consequences: * Negative numbers can be stored as values instead of being set to `0` * Maximum int of `9223372036854775807` instead of intended `18446744073709551615` Changes are necessary to every multisite database table, as they all touch site or network IDs. Patch imminent" johnjamesjacoby Candidates for Closure 52648 Problem with certain passwords not working to login to mysql db Database 5.2 normal major Awaiting Review defect (bug) new reporter-feedback 2021-02-25T03:26:23Z 2021-02-25T14:41:39Z "After migrating my WordPress blog from 1and1 hosting to a FAMP server the blog would not come up and complained it couldn't connect to the database. When the database was migrated the same username and password were retained from the 1and1 site. I traced the problem to wp-db.php in wp-includes on the following line: @mysqli_real_connect( $this->dbh, $host, $this->dbuser, $this->dbpassword, null, $port, $socket, $client_flags ); By hardcoding this password into this line: @mysqli_rea$this->dbpasswordl_connect( $this->dbh, $host, $this->dbuser, ""SpivknocKs3%"", null, $port, $socket, $client_flags ); The WordPress blog would come up. (Note this is no longer the password for the database.) I also echoed $this->dbpassword and it was the same as the hard coded password but only the hardcoded version of the password would allow a connection to the database. I changed the password for the database to one containing only upper and lowercase characters and numbers and it would connect fine and the blog would work. The problem was not quite this easy however. It also depended on whether I was logging in as ""user""@""localhost"" or ""user""@""127.0.0.1"". Both users existed on the mysql database and both had the same GRANTS. I tested the mysqli_real_connect() call independent of WordPress using the CONSTANT declarations from wp-conf.php pasted into the php manual reference code for this call and got similar results. I had moved the blog to a server running FreeBSD 12, apache 2.4, php 7.4, and mysql 5.7 when this problem started. I am currently running WordPress 5.2.9 running Arclite theme." kellnerp Candidates for Closure 43893 The maybe_create_table() function has two definitions Database low normal Awaiting Review defect (bug) new dev-feedback 2018-04-28T14:22:56Z 2020-09-01T16:05:56Z "The `maybe_create_table()` function has two definitions: 1. https://github.com/WordPress/wordpress-develop/blob/c71a898f784d8435c07bcf9ec9e30560dd3abe19/src/wp-admin/includes/upgrade.php#L2187-L2219 2. https://github.com/WordPress/wordpress-develop/blob/c71a898f784d8435c07bcf9ec9e30560dd3abe19/src/wp-admin/install-helper.php#L40-L70 The latter is contained within a `function_exists()` check, but this still means the function's behaviour can differ depending on which definition happens to load. The function is not used at all in WordPress core. It (they?) should probably be deprecated." johnbillion Candidates for Closure 56533 dbDelta() ignores changes to column nullability (NULL/NOT NULL) Database 6.0.2 normal major Awaiting Review defect (bug) new reporter-feedback 2022-09-08T12:04:42Z 2022-09-08T12:32:28Z "`dbDelta()` ignores changes to the nullability of columns. Test case: {{{#!php query(""DROP TABLE dbdelta_bug_test""); $wpdb->query($create); require_once ABSPATH . 'wp-admin/includes/upgrade.php'; dbDelta($update); }}} **Expected result:** Column `value` is nullable. **Actual result:** `dbDelta()` doesn't detect any changes to the table. " matt_fw Candidates for Closure 40779 function_exists() audit in wp-db.php Database low minor Awaiting Review defect (bug) new dev-feedback 2017-05-16T14:47:06Z 2021-06-01T12:50:59Z "A few `function_exists()` checks can be removed from `wp-db.php`, thanks to recent load order changes, probably nobody having looked in a while, and generally being afraid to break DB drop-ins: All of these functions are in files now loaded ahead of `require_wp_db()`: * `is_multisite` (load.php) * `_deprecated_function` (functions.php) * `mb_strlen` (compat.php) Bonus: * `mb_check_encoding()` could *maybe* be a new function in compat.php (it's used 3 times in core, using `@` or `function_exists`) ---- The only caveats are the installation process, and 10+ year-old HyperDB versions that do the old-school deep-integration thing, of directly including files – they'll need extra scrutiny." johnjamesjacoby Candidates for Closure 51097 post cannot be saved due to certain variant of Japanese Kanji Database 5.5 normal normal Awaiting Review defect (bug) new close 2020-08-21T21:33:08Z 2020-08-31T08:09:56Z "When I would like to save draft, wordpress failed with error message ""更新に失敗しました。 データベース内の投稿を更新できませんでした。"" (i.e. could not update a post in database). I figured out a variant of Kanji caused the problem. By mistake I used unordinary variant ""𠮟責"" instead of ordinary one ""叱責"". the unordinary one caused the trouble. " mogami74 Candidates for Closure 55206 wp core api memory leaks Database normal normal Awaiting Review defect (bug) assigned dev-feedback 2022-02-20T05:37:43Z 2022-04-29T04:44:55Z "I've experienced the following two memory leaks in WP core. One involves $wpdb when `SAVEQUERIES` is defined truthy, and the other involves `$wp_object_cache` growing as a consequence of calling core api functions that themselves save to the object cache. Both have happened for me in cases where I'm doing large batch processing involving thousands or tens of thousands of posts. I've had memory usage exceed 512MB and cause crashes. I'm including unit tests here showing each memory leak and also the fix that I've used to prevent the memory leak and keep my batch jobs running. {{{#!php queries particularly has a tendency to blow up. */ class WP_Memory_Leak_Tests extends WP_UnitTestCase { /** * This tests a condition which exposes a memory leak in the WPDB class. * If 'SAVEQUERIES' is defined as truthy, then the $wpdb->queries property * can grow indefinitely. */ public function test_WPDB_Memory_Leak() { // Once a constant is defined, it can't be undefined, it's often defined in dev or staging environments. define( 'SAVEQUERIES', true ); // I'll just start my cron job to read the import file I've got. It's // got a decent number of records. $number_of_records = 1000; global $wpdb; $memory = memory_get_usage( true ); $peak = memory_get_peak_usage( true ); foreach ( [ 'first', 'second' ] as $pass ) { // first pass through, we'll apply a fix for this memory leak. // second pass through, we'll bypass the fix and the tests will fail. for ( $i = 1; $i <= $number_of_records; $i ++ ) { if ( 'first' === $pass ) { $wpdb->queries = []; } // for this test, we'll do direct calls to $wpdb $wpdb->query( $wpdb->prepare( ""SELECT * FROM $wpdb->posts WHERE ID = %d"", $i ) ); } $this->assertEquals( $memory, memory_get_usage( true ), ""$pass pass"" ); $this->assertEquals( $peak, memory_get_peak_usage( true ), ""$pass pass"" ); } } /** * This tests a condition which exposes a memory leak in wp cache API. If * a large batch job attempts to do a lot of something that ends up caching * things ( like, for example, get_post or wp_insert_post ), then unless * the cache is flushed regularly, the memory usage grows indefinitely. */ public function test_WP_Cache_Memory_Leak() { // I'll just start my cron job to read the import file I've got. It's // got a decent number of records. $number_of_records = 1000; global $wpdb; $memory = memory_get_usage( true ); $peak = memory_get_peak_usage( true ); foreach ( [ 'first', 'second' ] as $pass ) { // first pass through, we'll apply a fix for this memory leak. // second pass through, we'll bypass the fix and the tests will fail. for ( $i = 1; $i <= $number_of_records; $i ++ ) { if ( 'first' === $pass ) { wp_cache_flush(); } // Because our last test defined 'SAVEQUERIES', we need to // always apply this fix, otherwise that memory leak manifests. // With us doing a core API function `wp_insert_post`, the number // of queries is quite large and memory __really__ grows. $wpdb->queries = []; // let's say we're inserting posts, maybe from an excel file. // this caches some things, so $wp_object_cache grows. wp_insert_post([ 'post_type' => 'post', 'post_title' => ""post $i"", 'post_content' => ""pass $pass"" ]); } $this->assertEquals( $memory, memory_get_usage( true ), ""$pass pass"" ); $this->assertEquals( $peak, memory_get_peak_usage( true ), ""$pass pass"" ); } } } }}} " sllimrovert Candidates for Closure 59172 wp_query meta_query NOT EXISTS not creating correct sql query Database 6.3 normal normal Awaiting Review defect (bug) new reporter-feedback 2023-08-23T12:53:28Z 2023-09-05T16:55:37Z "With the following wp_query: {{{#!php ""ids"", ""post_type"" => ""shop_order"", ""post_status"" => array('wc-processing'), ""posts_per_page"" => ""-1"", ""offset"" => ""0"", 'meta_query' => array( 'relation' => 'AND', array( 'key' => 'scg_is_glass', 'value' => ""1"", ), [ 'relation' => 'OR', [ 'key' => 'scg_processed', 'value' => ""0"", 'compare' => '=', ], [ 'key' => 'scg_processed', 'value' => ""0"", 'compare' => 'NOT EXISTS', ], ] ), 'date_query' => array( array( 'after' => date('Y-m-d', strtotime('-1 month')), ), ), ); }}} The outputeed sql query is the following: SELECT wp_posts.ID FROM wp_posts LEFT JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) LEFT JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id ) LEFT JOIN wp_postmeta AS mt2 ON ( wp_posts.ID = mt2.post_id AND mt2.meta_key = 'scg_processed' ) WHERE 1=1 AND (wp_posts.post_date > '2023-07-23 23:59:59') AND ( ( wp_postmeta.meta_key = 'scg_is_glass' AND wp_postmeta.meta_value = '1' ) AND ( ( mt1.meta_key = 'scg_processed' AND mt1.meta_value = '0' ) OR **mt2.post_id IS NULL** ) ) AND wp_posts.post_type = 'shop_order' AND ((wp_posts.post_status = 'wc-processing')) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC If you looked at the bolded (mt2.post_id IS NULL) above you would expect it to be: **( mt2.meta_key = 'scg_processed' AND mt2.meta_value IS NULL )**" anteatersa Candidates for Closure 44163 wpdb->update() fails, showing no error message Database normal normal Awaiting Review defect (bug) new reporter-feedback 2018-05-20T18:54:05Z 2018-05-21T13:52:46Z "It's my second report about the incompletness of wpdb->update function. 1-2 years ago I've reported the scenario when it failed to update, but `last_error` was empty. Now, another scenario, when it fails , but `last_error` is empty, is the following: i.e. create a table with ""NOT NULL"" {{{#!php query(""CREATE TABLE IF NOT EXISTS `example_table` ( `rowID` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(150) NOT NULL, `id` varchar(150) NOT NULL, `data` LONGTEXT NOT NULL DEFAULT '', PRIMARY KEY (`rowID`), UNIQUE KEY `rowID` (`rowID`) ) "". $wpdb->get_charset_collate() ."" AUTO_INCREMENT=1;"" ); }}} then insert some empty value: {{{ $wpdb->insert(""example_table"", array(""name"" => ""James"", ""id""=>""123"", ""data""=>""blabla"") ); }}} then try to update {{{ $wpdb->update(""example_table"", array(""name""=>""Jonathan""), array(""id""=>""133"") ); }}} it doesnt update. I have dumped `$this->dbh` (in wp-db.php 1887 line), and it shows affected lines: 0 `last_error` is empty. After spending much time, found that I was missing ""required"" parameter and added {{{ $wpdb->update(""example_table"", array(""name""=>""Jonathan"", ""data""=>""new-blabla""), array(""id""=>""133"") ); }}} (""NOT NULL"" requirement caused failure) so, it's good, last_error contained some information, when affected lines are 0, or like such scenarios." tazotodua Candidates for Closure 55115 Change deprecated wpdb::escape() method Database normal normal Awaiting Review enhancement new close 2022-02-08T15:47:19Z 2022-02-12T15:46:30Z "the function wpdb::escape( string|array $data ) is depreciate as per documentation [https://developer.wordpress.org/reference/classes/wpdb/escape/], but it's still using depreciated function such as wpdb::_weak_escape and itself. I think we shouldn't use depreciated functions since charity begins at home." azouamauriac Candidates for Closure 51769 Full-text search support Database normal normal Awaiting Review enhancement new dev-feedback 2020-11-13T12:18:27Z 2022-08-16T12:16:23Z "Let's talk about first-class fulltext search support in WP core. Is there any reason not to support FULLTEXT search in WordPress core for newer MySQL versions with the current search mechanism serving as a fallback for older MySQL versions? MySQL supports FULLTEXT indexes for MyISAM tables since version 3.23.23 and for InnoDB tables since 5.6. As @TimothyBlynJacobs noted, WordPress supports versions back to MySQL 5.0: https://github.com/WordPress/wordpress-develop/blob/master/src/wp-includes/version.php src/wp-includes/version.php As @desrosj noted: * The recommended versions are MySQL >= 5.6/MariaDB >= 10.1 as per https://wordpress.org/download/. * According to the WordPress stats page, a considerable amount of sites use MySQL 5.5 (35%), and some still use 5.1/5,0. Raising support to >= 5.6 is not really an option. The last discussion about that feature I found was from 10 years ago so maybe it's good time to re-evaluate: https://core.trac.wordpress.org/ticket/14722 " zieladam Candidates for Closure 37692 Introduce WP_Database_Table base class Database normal normal Awaiting Review enhancement new dev-feedback 2016-08-17T13:23:28Z 2017-03-15T17:20:50Z "I've always thought it odd that WordPress only versions blogs, and not each individual database table. On one hand, it's great that the schema changes rarely enough that WordPress core would not get a lot of use out of it. On the other, many plugins would benefit pretty hugely from a smart base class that encapsulated a lot of the procedural work of having custom database tables and maintaining a schema. BuddyPress, for example, comes with several object & metadata pairs, for groups, activity, friends, profiles, messages, notifications, etc... It currently takes WordPress's approach of having a big-dumb installer and a bunch of tangled together upgrade routines. I'd love it if each component could manage it's own schema on the fly, with it's own upgrade routines and database table classes to separate the responsibilities, but without needing to setup `admin_init` hooks and `version_compare()` checks for each component. Django has something similar currently, as do other open-source projects like Piwik, GitLab, Mattermost, etc... ---- I'm imagining that each core database table would extend the `WP_DB_Table` class, each with their own `db_version` and their own methods for upgrading to newer versions. Global tables (like `wp_users`) would use `site_id` `-1` in the `wp_sitemeta` database table to distinguish them as global, and not per-network or per-site. ---- This way, when a plugin like WooCommerce wants to introduce new database tables, they just extend the base class, pass in an array of column-keys & attributes, and the base class would handle the `$wpdb` table registration and all of the other bits and bobs. Eventually... eventually it could get paired up with some kind of a `WP_Base_Query` class to automatically handle cache-key assignments, and generate basic crud methods based on the parameters in the associated `WP_Database_Table` extension. ---- I think this becomes particularly useful in REST applications, where WordPress's APIs can be used and extended for any manner of scalable data storage outside of the core database schemas. Obviously this is a huge idea with lots of moving parts, and without a core need ideas like this are pretty slow on the go. I am already starting to do something similar in my own plugins though - just without the base class - and it feels much easier to maintain each plugin knowing there is a similar convention between them. See: https://code.flox.io/stuttter/wp-site-aliases/blob/master/includes/class-wp-site-aliases-db-table.php" johnjamesjacoby Candidates for Closure 39826 LIMIT clause not required in get_options function as there's already a UNIQUE KEY on option_name Database 4.7.2 normal normal Awaiting Review enhancement new dev-feedback 2017-02-09T23:41:05Z 2020-05-07T19:31:10Z "The Core get_options (wp-includes/option.php) uses LIMIT 1 to retrieve only a single row for an option. However, the option_name field already has a UNIQUE KEY in the table schema, so it's impossible for the db to contain or return more than one row for any option_name. Barring history I'm unaware of (if there used to not be a UNIQUE KEY on this field in the past), it's safe to remove the LIMIT 1 clauses from these queries (there are a few instances in the function. While LIMIT clauses can sometimes be harmful to performance, in this particular scenario that's not the case. However, it's just superfluous." arjenlentz Candidates for Closure 52253 New filter on wp-db.php Database normal normal Awaiting Review enhancement new reporter-feedback 2021-01-08T05:03:34Z 2021-01-08T18:54:25Z "I need to filter the database returned rows for a plugin I am developping. I cannot accomplish the functionality with current filters. I think that other devs may benefit from these couples of filters I propose. These lines of code placed in substitution of lines 2001 to 2006 of wp-db.php do the job. {{{ $this->last_result[ $num_rows ] = $row; /** * Filters the database returned row in a mysqli context. * * @since 5.6.1 * @param object $row Database fetched row. * @param string $query Database query. */ $row = apply_filters( 'returned_row_mysqli', $row, $query ); $num_rows++; } } elseif ( is_resource( $this->result ) ) { while ( $row = mysql_fetch_object( $this->result ) ) { $this->last_result[ $num_rows ] = $row; /** * Filters the database returned row in a mysql context. * * @since 5.6.1 * @param object $row Database fetched row. * @param string $query Database query. */ $row = apply_filters( 'returned_row_mysql', $row, $query ); $num_rows++; }}} Thanks in advance." guelben Candidates for Closure 49028 Provide functionality to suppress $wpdb query filter Database 5.4 normal normal Awaiting Review enhancement new reporter-feedback 2019-12-18T12:41:59Z 2020-02-08T13:52:34Z "The custom query class `wpdb` methods (`get_col`, `get_car`, `update`, `delete`...) all use the underlying `query` method which (since WP 2.1) has a filter hook 'query' which allows modification of the sql query. Some queries are specific to the internals of a plugin's functionality and are not meant to be exposed to other plugins/functionality, however, currently, there is no mechanism to suppress this filter. Would it make sense to add a boolean flag on the `$wpdb` instance which would allow developers to suppress any query modification? Something like `$wpdb->suppress_filters` which would be set to false by default." aurovrata Candidates for Closure 59639 Dedicated tables for media libary Database normal major Awaiting Review feature request new dev-feedback 2023-10-16T20:29:09Z 2023-10-16T20:29:09Z "I suggest to use a dedicated media library table in the database like (wp_media, wp_media_meta) to improve performaces for sites that have many media with many posts. This would lead to an overall improvement in the site's performance." sh1zen Tickets Needing Feedback 43578 Unexpected MYSQL data format Database 4.9.4 normal normal Future Release defect (bug) new dev-feedback 2018-03-19T19:36:00Z 2020-02-03T18:01:45Z "When I use field `user_id` in `$wpdb->insert` it set value to Integer, but the table I add data into has `user_id` text field. It works normally only if `format` parameter specified. Example: {{{#!php query(""CREATE TABLE {$wpdb->prefix}_test (`id` INT, `user_id` VARCHAR(16))""); $wpdb->insert(""{$wpdb->prefix}_test"", ['id' => 1, 'user_id' => 'stringKey']); print_r($wpdb->get_row(""SELECT * FROM {$wpdb->prefix}_test WHERE id = 1"")); }}} Result: `stdClass Object ( [id] => 1 [user_id] => 0 )`" loranrendel Tickets Needing Feedback 12257 wpdb Scales Badly Due to Unnecessary Copies of All Query Results Database normal critical defect (bug) assigned dev-feedback 2010-02-17T03:08:06Z 2019-06-04T19:21:45Z "While working on #11726, I encountered a reproducible crash in wpdb::query() The following code causes memory exhaustion on large result sets: {{{ while ( $row = @mysql_fetch_object($this->result) ) { $this->last_result[$num_rows] = $row; $num_rows++; } }}} The memory exhaustion message is error-controlled, causing a white screen of death even in debug mode. I searched wp-db.php for references to $this->last_result, and I found no justification for these object reference copies. $this->last_result '''should''' be maintained as a MySQL resource and properly optimized using the MySQL client layer instead of this PHP nonsense. Tagging for dev-feedback to discuss which Milestone is appropriate." miqrogroove Tickets Needing Feedback 46947 ‘❤’ in Comment Generates DB Error Database 5.1.1 normal normal Future Release defect (bug) new reporter-feedback 2019-04-16T13:02:03Z 2019-04-17T11:51:53Z "I got the following error message notice when a post comment included the ‘’ special character. When I approved the comment, there was no further error message generated. I have not seen this message before – but then again, we rarely get special character in comments. I am using WordPress 5.1.1. The WP-config: ‘define(‘DB_CHARSET’, ‘utf8′);’ Can someone please advise how to fix this? Regards and thanks, Angus Error message: *** [Thu Mar 21 02:17:57.574584 2019] [php7:notice] [pid 24789] [client 68.40.22.245:41759] WordPress database error Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,COERCIBLE) for operation ‘=’ for query SELECT comment_ID FROM xxxcomments WHERE comment_post_ID = 134978 AND comment_parent = ‘0’ AND comment_approved != ‘trash’ AND ( comment_author = ‘Rhonda Lott’ AND comment_author_email = ‘xxx@yahoo.com’ ) AND comment_content = ‘Amen And Amen! Thank you Rabbi!\xe2\x9d\xa4\xf0\x9f\x94\xa5\xf0\x9f\x92\xaf\xf0\x9f\x99\x8c’ LIMIT 1 made by wp_handle_comment_submission, wp_new_comment, wp_allow_comment, referer: https://www.hiskingdomprophecy.com/its-time-to-unearth-the-truth/ ***" HisKingdomProphecy Tickets Needing Feedback 15499 Add an index for get_lastpostmodified query OllieJones Database 3.0.1 normal normal Future Release enhancement reviewing dev-feedback 2010-11-19T18:20:31Z 2023-09-27T15:38:07Z "I had a friend (Jools Wills) look over a WordPress site recently, to get a fresh view on what might be optimised, and he noticed a query which might benefit from an additional index on `WP_Posts`. The query `SELECT post_modified_gmt FROM $wpdb->posts WHERE post_status = 'publish' AND post_type = 'post' ORDER BY post_modified_gmt DESC LIMIT 1` in `get_lastpostmodified` is run for last modified date in GMT, and currently doesn't use an index. This SQL is run whenever certain types of feed are requested as far as I can see. We added `CREATE INDEX type_status_modified ON wp_posts (post_type, post_status, post_modified_gmt);` and `CREATE INDEX type_status_modified_no_id ON wp_posts (post_type, post_status, post_date_gmt);` and the query runs a lot faster now. The following timings were taken running the first query (`post_modified_gmt`) on a 36,362 row posts table. Note that it doesn't use filesort after the index has been added. ''Before:'' {{{ mysql> EXPLAIN SELECT post_modified_gmt FROM slgr_posts WHERE post_status = 'publish' AND post_type = 'post' ORDER BY post_modified_gmt DESC LIMIT 1; +----+-------------+------------+------+------------------+------------------+---------+-------------+-------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+------+------------------+------------------+---------+-------------+-------+-----------------------------+ | 1 | SIMPLE | slgr_posts | ref | type_status_date | type_status_date | 124 | const,const | 24718 | Using where; Using filesort | +----+-------------+------------+------+------------------+------------------+---------+-------------+-------+-----------------------------+ 1 row in set (0.03 sec) }}} * 0.21290683746338ms * 0.25690102577209ms * 0.230553150177ms * 0.2274341583252ms * 0.23083996772766ms ''After:'' {{{ mysql> EXPLAIN SELECT post_modified_gmt FROM slgr_posts WHERE post_status = 'publish' AND post_type = 'post' ORDER BY post_modified_gmt DESC LIMIT 1; +----+-------------+------------+------+---------------------------------------+----------------------+---------+-------------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+------+---------------------------------------+----------------------+---------+-------------+-------+-------------+ | 1 | SIMPLE | slgr_posts | ref | type_status_date,type_status_modified | type_status_modified | 124 | const,const | 24718 | Using where | +----+-------------+------------+------+---------------------------------------+----------------------+---------+-------------+-------+-------------+ 1 row in set (0.00 sec) }}} * 0.00082707405090332ms * 0.00072288513183594ms * 0.00074386596679688ms * 0.00066494941711426ms * 0.00066208839416504ms In `get_lastpostmodified` both these queries are run, so the total savings in my case on a quiet server are nearly 0.5 seconds... worth having, I reckon. I've not created a patch for schema changes before, but I think the only place the change would need to go would be `scheme.php`? Suggested patch attached." simonwheatley Tickets Needing Feedback 18315 Add an index to the GUID column in the posts table Database 3.2.1 normal normal enhancement reopened dev-feedback 2011-08-02T04:31:01Z 2019-06-04T19:22:38Z "Running queries on the GUID column in the posts table is slow because the column is not indexed. The attached patch adds an index. Note, this affects ticket #18286 - I will update that ticket with appropriate patches to reflect this request." alexkingorg Tickets Needing Feedback 54042 Extending wpdb::prepare() to support IN() operator Database normal normal Future Release enhancement new dev-feedback 2021-08-31T14:23:26Z 2023-09-17T10:53:12Z "wpdb::prepare() helps avoid SQL Injection vulnerabilities, by escaping most variables correctly. WP 6.1 added support for Identifiers (table/field names) with `%i`, in #52506. But it's also fairly common to make a mistake to include values with the `IN()` operator, for example: {{{#!php prepare('WHERE id IN (%...d)', $ids); }}} Where `%...d` or `%...s` would safely (and easily) include a comma separated array of integers or strings - taking the idea of using '...' for variadics in PHP. https://wiki.php.net/rfc/variadics https://www.php.net/manual/en/functions.arguments.php#functions.variable-arg-list https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#operator_in" craigfrancis Tickets Needing Feedback 20634 dbDelta is unforgiving about field declarations Database 1.5 normal normal enhancement new dev-feedback 2012-05-08T03:27:05Z 2019-06-04T19:23:05Z "the variable type is case sensitive: int(22) != INT(22) the mysql type BOOL or BOOLEAN comes back from the db as tinyint(1): tinyint(1) != BOOLEAN Not a huge issue, just annoying. Makes dbDelta fire off unnecessary sql. " SidHarrell Tickets with Patches 13103 """Unknown character set"" during install into database reports successful install." barrykooij Database 3.0 normal normal defect (bug) assigned has-patch 2010-04-24T15:10:23Z 2019-06-04T19:21:49Z "During editing of the wp-config.php, I changed {{{ define('DB_CHARSET', 'utf8'); }}} to {{{ define('DB_CHARSET', 'utf8_general_ci'); }}} Which I thought was valid. After clicking ""Install Wordpress"" I got a page full of: {{{ WordPress database error: [Unknown character set: 'utf8_general_ci'] }}} And at the bottom, ""Success"" with a login prompt, but nothing was actually created in the database. I think WP shouldn't report a successful install if the user happened to accidentally change this value (or another) during install as it would cause confusion. I used a svn checked out version of WP on 2010-04-24 on my local computer using PHP5 and apache2, but I think this is really a problem since 2.6 (or when the current install system was implemented)." MECU Tickets with Patches 54801 Avoid WPDB errors when examining installation state of a site/network Database 3.0 normal normal Future Release defect (bug) new has-patch 2022-01-12T15:15:04Z 2022-09-27T19:12:20Z "When trying to discover whether a given site is already installed properly or still needs to have its installation routine triggered, the queries that are executed by default generate a two WPDB errors on empty sites that cannot easily be avoided: - Enumeration of site IDs triggers an error for the missing `wp_blogs` table; - Setting of the site name property triggers an error for the missing `wp_sitemeta` table. [[Image(https://p141.p3.n0.cdn.getcloudapp.com/items/xQuzDx5J/af6ecbf1-e74a-4237-82d6-47cb0b18fd26.jpeg?v=d5c251cf4ad17f9c808bdf8394034636)]] This is an operation that is commonly done via WP-CLI, and while the errors do not change the outcome (i.e. the check will fail either way), they do fill up the logs unnecessarily for an operation that should be safe to do. ~~I'd suggest doing a quick check on these fundamental operations to see if the tables are present before manipulating them.~~ ~~**Performance considerations**~~ ~~The suggested fix (see PR that will follow) adds two DB requests which, even though they should be very cheap, should be properly cached to ensure no important negative performance impact.~~ ~~The alternative would be to change the logic in WPDB so that it can be silenced for operations that are known to be failing in an expected manner. However, that seems like a major change throughout multiple parts of the code.~~ ~~I'm happy to discuss alternative ways of fixing this, as I'm not too happy with introducing two DB requests like that.~~ I suggest using `$wpdb->suppress_errors()` around the two problematic calls to avoid showing these errors." schlessera Tickets with Patches 38921 Defining filter pre_get_table_charset causes errors Database 4.2 normal normal Future Release defect (bug) new has-patch 2016-11-23T23:17:46Z 2020-05-27T08:29:22Z "In wp-db.php::get_table_charset(), a filter is defined, '''pre_get_table_charset'''. However, if this filter is defined, wp-db.php::get_col_charset() throws errors. This is because the latter assumes that it can find cached results cached in: {{{ $this->table_charset[]; }}} The assumption seems to be that if you add the '''pre_get_table_charset''' filter, you will also define the '''pre_get_col_charset''' filter. Code reference: [https://github.com/WordPress/WordPress/blob/master/wp-includes/wp-db.php#L2437] Note: defining this filter lets you hard-code the charset of tables, rather than doing a database lookup every time. This can be done quite safely because the most recent upgrade forced our DB tables to be '''utf8mb4'''." haoran Tickets with Patches 40088 Stop creating the `wp_links` database table Database 3.5 normal normal Future Release defect (bug) new close 2017-03-09T20:29:37Z 2022-08-10T19:19:35Z "In WordPress 3.5 (2012) the Links feature was disabled by default in new WordPress installations, and was hidden for old installations that had no links. See #21307. Every new WordPress site since then (including multisite) has continued to create an empty `wp_links` database table for a feature that was sunsetted 5 years ago." johnjamesjacoby Tickets with Patches 40695 `install_blog` suppresses database errors Database normal normal Future Release defect (bug) new has-patch 2017-05-08T20:18:03Z 2017-06-01T19:19:28Z "`install_blog` uses error suppression and `DESCRIBE $table` to detect the absence of tables. Error suppression can be removed in favor of `SHOW TABLES LIKE $table`, as in the patch on #40694. Another source of errors in `install_blog` is the call to `get_blogaddress_by_id`. This attempts to read the site's `home` attribute which hits the magic `WP_Site->__get`, which ultimately leads to a `SELECT` against the non-existent options table." andy Tickets with Patches 34872 dbDelta Missing Index Name Creates Duplicate Indexes Database 3.5.1 normal normal defect (bug) new has-patch 2015-12-06T18:00:25Z 2019-06-04T19:33:14Z "Reference ticket #10404. This is to decompose the original ticket into components. May be fixed in 4.4. Needs testing. ---- Creates duplicate indexes: {{{ create table x ( id mediumint(8) not null autoincrement, KEY (id) ) }}} Does not create duplicate indexes: {{{ create table x ( id mediumint(8) not null autoincrement, KEY id (id) ) }}} " charlestonsw Tickets with Patches 40694 dbDelta uses suppressed errors to detect table absence Database normal normal Future Release defect (bug) new has-patch 2017-05-08T20:00:36Z 2019-12-01T08:38:14Z "In dbDelta, `$tablefields` is populated by the statement `DESCRIBE {$table}`. When the table does not exist, this statement fails with an error. This statement is executed with error suppression enabled. Handling of suppressed errors is customizable in database drop-ins. In one large case, suppressed errors are logged for analysis. These suppressed `DESCRIBE` errors are considered a bug because it's possible to check for the presence of a table without errors by using a `SHOW TABLES LIKE` statement. It would be preferable to leave error suppression out of dbDelta and use the `SHOW TABLES LIKE` statement, as in the attached patch." andy Tickets with Patches 37508 wpdb->result instance should be checked `mysqli_num_fields` in `load_col_info()` Database 3.9 normal normal defect (bug) new needs-unit-tests 2016-07-29T07:33:53Z 2019-06-04T19:40:32Z "In `wpdb::load_col_info()` the function `mysqli_num_fields()` is called passing `$this->result` without any check on this property value. The problem is that function has a type hint to `mysqli_result` (see http://php.net/manual/en/mysqli-result.field-count.php), but `$this->result` may be `false`. Just like in other places in the same class, the function should check that `$this->result instanceof mysqli_result` before calling `mysqli_num_fields()`." giuseppe.mazzapica Tickets with Patches 31624 $wpdb->prepare() named placeholders Database 4.2 normal normal enhancement new needs-unit-tests 2015-03-13T08:36:52Z 2023-06-22T07:55:03Z "I think it would be handy to add named placeholders to $wpdb->prepare(). The functionality exists in most modern frameworks and cuts out the need for having to worry about the order of variables, (or repetition) in the current vsprint like syntax. What I'm proposing is that the second parameter of prepare() can optionally be an associative array where the $key is the named placeholder and the $value is the value associated with it. This wont affect any existing functionality of prepare() and is fully backwards compatible. If no associative array is passed it will continue to work as always. Patch with the described functionality is attached for testing." ozthegreat Tickets with Patches 54642 Add prepared query builder support for `$wpdb` to build prepared queries across multiple location. Database normal normal Future Release enhancement new has-patch 2021-12-17T09:07:50Z 2022-09-19T20:29:29Z "This requests scopes adding a query builder like API to WordPress, so that queries can be built across multiple functions, without needing to disable any PHPCS sniffs. Currently, if the query is not completely being built nearby, developers will eventually have to disable phpcs sniff, since they would need to combine or interpolate queries. Some examples of this are present in WP core as well, such as in the [https://github.com/WordPress/wordpress-develop/blob/954e9c153fe9d1b4f74d76bab9dd5175fd36cebc/src/wp-includes/class-wp-meta-query.php#L668 meta query], [https://github.com/WordPress/wordpress-develop/blob/954e9c153fe9d1b4f74d76bab9dd5175fd36cebc/src/wp-includes/taxonomy.php#L1585 taxonomy] etc. Although WP does a great job generally to avoid disabling phpcs, in the general plugin ecosystem, these examples are more prevalent. Note that most of the examples of disabling phpcs can be refactored to not needing to disable. However, as far as I can tell, building a query this way (across multiple functions/places) is not considered a bad practice, if this assumption is correct then we should support doing it better. This eventual disabling of PHPCS is risky and removes a major and effective protective layer. == Solution The solution for WordPress would be to provide an API where developers can build a prepared query in steps, and join them before executing in a safe manner. I looked at query builders for various ORMs such as Doctrine (Symphony), ActiveRecord (Rails), Laravel etc. While such extensive APIs would likely be overkill for WordPress, one possible solution is this hopefully simpler API that I am proposing in [https://github.com/WordPress/wordpress-develop/pull/2062 this PR 2062]. This PR adds a new class called `WP_DB_Partial_Query` which implements `__toString()` magic method so that it can be interpolated in sprintf calls. The objects of this class will represent a partial query and a new method `$wpdb->prepare_partial()` which will create and return these objects. This `$wpdb->prepare_partial()` method internally calls `$wpdb->prepare()` and takes exactly the same arguments. This PR also introduces a new placeholder `%q` (although this isn't necessary and `%s` can be used as well) which can be used to pass these prepared partial queries. I have added some examples to use the API in [https://github.com/vedanshujain/wordpress-develop/pull/1 this PR on my fork]. Note that this is only a PoC and implementation is not complete. Specifically, along with tests, a major component missing is to match against `%q` placeholders and quote the args if they are not objects of the `WP_DB_Partial_Query` class. I can polish the PoC more to get in an acceptable state if this is a direction that WordPress is interested to explore at all and there are no obvious problems with the general approach that I have missed. " vedjain Tickets with Patches 58344 Connect to database on first query Database normal normal Future Release enhancement new has-patch 2023-05-17T13:55:48Z 2023-09-14T03:37:54Z "Currently there is the database connection is made in `__construct` method by calling `db_connect` method. However with sites with persistent object caching enabled, this connection to the database may not be needed. It is common now for requests to have no database queries at all if persistent object caching is setup. Moving the `db_connect` call to the `query` method like this. {{{#!php public function query( $query ) { if ( ! $this->has_connected ) { $this->db_connect(); } if ( ! $this->ready ) { $this->check_current_query = true; return false; } }}} " spacedmonkey Tickets with Patches 28625 Enhancement: Add constants to support SSL connections for mysqli Database 4.0 normal normal Future Release enhancement assigned needs-unit-tests 2014-06-24T22:39:12Z 2023-06-24T12:22:46Z "In order to support SSL'ed MySQL connections with the `mysqli_*` functions introduced in WordPress 3.9 / PHP 5.5 `mysqli_ssl_set()` must be called to set the path to the SSL certs and keys to be used by the MySQL client before `mysqli_real_connect()` is called. We should add the following optional constants to allow for users to configure secure connections to the database. * `MYSQL_SSL_KEY` * `MYSQL_SSL_CERT` * `MYSQL_SSL_CA` * `MYSQL_SSL_CA_PATH` * `MYSQL_SSL_CIPHER` In addition this should only be set if the feature flag `MYSQLI_CLIENT_SSL` is set for the existing `MYSQL_CLIENT_FLAGS` constant." hypertextranch Tickets with Patches 26788 Improve dbDelta fields match Database 3.8 normal normal enhancement reopened has-patch 2014-01-07T12:12:19Z 2019-06-04T19:24:49Z Sometimes plugin authors use multiple spaces between the field name and type rather than a single space when using dbDelta, small change to the regex to stop this producing unnecessary alter queries. pollett Tickets with Patches 31018 Persistent database connections with mysqli Database 4.2 normal normal enhancement reopened needs-unit-tests 2015-01-14T22:37:39Z 2022-08-01T03:57:30Z "WordPress currently does not allow support for persistent database connections. This can be accomplished by prepending ""p:"" to the hostname with mysqli however with its current configuration WordPress will be confused by the "":"" and think the hostname is actually a port if specified in the wp-config.php file. This patch add support for a constant that allows persistent connections to be turned on or off. Why should this be added to core? Because persistent connections are useful :P. But really, we have seen requests for this in other tickets such as #27933. Additionally I am involved in a project where 10,000+ sites are requiring persistent db connection, not an insignificant number. Persistent db connections are also needed to ensure proper performance on IIS and Azure installations. In short a couple lines of code is all it takes to ensure WordPress continues to work well across other platforms and project requirements. Props to awoods and bradparbs for helping identify the issue and solution." blobaugh Tickets with Patches 47280 SQL_CALC_FOUND_ROWS is deprecated as of MySQL 8.0.17 johnbillion Database normal normal Future Release enhancement reviewing has-patch 2019-05-15T14:34:03Z 2024-02-22T07:49:16Z "Per https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_found-rows > The SQL_CALC_FOUND_ROWS query modifier and accompanying FOUND_ROWS() function are deprecated as of MySQL 8.0.17 and will be removed in a future MySQL version. As a replacement, considering executing your query with LIMIT, and then a second query with COUNT(*) and without LIMIT to determine whether there are additional rows. This is not yet immediately important because most hosts are on 5.5, or 5.6, rarely 5.7, but given the speed with which trac tickets move that impact very core functionalities, I thought it best to open this ticket to get the work started. This impacts all the 6 places where it's being used, though one of them is in the WP_Query definition." javorszky Tickets with Patches 54346 Slow SQL queries fetching posts from specific categories craigfrancis* Database normal normal Future Release enhancement accepted has-patch 2021-10-30T11:05:53Z 2023-12-11T00:44:48Z "Noticed that REST API queries used in mobile app are very slow and found that such SQL queries can be optimized to be 10 times faster (1.8134 seconds vs. 0.1804 seconds) for wp_posts table having 800k records, see below. Very similar queries are used when displaying posts from specific categories on the web, meaning optimizing this could lead to significant speed up everywhere. The one responsible for this is WP_Tax_Query->get_sql_for_clause function. REST API to fetch posts from specific categories, e.g. `/wp-json/wp/v2/posts/?per_page=10&_embed=1&categories=63545,63546,63547,63548,63549,63552,76287&page=1` executes the following SQL query: {{{ SELECT wp_posts.ID FROM wp_posts LEFT JOIN wp_term_relationships ON ( wp_posts.ID = wp_term_relationships.object_id ) WHERE 1 = 1 AND ( wp_term_relationships.term_taxonomy_id IN ( 63545, 63546, 63547, 63548, 63549, 63552, 76287 ) ) AND wp_posts.post_type = 'post' AND( (wp_posts.post_status = 'publish') ) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 10 }}} Query took 1.8134 seconds. This query can be optimized by using subquery: {{{ SELECT wp_posts.ID FROM wp_posts WHERE wp_posts.ID IN ( SELECT object_id FROM wp_term_relationships WHERE wp_term_relationships.term_taxonomy_id IN ( 63545, 63546, 63547, 63548, 63549, 63552, 76287 ) ) AND wp_posts.post_type = 'post' AND wp_posts.post_status = 'publish' ORDER BY wp_posts.post_date DESC LIMIT 0, 10 }}} Query took 0.1804 seconds seconds. " Krstarica Tickets with Patches 56091 Using %i for table/field names in wpdb::prepare() craigfrancis Database 6.1 low minor Future Release enhancement assigned has-patch 2022-06-28T19:10:52Z 2024-01-29T20:10:29Z "Now `wpdb::prepare()` supports `%i` for Identifiers (e.g. table/field names), via commit [53575], and ticket #52506. Queries within WP Core should use this, to ensure variables are always quoted, and avoid static analysis tools flagging unescaped SQL input (a non-`literal-string`) for the `$query` parameter: {{{#!php prepare( ""SELECT ID FROM $wpdb->posts WHERE post_type = %s"", $post_type ); $wpdb->prepare( ""SELECT ID FROM %i WHERE post_type = %s"", $wpdb->posts, $post_type ); }}} I'll write a patch for the first set, but I suspect there will be a lot of changes, and they should be checked carefully." craigfrancis Tickets with Patches 10404 dbDelta creates duplicate indexes when index definition contains spaces Database 2.8.1 normal normal enhancement reopened has-patch 2009-07-14T10:59:54Z 2023-01-21T16:01:09Z "I was adding a much needed index in wp_object_term_relationships, and testing revealed it was getting added multiple times. This works as intended: {{{ CREATE TABLE $wpdb->term_relationships ( object_id bigint(20) unsigned NOT NULL default 0, term_taxonomy_id bigint(20) unsigned NOT NULL default 0, term_order int(11) NOT NULL default 0, PRIMARY KEY (object_id,term_taxonomy_id), UNIQUE KEY reverse_pkey (term_taxonomy_id,object_id), KEY term_taxonomy_id (term_taxonomy_id) ) $charset_collate;""); }}} This doesn't: {{{ CREATE TABLE $wpdb->term_relationships ( object_id bigint(20) unsigned NOT NULL default 0, term_taxonomy_id bigint(20) unsigned NOT NULL default 0, term_order int(11) NOT NULL default 0, PRIMARY KEY (object_id,term_taxonomy_id), UNIQUE KEY reverse_pkey (term_taxonomy_id, object_id), KEY term_taxonomy_id (term_taxonomy_id) ) $charset_collate;""); }}} the only difference between the two is a space in the reverse_pkey column list. we should remove spaces in there to avoid potential bugs." Denis-de-Bernardy Tickets with Patches 57793 Add native support for SQLite Database normal normal Future Release feature request new has-patch 2023-02-23T07:10:43Z 2024-02-26T12:21:28Z "A few months ago, a proposal was published to natively support SQLite in WordPress Core (https://make.wordpress.org/core/2022/09/12/lets-make-wordpress-officially-support-sqlite/). That proposal outlines a lot of the benefits of supporting SQLite in Core. The proposal has received a lot of positive feedback, both from the community and the WordPress leadership so this Trac ticket is the next step, to promote collaboration and discussion. The performance team has been working on an implementation that adds support for SQLite via the SQLite Database Integration plugin (https://wordpress.org/plugins/sqlite-database-integration/). Before being published as a stand-alone plugin, it was implemented as a module in the Performance Lab plugin (https://wordpress.org/plugins/performance-lab/) where it received a lot of user-testing." aristath Tickets with Patches 15332 dbDelta($query) - do not create view Database 3.0.1 normal normal Future Release feature request reopened needs-unit-tests 2010-11-07T14:23:52Z 2018-07-30T08:45:42Z "during plugin creation I create few tables with dbDelta($query). that is working without problems. But on the end I create two views on this tables with dbDelta($query) - that is not working. The views are not created. The sql is ok, manually the create works." christian_gnoth Unpatched Bugs 35256 $wpdb->insert_id equals 0 Database 4.2.4 normal normal defect (bug) reopened 2015-12-29T19:54:39Z 2019-06-04T19:33:42Z "Hi, reading this https://make.wordpress.org/core/2015/04/02/the-utf8mb4-upgrade/ Ok then cool. But I've had this weird bug with the table posts : collation utf8_mb4 was not set properly for this table (I do not really know why). As a result everytime `$wpdb->insert_id` was used it just inserted posts with ID `0` ! For example, each upload just failed and because an AJAX grid layout is used in admin (media library page) it just failed to load anything ! Took me some time to realize this was related to a database issue and not my php code that uses `wp_insert_post()`. My question is : would it be safer to prevent WordPress from inserting posts with ID `0` in any case ? I'm refering to this line https://core.trac.wordpress.org/browser/tags/4.4/src/wp-includes/post.php#L2926 " jmlapam Unpatched Bugs 26858 Comment duplicate check has a slow query Database 2.0 normal normal Future Release defect (bug) assigned has-patch 2014-01-17T04:38:22Z 2022-11-29T13:38:39Z "When checking for duplicate comments, the query is pretty slow: {{{ SELECT comment_ID FROM wp_comments WHERE comment_post_ID = '1' AND comment_parent = '0' AND comment_approved != 'trash' AND ( comment_author = 'foo' OR comment_author_email = 'foo@bar.com' ) AND comment_content = 'some content' LIMIT 1; }}} This will use the comment_post_ID index, which means it will scan all comments on that post. For a post with thousands of comments, this starts to get slow. Instead, we should change `KEY comment_post_ID (comment_post_ID)` to `KEY comment_post_ID (comment_post_ID,comment_content(255))`, to cover this case. Props [http://www.mysqlperformanceblog.com/2014/01/16/analyzing-wordpress-mysql-queries-query-analytics/ MySQL Performance Blog] for finding this." pento Unpatched Bugs 32868 Consider running utf8mb4 conversion on each database update Database normal normal defect (bug) new 2015-07-03T03:27:55Z 2019-06-04T19:30:29Z "As mentioned in #32127 and elsewhere, we should run the utf8 -> utf8mb4 conversion on each db update. The main reason is that the server environment could change between updates. For example, if someone upgrades MySQL between 4.3 and 4.4's release, at present they'll update to 4.4 and still not have utf8mb4 support, even though WordPress will now be able to support it." dd32 Unpatched Bugs 60037 Differentiate between minimum required MySQL and MariaDB versions Database normal normal Future Release defect (bug) new 2023-12-08T14:13:08Z 2024-02-16T23:23:14Z "Currently, there is only [https://core.trac.wordpress.org/browser/tags/6.4.1/src/wp-includes/version.php#L47 one minimum required version defined] and checked against for database software when determining if a server is capable of installing and running WordPress. It's currently set at `5.0`, though that will hopefully be updated to `5.5` in WordPress 6.5 (see #60036). This has worked over the past 12+ years without issue, even though WordPress supports running either MySQL or MariaDB. This is due to the fact that MariaDB versions previously followed MySQL's numbering scheme with each release representing a fully-functional drop-in replacement. However, in 2012 this changed when MariaDB jumped from version `5.5` to `10.0`, where they have continued to increment from (the current LTS version is `10.11`, and current short term support version is `11.2`). In the near future when support for any version of MariaDB > 5.5 is dropped, this check will become useless, because MySQL 5.7, 8.0, 8.1, etc. will always be lower than 10.0, 10.11, 11.0, etc. An additional angle to this problem is [https://make.wordpress.org/core/2023/04/19/status-update-on-the-sqlite-project/ a recent proposal to add support for SQLite]. The most recent version of that software is `3.44.2`. SQLite is not a drop-in replacement in the same way MariaDB is. But with that and other challenges to adding support aside, no versions of SQLite would work in WordPress today (even with the minimum currently set at `5.0`). The relevant code should be updated to ensure that the correct minimum version for the current database type is used when checking a server is properly equipped to run WordPress. Follow up to #60036." desrosj Unpatched Bugs 52496 Improve MySQL 8.0 support Database 5.4 normal normal Future Release defect (bug) new 2021-02-11T07:11:12Z 2024-02-05T20:45:49Z "While setting up the GH Action for the WP Importer plugin which runs the plugin integration tests (using the WP Core test framework) against various PHP/MySQL/WP combinations, I noticed a number of PHP/MySQL 8.0 combinations which error out during the running of the WP Core test bootstrap with errors which seem to indicate underlying incompatibilities with MySQL 8.0. Relevant files in the Importer plugin setting up the test environment: * https://github.com/WordPress/wordpress-importer/blob/master/phpunit/install.sh * https://github.com/WordPress/wordpress-importer/blob/master/phpunit/bootstrap.php Test run showing the errors: https://github.com/WordPress/wordpress-importer/actions/runs/556887133 == Errors reported: PHP 5.6 / MySQL 8.0 / WP 5.5 and latest (5.6) (Error line numbers are based on latest) {{{ PHP Warning: mysqli_real_connect(): Server sent charset (255) unknown to the client. Please, report to the developers in /tmp/wordpress/wp-includes/wp-db.php on line 1653 PHP Warning: mysqli_real_connect(): (HY000/2054): Server sent charset unknown to the client. Please, report to the developers in /tmp/wordpress/wp-includes/wp-db.php on line 1653 PHP Warning: mysql_connect(): Server sent charset (255) unknown to the client. Please, report to the developers in /tmp/wordpress/wp-includes/wp-db.php on line 1685 PHP Warning: mysql_connect(): Server sent charset unknown to the client. Please, report to the developers in /tmp/wordpress/wp-includes/wp-db.php on line 1685 }}} PHP 7.0 - 7.3 / MySQL 8.0 / WP latest (5.6) {{{ PHP Warning: mysqli_real_connect(): The server requested authentication method unknown to the client [caching_sha2_password] in /tmp/wordpress/wp-includes/wp-db.php on line 1653 PHP Warning: mysqli_real_connect(): (HY000/2054): The server requested authentication method unknown to the client in /tmp/wordpress/wp-includes/wp-db.php on line 1653 }}} **Note:** ''**with the exact same setup, the PHP 7.4/8.0 - MySQL 8.0 - WP 5.6 test runs will pass.**'' == Investigating needed I haven't dug into the code. Reporting it here for further investigation. I imagine that either the PHP/MySQL version check could be adjusted to indicate that MySQL 8.0 is only supported when running on PHP 7.4 or higher. Alternatively, the underlying issues will need to be solved. I also wonder if the WP Core tests in CI are being run against enough PHP/MySQL combinations, as I would imagine that these issues could have been discovered earlier if the above problem combinations would haved been used in WP Core CI. Related: #49344 #51740" jrf Unpatched Bugs 21762 Storing a multidimensional array using register_setting double-serializes subarrays identified with a string sekatsim Database 3.4 normal normal defect (bug) new 2012-08-31T22:43:16Z 2019-06-04T19:23:21Z "Storing a multidimensional array, such as `$options['my-option'][$id] = ""value""`, into the database using register_setting results in the ""my-option"" string getting double serialized. The option_value is stored as: `s:9:""'my-option'"";a:1:{s:9:""option-id"";s:5:""value"";}` The my-option array is encased in both single and double quotes, so that `isset($options['option-id']` evaluates false, but `isset($options[""'option-id'""]` evaluates true." sekatsim Unpatched Bugs 28139 WP MU legacy problems: missing database tables Database 3.0 normal normal defect (bug) new 2014-05-05T19:15:30Z 2019-06-04T19:25:36Z "Helo there. We have found that WordPress Multisite sites updated from former WordPress MU are not working as expected. There are some missing tables that give problems with some plugins. The lack of tables doesn't allow you to access to the network configuration either. === '''Steps to reproduce''' === 1. Install WP MU 2.9.2 (last WP MU release - works as expected) 2. Update to WP 3.0 or directly to WP 3.9. (tested in 3.0, 3.0 and then 3.9, and 3.9 directly) 3. Try to access /wp-admin/network.php. You will have the message `The Network creation panel is not for WordPress MU networks.`, which is odd in a WP 3.9 Multisite. 4. Look for the source of the message in wp-admin/network.php {{{ if ( ! defined( 'MULTISITE' ) ) wp_die( __( 'The Network creation panel is not for WordPress MU networks.' ) ); }}} 5. Add `define ('MULTISITE', true);` to wp-config.php. You will need to repair the database. 6. Add `define('WP_ALLOW_REPAIR', true);` and proceed to repair the database. Assuming a `testing` database, you will get this message: {{{ wp_posts: Table 'testing.wp_posts' doesn't exist wp_comments: Table 'testing.wp_comments' doesn't exist wp_links: Table 'testing.wp_links' doesn't exist wp_options: Table 'testing.wp_options' doesn't exist wp_postmeta: Table 'testing.wp_postmeta' doesn't exist wp_terms: Table 'testing.wp_terms' doesn't exist wp_term_taxonomy: Table 'testing.wp_term_taxonomy' doesn't exist wp_term_relationships: Table 'testing.wp_term_relationships' doesn't exist wp_commentmeta: Table 'testing.wp_commentmeta' doesn't exist }}} === '''Problems found''' === 1. The lack of the `wp_options` table is especially problematic with plugins (Jetpack is unable to work with its new multisite option - thanks kraftbj for the help) 2. The message assumes `wp_links` as a needed table " bi0xid Unpatched Bugs 28591 dbDelta Non-literal DEFAULT not working (CURRENT_TIMESTAMP) Database normal normal defect (bug) new 2014-06-19T19:38:03Z 2019-06-04T19:25:59Z "Using dbDelta and any internal SQL values like CURRENT_TIMESTAMP won't work because dbDelta matches based on this regex: {{{ ""| DEFAULT '(.*?)'|i"" }}} The block current looks like this: {{{ if (preg_match(""| DEFAULT '(.*?)'|i"", $cfields[strtolower($tablefield->Field)], $matches)) { $default_value = $matches[1]; if ($tablefield->Default != $default_value) { // Add a query to change the column's default value $cqueries[] = ""ALTER TABLE {$table} ALTER COLUMN {$tablefield->Field} SET DEFAULT '{$default_value}'""; $for_update[$table.'.'.$tablefield->Field] = ""Changed default value of {$table}.{$tablefield->Field} from {$tablefield->Default} to {$default_value}""; } } }}} I'm not sure what the best solution is for this, but perhaps it should be: 1. Check if there is a default to change, if there is -- store default alter query, don't add to $cqueries[] yet 2. Check if there is a field type change OR a default non-literal found, if there is -- use this CHANGE COLUMN query instead of the 'default' changing query in point 1 Does that sound like a good solution here?" sc0ttkclark Unpatched Enhancements 35109 Add Online DDL support to dbDelta Database normal normal enhancement new needs-unit-tests 2015-12-15T22:24:20Z 2019-06-04T19:33:19Z "Since MySQL 5.1, a bunch of table level operations can be done in an asynchronous manner. We can add support for this by adding `ALGORITHM=INPLACE` to the `ALTER TABLE` query, but the fun part is that this algorithm isn't supported for all types of operations. So, we have a couple of options: * Keep an array of what operations can be done online in each MySQL and MariaDB version, which will need to be updated for each new version of MySQL. * Try all `ALTER` queries with the algorithm flag, then catch any SQL errors and fall back to the old style if needed. This will need careful testing with old versions of MySQL, particularly. https://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html" pento Unpatched Enhancements 38036 Add support for the utf8mb4_0900_ai_ci collation Database normal normal Future Release enhancement new 2016-09-13T10:11:38Z 2023-04-05T10:24:05Z "MySQL 8.0 adds the `utf8mb4_0900_ai_ci` collation, based on the current Unicode Collation algorithm. We should use it when possible. Note that the version check will need to be a little more complex than the `utf8mb4_520` check - MariaDB 10.x doesn't support `utf8mb4_0900_ai_ci`. " pento Unpatched Enhancements 31759 Allow to set $allow_bail when creating new wpdb object to handle connection error silently Database 4.1.1 normal normal enhancement new 2015-03-25T08:49:23Z 2019-06-04T19:28:21Z Class wpdb doesn't allow to set $allow_bail when creating new wpdb object. So there will be always error 500 on connection error. It would be great to have possibility to set $allow_bail at wpdb object creation (by constructor arguments perhaps). szaqal21 Unpatched Enhancements 38760 Capture result size in bytes when SAVEQUERIES is true Database normal normal Future Release enhancement new 2016-11-11T19:49:44Z 2017-10-03T10:06:01Z Inside of `WP_DB::_do_query()`, it would be neat if `$this->result` was measured as size in bytes, so debugging tools had a better sense of how much data was running over the network. danielbachhuber Unpatched Enhancements 45697 Database: Upgrading field collations Database normal normal Future Release enhancement new 2018-12-19T02:54:27Z 2022-08-22T15:43:24Z "[37523] added support for the `utf8mb4_unicode_520_ci ` collation, and #38036 exists to look at adding support for the `utf8mb4_0900_ai_ci` collation. With these variations, it's possible for a WordPress site to be installed, then MySQL is upgraded sometime later. Subsequent table changes (for example, a plugin creating a new table) would use the newer collation, while the original tables would use the older collation. This will cause `JOIN` queries between fields of different collations to fail." pento Unpatched Enhancements 6492 Guids No Longer Have Permalink Format Database 2.5 normal normal Future Release enhancement reopened 2008-03-31T06:03:30Z 2019-08-30T01:26:43Z "When you create a new post using WordPress 2.5 the GUID is created in the http://siteurl/?p= format even when permalinks are enabled. This is because the _transition_post_status function in /wp-includes/post.php now checks if the guid is empty (which it never is) before resetting/creating it with the proper permalink structure. Line 2841 should be removed. " brianwhite Unpatched Enhancements 8857 Make WP MySQL strict mode compliant Database 6.4.2 lowest normal enhancement reopened 2009-01-14T11:25:26Z 2024-01-22T08:45:43Z "Incorrect Mysql scheme during WordPress installation on DBMS with strict date/datetime format settings will result in errors and will be unable to finish succesfully. Existing default date and datetime values are '0000-00-00' and '0000-00-00 00:00:00' which are not supported any more. Please see official Mysql dev documentation for more information: {{{ The DATE type is used when you need only a date value, without a time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'. }}} http://dev.mysql.com/doc/refman/5.0/en/datetime.html " ghostks Unpatched Enhancements 42352 Support use of native MySQLi prepared queries Database normal normal Future Release enhancement new early 2017-10-27T04:15:52Z 2023-09-09T22:23:23Z "When we added `$wpdb->prepare()` back in WordPress 2.3, we were forced to roll our own MySQL Prepared queries as the MySQL extension didn't support it. While the MySQL extension still doesn't, the majority of WordPress sites are likely using the newer MySQLi engine (by default, enabled for PHP 5.5+ which accounts for 70~80% of sites). That makes now a good time to start investigating the potential implementation and usage of native prepared queries in the future. Attached is a first-draft of implementing native prepared queries into WPDB, it has no fallbacks for MySQL at present, but it would be possible to force support in for it using a ""simple"" SQL parser. Unfortunately I expect that this draft is incompatible with many DB drop-ins, which is okay, this is only a draft and proof-of-concept. I'll attach some examples of how this first draft could be used in a comment below." dd32 Unpatched Enhancements 41424 "Use a better error message than ""Error establishing a database connection"" when site isn't found" Database 4.9 normal normal Future Release enhancement new needs-unit-tests 2017-07-24T14:24:44Z 2017-11-22T08:58:59Z "In multisite, if this query returns no results, the database connection error is triggered: SELECT blog_id FROM wp_blogs WHERE domain IN ( 'example.com' ) AND path IN ( '/' ) ORDER BY blog_id ASC LIMIT 1 I think the error should not mention database connection but allude to the fact that the site was not found. For my use case, I had migrated a production database into QA and didn't update the domain to be qa.example.com so the connection failed. I hope this is helpful. I'm not sure I know what the exact solution is but I thought the connection attempt had failed, when in fact the connection had been made but the data was not as expected. Also, the failure was not found in debug.log." tthorp Unpatched Enhancements 31042 WPDB field_types Variable Too Broad? Database normal normal enhancement new 2015-01-17T03:07:49Z 2019-06-04T19:27:49Z "I was wondering if it might make sense to tie the field_types format definitions to tables and columns instead of just columns. As people use WordPress more there may be situations like the one I encountered today where columns of their enhancement tables overlap with those of WordPress' default tables. In my situation my user_id was more of a guid so it was a string that was being formatted to a decimal. While this might not be a common problem with the overhead it would take to change it to a multidimensional array it might make sense. If this is something others would find useful or practical I would be happy to rework the code." njkrut Unpatched Enhancements 10883 db-error.php not used for all DB failures Database 2.8.4 normal normal enhancement assigned 2009-10-01T02:45:28Z 2023-12-07T13:19:54Z "db-error.php (the optional custom DB error message file to be placed in wp-content) does not get included all the time. Sometimes wp-db.php will use its {{{bail()}}} method to spit out its own message. This code needs to be there too: {{{ if ( file_exists( WP_CONTENT_DIR . '/db-error.php' ) ) { require_once( WP_CONTENT_DIR . '/db-error.php' ); die(); } }}}" markjaquith Unpatched Enhancements 29938 mysqli_query and multiple resultsets Database 3.9 normal normal enhancement new needs-unit-tests 2014-10-12T10:24:20Z 2019-06-04T19:26:52Z "The WordPress Database API does not expose a way to work with multiple resultsets. Multiple resultsets are returned by queries that have several sets of results available and this is often the case with stored procedures and the usual way is to call {{{next_result}}} and {{{use/store_result}}} on the mysqli connection, however the connection is abstracted away behind the undocumented {{{$wpdb->dbh}}} field. {{{ -- Test procedure for out of sync mysqli commands DROP PROCEDURE IF EXISTS `mysqli_procedure_test`; DELIMITER ;; CREATE PROCEDURE `mysqli_procedure_test`() BEGIN SELECT * FROM `wp_posts` LIMIT 1; SELECT * FROM `wp_posts` LIMIT 1; END;; DELIMITER ; }}} When calling this procedure (apart from the issues outlined in ticket #28155) you can only access the first resultset using the documented APIs. To fetch the second one one would have to use the mysqli API directly. Need to come up with additional public methods to work with these via the Database API instead. There must be a way for a user to fetch the next resultset if there's one, or make this transparent somehow, perhaps using a {{{$wpdb->call( $procedure, $arguments )}}} invocation in the case of procedures? And something like {{{$wpdb->next_results}}} for everything else? Needs brainstorming." soulseekah Unpatched Enhancements 36242 wpdb set_sql_mode add param Database 3.9 normal normal enhancement new 2016-03-14T23:43:46Z 2019-06-04T19:35:29Z When making a creating a db drop-in that supports multiple databases, I had to override the whole {{{set_sql_mode}}} method. If {{{set_sql_mode}}} accepted a {{{$dbh}}} param, the method could easily be reused. spacedmonkey