Sfrgttk

On a brand new Kubuntu 14.04 install, I ran sudo aptitude install mysql-server-core-5.6. The install could not complete due to dependencies of some KDE packages on mysql-server-core-5.5, which would have been replaced. Now when I run sudo aptitude install mysql-server-5.5 I get this error after specifying a password:

Configuring mysql-server-5.5

Unable to set password for the MySQL "root" user

An error occurred while setting the password for the MySQL administrative user. This may have happened

because the account already has a password, or because of a communication problem with the MySQL server.

You should check the account's password after the package installation.

Please read the /usr/share/doc/mysql-server-5.5/README.Debian file for more information.

I have in fact read /usr/share/doc/mysql-server-5.5/README.Debian but there was nothing relevant for my situation. The MySQL log helps:

$ tail /var/log/mysql/error.log 

140818 10:17:16 InnoDB: Completed initialization of buffer pool

140818 10:17:16 InnoDB: highest supported file format is Barracuda.

140818 10:17:16  InnoDB: Waiting for the background threads to start

140818 10:17:17 InnoDB: 5.5.38 started; log sequence number 1595675

140818 10:17:17 [ERROR] /usr/sbin/mysqld: unknown option '--explicit_defaults_for_timestamp'

140818 10:17:17 [ERROR] Aborting



140818 10:17:17  InnoDB: Starting shutdown...

140818 10:17:18  InnoDB: Shutdown completed; log sequence number 1595675

140818 10:17:18 [Note]

However I cannot find in which script the --explicit_defaults_for_timestamp option is set. I tried to start mysql without service to avoid the --explicit_defaults_for_timestamp option but it still won't start:

$ ps aux | grep mysql

dotanco+ 25458  0.0  0.0  11748   928 pts/4    S+   10:30   0:00 grep --color=auto mysql



$ sudo mysqld_safe --skip-grant-tables &

[1] 25470

140818 10:30:54 mysqld_safe Can't log to error log and syslog at the same time.  Remove all --log-error configuration options for --syslog to take effect.

140818 10:30:54 mysqld_safe Logging to '/var/log/mysql/error.log'.

140818 10:30:54 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

140818 10:30:57 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended



[1]+  Done                    sudo mysqld_safe --skip-grant-tables



$ ps aux | grep mysql

dotanco+ 25810  0.0  0.0  11748   932 pts/4    S+   10:31   0:00 grep --color=auto mysql

The error tells me to Remove all --log-error configuration options. I therefore edited /etc/mysql/my.cnf and commented out the following line:

log-error  = /var/log/mysql/error.log

Now I get no error but still cannot log in:

$ sudo mysqld_safe --skip-grant-tables &

[1] 26558

140818 10:34:37 mysqld_safe Logging to syslog.

140818 10:34:38 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

140818 10:34:40 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended



[1]+  Done                    sudo mysqld_safe --skip-grant-tables



$ mysql -uroot

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

What might be the issue? How should I proceed?

EDIT

I've now commented out the explicit-defaults-for-timestamp line in my.cnf. Here is the entire file, minus header comments:

$ cat /etc/mysql/my.cnf 



[client]

port            = 3306

socket          = /var/run/mysqld/mysqld.sock



[mysqld_safe]

pid-file        = /var/run/mysqld/mysqld.pid

socket          = /var/run/mysqld/mysqld.sock

nice            = 0



[mysqld]

user            = mysql

pid-file        = /var/run/mysqld/mysqld.pid

socket          = /var/run/mysqld/mysqld.sock

port            = 3306

basedir         = /usr

datadir         = /var/lib/mysql

tmpdir          = /tmp

lc-messages-dir = /usr/share/mysql

#explicit_defaults_for_timestamp



bind-address    = 127.0.0.1



#log-error      = /var/log/mysql/error.log



sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES



symbolic-links=0



!includedir /etc/mysql/conf.d/

This is what happens when I try to run MySQL:

$ sudo echo 1 >> /var/log/mysql/error.log



$ tail /var/log/mysql/error.log 

140818 10:31:49  InnoDB: Waiting for the background threads to start

140818 10:31:50 InnoDB: 5.5.38 started; log sequence number 1595675

140818 10:31:50 [ERROR] /usr/sbin/mysqld: unknown option '--explicit_defaults_for_timestamp'

140818 10:31:50 [ERROR] Aborting



140818 10:31:50  InnoDB: Starting shutdown...

140818 10:31:51  InnoDB: Shutdown completed; log sequence number 1595675

140818 10:31:51 [Note] 

140818 10:31:51 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

1



$ sudo service mysql start

start: Job failed to start



$ tail /var/log/mysql/error.log 

