After setting up the replication in Maria DB, I was seeing error message in the error log and show slave status\G out put and the replication is working.
ERROR:
[ERROR] Slave I/O: Unable to load replication GTID slave state from mysql.gtid_slave_pos: Table 'mysql.gtid_slave_pos' doesn't exist, Internal MariaDB error code: 1146
So I tried a `mysql_upgrad` which told me to try --force as it looked already up to date, so I tried --force, which gave:
mysql.gtid_slave_pos
Error : Table 'mysql.gtid_slave_pos' doesn't exist in engine
status : Operation failed
I checked in the db/mysql folder and found that the .frm and .ibd is aready existing.
gtid_slave_pos.frm
gtid_slave_pos.ibd
I just removed the files and recreated the table with MyISAM engine, which solved the issue.
STEPS:
Go to the mysql folder ( default path in column store /usr/local/mariadb/columnstore/mysql/db/mysql )
drop the following files:
rm gtid_slave_pos.frm
rm gtid_slave_pos.ibd
mysql -uroot -p -s mysql
MariaDB [mysql]> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 156.23.25.15
Master_User: mysqlrepli
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000465
Read_Master_Log_Pos: 809269330
Relay_Log_File: relay.000190
Relay_Log_Pos: 790004371
Relay_Master_Log_File: mysql-bin.000465
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1932
Last_Error: Unable to load replication GTID slave state from mysql.gtid_slave_pos: Table 'mysql.gtid_slave_pos' doesn't exist in engine
Skip_Counter: 0
Exec_Master_Log_Pos: 809269330
Relay_Log_Space: 790004710
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1932
Last_SQL_Error: Unable to load replication GTID slave state from mysql.gtid_slave_pos: Table 'mysql.gtid_slave_pos' doesn't exist in engine
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
1 row in set (0.00 sec)
MariaDB [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=MyISAM DEFAULT CHARSET=utf8 COMMENT='Replication slave GTID state';
Query OK, 0 rows affected (0.01 sec)
MariaDB [mysql]> stop slave;
Query OK, 0 rows affected (0.03 sec)
MariaDB [mysql]> start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 156.23.25.15
Master_User: mysqlrepli
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000465
Read_Master_Log_Pos: 809289283
Relay_Log_File: relay.000191
Relay_Log_Pos: 15483
Relay_Master_Log_File: mysql-bin.000465
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 809289283
Relay_Log_Space: 790022182
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
1 row in set (0.00 sec)
Hope it helps
Thanks a lot... you saved me :D
ReplyDeleteok .. But my question is ..
Deletehow its happned ..?
i am also facing this issue ...
so, i wnt to know RCA for this issue ..
pls any one suggest
thanks a lot. it worked like charm
ReplyDeleteNot working! After deleting two files, we cannot create new table (it still exists). Attempt to restart maria daemon crashes database server.
ReplyDeleteThanks a lot! it worked for me!
ReplyDeleteI have the error, yet these tables already exist and work fine on the slave. The problem is they don't exist on the master. The master does not use such a table as it is an older mysql.
ReplyDeleteHow will this work if it is a MariaDB Galera Enviroment?
ReplyDeleteThank you very much
ReplyDeleteGreat and that i have a tremendous offer: Where To Start With Whole House Renovation whole house renovation
ReplyDelete