Database Conversion Tools [Support Thread]

This plugin's been around for a while (https://www.zen-cart.com/downloads.php?do=file&id=2367) and deserves its own support thread!

I've just submitted v4 to the Plugins area. That version also checks the overall database collation and, if it's not currently utf8, makes that conversion, too!

The previous versions of this plugin only focused on converting the table-based collations. If a database started with a collation of latin1_general_ci, for instance, the previous versions would properly convert all the database tables to use utf8_general_ci but leave the overall collation unchanged.

The next time a new table is added to that database, that table and all character fields within it would default to the database's default: latin1_general_ci. Not a good thing!

v4 is now available for download from the Plugins.

Hi

I have latin1 and utf8 tables in my database, is this module ok to use on this?

Regards

Mike

Quote:

---

Originally Posted by mike14017

Hi

I have latin1 and utf8 tables in my database, is this module ok to use on this?

Regards

Mike

---

Yes, that's the problem that this module solves! The plugin's processing will convert the database, all tables and their fields to utf8 ... just remember to update your configure.php files (both admin and storefront) to identify that the database's DB_CHARSET is now utf8 instead of latin1.

You should also change the CHARSET definition in your store's base language files (e.g. /includes/languages/english.php, /YOUR_ADMIN/includes/languages/english.php) to reflect a utf8-type character encoding.

Hi

Done all that and converted DB. Everything seems fine.

What a cracking module, thank you.

Mike

Thanks for a great tool Cindy - fast and seamless.

cheers,
Mike

Quote:

---

Originally Posted by shags38

Thanks for a great tool Cindy - fast and seamless.

cheers,
Mike

---

Mike, it's a collaborative effort ... with the primary thanks going to @a_berenzin (original author) for the idea and the base processing. Additional changes by @mc12345678 and myself were simply adding "frosting to the cake" to improve its processing.

I've just submitted v1.2-lat9 for the plugin moderators' review and will post back here when it's available for download.

This version, split from the plugin's base GitHub repository, adds the quotes required around text-fields' default values and now makes use of the site's database settings present in the associated /includes/configure.php.

Approved.

Houston, we have a problem.

It's never 'fun' upgrading a previous site to zc157, especially when the database contains the dreaded mixed collations. I've had the pleasure of updating a couple of sites that started with a mixture of various flavors of latin1 and utf8 and adopted the following procedure for those upgrades.

1. Change the site's configure.php files, setting DB_CHARSET to 'latin1'.
2. Run /zc_install/sql/install/zero_dates_cleanup.sql. Make sure it's the updated version of that script which uses the MySql CAST function.
3. Run /zc_install to upgrade the site's database.
4. Run the (older) convert_db2utf8.php script.
5. Change the site's configure.php files, setting DB_CHARSET to 'utf8'.
6. Run the utf8mb4_conversion.php script.
7. Change the site's configure.php files, setting DB_CHARSET to 'utf8mb4'.

All was going well, until I added a plugin whose installation adds a table which includes one or more character (i.e. 'char' or 'varchar') fields. When that table was added, it and its fields' collations were set to 'utf8mb4_unicode_520_ci'. Unfortunately, while the pre-existing tables' collations were set to 'utf8mb4_unicode_520_ci', those tables' fields were set to 'utf8mb4_general_ci'. When a SQL query that binds the newly-added table to a pre-existing one, I was met with:

HP Fatal error: 1267:Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_520_ci,IMPLICIT) for operation '='

I was able to correct the issue by logging into the site's webhost and, using phpMyAdmin's Operations tab, changing the collation of the database, all tables and all table fields to utf8mb4_general_ci.

I've got a beta release of my update to the convert_db2utf8 and utf8mb4-conversion utilities available for (er) some beta testing: https://github.com/lat9/database-con...g/v2.0.0-beta1

Please remember that this is a beta, so make sure to back up any database that you're using to kick the tires! This tool provides the following features:

1. Detects and corrects zero-date defaults in database fields.
2. Detects and corrects zero-date values in database fields.
3. Reports on any mixed-collations present in the database and provides a means to correct them.
4. Provides a means to update a latin1 database to either utf8 or utf8mb4 or a utf8 database to utf8mb4.

I'd especially appreciate any testing on 'true' multi-byte (e.g. Japanese or Hebrew) databases, as I have no access to such an installation.

Any reports of issues (and, hopefully successes), please report back here.

Fantastic news, thank you Cindy.

For those who may not be aware, moving to UTF-8 is a best practice for Zen Cart. It allows you to support emojis, various foreign languages (including foreign names), and many other things. You can read about it here:

https://docs.zen-cart.com/user/upgra...nvert_to_utf8/

Quote:

---

Originally Posted by swguy

Fantastic news, thank you Cindy.

For those who may not be aware, moving to UTF-8 is a best practice for Zen Cart. It allows you to support emojis, various foreign languages (including foreign names), and many other things. You can read about it here:

https://docs.zen-cart.com/user/upgra...nvert_to_utf8/

---

I believe that it's the database's utf8mb4 character-set that fully enables those features.

The updated Database Conversion Tool is now available for download: https://www.zen-cart.com/downloads.php?do=file&id=2367

Additional information can be found on the tool's GitHub repository: https://github.com/lat9/database-converter

Hi All,

Just received this message after running the program on a live server:

ALTER TABLE `product_type_layout` DROP INDEX `unq_config_id_zen`, DROP INDEX `idx_key_value_zen`, DROP INDEX `idx_type_id_sort_order_zen`
1075: Incorrect table definition; there can be only one auto column and it must be defined as a key

I am running version 1.5.5.e and I am not sure what I should be doing with this error so I have restored the backup database that I had.

Any assistance would be a great help because the Collations (of live server before running the module) in the database consist of the following:

latin1_swedish_ci
utf8mb4_general_ci
utf8_general_ci

@Bruce1952, please access the site's cPanel's phpMyAdmin and post a screenshot of the "Structure View" of the site's product_type_layout table.

Weird issue:

[30-Jul-2023 18:30:02 UTC] PHP Fatal error: Uncaught mysqli_sql_exception: Incorrect table definition; there can be only one auto column and it must be defined as a key in /Users/scott/sites/client/ConvertDb.php:736
Stack trace:
#0 /Users/scott/sites/client/ConvertDb.php(736): mysqli_query(Object(mysqli), 'ALTER TABLE `ze...')
#1 /Users/scott/sites/client/ConvertDb.php(375): ConvertDb->doQuery('ALTER TABLE `ze...')
#2 /Users/scott/sites/client/convert_database.php(282): ConvertDb->updateTableCollations('zen_better_toge...', 'utf8mb4_unicode...')
#3 {main}
thrown in /Users/scott/sites/client/ConvertDb.php on line 736

What's confusing is that there *is* only one auto column and it *is* defined as a key. So I'm not sure why this is failing.

Also, this backtrace was not written to /logs, which might be a feature to add in the next version.

Another database, different issue:

[30-Jul-2023 22:06:38 UTC] PHP Fatal error: Uncaught mysqli_sql_exception: Specified key was too long; max key length is 1000 bytes in /Users/scott/sites/thatsoft_store/ConvertDb.php:736
Stack trace:
#0 /Users/scott/sites/thatsoft_store/ConvertDb.php(736): mysqli_query(Object(mysqli), 'CREATE INDEX `i...')
#1 /Users/scott/sites/thatsoft_store/ConvertDb.php(395): ConvertDb->doQuery('CREATE INDEX `i...')
#2 /Users/scott/sites/thatsoft_store/convert_database.php(282): ConvertDb->updateTableCollations('coupons', 'utf8mb4_unicode...')
#3 {main}
thrown in /Users/scott/sites/thatsoft_store/ConvertDb.php on line 736

Keys on coupons table are as per basic install in 1.5.8.

Running in PHP 8.1 + MySQL 5.7.39

Quote:

---

Originally Posted by swguy

Weird issue:

[30-Jul-2023 18:30:02 UTC] PHP Fatal error: Uncaught mysqli_sql_exception: Incorrect table definition; there can be only one auto column and it must be defined as a key in /Users/scott/sites/client/ConvertDb.php:736
Stack trace:
#0 /Users/scott/sites/client/ConvertDb.php(736): mysqli_query(Object(mysqli), 'ALTER TABLE `ze...')
#1 /Users/scott/sites/client/ConvertDb.php(375): ConvertDb->doQuery('ALTER TABLE `ze...')
#2 /Users/scott/sites/client/convert_database.php(282): ConvertDb->updateTableCollations('zen_better_toge...', 'utf8mb4_unicode...')
#3 {main}
thrown in /Users/scott/sites/client/ConvertDb.php on line 736

What's confusing is that there *is* only one auto column and it *is* defined as a key. So I'm not sure why this is failing.

Also, this backtrace was not written to /logs, which might be a feature to add in the next version.

---

Shoot a copy of the original, zipped or gzipped database to my direct email and I'll give it a look-see.

Quote:

---

Originally Posted by swguy

Another database, different issue:

[30-Jul-2023 22:06:38 UTC] PHP Fatal error: Uncaught mysqli_sql_exception: Specified key was too long; max key length is 1000 bytes in /Users/scott/sites/thatsoft_store/ConvertDb.php:736
Stack trace:
#0 /Users/scott/sites/thatsoft_store/ConvertDb.php(736): mysqli_query(Object(mysqli), 'CREATE INDEX `i...')
#1 /Users/scott/sites/thatsoft_store/ConvertDb.php(395): ConvertDb->doQuery('CREATE INDEX `i...')
#2 /Users/scott/sites/thatsoft_store/convert_database.php(282): ConvertDb->updateTableCollations('coupons', 'utf8mb4_unicode...')
#3 {main}
thrown in /Users/scott/sites/thatsoft_store/ConvertDb.php on line 736

Keys on coupons table are as per basic install in 1.5.8.

Running in PHP 8.1 + MySQL 5.7.39

---

What was the database's base charset/collation and that for the coupons table originally?

> What was the database's base charset/collation and that for the coupons table originally?

UTF8.

sending both db's as requested

Quote:

---

Originally Posted by swguy

Weird issue:

[30-Jul-2023 18:30:02 UTC] PHP Fatal error: Uncaught mysqli_sql_exception: Incorrect table definition; there can be only one auto column and it must be defined as a key in /Users/scott/sites/client/ConvertDb.php:736
Stack trace:
#0 /Users/scott/sites/client/ConvertDb.php(736): mysqli_query(Object(mysqli), 'ALTER TABLE `ze...')
#1 /Users/scott/sites/client/ConvertDb.php(375): ConvertDb->doQuery('ALTER TABLE `ze...')
#2 /Users/scott/sites/client/convert_database.php(282): ConvertDb->updateTableCollations('zen_better_toge...', 'utf8mb4_unicode...')
#3 {main}
thrown in /Users/scott/sites/client/ConvertDb.php on line 736

What's confusing is that there *is* only one auto column and it *is* defined as a key. So I'm not sure why this is failing.

Also, this backtrace was not written to /logs, which might be a feature to add in the next version.

---

While there is only one AUTO_INCREMENT column in that table, its value is specified as a KEY ... not a PRIMARY KEY. The tool sets a table's AUTO_INCREMENT field as its PRIMARY KEY and then adds any additional table-keys. That's why the error message is being displayed, since the PRIMARY KEY is now the same as the to-be-added KEY.

Code:

---

CREATE TABLE `zen_better_together_admin` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `linkage_type` char(20) CHARACTER SET utf8mb4 NOT NULL,
  `field1` int(11) NOT NULL DEFAULT '0',
  `field2` int(11) NOT NULL DEFAULT '0',
  `active` char(1) CHARACTER SET utf8mb4 NOT NULL DEFAULT 'Y',
  `discount_units` char(1) CHARACTER SET utf8mb4 NOT NULL DEFAULT '%',
  `discount_amount` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `date_added` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',
  `start_date` date NOT NULL DEFAULT '0001-01-01',
  `end_date` date NOT NULL DEFAULT '0001-01-01',
  KEY `idx` (`id`),
  KEY `idx_date_added` (`date_added`)
) ENGINE=MyISAM AUTO_INCREMENT=44 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

---

The tool uses only a site's /includes/local/configure.php (if present) and the /includes/configure.php to determine the database-related configuration values, so it's not aware of the site's /logs sub-directory. That's why the error(s) aren't created in a log file.

Note: I'm running my testing with the following MySQL configuration settings:

Code:

---

define('DB_MYSQL_MODE', 'ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION');

---

Note, too, that while the site's configure.php indicates a DB_PREFIX of 'zen_', there are a number of tables in the database that don't use a 'zen_' prefix. Those won't participate in any charset/collation conversion.

Quote:

---

Originally Posted by swguy

Another database, different issue:

[30-Jul-2023 22:06:38 UTC] PHP Fatal error: Uncaught mysqli_sql_exception: Specified key was too long; max key length is 1000 bytes in /Users/scott/sites/thatsoft_store/ConvertDb.php:736
Stack trace:
#0 /Users/scott/sites/thatsoft_store/ConvertDb.php(736): mysqli_query(Object(mysqli), 'CREATE INDEX `i...')
#1 /Users/scott/sites/thatsoft_store/ConvertDb.php(395): ConvertDb->doQuery('CREATE INDEX `i...')
#2 /Users/scott/sites/thatsoft_store/convert_database.php(282): ConvertDb->updateTableCollations('coupons', 'utf8mb4_unicode...')
#3 {main}
thrown in /Users/scott/sites/thatsoft_store/ConvertDb.php on line 736

Keys on coupons table are as per basic install in 1.5.8.

Running in PHP 8.1 + MySQL 5.7.39

---

What 'flavor' of utf8mb4_unicode collation was requested? There are a couple, e.g. utf8mb4_unicode_ci, utf8mb4_unicode_520_ci.

> What 'flavor' of utf8mb4_unicode collation was requested?
utf8mb4_unicode_ci

> there are a number of tables in the database that don't use a 'zen_' prefix.
Yep just ignore those. They aren't for Zen Cart.

> While there is only one AUTO_INCREMENT column in that table, its value is specified as a KEY ... not a PRIMARY KEY.

Ah, nice catch. This is an ancient table that i should have junked anyway. Let's call issue 1 fixed with the caveat that people need to check by hand if any created tables have this problem.

