Not the answer you need?
Register and ask your own question!

Federated Engine Issue

SebastianGSebastianG ContributorCurrent User Role Contributor
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

Comments

  • vinicius.grippavinicius.grippa Percona Percona Staff Role
    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.
  • SebastianGSebastianG Contributor Current User Role Contributor
    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...
  • SebastianGSebastianG Contributor Current User Role Contributor
    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&#64;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
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.