Database Conversion Tools [Support Thread]

[lat9]

@NWCE, since I'm not familiar with the Instant Search plugin (and how it might modify the schema of the products and/or products_description tables) I'll suggest that you post in that plugin's support-thread to find out that information.

[NWCE]

Thanks for your reply lat9.....I will do that!

[gernot]

Hello,
I am back to working with this tool, as part of upgrading ZenCart v1.5.6c to v2.1.0, and have some questions.
I have tested the conversion successfully with utf8mb4_unicode_520_ci, only having to adjust some indices that needed truncating as previously advised in this thread.

(1) I would like to use the collation utf8mb4_0900_ai_ci, which is not currently available in my 5.5.5-10.3.24-MariaDB-2 install (pinned on my linux system to block upgrades). Does the conversion need to be made on the current version of MariaDB, or can I upgrade the database to the 11.x which is compatible with ZenCart v2.1.0 (updating my.cnf as part of the process), and then convert, which I believe will lead to better defaults.

(2) Alternatively, if I do the conversion at the current state of MariaDB version 10.3.24, and thereafter upgrade MariaDB to 11.x, could I run the conversion again, merely changing the collation this time?

In either case I think I will need to add the collation to the code in the same way that the current utf8mb4_unicode_520_ci collation is there.

[lat9]

Hello,
I am back to working with this tool, as part of upgrading ZenCart v1.5.6c to v2.1.0, and have some questions.
I have tested the conversion successfully with utf8mb4_unicode_520_ci, only having to adjust some indices that needed truncating as previously advised in this thread.

(1) I would like to use the collation utf8mb4_0900_ai_ci, which is not currently available in my 5.5.5-10.3.24-MariaDB-2 install (pinned on my linux system to block upgrades). Does the conversion need to be made on the current version of MariaDB, or can I upgrade the database to the 11.x which is compatible with ZenCart v2.1.0 (updating my.cnf as part of the process), and then convert, which I believe will lead to better defaults.

(2) Alternatively, if I do the conversion at the current state of MariaDB version 10.3.24, and thereafter upgrade MariaDB to 11.x, could I run the conversion again, merely changing the collation this time?

In either case I think I will need to add the collation to the code in the same way that the current utf8mb4_unicode_520_ci collation is there.

Adding an unsupported collation to the converter will result in an unusable database on your 5.5.5 site. I'll suggest going with path #2 identified above.

[lat9]

Adding an unsupported collation to the converter will result in an unusable database on your 5.5.5 site. I'll suggest going with path #2 identified above.

Make sure, also, to run the zc210 zc_install process before you change any database collations to utf8mb4. The zc_install process (at the zc158 step?) takes care of the resizing of various varchar indices.

[gernot]

Hello @lat9,
Thank you for your comments. I did not realize that there were downsides to doing the change at v1.5.6. I thought it would be better to do it before upgrading (the ZenCart DB) to v2.1.0, but in fact it turns out I should first upgrade ZenCart and then do the utfmb8 conversion? Or are you referring only to the collation having to wait?

For question (1), the intention was to upgrade the DB so that the collation would be available.

I've also just realized that the latest collation (default in MariaDB 11.6) is utf8mb4_uca1400_ai_ci (using Unicode Collation Algorithm 1400) rather than the now already older utf8mb4_0900_ai_ci (using Unicode Collation Algorithm 900).

So to conclude, the better way, to take advantage of changes in ZenCart installers, would be as follows (all on a test cart!):
(0) create a backup of the DB (done)
(1) upgrade ZenCart files to v2.1.0 (done)
(2) upgrade ZenCart DB to v2.1.0 by running the installer
(3) upgrade the underlying database version from MariaDB 10.3.24 to 11.x
(4) backup DB again
(5) convert from current utf8(mb3) to utf8mb4 and collation to utf8mb4_uca1400_ai_ci (which I would have to add to the code as an option).

[lat9]

Hello @lat9,
Thank you for your comments. I did not realize that there were downsides to doing the change at v1.5.6. I thought it would be better to do it before upgrading (the ZenCart DB) to v2.1.0, but in fact it turns out I should first upgrade ZenCart and then do the utfmb8 conversion? Or are you referring only to the collation having to wait?

For question (1), the intention was to upgrade the DB so that the collation would be available.

I've also just realized that the latest collation (default in MariaDB 11.6) is utf8mb4_uca1400_ai_ci (using Unicode Collation Algorithm 1400) rather than the now already older utf8mb4_0900_ai_ci (using Unicode Collation Algorithm 900).

So to conclude, the better way, to take advantage of changes in ZenCart installers, would be as follows (all on a test cart!):
(0) create a backup of the DB (done)
(1) upgrade ZenCart files to v2.1.0 (done)
(2) upgrade ZenCart DB to v2.1.0 by running the installer
(3) upgrade the underlying database version from MariaDB 10.3.24 to 11.x
(4) backup DB again
(5) convert from current utf8(mb3) to utf8mb4 and collation to utf8mb4_uca1400_ai_ci (which I would have to add to the code as an option).

Sounds like a good plan; just remember to change both configure.php files to indicate that the DB_CHARSET is utf8mb4.

[gernot]

Hello @lat9,
Understood, thank you very much.

[gernot]

Hi @lat9,
Incidentally, the reason I was under the impression I needed to run the conversion at ZenCart v1.5.6 rather than after upgrading ZenCart to v2.1.0, is because the plugin is advertised to be compatible with v1.5.6, v1.5.7 and v1.5.8.
Hence I thought that I won't be able to run it with a later ZenCart version (compounded by the now known to be mistaken assumption that moving to real UTF-8 earlier rather than later was better).
I will let you know how my tests proceed with ZenCart v2.1.0.

[lat9]

Hi @lat9,
Incidentally, the reason I was under the impression I needed to run the conversion at ZenCart v1.5.6 rather than after upgrading ZenCart to v2.1.0, is because the plugin is advertised to be compatible with v1.5.6, v1.5.7 and v1.5.8.
Hence I thought that I won't be able to run it with a later ZenCart version (compounded by the now known to be mistaken assumption that moving to real UTF-8 earlier rather than later was better).
I will let you know how my tests proceed with ZenCart v2.1.0.

Thanks for that update; the plugin is now "advertised" as being compatible with v2.0.x and v2.1.0 as well.