At Beam, we recently completed a project that involved reworking the way we associate our claims data with our provider data (a provider being a dentist and a claim being a description of the services that a provider performed on one of our subscribers). We store our claims information in MySQL and our provider data lives in Elasticsearch. In the past, we stored a few of the provider attributes on the claim that when combined created a unique identifier for that provider. Our problem was that if a provider’s information changed, all claims associated with that provider pointed to the new version of the provider. This meant we were losing historical data associated with those claims. To circumvent this, we decided to also add the Elasticsearch ID of the provider to the claim so that we could retrieve the exact provider record from Elasticsearch that was originally associated with the claim.<figure><img alt="" src="https://cdn-images-1.medium.com/max/800/1*aKxfrwQhexd9gsYWiBHFJw.jpeg" /><figcaption>The Elasticsearch IDs in MySQL</figcaption></figure>The project was a success, and when we merged the pull request everything worked the way we had anticipated. After a few days in production, we started seeing some instances where searching for a provider’s claims did not return the claims we were expecting, so we started digging through our code to see where we could have gone wrong.What we finally discovered was that there were a few instances of multiple providers having the same Elasticsearch ID, aside from the case. For example:<pre>Provider 1 Elasticsearch ID: AWotUW6UTE8lg8DbrKkw</pre><pre>Provider 2 Elasticsearch ID: AWotUW6UTE8lg8DbrKkW</pre>Notice that the last character “w” is capitalized in the second ID, but not the first. The column in MySQL that was storing the Elasticsearch ID was created using a Rails migration that looked something like this:<pre>add_column :claims, :elasticsearch_id, :string</pre>By default, the column created by this migration will collate on utf8mb4_general_ci. The important part of this setting, in this case, is the _ci suffix. <a href="https://dev.mysql.com/doc/refman/5.7/en/charset-collation-names.html">This suffix</a> specifies that the column is case insensitive. To illustrate this issue, I have an example below:<pre>[1] pry > Claims.all => [<Claim:1 id=1 elasticsearch_id='AWotUW6UTE8lg8DbrKkw'> <Claim:2 id=2 elasticsearch_id='AWotUW6UTE8lg8DbrKkW'>]</pre><pre>[2] pry > Claim.where(elasticsearch_id: 'AWotUW6UTE8lg8DbrKkw') => [<Claim:1 id=1 elasticsearch_id='AWotUW6UTE8lg8DbrKkw'> <Claim:2 id=2 elasticsearch_id='AWotUW6UTE8lg8DbrKkW'>]</pre><pre>[3] pry > Claim.where(elasticsearch_id: 'AWotUW6UTE8lg8DbrKkW') => [<Claim:1 id=1 elasticsearch_id='AWotUW6UTE8lg8DbrKkw'> <Claim:2 id=2 elasticsearch_id='AWotUW6UTE8lg8DbrKkW'>]</pre>In the example, we have two claims associated with different providers. When we try to find the claims associated with each provider, we get the claims for both providers.So how did we solve this issue? We opted to update the elasticsearch_id column to use a binary collation. This means that the values in this column will be stored as a sequence of bytes rather than a sequence of characters. We did this with a migration that looked like this:<pre>execute("ALTER TABLE `claims` MODIFY `elasticsearch_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;")</pre>To help illustrate this change, here is what the comparison looked like before and after our fix:<pre>When comparing awotuw6ute8lg8dbrkkw and awotuw6ute8lg8dbrkkW</pre><pre>utf8mb4_general_ci collation awotuw6ute8lg8dbrkkw == awotuw6ute8lg8dbrkkw</pre><pre>utf8mb4_bin collation 41576F74555736555445386C67384462724B6B77 == 41576F74555736555445386C67384462724B6B57</pre>Reflecting on this entire process, it was a really valuable learning experience for me. The most important thing I took away from all the whole experience is that frameworks like Rails can do a lot of cool, “magical” things which is great. It’s important though to take the time to understand what is going on under the hood so that you understand what that “magic” does and everything rails does for you is deliberate.<img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=26b272d94e" width="1" height="1" alt=""><hr><a href="https://medium.com/beambenefits/mysql-elasticsearch-and-the-case-of-the-doppelg%C3%A4nger-ids-26b272d94e">MySQL, Elasticsearch, and the case of the Doppelgänger IDs</a> was originally published in <a href="https://medium.com/beambenefits">Beam Benefits</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.

Will Naugle

MySQL, Elasticsearch, and the case of the Doppelgänger IDs