Database Conversion Tools [Support Thread]

@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.

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

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.

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.
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.

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).

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).
Sounds like a good plan; just remember to change both configure.php files to indicate that the DB_CHARSET is utf8mb4.

Hello @lat9,
Understood, thank you very much.

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.

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.
Thanks for that update; the plugin is now "advertised" as being compatible with v2.0.x and v2.1.0 as well.

Hi @lat9
I am trying to set up a test DB in ZC 2.1.0, and import the old 1.5.6c DB into that.
However, if I try to use a DB with ZC 2.1.0, I have to set up admin & storefront configure.php first.

I am confused now, since it appears that utf8mb4 is a requirement in the configure.php:

define('DB_CHARSET', '%%_DB_CHARSET_%%'); // 'utf8mb4' required. If using older 'utf8' or 'latin1', convert your database to utf8mb4. See conversion util in docs site.

So it appears I would need to convert the database first, other charsets are no longer supported in 2.1.0?

If so, then I suppose I will need to revert to the other plan option, of converting to utf8mb4 on 1.5.6c, and then converting again later in 2.1.0, only for the the collation update?

Hi @lat9
I suppose I might try to run a DB upgrade using ZC 1.5.8a, where an option of utf8 is still there, and where you mentioned there is automatic adjusting of VARCHAR columns.
I am not sure if this is viable though using the base ZC files and my 1.5.6c database, or whether I would need to customize the base ZC files first, so as to let the installer "know" about the extra tables. Which would be several more weeks of work.
All in all, this seems like too many dependencies not all in one place.

I have gone for the to me safer-sounding convert-now approach in ZC 1.5.6c.
The conversion, as previously noted, deleted 3 indices on my test DB, which I then recreated manually using truncated lengths of fields as applicable.

I now have a utf8mb4 DB with a fairly old collation which I plan to update in ZC 2.1.0.

Hi,
I have recently upgraded our site from 1.5.7c to 2.1.0. After upgrading I used the Zen Cart Database Conversion Tool to convert from UTF8 to UTF8MB4.

A customer has informed us that they are no longer able to log into their account. I have checked our old test customer account and this did not seem to work either.

It looks as though the passwords may not have converted to the new format or were affected by the upgrade to 2.1.0. The accounts can be accessed by resetting the passwords.

If anyone can offer any advice on what may be causing this issue it would be much appreciated.
Many thanks
Rick

richard7315:

Hi,
I have recently upgraded our site from 1.5.7c to 2.1.0. After upgrading I used the Zen Cart Database Conversion Tool to convert from UTF8 to UTF8MB4.

A customer has informed us that they are no longer able to log into their account. I have checked our old test customer account and this did not seem to work either.

It looks as though the passwords may not have converted to the new format or were affected by the upgrade to 2.1.0. The accounts can be accessed by resetting the passwords.

If anyone can offer any advice on what may be causing this issue it would be much appreciated.
Many thanks
Rick
I'm remembering an issue with "special" characters (i.e. & < > " ') in admin passwords, I don't know if that's what's going on.

If you have access to the old database, you might verify that the old password still works, and if it does, export the customers table and try updating the customers_password field in the new database.

Do in a test environment first, of course.
This was an issue in the old database updater but I have not seen it in this current version, even after running it dozens of times. Sorry you are having this issue.

Hi lat9 and swguy
Many thanks for your replies. After further investigation I have found some older test account login details. I can confirm that these passwords are working after upgrading, but they don’t contain special characters.

It appears that special characters in the customers password could be the cause of the issue. I will contact the customer to see if this is the case.
Rick

Hi All,

Modules Installed
Zencart Version 1.5.8a / Simple Popup
Bootstrap Template Version 3.7.6 / Square_Webpay
TY Tracker Version 1.0.1 / PayPal
Sitemap Version 3.9.9 / Changed Files Report
CEON Mappings Version 2.2.0 / Product Review Reminder
Quick Update Version 2.10 / Numinix Product Fields 3.1.3
Printable Price List Version 4.0.1 / Order Comment Boilmaker
Easy Populate Version 7.039ZC / Email Archive Manager Version 3.0
Handling Fee Version 1.2.0

Just got this error message when backing up database (using php admin) from my live server and then restoring to my test site.

1. Backed up the live site database.

2. Changed the file name to the test site database file name.

3. Then I used the file with the test database name and used phpadmin restore database option

I then got this message and it only restored upto the 16 of this month.

Error message is:
Error: SyntaxError: Unexpected token '<', " <!DOCTYPE "... is not valid JSON

I have copied the database before without any issues before but this time I got the error message.

The only thing that I have done is added over 5000 products in the last couple of weeks using EP4 and appears to have gone through without any issues.

As to error logs I have not been getting any at this stage only today after doing a copy of the database.

Bruce1952:

Hi All,

Modules Installed
Zencart Version 1.5.8a / Simple Popup
Bootstrap Template Version 3.7.6 / Square_Webpay
TY Tracker Version 1.0.1 / PayPal
Sitemap Version 3.9.9 / Changed Files Report
CEON Mappings Version 2.2.0 / Product Review Reminder
Quick Update Version 2.10 / Numinix Product Fields 3.1.3
Printable Price List Version 4.0.1 / Order Comment Boilmaker
Easy Populate Version 7.039ZC / Email Archive Manager Version 3.0
Handling Fee Version 1.2.0

Just got this error message when backing up database (using php admin) from my live server and then restoring to my test site.

1. Backed up the live site database.

2. Changed the file name to the test site database file name.

3. Then I used the file with the test database name and used phpadmin restore database option

I then got this message and it only restored upto the 16 of this month.

Error message is:
Error: SyntaxError: Unexpected token '<', " <!DOCTYPE "... is not valid JSON

I have copied the database before without any issues before but this time I got the error message.

The only thing that I have done is added over 5000 products in the last couple of weeks using EP4 and appears to have gone through without any issues.

As to error logs I have not been getting any at this stage only today after doing a copy of the database.

1. That's not the full error log. A full error log will include the page from which the log originated as well as a backtrace of who-called-who.
2. This has nothing to do with the Database Conversion Tool(s).

lat9:

1. That's not the full error log. A full error log will include the page from which the log originated as well as a backtrace of who-called-who.
2. This has nothing to do with the Database Conversion Tool(s).

Excuse me no error log was produced in zencart the error came up on screen from the phpadmin panel when doing the update to the test site of the database.

I don't know where this error log you are talking about be as it is not in the zencart error log area at all on the test site.