Check if input string conforms with DB collation

[Saurav Kothari]

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ıas...@test.com

mysql> select * from user_subscriptions where email='hsıas...@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