Solved Where are MariaDB log files

fred974

Daemon

Reaction score: 37
Messages: 1,588

Hi,

Today I lost al my website and I believe that the issue is the the MariaDB jail server as the message I had was
Code:
error establishing a database connection
I have 10 wordpress website using that single database.

Could anyone please tell me what log I need to look at in order to understand what happen to my system?

I looked /var/log/messages and found no issue.
In /var/db/mysql i have a lot of files but not sure where to start
mariadb:/var/db/mysql@[13:30] # ll
Code:
total 691759
drwx------  2 mysql  mysql         145 May  2 09:44 abs_staging/
-rw-rw----  1 mysql  mysql      598016 Jun 14 13:05 aria_log.00000001
-rw-rw----  1 mysql  mysql          52 Jun 14 13:05 aria_log_control
drwx------  2 mysql  mysql         344 Jun  5 23:52 bol_staging/
drwx------  2 mysql  mysql         392 Jun 14 13:30 bollxxx_live/
drwx------  2 mysql  mysql          65 Mar 12  2018 caa_live/
drwx------  2 mysql  mysql          65 Jun  7 13:44 chn_staging/
drwx------  2 mysql  mysql         369 May 29 15:33 dur_live/
drwx------  2 mysql  mysql         349 Sep 20  2018 dur_staging/
drwx------  2 mysql  mysql         265 Jun 13 15:05 equ_live/
drwx------  2 mysql  mysql         265 Jun  5 08:37 equ_staging/
drwx------  2 mysql  mysql           3 Jun  1  2017 fot_staging/
-rw-rw----  1 mysql  mysql    50331648 Feb 23  2017 ib_logfile0
-rw-rw----  1 mysql  mysql    50331648 Feb 23  2017 ib_logfile1
-rw-rw----  1 mysql  mysql    12582912 Feb 23  2017 ibdata1
drwx------  2 mysql  mysql         197 Jun 13 20:35 lcc_live/
drwx------  2 mysql  mysql         163 Jan 28 13:31 mainwp_live/
-rw-rw----  1 mysql  mysql   586149864 Jun 14 13:29 mariadb.trixxx.co.uk.err
-rw-rw----  1 mysql  mysql           6 Jun 14 12:42 mariadb.trinixxx.co.uk.pid
drwx------  2 mysql  mysql         102 Feb 15  2018 mma_hesk/
drwx------  2 mysql  mysql         290 Jun 13 15:12 mma_live/
drwx------  2 mysql  mysql         252 Nov 10  2017 mma_staging/
-rw-rw----  1 mysql  mysql           0 Feb 23  2017 multi-master.info
drwx------  2 mysql  mysql           3 May 23 22:04 mwa_staging/
-rw-r--r--  1 mysql  mysql         394 Mar 13  2017 my.cnf
drwx------  2 mysql  mysql          89 Jan 13  2018 mysql/
-rw-rw----  1 mysql  mysql  1073747239 Jun 11 22:15 mysql-bin.000474
-rw-rw----  1 mysql  mysql  1073752721 Jun 13 04:08 mysql-bin.000475
-rw-rw----  1 mysql  mysql   454793857 Jun 13 14:32 mysql-bin.000476
-rw-rw----  1 mysql  mysql   793776258 Jun 14 13:24 mysql-bin.000477
-rw-rw----  1 mysql  mysql     3414750 Jun 14 12:35 mysql-bin.000478
-rw-rw----  1 mysql  mysql    36784898 Jun 14 13:30 mysql-bin.000479
-rw-rw----  1 mysql  mysql         114 Jun 14 12:42 mysql-bin.index
-rw-------  1 mysql  mysql  1046515712 Feb 20 23:25 mysqld.core
drwx------  2 mysql  mysql         962 May 28 13:25 naturexxx/
drwx------  2 mysql  mysql           3 Jan 13  2018 performance_schema/
drwx------  2 mysql  mysql          27 Mar 27  2018 rdd_live/
drwx------  2 mysql  mysql          25 Oct  4  2018 seafile_ccnet/
drwx------  2 mysql  mysql          51 Oct  4  2018 seafile_live/
drwx------  2 mysql  mysql         133 Jun 14  2018 seafile_seahub/
drwx------  2 mysql  mysql         283 Jun  8 12:29 sfk_live/
drwx------  2 mysql  mysql         211 Jun  6  2018 sfk_staging/
drwx------  2 mysql  mysql         491 Jun 14 13:23 sfl_live/
drwx------  2 mysql  mysql         199 Mar 26  2018 sfl_staging/
drwx------  2 mysql  mysql         187 Jun 13 23:02 tri_live/
drwx------  2 mysql  mysql         488 Apr 11  2017 trinibarr/
drwx------  2 mysql  mysql         102 Apr 11  2017 trinihesk/
drwx------  2 mysql  mysql         411 May 12  2017 trinitest/
drwx------  2 mysql  mysql        1517 Feb 22  2018 triniyeti/
Thank you in advance
 

