Unable to connect MySQL Workbench to Percona Xtradb Cluster 8.0.28

[johnmorganhouse]

Hello - I am trying to connect AWS Database Migration Service to MySQL running as a Percona XtraDB Cluster 8.0.28. On this installation, clustering is not enabled and is running as a single host.

I am unable to retrieve table and schema data with DMS, but it does pass the connection test.
I am unable to open the database with Workbench.

I am able to rapidly retrieve schema and table data if I use:

1. CLI - works every time
2. The “Reverse Engineer Database” option in Workbench - works too - retrieve schema and table data, even from large tables, quickly.

I have installed the same version of Percona MySQL on a test machine and am seeing the exact same results.

2023-02-14T21:58:36:193103 [METADATA_MANAGE ]I: Connecting to MySQL using ODBC connection string: DRIVER={MySQL ODBC 8.0 Unicode Driver};SERVER=10.xxx.xxx.xxx;port=3306;UID=migration;DB=;CHARSET=binary;initstmt=SET time_zone=‘+00:00’;Option=74448896;NO_LOCALE=1;PWD=***; (mysql_endpoint_imp.c:720)
2023-02-14T21:58:36:371638 [METADATA_MANAGE ]I: Source endpoint ‘Mysql’ is using provider syntax ‘MySQL’ (provider_syntax_manager.c:623)
2023-02-14T21:58:36:399790 [SOURCE_UNLOAD ]I: The UnloadTimeout internal parameter was ignored because the have_statement_timeout system variable (which limits the duration of both the unload query AND the unload operation) is set on MySQL server. If you still want to use the unloadTimeout internal parameter, set the ForceUnloadTimeout internal parameter to TRUE. (mysql_endpoint_imp.c:832)
2023-02-14T21:58:36:399821 [METADATA_MANAGE ]I: Unload Timeout = 999999 seconds (mysql_endpoint_imp.c:836)
2023-02-14T21:58:36:429792 [METADATA_MANAGE ]I: Going to connect to ODBC connection string: DRIVER={MySQL ODBC 8.0 Unicode Driver};SERVER=rds-cluster-1.cluster-choh9m88rfg0.us-west-1.rds.amazonaws.com;PORT=3306;FOUND_ROWS=1;CHARSET=binary;NO_LOCALE=1;ENABLE_LOCAL_INFILE=1;initstmt=SET FOREIGN_KEY_CHECKS=0; executeTimeout=3600;UID=admin; (odbc_endpoint_imp.c:1993)
2023-02-14T21:58:36:561192 [METADATA_MANAGE ]I: Target endpoint ‘Mysqltarget’ is using provider syntax ‘MySQL’ (provider_syntax_manager.c:629)
2023-02-14T21:58:36:561233 [TASK_MANAGER ]I: Preparing all components (replicationtask.c:1960)
2023-02-14T21:58:36:561374 [TASK_MANAGER ]I: Task - MVUJOM5WUR2RHP3C3KVGLA3F72FZ32CA5JSHYMQ is in STARTING state, updating starting status to AR_PREPARING_COMPONENTS (repository.c:5110)
2023-02-14T21:58:36:568236 [TASK_MANAGER ]I: Creating threads for all components (replicationtask.c:1993)
2023-02-14T21:58:36:568356 [TASK_MANAGER ]I: Task - MVUJOM5WUR2RHP3C3KVGLA3F72FZ32CA5JSHYMQ is in STARTING state, updating starting status to AR_CREATING_TREADS (repository.c:5110)
2023-02-14T21:58:36:575296 [TASK_MANAGER ]I: Task - MVUJOM5WUR2RHP3C3KVGLA3F72FZ32CA5JSHYMQ is in STARTING state, updating starting status to AR_CREATING_TABLES_LIST (repository.c:5110)
2023-02-14T21:58:36:582288 [TABLES_MANAGER ]I: Calling for get capture table list from the Metadata Manager started. (tasktablesmanager.c:934)
2023-02-14T22:05:37:250981 [METADATA_MANAGE ]E: RetCode: SQL_ERROR SqlState: 08S01 NativeError: 2013 Message: [MySQL][ODBC 8.0(w) Driver][mysqld-8.0.28-19.1]Lost connection to MySQL server during query [1022502] (ar_odbc_stmt.c:3932)
2023-02-14T22:05:37:251041 [METADATA_MANAGE ]W: Failed to get the capture list from the endpoint. Attempt #1 from 5 (metadatamanager.c:4087)
2023-02-14T22:05:37:264880 [METADATA_MANAGE ]W: Failed to get the capture list from the endpoint. Attempt #2 from 5 (metadatamanager.c:4087)
2023-02-14T22:05:37:271515 [METADATA_MANAGE ]W: Failed to get the capture list from the endpoint. Attempt #3 from 5 (metadatamanager.c:4087)
2023-02-14T22:05:37:278343 [METADATA_MANAGE ]W: Failed to get the capture list from the endpoint. Attempt #4 from 5 (metadatamanager.c:4087)
2023-02-14T22:05:37:285035 [METADATA_MANAGE ]E: Failed to get the capture list from the endpoint. Attempt #5 from 5 [1022502] (metadatamanager.c:4095)
2023-02-14T22:05:37:285042 [TABLES_MANAGER ]E: Cannot get captured tables list [1020486] (tasktablesmanager.c:938)
2023-02-14T22:05:37:285044 [TASK_MANAGER ]E: Build tables list failed [1020486] (replicationtask.c:2124)
2023-02-14T22:05:37:285045 [TASK_MANAGER ]E: Task ‘MVUJOM5WUR2RHP3C3KVGLA3F72FZ32CA5JSHYMQ’ failed [1020486] (replicationtask.c:3385)
2023-02-14T22:05:37:292096 [TASK_MANAGER ]I: Task - MVUJOM5WUR2RHP3C3KVGLA3F72FZ32CA5JSHYMQ is in ERROR state, updating starting status to AR_NOT_APPLICABLE (repository.c:5102)
2023-02-14T22:05:37:298907 [TASK_MANAGER ]E: Task ‘MVUJOM5WUR2RHP3C3KVGLA3F72FZ32CA5JSHYMQ’ encountered a fatal error (repository.c:5196)
2023-02-14T22:05:46:150367 [TASK_MANAGER ]I: Task Management thread terminated abnormally (replicationtask.c:4053)
2023-02-14T22:05:46:151395 [AT_GLOBAL ]I: Closing log file at Tue Feb 14 22:05:46 2023 (at_logger.c:2766)

