6

In my ubuntu 16.04 server I am trying to install mysql by the following command

sudo apt-get install mysql-server

Each time it got stuck with the message :

update-alternatives: using /etc/mysql/mysql.cnf to provide /etc/mysql/my.cnf (my.cnf) in auto mode 
Renaming removed key_buffer and myisam-recover options (if present) 
Checking if update is needed.
This installation of MySQL is already upgraded to 5.7.16, use --force if you still need to run mysql_upgrade

I have to press Ctrl+z to stop it. I have checked the log file in /var/log/mysql/error.log and last 10 line of it shows this result:

2017-01-18T09:07:30.329930Z 0 [Note] InnoDB: Buffer pool(s) load completed at 170118  9:07:30
2017-01-18T09:07:30.335086Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2017-01-18T09:07:30.335137Z 0 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
2017-01-18T09:07:30.335157Z 0 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
2017-01-18T09:07:30.335216Z 0 [Note] Server socket created on IP: '127.0.0.1'.
2017-01-18T09:07:30.349491Z 0 [Note] Event Scheduler: Loaded 1 event
2017-01-18T09:07:30.349749Z 0 [Note] Execution of init_file '/var/lib/mysql-files/tmp.sJ5rMac6mK' started.
2017-01-18T09:07:30.350543Z 1 [ERROR] 1396  Operation ALTER USER failed for 'root'@'localhost'
2017-01-18T09:07:30.350737Z 0 [Note] Execution of init_file '/var/lib/mysql-files/tmp.sJ5rMac6mK' ended.
2017-01-18T09:07:30.350864Z 0 [Note] mysqld: ready for connections.

After a reboot I logged in to mysql by mysql -u root. within mysql I cannot do any operation, creating new user or modifying privileges nothing. I am just able to browse through default databases

  • information_schema
  • test

when I typed show grants it shows me the following result

+--------------------------------------+
| Grants for @localhost                |
+--------------------------------------+
| GRANT USAGE ON *.* TO ''@'localhost' |
+--------------------------------------+

Which is quite strange. To make sure of this I checked the user_privileges table in information_schema which gives me this:

+----------------+---------------+----------------+--------------+
| GRANTEE        | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE |
+----------------+---------------+----------------+--------------+
| ''@'localhost' | def           | USAGE          | NO           |
+----------------+---------------+----------------+--------------+

How can I enable root access to create, update?

  • Have you tried sudo apt-get install --reinstall mysql-server? – Melebius Jan 18 '17 at 9:55
  • @Melebius I have reinstalled several times. still got the error. reinstall also got stuck in the middle with the same error in log file. – user3177667 Jan 18 '17 at 10:50
15

An easy solution is to sudo killall mysqld while the apt-get operation is running.

The apt operation just kept on running without any errors after that(!)

Source: https://bugs.launchpad.net/ubuntu/+source/mysql-5.7/+bug/1596815

  • this is the best solution! I recommend to do this instead of uninstalling mysql, cause it'll delete all your db! – RollsAppleTree Feb 4 '18 at 17:39
  • 1
    Note that you may have to do this more than once during the upgrade process. – devius Aug 4 '18 at 19:10
7

At last I got the workaround. Here is what I did

  • Uninstall mysql using sudo apt purge mysql*
  • Remove /etc/mysql folder
  • Remove /var/lib/mysql, /var/lib/mysql-files, /var/lib/mysql-kering folders.
  • Reboot
  • Install mysql using sudo apt-get install mysql-server

This time mysql installed successfully without getting stuck. While installation it prompted me for root password and I provided one. After the installation I logged in with root with my provided password. This time creating new user, updating all works fine. Now, here is the result of show grants:

+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+

Although I still don't know why at first time it didn't work. I tried by removing only the cnf file but that didn't work. When I removed all mysql folders inside /var/lib then it worked. So I guess there was some file inside /var/lib/msyql directory which prevented to create any new user (even root user).

1

I've been looking for months for a solution to the problems with mysql_server configuration, which froze when installing anything else, thus having to kill the processes and manually start mysql. This also made me have to start MySQL manually each time I restarted the computer.

I edited the /var/lib/dpkg/info/mysql-server-5.7.postinst and I noticed the configure) part doesn't return anything so it just freezes.

You just need to edit that file and add at the end of the configure) section (before the section on abort-upgrade|abort-remove|abort-configure) a line that says "exit 0", around line 290. Mine looks now like this:

    # To avoid downgrades. This has to happen after the database is created, or --initialize will fail
touch $mysql_statedir/debian-5.7.flag

    exit 0   # Corregimos el bug del postinst de mysql

;;
abort-upgrade|abort-remove|abort-configure)
-1

It is because of no root user is created in MySQL. But I don't know why. Try to insert 'root' user to mysql.user table.

Firstly, stop the mysql server.

sudo /etc/init.d/mysql stop 

To start MySQL with --skip-grant-tables option, let try to config my.cnf.

sudo vi /etc/mysql/my.cnf

Add following line under [mysqld].

 skip-grant-tables

Start MySQL server again.

sudo /etc/init.d/mysql start

Login to MySQL

mysql -u root

Insert 'root' user with your desire password.

mysql> INSERT INTO mysql.user VALUES ('localhost','root',password('newpassword'),'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); 
mysql> INSERT INTO mysql.user VALUES ('127.0.0.1','root',password('newpassword'),'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); 

newpassword should be change to your desired password. mysql> exit

Remove skip-grant-tables in my.cnf config to use authentication.

After that retart MySQL server in normal way.

sudo /etc/init.d/mysql restart

Try to login with root and your password.

  • when I try to login after --skip-grant-tables command it gives me this error ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) – user3177667 Jan 18 '17 at 10:58
  • How about with this command sudo mysqld --skip-grant-tables --bind-address=127.0.0.1. – San Lin Naing Jan 18 '17 at 11:20
  • I had edited the answer with the way of my.cnf configuration. – San Lin Naing Jan 18 '17 at 12:13
  • Thank you @San. I get it to work by just clean sweeping all mysql folders. see my answer. – user3177667 Jan 18 '17 at 12:29
  • Nice answer. Good. – San Lin Naing Jan 18 '17 at 17:36

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.