title: mysql: error code [1267]; Illegal mix of collations (latin1_general_cs,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='
body: <p>I want to make the <code>password</code> column of my User table to be <code>case sensitive</code> in mysql.</p>
  
  <p>Following is the description of the table:</p>
  
  <pre><code>/*Table: mst_user*/
  
     FIELD          TYPE          COLLATION        
  -------------  ------------  -----------------
  user_id        VARCHAR(100)  latin1_swedish_ci
  first_name     VARCHAR(25)   latin1_swedish_ci
  last_name      VARCHAR(25)   latin1_swedish_ci
  USER_PASSWORD  VARCHAR(50)   latin1_swedish_ci
  user_status    INT(11)       (NULL)           
  version_id     INT(11)       (NULL)           
  active_status  INT(11)       (NULL)           
  user_type      INT(11)       (NULL)    
  </code></pre>
  
  <p>To make the <code>USER_PASSWORD</code> field case sensitive I executed following query:</p>
  
  <pre><code>ALTER TABLE `mst_user` MODIFY `USER_PASSWORD` VARCHAR(50) COLLATE `latin1_general_cs`;
  </code></pre>
  
  <p>This worked and the field is now case sensitive.</p>
  
  <p>But I have a store procedure which executes a <code>SELECT</code> query on this table to check if the user exists for the given credentials. </p>
  
  <p>Stored Proc::</p>
  
  <pre><code>CREATE PROCEDURE `usp_password_verify`(ip_login_id         VARCHAR(200),
                                   ip_user_password    VARCHAR(200),
                                  INOUT success     INT(1),
  INOUT tbl_usr_password          VARCHAR(100),
  INOUT  pkg_user_password         VARCHAR(100))
  BEGIN
    SELECT COUNT(*)
      INTO success
      FROM mst_user
     WHERE UPPER (user_id) = UPPER (ip_login_id)
     AND USER_PASSWORD=ip_user_password;
  
     SET tbl_usr_password = '';
     SET pkg_user_password= '';
  END$$
  </code></pre>
  
  <p>When I call this stored proc from my java code I am getting the following error:</p>
  
  <pre><code>**error code [1267]; Illegal mix of collations (latin1_general_cs,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='**
  </code></pre>
  
  <p>Can anyone help what is wrong with this? 
  Something that works as a simple query gives error while executing it in a stored proc!?</p>