Wednesday, May 25, 2016
+----------------+------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
| 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ıasdf@test.com
mysql> select * from user_subscriptions where email='hsıasdf@test.com';
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 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
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 rubyonrails-talk+unsubscribe@googlegroups.com.
To post to this group, send email to rubyonrails-talk@googlegroups.com.
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.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment