Database Conversion Tools [Support Thread]

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?

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?

gernot:

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

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:

        $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:

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

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

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

Thank you,
I got the same error

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?

gernot:

Thank you,
I got the same error

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:

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:

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.

[TABLE]
[TR]
[TH]Next Action[/TH]
[/TR]
[TR]
[TD]
[LIST]
[*]The database's character-set doesn't match DB_CHARSET.
[*]Congratulations, no database issues were found.
[/LIST]
[/TD]
[/TR]
[/TABLE]

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

[TABLE]
[TR]
[TD="align: right"]MySQL Version:[/TD]
[TD]8.0.36[/TD]
[/TR]
[TR]
[TD="align: right"]Zen-Cart Database Version:[/TD]
[TD]2.1.0[/TD]
[/TR]
[TR]
[TD="align: right"]Database Name:[/TD]
[TD]dbs12804405[/TD]
[/TR]
[TR]
[TD="align: right"]Database Prefix:[/TD]
[TD]'zencrt_'[/TD]
[/TR]
[TR]
[TD="align: right"]Database Character Set:[/TD]
[TD="class: text-danger"]utf8mb4[/TD]
[/TR]
[TR]
[TD="align: right"]DB_CHARSET:[/TD]
[TD="class: text-danger"]utf8[/TD]
[/TR]
[TR]
[TD="align: right"]Database Collation:[/TD]
[TD]utf8mb4_general_ci[/TD]
[/TR]
[TR]
[TD="align: right"]Number of Tables:[/TD]
[TD]115[/TD]
[/TR]
[/TABLE]

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

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.

[TABLE]
[TR]
[TH]Next Action[/TH]
[/TR]
[TR]
[TD]
[LIST]
[]The database's character-set doesn't match DB_CHARSET.
[]Congratulations, no database issues were found.
[/LIST]
[/TD]
[/TR]
[/TABLE]

> 
> Here is what my current output states after using the tool:
> 
> ```
[TABLE]
[TR]
[TD="align: right"]MySQL Version:[/TD]
[TD]8.0.36[/TD]
[/TR]
[TR]
[TD="align: right"]Zen-Cart Database Version:[/TD]
[TD]2.1.0[/TD]
[/TR]
[TR]
[TD="align: right"]Database Name:[/TD]
[TD]dbs12804405[/TD]
[/TR]
[TR]
[TD="align: right"]Database Prefix:[/TD]
[TD]'zencrt_'[/TD]
[/TR]
[TR]
[TD="align: right"]Database Character Set:[/TD]
[TD="class: text-danger"]utf8mb4[/TD]
[/TR]
[TR]
[TD="align: right"]DB_CHARSET:[/TD]
[TD="class: text-danger"]utf8[/TD]
[/TR]
[TR]
[TD="align: right"]Database Collation:[/TD]
[TD]utf8mb4_general_ci[/TD]
[/TR]
[TR]
[TD="align: right"]Number of Tables:[/TD]
[TD]115[/TD]
[/TR]
[/TABLE]

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

define('DB_CHARSET', 'utf8[B]mb4[/B]');

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.

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!