Fix dbt-core MySQL Connection Issues After Upgrade

The dbt-core MySQL connection issue after a version upgrade typically stems from compatibility conflicts between dbt versions, MySQL connector dependencies, or configuration changes. When dbt attempts to establish connections but fails silently after an upgrade, it often indicates that while the commands execute without error, the underlying database connection isn’t being properly established due to dependency mismatches or configuration incompatibilities.

Contents

• Understanding the Root Cause
• Troubleshooting Steps
• Debugging Techniques
• Configuration and Dependency Management
• MySQL-Specific Considerations
• Long-Term Solutions

Understanding the Root Cause

The connection issues you’re experiencing likely stem from several common problems that occur after dbt-core version upgrades:

Dependency Incompatibilities: When dbt-core is upgraded, it may require specific versions of the MySQL connector (such as mysql-connector-python or PyMySQL). The dbt Community Forum reports similar issues where “the latest dbt-core is incompatible with the latest mysql plugin.”

SSL and Authentication Changes: MySQL 8.0 introduced significant changes to SSL connection handling. As noted in the MySQL 8.0 release notes, “During the reconnection process, the primary hung when trying to create the connection, thus blocking the single XCom thread” due to changes in SSL_connect() implementation.

Global Data Dictionary Changes: MySQL 8.0 incorporates a global data dictionary containing information about database objects in transactional tables, which can affect how clients connect and query the database source.

The debug output showing “Acquiring new mysql connection” but stopping without errors suggests that dbt is attempting connections but failing silently, often due to these underlying compatibility issues.

Troubleshooting Steps

1. Verify MySQL Connector Compatibility

Check your current MySQL connector version and ensure it’s compatible with your dbt-core version:

pip show mysql-connector-python
pip show PyMySQL

If you encounter the issue described in the dbt Community Forum, you may need to explicitly pin or update the MySQL connector version in your requirements.txt file.

2. Force-Reinstall Dependencies

Since you’ve already recreated the virtual environment, ensure all dependencies are properly installed with compatible versions:

pip install --upgrade dbt-core dbt-mysql
pip install --force-reinstall mysql-connector-python==8.0.33  # Use a specific compatible version

3. Check MySQL Version Compatibility

Verify your MySQL server version and compare it with the supported versions in your dbt-core version. The MySQL Upgrade Checker can help identify potential compatibility issues before they cause connection problems.

4. Test Connection Outside of dbt

Use MySQL client tools to verify the connection works independently:

mysql -h localhost -u your_username -p your_database

If this fails, the issue is with MySQL server configuration or network settings, not dbt itself.

Debugging Techniques

1. Enable Detailed Logging

Increase dbt’s logging level to capture more detailed connection information:

dbt debug --log-format=json --log-path=./debug_logs

2. Use MySQL Debug Mode

According to MySQL documentation, you can configure MySQL with debug options to capture detailed connection information:

# For MySQL 8.0
mysql --debug=d:t:i:o,/tmp/mysql_debug.log

3. Connection Testing Script

Create a simple Python script to test the connection directly:

import mysql.connector
from mysql.connector import Error

try:
    connection = mysql.connector.connect(
        host='localhost',
        database='your_database',
        user='your_username',
        password='your_password'
    )
    if connection.is_connected():
        print("Successfully connected to MySQL database")
except Error as e:
    print(f"Error connecting to MySQL: {e}")

Configuration and Dependency Management

1. Profile.yml Configuration Update

Your profile.yml may need updates for compatibility. Ensure it includes all required parameters:

your_target:
  type: mysql
  host: localhost
  port: 3306
  user: your_username
  password: your_password
  database: your_database
  schema: your_schema
  # Add these for MySQL 8.0 compatibility
  # ssl_disabled: true
  # charset: utf8mb4

2. Requirements.txt Management

Create a requirements.txt file with pinned versions to ensure compatibility:

dbt-core==1.7.19
dbt-mysql==1.7.19
mysql-connector-python==8.0.33
PyMySQL==1.1.0

3. Virtual Environment Isolation

Ensure complete isolation by removing all dbt-related packages:

pip uninstall dbt-core dbt-mysql mysql-connector-python PyMySQL
pip install -r requirements.txt

MySQL-Specific Considerations

1. Authentication Plugin Compatibility

MySQL 8.0 uses caching_sha2_password by default, which may require explicit configuration in your connection string. Add this to your profile.yml if needed:

auth_plugin: mysql_native_password

2. SSL Configuration Issues

The SSL changes in MySQL 8.0.27 can cause connection hangs. Try disabling SSL temporarily for testing:

ssl_disabled: true

3. Plugin Loading Issues

As noted in MySQL documentation, “InnoDB tablespace encryption requires that the keyring plugin to be used be loaded prior to InnoDB initialization.” Ensure your MySQL server configuration doesn’t have plugin loading conflicts.

Long-Term Solutions

1. Version Pinning Strategy

Implement a version pinning strategy in your requirements.txt to prevent automatic upgrades that break compatibility:

# Freeze exact versions
dbt-core==1.7.19
dbt-mysql==1.7.19
mysql-connector-python==8.0.33

2. Testing Environment Parity

Ensure your development environment matches production MySQL versions and configurations. Use the MySQL Upgrade Checker to validate compatibility before upgrades.

3. Gradual Upgrade Process

When upgrading dbt-core in the future:

1. Create a test environment
2. Upgrade dbt-core and dependencies
3. Run comprehensive tests
4. Verify connection stability
5. Only then upgrade production

As one user in the dbt Community Forum discovered, the “latest dbt-core is incompatible with the latest mysql plugin,” making gradual testing essential.

Conclusion

The connection issues you’re experiencing are common after dbt-core version upgrades and typically stem from compatibility conflicts between dbt versions, MySQL connector dependencies, or configuration changes. The key steps to resolve this include verifying MySQL connector compatibility, forcing dependency reinstallation, enabling detailed logging, and updating your configuration files for MySQL 8.0 compatibility requirements.

For immediate resolution, focus on ensuring your MySQL connector version (mysql-connector-python) is compatible with your dbt-core version 1.7.19, and consider temporarily disabling SSL or adjusting authentication plugins if using MySQL 8.0. Implement version pinning in your requirements.txt to prevent future compatibility issues, and always test upgrades in a separate environment before applying them to production.

Sources

1. dbt-core having issues with establishing connection with mysql - Show and Tell - dbt Community Forum
2. Changes in MySQL 8.0 - Oracle Documentation
3. Changes in MySQL 8.0.42 (2025-04-15) - Oracle Documentation
4. Checking Your MySQL Server Before an Upgrade with MySQL Shell - Another Boring Tech Blog
5. Debugging a MySQL Client - MySQL 8.0 Documentation
6. Changes in MySQL 5.7 - Oracle Documentation