Database Conversion Tools [Support Thread]

[lat9]

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.

[gothstone]

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.

[lat9]

Thanks for the follow-up!

[swguy]

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

[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?

[lat9]

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' => [],
            ];

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

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

[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?

[lat9]

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.

[swguy]

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

[gernot]

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)