Troubleshoot MySQL ingestion issues

Preview

The MySQL connector is in Public Preview. Contact your Databricks account team to request access.

info

This page contains references to the term slave, a term that Databricks doesn't use. When the term is removed from the third-party software, we'll remove it from this page.

General pipeline troubleshooting

The troubleshooting steps in this section apply to all managed ingestion pipelines in Lakeflow Connect.

If a pipeline fails while running, click the step that failed and confirm whether the error message provides sufficient information about the nature of the error.

You can also check and download the cluster logs from the pipeline details page by clicking Update details in the right-hand panel, then Logs. Scan the logs for errors or exceptions.

Connector-specific troubleshooting

The troubleshooting steps in this section are specific to the MySQL connector.

Connection issues

Test Connection button fails

Problem: The Test Connection button fails in the UI, even though credentials are correct.

Cause: This is a known limitation for MySQL users with sha256_password or caching_sha2_password authentication plugins.

Solution: You can safely ignore this error and proceed with creating the connection. To confirm that your credentials work, run the following in the MySQL command-line client or another MySQL client tool:

Bash

mysql -h your-mysql-host -u lakeflow_connect_user -p

If you can connect with the MySQL client, the connection will work for ingestion despite the Test Connection failure.

Can't connect to MySQL server

Problem: Connection fails with errors like "Can't connect to MySQL server" or "Connection refused".

Possible causes and solutions:

1. Network connectivity:

   • Verify firewall rules allow traffic from Databricks IP ranges on port 3306.
   • Check security groups (AWS), NSGs (Azure), or firewall rules (GCP).
   • Verify VPC peering or network connectivity is properly configured.

2. MySQL not listening on expected interface:

   • Check the MySQL bind-address setting.
   • Ensure MySQL is listening on 0.0.0.0 or the specific IP address you're connecting to.

3. Wrong host or port:

   • Verify the hostname or IP address is correct.
   • Confirm the port (default 3306) is correct.

Access denied for user

Problem: Connection fails with "Access denied for user 'lakeflow_connect_user'@'host'".

Possible causes and solutions:

1. Wrong password: Verify the password is correct

2. User not created or wrong host:

   SQL

   -- Check if user exists
   SELECT User, Host FROM mysql.user WHERE User = 'lakeflow_connect_user';
   
   -- User might need to be created for specific host or '%'
   CREATE USER 'lakeflow_connect_user'@'%' IDENTIFIED BY 'password';

3. Missing privileges:

   SQL

   -- Grant required privileges
   GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'lakeflow_connect_user'@'%';
   GRANT SELECT ON your_database.* TO 'lakeflow_connect_user'@'%';
   FLUSH PRIVILEGES;

Binary log issues

Binary logging not enabled

Problem: Pipeline fails with error indicating binary logging is not enabled.

Solution: Enable binary logging on your MySQL server:

• RDS/Aurora: Configure the parameter group and restart.
• Azure MySQL: Configure server parameters and restart.
• GCP Cloud SQL: Configure database flags and restart.
• EC2: Edit my.cnf and add log-bin=mysql-bin, then restart MySQL.

Verify with:

SQL

SHOW VARIABLES LIKE 'log_bin';
-- Should return ON

Wrong binlog format

Problem: Pipeline fails because binlog format is not ROW.

Solution: Set binlog format to ROW:

• RDS/Aurora: Set binlog_format=ROW in the parameter group.
• Azure MySQL: Set binlog_format=ROW in server parameters.
• GCP Cloud SQL: Set binlog_format=ROW in database flags.
• EC2: Add binlog_format=ROW to my.cnf.

Restart MySQL and verify:

SQL

SHOW VARIABLES LIKE 'binlog_format';
-- Should return ROW

Binlog purged before processing

Problem: Pipeline fails with error about missing binlog files.

Cause: Binary logs were purged before the ingestion gateway could process them.

Solution:

1. Increase binlog retention:

   SQL

   -- RDS/Aurora:
   CALL mysql.rds_set_configuration('binlog retention hours', 168); -- 7 days
   
   -- EC2 (MySQL 8.0):
   SET GLOBAL binlog_expire_logs_seconds = 604800; -- 7 days
   
   -- EC2 (MySQL 5.7):
   SET GLOBAL expire_logs_days = 7;

2. Perform a full refresh of affected tables:

   • See Fully refresh target tables

3. Ensure the ingestion gateway runs continuously

Prevention: Configure adequate binlog retention (7 days recommended). Setting a lower value might cause binlogs to be cleaned up before the ingestion gateway processes them.

Ingestion gateway issues

Gateway not starting

Problem: Ingestion gateway fails to start or immediately fails.

Possible causes and solutions:

1. Connection issues: Verify the connection works (see Connection issues).

2. Insufficient compute resources:

   • Check cluster creation permissions and policies.

3. Staging volume issues:

   • Verify you have CREATE VOLUME privileges on the staging schema.
   • Check that the staging catalog isn't a foreign catalog.

Gateway running but not capturing changes

Problem: Gateway is running but new changes aren't being captured.

Possible causes and solutions:

1. Binlog retention too short:

   • Increase binlog retention.
   • Monitor binlog files to ensure they're not purged too quickly.

2. Network interruptions:

   • Check for network connectivity issues.
   • Review gateway logs for connection errors.

Ingestion pipeline issues

Tables not being ingested

Problem: Some tables are not being ingested even though they're selected.

Possible causes and solutions:

1. Spatial data types:

   • Tables with spatial columns can't be ingested.
   • Exclude these tables from ingestion.

2. Missing privileges:

   • Verify the replication user has SELECT on all source tables.
   SQL

   SHOW GRANTS FOR 'lakeflow_connect_user'@'%';

Checking logs

To diagnose issues, check the following logs:

1. Gateway logs:

   • Navigate to the gateway pipeline in the Databricks UI.
   • Click on the latest run.
   • Review cluster logs and DLT event logs.

2. Pipeline logs:

   • Navigate to the ingestion pipeline.
   • Click on the latest run.
   • Review DLT event logs and any error messages.

3. MySQL logs:

   • Check the MySQL error log for connection or replication issues.
   • Review binlog files if position errors occur.

Get help

If you continue to experience issues:

1. Collect relevant information:

   • Error messages from pipeline logs.
   • Gateway cluster logs.
   • MySQL version and deployment type.
   • Source configuration (binlog settings, retention).

2. Contact Databricks support with the collected information.

3. During Public Preview, you can also contact your Databricks account team.