Can't upgrade 3.4.6 to 4.4.6. UTF8 tool error - Key too long

[Maxxius]

Hi, I have a problem right off the bat with my upgrade process.

I have launched the upgrade process and first off the upgrader switched off to the UTF-8 converter tool which gives me this error:

"Error: Specified key was too long; max key length is 1000 bytes File: /system/Db/Db.php Line: 198"

My forum has been converted to UTF8 when it was being upgraded to 3.1.4 back in 2011. The cmd-line tool released by IPS in 2011 was used to convert the whole database to UTF8 from something swedish was successfully used. Took a long time and a lot of effort but everything succeeded. Then later in 2014 forum was upgraded to 3.4.x and I lived happily ever after. No character or any database-related issues whatsoever occurred until now. Now in phpMyAdmin collation column says "utf8_general_ci" I have no idea why this is happening.

Any idea what could be wrong? @Pete T @DawPi @Woodsman @ASTRAPI @Mike John @Makoto @A Zayed @Adriano Faria

[DawPi]

Change tables from MyISAM to Innodb. 🙂

[Maxxius]

@DawPi you sure I go thru every table, click browse on it. then go to operations and change it like that?

[DawPi]

https://stackoverflow.com/questions/3856435/how-to-convert-all-tables-from-myisam-into-innodb

SET @DATABASE_NAME = 'name_of_your_db';

SELECT  CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements
FROM    information_schema.tables AS tb
WHERE   table_schema = @DATABASE_NAME
AND     `ENGINE` = 'MyISAM'
AND     `TABLE_TYPE` = 'BASE TABLE'
ORDER BY table_name DESC;

 

Of course make database backup first!

[Maxxius]

I made a lot of progress with this issue but now I'm completely stuck and need your assistance. I have found out that the error "Error: Specified key was too long; max key length is 1000 bytes File: /system/Db/Db.php Line: 198 " given by UTF8 converter tool is because of these following tables:

blog_blogs
blog this
bpi_ccs_menus
bpi_core_sys_conf_settings
bpi_gallery_albums
bpi_gallery_images
bpi_members
bpi_nexus_subscriptions
bpi_rc_classes
bpi_skin_merge_changes
bpi_tags_index

If I made those tables manually use InnoDB instead of mysiam the converter tool completed without an issue.

Then came the upgrade process which went for a long time and got stuck in this step:
 

Step 7: Upgrade 1071
Specified key was too long; max key length is 1000 bytes
/home/test/domains/testsite.com/public_html/applications/blog/setup/upg_40000/queries.json - query #5

ALTER TABLE `bpi_blog_blogs` ENGINE=MyISAM, ADD KEY `blog_grabber` (`blog_disabled`,`blog_view_level`)

I assume this is because I changed the table to InnoDB and it wants it to be MyISAM but I cant go back to phpMyadmin and change it because of this error

And what's more I think I'd be getting the same error when I arrived at other tables mentioned in the list its just that the blog_blogs alphabetically is the first on the list.

[Maxxius]

Tried putting

$INFO['mysql_tbl_type']      =  'InnoDB'; 

in my config file but result was the same.

[bfarber]

If you submit a ticket we'll be happy to take a look.

[Maxxius]

The ticket was put in and it has been in escalated status for over a day now. Hope this will be sorted out.

[Lucas James]

16 hours ago, Maxxius said:

it has been in escalated status

That's where @Stuart Silvester usually comes in! 

[Maxxius]

He just let me know the issue was send to Tier III support now 😄