This issue has stumped my team and the DB specialists team at AWS. Anybody seen this before and know how to solve it?

[Pep_Pla]

Hi @johnmorganhouse

Thank you for being part of our community. Looks like ODBC is getting a 2013 error. This error is generated by a closed connection.

I recommend that you check this: https://dev.mysql.com/doc/refman/8.0/en/error-lost-connection.html

And check also any timeout value at database level and at network level.

Not sure if it there are specific timeout variables that could affect ODBC driver behavior.

Pep

PS: Show global variables like ‘%timeout%’ could be a good starting point. Also check if the error happens at a constant interval.

[matthewb]

MySQL/PXC isn’t crashing is it? Also, I just want to verify that you have indeed commented out ALL wsrep_* parameters and started MySQL normally. Starting it in single-server can still be a proper PXC mode unless you have commented out all the PXC-wsrep parameters.

[johnmorganhouse]

Thank you guys! Here’s the contents of my.cnf and percona-server.cnf. I have applied all of the timeout settings as documented and do not see any PXC wsrep settings except in the list of timeout settings. I will extend the net_read_timeout to 60 and give it another try. I did a test with a non-Percona version of mysql last night and it worked the first time - so I am hopeful there’s an ini setting somewhere in all this that’s causing the trouble. I will be grateful for any additional assistance.