Cleaned up a couple more issues like this and it worked like a champ. Thank you! (This was problem #1 - still unsure about #2)

On #2

> [30-Jul-2023 22:06:38 UTC] PHP Fatal error: Uncaught mysqli_sql_exception: Specified key was too long; max key length is 1000 bytes in /Users/scott/sites/thatsoft_store/ConvertDb.php:736

I have modified the field length of coupon_code but only from 32 -> 255. Not sure how it got to 1000.

Quote:

---

Originally Posted by swguy

On #2

> [30-Jul-2023 22:06:38 UTC] PHP Fatal error: Uncaught mysqli_sql_exception: Specified key was too long; max key length is 1000 bytes in /Users/scott/sites/thatsoft_store/ConvertDb.php:736

I have modified the field length of coupon_code but only from 32 -> 255. Not sure how it got to 1000.

---

That would be it; when Zen Cart started supporting utf8mb4, all char/varchar fields that acted as indices were changed to 191 from 255. See this MySQL link for additional details: https://dev.mysql.com/doc/refman/5.7...onversion.html

Holy cow! Thanks for this detective work. Case closed!


Note for other upgraders: these changes (255->191) were done in the 1.5.6 upgrade.

And now I realize you provided an INCREDIBLY CLEAR error message at the bottom of the output that I very cleverly missed because I was so busy trying to chase down the log file. :(

Nope there *is* still an issue. This message only works in PHP 7.4. When you run in PHP 8.1, it dies silently.

You can easily dup this by adding the old better_together_admin table def, which has the primary key error.

Code:

---

DROP TABLE IF EXISTS better_together_admin;
CREATE TABLE better_together_admin (
  id int(11) NOT NULL auto_increment,
  linkage_type char(20) NOT NULL default ' ',
  field1 int(11) NOT NULL default '0',
  field2 int(11) NOT NULL default '0',
  active char(1) NOT NULL default 'Y',
  discount_units char(1) NOT NULL default '%',
  discount_amount decimal(15,4) NOT NULL default '0.0000',
  date_added datetime NOT NULL default '0001-01-01 00:00:00',
  start_date date NOT NULL default '0001-01-01',
  end_date date NOT NULL default '0001-01-01',
  KEY idx_date_added (date_added),
  KEY idx(id)
) ENGINE=MyISAM;

---

(Not sure this helps, but the old converter also had an issue in PHP 8+ with silent death.)

Fix submitted for this issue.

Quote:

---

Originally Posted by swguy

Fix submitted for this issue.

---

Thanks for that; the update will be included in v2.0.1.

Quote:

---

Originally Posted by lat9

Thanks for that; the update will be included in v2.0.1.

---

v2.0.1 of the Database Conversion tool is now available for download: https://www.zen-cart.com/downloads.php?do=file&id=2367

This release contains a BUGFIX (see GitHub issue #8) that enables interoperability with PHP versions 8.0 and later. Thanks to @swguy for the identification and correction of the issue.

The use of the new converter plugin at https://www.zen-cart.com/downloads.php?do=file&id=2367 is now the recommended approach for UTF-8 conversions.

So I just garbed the new one. I do not see how or where to pick to convert to just utf8 its all utf8mb4 something

You want utf8mb4.

Quote:

---

Originally Posted by swguy

You want utf8mb4.

---

ok I guess i missed ready something where utf8 was what it should be

The converter fails out for me at Processing table [address_book]:
Error log says:
[19-Sep-2023 13:16:26 UTC] PHP Fatal error: Uncaught mysqli_sql_exception: Data too long for column 'address_title' at row 19641 in /home/pcs/dev/utf8mb4-conversion.php:170
Stack trace:
#0 /home/pcs/dev/utf8mb4-conversion.php(170): mysqli_query(Object(mysqli), 'ALTER TABLE `ad...')
#1 {main}
thrown in /home/pcs/dev/utf8mb4-conversion.php on line 170

I don't know the sort order, how can I find row 19641 to fix the address?

That's not a default field. You'll have to adjust your database to allow for the greater length required by UTF8MB4.

Ya, I had just figured out it's leftover garbage from an old mod. The uninstall SQL didn't clean up what it added. :censored: Yay, now I get to dig through the whole DB for extra garbage.

Quote:

---

Originally Posted by gothstone

The converter fails out for me at Processing table [address_book]:
Error log says:
[19-Sep-2023 13:16:26 UTC] PHP Fatal error: Uncaught mysqli_sql_exception: Data too long for column 'address_title' at row 19641 in /home/pcs/dev/utf8mb4-conversion.php:170
Stack trace:
#0 /home/pcs/dev/utf8mb4-conversion.php(170): mysqli_query(Object(mysqli), 'ALTER TABLE `ad...')
#1 {main}
thrown in /home/pcs/dev/utf8mb4-conversion.php on line 170

I don't know the sort order, how can I find row 19641 to fix the address?

---

What was the original definition of that address_title field? Perhaps varchar(255)? If that's the case, the maximum varchar length for a utf8mb4 database is 192, so you're looking for records in that table that are between 193 and 255 characters in length.

Ya, please ignore entirely. Not only was the column complete garbage, but i had the entirely wrong version of the plug-in from your github. The published one worked quite nicely. No clue what i was thinking or how i managed that.

Thanks for the follow-up!

It could be worthwhile to do a pre-check (just like the date pre-check) for this condition. I had to do a few ALTER TABLE commands to fix things like this:

CREATE INDEX `idx_last_page_url_zen` ON `whos_online` (last_page_url)
1071: Specified key was too long; max key length is 1000 bytes

Hello,
What would be the fix for that type of error?

Code:

---

CREATE INDEX `idx_last_page_url_zen` ON `whos_online` (last_page_url)
1071: Specified key was too long; max key length is 1000 bytes

---

Change table from ISAM to InnoDB?

Quote:

---

Originally Posted by gernot

Hello,
What would be the fix for that type of error?

Code:

---

CREATE INDEX `idx_last_page_url_zen` ON `whos_online` (last_page_url)
1071: Specified key was too long; max key length is 1000 bytes

---

Change table from ISAM to InnoDB?

---

Since I've never experienced this error myself, I'm not clear on what correction is needed.

Off the top of my head, I'm thinking that perhaps that table needs to be truncated prior to any collation conversion.

In ConvertDb.php, find this section in the getDbTablesAndFields method:

Code:

---

            $table['has_date_fields'] = false;
            $table['has_text_fields'] = false;
            $this->tables[$table_name] = [
                'info' => $table,
                'fields' => [],
            ];

            $fields = $this->doQuery(sprintf($fields_sql_base, $table_name));

---

... and add the highlighted section:

Code:

---

            $table['has_date_fields'] = false;
            $table['has_text_fields'] = false;
            $this->tables[$table_name] = [
                'info' => $table,
                'fields' => [],
            ];

            if ($table_name === 'whos_online' || $table_name === 'db_cache') {
                $this->doQuery('TRUNCATE TABLE ' . $table_name);
            }

            $fields = $this->doQuery(sprintf($fields_sql_base, $table_name));

---

Thank you,
I got the same error

HTML Code:

---

1071: Specified key was too long; max key length is 1000 bytes

---

on 3 different tables, and ran the program again and again until the results showed no more issues.
I'll re-restore the backup data into the testing DB and see what tables exactly those were, although if there are any logs I would be able to check there.
Did not see any location of logs though, are there any?

Quote:

---

Originally Posted by gernot

Thank you,
I got the same error

HTML Code:

---

1071: Specified key was too long; max key length is 1000 bytes

---

on 3 different tables, and ran the program again and again until the results showed no more issues.
I'll re-restore the backup data into the testing DB and see what tables exactly those were, although if there are any logs I would be able to check there.
Did not see any location of logs though, are there any?

---

No logs will be generated, unfortunately. Knowing which tables are affected will help.

> I got the same error ... on 3 different tables, and ran the program again and again until the results showed no more issues.

I suspect the relevant indexes aren't there any more - you should check.
No need to switch from ISAM to InnoDB though. Just create a shorter index before you do the conversion.

DROP INDEX idx_last_page_url_zen ON whos_online;
CREATE INDEX idx_last_page_url_zen ON whos_online (last_page_url(191));

This way when you convert to UTF8, you won't overflow.

Hi, excuse the long wait, I've imported the original data back into the test DB and run the convert_database.php.
Sure enough, the conversion stops at the table media_manager, with the error message:

HTML Code:

---

CREATE INDEX `idx_media_name_zen` ON `media_manager` (media_name)
1071: Specified key was too long; max key length is 1000 bytes;

---

Reviewing the tables I see the converter has stopped there, and I haven't run it again for now to proceed (which I did previously) to see which table it complained about next.
I don't understand the error at all. Looking at the table in question, it is all of 2 lines long, and media_name is about 2 dozen ASCII characters:

HTML Code:

---

MariaDB [zencartshoptest]> desc media_manager;
+---------------+--------------+------+-----+---------------------+----------------+
| Field        | Type        | Null | Key | Default            | Extra          |
+---------------+--------------+------+-----+---------------------+----------------+
| media_id      | int(11)      | NO  | PRI | NULL                | auto_increment |
| media_name    | varchar(255) | NO  |    |                    |                |
| last_modified | datetime    | NO  |    | 0001-01-01 00:00:00 |                |
| date_added    | datetime    | NO  |    | 0001-01-01 00:00:00 |                |
+---------------+--------------+------+-----+---------------------+----------------+
4 rows in set (0.001 sec)

MariaDB [zencartshoptest]> select * from media_manager;
+----------+---------------------------+---------------------+---------------------+
| media_id | media_name                | last_modified      | date_added          |
+----------+---------------------------+---------------------+---------------------+
|        1 | Russ Tippins - The Hunter | 2004-06-01 20:57:43 | 2004-06-01 20:42:53 |
|        2 | Help!                    | 2004-07-13 01:01:14 | 2004-07-12 17:57:45 |
+----------+---------------------------+---------------------+---------------------+
2 rows in set (0.001 sec)

---

It doesn't matter how long the current data is. The field is sized for 255 characters. When a character takes 4 bytes (UTF), you can see this is > 1000.

Ah, now I understand. I need to reduce the size of the field definition to be < 1000 chars(=bytes) when using 4-bytes per character.

Correct - or change the index to work on less than 1,000 characters.

Evening all,

I ran the tool and I believe corrected prior mismatch. However I'm still seeing the following message and unsure if it needs to be addressed.

Code:

---

Next Action
The database's character-set doesn't match DB_CHARSET. Congratulations, no database issues were found.

---

Here is what my current output states after using the tool:

Code:

---

MySQL Version:	8.0.36
Zen-Cart Database Version:	2.1.0
Database Name:	dbs12804405
Database Prefix:	'zencrt_'
Database Character Set:	utf8mb4
DB_CHARSET:	utf8
Database Collation:	utf8mb4_general_ci
Number of Tables:	115

---

I checked all my configure and language files and they are set to utf8 and utf-8 accordingly.

Unless this is relevant, found this under server info:

Database Variables: MySQL 8.0.36

Variable Name	Value

character_set_client	utf8mb3
character_set_connection	utf8mb3
character_set_database	utf8mb4
character_set_filesystem	binary
character_set_results	utf8mb3
character_set_server	utf8mb4
character_set_system	utf8mb3

Quote:

---

Originally Posted by Bubbadood

Evening all,

I ran the tool and I believe corrected prior mismatch. However I'm still seeing the following message and unsure if it needs to be addressed.

Code:

---

Next Action
The database's character-set doesn't match DB_CHARSET. Congratulations, no database issues were found.

---

Here is what my current output states after using the tool:

Code:

---

MySQL Version:	8.0.36
Zen-Cart Database Version:	2.1.0
Database Name:	dbs12804405
Database Prefix:	'zencrt_'
Database Character Set:	utf8mb4
DB_CHARSET:	utf8
Database Collation:	utf8mb4_general_ci
Number of Tables:	115

---

I checked all my configure and language files and they are set to utf8 and utf-8 accordingly.

---

Right, you need to change the DB_CHARSET (in both the /admin/includes/configure.php and /includes/configure.php to read

Code:

---

define('DB_CHARSET', 'utf8mb4');

---

Thank you Cindy. I swear FileZilla makes me nuts. I knew I did that so I had to go back and look at the logs. One of these days I'm going to start paying closer attention to the output window of FZ. I have no idea why FZ sometimes cannot overwrite files. I'm all good now... apologies for the noob mistake.

Code:

---

Status:    Starting upload of C:\Users\Bubba\Downloads\database-converter-2.0.1\configure.phpCommand:    cd "/collector/includes"
Response:    New directory is: "/collector/includes"
Command:    put "C:\Users\Bubba\Downloads\database-converter-2.0.1\configure.php" "configure.php"
Error:    /collector/includes/configure.php: open for write: permission denied
Error:    File transfer failed
Status:    Starting upload of C:\Users\Bubba\Downloads\database-converter-2.0.1\configure.php
Status:    Retrieving directory listing of "/collector/includes"...
Status:    Listing directory /collector/includes
Command:    put "C:\Users\Bubba\Downloads\database-converter-2.0.1\configure.php" "configure.php"
Error:    /collector/includes/configure.php: open for write: permission denied
Error:    File transfer failed

---

>I swear FileZilla makes me nuts

Try Beyond Compare, you'll never look back.

I am in the process of upgrading my current site from v.1.5.8 to v.2.10

I have done all of the merging of files and installing of plugins and have tested and work well.

I then imported my live site (Zen Cart v.1.5.8) in and test, and everything works well.

I then ran v.2.0.1 of this plugin successfully (choosing utf8mb4_general_ci) and it breaks the Instant Search plug in that previously worked. My includes/config and Admin/includes/config files are already set to utf8mb4 in my live 1.5.8 site and database and current upgraded site....just wanted to run this to emoji errors, etc.

I received this error in the Debug Logs:

PHP Fatal error: MySQL error 1191: Can't find FULLTEXT index matching the column list ::
SELECT
p.*,
pd.products_name,
m.manufacturers_name,
MATCH(pd.products_name) AGAINST('love*' IN BOOLEAN MODE) AS name_relevance_boolean,
MATCH(pd.products_name) AGAINST('love' WITH QUERY EXPANSION) AS name_relevance_natural , MATCH(pd.products_description) AGAINST('love' WITH QUERY EXPANSION) AS description_relevance
FROM
zen_products_description pd
JOIN zen_products p ON (p.products_id = pd.products_id)
LEFT JOIN zen_manufacturers m ON (m.manufacturers_id = p.manufacturers_id)
WHERE
p.products_status <> 0
AND pd.language_id = 1
AND p.products_id NOT IN (1459,1458)
AND (
( MATCH(pd.products_name) AGAINST('love*' IN BOOLEAN MODE) + MATCH(pd.products_name) AGAINST('love' WITH QUERY EXPANSION) ) > 0 OR MATCH(pd.products_description) AGAINST('love' WITH QUERY EXPANSION) > 0
)
ORDER BY
name_relevance_boolean DESC,
name_relevance_natural DESC,
description_relevance DESC,
p.products_sort_order,
pd.products_name
LIMIT
38
==> (as called by) /home/mysite/public_html/new210/zc_plugins/InstantSearch/v4.0.3/classes/SearchEngineProviders/MysqlSearchEngineProvider.php on line 174 <== in /home/mysite/public_html/new210/includes/classes/db/mysql/query_factory.php on line 733.

In this error where is references the word "love"....that was the word I inputted into my search when testing...
Not only do I get this Debug Log error, but the Instant Search no longer works....in fact none of the Search works.

Any ideas why this happened and how to fix it???

I have tried uninstalling the Instant Search Plugin completely by removing all of the files and then checking the Admin->Modules->Plugin Mgr to verify that it has been uninstalled...and it has.

I then reinstalled the plugin by uploading all of the files....I now see the plugin in Admin->Modules->PluginMgr and when I press the Install button, I receive the error at the top of my Admin page of:

Duplicate key name 'idx_products_name'
Duplicate key name 'idx_products_description'

The plugin will not install.

Hoping this helps to decipher what took place when using the database converter tool...and how to fix it?

Thanks for your help!

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