Question

HTTPS Returns 500 Internal Server Error While HTTP Does Not?

Posted February 25, 2017 42.3k views
UbuntuApache

I’ve been trying to get SSL running on my domain (thebashfeed.com) for a few days. I believe I have Apache set up properly and installed Lets Encrypt using DO’s official tutorial. The site is accessible from http:// but not from https://. In fact, when you lead the domain with https:// it returns an internal server error with the following verbiage:

Internal Server Error

The server encountered an internal error or misconfiguration and was unable to complete your request.

Please contact the server administrator at webmaster@localhost to inform them of the time this error occurred, and the actions you performed just before this error.

More information about this error may be available in the server error log.
**

Here is my server’s Apache error log:**

[Fri Feb 24 19:25:11.151229 2017] [mpm_prefork:notice] [pid 30408] AH00163: Apache/2.4.7 (Ubuntu) PHP/5.5.9-1ubuntu4.21 OpenSSL/1.0.1f configured – resuming normal operations
[Fri Feb 24 19:25:11.151330 2017] [core:notice] [pid 30408] AH00094: Command line: ’/usr/sbin/apache2’
libpng warning: Incorrect sBIT chunk length
libpng warning: Incorrect sBIT chunk length
[Fri Feb 24 20:49:50.636080 2017] [mpm_prefork:notice] [pid 30408] AH00169: caught SIGTERM, shutting down
[Fri Feb 24 20:49:51.460157 2017] [mpm_prefork:notice] [pid 30887] AH00163: Apache/2.4.7 (Ubuntu) PHP/5.5.9-1ubuntu4.21 OpenSSL/1.0.1f configured – resuming normal operations
[Fri Feb 24 20:49:51.460250 2017] [core:notice] [pid 30887] AH00094: Command line: ’/usr/sbin/apache2’
[Fri Feb 24 21:00:11.496576 2017] [mpm_prefork:notice] [pid 30887] AH00169: caught SIGTERM, shutting down
[Fri Feb 24 21:15:00.327000 2017] [mpm_prefork:notice] [pid 31444] AH00163: Apache/2.4.7 (Ubuntu) PHP/5.5.9-1ubuntu4.21 OpenSSL/1.0.1f configured – resuming normal operations
[Fri Feb 24 21:15:00.327157 2017] [core:notice] [pid 31444] AH00094: Command line: ’/usr/sbin/apache2’

If you can help me understand what needs to be done I would greatly appreciate it.

Thank in advance.

Add a comment
bashfeedbusiness
By:
bashfeedbusiness
Add a comment
4 comments
  • hansen February 25, 2017
    Reply Report

    Can you supply your entire configuration for the domain? It should be placed in /etc/apache2/sites-available/
    If you have sensitive information, like paths, domains or IPs, then just mask it.
    There must be a configuration error somewhere, which leads to the internal error.
    Sadly the log file you’ve included doesn’t give much help. It usually does, but something else must be triggering a fatal error.

  • bashfeedbusiness February 25, 2017
    Reply Report

    This is the configuration located at /etc/apache2/sites-available/mydomain.com.conf

    <VirtualHost *:80>
    # The ServerName directive sets the request scheme, hostname and port that
    # the server uses to identify itself. This is used when creating
    # redirection URLs. In the context of virtual hosts, the ServerName
    # specifies what hostname must appear in the request’s Host: header to
    # match this virtual host. For the default virtual host (this file) this
    # value is not decisive as it is used as a last resort host regardless.
    # However, you must set it for any further virtual host explicitly.
    #ServerName www.example.com

        ServerAdmin webmaster@mydomain.com
    DocumentRoot /var/www/
    ServerName mydomain.com
    ServerAlias www.mydomain.com

    # Available loglevels: trace8, ..., trace1, debug, info, notice, warn,
    # error, crit, alert, emerg.
    # It is also possible to configure the loglevel for particular
    # modules, e.g.
    #LogLevel info ssl:warn

    ErrorLog ${APACHE_LOG_DIR}/error.log
    CustomLog ${APACHE_LOG_DIR}/access.log combined

    <Directory /var/www>
            Options Indexes FollowSymLinks MultiViews
            AllowOverride All
            Order allow,deny
            allow from all
                    Require all granted
    </Directory>
    # For most configuration files from conf-available/, which are
    # enabled or disabled at a global level, it is possible to
    # include a line for only one particular virtual host. For example the
    # following line enables the CGI configuration for this host only
    # after it has been globally disabled with "a2disconf".
    #Include conf-available/serve-cgi-bin.conf

    </VirtualHost>

    <VirtualHost *:443>
    ServerName mydomain.com
    ServerAlias www.mydomain.com

        <Directory /var/www>
            Options Indexes FollowSymLinks MultiViews
            AllowOverride All
            Order allow,deny
            allow from all
    Require all granted
    </Directory>

    ServerAdmin webmaster@mydomain.com
    DocumentRoot /var/www
    SSLEngine on
    SSLCertificateFile /etc/letsencrypt/live/mydomain.com/cert.pem
    SSLCertificateKeyFile /etc/letsencrypt/live/mydomain.com/privkey.pem

    ErrorLog ${APACHE_LOG_DIR}/error.log
    CustomLog ${APACHE_LOG_DIR}/access.log combined

    </VirtualHost>

    vim: syntax=apache ts=4 sw=4 sts=4 sr noet

    NOTE
    It might be worth noting that there is a 000-default.conf in the same location, which I have made some adjustments to although it is my understanding that the 000-default.conf file has been disabled. I could be wrong though.

  • hansen February 25, 2017
    Reply Report

    Every conf-file placed in /etc/apache2/sites-enabled/ are active. Everything here should only be linked to files in /sites-available/
    Can you also list your /etc/apache2/conf-available/ssl-params.conf
    It seems like something might have happened when Let’s Encrypt created the configuration automatically.

  • bashfeedbusiness February 25, 2017
    Reply Report

    That file doesn’t exist in that location. Could that be the issue?

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

