MariaDB 10.1 crashing repeatedly

Too much time today has been spent looking over a lightly loaded MariaDB instance that was crashing and restart repeatedly. Low load, low user, low database, low use. New build, good health, SSD’s, hardware RAID, configuration from Percona’s Wizard, and Ubuntu 14.04.1 LTS.

Looking over the mysql error log the parts that were getting the most of my attention were:

[ERROR] mysqld got signal 11 ;

…which is where it all stops being fun.

Then the worrying advisory (which is actually saying – “you know what – its all good in the ‘hood“) and is completely a wrong steer:

Server version: 10.1.9-MariaDB-1~trusty-log
key_buffer_size=33554432
read_buffer_size=131072
max_used_connections=0
max_threads=502
thread_count=0
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads
= 1135351 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Then we move on to theĀ  far more hard core and meaningless (in terms of a worthy Google)…

Thread pointer: 0x0x0
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 = 0x0 thread_stack 0x48000
/usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x7f6267d3a5ae]
/usr/sbin/mysqld(handle_fatal_signal+0x38d)[0x7f626786a47d]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x10340)[0x7f6265dc8340]
/usr/sbin/mysqld(+0x8df8ea)[0x7f6267ba08ea]
/usr/sbin/mysqld(+0x8ae12d)[0x7f6267b6f12d]
/usr/sbin/mysqld(+0x875487)[0x7f6267b36487]
/usr/sbin/mysqld(+0x8dc2fe)[0x7f6267b9d2fe]
/usr/sbin/mysqld(+0x8ca1f0)[0x7f6267b8b1f0]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x8182)[0x7f6265dc0182]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f62654e347d]
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.
151126 16:32:58 mysqld_safe Number of processes running now: 0
151126 16:32:58 mysqld_safe mysqld restarted

None of which appeared to be making much of a difference.

Reducing the size of the InnoDB buffers allowed it to restart faster… to the extent that down from 12G to 256M it was generating logging so quickly as it cycled booting fast enough to make me believe that free energy was possible.

The only other part that was sticking out at me was a [notice] – you know like “I am starting with this option” or “Hi I am this version” or “option B is enabled“…. you know “notices“.

[Note] InnoDB: The log sequence numbers 1645491212 and 1645491212
in ibdata files do not match the log sequence number 1645519434
in the ib_logfiles!

There we are. Innocuous enough within this sea of logging. However this was the entire cause of the issue.

For the sake of making this findable in future – lets narrow this down to:

ibdata files do not match the log sequence number

So – for all the searching of logs, internet, soul… the solution was straight forward – I added the following to my my.cnf file…

innodb_force_recovery = 6

…under the InnoDB section that otherwise read as follows:

# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 256M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 12G
innodb_force_recovery = 6

Allowing the server to restart – and that was it – done – sorted – back to business as usual.

Three words and a number and my life is a much better place – apparently as notices go that is vital – and it wont fix it until you ask it to try REALLY hard.

You learn something every day.

2 Responses to “MariaDB 10.1 crashing repeatedly

  • A word to the wise:

    Through much wailing and gnashing of teeth I have learnt another thing today.

    ERROR 1036 (HY000) at line 30: Table ‘BlahBlahBlah’ is read only

    SO, here we are. Read only tables, and no matter what you do regarding checking permissions on physical files, repairs, settings to ensure the default is not read only – the innodb tables are stuck RO.

    You start thinking about deleting folders and rebuilding them from backups – that will work right? But you cannot delete them – because they are properly irrevocably RO.

    nnnnnnNNNNNNNAAAAAARGH!

    It transpires that keeping the:

    innodb_force_recovery = 6

    … or more specifically anything above a 4 (I believe – I had better check that) will result in innodb tables being read only.

    You live – you learn*.

    * and you get a little greyer in the process every day.

  • Regretfully before too long I was back to square one.

    Cycling booting restarting.

    Nice.

    RO tables, and a fix works in terms of RO tables, and then back to looping. Nice.

    *SO* – dumped the whole lot to file.

    Purged MariaDB 10.1.

    Deleted the folder structure.

    Installed MariaDB 10.0

    Reimported the data forcing it all to InnoDB format (previously some MYI).

    Problem goes away.

    “Great Success”

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this:
Skip to toolbar