percona-server.cnf:

/etc/mysql# cat percona-server.cnf

The Percona Server 5.7 configuration file.

* IMPORTANT: Additional settings that can override those from this file!

The files must end with ‘.cnf’, otherwise they’ll be ignored.

Please make any edits and changes to the appropriate sectional files

included below.

[mysqld]
innodb_buffer_pool_size = 2G
innodb_stats_on_metadata = OFF
query_cache_type = 0
innodb_log_file_size = 256M
innodb_thread_concurrency = 96
innodb_file_per_table
innodb_flush_log_at_trx_commit = 1 # may change to 2 or 0
innodb_log_buffer_size=8M
skip-innodb_doublewrite
innodb_flush_method = O_DIRECT
innodb_io_capacity=3000
innodb_io_capacity_max=6000
max_connections=60000
interactive_timeout=28800
wait_timeout=120
#encrypt_cluster_traffic=OFF

slow_query_log = ON
slow_query_log_file = “/var/log/mysql/slow.log”

key_buffer_size = 1G
bulk_insert_buffer_size = 64M
join_buffer_size = 64M
sort_buffer_size = 64M
read_buffer_size = 16M
read_rnd_buffer_size = 16M
sql_mode = “STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”

log-bin=mysql-bin
server-id=1
sync_binlog=1

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/percona-server.conf.d/

my.cnf:

/etc/mysql# cat my.cnf

The Percona Server 8.0 configuration file.

For explanations see

http://dev.mysql.com/doc/mysql/en/server-system-variables.html

* IMPORTANT: Additional settings that can override those from this file!

The files must end with ‘.cnf’, otherwise they’ll be ignored.

[mysqld]
innodb_buffer_pool_size = 32G
innodb_stats_on_metadata = OFF
#query_cache_type = 0
#innodb_log_file_size = 256M
#innodb_thread_concurrency = 96
#innodb_file_per_table
#innodb_flush_log_at_trx_commit = 1 # may change to 2 or 0
#innodb_log_buffer_size=8M
#skip-innodb_doublewrite
#innodb_flush_method = O_DIRECT
#innodb_io_capacity=3000
#innodb_io_capacity_max=6000
max_connections=30000
open_files_limit=100000
ssl=0
slow_query_log = ON
slow_query_log_file = “/var/log/mysql/slow.log”
connect_timeout = 600
net_read_timeout = 30
wait_timeout = 120
interactive_timeout = 28800
general_log_file = “/var/log/mysql/mysql.log”
general_log = ON

key_buffer_size = 4G
bulk_insert_buffer_size = 64M
join_buffer_size = 64M
sort_buffer_size = 64M
read_buffer_size = 16M
read_rnd_buffer_size = 16M
#sql_mode = “STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”
sql_mode = “STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION”

#log-bin=mysql-bin
#server-id=1
#sync_binlog=1

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
!includedir /etc/mysql/percona-server.conf.d/

[mysqld]

General replication settings

disabled_storage_engines=“MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY”
gtid_mode = ON
enforce_gtid_consistency = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
binlog_checksum = NONE
log_slave_updates = ON
log_bin = binlog
binlog_format = ROW
transaction_write_set_extraction = XXHASH64
#tls_version = invalid

#loose-group_replication_bootstrap_group = OFF
#loose-group_replication_start_on_boot = OFF
#loose-group_replication_ssl_mode = REQUIRED
#loose-group_replication_recovery_use_ssl = 1

Shared replication group configuration

#loose-group_replication_group_name = “”
#loose-group_replication_ip_whitelist = “”
#loose-group_replication_group_seeds = “”

Single or Multi-primary mode? Uncomment these two lines

for multi-primary mode, where any host can accept writes

#loose-group_replication_single_primary_mode = OFF
#loose-group_replication_enforce_update_everywhere_checks = ON

Host specific replication configuration

