Database problems with v4.7.14

[SMN_Admin]

As we tried our updates to v4.7.15 we got some errors with the database and that there are 'some' fixes required. And while trying to do the fixes with the web gui it broke our complete forum.

Lesson learned, I will take my time for the updates, but now with the update v4.7.16 it seems like urgent to do the update and we still have the issue with the database and even with manually adding the mysql statements it seems not to fix the problem, as the messages are the same even if I do some of the queries.

We are on a self hosted version at a webspace hoster.
Database Version 8.0.36-28

UPDATE `smn_core_modules` SET `sys_module_title`='' WHERE `sys_module_title` IS NULL;

UPDATE `smn_core_modules` SET `sys_module_application`='' WHERE `sys_module_application` IS NULL;

UPDATE `smn_core_modules` SET `sys_module_key`='' WHERE `sys_module_key` IS NULL;

CREATE TABLE `smn_core_modules_new` LIKE `smn_core_modules`;

ALTER TABLE `smn_core_modules_new` CHANGE COLUMN `sys_module_title` `sys_module_title` VARCHAR (32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' , DROP INDEX `identifier`, DROP INDEX `sys_module_application`, CHANGE COLUMN `sys_module_application` `sys_module_application` VARCHAR (32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' , ADD UNIQUE KEY `identifier` (`sys_module_application`,`sys_module_key`,`sys_module_area`), ADD KEY `sys_module_application` (`sys_module_application`), DROP INDEX `sys_module_key`, CHANGE COLUMN `sys_module_key` `sys_module_key` VARCHAR (32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' , ADD KEY `sys_module_key` (`sys_module_key`), CHANGE COLUMN `sys_module_area` `sys_module_area` VARCHAR (32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT 'front' , CHANGE COLUMN `sys_module_default_controller` `sys_module_default_controller` VARCHAR (32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL ;

INSERT IGNORE INTO `smn_core_modules_new` SELECT * FROM `smn_core_modules`;

DROP TABLE `smn_core_modules`;

RENAME TABLE `smn_core_modules_new` TO `smn_core_modules`;

This is only one of the blocks, but it seems like it wants to do it for all of the tables.

So my questions are:

Will these only be updated, after all queries are done?

And can this be an issue with our hosters db and how to fix this, that we can update our forum?

[Miss_B]

1 minute ago, SMN_Admin said:

And while trying to do the fixes with the web gui it broke our complete forum.

It broked it how? What error messages did you get? Can you remember them?

[SMN_Admin]

The forum itself was not working anymore due to a corrupt database. The error in the database was

xxx.smn_core_modules_new
Error    : Incorrect key file for table 'smn_core_modules_new'; try to repair it
error    : Corrupt

That still happens if I try to execute the query in one go. I have to break it down in smaller queries. And because the the web gui dosn't check if the new tables is working before it drops the old one we had a corrupt database.
 

[Stuart Silvester]

On the support page in your AdminCP, please follow the instructions to convert your database to utf8mb4. MySQL removed utf8_ and aliased it to utf8mb3_ which can cause issues with checking the database structure since the collation is not what is expected.

[Miss_B]

Additionally, I have seen this type of error before when I upgraded a forum for someone here and in that case it was cause by insufficient disk space in the Temp folder.

The sql query(ies) fetch data that is greater than the size of the tmp folder.

If the utf8mb4conversion won't solve the issue, you should check the location of the /tmp folder to ensure that it has enough disk space to store temporary tables. 

[Marc Stridgen]

Note before doing the mb4 conversion, you need to ensure you repair that table or restore from backup if needed

[SMN_Admin]

Hey Marc,

we did a health check before that, but thanks for reminding us!

As Miss_B already mentioned is the database itself a problem, but as we only have a chroot webspace the database is out of our scope and we have to get in touch with our hosting provider.

We get the error:

Sorry, you do not have permission for that!
4C171/4 The database could not be converted. Contact your hosting provider or system administrator for assistance.

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

But again, I think our hosting provider has to do here his thing.

[Marc Stridgen]

No problem. Please let us how how you go

[SMN_Admin]

I had to change a lot of data types to convert everything to utf8mb4 the script in the AdminCP did a great work. After that the Update Script gave us the right queries again to repair the things I changed. 

Long story short, the update worked and the DB is now utf8mb4.

Thanks!

[Marc Stridgen]

If you had to do that, it seems that you may have had it set in your conf_global.php file to true, when in fact it hadnt been converted. If you have any further issues on this, set it to false and you will see the converter show up in the support area of your admin CP, and it will allow you to do the above automatically