SMN_Admin Posted April 3 Share Posted April 3 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? Link to comment Share on other sites More sharing options...
Miss_B Posted April 3 Share Posted April 3 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? Link to comment Share on other sites More sharing options...
SMN_Admin Posted April 3 Author Share Posted April 3 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. Link to comment Share on other sites More sharing options...
Solution Stuart Silvester Posted April 3 Solution Share Posted April 3 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 and Marc 2 Link to comment Share on other sites More sharing options...
Miss_B Posted April 3 Share Posted April 3 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. Link to comment Share on other sites More sharing options...
Marc Posted April 3 Share Posted April 3 Note before doing the mb4 conversion, you need to ensure you repair that table or restore from backup if needed Link to comment Share on other sites More sharing options...
SMN_Admin Posted April 4 Author Share Posted April 4 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. Link to comment Share on other sites More sharing options...
Marc Posted April 4 Share Posted April 4 No problem. Please let us how how you go Link to comment Share on other sites More sharing options...
SMN_Admin Posted April 24 Author Share Posted April 24 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! Link to comment Share on other sites More sharing options...
Marc Posted April 24 Share Posted April 24 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 Link to comment Share on other sites More sharing options...
Recommended Posts