server_id = 1
bind-address = “10.168.17.109”
#bind-address = “127.0.0.1”
report_host = “”
#loose-group_replication_local_address = “”

attempt to kill off replica slaves

#slave-parallel-threads = 0

mysql> show global status like ‘Aborted_connects’;
±-----------------±------+
| Variable_name | Value |
±-----------------±------+
| Aborted_connects | 0 |
±-----------------±------+
1 row in set (0.01 sec)

mysql> show global variables like ‘%timeout%’;
±------------------------------------±---------+
| Variable_name | Value |
±------------------------------------±---------+
| connect_timeout | 600 |
| delayed_insert_timeout | 300 |
| have_statement_timeout | YES |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_print_lock_wait_timeout_info | OFF |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| mysqlx_connect_timeout | 30 |
| mysqlx_idle_worker_thread_timeout | 60 |
| mysqlx_interactive_timeout | 28800 |
| mysqlx_port_open_timeout | 0 |
| mysqlx_read_timeout | 30 |
| mysqlx_wait_timeout | 28800 |
| mysqlx_write_timeout | 60 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| replica_net_timeout | 60 |
| rpl_stop_replica_timeout | 31536000 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 60 |
| thread_pool_idle_timeout | 60 |
| wait_timeout | 120 |
| wsrep_RSU_commit_timeout | 5000 |
±------------------------------------±---------+
25 rows in set (0.00 sec)

[Pep_Pla]

Hi,

What is the value of max_allowed_packet?

I recommend you test with higher values of net_read_timeout and wait_timeout.

Pep

[johnmorganhouse]

Thanks for replying so quickly.

mysql> show variables like ‘max_allowed_packet’;
±-------------------±---------+
| Variable_name | Value |
±-------------------±---------+
| max_allowed_packet | 67108864 |
±-------------------±---------+
1 row in set (0.01 sec)

[Pep_Pla]

Hi,

Looks like this are already recommendations by Amazon

Troubleshooting migration tasks in AWS Database Migration Service - AWS...

I would increase the values of the timeouts and change max_allowed_packet.

Pep

[johnmorganhouse]

Howdy!

Below are the settings currently enabled for max_allowed_packet, all the timeout variables and all the plugins. I have also included the config from my.cnf from my test machine. So far, none of these settings or adjustments have worked to allow AWS DMS to connect and capture tables.

Any ideas will be welcomed.

Best,

John

[Pep_Pla]

Hi John,

I think the problem is not that DMS can’t connect.

2023-02-14T21:58:36:429792 [METADATA_MANAGE ]I: Going to connect to ODBC connection string: DRIVER={MySQL ODBC 8.0 Unicode Driver};SERVER=rds-cluster-1.cluster-choh9m88rfg0.us-west-1.rds.amazonaws.com;PORT=3306;FOUND_ROWS=1;CHARSET=binary;NO_LOCALE=1;ENABLE_LOCAL_INFILE=1;initstmt=SET FOREIGN_KEY_CHECKS=0; executeTimeout=3600;UID=admin; (odbc_endpoint_imp.c:1993)

The problem is that the connection is closed while a query is executed:

2023-02-14T22:05:37:250981 [METADATA_MANAGE ]E: RetCode: SQL_ERROR SqlState: 08S01 NativeError: 2013 Message: [MySQL][ODBC 8.0(w) Driver][mysqld-8.0.28-19.1]Lost connection to MySQL server during query [1022502] (ar_odbc_stmt.c:3932)

I suggest trying these values in the database server:

max_allowed_packet=671088640
innodb_lock_wait_timeout=600
net_read_timeout=600
wait_timeout=600

You should also check the error log in the database server. And the processlist at the time of the execution.

Thanks

Pep

[johnmorganhouse]

Hi Pep,

Thanks so much for your assistance so far. We have the database team at AWS stumped and thanks to your help we are now seeing table data in our database migration task following the addition of:

