Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

1.4.2 Upgrade Mixed Collation Error
 
ladle
Sr. Member
 
Avatar
Total Posts:  173
Joined:  2009-06-11
 

Upgrading I get the following error.  I’ve tried setting all my tables to the same collation and re-upgrading, and get the same error.  Any ideas?

Error in file: “/home/myroot/public_html/mywebsite/app/code/core/Mage/Directory/sql/directory_setup/mysql4-upgrade-0.8.10-0.8.11.php” - SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation ‘=’

Trace:
#0 /home/myroot/public_html/mysite.info/app/code/core/Mage/Core/Model/Resource/Setup.php(390): Mage::exception(’Mage_Core’, ‘Error in file: ...’)
#1 /home/myroot/public_html/mywebsite/app/code/core/Mage/Core/Model/Resource/Setup.php(289): Mage_Core_Model_Resource_Setup->_modifyResourceDb(’upgrade’, ‘0.8.10’, ‘0.8.11’)
#2 /home/myroot/public_html/mywebsite/app/code/core/Mage/Core/Model/Resource/Setup.php(233): Mage_Core_Model_Resource_Setup->_upgradeResourceDb(’0.8.10’, ‘0.8.11’)
#3 /home/myroot/public_html/mywebsite/app/code/core/Mage/Core/Model/Resource/Setup.php(161): Mage_Core_Model_Resource_Setup->applyUpdates()
#4 /home/myroot/public_html/mywebsite/app/code/core/Mage/Core/Model/App.php(363): Mage_Core_Model_Resource_Setup::applyAllUpdates()
#5 /home/myroot/public_html/mywebsite/app/code/core/Mage/Core/Model/App.php(295): Mage_Core_Model_App->_initModules()
#6 /home/myroot/public_html/mywebsite/app/Mage.php(596): Mage_Core_Model_App->run(Array)
#7 /home/myroot/public_html/mywebsite/index.php(80): Mage::run(’’, ‘store’)
#8 {main}

 
Magento Community Magento Community
Magento Community
Magento Community
 
nickvahalik
Jr. Member
 
Total Posts:  4
Joined:  2008-05-19
 

Sounds like you imported the tables and the export didn’t have the proper table collation.  You need to update the encoding and collation of your DB tables to be UTF-8.

If you have Wiz installed, you can copy and paste this code into a php file and then run the script like so:

wiz magento-script <file you saved the script as>

<?php

$db 
Mage::getSingleton('core/resource')->getConnection();
$dbConfig $db->getConfig();

$db->query('SET FOREIGN_KEY_CHECKS = 0;');

$characterSet 'utf8';
$collation 'utf8_general_ci';

$statementBuilder $db->query("SELECT distinct CONCAT( 'alter table ', TABLE_SCHEMA, '.', TABLE_NAME, '  CONVERT TO CHARACTER SET $characterSet COLLATE $collation;' ) as query, TABLE_NAME as t FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '{$dbConfig['dbname']}';");
while (
$r $statementBuilder->fetch()) {
    $db
->query($r['query']);
    echo 
$r['t'" converted to $characterSet/$collation." PHP_EOL;
}

(This script is an adaptation for Wiz from this page: http://www.edmondscommerce.co.uk/mysql/mysql-bulk-update-collation-and-character-set-for-entire-database/)

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top