11

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.

  • when you run netstat -tlpn, do you see the port 3306 for MySQL? – Craig Efrein Aug 18 '14 at 8:09
  • @CraigEfrein: No, port 3306 is not being listened to. I even restart the system to see if MySQL would start on boot, but it didn't. – dotancohen Aug 18 '14 at 8:59
  • And the --explicit_defaults_for_timestamp, possible places to look, if you haven't already. /etc/mysql/my.cnf, /etc/init.d/mysql and /etc/default/mysql – Craig Efrein Aug 18 '14 at 9:03
  • Thank you. The string timestamp was found only in etc/mysql/my.cnf and I've comented it out. Yet, I still get the same mention of it in the log. – dotancohen Aug 18 '14 at 9:26
  • Is there a my.cnf in your home directory? – Vérace Aug 18 '14 at 12:00
4

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
|improve this answer|||||
  • Thank you Craig. I was able to reinstall mysql-server-core-5.5 but that did not resolve the issue. I'll add that to the question. – dotancohen Aug 18 '14 at 11:17
  • @dotancohen, updated my answer – Craig Efrein Aug 18 '14 at 12:18
  • Thank you. I completely removed any trace of 5.6, rm'ed both /var/lib/mysql and /var/log/mysql. The mysql_install_db command failed because resolveip was not found. I then installed mysql-server-5.5 (which apparently is not installed along with mysql-server-core-5.5) to get resolveip. During the install of mysql-server-5.5 I got the Unable to set password for the MySQL "root" user error again, back to square one! – dotancohen Aug 18 '14 at 13:14
  • @dotancohen updated answer. This depends on whether or not MySQL is actually listening. – Craig Efrein Aug 18 '14 at 13:27
  • Thanks. In fact, mysqld_safe dies right away: 40818 16:52:52 mysqld_safe Logging to '/var/lib/mysql/bruno.err'. , 140818 16:52:52 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql , 140818 16:52:54 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended. – dotancohen Aug 18 '14 at 13:57
1

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.

|improve this answer|||||
  • Thank you. You are right, I had that line as well though I missed it. Now, mysql won't start but nothing is written to the logs. I'll update the question. – dotancohen Aug 18 '14 at 11:05
  • I know that on Xubuntu (can't stand Unity desktop!) I had the devil of a time with issues like this. I now build from source - if you're interested, I'll write out how I do it exactly. What I do is adapted from here after much trial and error. It's a bit more work than sudo apt-get install xxx, but at least I know where my files are and what to do if things start to go wrong! – Vérace Aug 18 '14 at 12:06
  • Thank you, if I cannot get through this then I'll try the instructions that you link to. – dotancohen Aug 18 '14 at 12:08
  • Well, that's for people who want to compile and run mysql through eclipse - I've adapted it for someone who just wants to run mysql normally. Why are you using 5.5? – Vérace Aug 18 '14 at 12:11
  • Actually, I would prefer 5.6 and I don't use eclipse. – dotancohen Aug 18 '14 at 12:40

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.