max_allowed_packet=671088640 innodb_lock_wait_timeout=600 net_read_timeout=600 wait_timeout=600

However, once all the table data loads, and after a 15 minute delay, the connection is lost again. Can you see anything in this log snippet that is useful?

> <br><br>2023-02-17T16:32:38:352642 [SOURCE_UNLOAD ]D: Added all columns to table_def successfully (odbc_util.c:1382)<br><br> |
> - |
> <br><br>2023-02-17T16:47:41:879609 [SOURCE_UNLOAD ]D: Got table indexes successfully (odbc_util.c:1387)<br><br> |
> <br><br>2023-02-17T17:02:43:001667 [SOURCE_UNLOAD ]D: Got table PK successfully (odbc_util.c:1417)<br><br> |
> <br><br>2023-02-17T17:02:43:001714 [SOURCE_UNLOAD ]D: No Index or PK added to table_def (odbc_util.c:1542)<br><br> |
> <br><br>2023-02-17T17:17:44:119845 [SOURCE_UNLOAD ]D: Table cardinality is 0 (odbc_util.c:1578)<br><br> |
> <br><br>2023-02-17T17:17:44:119888 [SOURCE_UNLOAD ]T: +===== Table Metadata ========+ (odbc_util.c:713)<br><br> |
> <br><br>2023-02-17T17:17:44:119895 [SOURCE_UNLOAD ]T: Table: socialos.stellar Column Count: 18 (odbc_util.c:721)<br><br> |
> <br><br>2023-02-17T17:17:44:119901 [SOURCE_UNLOAD ]T: (odbc_util.c:722)<br><br> |
> <br><br>2023-02-17T17:17:44:119906 [SOURCE_UNLOAD ]T: No Name Type Length Precision Scale Nullable? Attributes (odbc_util.c:723)<br><br> |
> <br><br>2023-02-17T17:17:44:119912 [SOURCE_UNLOAD ]T: --- ----------------------------- ------------------------ ------ --------- ----- --------- ---------- (odbc_util.c:724)<br><br> |
> <br><br>2023-02-17T17:17:44:119925 [SOURCE_UNLOAD ]T: 001. stellar kAR_DATA_TYPE_I8 19 19 0 Yes Numeric (odbc_util.c:736)<br><br> |
> <br><br>2023-02-17T17:17:44:119933 [SOURCE_UNLOAD ]T: 002. amount kAR_DATA_TYPE_NUMERIC 23 20 7 Yes Numeric,PackedDec (odbc_util.c:736)<br><br> |
> <br><br>2023-02-17T17:17:44:119940 [SOURCE_UNLOAD ]T: 003. asset_code kAR_DATA_TYPE_WSTR 12 0 0 Yes Text,Wide (odbc_util.c:736)<br><br> |
> <br><br>2023-02-17T17:17:44:119947 [SOURCE_UNLOAD ]T: 004. asset_issuer kAR_DATA_TYPE_WSTR 60 0 0 Yes Text,Wide (odbc_util.c:736)<br><br> |
> <br><br>2023-02-17T17:17:44:119953 [SOURCE_UNLOAD ]T: 005. asset_type kAR_DATA_TYPE_WSTR 25 0 0 Yes Text,Wide (odbc_util.c:736)<br><br> |
> <br><br>2023-02-17T17:17:44:119960 [SOURCE_UNLOAD ]T: 006. created_at kAR_DATA_TYPE_TIMESTAMP 19 19 0 Yes Time (odbc_util.c:736)<br><br> |
> <br><br>2023-02-17T17:17:44:119967 [SOURCE_UNLOAD ]T: 007. from kAR_DATA_TYPE_WSTR 60 0 0 Yes Text,Wide (odbc_util.c:736)<br><br> |
> <br><br>2023-02-17T17:17:44:119973 [SOURCE_UNLOAD ]T: 008. funder kAR_DATA_TYPE_WSTR 60 0 0 Yes Text,Wide (odbc_util.c:736)<br><br> |
> <br><br>2023-02-17T17:17:44:119980 [SOURCE_UNLOAD ]T: 009. id kAR_DATA_TYPE_WSTR 20 0 0 Yes Text,Wide (odbc_util.c:736)<br><br> |
> <br><br>2023-02-17T17:17:44:119986 [SOURCE_UNLOAD ]T: 010. paging_token kAR_DATA_TYPE_WSTR 20 0 0 Yes Text,Wide (odbc_util.c:736)<br><br> |
> <br><br>2023-02-17T17:17:44:119993 [SOURCE_UNLOAD ]T: 011. starting_balance kAR_DATA_TYPE_NUMERIC 23 20 7 Yes Numeric,PackedDec (odbc_util.c:736)<br><br> |
> <br><br>2023-02-17T17:17:44:120000 [SOURCE_UNLOAD ]T: 012. to kAR_DATA_TYPE_WSTR 60 0 0 Yes Text,Wide (odbc_util.c:736)<br><br> |
> <br><br>2023-02-17T17:17:44:120006 [SOURCE_UNLOAD ]T: 013. transaction_hash kAR_DATA_TYPE_WSTR 64 0 0 Yes Text,Wide (odbc_util.c:736)<br><br> |
> <br><br>2023-02-17T17:17:44:120013 [SOURCE_UNLOAD ]T: 014. type kAR_DATA_TYPE_WSTR 25 0 0 Yes Text,Wide (odbc_util.c:736)<br><br> |
> <br><br>2023-02-17T17:17:44:120053 [SOURCE_UNLOAD ]T: 015. type_i kAR_DATA_TYPE_I4 10 10 0 Yes Numeric (odbc_util.c:736)<br><br> |
> <br><br>2023-02-17T17:17:44:120060 [SOURCE_UNLOAD ]T: 016. links kAR_DATA_TYPE_BLOB 1048576 -4 0 Yes Opaque (odbc_util.c:736)<br><br> |
> <br><br>2023-02-17T17:17:44:120067 [SOURCE_UNLOAD ]T: 017. transaction kAR_DATA_TYPE_BLOB 1048576 -4 0 Yes Opaque (odbc_util.c:736)<br><br> |
> <br><br>2023-02-17T17:17:44:120074 [SOURCE_UNLOAD ]T: 018. memo kAR_DATA_TYPE_WSTR 100 0 0 Yes Text,Wide (odbc_util.c:736)<br><br> |
> <br><br>2023-02-17T17:17:44:120081 [SOURCE_UNLOAD ]T: (odbc_util.c:742)<br><br> |
> <br><br>2023-02-17T17:17:44:120086 [SOURCE_UNLOAD ]T: (odbc_util.c:744)<br><br> |
> <br><br>2023-02-17T17:17:44:120090 [SOURCE_UNLOAD ]T: PK information: Name= (odbc_util.c:746)<br><br> |
> <br><br>2023-02-17T17:17:44:120096 [SOURCE_UNLOAD ]T: --------------- (odbc_util.c:747)<br><br> |
> <br><br>2023-02-17T17:17:44:120101 [SOURCE_UNLOAD ]T: (odbc_util.c:749)<br><br> |
> <br><br>2023-02-17T17:17:44:120105 [SOURCE_UNLOAD ]T: No Segment Column Name Position (odbc_util.c:751)<br><br> |
> <br><br>2023-02-17T17:17:44:120111 [SOURCE_UNLOAD ]T: --- ----------------------------- -------- (odbc_util.c:753)<br><br> |
> <br><br>2023-02-17T17:17:44:120116 [SOURCE_UNLOAD ]T: (odbc_util.c:761)<br><br> |
> <br><br>2023-02-17T17:17:44:120120 [SOURCE_UNLOAD ]T: +== End of Table Metadata ====+ (odbc_util.c:762)<br><br> |
> <br><br>2023-02-17T17:17:44:120125 [SOURCE_UNLOAD ]T: (odbc_util.c:763)<br><br> |
> <br><br>2023-02-17T17:17:44:120133 [SOURCE_UNLOAD ]I: set_columns_orig_type for socialos.stellar (mysql_endpoint_metadata.c:572)<br><br> |
> <br><br>2023-02-17T17:32:45:240237 [SOURCE_UNLOAD ]D: RetCode: SQL_ERROR SqlState: 08S01 NativeError: 2013 Message: [MySQL][ODBC 8.0(w) Driver][mysqld-8.0.28-19.1]Lost connection to MySQL server during query [1022502] (ar_odbc_stmt.c:2738)<br><br> |
> <br><br>2023-02-17T17:32:45:240289 [SOURCE_UNLOAD ]D: Network error encountered (ar_odbc_util.c:1015)<br><br> |
> <br><br>2023-02-17T17:32:45:240365 [SOURCE_UNLOAD ]D: Cannot refresh source table (1) metadata [1022506] (endpointshell.c:3661)<br><br> |
> <br><br>2023-02-17T17:32:45:240381 [SOURCE_UNLOAD ]D: Metadata connection error [1022506] (mysql_endpoint_imp.c:115)<br><br> |
> <br><br>2023-02-17T17:32:45:240390 [SOURCE_UNLOAD ]D: Endpoint is disconnected [1020414] (endpointshell.c:3837)<br><br> |
> <br><br>2023-02-17T17:32:45:240400 [SOURCE_UNLOAD ]D: Error executing command [1020414] (streamcomponent.c:1980)<br><br> |
> <br><br>2023-02-17T17:32:45:240419 [SOURCE_UNLOAD ]D: Stream component 'st_1_4QQINAP5JPE22JCVTMYUYPYXV6JA7HKKIEPTQPA' terminated [1020414] (subtask.c:1594)<br><br> |
> <br><br>2023-02-17T17:32:45:240453 [SOURCE_UNLOAD ]D: Entering 'mysql_endpoint_free_imp' (mysql_endpoint_imp.c:908)<br><br> |
> <br><br>2023-02-17T17:32:45:240691 [TASK_MANAGER ]I: Task - 3FXM6ON6SQWYTAIYI5NLPC4MCNKP7NKLZSXNGFY is in ERROR state, updating starting status to AR_NOT_APPLICABLE (repository.c:5102)<br><br> |
> <br><br>2023-02-17T17:32:45:240469 [TASK_MANAGER ]E: Task error notification received from subtask 1, thread 0 [1020414] (replicationtask.c:2883)<br><br> |
> <br><br>2023-02-17T17:32:45:240484 [TASK_MANAGER ]E: RetCode: SQL_ERROR SqlState: 08S01 NativeError: 2013 Message: [MySQL][ODBC 8.0(w) Driver][mysqld-8.0.28-19.1]Lost connection to MySQL server during query; Cannot refresh source table (1) metadata; Metadata connection error; Endpoint is disconnected; Error executing command; Stream component failed at subtask 1, component st_1_4QQINAP5JPE22JCVTMYUYPYXV6JA7HKKIEPTQPA; Stream component 'st_1_4QQINAP5JPE22JCVTMYUYPYXV6JA7HKKIEPTQPA' terminated [1020414] (replicationtask.c:2891)<br><br> |
> <br><br>2023-02-17T17:32:45:254022 [TASK_MANAGER ]E: Task '3FXM6ON6SQWYTAIYI5NLPC4MCNKP7NKLZSXNGFY' encountered a recoverable error, retry attempt # 1 (repository.c:5185)<br><br> |
> <br><br>2023-02-17T17:32:45:261034 [SOURCE_CAPTURE ]D: Entering 'mysql_endpoint_free_imp' (mysql_endpoint_imp.c:908)<br><br> |
> <br><br>2023-02-17T17:32:45:264131 [SOURCE_UNLOAD ]D: Leaving 'mysql_endpoint_free_imp' (mysql_endpoint_imp.c:922)<br><br> |
> <br><br>2023-02-17T17:32:45:240237 [SOURCE_UNLOAD ]E: RetCode: SQL_ERROR SqlState: 08S01 NativeError: 2013 Message: [MySQL][ODBC 8.0(w) Driver][mysqld-8.0.28-19.1]Lost connection to MySQL server during query [1022502] (ar_odbc_stmt.c:2738)<br><br> |
> <br><br>2023-02-17T17:32:45:240365 [SOURCE_UNLOAD ]E: Cannot refresh source table (1) metadata [1022506] (endpointshell.c:3661)<br><br> |
> <br><br>2023-02-17T17:32:45:240381 [SOURCE_UNLOAD ]E: Metadata connection error [1022506] (mysql_endpoint_imp.c:115)<br><br> |
> <br><br>2023-02-17T17:32:45:240390 [SOURCE_UNLOAD ]E: Endpoint is disconnected [1020414] (endpointshell.c:3837)<br><br> |
> <br><br>2023-02-17T17:32:45:240400 [SOURCE_UNLOAD ]E: Error executing command [1020414] (streamcomponent.c:1980)<br><br> |
> <br><br>2023-02-17T17:32:45:240413 [TASK_MANAGER ]E: Stream component failed at subtask 1, component st_1_4QQINAP5JPE22JCVTMYUYPYXV6JA7HKKIEPTQPA [1020414] (subtask.c:1414)<br><br> |
> <br><br>2023-02-17T17:32:45:240419 [SOURCE_UNLOAD ]E: Stream component 'st_1_4QQINAP5JPE22JCVTMYUYPYXV6JA7HKKIEPTQPA' terminated [1020414] (subtask.c:1594)<br><br> |
> <br><br>2023-02-17T17:32:45:303128 [SOURCE_CAPTURE ]D: Leaving 'mysql_endpoint_free_imp' (mysql_endpoint_imp.c:922)<br><br> |
> <br><br>2023-02-17T17:32:52:220973 [TASK_MANAGER ]I: Subtask #1 ended (replicationtask_util.c:589)<br><br> |
> <br><br>2023-02-17T17:32:52:221032 [TASK_MANAGER ]I: Task management thread terminated (replicationtask.c:3959)<br><br> |
> <br><br>2023-02-17T17:32:54:279330 [AT_GLOBAL ]I: Closing log file at Fri Feb 17 17:32:54 2023 (at_logger.c:2766)<br><br> |

