Ubuntu 14.04 MySQL 5.6 Upgrade Issue: Can't Start MySQL

April 12, 2017 2.3k views
MySQL Ubuntu

I have a droplet that I had installed MySQL 5.5 upon initial setup of server a year or so ago but haven't needed MySQL until recently. First, I wanted to update to 5.6 as it was what the project called for. So I ssh'd into the server and made sure everything was in working order first; logged into mysql, looked at the default test db etc, all was well. So I went on to updating:

sudo apt-get update
sudo apt-get upgrade
sudo apt-get install mysql-server-5.6

Everything appeared to be going fine, but upon exiting the install I get the following:

2017-04-12 15:00:51 0 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
2017-04-12 15:00:51 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-04-12 15:00:51 0 [Note] /usr/sbin/mysqld (mysqld 5.6.33-0ubuntu0.14.04.1) starting as process 6673 ...
start: Job failed to start
invoke-rc.d: initscript mysql, action "start" failed.
dpkg: error processing package mysql-server-5.6 (--configure):
 subprocess installed post-installation script returned error exit status 1
E: Sub-process /usr/bin/dpkg returned an error code (1)

And when trying to connect to mysql (mysql -u root -p) I get:

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

I've completely uninstalled, reinstalled, cleaned, autocleaned, and am at a loss. I even tried uninstalling 5.6 and trying to go back to 5.5 but that results in the same as above. What have I done? And what can I do to resolve it?

Thanks!

