Mixed collations in database after upgrade

[arthurdent]

I've recently upgraded from 1.3.7 to 1.5.0RC1 (+RC2 upgrade). I've set define('CHARSET', 'iso-8859-1') in english.php to keep the original charset, but I didn't realise that I should also have changed the DB_CHARSET in zc_install/includes/installer_params.php to latin1 (https://www.zen-cart.com/tutorials/i...hp?article=414) before running the upgrading.

I now see that the tables that I copied over from the old db are latin1_swedish_ci, but the new 1.5.0 tables (admin_menus, admin_pages, admin_pages_to_profiles, admin_profiles) are utf8_general_ci

I'm assuming this won't cause any problems as 1) I'm not using Admin Profiles and 2) those tables are unlikely to contain any foreign characters anyway, but I'm just checking that having mixed collations in a db isn't a bad thing per se. If it is, can I just manually change them to latin1 in PhpMyAdmin?

Thanks

[DrByte]

No, you should never "just change" a table's collation blindly in phpMyAdmin. There's a lot more to it than that, including changing individual fields as well as ensuring data integrity.

It's certainly better to NOT be mixing collations unintentionally.

As far as your assumptions about Admin Profiles, when you move to v1.5, you ARE using the new admin users/security/profiles feature that comes in 1.5. That is NOT the same as the "admin profiles addon" to which I suspect you're thinking you're referring. But, the table names you mentioned ARE part of the core admin security functionality, and not superfluous pieces to be ignored.

Since the RCx releases are still beta software, they're not intended to be used directly on live sites.
I would suggest that you re-do your upgrade after ensuring you've set things to stay in latin1 mode consistently.

[arthurdent]

Ah, I was mistakenly under the impression that RC versions weren't beta... the 1.5.0 BETA post does have DO NOT USE ON LIVE SITES!!! in big red letters... :)

Can I clarify your advice - the post-upgrade db has had new products/customers/orders, so I can't really reimport the old db to do the upgrade again, and I assume if I just export the current db, change DB_CHARSET in installer_params.php to latin1 and reimport, the relevant tables will still be utf8 as they're already defined. And can you Upgrade from a db that's already been upgraded?

[DrByte]

Can I clarify your advice - the post-upgrade db has had new products/customers/orders, so I can't really reimport the old db to do the upgrade again

Correct.

and I assume if I just export the current db, change DB_CHARSET in installer_params.php to latin1 and reimport, the relevant tables will still be utf8 as they're already defined.

Correct. That would be moot, since there's nothing to upgrade anyway.

And can you Upgrade from a db that's already been upgraded?

Um ... moot. No.


So ... your next question is going to be "so what do I do now?".

Lest I sound like I'm contradicting myself here, use phpMyAdmin and change those few tables, and all the fields within them, to the appropriate collation/charset. BUT BEFORE YOU DO THAT, check EVERY record in EACH of those tables and make sure NOTHING in ANY of those records contains extended characters. If they do, then the conversion process is MUCH more complicated, and really requires proper handling by someone who knows the big picture of what's going on and exactly what they're doing.

Yes, I know, you're going to tell me that your data is fine because you've never used the features in those tables, and when you look at the data you'll just find your own admin user that's using only normal common english characters. Consider yourself fortunate.

[arthurdent]

Thanks as ever DrB.

Moot. There's a word you don't hear too often these days.