140818 10:31:49  InnoDB: Waiting for the background threads to start

140818 10:31:50 InnoDB: 5.5.38 started; log sequence number 1595675

140818 10:31:50 [ERROR] /usr/sbin/mysqld: unknown option '--explicit_defaults_for_timestamp'

140818 10:31:50 [ERROR] Aborting



140818 10:31:50  InnoDB: Starting shutdown...

140818 10:31:51  InnoDB: Shutdown completed; log sequence number 1595675

140818 10:31:51 [Note] 

140818 10:31:51 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

1



$ grep explicit_defaults_for_timestamp /etc/mysql/my.cnf /etc/init.d/mysql /etc/default/mysql

/etc/mysql/my.cnf:#explicit_defaults_for_timestamp

grep: /etc/default/mysql: No such file or directory

As can be seen, nothing new is written to the logs, and the explicit_defaults_for_timestamp option is not set anywhere.

I was able to reinstall mysql-server-core-5.5 but that did not resolve the issue:

$ sudo aptitude reinstall mysql-server-core-5.5

The following packages will be REINSTALLED:

  mysql-server-core-5.5 

0 packages upgraded, 0 newly installed, 1 reinstalled, 0 to remove and 0 not upgraded.

Need to get 3,213 kB of archives. After unpacking 0 B will be used.

Get: 1 http://il.archive.ubuntu.com/ubuntu/ trusty-updates/main mysql-server-core-5.5 amd64 5.5.38-0ubuntu0.14.04.1 [3,213 kB]

Fetched 3,213 kB in 1s (3,163 kB/s)                

(Reading database ... 217167 files and directories currently installed.)

Preparing to unpack .../mysql-server-core-5.5_5.5.38-0ubuntu0.14.04.1_amd64.deb ...

Unpacking mysql-server-core-5.5 (5.5.38-0ubuntu0.14.04.1) over (5.5.38-0ubuntu0.14.04.1) ...

Replaced by files in installed package mysql-common (5.6.20-1ubuntu14.04) ...

Processing triggers for man-db (2.6.7.1-1) ...

Setting up mysql-server-core-5.5 (5.5.38-0ubuntu0.14.04.1) ...



$ sudo service mysql status

mysql stop/waiting



$ sudo service mysql start

start: Job failed to start



$ tail /var/log/mysql/error.log 

140818 10:31:49  InnoDB: Waiting for the background threads to start

140818 10:31:50 InnoDB: 5.5.38 started; log sequence number 1595675

140818 10:31:50 [ERROR] /usr/sbin/mysqld: unknown option '--explicit_defaults_for_timestamp'

140818 10:31:50 [ERROR] Aborting



140818 10:31:50  InnoDB: Starting shutdown...

140818 10:31:51  InnoDB: Shutdown completed; log sequence number 1595675

140818 10:31:51 [Note] 

140818 10:31:51 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

1

That 1 on the last line of the log was added earlier with echo, and indicates that nothing new was added to the log.

You installed mysql-server-core-5.6, which partially failed or partially installed.

The -explicit_defaults_for_timestamp is only for MySQL 5.6. Its probable that the partial installation of mysql-server-core-5.6 added this option. I am going to suggest now that you remove any trace of mysql-server-core-5.6 and reinitialize the data directory.

1. 
   

   kill any running mysqld processes:

   
   
   

   ps aux | grep mysql
   
   kill pid
   
   

   
   


2. 
   

   Uninstall the mysql-server-core-5.6 packages:

   
   
   

   apt-get remove mysql-server-core-5.6
   
   

   
   
   

   A list of files is here

   
   


3. 
   

   Reinitialize the database directory:

   
   
   

   A. rm -Rf /var/lib/mysql/*

   
   
   

   B. mysql_install_db /var/lib/mysql

   
   



4. Comment out the !includedir /etc/mysql/conf.d/ option in my.cnf

Kill any existing MySQL Process and then start MySQL using the skip-grant-tables option.

A. Get the exact path of the mysqld daemon:

which mysqld_safe

B. Run MySQL without grant tables:

/mysqld_safe_directory/mysqld_safe --skip-grant-tables &

ex /bin/mysqld_safe

C. Make sure mysql is listening:

netstat -tlpn 

you should see port 3306.

D. If so, log into MySQL:

mysql -u root -h 127.0.0.1

E. Set new password:

mysql> use mysql;

mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';

mysql> flush privileges;

mysql> quit

I have this line in my.cnf

[pol@localhost mysql-5.6.19-linux-x86_64]$ grep expli my.cnf

explicit-defaults-for-timestamp = TRUE

I think that the my.cnf parameters should use hyphens and not underscores.