SirDice

Administrator
Staff member
Administrator
Moderator

Reaction score: 7,886
Messages: 31,157

The default log file is /var/db/mysql/`hostname -f`.err.
 
OP
OP
fred974

fred974

Daemon

Reaction score: 37
Messages: 1,588

This is the log aorund the time of the crash
Code:
2019-06-14 11:48:22 34947827200 [Note] Suppressing warnings of type 'The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted.' for up to 300 seconds because of flooding
190614 12:26:01 mysqld_safe Starting mysqld daemon with databases from /var/db/mysql
2019-06-14 12:26:02 34422751232 [Note] /usr/local/libexec/mysqld (mysqld 10.1.35-MariaDB) starting as process 84009 ...
2019-06-14 12:26:03 34422751232 [Note] InnoDB: innodb_empty_free_list_algorithm has been changed to legacy because of small buffer pool size. In order to use backoff, increase buffer pool at least up to 20MB.

2019-06-14 12:26:03 34422751232 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2019-06-14 12:26:03 34422751232 [Note] InnoDB: The InnoDB memory heap is disabled
2019-06-14 12:26:03 34422751232 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-06-14 12:26:03 34422751232 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2019-06-14 12:26:03 34422751232 [Note] InnoDB: Compressed tables use zlib 1.2.11
2019-06-14 12:26:03 34422751232 [Note] InnoDB: Using SSE crc32 instructions
2019-06-14 12:26:03 34422751232 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2019-06-14 12:26:03 34422751232 [Note] InnoDB: Completed initialization of buffer pool
2019-06-14 12:26:03 34422751232 [Note] InnoDB: Highest supported file format is Barracuda.
2019-06-14 12:26:03 34422751232 [Note] InnoDB: Starting crash recovery from checkpoint LSN=371199792870
2019-06-14 12:26:19 34422751232 [Note] InnoDB: Processed 2189 .ibd/.isl files
2019-06-14 12:26:35 34422751232 [Note] InnoDB: Processed 3850 .ibd/.isl files
2019-06-14 12:26:36 34422751232 [Note] InnoDB: Restoring possible half-written data pages from the doublewrite buffer...
2019-06-14 12:26:37 34422751232 [Note] InnoDB: Read redo log up to LSN=371199923712
2019-06-14 12:26:37 34422751232 [Note] InnoDB: Starting final batch to recover 54 pages from redo log
InnoDB: Last MySQL binlog file position 0 793748930, file name ./mysql-bin.000477
2019-06-14 12:26:38 34422751232 [Note] InnoDB: 128 rollback segment(s) are active.
2019-06-14 12:26:38 34422751232 [Note] InnoDB: Waiting for purge to start
2019-06-14 12:26:38 34422751232 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.39-83.1 started; log sequence number 371199889554
2019-06-14 12:26:39 34937568256 [Note] InnoDB: Dumping buffer pool(s) not yet started
2019-06-14 12:26:39 34422751232 [Note] Plugin 'FEEDBACK' is disabled.
2019-06-14 12:26:39 34422751232 [Note] Recovering after a crash using mysql-bin
2019-06-14 12:26:52 34422751232 [Note] Starting crash recovery...
2019-06-14 12:26:52 34422751232 [Note] Crash recovery finished.
2019-06-14 12:26:52 34937570816 [Warning] InnoDB: Cannot open table mysql/gtid_slave_pos from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
2019-06-14 12:26:52 34937570816 [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1932: Table 'mysql.gtid_slave_pos' doesn't exist in engine
2019-06-14 12:26:52 34422751232 [Note] Server socket created on IP: '0.0.0.0'.
2019-06-14 12:26:52 34919731200 [Warning] InnoDB: Cannot open table mysql/gtid_slave_pos from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
2019-06-14 12:26:52 34919731200 [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1932: Table 'mysql.gtid_slave_pos' doesn't exist in engine
2019-06-14 12:26:53 34422751232 [Note] /usr/local/libexec/mysqld: ready for connections.
Version: '10.1.35-MariaDB'  socket: '/tmp/mysql.sock'  port: 3306  FreeBSD Ports
2019-06-14 12:26:53 34919744000 [Warning] IP address '10.44.1.17' could not be resolved: hostname nor servname provided, or not known
2019-06-14 12:26:53 821610200 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2019-06-14 12:26:56 34919747840 [Warning] IP address '10.44.1.19' could not be resolved: hostname nor servname provided, or not known
2019-06-14 12:27:10 34920638464 [Warning] IP address '10.44.1.16' could not be resolved: hostname nor servname provided, or not known
2019-06-14 12:27:10 34920642304 [Warning] IP address '10.44.1.24' could not be resolved: hostname nor servname provided, or not known
2019-06-14 12:27:35 34920644864 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE equ_posts SET post_password = '49bc8767c29283e1ff89', post_modified_gmt = '2019-06-14 12:27:35', post_modified = '2019-06-14 12:27:35' WHERE post_type = 'scheduled-action' AND post_status = 'pending' AND post_password = '' AND post_date_gmt <= '2019-06-14 12:27:35' ORDER BY menu_order ASC, post_date_gmt ASC, ID ASC LIMIT 25
2019-06-14 12:27:36 34939028736 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE sfk_posts SET post_password = '22e198294124210ec5c7', post_modified_gmt = '2019-06-14 12:27:36', post_modified = '2019-06-14 12:27:36' WHERE post_type = 'scheduled-action' AND post_status = 'pending' AND post_password = '' AND post_date_gmt <= '2019-06-14 12:27:36' ORDER BY menu_order ASC, post_date_gmt ASC, ID ASC LIMIT 25
2019-06-14 12:29:14 34920644864 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE equ_posts SET post_password = 'c438b9fb6876df8facad', post_modified_gmt = '2019-06-14 12:29:14', post_modified = '2019-06-14 12:29:14' WHERE post_type = 'scheduled-action' AND post_status = 'pending' AND post_password = '' AND post_date_gmt <= '2019-06-14 12:29:14' ORDER BY menu_order ASC, post_date_gmt ASC, ID ASC LIMIT 25
2019-06-14 12:29:49 34742540288 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE sfk_posts SET post_password = '91c0b7450542f503f821', post_modified_gmt = '2019-06-14 12:29:49', post_modified = '2019-06-14 12:29:49' WHERE post_type = 'scheduled-action' AND post_status = 'pending' AND post_password = '' AND post_date_gmt <= '2019-06-14 12:29:49' ORDER BY menu_order ASC, post_date_gmt ASC, ID ASC LIMIT 25
2019-06-14 12:30:34 34939032576 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE equ_posts SET post_password = 'e0e138f742abf66c79dd', post_modified_gmt = '2019-06-14 12:30:34', post_modified = '2019-06-14 12:30:34' WHERE post_type = 'scheduled-action' AND post_status = 'pending' AND post_password = '' AND post_date_gmt <= '2019-06-14 12:30:34' ORDER BY menu_order ASC, post_date_gmt ASC, ID ASC LIMIT 25
2019-06-14 12:30:36 34939028736 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE sfk_posts SET post_password = '3d664c18b45aca82147c', post_modified_gmt = '2019-06-14 12:30:36', post_modified = '2019-06-14 12:30:36' WHERE post_type = 'scheduled-action' AND post_status = 'pending' AND post_password = '' AND post_date_gmt <= '2019-06-14 12:30:36' ORDER BY menu_order ASC, post_date_gmt ASC, ID ASC LIMIT 25
2019-06-14 12:31:37 34921200384 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE sfk_posts SET post_password = 'c9b0fe702bb497bb3c56', post_modified_gmt = '2019-06-14 12:31:37', post_modified = '2019-06-14 12:31:37' WHERE post_type = 'scheduled-action' AND post_status = 'pending' AND post_password = '' AND post_date_gmt <= '2019-06-14 12:31:37' ORDER BY menu_order ASC, post_date_gmt ASC, ID ASC LIMIT 25
2019-06-14 12:31:54 34921196544 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE equ_posts SET post_password = 'abe59a15d694a582c4d6', post_modified_gmt = '2019-06-14 12:31:54', post_modified = '2019-06-14 12:31:54' WHERE post_type = 'scheduled-action' AND post_status = 'pending' AND post_password = '' AND post_date_gmt <= '2019-06-14 12:31:54' ORDER BY menu_order ASC, post_date_gmt ASC, ID ASC LIMIT 25
2019-06-14 12:32:12 34920646144 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave. Statement: DELETE FROM sfk_wps_key WHERE key_id NOT IN (SELECT key_id FROM sfk_wps_index)
2019-06-14 12:32:33 34939470848 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE sfk_posts SET post_password = 'b1e2be118f35e08135d3', post_modified_gmt = '2019-06-14 12:32:33', post_modified = '2019-06-14 12:32:33' WHERE post_type = 'scheduled-action' AND post_status = 'pending' AND post_password = '' AND post_date_gmt <= '2019-06-14 12:32:33' ORDER BY menu_order ASC, post_date_gmt ASC, ID ASC LIMIT 25
2019-06-14 12:33:02 34921196544 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE equ_posts SET post_password = 'b4723ba64ab27b4d9d3d', post_modified_gmt = '2019-06-14 12:33:02', post_modified = '2019-06-14 12:33:02' WHERE post_type = 'scheduled-action' AND post_status = 'pending' AND post_password = '' AND post_date_gmt <= '2019-06-14 12:33:02' ORDER BY menu_order ASC, post_date_gmt ASC, ID ASC LIMIT 25
2019-06-14 12:33:30 34920644864 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE sfk_posts SET post_password = 'b750c4fbb66e4d053f1c', post_modified_gmt = '2019-06-14 12:33:30', post_modified = '2019-06-14 12:33:30' WHERE post_type = 'scheduled-action' AND post_status = 'pending' AND post_password = '' AND post_date_gmt <= '2019-06-14 12:33:30' ORDER BY menu_order ASC, post_date_gmt ASC, ID ASC LIMIT 25
2019-06-14 12:34:34 34920644864 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE equ_posts SET post_password = '4f4afefd85bc00545f94', post_modified_gmt = '2019-06-14 12:34:34', post_modified = '2019-06-14 12:34:34' WHERE post_type = 'scheduled-action' AND post_status = 'pending' AND post_password = '' AND post_date_gmt <= '2019-06-14 12:34:34' ORDER BY menu_order ASC, post_date_gmt ASC, ID ASC LIMIT 25
2019-06-14 12:34:39 34921605632 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: UPDATE sfk_posts SET post_password = 'f8d10dcea2d271308c1d', post_modified_gmt = '2019-06-14 12:34:39', post_modified = '2019-06-14 12:34:39' WHERE post_type = 'scheduled-action' AND post_status = 'pending' AND post_password = '' AND post_date_gmt <= '2019-06-14 12:34:39' ORDER BY menu_order ASC, post_date_gmt ASC, ID ASC LIMIT 25
2019-06-14 12:35:46 34920644864 [Note] /usr/local/libexec/mysqld: Normal shutdown
2019-06-14 12:35:46 34920644864 [Note] Event Scheduler: Purging the queue. 0 events
2019-06-14 12:35:46 34937563136 [Note] InnoDB: FTS optimize thread exiting.
2019-06-14 12:35:46 34920644864 [Note] InnoDB: Starting shutdown...
2019-06-14 12:35:48 34920644864 [Note] InnoDB: Waiting for page_cleaner to finish flushing of buffer pool
2019-06-14 12:35:50 34920644864 [Note] InnoDB: Shutdown completed; log sequence number 371202803765
2019-06-14 12:35:50 34920644864 [Note] /usr/local/libexec/mysqld: Shutdown complete

190614 12:35:50 mysqld_safe mysqld from pid file /var/db/mysql/mariadb.trinitech.co.uk.pid ended
190614 12:42:01 mysqld_safe Starting mysqld daemon with databases from /var/db/mysql
2019-06-14 12:42:01 34422751232 [Note] /usr/local/libexec/mysqld (mysqld 10.1.35-MariaDB) starting as process 24344 ...
2019-06-14 12:42:01 34422751232 [Note] InnoDB: innodb_empty_free_list_algorithm has been changed to legacy because of small buffer pool size. In order to use backoff, increase buffer pool at least up to 20MB.

2019-06-14 12:42:01 34422751232 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2019-06-14 12:42:01 34422751232 [Note] InnoDB: The InnoDB memory heap is disabled
2019-06-14 12:42:01 34422751232 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-06-14 12:42:01 34422751232 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2019-06-14 12:42:01 34422751232 [Note] InnoDB: Compressed tables use zlib 1.2.11
2019-06-14 12:42:01 34422751232 [Note] InnoDB: Using SSE crc32 instructions
2019-06-14 12:42:01 34422751232 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2019-06-14 12:42:01 34422751232 [Note] InnoDB: Completed initialization of buffer pool
2019-06-14 12:42:01 34422751232 [Note] InnoDB: Highest supported file format is Barracuda.
2019-06-14 12:42:03 34422751232 [Note] InnoDB: 128 rollback segment(s) are active.
2019-06-14 12:42:03 34422751232 [Note] InnoDB: Waiting for purge to start
2019-06-14 12:42:03 34422751232 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.39-83.1 started; log sequence number 371202803765
2019-06-14 12:42:03 34930345728 [Note] InnoDB: Dumping buffer pool(s) not yet started
2019-06-14 12:42:03 34422751232 [Note] Plugin 'FEEDBACK' is disabled.
2019-06-14 12:42:03 34930348288 [Warning] InnoDB: Cannot open table mysql/gtid_slave_pos from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
2019-06-14 12:42:03 34930348288 [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1932: Table 'mysql.gtid_slave_pos' doesn't exist in engine
2019-06-14 12:42:03 34422751232 [Note] Server socket created on IP: '0.0.0.0'.
2019-06-14 12:42:04 34930350848 [Warning] InnoDB: Cannot open table mysql/gtid_slave_pos from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
2019-06-14 12:42:04 34930350848 [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1932: Table 'mysql.gtid_slave_pos' doesn't exist in engine
2019-06-14 12:42:04 34422751232 [Note] /usr/local/libexec/mysqld: ready for connections.
Version: '10.1.35-MariaDB'  socket: '/tmp/mysql.sock'  port: 3306  FreeBSD Ports
2019-06-14 12:42:19 34745351424 [Warning] IP address '10.44.1.19' could not be resolved: hostname nor servname provided, or not known
2019-06-14 12:42:19 816fbfd00 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2019-06-14 12:42:26 34948297472 [Warning] IP address '10.44.1.17' could not be resolved: hostname nor servname provided, or not known
I have next to 0 database skills. Could anyone please assist me in understanding why the DB crashed?


Thank you
 

SirDice

Administrator
Staff member
Administrator
Moderator

Reaction score: 7,886
Messages: 31,157

Could anyone please assist me in understanding why the DB crashed?
I can't tell from this bit of log. I can see it's trying to recover but it has some problems with the mysql database. It may have gotten corrupted due to the crash. Try running a CHECK TABLE on mysql.gtid_slave_pos and mysql.innodb_table_stats.

Also remove the mysqld.core it's taking up quite a bit of space, move mariadb.trinitech.co.uk.err out of the way too, it's rather large. If you (re)move the old log restart MySQL to have it create a fresh new one.
 
OP
OP
fred974

fred974

Daemon

Reaction score: 37
Messages: 1,588

CHECK TABLE mysql.gtid_slave_pos;
Code:
+----------------------+-------+----------+------------------------------------------------------+
| Table                | Op    | Msg_type | Msg_text                                             |
+----------------------+-------+----------+------------------------------------------------------+
| mysql.gtid_slave_pos | check | Error    | Table 'mysql.gtid_slave_pos' doesn't exist in engine |
| mysql.gtid_slave_pos | check | status   | Operation failed                                     |
+----------------------+-------+----------+------------------------------------------------------+
2 rows in set (0.00 sec)
CHECK TABLE mysql.innodb_table_stats;
Code:
+--------------------------+-------+----------+----------------------------------------------------------+
| Table                    | Op    | Msg_type | Msg_text                                                 |
+--------------------------+-------+----------+----------------------------------------------------------+
| mysql.innodb_table_stats | check | Error    | Table 'mysql.innodb_table_stats' doesn't exist in engine |
| mysql.innodb_table_stats | check | status   | Operation failed                                         |
+--------------------------+-------+----------+----------------------------------------------------------+
2 rows in set (0.00 sec)
SirDice ,do i need to shutdown MariaDB before i delete the *mysqld.core ?

/var/db/mysql/my.cnf
Code:
[mysqld]
log-bin=mysql-bin
expire-logs-days=3
sync-binlog=1

# DATA STORAGE #
innodb_data_home_dir = /var/db/mysql-innodb
innodb_log_group_home_dir = /var/db/mysql-innodb-logs
# INNODB #
innodb-flush-method            = O_DIRECT

# UTF-8 encoding settings #
collation-server               = utf8_unicode_ci
init-connect                   ='SET NAMES utf8'
character-set-server           = utf8
 

SirDice

Administrator
Staff member
Administrator
Moderator

Reaction score: 7,886
Messages: 31,157

Right, so your tables have completely disappeared. Do you use replication? If not then gtid_slave_pos isn't that important. Same for innodb_table_stats. But this did give me a clue, is this an upgrade from 5.5 to 5.6? Did you run mysql_upgrade(1)? Try running that regardless, with some luck it'll re-create those tables.

do i need to shutdown MariaDB before i delete the *mysqld.core ?
No, that's not necessary. It's a core(5) dump of the crashed process. You can remove the file without issues.
 
OP
OP
fred974

fred974

Daemon

Reaction score: 37
Messages: 1,588

I cannot remember the last update I ran :(
The database server is mariadb101-server-10.1.35_1
here is what i get when i run mysql_upgrade(1)
Code:
Phase 1/7: Checking and upgrading mysql database
Processing databases
mysql
mysql.column_stats                                 OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.gtid_slave_pos
Error    : Table 'mysql.gtid_slave_pos' doesn't exist in engine
status   : Operation failed
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.index_stats                                  OK
mysql.innodb_index_stats
Error    : Table 'mysql.innodb_index_stats' doesn't exist in engine
status   : Operation failed
mysql.innodb_table_stats
Error    : Table 'mysql.innodb_table_stats' doesn't exist in engine
status   : Operation failed
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.roles_mapping                                OK
mysql.servers                                      OK
mysql.table_stats                                  OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK

Repairing tables
mysql.gtid_slave_pos
Error    : Table 'mysql.gtid_slave_pos' doesn't exist in engine
status   : Operation failed
mysql.innodb_index_stats
Error    : Table 'mysql.innodb_index_stats' doesn't exist in engine
status   : Operation failed
mysql.innodb_table_stats
Error    : Table 'mysql.innodb_table_stats' doesn't exist in engine
status   : Operation failed
Phase 2/7: Installing used storage engines... Skipped
Phase 3/7: Fixing views
mma_live.mma_affiliate_wp_campaigns                OK
mma_staging.mma_affiliate_wp_campaigns
Error    : The user specified as a definer ('mma_live'@'10.8.20.22') does not exist
error    : Corrupt
Phase 4/7: Running 'mysql_fix_privilege_tables'
ERROR 1932 (42S02) at line 595: Table 'mysql.innodb_index_stats' doesn't exist in engine
ERROR 1243 (HY000) at line 596: Unknown prepared statement handler (stmt) given to EXECUTE
ERROR 1932 (42S02) at line 598: Table 'mysql.innodb_table_stats' doesn't exist in engine
ERROR 1243 (HY000) at line 599: Unknown prepared statement handler (stmt) given to EXECUTE
ERROR 1932 (42S02) at line 603: Table 'mysql.innodb_index_stats' doesn't exist in engine
ERROR 1932 (42S02) at line 607: Table 'mysql.innodb_table_stats' doesn't exist in engine
ERROR 1932 (42S02) at line 610: Table 'mysql.innodb_table_stats' doesn't exist in engine
FATAL ERROR: Upgrade failed
 

SirDice

Administrator
Staff member
Administrator
Moderator

Reaction score: 7,886
Messages: 31,157

Bugger, it doesn't create the tables. I was hoping it would create them if they were missing.

These are from MariaDB 10.2, I'm not entirely sure they are correct for 10.1 but it's worth a shot.
Run mysql and use these:
Code:
use mysql;
CREATE TABLE `gtid_slave_pos` (
  `domain_id` int(10) unsigned NOT NULL,
  `sub_id` bigint(20) unsigned NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `seq_no` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`domain_id`,`sub_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Replication slave GTID position';

CREATE TABLE `innodb_table_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(199) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `n_rows` bigint(20) unsigned NOT NULL,
  `clustered_index_size` bigint(20) unsigned NOT NULL,
  `sum_of_other_index_sizes` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`database_name`,`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
You could also (on a different machine) install MariaDB 10.1 with a clean slate. Use a command like show create table mysql.gtid_slave_pos \G to see the exact command to create the table. Create those two missing tables and run mysql_upgrade(1) again.
 
OP
OP
fred974

fred974

Daemon

Reaction score: 37
Messages: 1,588

SirDice thank you for your valuable assistance here.
Will upgrading from tp MariaDB 10.1.40 reinstall the missing table you think?
Also, I only use 1 database and do not have replication
 

anlashok

Member

Reaction score: 10
Messages: 42

Code:
2019-06-14 12:42:04 34930350848 [Warning] InnoDB: Cannot open table mysql/gtid_slave_pos from the internal data dictionary of InnoDB though the .frm file for the table exists.
This part of the log file is saying that there exists a .frm file for the table its complaining about. Running mysql_upgrade won't try to recreate the tables because they already partly exist and it won't overwrite/destroy them. Try renaming the two .frm and .ibd files for the offending two tables in the mysql directory (and/or make backups) and then try mysql_upgrade again. You might need mysql_upgrade --force
 
OP
OP
fred974

fred974

Daemon

Reaction score: 37
Messages: 1,588

Try renaming the two .frm and .ibd files for the offending two tables in the mysql directory
How do I know which one its is? The error doesn't provide thefullfile name and I have lots of .frm file in /var/db/mysql/domain/*
 
OP
OP
fred974

fred974

Daemon

Reaction score: 37
Messages: 1,588

sorry, is it this 2 files?
Code:
/var/db/mysql/mysql/gtid_slave_pos.ibd
/var/db/mysql/mysql/gtid_slave_pos.frm
 

anlashok

Member

Reaction score: 10
Messages: 42

yes they are the ones I meant and
Code:
/var/db/mysql/mysql/innodb_table_stats.frm/.ibd
as it had an issue with that one too.

they are the files corresponding to the tables SirDice was getting you to rebuild.
 

anlashok

Member

Reaction score: 10
Messages: 42

you also probably have an issue with a missing user for the mma_staging database.
Code:
mma_staging.mma_affiliate_wp_campaigns
Error    : The user specified as a definer ('mma_live'@'10.8.20.22') does not exist
error    : Corrupt
 
OP
OP
fred974

fred974

Daemon

Reaction score: 37
Messages: 1,588

Here is a recap of what I have done so far
cd /var/db/mysql/mysql
mv gtid_slave_pos.frm gtid_slave_pos.frm.bak
mv gtid_slave_pos.ibd gtid_slave_pos.ibd.bakmv
mv innodb_table_stats.frm innodb_table_stats.frm.bak
mv innodb_table_stats.ibd innodb_table_stats.ibd.bak

I then upgraded the database
Code:
mariadb101-server: 10.1.35_1 -> 10.1.40
mariadb101-client: 10.1.35_1 -> 10.1.40
CHECK TABLE mysql.gtid_slave_pos;
Code:
+----------------------+-------+----------+----------+
| Table                | Op    | Msg_type | Msg_text |
+----------------------+-------+----------+----------+
| mysql.gtid_slave_pos | check | status   | OK       |
+----------------------+-------+----------+----------+
1 row in set (0.00 sec)
CHECK TABLE mysql.innodb_table_stats;
Code:
+--------------------------+-------+----------+----------+
| Table                    | Op    | Msg_type | Msg_text |
+--------------------------+-------+----------+----------+
| mysql.innodb_table_stats | check | status   | OK       |
+--------------------------+-------+----------+----------+
1 row in set (0.00 sec)
/usr/local/bin/mysql_upgrade -u dbadmin -p
Code:
Enter password:
Phase 1/7: Checking and upgrading mysql database
Processing databases
mysql
mysql.column_stats                                 OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.gtid_slave_pos                               OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.index_stats                                  OK
mysql.innodb_index_stats
Error    : Table 'mysql.innodb_index_stats' doesn't exist in engine
status   : Operation failed
mysql.innodb_table_stats                           OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.roles_mapping                                OK
mysql.servers                                      OK
mysql.table_stats                                  OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK

Repairing tables
mysql.innodb_index_stats
Error    : Table 'mysql.innodb_index_stats' doesn't exist in engine
status   : Operation failed
Phase 2/7: Installing used storage engines... Skipped
Phase 3/7: Fixing views
mma_live.mma_affiliate_wp_campaigns                OK
mma_staging.mma_affiliate_wp_campaigns
Error    : The user specified as a definer ('mma_live'@'10.8.20.22') does not exist
error    : Corrupt
Phase 4/7: Running 'mysql_fix_privilege_tables'
ERROR 1932 (42S02) at line 592: Table 'mysql.innodb_index_stats' doesn't exist in engine
ERROR 1243 (HY000) at line 593: Unknown prepared statement handler (stmt) given to EXECUTE
ERROR 1932 (42S02) at line 600: Table 'mysql.innodb_index_stats' doesn't exist in engine
FATAL ERROR: Upgrade failed
So the missing tables are back but I still cannot run mysql_upgrade(1) and no idea how to fix the error
Code:
mma_live.mma_affiliate_wp_campaigns                OK
mma_staging.mma_affiliate_wp_campaigns
Error    : The user specified as a definer ('mma_live'@'10.8.20.22') does not exist
error    : Corrupt
the mma_live user is not using that jail IP. Could it be because the database was moved from one host to another using rsync?
 
OP
OP
fred974

fred974

Daemon

Reaction score: 37
Messages: 1,588

Also, could anyone please provide a basic my.cnf file so I can see what to add in mine?
 

anlashok

Member

Reaction score: 10
Messages: 42

Looks like there is a third table mysql.innodb_index_stats corrupted. Try removing those files in /var/db/mysql/mysql/innodb_index_stats.frm/.ibd. its create statement is:
Code:
CREATE TABLE `innodb_index_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `index_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `stat_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `stat_value` bigint(20) unsigned NOT NULL,
  `sample_size` bigint(20) unsigned DEFAULT NULL,
  `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
Several default my.cnf files for mariadb-server are located below
Code:
# ls /usr/local/share/mysql/my*.cnf
/usr/local/share/mysql/my-huge.cnf
/usr/local/share/mysql/my-medium.cnf
/usr/local/share/mysql/my-innodb-heavy-4G.cnf
/usr/local/share/mysql/my-small.cnf
/usr/local/share/mysql/my-large.cnf
And your server is _not_ using /var/db/mysql/my.cnf as its mysql config file. The data directories are not in the correct place based on this
Code:
# DATA STORAGE #
innodb_data_home_dir = /var/db/mysql-innodb
innodb_log_group_home_dir = /var/db/mysql-innodb-logs
as the db files would be in /var/db/mysql-innodb/* if it was using that file and not /var/db/mysql/*. Look instead in /usr/local/etc/my.cnf for the one actually being used I would imagine
 

anlashok

Member

Reaction score: 10
Messages: 42

Code:
mma_live.mma_affiliate_wp_campaigns                OK
mma_staging.mma_affiliate_wp_campaigns
Error    : The user specified as a definer ('mma_live'@'10.8.20.22') does not exist
error    : Corrupt
the mma_live user is not using that jail IP. Could it be because the database was moved from one host to another using rsync?
To fix the above, you will need to create a new user in the mysql database with `Host` set to '10.8.20.22' and `User` set to 'mma_live'. Its trying to update/recreate a database view in mma_staging db using the mma_live user from mma_live database. its a fairly minor issue though and won't be causing your main issue.
 

anlashok

Member

Reaction score: 10
Messages: 42

One question just struck me, where is this error message being displayed?
Code:
error establishing a database connection
You seem to be able to access the mysql server ok from the command line even if there are issues with a few mysql tables at the moment, so that means that the mysql server is definitely running. So is this error displayed in the web browser, and if it is have you checked the web server logs for clues as to why the web server cant talk to the DB server?.
 
OP
OP
fred974

fred974

Daemon

Reaction score: 37
Messages: 1,588

I removed /var/db/mysql/mysql/innodb_index_stats.frm/.ibd and created a dummy account
Code:
GRANT SELECT ON mma_live.* to 'mma_live'@'10.44.1.22' IDENTIFIED BY '@#m-blabal+ube_DUMMY';
and ran mysql_upgrade(1) again.
This time everything worked like charm.
Yes, It looks like it was the same issue. Thank you and SirDice I now learn how to fix this issue.

And your server is _not_ using /var/db/mysql/my.cnf as its mysql config file.
I don't have /usr/local/etc/my.cnf but I will create it..
I forgot about that part and I have been told before :(

Thank you very much for your patience in helping me here.
 
Top