Access denied for user 'root'@'localhost' to database 'nextcloud'

Hello,

I had a corrupted database: error reading 'mysql/ibdata1': Input/output error. I have tried different solutions but nothing works since this file is unreadable.

What I have done so far is

  • mv /var/lib/mysql

  • apt-get install --reinstall mariadb-server

  • yunohost tools regen-conf -f mysql

  • yunohost service restart mysql

  • I have also restored the permissions as mentioned here with GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY '<password>';

… but when I try to restore any app, I get this error Access denied for user 'root'@'localhost' to database 'wallabag2 'nextcloud'

This commands runs successfully mysql -u root -p$(cat /etc/yunohost/mysql) mysql so the password is correct.

Anyway but permission is still missing. Maybe @kanhu you have a trick for that ? I have seen posts from you.

Any help welcome (French or English).

This error message is not a full sentence … what does it says exactly ?

@Aleks Sorry, bad copy paste

Access denied for user ‘root’@‘localhost’ to database ‘wallabag2’

Sooo I’m not super expert with mysql/mariadb but let’s try this :

  • Being logged as root, are you able to run mysql (N.B. : without -u or -p)
  • Open a mysql prompt (for example using the command you mentioned with -u and -p if just mysql did not work) and try to run in the mysql prompt:

SHOW DATABASES;

(the point being to list all existing databases to make sure they still exist)

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
+--------------------+
2 rows in set (0.002 sec)

I have no other databases. Note that MySQL is restarting from scratch and that I have not yet restored any app.

Oh okay I see … but then can you share the full log of the attempted restauration

I may have one idea to try but first would be sure to understand what’s going on

2021-02-25 12:44:02,045: DEBUG - + local db=wallabag2
2021-02-25 12:44:02,046: DEBUG - + local 'sql=CREATE DATABASE wallabag2;'
2021-02-25 12:44:02,047: DEBUG - + [[ 3 -gt 1 ]]
2021-02-25 12:44:02,048: DEBUG - + sql+=' GRANT ALL PRIVILEGES ON wallabag2.* TO '\''wallabag2'\''@'\''localhost'\'''
2021-02-25 12:44:02,049: DEBUG - + [[ -n ********** ]]
2021-02-25 12:44:02,050: DEBUG - + sql+=' IDENTIFIED BY '\''**********'\'''
2021-02-25 12:44:02,051: DEBUG - + sql+=' WITH GRANT OPTION;'
2021-02-25 12:44:02,052: DEBUG - + ynh_mysql_execute_as_root '--sql=CREATE DATABASE wallabag2; GRANT ALL PRIVILEGES ON wallabag2.* TO '\''wallabag2'\''@'\''localhost'\'' IDENTIFIED BY '\''**********'\'' WITH GRANT OPTION;'
2021-02-25 12:44:02,162: DEBUG - + database=
2021-02-25 12:44:02,163: DEBUG - + '[' -n '' ']'
2021-02-25 12:44:02,164: DEBUG - ++ cat /etc/yunohost/mysql
2021-02-25 12:44:02,164: DEBUG - + ynh_mysql_connect_as --user=root --password=**********
2021-02-25 12:44:02,273: DEBUG - + database=
2021-02-25 12:44:02,274: DEBUG - + mysql --user=root --password=********** --batch ''
2021-02-25 12:44:02,375: DEBUG - + ynh_exit_properly
2021-02-25 12:44:02,376: WARNING - ERROR 1044 (42000) at line 1: Access denied for user 'root'@'localhost' to database 'wallabag2'
2021-02-25 12:44:02,377: DEBUG - + local exit_code=1
2021-02-25 12:44:02,378: DEBUG - + '[' 1 -eq 0 ']'
2021-02-25 12:44:02,379: DEBUG - + trap '' EXIT
2021-02-25 12:44:02,379: DEBUG - + set +o errexit
2021-02-25 12:44:02,380: DEBUG - + set +o nounset
2021-02-25 12:44:02,380: DEBUG - + sleep 0.5
2021-02-25 12:44:02,883: DEBUG - + type -t ynh_clean_setup
2021-02-25 12:44:02,885: DEBUG - + exit 1
2021-02-25 12:44:02,918: ERROR - Impossible de restaurer 'wallabag2'

Full log at https://paste.yunohost.org/raw/supebacuxa

Go to mysql.

USE mysql;

Show the output.

SELECT User, Host, plugin FROM mysql.user;

If the root user has auth_socket you need to change it to mysql_native_password.

Do this to change to mysql_native_password.

UPDATE user SET plugin='mysql_native_password' WHERE User='root';
FLUSH PRIVILEGES;

The plugin column was empty. After applying the change it looks like this

+------+-----------+-----------------------+
| User | Host      | plugin                |
+------+-----------+-----------------------+
| root | localhost | mysql_native_password |
+------+-----------+-----------------------+

By the way 0 row affected: MariaDB [mysql]> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.002 sec)

… but I still have ERROR 1044 (42000) at line 1: Access denied for user 'root'@'localhost' to database 'wallabag2'

I would argue the opposite :confused:

I would run GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED WITH unix_socket WITH GRANT OPTION;

MariaDB [(mysql)]> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED WITH unix_socket WITH GRANT OPTION;
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)```

In my Yunohost it was mysql_native_password. I have not tested if socket is working for me.

@Dams
What is output of:

 SELECT host,user,password,Grant_priv,Super_priv FROM mysql.user;

I think this would be privilege issue.

+-----------+------+-------------------------------------------+------------+------------+
| host      | user | password                                  | Grant_priv | Super_priv |
+-----------+------+-------------------------------------------+------------+------------+
| localhost | root | *4E617FEEEEFCFAD5E2734439948B587CBB4F3D4E | N          | Y          |
+-----------+------+-------------------------------------------+------------+------------+

Grant privileges then.

UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root';
FLUSH PRIVILEGES;
GRANT ALL ON *.* TO 'root'@'localhost';

If this works, you may change to unix_socket and see if that works.

MariaDB [mysql]> UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root';
Query OK, 1 row affected (0.447 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [mysql]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.002 sec)

MariaDB [mysql]> GRANT ALL ON *.* TO 'root'@'localhost';
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Does the FLUSH works ?

Maybe mysql restart?
I think you are not root???

Okay thanks @kanhu and @Aleks it works :grinning:

Resume

Clear MySQL with

  • mv /var/lib/mysql
  • apt-get install --reinstall mariadb-server
  • yunohost tools regen-conf -f mysql
  • yunohost service restart mysql
  • get password from `cat /etc/yunohost/mysql``
# reset password
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY '<password>';

# first (not sure it was useful)
UPDATE user SET plugin='mysql_native_password' WHERE User='root';

# then 
UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root';
FLUSH PRIVILEGES;
GRANT ALL ON *.* TO 'root'@'localhost';

# then
mysql restart
1 Like

The response is spread among the discussion. Therefor I have created a summary, and tagged it as “solution”.

This topic was automatically closed 15 days after the last reply. New replies are no longer allowed.