1 comment
  • Hello,

    I just had this issue after trying to upgrade ftom 5.5 to 5.6

    2017-04-28 20:43:53 0 [Warning] options --log-slow-admin-statements, --log-queries-not-using-indexes and --log-slow-slave-statements have no effect if --slow-query-log is not set
    2017-04-28 20:43:53 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
    2017-04-28 20:43:53 0 [Note] /usr/sbin/mysqld (mysqld 5.6.33-0ubuntu0.14.04.1-log) starting as process 31220 ...
    

    And it turns out that mysql server was trying to start but due to invalid config options it couldn't.

    You need to check your mysql error log (in my case /var/log/mysql/error.log) and change the mysql.conf (in my case `/etc/mysql/my.cnf).

    I had errors such as:

    2017-04-28 20:45:16 32149 [Note] InnoDB: 5.6.33 started; log sequence number 247826638098
    2017-04-28 20:45:16 32149 [ERROR] /usr/sbin/mysqld: unknown variable 'table_cache=4096'
    2017-04-28 20:45:16 32149 [ERROR] Aborting
    ...
    ...
    ...
    2017-04-28 21:09:38 19934 [ERROR] /usr/sbin/mysqld: unknown variable 'log_slow_queries=/var/log/mysql/mysql-slow.log'
    2017-04-28 21:09:38 19934 [ERROR] Aborting
    

    Hope this help others!

3 Answers
mrcbrown April 12, 2017
Accepted Answer

If it were me I'd remove any instances of old MySQL configurations - it could just be something is configured from the old setup, is the error message still the same or has it changed at all?

  • It's exactly the same. Could you inform me of what all needs to be completely removed to be considered free of all mysql binaries, configs, etc so that I can ensure a fresh install should work? As opposed to me unintelligently searching my entire droplet for references to mysql... I'm a decent developer, not the best sys admin. :P

    • All about learning, we all start somewhere:

      https://askubuntu.com/questions/640899/uninstall-mysql-completely

      Try this, it's a good tutorial, should help clear it all out - give that a go.

      • Still no luck. The exact same issue continues to arise:

        sudo apt-get install mysql-server-5.6
        Reading package lists... Done
        Building dependency tree
        Reading state information... Done
        The following extra packages will be installed:
          libdbd-mysql-perl libdbi-perl libterm-readkey-perl mysql-client-5.6
          mysql-client-core-5.6 mysql-common-5.6 mysql-server-core-5.6
        Suggested packages:
          libclone-perl libmldbm-perl libnet-daemon-perl libplrpc-perl
          libsql-statement-perl mailx tinyca
        The following NEW packages will be installed:
          libdbd-mysql-perl libdbi-perl libterm-readkey-perl mysql-client-5.6
          mysql-client-core-5.6 mysql-common-5.6 mysql-server-5.6
          mysql-server-core-5.6
        0 upgraded, 8 newly installed, 0 to remove and 2 not upgraded.
        Need to get 0 B/20.9 MB of archives.
        After this operation, 163 MB of additional disk space will be used.
        Do you want to continue? [Y/n] y
        Preconfiguring packages ...
        Selecting previously unselected package libdbi-perl.
        (Reading database ... 94696 files and directories currently installed.)
        Preparing to unpack .../libdbi-perl_1.630-1_amd64.deb ...
        Unpacking libdbi-perl (1.630-1) ...
        Selecting previously unselected package libdbd-mysql-perl.
        Preparing to unpack .../libdbd-mysql-perl_4.025-1ubuntu0.1_amd64.deb ...
        Unpacking libdbd-mysql-perl (4.025-1ubuntu0.1) ...
        Selecting previously unselected package libterm-readkey-perl.
        Preparing to unpack .../libterm-readkey-perl_2.31-1_amd64.deb ...
        Unpacking libterm-readkey-perl (2.31-1) ...
        Selecting previously unselected package mysql-client-core-5.6.
        Preparing to unpack .../mysql-client-core-5.6_5.6.33-0ubuntu0.14.04.1_amd64.deb ...
        Unpacking mysql-client-core-5.6 (5.6.33-0ubuntu0.14.04.1) ...
        Selecting previously unselected package mysql-client-5.6.
        Preparing to unpack .../mysql-client-5.6_5.6.33-0ubuntu0.14.04.1_amd64.deb ...
        Unpacking mysql-client-5.6 (5.6.33-0ubuntu0.14.04.1) ...
        Selecting previously unselected package mysql-server-core-5.6.
        Preparing to unpack .../mysql-server-core-5.6_5.6.33-0ubuntu0.14.04.1_amd64.deb ...
        Unpacking mysql-server-core-5.6 (5.6.33-0ubuntu0.14.04.1) ...
        Selecting previously unselected package mysql-server-5.6.
        Preparing to unpack .../mysql-server-5.6_5.6.33-0ubuntu0.14.04.1_amd64.deb ...
        egrep: /etc/mysql/: No such file or directory
        Unpacking mysql-server-5.6 (5.6.33-0ubuntu0.14.04.1) ...
        Selecting previously unselected package mysql-common-5.6.
        Preparing to unpack .../mysql-common-5.6_5.6.33-0ubuntu0.14.04.1_all.deb ...
        Unpacking mysql-common-5.6 (5.6.33-0ubuntu0.14.04.1) ...
        Processing triggers for man-db (2.6.7.1-1ubuntu1) ...
        Processing triggers for ureadahead (0.100.0-16) ...
        Setting up libdbi-perl (1.630-1) ...
        Setting up libdbd-mysql-perl (4.025-1ubuntu0.1) ...
        Setting up libterm-readkey-perl (2.31-1) ...
        Setting up mysql-client-core-5.6 (5.6.33-0ubuntu0.14.04.1) ...
        Setting up mysql-client-5.6 (5.6.33-0ubuntu0.14.04.1) ...
        Setting up mysql-server-core-5.6 (5.6.33-0ubuntu0.14.04.1) ...
        Setting up mysql-server-5.6 (5.6.33-0ubuntu0.14.04.1) ...
        2017-04-12 16:39:20 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
        2017-04-12 16:39:20 0 [Note] /usr/sbin/mysqld (mysqld 5.6.33-0ubuntu0.14.04.1) starting as process 11869 ...
        2017-04-12 16:39:20 11869 [Note] Plugin 'FEDERATED' is disabled.
        2017-04-12 16:39:20 11869 [Note] InnoDB: Using atomics to ref count buffer pool pages
        2017-04-12 16:39:20 11869 [Note] InnoDB: The InnoDB memory heap is disabled
        2017-04-12 16:39:20 11869 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
        2017-04-12 16:39:20 11869 [Note] InnoDB: Memory barrier is not used
        2017-04-12 16:39:20 11869 [Note] InnoDB: Compressed tables use zlib 1.2.8
        2017-04-12 16:39:20 11869 [Note] InnoDB: Using Linux native AIO
        2017-04-12 16:39:20 11869 [Note] InnoDB: Using CPU crc32 instructions
        2017-04-12 16:39:20 11869 [Note] InnoDB: Initializing buffer pool, size = 128.0M
        2017-04-12 16:39:20 11869 [Note] InnoDB: Completed initialization of buffer pool
        2017-04-12 16:39:20 11869 [Note] InnoDB: Highest supported file format is Barracuda.
        2017-04-12 16:39:20 11869 [Note] InnoDB: 128 rollback segment(s) are active.
        2017-04-12 16:39:20 11869 [Note] InnoDB: Waiting for purge to start
        2017-04-12 16:39:20 11869 [Note] InnoDB: 5.6.33 started; log sequence number 1625977
        2017-04-12 16:39:20 11869 [Note] Binlog end
        2017-04-12 16:39:20 11869 [Note] InnoDB: FTS optimize thread exiting.
        2017-04-12 16:39:20 11869 [Note] InnoDB: Starting shutdown...
        2017-04-12 16:39:22 11869 [Note] InnoDB: Shutdown completed; log sequence number 1625987
        start: Job failed to start
        invoke-rc.d: initscript mysql, action "start" failed.
        dpkg: error processing package mysql-server-5.6 (--configure):
         subprocess installed post-installation script returned error exit status 1
        Setting up mysql-common-5.6 (5.6.33-0ubuntu0.14.04.1) ...
        Errors were encountered while processing:
         mysql-server-5.6
        E: Sub-process /usr/bin/dpkg returned an error code (1)
        
        • How big is your droplet? Memory specifically? There has been some discussions I could find that say on a 512MB instance it could fail as one point simply because 5.6 defaults are higher and it consumes more memory - another says AppArmor could be the issue:

          https://bugs.launchpad.net/ubuntu/+source/mysql-5.6/+bug/1312936

          If you are using 512MB instance, may try a 1GB upgrade or one person went the cheaper route:

          http://askubuntu.com/a/510381

          Which walks through creating some additional swap which should work to improve memory so it can work itself out.

          • Nice. This does seem to help, though I'm not sure why. Especially since the issue occurred with 5.5 and 5.6. I took the swap approach for now as I'm still not convinced this is a reason to spend more money. 512mb should be more than sufficient AFAICT. Worth noting in regard to apparmor, which i'd never heard of till today, I did get one additional line after the process started:

            AppArmor parser error for /etc/apparmor.d/usr.sbin.mysqld in /etc/apparmor.d/usr.sbin.mysqld at line 9: Could not open 'abstractions/mysql'
            

            So far it hasn't had any negative effect, but still curious how all this happened.

            Either way, thanks for your time and effort in investigating.

    • Happy to help!

If you have no active data in-use, have you tried moving the entire MySQL Data folder out of the equation and then re-install 5.6? So something like:

mv /var/lib/mysql/ /var/lib/mysql_old/

Then try the install again, seems like maybe old tables are causing the issues or an upgrade gone wrong some how, but try clearing the data and try it again.

  • No luck. Same issue.

    • Same error message exactly? How about your my.cnf? Still stock from 5.5?

      • Looks like the standard default my.cnf:

        #
        # The MySQL database server configuration file.
        #
        # You can copy this to one of:
        # - "/etc/mysql/my.cnf" to set global options,
        # - "~/.my.cnf" to set user-specific options.
        #
        # One can use all long options that the program supports.
        # Run program with --help to get a list of available options and with
        # --print-defaults to see which it would actually understand and use.
        #
        # For explanations see
        # http://dev.mysql.com/doc/mysql/en/server-system-variables.html
        
        # This will be passed to all mysql clients
        # It has been reported that passwords should be enclosed with ticks/quotes
        # escpecially if they contain "#" chars...
        # Remember to edit /etc/mysql/debian.cnf when changing the socket location.
        [client]
        port        = 3306
        socket      = /var/run/mysqld/mysqld.sock
        
        # Here is entries for some specific programs
        # The following values assume you have at least 32M ram
        
        # This was formally known as [safe_mysqld]. Both versions are currently parsed.
        [mysqld_safe]
        socket      = /var/run/mysqld/mysqld.sock
        nice        = 0
        
        [mysqld]
        #
        # * Basic Settings
        #
        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
        skip-external-locking
        #
        # Instead of skip-networking the default is now to listen only on
        # localhost which is more compatible and is not less secure.
        bind-address        = 127.0.0.1
        #
        # * Fine Tuning
        #
        key_buffer      = 16M
        max_allowed_packet  = 16M
        thread_stack        = 192K
        thread_cache_size       = 8
        # This replaces the startup script and checks MyISAM tables if needed
        # the first time they are touched
        myisam-recover         = BACKUP
        #max_connections        = 100
        #table_cache            = 64
        #thread_concurrency     = 10
        #
        # * Query Cache Configuration
        #
        query_cache_limit   = 1M
        query_cache_size        = 16M
        #
        # * Logging and Replication
        #
        # Both location gets rotated by the cronjob.
        # Be aware that this log type is a performance killer.
        # As of 5.1 you can enable the log at runtime!
        #general_log_file        = /var/log/mysql/mysql.log
        #general_log             = 1
        #
        # Error log - should be very few entries.
        #
        log_error = /var/log/mysql/error.log
        #
        # Here you can see queries with especially long duration
        #log_slow_queries   = /var/log/mysql/mysql-slow.log
        #long_query_time = 2
        #log-queries-not-using-indexes
        #
        # The following can be used as easy to replay backup logs or for replication.
        # note: if you are setting up a replication slave, see README.Debian about
        #       other settings you may need to change.
        #server-id      = 1
        #log_bin            = /var/log/mysql/mysql-bin.log
        expire_logs_days    = 10
        max_binlog_size         = 100M
        #binlog_do_db       = include_database_name
        #binlog_ignore_db   = include_database_name
        #
        # * InnoDB
        #
        # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
        # Read the manual for more InnoDB related options. There are many!
        #
        # * Security Features
        #
        # Read the manual, too, if you want chroot!
        # chroot = /var/lib/mysql/
        #
        # For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
        #
        # ssl-ca=/etc/mysql/cacert.pem
        # ssl-cert=/etc/mysql/server-cert.pem
        # ssl-key=/etc/mysql/server-key.pem
        
        
        
        [mysqldump]
        quick
        quote-names
        max_allowed_packet  = 16M
        
        [mysql]
        #no-auto-rehash # faster start of mysql but no tab completition
        
        [isamchk]
        key_buffer      = 16M
        
        #
        # * IMPORTANT: Additional settings that can override those from this file!
        #   The files must end with '.cnf', otherwise they'll be ignored.
        #
        !includedir /etc/mysql/conf.d/
        

@anthonygreco

swap isn't generally recommended on solid-state disks (even DigitalOcean recommends against it) as it can degrade disk performance -- this is especially the case if you're using swap as more than just a means to provide temporary relief of an issue (i.e you're using it as a source of RAM in place of physically upgrading).

Even with SSD's, RAM is still faster, which means offloading to swap (i.e. the disk) is going to be far slower than it would be with RAM and it's going to cause higher CPU usage (as high disk usage causes wait times to jump, thus CPU jumps with it).

That said, the error you're seeing is most likely due to a package of some sort being left behind and it's causing issues.

You should be able to run:

apt-get -y remove mysql* && apt-get -y purge mysql*

Then try running:

apt-get update && apt-get -y upgrade

And it wipe the vast majority of what is installed -- while updating/upgrading existing packages. Some directories and files may still persist, though that should get rid of MySQL and it's dependencies for the most part.

NOTE -- Purge may remove your database data. So if you need data and haven't backed it up, you may lose it and there's no way to recover it unless you've got backups.

What you've posted doesn't show an issue with limited RAM, though it very well could be the case if MySQL fails to start once you get it installed without conflicts. 512MB of RAM really isn't much to be honest. Yes, you can run MySQL on 512MB RAM, though not all that efficiently and you need to know how to tweak/tune MySQL for low-RAM use cases. The default configuration most likely won't be suitable and isn't really meant for production -- it's meant as a starting point.

  • Yes, I agree, I may need to upgrade. ATM I'm just trying to find out how to UNDO the swapfile I added from http://askubuntu.com/questions/457923/why-did-installation-of-mysql-5-6-on-ubuntu-14-04-fail/510381 i.e.:

    sudo fallocate -l 4G /swapfile
    sudo chmod 600 /swapfile
    sudo mkswap /swapfile
    sudo swapon /swapfile
    

    but sudo swapoff /swapfile fails with:
    swapoff: /swapfile: swapoff failed: Cannot allocate memory

    Can you recommend how to UNDO that swap, then I'll again try wiping everything based on your commands above and try again?

    • @anthonygreco

      You should be able to use swapoff -a and then remove the entry from /etc/fstab. You will want to reboot once you make those changes using reboot from the CLI so they stick. As long as swap is off, you should then be able to remove the file.

      • Commented the one line that was active in /etc/fstab. Resized droplet, all issues are addressed. Looks like mysql really did need more memory. Thanks all.

        • @anthonygreco

          That's the case more often then not :-).

          When it comes to MySQL, it's all about tweaking and tuning the configuration, but there's a point where regardless of what you do, you simply need more RAM.

          There's also a point where throwing more RAM at it won't help and the only option is to tweak and tune. It goes both ways.

Have another answer? Share your knowledge.