Printable View
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.Quote:
Originally Posted by 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.
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?
Change table from ISAM to InnoDB?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
Since I've never experienced this error myself, I'm not clear on what correction is needed.Quote:
Originally Posted by gernot
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:
... and add the highlighted section: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));
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
on 3 different tables, and ran the program again and again until the results showed no more issues.HTML Code:1071: Specified key was too long; max key length is 1000 bytes
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.Quote:
Originally Posted by gernot
> 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:
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.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;
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)