[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     |
----------------------------+------------------
Changes (by Otto42):

 * keywords:   => 2nd-opinion


Comment:

 This appears to be because the data in the geonames table is stored
 incorrectly in a latin1 field, and so the collation for casing doesn't
 work properly.

 I downloaded a copy of the table to work with it locally, and I found that
 altering the name column to utf8mb4 fixes the problem. To not convert the
 existing data incorrectly, the SQL that should be run is as follows:

 {{{
 ALTER TABLE geonames_summary MODIFY name BINARY(200);
 ALTER TABLE geonames_summary MODIFY name CHAR(200) CHARACTER SET utf8mb4;
 }}}

 (Binary first to prevent converting the data, which is already utf8,
 stored in a latin1 column.)

 After making this change, this query works properly:

 {{{
 SELECT * FROM `geoname_summary` WHERE `name` LIKE '%санкт-петербург%'
 }}}


 I'd like a second opinion though instead of just altering the data
 directly. @dd32 Thoughts? Also perhaps about changing the other 3 columns,
 which are all English but should still be utf8 just the same.

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