MySQL Version: Ver 8.0.22-0ubuntu0.20.04.2 for Linux on x86_64 ((Ubuntu))
Today I encountered that my MySQL stopped running, all my websites are offline.
When I try to run manually I get the following by systemctl start mysql.service:
Job for mysql.service failed because the control process exited with error code.
See "systemctl status mysql.service" and "journalctl -xe" for details.
I found out that the disk storage of my VPS was full (mainly because binlogs in /var/lib/mysql), and this error I saw also in the Error log (
sudo less /var/log/mysql/error.log):
2020-11-02T10:30:52.599634Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.22-0ubuntu0.20.04.2) (Ubuntu).
2020-11-02T10:30:53.390592Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.22-0ubuntu0.20.04.2) starting as process 2940
2020-11-02T10:30:53.404968Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-11-02T10:30:53.690848Z 1 [Warning] [MY-012637] [InnoDB] 1048576 bytes should have been written. Only 192512 bytes written. Retrying for the remaining bytes.
2020-11-02T10:30:53.691043Z 1 [Warning] [MY-012638] [InnoDB] Retry attempts for writing partial data failed.
2020-11-02T10:30:53.691148Z 1 [ERROR] [MY-012639] [InnoDB] Write to file ./ib_logfile1 failed at offset 16777216, 1048576 bytes should have been written, only 192512 were written. Operating system error number 28. Check that your OS and file system support files of this size. Check also that the disk is not full or a disk quota exceeded.
2020-11-02T10:30:53.691259Z 1 [ERROR] [MY-012640] [InnoDB] Error number 28 means 'No space left on device'
2020-11-02T10:30:53.691476Z 1 [ERROR] [MY-012888] [InnoDB] Cannot set log file ./ib_logfile1 to size 48 MB
2020-11-02T10:30:53.695198Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Generic error.
2020-11-02T10:30:54.066954Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2020-11-02T10:30:54.067499Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2020-11-02T10:30:54.067874Z 0 [ERROR] [MY-010119] [Server] Aborting
So I made space without touching anything regarding MySQL. Now I have made enough Space (661MB Free out of 20GB). I get this error:
2020-11-02T13:32:39.421602Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.22-0ubuntu0.20.04.2) starting as process 12126
2020-11-02T13:32:39.449551Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-11-02T13:32:40.061661Z 1 [ERROR] [MY-011971] [InnoDB] Tablespace 'innodb_system' Page [page id: space=0, page number=5] log sequence number 45141840474 is in the future! Current system log sequence number 43132906578.
2020-11-02T13:32:40.061971Z 1 [ERROR] [MY-011972] [InnoDB] Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html for information about forcing recovery.
...
2020-11-02T13:40:36.582575Z 1 [ERROR] [MY-011971] [InnoDB] Tablespace 'mysql' Page [page id: space=4294967294, page number=1493] log sequence number 44244348592 is in the future! Current system log sequence number 43132906578.
2020-11-02T13:40:36.592232Z 1 [ERROR] [MY-011972] [InnoDB] Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-reco:
2020-11-02T15:51:40.766981Z 1 [Warning] [MY-010005] [Server] Skip re-populating collations and character sets tables in InnoDB read-only mode.
2020-11-02T15:51:40.824752Z 0 [ERROR] [MY-011971] [InnoDB] Tablespace 'mysql' Page [page id: space=4294967294, page number=2050] log sequence number 44206240401 is in the future! Current system log sequence number 43132916576.
2020-11-02T15:51:40.843844Z 0 [ERROR] [MY-011972] [InnoDB] Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html for information about forcing recovery.
2020-11-02T15:51:40.920218Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2020-11-02T15:51:40.935848Z 2 [Warning] [MY-011018] [Server] Skip updating information_schema metadata in InnoDB read-only mode.
2020-11-02T15:51:41.003229Z 0 [Warning] [MY-010970] [Server] Skipped updating resource group metadata in InnoDB read only mode.
2020-11-02T15:51:41.013832Z 0 [Warning] [MY-010970] [Server] Skipped updating resource group metadata in InnoDB read only mode.
2020-11-02T15:51:41.273917Z 0 [System] [MY-010229] [Server] Starting XA crash recovery...
2020-11-02T15:51:41.364347Z 0 [ERROR] [MY-013183] [InnoDB] Assertion failure: trx0trx.cc:1778:trx_sys->n_prepared_trx > 0 thread 140417917017216
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/8.0/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
15:51:41 UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
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 0x46000
/usr/sbin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x41) [0x557d3b8a4871]
/usr/sbin/mysqld(handle_fatal_signal+0x31b) [0x557d3a7094cb]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x153c0) [0x7fb598af13c0]
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0xcb) [0x7fb59814f18b]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x12b) [0x7fb59812e859]
/usr/sbin/mysqld(+0xe7a612) [0x557d3a46d612]
/usr/sbin/mysqld(+0x25be9f8) [0x557d3bbb19f8]
/usr/sbin/mysqld(trx_commit_low(trx_t*, mtr_t*)+0x308) [0x557d3bbb3108]
/usr/sbin/mysqld(trx_commit(trx_t*)+0x12b) [0x557d3bbb37db]
/usr/sbin/mysqld(trx_commit_for_mysql(trx_t*)+0x317) [0x557d3bbb50e7]
/usr/sbin/mysqld(innobase_commit_low(trx_t*)+0x1b) [0x557d3b971f2b]
/usr/sbin/mysqld(+0x23a07e2) [0x557d3b9937e2]
/usr/sbin/mysqld(+0x10ec8e4) [0x557d3a6df8e4]
/usr/sbin/mysqld(plugin_foreach_with_mask(THD*, bool (**)(THD*, st_plugin_int*, void*), int, unsigned int, void*)+0x235) [0x557d3a5e84a5]
/usr/sbin/mysqld(plugin_foreach_with_mask(THD*, bool (*)(THD*, st_plugin_int*, void*), int, unsigned int, void*)+0x30) [0x557d3a5e8730]
/usr/sbin/mysqld(ha_recover(mem_root_unordered_set<unsigned long long, std::hash<unsigned long long>, std::equal_to<unsigned long long> > const*)+0x253) [0x557d3a6dfb93]
/usr/sbin/mysqld(MYSQL_BIN_LOG::open_binlog(char const*)+0x1769) [0x557d3b507b99]
/usr/sbin/mysqld(+0xeee257) [0x557d3a4e1257]
/usr/sbin/mysqld(mysqld_main(int, char**)+0x1c6c) [0x557d3a4e78dc]
/lib/x86_64-linux-gnu/libc.so.6(__libc_start_main+0xf3) [0x7fb5981300b3]
/usr/sbin/mysqld(_start+0x2e) [0x557d3a4cbdee]
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.
... = continues like this
Any Idea how to start MySQL like it was before the crash (due to having not enough disk space)?
Update: I see on the website that I need to assert this to 1. [mysqld] innodb_force_recovery = 1 Where can I find this, and how to set it to 1?
Oh and another important question, how to disable BinLog since when I look into nano /etc/mysql/mysql.conf.d I see everything regarding binlog already commented except that the size of them is max 100M, but it keeps making these files.
