MySQL Database Crashes - Error Log Included

October 3, 2019 396 views
Databases LAMP Stack MySQL

Hello, I am running a Ubuntu LAMP on 18.04 (Size: 1 vCPUs/1GB / 25GB Disk)

I have a wordpress site installed with low traffic. I am noticing i cant login, and blog posts are not showing, i later found out the MySQL database is not running and have to restart.

Here is the output of the error log:

5 comments
  • Edit WYSWIG not working properly first time. Here is the log. - Please can you help me understand the problem and offer a potential solution?

    2019-10-03T08:27:43.272944Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
    2019-10-03T08:27:43.278306Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.27-0ubuntu0.18.04.1) starting as process 29423 ...
    2019-10-03T08:27:43.287454Z 0 [Note] InnoDB: PUNCH HOLE support available
    2019-10-03T08:27:43.287516Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
    2019-10-03T08:27:43.287526Z 0 [Note] InnoDB: Uses event mutexes
    2019-10-03T08:27:43.287532Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
    2019-10-03T08:27:43.287538Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
    2019-10-03T08:27:43.287551Z 0 [Note] InnoDB: Using Linux native AIO
    2019-10-03T08:27:43.289023Z 0 [Note] InnoDB: Number of pools: 1
    2019-10-03T08:27:43.292743Z 0 [Note] InnoDB: Using CPU crc32 instructions
    2019-10-03T08:27:43.302463Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
    2019-10-03T08:27:43.329478Z 0 [Note] InnoDB: Completed initialization of buffer pool
    2019-10-03T08:27:43.335706Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
    2019-10-03T08:27:43.362849Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
    2019-10-03T08:27:43.367962Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 1537785807
    2019-10-03T08:27:43.367996Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1537785816
    2019-10-03T08:27:43.368007Z 0 [Note] InnoDB: Database was not shutdown normally!
    2019-10-03T08:27:43.368014Z 0 [Note] InnoDB: Starting crash recovery.
    2019-10-03T08:27:43.600640Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
    2019-10-03T08:27:43.600673Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
    2019-10-03T08:27:43.600729Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
    2019-10-03T08:27:43.639300Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
    2019-10-03T08:27:43.640184Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
    2019-10-03T08:27:43.640199Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
    2019-10-03T08:27:43.640486Z 0 [Note] InnoDB: Waiting for purge to start
    2019-10-03T08:27:43.690730Z 0 [Note] InnoDB: 5.7.27 started; log sequence number 1537785816
    2019-10-03T08:27:43.691067Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
    2019-10-03T08:27:43.691835Z 0 [Note] Plugin 'FEDERATED' is disabled.
    2019-10-03T08:27:43.708188Z 0 [Note] InnoDB: Buffer pool(s) load completed at 191003  8:27:43
    2019-10-03T08:27:43.719272Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
    2019-10-03T08:27:43.719310Z 0 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
    2019-10-03T08:27:43.719337Z 0 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
    2019-10-03T08:27:43.719381Z 0 [Note] Server socket created on IP: '127.0.0.1'.
    2019-10-03T08:27:43.773424Z 0 [Note] Event Scheduler: Loaded 0 events
    2019-10-03T08:27:43.773645Z 0 [Note] /usr/sbin/mysqld: ready for connections.
    Version: '5.7.27-0ubuntu0.18.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
    2019-10-03T08:27:55.720780Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
    2019-10-03T08:27:55.723032Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.27-0ubuntu0.18.04.1) starting as process 29592 ...
    2019-10-03T08:27:55.729763Z 0 [Note] InnoDB: PUNCH HOLE support available
    2019-10-03T08:27:55.729792Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
    2019-10-03T08:27:55.729798Z 0 [Note] InnoDB: Uses event mutexes
    2019-10-03T08:27:55.729802Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
    2019-10-03T08:27:55.729807Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
    2019-10-03T08:27:55.729825Z 0 [Note] InnoDB: Using Linux native AIO
    2019-10-03T08:27:55.730837Z 0 [Note] InnoDB: Number of pools: 1
    2019-10-03T08:27:55.733847Z 0 [Note] InnoDB: Using CPU crc32 instructions
    2019-10-03T08:27:55.735998Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
    2019-10-03T08:27:55.747124Z 0 [Note] InnoDB: Completed initialization of buffer pool
    2019-10-03T08:27:55.750440Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
    2019-10-03T08:27:55.768968Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
    2019-10-03T08:27:55.772922Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 1537785959
    2019-10-03T08:27:55.772947Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1537785968
    2019-10-03T08:27:55.772957Z 0 [Note] InnoDB: Database was not shutdown normally!
    2019-10-03T08:27:55.772964Z 0 [Note] InnoDB: Starting crash recovery.
    2019-10-03T08:27:55.979445Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
    2019-10-03T08:27:55.979484Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
    2019-10-03T08:27:55.979533Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
    2019-10-03T08:27:56.006469Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
    2019-10-03T08:27:56.007436Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
    2019-10-03T08:27:56.007453Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
    2019-10-03T08:27:56.007763Z 0 [Note] InnoDB: Waiting for purge to start
    2019-10-03T08:27:56.058008Z 0 [Note] InnoDB: 5.7.27 started; log sequence number 1537785968
    2019-10-03T08:27:56.058298Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
    2019-10-03T08:27:56.059079Z 0 [Note] Plugin 'FEDERATED' is disabled.
    2019-10-03T08:27:56.069842Z 0 [Note] InnoDB: Buffer pool(s) load completed at 191003  8:27:56
    2019-10-03T08:27:56.073222Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
    2019-10-03T08:27:56.073242Z 0 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
    2019-10-03T08:27:56.073254Z 0 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
    2019-10-03T08:27:56.073288Z 0 [Note] Server socket created on IP: '127.0.0.1'.
    
  • This is the error log after i restart MySQL - should i pull the log before, to understand why it crashed?

  • Hi @silentcolours,

    Yes please pull the log before MySQL crashes to see what the issue is.

    Additionally, please check for kill and oom commands in /var/log/messages.

    Kind regards,
    Kalin

  • Database Error again:

    sudo less /var/log/mysql/error.log returns this:

    2019-10-03T09:54:27.108417Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
    2019-10-03T09:54:27.110655Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.27-0ubuntu0.18.04.1) starting as process 31223 ...
    2019-10-03T09:54:27.126102Z 0 [Note] InnoDB: PUNCH HOLE support available
    2019-10-03T09:54:27.126147Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
    2019-10-03T09:54:27.126152Z 0 [Note] InnoDB: Uses event mutexes
    2019-10-03T09:54:27.126156Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
    2019-10-03T09:54:27.126161Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
    2019-10-03T09:54:27.126170Z 0 [Note] InnoDB: Using Linux native AIO
    2019-10-03T09:54:27.127201Z 0 [Note] InnoDB: Number of pools: 1
    2019-10-03T09:54:27.130288Z 0 [Note] InnoDB: Using CPU crc32 instructions
    2019-10-03T09:54:27.132650Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
    2019-10-03T09:54:27.133678Z 0 [ERROR] InnoDB: mmap(137428992 bytes) failed; errno 12
    2019-10-03T09:54:27.133701Z 0 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
    2019-10-03T09:54:27.133708Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
    2019-10-03T09:54:27.133717Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
    2019-10-03T09:54:27.133723Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
    2019-10-03T09:54:27.133730Z 0 [ERROR] Failed to initialize builtin plugins.
    2019-10-03T09:54:27.133744Z 0 [ERROR] Aborting
    
    2019-10-03T09:54:27.138179Z 0 [Note] Binlog end
    2019-10-03T09:54:27.138253Z 0 [Note] Shutting down plugin 'CSV'
    2019-10-03T09:54:27.145563Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
    
  • Based on the above log, i upgrade the droplet to the 2GB Ram.

    Is this a short term fix? is there something else i can/should change?

2 Answers

Hi @silentcolours,

Yes it seems an upgrade was needed.

Additionally, I’ll recommend you to enable slow query log and see if there are any queries taking a lot of time to be executed. Most probably they would be the reason behind the High Memory Usage.

Kind regards,
Kalin

Have another answer? Share your knowledge.