Dams
February 24, 2021, 11:49am
1
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).
Aleks
February 25, 2021, 12:57pm
3
This error message is not a full sentence … what does it says exactly ?
Dams
February 25, 2021, 12:59pm
4
@Aleks Sorry, bad copy paste
Access denied for user ‘root’@‘localhost’ to database ‘wallabag2’
Aleks
February 25, 2021, 1:04pm
5
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)
Dams
February 25, 2021, 1:11pm
6
Aleks:
SHOW DATABASES;
+--------------------+
| 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.
Aleks
February 25, 2021, 1:13pm
7
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
Dams
February 25, 2021, 1:16pm
8
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
kanhu
February 25, 2021, 1:22pm
9
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 .
kanhu
February 25, 2021, 1:24pm
10
Do this to change to mysql_native_password .
UPDATE user SET plugin='mysql_native_password' WHERE User='root';
FLUSH PRIVILEGES;
Dams
February 25, 2021, 1:30pm
11
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'
Aleks
February 25, 2021, 1:33pm
12
I would argue the opposite
I would run GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED WITH unix_socket WITH GRANT OPTION;
Dams
February 25, 2021, 1:36pm
13
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)```
kanhu
February 25, 2021, 1:44pm
14
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.
Dams
February 25, 2021, 1:53pm
15
+-----------+------+-------------------------------------------+------------+------------+
| host | user | password | Grant_priv | Super_priv |
+-----------+------+-------------------------------------------+------------+------------+
| localhost | root | *4E617FEEEEFCFAD5E2734439948B587CBB4F3D4E | N | Y |
+-----------+------+-------------------------------------------+------------+------------+
kanhu
February 25, 2021, 1:56pm
16
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.
Dams
February 25, 2021, 1:59pm
17
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 ?
kanhu
February 25, 2021, 2:00pm
18
Maybe mysql restart?
I think you are not root???
Dams
February 25, 2021, 2:14pm
19
Okay thanks @kanhu and @Aleks it works
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
Dams
February 25, 2021, 4:58pm
20
The response is spread among the discussion. Therefor I have created a summary, and tagged it as “solution”.
system
Closed
March 12, 2021, 4:58pm
21
This topic was automatically closed 15 days after the last reply. New replies are no longer allowed.