[wp-trac] [WordPress Trac] #50789: Improve WPDB logic around information_schema

[WordPress Trac]

#50789: Improve WPDB logic around information_schema
--------------------------+------------------------------
 Reporter:  andy          |       Owner:  (none)
     Type:  defect (bug)  |      Status:  new
 Priority:  normal        |   Milestone:  Awaiting Review
Component:  Database      |     Version:  trunk
 Severity:  normal        |  Resolution:
 Keywords:                |     Focuses:  multisite
--------------------------+------------------------------

Comment (by andy):

 Replying to [comment:1 johnjamesjacoby]:
 > Interesting. (Also, hey @andy!)

 Hey, @johnjamesjacoby! :D

 > It's not common, but querying the `information_schema` database is a
 valid use of `WPDB::query()`. I expect for `get_table_from_query()` to
 return the first table being queried, not the `TABLE_NAME` - it seems too
 clever.

 The original and primary use for `get_table_from_query` in hyperdb was to
 allow hyperdb to route the query to the right database server. Years
 later, @pento borrowed this code for core in [30345] for a different
 purpose: to enable `get_table_charset()` to write a `SHOW COLUMNS`
 statement ultimately supporting `strip_invalid_text_from_query()`. This
 does not fit with the proposal that I made, i.e. to extract from the
 `TABLE_NAME` value in the `WHERE` clause.

 In agreement with your assessment, I now think that hyperdb should use a
 separate or hybrid solution, taking back responsibility for extracting the
 name that it needs for query routing.

 Still, there is a valid issue in the original description: the bug where
 `get_table_from_query` returns the database name `information_schema`
 instead of the table name `TABLE_CONSTRAINTS. This should be fixed. In the
 process, it can be considered whether to return the extracted database
 name somewhere (e.g. `information_schema`).

 > I probably wouldn't query `information_schema` directly in this
 situation for this reason. WooCommerce ''should'' be storing a version as
 an option for each of its custom tables, and the values of those options
 should dictate what SQL is generated. (This is also the approach I've
 taken in the [https://github.com/berlindb/core BerlinDB] project.)

 We agree that `information_schema` queries should be avoided. I can't
 speak for the WooCommerce logic of using both a stored version number and
 a schema check. But WooCommerce got a pull request to replace the
 `information_schema` query with a `SHOW CREATE TABLE`:
 https://github.com/Automattic/woocommerce/pull/84

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/50789#comment:3>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform