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 Kothari
--
You received this message because you are subscribed to the Google Groups "Ruby
on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/rubyonrails-talk/473fcf6f-2d88-438b-8d62-9fbc2be633d7%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
