What we have is a table with an email field - collation
latin1_swedish_ci
because of the limitations of our email service.
±---------------±-----------±------------------±-----±----±--------±---------------±--------------------------------±--------+
| Field | Type | Collation | Null | Key | Default |
Extra | Privileges | Comment |
±---------------±-----------±------------------±-----±----±--------±---------------±--------------------------------±--------+
| id | int(11) | NULL | NO | PRI | NULL |
auto_increment | select,insert,update,references | |
| email | text | latin1_swedish_ci | YES | | NULL |
| select,insert,update,references | |
| moved_to_users | tinyint(1) | NULL | YES | | NULL |
| select,insert,update,references | |
| created_at | datetime | NULL | YES | | NULL |
| select,insert,update,references | |
| updated_at | datetime | NULL | YES | | NULL |
| select,insert,update,references | |
±---------------±-----------±------------------±-----±----±--------±---------------±--------------------------------±--------+
This table is checked when a new user arrives to see if he was already
subscribed.
Comes along a user with characters in their email that would not be
valid
for this particular collation. Take for instance hsı[email protected]
mysql> select * from user_subscriptions where email=‘hsı[email protected]’;
ERROR 1267 (HY000): Illegal mix of collations
(latin1_swedish_ci,IMPLICIT)
and (utf8_general_ci,COERCIBLE) for operation ‘=’
Would it be possible to determine before saving if a string will adhere
to
the collation rules of database?
I tried using transactions as mentioned in this SO answer
http://stackoverflow.com/a/37409558/2651076 but it does not work or
maybe
I am not using it right.
I wrapped this in a begin rescue block as a validation method
def validate_collation_again
email = params[:name].try(:strip)
begin
UserSubscription.find_by_email!(email)
rescue Exception => e
#handle
end
end
Is it possible for me to check before any DB trigger to
save/create/find;
if an entry with this particular email will agree with the collation?
Some
regex maybe?
String#encode cannot be checked as the application is built using Ruby
1.8.7
This question is also on StackOverflow
http://stackoverflow.com/questions/37407604/check-if-input-string-conforms-with-db-collation
Regards,
Saurav K.