Error Message

I’m trying to sort out what the following error means. It happens every time I try to edit an item in Admin:



Database error: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation ‘=’ (1267)

Invalid query: SELECT cscart_blocks.block_id, cscart_block_descriptions.description, cscart_block_positions.group_id as parent_id FROM cscart_blocks LEFT JOIN cscart_block_descriptions ON cscart_block_descriptions.block_id = cscart_blocks.block_id AND cscart_block_descriptions.object_type = ‘B’ AND cscart_block_descriptions.lang_code = ‘EN’ LEFT JOIN cscart_block_positions ON cscart_block_positions.block_id = cscart_blocks.block_id AND cscart_block_positions.location = cscart_blocks.location AND (cscart_block_positions.object_id = 20364 OR (cscart_block_positions.object_id = ‘0’ AND NOT EXISTS(SELECT * FROM cscart_block_positions as bp WHERE bp.block_id = cscart_blocks.block_id AND bp.location = cscart_blocks.location AND bp.object_id = 20364))) WHERE cscart_blocks.block_type = ‘G’ AND (cscart_blocks.location = ‘products’ OR cscart_blocks.location = ‘all_pages’)



It took almost seven hours for me to import the file, so I really don’t want to have to retry that…ideas?

[quote name=‘nissansr’]

Database error: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT)[/quote]



You need to change the database to ‘utf8_unicode_ci’ for all tables, otherwise you encounter that issue many times.



J.

How exactly do I do that? This error just started after upgrading to the latest version of CSC and I’m not that great with PHPAdmin

NissanSR…did you figure out the same problem I am having after tech updated my cs cart (but I probably did something to cause the collation error)?



Hope to hear from you…mickeymouse

This is often caused when changing hosting providers because not all use the same default collation type.



It is a PIA to manually change collations and charsets for multiple individual tables and you should also verify and possibly change the default type for your database so this doesn’t continue to happen every time you add new tables or upgrade.



I have a script that can repair this issue easily but can’t make it publicly available because it is server and client specific and could destroy your database if not configured properly before it is ran so I would prefer to do this for you.



I would need FTP access and know what collation is preferred and is set as default for your database. utf8_unicode is common and fine for most clients using the English language but it may not be best for others.



I would prefer to have temporary access to your control panel so I can verify this information and make a backup first.

[quote name=‘mickeymouse1000’]NissanSR…did you figure out the same problem I am having after tech updated my cs cart (but I probably did something to cause the collation error)?



Hope to hear from you…mickeymouse[/QUOTE]



I had support fix it for me, but basically it was caused by “cscart_block_positions”

nissansr: thank you for the answer. many forums don’t finish the thread to help the rest of us but i notice that cs carts rarely have no replies to calls for help.



scombs: we started to upload info from scratch and got the error message. tech support upgraded the site for me to their latest version.

[quote name=‘mickeymouse1000’]nissansr: thank you for the answer. many forums don’t finish the thread to help the rest of us but i notice that cs carts rarely have no replies to calls for help.



scombs: we started to upload info from scratch and got the error message. tech support upgraded the site for me to their latest version.[/QUOTE]



I ran the collation script, please check your PM

wow…talk about fast service. i followed the rest of the directions you sent and after 30 seconds the site was working again. guess i will have to work into the night instead of goofing off…or maybe i will be goofing less and working more, but if there isn’t SOME goofing i will have to resort to drinking! thank you very much!