[wp-meta] [Making WordPress.org] #3295: Events API location search is case-sensitive

[Making WordPress.org]

#3295: Events API location search is case-sensitive
----------------------------+------------------
 Reporter:  SergeyBiryukov  |       Owner:
     Type:  defect          |      Status:  new
 Priority:  normal          |   Milestone:
Component:  API             |  Resolution:
 Keywords:  2nd-opinion     |
----------------------------+------------------

Comment (by dd32):

 Replying to [comment:1 Otto42]:
 > @dd32 Thoughts? Also perhaps about changing the other 3 columns, which
 are all English but should still be utf8 just the same.

 In general, utf8(mb4)? doesn't work as you'd expect it to on WordPress.org
 due to HyperDB and connection charsets - I'm fairly certain our HyperDB
 doesn't even support utf8mb4 (We force it to in some places, like on
 WordCamp.org and BuddyPress.org, which results in lovely charset issues
 everywhere else). Latin1 is a better option in general, except for this
 specific case of upper/lower-case values.

 Personally I think I'd prefer to instead ensure that `geoname_summary` is
 always lower-case'd, even if that means running a `UPDATE.. SET name =
 CONVERT(...)` query against the table after updates to convert all the
 data to lower-case.. however that might not be viable.

 However, as a test, I've created `geoname_summary_utf8mb4_test` in the
 same DB for testing, using your SQL from above, and as expected, it fails
 to query the table unless we force HyperDB to utf8 mode manually
 Should also note that that conversion has resulted in the fields being
 padded to 200char of null bytes after each name - I tried setting it to
 VARCHAR but that didn't help.
 {{{
 <?php define('SAVEQUERIES', true ); include './wp-load.php';

 add_db_table('geoip', 'geoname_summary_utf8mb4_test');

 var_dump([
         'lower' => $wpdb->get_results( "SELECT * FROM
 `geoname_summary_utf8mb4_test` WHERE `name` LIKE '%санкт-петербург%'" ),
         'upper' => $wpdb->get_results( "SELECT * FROM
 `geoname_summary_utf8mb4_test` WHERE `name` LIKE '%Санкт-Петербург%'" ),
         'id' => $wpdb->get_results( "SELECT * FROM
 `geoname_summary_utf8mb4_test` WHERE id = 41827" ),
         'queries' => $wpdb->queries
 ]);
 // Only the ID query will work above

 $wpdb->queries = array();

 $dbh = $wpdb->db_connect( "SELECT * FROM `geoname_summary_utf8mb4_test`
 WHERE `name` LIKE '%санкт-петербург%'" );
 $wpdb->set_charset( $dbh, 'utf8' );

 var_dump([
         'lower' => $wpdb->get_results( "SELECT * FROM
 `geoname_summary_utf8mb4_test` WHERE `name` LIKE '%санкт-петербург%'" ),
         'upper' => $wpdb->get_results( "SELECT * FROM
 `geoname_summary_utf8mb4_test` WHERE `name` LIKE '%Санкт-Петербург%'" ),
         'id' => $wpdb->get_results( "SELECT * FROM
 `geoname_summary_utf8mb4_test` WHERE id = 41827" ),
         'queries' => $wpdb->queries
 ]);
 // All above will work.
 $wpdb->set_charset( $dbh, 'latin1' ); // Must set it back or it'll affect
 other things too.
 }}}

 @iandunn Can you try running the test table above against your tests, and
 any other edge cases that haven't been working? Just remember you'll need
 to force the connection to be in utf8 mode.

--
Ticket URL: <https://meta.trac.wordpress.org/ticket/3295#comment:3>
Making WordPress.org <https://meta.trac.wordpress.org/>
Making WordPress.org