General Error: 1267 Illegal Mix of Collations

[linux4me]

I just updated to v. 8.0, and while I was troubleshooting another issue with cron after updating to PHP 7.4.11, I discovered I was getting this error in the SSL Sync section of the cron task. Here is the complete error:

SSL Sync

                                                                                
 [ERROR] PDOException: SQLSTATE[HY000]: General error: 1267 Illegal mix of      
         collations (utf8mb4_unicode_ci,IMPLICIT) and                           
         (utf8mb4_general_ci,IMPLICIT) for operation '=' in                     
         /chroot/home/username/thedomain.com/html/vendor/illuminate/da
         tabase/Connection.php:331                                              
         Stack trace:                                                           
         #0                                                                     
         /chroot/home/username/thedomain.com/html/vendor/illuminate/da
         tabase/Connection.php(331): PDO->prepare('select `userid`...')         
         #1                                                                     
         /chroot/home/username/thedomain.com/html/vendor/illuminate/da
         tabase/Connection.php(664):                                            
         Illuminate\Database\Connection->Illuminate\Database\{closure}('select  
         `userid`...', Array)                                                   
         #2                                                                     
         /chroot/home/username/thedomain.com/html/vendor/illuminate/da
         tabase/Connection.php(631):                                            
         Illuminate\Database\Connection->runQueryCallback('select `userid`...', 
         Array, Object(Closure))                                                
         #3                                                                     
         /chroot/home/username/thedomain.com/html/vendor/illuminate/da
         tabase/Connection.php(339): Illuminate\Database\Connection->run('select
         `userid`...', Array, Object(Closure))                                  
         #4                                                                     
         /chroot/home/username/thedomain.com/html/vendor/illuminate/da
         tabase/Query/Builder.php(2202):                                        
         Illuminate\Database\Connection->select('select `userid`...', Array,    
         true)                                                                  
         #5                                                                     
         /chroot/home/username/thedomain.com/html/vendor/illuminate/da
         tabase/Query/Builder.php(2377):                                        
         Illuminate\Database\Query\Builder->runSelect()                         
         #6                                                                     
         /chroot/home/username/thedomain.com/html/vendor/illuminate/da
         tabase/Query/Builder.php(2685):                                        
         Illuminate\Database\Query\Builder->Illuminate\Database\Query\{closure}(
         )                                                                      
         #7                                                                     
         /chroot/home/username/thedomain.com/html/vendor/illuminate/da
         tabase/Query/Builder.php(2379):                                        
         Illuminate\Database\Query\Builder->onceWithColumns(Array,              
         Object(Closure))                                                       
         #8                                                                     
         /chroot/home/username/thedomain.com/html/vendor/whmcs/whmcs-f
         oundation/lib/Cron/Task/SslStatusSync.php(0):                          
         Illuminate\Database\Query\Builder->pluck('userid', 'domain')           
         #9                                                                     
         /chroot/home/username/thedomain.com/html/vendor/whmcs/whmcs-f
         oundation/lib/Cron/Task/SslStatusSync.php(0):                          
         WHMCS\Cron\Task\SslStatusSync->getNewDomains()                         
         #10                                                                    
         /chroot/home/username/thedomain.com/html/vendor/whmcs/whmcs-f
         oundation/lib/Scheduling/Task/AbstractTask.php(0):                     
         WHMCS\Cron\Task\SslStatusSync->__invoke()                              
         #11                                                                    
         /chroot/home/username/thedomain.com/html/vendor/whmcs/whmcs-f
         oundation/lib/Scheduling/Task/AbstractTask.php(0):                     
         WHMCS\Scheduling\Task\AbstractTask->execute()                          
         #12                                                                    
         /chroot/home/username/thedomain.com/html/vendor/whmcs/whmcs-f
         oundation/lib/Cron/Console/Command/AbstractCronCommand.php(0):         
         WHMCS\Scheduling\Task\AbstractTask->run()                              
         #13                                                                    
         /chroot/home/username/thedomain.com/html/vendor/whmcs/whmcs-f
         oundation/lib/Cron/Console/Command/AbstractCronCommand.php(0):         
         WHMCS\Cron\Console\Command\AbstractCronCommand->executeCollection(Objec
         t(WHMCS\Scheduling\Task\Collection))                                   
         #14                                                                    
         /chroot/home/username/thedomain.com/html/vendor/symfony/conso
         le/Command/Command.php(255):                                           
         WHMCS\Cron\Console\Command\AbstractCronCommand->execute(Object(WHMCS\Cr
         on\Console\Input\CliInput),                                            
         Object(Symfony\Component\Console\Output\ConsoleOutput))                
         #15                                                                    
         /chroot/home/username/thedomain.com/html/vendor/symfony/conso
         le/Application.php(912):                                               
         Symfony\Component\Console\Command\Command->run(Object(WHMCS\Cron\Consol
         e\Input\CliInput),                                                     
         Object(Symfony\Component\Console\Output\ConsoleOutput))                
         #16                                                                    
         /chroot/home/username/thedomain.com/html/vendor/symfony/conso
         le/Application.php(264):                                               
         Symfony\Component\Console\Application->doRunCommand(Object(WHMCS\Cron\C
         onsole\Command\AllCommand), Object(WHMCS\Cron\Console\Input\CliInput), 
         Object(Symfony\Component\Console\Output\ConsoleOutput))                
         #17                                                                    
         /chroot/home/username/thedomain.com/html/vendor/symfony/conso
         le/Application.php(140):                                               
         Symfony\Component\Console\Application->doRun(Object(WHMCS\Cron\Console</>
         Input\CliInput),                                                       
         Object(Symfony\Component\Console\Output\ConsoleOutput))                
         #18                                                                    
         /chroot/home/username/thedomain.com/whmcs/crons/cron.php(0): 
         Symfony\Component\Console\Application->run(Object(WHMCS\Cron\Console\In
         put\CliInput), Object(Symfony\Component\Console\Output\ConsoleOutput)) 
         #19 {main}                                                             
                                                                                
         Next Illuminate\Database\QueryException: SQLSTATE[HY000]: General      
         error: 1267 Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and
         (utf8mb4_general_ci,IMPLICIT) for operation '=' (SQL: select `userid`, 
         `domain` from `tblhosting` left join `tblsslstatus` on                 
         `tblhosting`.`domain` = `tblsslstatus`.`domain_name` where `domain` != 
         and `tblsslstatus`.`id` is null limit 100) in                          
         /chroot/home/username/thedomain.com/html/vendor/illuminate/da
         tabase/Connection.php:671                                              
         Stack trace:                                                           
         #0                                                                     
         /chroot/home/username/thedomain.com/html/vendor/illuminate/da
         tabase/Connection.php(631):                                            
         Illuminate\Database\Connection->runQueryCallback('select `userid`...', 
         Array, Object(Closure))                                                
         #1                                                                     
         /chroot/home/username/thedomain.com/html/vendor/illuminate/da
         tabase/Connection.php(339): Illuminate\Database\Connection->run('select
         `userid`...', Array, Object(Closure))                                  
         #2                                                                     
         /chroot/home/username/thedomain.com/html/vendor/illuminate/da
         tabase/Query/Builder.php(2202):                                        
         Illuminate\Database\Connection->select('select `userid`...', Array,    
         true)                                                                  
         #3                                                                     
         /chroot/home/username/thedomain.com/html/vendor/illuminate/da
         tabase/Query/Builder.php(2377):                                        
         Illuminate\Database\Query\Builder->runSelect()                         
         #4                                                                     
         /chroot/home/username/thedomain.com/html/vendor/illuminate/da
         tabase/Query/Builder.php(2685):                                        
         Illuminate\Database\Query\Builder->Illuminate\Database\Query\{closure}(
         )                                                                      
         #5                                                                     
         /chroot/home/username/thedomain.com/html/vendor/illuminate/da
         tabase/Query/Builder.php(2379):                                        
         Illuminate\Database\Query\Builder->onceWithColumns(Array,              
         Object(Closure))                                                       
         #6                                                                     
         /chroot/home/username/thedomain.com/html/vendor/whmcs/whmcs-f
         oundation/lib/Cron/Task/SslStatusSync.php(0):                          
         Illuminate\Database\Query\Builder->pluck('userid', 'domain')           
         #7                                                                     
         /chroot/home/username/thedomain.com/html/vendor/whmcs/whmcs-f
         oundation/lib/Cron/Task/SslStatusSync.php(0):                          
         WHMCS\Cron\Task\SslStatusSync->getNewDomains()                         
         #8                                                                     
         /chroot/home/username/thedomain.com/html/vendor/whmcs/whmcs-f
         oundation/lib/Scheduling/Task/AbstractTask.php(0):                     
         WHMCS\Cron\Task\SslStatusSync->__invoke()                              
         #9                                                                     
         /chroot/home/username/thedomain.com/html/vendor/whmcs/whmcs-f
         oundation/lib/Scheduling/Task/AbstractTask.php(0):                     
         WHMCS\Scheduling\Task\AbstractTask->execute()                          
         #10                                                                    
         /chroot/home/username/thedomain.com/html/vendor/whmcs/whmcs-f
         oundation/lib/Cron/Console/Command/AbstractCronCommand.php(0):         
         WHMCS\Scheduling\Task\AbstractTask->run()                              
         #11                                                                    
         /chroot/home/username/thedomain.com/html/vendor/whmcs/whmcs-f
         oundation/lib/Cron/Console/Command/AbstractCronCommand.php(0):         
         WHMCS\Cron\Console\Command\AbstractCronCommand->executeCollection(Objec
         t(WHMCS\Scheduling\Task\Collection))                                   
         #12                                                                    
         /chroot/home/username/thedomain.com/html/vendor/symfony/conso
         le/Command/Command.php(255):                                           
         WHMCS\Cron\Console\Command\AbstractCronCommand->execute(Object(WHMCS\Cr
         on\Console\Input\CliInput),                                            
         Object(Symfony\Component\Console\Output\ConsoleOutput))                
         #13                                                                    
         /chroot/home/username/thedomain.com/html/vendor/symfony/conso
         le/Application.php(912):                                               
         Symfony\Component\Console\Command\Command->run(Object(WHMCS\Cron\Consol
         e\Input\CliInput),                                                     
         Object(Symfony\Component\Console\Output\ConsoleOutput))                
         #14                                                                    
         /chroot/home/username/thedomain.com/html/vendor/symfony/conso
         le/Application.php(264):                                               
         Symfony\Component\Console\Application->doRunCommand(Object(WHMCS\Cron\C
         onsole\Command\AllCommand), Object(WHMCS\Cron\Console\Input\CliInput), 
         Object(Symfony\Component\Console\Output\ConsoleOutput))                
         #15                                                                    
         /chroot/home/username/thedomain.com/html/vendor/symfony/conso
         le/Application.php(140):                                               
         Symfony\Component\Console\Application->doRun(Object(WHMCS\Cron\Console</>
         Input\CliInput),                                                       
         Object(Symfony\Component\Console\Output\ConsoleOutput))                
         #16                                                                    
         /chroot/home/username/thedomain.com/whmcs/crons/cron.php(0): 
         Symfony\Component\Console\Application->run(Object(WHMCS\Cron\Console\In
         put\CliInput), Object(Symfony\Component\Console\Output\ConsoleOutput)) 
         #17 {main}

I checked the tables tblsslstatus and tblhosting, and they did have different collations (utf8_general_ci), but I have since changed all the database collations to utf8mb4_unicode_ci, which the majority of the tables already were, and re-run cron, but I still get the same error.

I'm running MariaDB 10.3.24.

How do I fix this?

[linux4me]

I finally figured this out.

I was using phpMyAdmin to look at the database collation of all the tables by clicking the database name in the left sidebar of phpMyAdmin. I had changed the collation to utf8mb4_unicode_ci for all the tables, but I was still getting the error above. Although the main structure view in phpMyAdmin showed all the tables were using utf8mb4_unicode_ci, when I looked at the structure of the tblsslstatus table, it still showed that utf8mb4_general_ci was being used by all the varchar fields. Once I changed each of those fields to utf8mb4_unicode_ci, the error message was gone and the SSL Sync worked when I re-ran the cron command.