Thanks in advance,

John

[Pep_Pla]

Hi,

It looks like this table has two BLOBs. As this is a migration, I think it makes sense to test the highest possible value for max_allowed_packet.

Try with this:
max_allowed_packet= 1073741824

Thanks,

[johnmorganhouse]

Hey there Pep!

I wanted to say thanks to you and the community for taking time out of your days to reply and assist me. I have learned so much in this process and have you folks to thank.

Of course there were a thousand things that needed to be absolutely correct, but there was one tiny thing that was causing all of my communications problems.

I am migrating out of IBM to AWS and have an ipsec Site-to-Site VPN making the connection between the two. On the IBM side, we have Brocade Vyatta routers handling the tunnel. I had the MTU set on the vti interfaces, but I also needed to set the tcp-mss limit. Once I set this, traffic was able to return.

set interfaces vti vti0 ip tcp-mss limit 1350
set interfaces vti vti0 mtu 1436

It was pretty strange behavior in that I could establish ssh connections and scp files from AWS to IBM, but ultimately, anything that required bi-directional communication would fail because the tcp-mss size was bloated due to the ipsec overhead. Once I capped it at 1350, all my problems were resolved.

And notably, no special timeouts or tweaks were needed on the Percona side. AWS DMS works well with it and it’s amazing to see it all working.

Thanks again Pep!

Best,

John