Skip to main content
Last updated on

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.

For general troubleshooting guidance that applies to all managed ingestion pipelines, see Troubleshoot managed ingestion pipelines.

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:
    -- Minimum: one day (24 hours), recommended: seven days (168 hours)
    CALL mysql.rds_set_configuration('binlog retention hours', 168);

    -- EC2 (MySQL 8.0):
    -- Minimum: one day (86400 seconds), recommended: seven days (604800 seconds)
    SET GLOBAL binlog_expire_logs_seconds = 604800;

    -- EC2 (MySQL 5.7):
    -- Minimum: one day, recommended: seven days
    SET GLOBAL expire_logs_days = 7;

  2. Perform a full refresh of affected tables:

  3. Ensure the ingestion gateway runs continuously

Prevention: Configure adequate binlog retention (minimum one day, seven 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.

TLS server certificate errors​

For troubleshooting TLS server certificate validation errors, see Troubleshoot TLS certificate errors.

On this page