I got the following error on a simple select query:
[21-May-2025 20:20:52 America/New_York] PHP Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (latin1_swedish_ci,IMPLIC
IT) and (utf8mb4_0900_ai_ci,COERCIBLE) for operation '=' in /home/server/public_html/api/v1/0.50/includes.php:196
Stack trace:
#0 /home/server/public_html/api/v1/0.50/includes.php(196): PDOStatement->execute(Array)
#1 /home/server/public_html/api/v1/0.50/emailVerify.html(86): dbSelect('SELECT * FROM u...', Array)
#2 {main}
Its a table that hosts user emails and verification codes. The user is sent an email and when they click on a link in the email, the link the opens the validation page and in that page it does the following query simple query:
$vCode = $_REQUEST['v'] ; // IE: YzFlMGM3M2M1NDIyMzhkNDUxNGFkNGF
$userEmail = $_REQUEST['e'] ; // IE: [email protected]
$query = "select * from userEmailVerify where user_email=? and code=?" ;
$stmt = $pdo->prepare($query) ;
$result = $stmt->execute([$userEmail,$vCode]) ;
This query is performed dozens of times a day and over the 3 years this table and scripts have been in service this is the first time I have ever seen this error. I manually performed the same SELECT query using the users email and verification code that triggered this error and the query worked fine. The error happened back on the 21st and the same scripts have been processed dozens of times since that error without any further error.
I have read several threads about collation and don’t think any of what I read applies to this situation.
What could have caused this?