Corrupt mysql base (probably) repared, but unable to connect afterwords

Support
,
1

My YunoHost configuration

Hardware: lime 2
Internet access: ethernet at home
YunoHost version:
yunohost:
repo: stable
version: 2.7.9

yunohost-admin:
repo: stable
version: 2.7.7

moulinette:
repo: stable
version: 2.7.7

ssowat:
repo: stable
version: 2.7.7

Description of my problem

I had some power cuts and my DB has end up being corrupted. On the recommandations of xmpp support, i followed these recommandations : MYSQL root user is being denied permission while installing an app

It all went well, and it seems the bases have been repared… The issue is now that i can’t use mysql : whatever the application, i get an error :

Terminal :
# mysql -u root -p$(cat /etc/yunohost/mysql) mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

PHPMyAdmin :
#1045 - Access denied for user 'root'@'localhost' (using password: YES)
mysqli_real_connect(): (28000/1045): Access denied for user 'phpmyadmin'@'localhost' (using password: YES)

i tried what was suggested on this topic but it also failed

I also had a look at this page and tried the hard way :

sudo dpkg-reconfigure mysql-server-5.5

which failed too (impossible to change root password, which was already set)

as no user can connect, all methods using :

sudo mysql --defaults-file=/etc/mysql/debian.cnf

also failed as

ERROR 1045 (28000): Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)

i don’t quite know what to do and i would appeciate help as it will quite quickly be a real issue (for now I don’t have nextcloud and git running) as it appears it blocks LE certificate renewal…

Logs

webui diagnosis : https://paste.yunohost.org/becukamoki.json

last 1000 lines of mysql log (activated when it started to fail) : https://paste.yunohost.org/bebegamadi

2

I’ve had the same issue with mysql a couple of weeks ago and I solved it using this how to
Especially from these lines:

# in one shell
  mysqld_safe --skip-grant-tables
  
  # in another one, get the list of tables to fix
  myisamchk -a *.MYI  | grep error 
  
  # this open a mysql shell
  mysql mysql  # yes twice
  
  # for every corrupt table try this: (although they will likely fail)
  mysql> repair table $TABLE_NAME;
  
  # if this doesn't work, do
  # WARNING: THIS WILL CRUNCH THE TABLE AND DESTROY THE DATA
  mysql> repair table $TABLE_NAME use_frm;
  
  # now you probably don't have any users in the table, so let's recreate them if we can ...
  # start by getting the password of mysql like that
  cat /etc/yunohost/mysql
  
  # let's go back to MySQL
  mysql mysql
  mysql> insert into user values('localhost','root', PASSWORD('put the mysql password here'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'','','N','N');
  mysql> insert into user values('::1','root', PASSWORD('put the mysql password here'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'','','N','N');
  mysql> insert into user values('127.0.0.1','root', PASSWORD('put the mysql password here'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'','','N','N');
  
  # resurrect debian-sys-maint as well, by running the following command in a shell
  # (press enter when prompted for a new password)
  dpkg-reconfigure mariadb-server-10.0
  # or this one if you are still using mysql (old yunohost stable)
  dpkg-reconfigure mysql-server-5.5
  
  # from now on you should be able to rerun mysql normally and log as user
  # stop mysqld_safe (you need to kill it)
  ps ax | grep mysql
  # kill THE_TWO_PIDS_FOR_MYSQLD_SAFE
  
  /etc/init.d/mysql start
  
  # and you can log using this
  mysql -p$(cat /etc/yunohost/mysql) mysql
  
  # now, for every app you'll need to recreate the user in the user table
  # you can find how the password is stored in the installation script of the app
  # let's just hope that the tables of the app aren't corrupted...
  
  # for example for agendav you need to use this shell command to get the password
  yunohost app setting agendav mysqlpwd
  
  # 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,'','','N','N');
  GRANT ALL PRIVILEGES ON agendav.* TO 'agendav'@localhost;
  
  # and don't forget this otherwise nothing will work
  FLUSH PRIVILEGES;
  
  # 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,'','','N','N'); GRANT ALL PRIVILEGES ON $i.* TO '$i'@localhost;"; done; mysql -p$(cat /etc/yunohost/mysql) mysql -e "FLUSH PRIVILEGES;"

Hope it could help you…

1 Like
3

i think It s not this entry

Don t you use the last release of Yunohost?

Your tuto is right
try for mariaDB

dpkg-reconfigure mariadb-server-10.0

4

@Benance merci oui ca m’a aidé ! Pour une raison que je ne m’explique pas, en revanche, les entrées mysql ne correspondaient pas (pas le bon nombre de colonnes). Pour qui tomberait sur ce post, le lien donné par Benance est plus complet et top, et en ce qui me concerne, les commandes pour recréer les utilisateurs des apps devaient avoir la forme suivante (et root identique en remplacant les N par des Y et agendav par root) :

insert into user values(‘localhost’, ‘agendav’, PASSWORD(‘mon_mot_de_passe’), ‘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’, ‘’, ‘’);

@yolateng0
As far as I know, on stable (jessie), ynh currently uses mysql-server-5.5 and not mariadb-server-10.0 (which isn’t installed on my server)