MySQL Ignores Right Padded Spaces?

When working on an established Rails project, we noticed that trailing spaces weren’t getting trimmed off. Discussion ensued about whether to do data cleanup and then someone noticed that the where method in Rails didn’t care about the spaces. Not having seen this in my PostgreSQL days, I was assuming that there was some magic happening in either the Rails codebase or the MySQL adapter. So I went to the underlying DB: No, searching for column='hi' found values where the column value was actually 'hi '.

In searching on StackOverflow I found this answer states that MySQL ignores trailing spaces in string comparison, echoed in the MySQL 5.7 documentation:

All MySQL collations are of type PAD SPACE. This means that all CHARVARCHAR, and TEXT values are compared without regard to any trailing spaces. “Comparison” in this context does not include the LIKE pattern-matching operator, for which trailing spaces are significant. 

Of course, I’m using MySQL 8.0 and so is the app. So I dug into the collation, setting up the following table:

mysql> select concat('"', thing, '"'), unthing from surelynot;
+-------------------------+---------+
| concat('"', thing, '"') | unthing |
+-------------------------+---------+
| "hi "                   | what    |
| " hi "                  | what2   |
| " hi"                   | what3   |
| "hi"                    | what4   |
+-------------------------+---------+
4 rows in set (0.02 sec)

Setting the collation to MySQL 8.x’s default, I get one row for 'hi':

mysql> alter table surelynot convert to character set utf8mb4 collate utf8mb4_0900_ai_ci;
Query OK, 4 rows affected (0.16 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select concat('"', thing, '"'), unthing from surelynot where thing = 'hi';
+-------------------------+---------+
| concat('"', thing, '"') | unthing |
+-------------------------+---------+
| "hi"                    | what4   |
+-------------------------+---------+
1 row in set (0.05 sec)

However, using the latin1 or latin2 collation that was on the other database, I get two results for 'hi', one for the exact match, and one for the right-padded. Note that left-padding has no impact on the results:

mysql> select concat('"', thing, '"'), unthing from surelynot where thing = 'hi';
+-------------------------+---------+
| concat('"', thing, '"') | unthing |
+-------------------------+---------+
| "hi "                   | what    |
| "hi"                    | what4   |
+-------------------------+---------+
2 rows in set (0.00 sec)

The ucs2_general_ci also has this property as does the utf8_general_ci collation. Ultimately, only the default utf8mb4_0900_ai_ci paid attention to right padded spaces when looking for string equality in the where clause.


XML Handler Bypassing ParseError Handling Rack Middleware on Rails 6

In a project using actionpack-xml_parser, and implementing a modified version of Catching Invalid JSON Parse Errors with Rack Middleware to also handle XML errors, an upgrade to Rails 6 broke the handling of the ParseError. Interestingly enough, JSON was being handled as expected.

TL;DR: The short answer to this is that config/initializers/wrap_parameters.rb only had json mentioned as a format. This can be remedied in the initializer or on a per controller basis. The entire app fix was just adding :xml to the initializer:

ActiveSupport.on_load(:action_controller) do
  wrap_parameters format: [:json, :xml]
end

In looking through the stack traces/call stack for where the parse error occurs (ActionDispatch::Http::Parameters#parse_formatted_parameters) using the caller array in the gem source and backtrace in the rescue, I was able to catch that ActionController::Metal::ParamsWrapper#_wrapper_enabled? was called for JSON, but not for XML. Ultimately, I was able to track down the difference to ActionController::Metal::ParamsWrapper#_wrapper_formats?, which was only returning [:json], which led to the wrap_parameters functionality in Rails.