Differences

This shows you the differences between two versions of the page.

--- howto:fix_self_corrupt_mysql [2016/07/28 15:51]
bram with have switched to mariadb which is still totally crapy
+++ howto:fix_self_corrupt_mysql [2018/12/08 22:28]
127.0.0.1 external edit
@@ Line 2: / Line 2: @@
 In some situations (typically power failure), MySQL corrupts its own database (called "mysql") and won't start again. This breaks any application using MySQL.
+**You can try this script by Bram that should normally auto-fix this issue but need more testing (and we are looking for testing) https://github.com/labriqueinternet/auto-fix-mysql Plz contact us if you intent to do so**
 Here is a way to restore MySQL in this situation. I've never managed to prevent that, but [[howto:daily_backup|backing up this DB]] would likely allow simpler restorations.
@@ Line 19: / Line 21: @@
     # let's backup things
     cd ..
-    cp -rv mysql mysql.backup
+    cp -rv mysql ../mysql.backup
     cd mysql
@@ Line 95: / Line 97: @@
     # and to recreate agendav user
-    insert into user values('localhost','agendav', PASSWORD('put the password here'),'N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0,NULL,'');
+    insert into user values('localhost','agendav', PASSWORD('put the password here'),'N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0,'','','N','N');
     GRANT ALL PRIVILEGES ON agendav.* TO 'agendav'@localhost;
@@ Line 103: / Line 105: @@
     # you can try a bash script like that:
     # BUT I HAVEN'T TESTED IT
-    for i in app1 app2 app3; do mysql -p$(cat /etc/yunohost/mysql) mysql -e "insert into user values('localhost','$i', PASSWORD('$(yunohost app setting $i mysqlpwd)'),'N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0,NULL,''); GRANT ALL PRIVILEGES ON $i.* TO '$i'@localhost;"; done; mysql -p$(cat /etc/yunohost/mysql) mysql -e "FLUSH PRIVILEGES;"
+    for i in app1 app2 app3; do mysql -p$(cat /etc/yunohost/mysql) mysql -e "insert into user values('localhost','$i', PASSWORD('$(yunohost app setting $i mysqlpwd)'),'N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0,'','','N','N'); GRANT ALL PRIVILEGES ON $i.* TO '$i'@localhost;"; done; mysql -p$(cat /etc/yunohost/mysql) mysql -e "FLUSH PRIVILEGES;"
+---
+On another weird situation, the DB permissions disappeared and we were unable to re-grant permissions to the app because root was not allowed to give permissions... And it seemed not possible to grant it permissions for *.* for some really weird reasons. ljf was able to hand-craft a request to manually give the permissions to root on all tables, allowing to grant permissions to specific users.
+I think we started with a mysqld_safe --skip-grant-tables in one terminal, and then in another :
+    for i in app1 app2 app3; do mysql -p$(cat /etc/yunohost/mysql) mysql -e "delete from user WHERE User='$i'; insert into user values('localhost','$i', PASSWORD('$(yunohost app setting $i mysqlpwd)'),'N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0,'','','N','N'); INSERT INTO db VALUES('localhost','$i','root','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y'); FLUSH PRIVILEGES; GRANT ALL PRIVILEGES ON $i.* TO '$i'@localhost;"; done; mysql -p$(cat /etc/yunohost/mysql) mysql -e "FLUSH PRIVILEGES;"