×
8 answers
jtittle1 March 8, 2017

@bashfeedbusiness

In regards to Apache, if you’re still having an issue with Apache + SSL, I just posted recent configs for LetsEncrypt, which I’ve outlined below for comparison.

…

/etc/apache2/sites-available/000-default.conf

<VirtualHost *:80>
        ServerAdmin webmaster@localhost
        DocumentRoot /var/www/html
        ServerName domain.com
        ServerAlias www.domain.com

        <Directory /var/www/html/>
            Options FollowSymLinks
            AllowOverride All
            Require all granted
        </Directory>

        ErrorLog ${APACHE_LOG_DIR}/error.log
        CustomLog ${APACHE_LOG_DIR}/access.log combined

        RewriteEngine on
        RewriteCond %{SERVER_NAME} =domain.com [OR]
        RewriteCond %{SERVER_NAME} =www.domain.com
        RewriteRule ^ https://%{SERVER_NAME}%{REQUEST_URI} [END,QSA,R=permanent]
</VirtualHost>

…

/etc/apache2/sites-available/000-default-le-ssl.conf

<IfModule mod_ssl.c>
<VirtualHost *:443>
        ServerAdmin webmaster@localhost
        DocumentRoot /var/www/html
        ServerName domain.com
        ServerAlias www.domain.com

        <Directory /var/www/html/>
            Options FollowSymLinks
            AllowOverride All
            Require all granted
        </Directory>

        ErrorLog ${APACHE_LOG_DIR}/error.log
        CustomLog ${APACHE_LOG_DIR}/access.log combined
        SSLCertificateFile /etc/letsencrypt/live/domain.com/fullchain.pem
        SSLCertificateKeyFile /etc/letsencrypt/live/domain.com/privkey.pem
        Include /etc/letsencrypt/options-ssl-apache.conf
</VirtualHost>
</IfModule>

…

/var/www/html/.htaccess

# BEGIN WordPress
<IfModule mod_rewrite.c>
RewriteEngine On
RewriteBase /
RewriteRule ^index\.php$ - [L]
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule . /index.php [L]
</IfModule>

# END WordPress

…

Of course, in the above, domain.com should be replaced with your actual domain name. These are stock, and should work for any domain that has a valid SSL Certificate from LetsEncrypt w/ WP.

These are from ./sites-available and are symlinked to ./sites-enabled by default (which was done by LE when the service was ran).

The line in /etc/apache2/apache2.conf that controls pulling from ./sites-enabled is:

IncludeOptional sites-enabled/*.conf

So you could very well change that to better suit your own needs, or to just include a single site. For example, I could modify that to:

IncludeOptional sites-available/000-default.conf
IncludeOptional sites-available/000-default-le-ssl.conf

And in those files, I’d use the configuration I posted above, then restart apache2.

service apache2 restart

And that should get things in working order for you. You don’t have to use those two directories. I rarely do as I rather dislike having to use symlinks for configuration. I’d much rather it be located in a central directory and simply use mv or cp to get files where they need to be as symlinks can be finicky from time to time.

Reply Report
  • bashfeedbusiness March 8, 2017

    Awesome, this did the trick and brought to light a deluge of syntax errors, which I think were also causing conflicts within other files. Once fixed the https:// was working on my site! :)

    Thank you!!

    Reply Report
jtittle1 February 26, 2017

@bashfeedbusiness

If you don’t have the ./sites-available/ and ./sites-enabled/ directories, then you’re most likely pulling from either ./conf or ./conf.d, in which case anything that resides in there is going to get included by Apache during startup.

So if there’s an error in your 000-default.conf file, it could easily affect the rest of your sites just as a misconfigured .htaccess file could.

Do you happen to use .htaccess for any of your websites (normally in the web root for the domain) or is all configuration done through the Apache configuration?

Reply Report
  • bashfeedbusiness March 8, 2017

    @jtittle @hansen Any ideas for me? I’m getting frequent database errors now, too. I have had to run a mysql restart almost every day since I installed SSL.

    Reply Report
    • hansen March 8, 2017

      Okay, the HTTPS doesn’t have anything to do with MySQL, so let’s just focus on MySQL for now.
      What type of error messages are you seeing?
      Can you show the mysql log file here? It should be placed in /var/log/mysql.log but might be in a different location on your server.

      Reply Report
    • jtittle1 March 8, 2017

      @bashfeedbusiness

      SSL wouldn’t directly affect MySQL (i.e. cause it to crash, fail, etc) – unless you’ve setup SSL for MySQL (which would be entirely different than setting it up for Apache), so another issue would be responsible for this.

      @hansen beat me to saying that, but posting anyway :-).

      So as noted, we’d need to look at the MySQL log. If you can run tail -50 on the log and post the output, that’ll give us an idea of what’s going on.

      i.e.

      tail -50 /var/log/mysql/error.log
      Reply Report
      • bashfeedbusiness March 8, 2017

        @jtittle @hansen

        Oddly, when I sudo nano /var/log/mysql.log (which I have located in Filezilla) it returns that there is no such file or directory.

        However,
        this is what the tail -50 /var/log/mysql/error.log command returned:

        170308 14:51:40 [Note] Plugin ‘FEDERATED’ is disabled.
        170308 14:51:40 InnoDB: The InnoDB memory heap is disabled
        170308 14:51:40 InnoDB: Mutexes and rw_locks use GCC atomic builtins
        170308 14:51:40 InnoDB: Compressed tables use zlib 1.2.8
        170308 14:51:40 InnoDB: Using Linux native AIO
        170308 14:51:40 InnoDB: Initializing buffer pool, size = 128.0M
        170308 14:51:40 InnoDB: Completed initialization of buffer pool
        170308 14:51:40 InnoDB: highest supported file format is Barracuda.
        InnoDB: Log scan progressed past the checkpoint lsn 46413789915
        170308 14:51:40 InnoDB: Database was not shut down normally!
        InnoDB: Starting crash recovery.
        InnoDB: Reading tablespace information from the .ibd files…
        InnoDB: Restoring possible half-written data pages from the doublewrite
        InnoDB: buffer…
        InnoDB: Doing recovery: scanned up to log sequence number 46413790235
        170308 14:51:41 InnoDB: Starting an apply batch of log records to the database…
        InnoDB: Progress in percents: 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
        InnoDB: Apply batch completed
        170308 14:51:41 InnoDB: Waiting for the background threads to start
        170308 14:51:42 InnoDB: 5.5.38 started; log sequence number 46413790235
        170308 14:51:42 [Note] Server hostname (bind-address): '127.0.0.1’; port: 3306
        170308 14:51:42 [Note] - '127.0.0.1’ resolves to '127.0.0.1’;
        170308 14:51:42 [Note] Server socket created on IP: '127.0.0.1’.
        170308 14:51:42 [Note] Event Scheduler: Loaded 0 events
        170308 14:51:42 [Note] /usr/sbin/mysqld: ready for connections.
        Version: '5.5.38-0ubuntu0.14.04.1’ socket: ’/var/run/mysqld/mysqld.sock’ port: 3306 (Ubuntu)
        170308 14:52:11 [Note] /usr/sbin/mysqld: Normal shutdown

        170308 14:52:11 [Note] Event Scheduler: Purging the queue. 0 events
        170308 14:52:11 InnoDB: Starting shutdown…
        170308 14:52:13 InnoDB: Shutdown completed; log sequence number 46413796258
        170308 14:52:13 [Note] /usr/sbin/mysqld: Shutdown complete

        170308 14:52:13 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
        170308 14:52:13 [Note] Plugin 'FEDERATED’ is disabled.
        170308 14:52:13 InnoDB: The InnoDB memory heap is disabled
        170308 14:52:13 InnoDB: Mutexes and rw_locks use GCC atomic builtins
        170308 14:52:13 InnoDB: Compressed tables use zlib 1.2.8
        170308 14:52:13 InnoDB: Using Linux native AIO
        170308 14:52:13 InnoDB: Initializing buffer pool, size = 128.0M
        170308 14:52:13 InnoDB: Completed initialization of buffer pool
        170308 14:52:13 InnoDB: highest supported file format is Barracuda.
        170308 14:52:13 InnoDB: Waiting for the background threads to start
        170308 14:52:14 InnoDB: 5.5.38 started; log sequence number 46413796258
        170308 14:52:14 [Note] Server hostname (bind-address): '127.0.0.1’; port: 3306
        170308 14:52:14 [Note] - '127.0.0.1’ resolves to '127.0.0.1’;
        170308 14:52:14 [Note] Server socket created on IP: '127.0.0.1’.
        170308 14:52:14 [Note] Event Scheduler: Loaded 0 events
        170308 14:52:14 [Note] /usr/sbin/mysqld: ready for connections.
        Version: '5.5.38-0ubuntu0.14.04.1’ socket: ’/var/run/mysqld/mysqld.sock’ port: 3306 (Ubuntu)

        Reply Report
        • hansen March 8, 2017

          @bashfeedbusiness Great, can you do it again with tail -100 /var/log/mysql/error.log since we need to see why it crashed.
          @jtittle Beat you :-)

          Reply Report
          • bashfeedbusiness March 8, 2017

            @hansen

            Here is the return from the -100 command:

            170308 7:06:12 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
            170308 7:06:12 [Note] Plugin ‘FEDERATED’ is disabled.
            170308 7:06:12 InnoDB: The InnoDB memory heap is disabled
            170308 7:06:12 InnoDB: Mutexes and rw_locks use GCC atomic builtins
            170308 7:06:12 InnoDB: Compressed tables use zlib 1.2.8
            170308 7:06:12 InnoDB: Using Linux native AIO
            170308 7:06:12 InnoDB: Initializing buffer pool, size = 128.0M
            InnoDB: mmap(137363456 bytes) failed; errno 12
            170308 7:06:12 InnoDB: Completed initialization of buffer pool
            170308 7:06:12 InnoDB: Fatal error: cannot allocate memory for the buffer pool
            170308 7:06:12 [ERROR] Plugin 'InnoDB’ init function returned error.
            170308 7:06:12 [ERROR] Plugin 'InnoDB’ registration as a STORAGE ENGINE failed.
            170308 7:06:12 [ERROR] Unknown/unsupported storage engine: InnoDB
            170308 7:06:12 [ERROR] Aborting

            170308 7:06:12 [Note] /usr/sbin/mysqld: Shutdown complete

            170308 7:06:13 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
            170308 7:06:13 [Note] Plugin 'FEDERATED’ is disabled.
            170308 7:06:13 InnoDB: The InnoDB memory heap is disabled
            170308 7:06:13 InnoDB: Mutexes and rw_locks use GCC atomic builtins
            170308 7:06:13 InnoDB: Compressed tables use zlib 1.2.8
            170308 7:06:13 InnoDB: Using Linux native AIO
            170308 7:06:13 InnoDB: Initializing buffer pool, size = 128.0M
            InnoDB: mmap(137363456 bytes) failed; errno 12
            170308 7:06:13 InnoDB: Completed initialization of buffer pool
            170308 7:06:13 InnoDB: Fatal error: cannot allocate memory for the buffer pool
            170308 7:06:13 [ERROR] Plugin 'InnoDB’ init function returned error.
            170308 7:06:13 [ERROR] Plugin 'InnoDB’ registration as a STORAGE ENGINE failed.
            170308 7:06:13 [ERROR] Unknown/unsupported storage engine: InnoDB
            170308 7:06:13 [ERROR] Aborting

            170308 7:06:13 [Note] /usr/sbin/mysqld: Shutdown complete

            170308 14:51:40 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
            170308 14:51:40 [Note] Plugin 'FEDERATED’ is disabled.
            170308 14:51:40 InnoDB: The InnoDB memory heap is disabled
            170308 14:51:40 InnoDB: Mutexes and rw_locks use GCC atomic builtins
            170308 14:51:40 InnoDB: Compressed tables use zlib 1.2.8
            170308 14:51:40 InnoDB: Using Linux native AIO
            170308 14:51:40 InnoDB: Initializing buffer pool, size = 128.0M
            170308 14:51:40 InnoDB: Completed initialization of buffer pool
            170308 14:51:40 InnoDB: highest supported file format is Barracuda.
            InnoDB: Log scan progressed past the checkpoint lsn 46413789915
            170308 14:51:40 InnoDB: Database was not shut down normally!
            InnoDB: Starting crash recovery.
            InnoDB: Reading tablespace information from the .ibd files…
            InnoDB: Restoring possible half-written data pages from the doublewrite
            InnoDB: buffer…
            InnoDB: Doing recovery: scanned up to log sequence number 46413790235
            170308 14:51:41 InnoDB: Starting an apply batch of log records to the database…
            InnoDB: Progress in percents: 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
            InnoDB: Apply batch completed
            170308 14:51:41 InnoDB: Waiting for the background threads to start
            170308 14:51:42 InnoDB: 5.5.38 started; log sequence number 46413790235
            170308 14:51:42 [Note] Server hostname (bind-address): '127.0.0.1’; port: 3306
            170308 14:51:42 [Note] - '127.0.0.1’ resolves to '127.0.0.1’;
            170308 14:51:42 [Note] Server socket created on IP: '127.0.0.1’.
            170308 14:51:42 [Note] Event Scheduler: Loaded 0 events
            170308 14:51:42 [Note] /usr/sbin/mysqld: ready for connections.
            Version: '5.5.38-0ubuntu0.14.04.1’ socket: ’/var/run/mysqld/mysqld.sock’ port: 3306 (Ubuntu)
            170308 14:52:11 [Note] /usr/sbin/mysqld: Normal shutdown

            170308 14:52:11 [Note] Event Scheduler: Purging the queue. 0 events
            170308 14:52:11 InnoDB: Starting shutdown…
            170308 14:52:13 InnoDB: Shutdown completed; log sequence number 46413796258
            170308 14:52:13 [Note] /usr/sbin/mysqld: Shutdown complete

            170308 14:52:13 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
            170308 14:52:13 [Note] Plugin 'FEDERATED’ is disabled.
            170308 14:52:13 InnoDB: The InnoDB memory heap is disabled
            170308 14:52:13 InnoDB: Mutexes and rw_locks use GCC atomic builtins
            170308 14:52:13 InnoDB: Compressed tables use zlib 1.2.8
            170308 14:52:13 InnoDB: Using Linux native AIO
            170308 14:52:13 InnoDB: Initializing buffer pool, size = 128.0M
            170308 14:52:13 InnoDB: Completed initialization of buffer pool
            170308 14:52:13 InnoDB: highest supported file format is Barracuda.
            170308 14:52:13 InnoDB: Waiting for the background threads to start
            170308 14:52:14 InnoDB: 5.5.38 started; log sequence number 46413796258
            170308 14:52:14 [Note] Server hostname (bind-address): '127.0.0.1’; port: 3306
            170308 14:52:14 [Note] - '127.0.0.1’ resolves to '127.0.0.1’;
            170308 14:52:14 [Note] Server socket created on IP: '127.0.0.1’.
            170308 14:52:14 [Note] Event Scheduler: Loaded 0 events
            170308 14:52:14 [Note] /usr/sbin/mysqld: ready for connections.
            Version: '5.5.38-0ubuntu0.14.04.1’ socket: ’/var/run/mysqld/mysqld.sock’ port: 3306 (Ubuntu)

            P.S. Thank you both so much for all your help

            Reply Report
        • jtittle1 March 8, 2017

          @bashfeedbusiness

          Try running

          tail -100 /var/log/mysql/error.log

          From what I can see, there’s nothing showing in those 50 lines that indicates any sort of issue. What I normally look for when a client is telling me that MySQL is randomly crashing is OOM errors, or other signals that tell me RAM is being exhausted as that’s actually common.

          It can happen a lot when you’re using the default MySQL configuration as it’s not really tuned for production use unless it’s super-low traffic.

          Reply Report
          • hansen March 8, 2017

            @bashfeedbusiness Like @jtittle is saying, the server is actually running out of RAM, hence the error 170308 7:06:13 InnoDB: Fatal error: cannot allocate memory for the buffer pool
            You could add more RAM to your droplet (scale it up to the next price level), which I would recommend, or you would have to tune the buffer pool to something lower than 128MB, which I wouldn’t recommend.

            Reply Report
bashfeedbusiness February 26, 2017

I do have both the ./sites-available and ./sites-enabled directories, just not the ./ssl-params.conf file.

Yes I use .htaccess for the wordpress installation on the domain hosted on this Droplet.

Reply Report
jtittle1 March 8, 2017

@bashfeedbusiness

This is what I was looking for :-).

170308 7:06:12 InnoDB: Fatal error: cannot allocate memory for the buffer pool
170308 7:06:12 [ERROR] Plugin 'InnoDB' init function returned error.
170308 7:06:12 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
170308 7:06:12 [ERROR] Unknown/unsupported storage engine: InnoDB
170308 7:06:12 [ERROR] Aborting

Specifically:

170308 7:06:12 InnoDB: Fatal error: cannot allocate memory for the buffer pool

That error means you’re running out of RAM and MySQL/MariaDB is not able to allocate sufficient RAM, so it fails. What size is your Droplet? (specifically RAM).

Also, let’s figure out where your actual MySQL configuration is. You should have this directory:

/etc/mysql/mysql.conf.d

Inside that directory is going to be one of two files.

1). mysqld.cnf

or

2). 50-server.cnf

Can you post the complete contents of that file?

Reply Report
  • bashfeedbusiness March 8, 2017

    The conf.d file contains 2 files:

    1.) .keepme (empty)
    2.) .mysqldsafesyslog.cnf

    .mysqldsafesyslog.cnf only contains two lines which are:

    [mysqld_safe]
    syslog

    I’m not opposed to upgrading my droplet’s RAM but if there is another option let me know.

    Thank you.

    Reply Report
    • jtittle1 March 8, 2017

      @bashfeedbusiness

      What size is your current Droplet?

      I tried scanning over the previous posts, though didn’t see a mention of whether you were using MariaDB or MySQL. If MariaDB, then that 50-server.cnf file will be in:

      /etc/mysql/mariadb.conf.d

      That’s ultimately what I’m trying to find. The default configuration file should contain lines such as:

      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

      That’s the file I need to see (the entire file) so I can see what’s showing up there.

      If this is a 1GB Droplet, chances are you’ll need to upgrade to at least a 2GB, and we can then tweak configuration a bit (once we know the actual file we need to modify).

      Reply Report
      • bashfeedbusiness March 8, 2017

        I actually have a 4GB droplet configured w/ 60GB of disk space. Our site gets a lot of traffic.

        I was able to locate the file under /etc/mysql/my.cnf
        Here’s the contents:

        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

        keybuffer = 16M
        max        allowedpacket = 16M
        thread        stack = 192K
        threadcachesize = 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

        querycachelimit = 1M
        querycachesize = 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!

        generallogfile = /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

        logslowqueries = /var/log/mysql/mysql-slow.log

        longquerytime = 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

        expirelogsdays = 10
        maxbinlogsize = 100M

        binlogdodb = includedatabasename

        binlogignoredb = includedatabasename

        * 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
        maxallowedpacket = 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/

        Reply Report
jtittle1 March 8, 2017

@bashfeedbusiness

If you wouldn’t mind, would you please repost that in a code block. To do that, it’d be three backticks, hit enter, paste the config, hit enter, and three more backticks.

Using a code block will keep it formatted. The copy and paste above slightly changes the config and it’s hard to tell what is and isn’t commented in the file as it appears much of that has been stripped.

Once I have that, I’ll see what we can do to tune the config.

You mention that you have higher levels of traffic. What are we looking at?

Reply Report
  • bashfeedbusiness March 8, 2017 
    #
# 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/
    Reply Report
jtittle1 March 8, 2017

@bashfeedbusiness

Here’s what I would try and see how it works for you. This is based off an 8GB & 12GB Droplet I was working on with a client just a week ago.

If this doesn’t work, you may in fact need to upgrade RAM, or consider splitting the database from the web server (i.e. a Droplet dedicated to MySQL only).

Please make sure you backed up your configuration prior (as noted in my previous response) so you can revert back should something not work. This should work as-is, though different versions have different options.

[client]
port                                    = 3306
socket                                  = /var/run/mysqld/mysqld.sock

[mysqld_safe]
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

skip-external-locking

bind-address                            = 127.0.0.1

key_buffer_size                         = 256M
join_buffer_size                        = 128K
read_buffer_size                        = 256K
sort_buffer_size                        = 256K
table_definition_cache                  = 8192
table_open_cache                        = 4096
thread_cache_size                       = 256
tmp_table_size                          = 256M
max_heap_table_size                     = 256M
max_allowed_packet                      = 64M
thread_stack                            = 192K
thread_cache_size                       = 8
myisam_sort_buffer_size                 = 256M
myisam_max_sort_file_size               = 2048M

myisam-recover                          = BACKUP

group_concat_max_len                    = 1024
max_length_for_sort_data                = 1024
net_buffer_length                       = 16384
max_connect_errors                      = 100000
concurrent_insert                       = 2
read_rnd_buffer_size                    = 512K
bulk_insert_buffer_size                 = 8M

query_cache_limit                       = 512K
query_cache_size                        = 64M
query_cache_type                        = 1
query_cache_min_res_unit                = 2K
query_prealloc_size                     = 262144
query_alloc_block_size                  = 65536

transaction_alloc_block_size            = 8192
transaction_prealloc_size               = 4096

default-storage-engine                  = InnoDB

log_warnings                            = 1
slow_query_log                          = 0
long_query_time                         = 1
slow_query_log_file                     = /var/log/mysql/slowq.log
log_error                               = /var/log/mysql/error.log

innodb_large_prefix                     = 1
innodb_purge_threads                    = 1
innodb_doublewrite                      = 1

innodb_file_per_table                   = 1
innodb_open_files                       = 1000
innodb_data_file_path                   = ibdata1:10M:autoextend
innodb_buffer_pool_size                 = 128M
innodb_additional_mem_pool_size         = 32M

innodb_log_files_in_group               = 2
innodb_log_file_size                    = 64M
innodb_log_buffer_size                  = 8M
innodb_flush_log_at_trx_commit          = 2
innodb_thread_concurrency               = 0
innodb_lock_wait_timeout                = 50

innodb_io_capacity                      = 100
innodb_read_io_threads                  = 2
innodb_write_io_threads                 = 2

[mysqldump]
quick
quote-names
max_allowed_packet                      = 64M

[mysql]

[isamchk]
key_buffer                              = 256M 
sort_buffer                             = 256K
read_buffer                             = 256K
write_buffer                            = 256K

!includedir /etc/mysql/conf.d/

I’ve cleaned up the comments and removed the options that were commented out to reduce the size, though it’s still a little lengthy.

Should MySQL fail to start, we need to check the MySQL error log, run tail -20 on it and that should help us troubleshoot which configuration var doesn’t work with the version you’re running.

Reply Report
  • bashfeedbusiness March 9, 2017

    I’ve made these updates to the file. So far everything looks good, but I’ll keep this thread open for a couple days to be sure.

    Thank you @jtittle and @hansen for all your help!

    Reply Report
    • hansen March 9, 2017

      Sorry @bashfeedbusiness I was out - so all credit to @jtittle for a really good my.cnf configuration (it looks like one of my servers :)
      If you have Perl installed (think it’s installed by default on Ubuntu), then I would highly recommend using this to help tune your database:
      https://github.com/major/MySQLTuner-perl
      It’s a single script that looks through the statistics on the database and gives hints what you might want to look at.
      You shouldn’t pay much notice to this if the database has been running for less than a day. The more time the database runs, the better the analysis will be.

      Reply Report
      • jtittle1 March 9, 2017

        @bashfeedbusiness

        I’d second the recommendation on MySQL Tuner. It can be very helpful when trying to troubleshoot and further optimize configuration settings.

        It’s an essential tool that anyone running MySQL should have and it’s actively ran and maintained by a fellow sysadmin that knows his stuff.

        It does output a lot of information, so if you use it, don’t be alarmed. Much like the configuration example I provided, it’s pretty in-depth.

        Reply Report
  • bashfeedbusiness March 10, 2017

    @jtittle @hansen

    Hate to bother you guys again, but today i’m getting a database error when trying to reach the site. I tried a mysql restart from root to no avail.

    Heres the error:

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

    Here’s the -20 error log:

    170309 19:43:13 [Note] /usr/sbin/mysqld: Shutdown complete

170309 19:43:14 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
170309 19:43:14 [Note] Plugin 'FEDERATED' is disabled.
170309 19:43:14 InnoDB: The InnoDB memory heap is disabled
170309 19:43:14 InnoDB: Mutexes and rw_locks use GCC atomic builtins
170309 19:43:14 InnoDB: Compressed tables use zlib 1.2.8
170309 19:43:14 InnoDB: Using Linux native AIO
170309 19:43:14 InnoDB: Initializing buffer pool, size = 128.0M
170309 19:43:14 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 67108864 bytes!
170309 19:43:15 [ERROR] Plugin 'InnoDB' init function returned error.
170309 19:43:15 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
170309 19:43:15 [ERROR] Unknown/unsupported storage engine: InnoDB
170309 19:43:15 [ERROR] Aborting

170309 19:43:15 [Note] /usr/sbin/mysqld: Shutdown complete

    Any help is much appreciated.

    Reply Report
    • jtittle1 March 10, 2017

      @bashfeedbusiness

      The error:

      InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 67108864 bytes!

      .. is common when the InnoDB log files differ. Generally what you’d need to do there is backup the existing log files, delete them, and then restart MySQL.

      You should have two, ib_logfile0 and ib_logfile1 – back them both up, delete them, and then restart.

      Reply Report
      • bashfeedbusiness March 10, 2017

        Any idea where those files may be located?

        I looked in /var/lib/mysql but the system says “no such file or directory.”

        Reply Report
        • hansen March 10, 2017

          @bashfeedbusiness
          That’s very, very weird. Since both your previous configuration and the one @jtittle supplied has datadir = /var/lib/mysql - meaning that should be the main directory for all the databases, indexes and InnoDB logs.
          If you run this command, does it say “No such file or directory” ?

          ls /var/lib/mysql
          Reply Report
jtittle1 March 9, 2017

@bashfeedbusiness

No problem at all, glad to help! Let me know how things go.

Reply Report
jtittle1 March 10, 2017

@bashfeedbusiness

Correct. Those files will be recreated once you start/restart MySQL and will then take on the size that is set in the newer configuration, which should prevent that error from popping back up.

If MySQL still fails to start, please tail -50 the log file and post back and we’ll take another look.

Reply Report
  • bashfeedbusiness March 10, 2017

    @hansen @jtittle

    Unfortunately I still get the Job Failed To Start error when I run service mysql restart

    I fear that I may have mucked up the renaming of the files. I used the following command while in the /var/lib/mysql directory:

    mv ib_logfile0 backupib_logfile0
mv ib_logfile1 backupib_logfile1

    But because it didn’t return any confirmation I tried it again with different file names, which I think may have been counterintuitive. Here’s what the ls returns now:

    backupiblogfile0   debian-5.5.flag  ib_logfile1         nicksap_wrdp7
backupib_logfile0  ibdata1          mysql               performance_schema
backupiblogfile1   ib_logfile0      mysql_upgrade_info  wordpress

    Here is the tail -50

    InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: for more information.
170310 15:15:15  InnoDB: Assertion failure in thread 140611009816320 in file fsp0fsp.c line 3544
InnoDB: Failing assertion: xdes_get_bit(descr, XDES_FREE_BIT, header_page % FSP_EXTENT_SIZE, mtr) == FALSE
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
20:15:15 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed, 
something is definitely wrong and this may fail.

key_buffer_size=268435456
read_buffer_size=262144
max_used_connections=0
max_threads=151
thread_count=0
connection_count=0
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 341196 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0 thread_stack 0x30000
/usr/sbin/mysqld(my_print_stacktrace+0x20)[0x7fe2b612cf20]
/usr/sbin/mysqld(handle_fatal_signal+0x3d5)[0x7fe2b60178d5]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x10330)[0x7fe2b4da9330]
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0x37)[0x7fe2b4400c37]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x148)[0x7fe2b4404028]
/usr/sbin/mysqld(+0x5fe2a7)[0x7fe2b623c2a7]
/usr/sbin/mysqld(+0x58c63b)[0x7fe2b61ca63b]
/usr/sbin/mysqld(+0x58d697)[0x7fe2b61cb697]
/usr/sbin/mysqld(+0x64d16f)[0x7fe2b628b16f]
/usr/sbin/mysqld(+0x643ab5)[0x7fe2b6281ab5]
/usr/sbin/mysqld(+0x58da45)[0x7fe2b61cba45]
/usr/sbin/mysqld(+0x583ca6)[0x7fe2b61c1ca6]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x8184)[0x7fe2b4da1184]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7fe2b44c437d]
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
    Reply Report
    • hansen March 10, 2017

      Ohh no. Using the mv command doesn’t give you any validation by default, but it will give an error if it fails.
      You shouldn’t be able to run the mv-command again on the same original files, since they’ve been moved. But I can still see the iblogfile0 and iblogfile1, so I don’t know if they were regenerated when you tried to start again or never removed.
      Can you run tail -100 /var/log/mysql/error.log since we need to see the lines before to figure out why it crashed. The -50 or -100 tells it to give the last 50 or 100 lines.

      Reply Report
      • bashfeedbusiness March 10, 2017

        Here’s the tail -100:

        InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: for more information.
170310 15:15:15  InnoDB: Error: page 2445 log sequence number 46424698356
InnoDB: is in the future! Current system log sequence number 46413796364.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: for more information.
170310 15:15:15  InnoDB: Error: page 4656 log sequence number 46425279887
InnoDB: is in the future! Current system log sequence number 46413796364.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: for more information.
170310 15:15:15  InnoDB: Error: page 4623 log sequence number 46424541647
InnoDB: is in the future! Current system log sequence number 46413796364.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: for more information.
170310 15:15:15  InnoDB: Error: page 3644 log sequence number 46424834995
InnoDB: is in the future! Current system log sequence number 46413796364.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: for more information.
170310 15:15:15  InnoDB: Error: page 4669 log sequence number 46425344726
InnoDB: is in the future! Current system log sequence number 46413796364.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: for more information.
170310 15:15:15  InnoDB: Error: page 1056 log sequence number 46424957935
InnoDB: is in the future! Current system log sequence number 46413796364.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: for more information.
170310 15:15:15  InnoDB: Error: page 569 log sequence number 46425302304
InnoDB: is in the future! Current system log sequence number 46413801053.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: for more information.
170310 15:15:15  InnoDB: Error: page 3615 log sequence number 46425302195
InnoDB: is in the future! Current system log sequence number 46413801159.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: for more information.
170310 15:15:15  InnoDB: Assertion failure in thread 140611009816320 in file fsp0fsp.c line 3544
InnoDB: Failing assertion: xdes_get_bit(descr, XDES_FREE_BIT, header_page % FSP_EXTENT_SIZE, mtr) == FALSE
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
20:15:15 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed, 
something is definitely wrong and this may fail.

key_buffer_size=268435456
read_buffer_size=262144
max_used_connections=0
max_threads=151
thread_count=0
connection_count=0
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 341196 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0 thread_stack 0x30000
/usr/sbin/mysqld(my_print_stacktrace+0x20)[0x7fe2b612cf20]
/usr/sbin/mysqld(handle_fatal_signal+0x3d5)[0x7fe2b60178d5]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x10330)[0x7fe2b4da9330]
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0x37)[0x7fe2b4400c37]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x148)[0x7fe2b4404028]
/usr/sbin/mysqld(+0x5fe2a7)[0x7fe2b623c2a7]
/usr/sbin/mysqld(+0x58c63b)[0x7fe2b61ca63b]
/usr/sbin/mysqld(+0x58d697)[0x7fe2b61cb697]
/usr/sbin/mysqld(+0x64d16f)[0x7fe2b628b16f]
/usr/sbin/mysqld(+0x643ab5)[0x7fe2b6281ab5]
/usr/sbin/mysqld(+0x58da45)[0x7fe2b61cba45]
/usr/sbin/mysqld(+0x583ca6)[0x7fe2b61c1ca6]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x8184)[0x7fe2b4da1184]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7fe2b44c437d]
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
        Reply Report
    • jtittle1 March 10, 2017

      @bashfeedbusiness

      Would it be possible for you to shoot me an e-mail @ jtittle [at] ninja.sh? (replace [at] with @ and remove the spaces)

      If you’d be willing to let me login and take a closer look, I’d be more than happy to do so, so that we’re not delaying the process and can work together to get you back up and running.

      This would be the easiest way so that we aren’t going back and forth, and so that I can have hands-on access.

      Reply Report
      • bashfeedbusiness March 10, 2017

        Wow, that would be amazing. Thank you. Just sent you an email.

        Reply Report
      • hansen March 10, 2017

        @jtittle Excellent idea. I would put it in recovery, dump to sql, clear all data and import sql. Can you write a little comment later how you did it?

        Reply Report
        • jtittle1 March 10, 2017

          @bashfeedbusiness @hansen

          At first, I thought recovery was what would be needed, though that wasn’t the case here. To start, I created a mirror backup of the MySQL data directory, then removed the InnoDB log files entirely (since I’d have a backup to copy back over if needed).

          Once the log files were removed, I removed the my.cnf file, copied over the same as I posted above and commented out the InnoDB-specific configuration changes.

          Once those two things were done, MySQL restarted without any issues.

          Since InnoDB can be finicky at times, especially when changing configuration after you’ve already got MySQL up and running, in this specific case, I felt it was best to simply comment out those changes.

          We could play with them a little more, but it’d be best to do this on a separate DB server, then import the database to it instead of testing it on a live server – mainly so we don’t run in to this again.

          Reply Report
        • jtittle1 March 10, 2017

          @bashfeedbusiness @hansen

          Status Update

          So what I ended up doing, since MySQL went down again shortly after my last comment, was starting up in recovery. It took setting it to 2 before it’d start.

          Once in recovery, a simple backup of all databases using mysqldump and then a single backup of the primary database for the website was needed before moving on to the next step: remove MySQL entirely (apt-get -y purge mysql-*).

          Once MySQL was purged, MariaDB was installed, the database was restored and we switched over to PHP5’s php5-mysqlnd package (since that’s the recommended package for MySQL on PHP5).

          Once a little bit of housekeeping was done to get everything setup in a more secure manor, things have been working well, though I’m still watching over the site to see if anything else happens to arise.

          Reply Report
Submit an Answer

Related

Looking for something else?

Ask a new question Search for more help