Hello,
This is my very first post here so please be gentle.
I’m using “5.7.25-28-57-log Percona XtraDB Cluster (GPL), Release rel28, Revision a2ef85f, WSREP version 31.35, wsrep_31.35” not quite in a “Galera” fashion but more like in a traditional fashion, master > slave setup and I am now facing a dilemma.
Master has now FEDERATED engine enabled for a few tables and works great for my specific scenario, the problem that I’m facing is with my Slave node.
When I enable FDERATED engine via my.cnf as I did for the Master node I’m getting an error like this:
Thread pointer: 0x7fc2e00008c0
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 = 7fc38c1b7890 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x3b)[0xf3b44b]
/usr/sbin/mysqld(handle_fatal_signal+0x471)[0x7b0a11]
/lib64/libpthread.so.0(+0xf5d0)[0x7fd5520bb5d0]
/usr/sbin/mysqld(_ZN12ha_federated7rnd_posEPhS0_+0x2f)[0xfc711f]
/usr/sbin/mysqld(_ZN7handler10ha_rnd_posEPhS0_+0x192)[0x82d962]
/usr/sbin/mysqld(_ZN7handler17rnd_pos_by_recordEPh+0x36)[0x836176]
/usr/sbin/mysqld(_ZN14Rows_log_event24do_index_scan_and_updateEPK14Relay_log_info+0x1c6)[0xeafc66]
/usr/sbin/mysqld(_ZN14Rows_log_event14do_apply_eventEPK14Relay_log_info+0xb7c)[0xeadc0c]
/usr/sbin/mysqld(_ZN9Log_event11apply_eventEP14Relay_log_info+0x6d)[0xea556d]
/usr/sbin/mysqld(_Z26apply_event_and_update_posPP9Log_eventP3THDP14Relay_log_info+0x511)[0xef2651]
/usr/sbin/mysqld(handle_slave_sql+0x180d)[0xeff43d]
/usr/sbin/mysqld(pfs_spawn_thread+0x1b4)[0xf53d44]
/lib64/libpthread.so.0(+0x7dd5)[0x7fd5520b3dd5]
/lib64/libc.so.6(clone+0x6d)[0x7fd550283ead]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0): is an invalid pointer
Connection ID (thread ID): 2
Status: NOT_KILLED
You may download the Percona XtraDB Cluster operations manual by visiting
http://www.percona.com/software/percona-xtradb-cluster/. You may find information
in the manual which will help you identify the cause of the crash.
Can anyone help me to solve this mystery or at least to guide me if FEDERATED is supported with a Master > Slave setup on Percona XtraDB Cluster?
Thank you
Hi Sebastian!
Welcome to the Percona forum. Glad that you reached us.
From my understanding is that you are using the binaries from the Percona Cluster but with only one node in a Master → Slave configuration.
The error that you are relating seems to be a bug when using Federated tables. Do you have a reproducible test case to share?
Please share your my.cnf settings from both servers as well and I will be glad to investigate it.
Hi Vinicius,
That’s correct, I am using the binaries from Percona Cluster, initially the stack has been designed as a multi-master setup using Galera, 3 nodes but after multiple trails even using ProxySQL we gave up realising that the WebApp has a quite a bad design that nothing will be good enough unless the monolithic logic will be sliced, buy as I said long was quite challenging and we’ve ended up with this Master > Slave setup that looks to be stable for now.
Recently one of my colleagues enabled FEDERATED on MASTER node and it looks that we’re getting the right / expected results using this architecture.
The problem is with one of the node that simply doesn’t starts if we enable FEDERATED.
PS: Please don’t judge the config.
Master Node
MySQL Status and Engines
mysql> status;
--------------
mysql Ver 14.14 Distrib 5.7.25-28, for Linux (x86_64) using 6.2
Connection id: 3494912
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.25-28-57-log Percona XtraDB Cluster (GPL), Release rel28, Revision a2ef85f, WSREP version 31.35, wsrep_31.35
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 6 days 8 hours 20 min 5 sec
Threads: 47 Questions: 122817837 Slow queries: 0 Opens: 52567 Flush tables: 1 Open tables: 15094 Queries per second avg: 223.954
--------------
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| CSV | YES | CSV storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| FEDERATED | YES | Federated MySQL storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
my.cnf
[client]
# empty line
[mysqld_safe]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/lib/mysql/mysql.sock
[mysqld]
#### Service Settings ####
server-id = 1
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/lib/mysql/mysql.sock
datadir = /var/lib/mysql
tmpdir = /mnt/mysql-data/tmp
log-error = /var/log/mysqld.log
slow-query-log = 0
long_query_time = 30
log_queries_not_using_indexes = 0
slow-query-log-file = /var/log/slow-queries.log
secure-file-priv = /mnt/mysql-exports-dbm001
symbolic-links = 1
innodb_stats_on_metadata = on
#### BinLogs Settings ####
binlog_format = ROW
log-bin = /mnt/mysql-binlogs/bin
log_slave_updates
expire_logs_days = 7
sync_binlog = 1
#### Service Flags ####
default_storage_engine = InnoDB
sql_mode = "NO_ENGINE_SUBSTITUTION"
skip_name_resolve
skip_external_locking
explicit_defaults_for_timestamp
federated
#### Service Limits ####
max_allowed_packet = 256M
max_connections = 1200
max_connect_errors = 1000000
max_heap_table_size = 256M
open_files_limit = 240K
table_definition_cache = 30K
table_open_cache = 131072
table_open_cache_instances = 16
query_cache_type = OFF
sort_buffer_size = 16M
tmp_table_size = 4G
thread_cache_size = 8192
wait_timeout = 3600 # 1 hour
interactive_timeout = 3600 # 1 hour
relay_log_info_repository = TABLE
relay_log_recovery = ON
query_cache_size = 1048576
#### InnoDB Settings ####
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 1G
innodb_flush_log_at_trx_commit = 0
innodb-file-per-table = 1
innodb-buffer-pool-size = 96G
innodb_thread_concurrency = 16
innodb_max_dirty_pages_pct = 75
innodb_checksum_algorithm = CRC32
innodb_log_buffer_size = 1024M
innodb_purge_threads = 14
innodb_read_io_threads = 18
innodb_write_io_threads = 18
innodb_autoinc_lock_mode = 2
innodb_io_capacity = 18000
innodb_io_capacity_max = 19000
innodb_buffer_pool_dump_pct = 75
innodb_buffer_pool_dump_at_shutdown = on
innodb_buffer_pool_load_at_startup = on
innodb_buffer_pool_instances = 12
innodb_buffer_pool_chunk_size = 8G
innodb_page_cleaners = 16
innodb_lru_scan_depth = 512
# innodb_parallel_doublewrite_path = /mnt/mysql-tmpdir/xb_doublewrite
#innodb_force_recovery = 4
#### GTID Replication ####
# gtid_mode = on
# enforce-gtid-consistency = on
Federated Table
mysql> SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = '<DATABASE>';
+--------------------------------+-----------+
| TABLE_NAME | ENGINE |
+--------------------------------+-----------+
| <TABLE_W> | FEDERATED |
| <TABLE_X> | FEDERATED |
| <TABLE_Y> | FEDERATED |
| <TABLE_Z> | FEDERATED |
+--------------------------------+-----------+
4 rows in set (0.00 sec)
…had to split this thread…
Slave Node
MySQL Status and Engines
mysql> status;
--------------
mysql Ver 14.14 Distrib 5.7.25-28, for Linux (x86_64) using 6.2
Connection id: 6
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.25-28-57-log Percona XtraDB Cluster (GPL), Release rel28, Revision a2ef85f, WSREP version 31.35, wsrep_31.35
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 4 hours 31 min 14 sec
Threads: 2 Questions: 16192 Slow queries: 0 Opens: 114 Flush tables: 1 Open tables: 107 Queries per second avg: 0.994
--------------
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| CSV | YES | CSV storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
my.cnf
[client]
# empty line
[mysqld_safe]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/lib/mysql/mysql.sock
[mysqld]
# skip-slave-start
#### Service Settings ####
server-id = 2
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/lib/mysql/mysql.sock
datadir = /var/lib/mysql
tmpdir = /mnt/mysql-temp
log-error = /var/log/mysqld.log
slow-query-log = 0
long_query_time = 30
log_queries_not_using_indexes = 0
secure-file-priv = /mnt/mysql-exports
symbolic-links = 1
#### BinLogs Settings ####
binlog_format = ROW
log-bin = /mnt/mysql-binlogs/bin
binlog_space_limit = 450G
expire_logs_days = 7
sync_binlog = 1
relay_log = /mnt/mysql-relaylogs/bin
relay_log_info_repository = TABLE
relay_log_recovery = ON
relay_log_space_limit = 450G
log_slave_updates
#### Service Flags ####
default_storage_engine = InnoDB
sql_mode = "NO_ENGINE_SUBSTITUTION"
skip_name_resolve
skip_external_locking
explicit_defaults_for_timestamp
# federated
#### Service Limits ####
max_allowed_packet = 512M
max_connections = 1200
max_connect_errors = 1000000
max_heap_table_size = 128M
open_files_limit = 240K
table_definition_cache = 30K
table_open_cache = 120K
table_open_cache_instances = 16
query_cache_type = OFF
sort_buffer_size = 16M
tmp_table_size = 64M
thread_cache_size = 14K
read_buffer_size = 512K
read_rnd_buffer_size = 512K
wait_timeout = 28800
interactive_timeout = 3600
#### InnoDB Settings ####
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 8G
innodb-flush-log-at-trx-commit = 2
innodb-file-per-table = 1
innodb-buffer-pool-size = 64G
innodb_buffer_pool_instances = 10
innodb_thread_concurrency = 16
innodb_max_dirty_pages_pct = 75
innodb_checksum_algorithm = CRC32
innodb_log_buffer_size = 1024M
innodb_purge_threads = 14
innodb_read_io_threads = 18
innodb_write_io_threads = 18
innodb_autoinc_lock_mode = 2
innodb_buffer_pool_dump_pct = 75
innodb_buffer_pool_dump_at_shutdown = on
innodb_buffer_pool_load_at_startup = on
#### Replication Settings ####
read_only = 1
# gtid_mode = on
# enforce-gtid-consistency = on
As you can see FEDERATED has been disabled, if I try to start the slave having this config (FEDRATED disabled) I am getting:
Last_SQL_Error: Error executing row event: 'Unknown storage engine 'FEDERATED''
Which is absolutely fine, that’s expected as FEDERATED is not loaded.
Once FEDERATED is enabled via my.cnf SLAVE throws this error:
...
2019-03-11T08:21:43.893854Z 0 [Note] WSREP: Found pre-stored initial position: f0a84c8f-fe7f-0000-ba8d-800000000000:65207025
2019-03-11T08:21:43.893862Z 0 [Note] WSREP: wsrep_load(): loading provider library 'none'
2019-03-11T08:21:43.893914Z 0 [Note] WSREP: Setting wsrep_ready to true
2019-03-11T08:21:43.894157Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.25-28-57-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Percona XtraDB Cluster (GPL), Release rel28, Revision a2ef85f, WSREP version 31.35, wsrep_31.35
08:21:43 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.
Please help us make Percona XtraDB Cluster better by reporting any
bugs at https://jira.percona.com/projects/PXC/issues
key_buffer_size=8388608
read_buffer_size=524288
max_used_connections=0
max_threads=1201
thread_count=2
connection_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 20301059 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
Thread pointer: 0x7f35e80008c0
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 = 7f3684075890 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x3b)[0xf3b44b]
/usr/sbin/mysqld(handle_fatal_signal+0x471)[0x7b0a11]
/lib64/libpthread.so.0(+0xf5d0)[0x7f484bf7d5d0]
/usr/sbin/mysqld(_ZN12ha_federated7rnd_posEPhS0_+0x2f)[0xfc711f]
/usr/sbin/mysqld(_ZN7handler10ha_rnd_posEPhS0_+0x192)[0x82d962]
/usr/sbin/mysqld(_ZN7handler17rnd_pos_by_recordEPh+0x36)[0x836176]
/usr/sbin/mysqld(_ZN14Rows_log_event24do_index_scan_and_updateEPK14Relay_log_info+0x1c6)[0xeafc66]
/usr/sbin/mysqld(_ZN14Rows_log_event14do_apply_eventEPK14Relay_log_info+0xb7c)[0xeadc0c]
/usr/sbin/mysqld(_ZN9Log_event11apply_eventEP14Relay_log_info+0x6d)[0xea556d]
/usr/sbin/mysqld(_Z26apply_event_and_update_posPP9Log_eventP3THDP14Relay_log_info+0x511)[0xef2651]
/usr/sbin/mysqld(handle_slave_sql+0x180d)[0xeff43d]
/usr/sbin/mysqld(pfs_spawn_thread+0x1b4)[0xf53d44]
/lib64/libpthread.so.0(+0x7dd5)[0x7f484bf75dd5]
/lib64/libc.so.6(clone+0x6d)[0x7f484a145ead]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0): is an invalid pointer
Connection ID (thread ID): 1
Status: NOT_KILLED
You may download the Percona XtraDB Cluster operations manual by visiting
http://www.percona.com/software/percona-xtradb-cluster/. You may find information
in the manual which will help you identify the cause of the crash.
Thank you