Pre_hook calling procedure breaks mysql connection

[dbeatty10]

Transfer of https://github.com/dbt-labs/dbt-core/issues/7757 opened by @Randyhall91.

Is this a new bug in dbt-core?

• [X] I believe this is a new bug in dbt-core
• [X] I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

The issue I'm having is, after creating a procedure in mysql and then calling it in a pre_hook the rest of the dbt file fails with the message " mysql adapter: MySQL error: MySQL Connection not available." The pre_hook runs successfully but anything after it resolves crashes with mysql connection failure.

Expected Behavior

I expect the pre_hook that uses a stored procedure to fire and the table to materialize as normal.

Steps To Reproduce

1. Created stored procedure in mysql. Testing with a test procedure shows it breaks regardless of procedures action. DELIMITER // CREATE PROCEDURE test( IN table_name VARCHAR(255), ) BEGIN select(table_name); END//

DELIMITER ;

2. Use pre_hook to call the stored procedure {{ config( pre_hook=[ "use {{ var('schema') }};", "call test('work_order_notes');" ], )}}
3. dbt run

Relevant log output

19:22:49.053883 [debug] [Thread-1  ]: mysql adapter: MySQL error: MySQL Connection not available.
19:22:49.053883 [debug] [Thread-1  ]: On model.data.work_orders: ROLLBACK
19:22:49.053883 [debug] [Thread-1  ]: Failed to rollback 'model.data.work_orders'
19:22:49.054926 [debug] [Thread-1  ]: finished collecting timing info
19:22:49.054926 [debug] [Thread-1  ]: On model.data.work_orders: Close
19:22:49.055938 [debug] [Thread-1  ]: Database Error in model work_orders (models\marts\work_orders.sql)
  MySQL Connection not available.

Environment

- OS: Windows 11
- Python: 3.9
- dbt-core: 1.1.5

Which database adapter are you using with dbt?

other (mention it in "Additional Context")

Additional Context

mysql: 1.1.0

I've tested this stored procedure in mysql workbench without any issues and this part of the code works fine but when added to the pre_hook in dbt the following table fails to be created.

Without the pre_hook the table generates with no issues.

[hugochinchilla]

same issue here

[hugochinchilla]

Model:

{{
  config(
    materialized='table',
    pre_hook=[
        drop_index(['date', 'user_id'])
    ]
  )
}}

SELECT 1 FROM DUAL

Macros:

{% macro drop_index(columns) %}
USE reporting;
CALL drop_index_if_exists('example','example_table');
{% endmacro %}

Logs:

11:19:01.331387 [debug] [Thread-1 (]: Using mysql connection "model.scoolinary.lessons_completed"
11:19:01.332020 [debug] [Thread-1 (]: On model.scoolinary.lessons_completed: /* {"app": "dbt", "dbt_version": "1.7.14", "profile_name": "scoolinary", "target_name": "prod", "node_id": "model.scoolinary.lessons_completed"} */

        USE reporting;
CALL drop_index_if_exists('example','example_table');

11:19:01.409351 [debug] [Thread-1 (]: SQL status: SUCCESS 0 in 0.0 seconds
11:19:01.426447 [debug] [Thread-1 (]: Using mysql connection "model.scoolinary.lessons_completed"
11:19:01.427109 [debug] [Thread-1 (]: On model.scoolinary.lessons_completed: /* {"app": "dbt", "dbt_version": "1.7.14", "profile_name": "scoolinary", "target_name": "prod", "node_id": "model.scoolinary.lessons_completed"} */

  create temporary table
    `reporting_reporting`.`lessons_completed__dbt_tmp`

      as

    (

SELECT 1 FROM DUAL
    )

11:19:01.427764 [debug] [Thread-1 (]: mysql adapter: MySQL error: MySQL Connection not available.
11:19:01.428352 [debug] [Thread-1 (]: On model.scoolinary.lessons_completed: ROLLBACK
11:19:01.430010 [debug] [Thread-1 (]: Failed to rollback 'model.scoolinary.lessons_completed'
11:19:01.430908 [debug] [Thread-1 (]: Timing info for model.scoolinary.lessons_completed (execute): 11:19:00.939515 => 11:19:01.430564
11:19:01.431514 [debug] [Thread-1 (]: On model.scoolinary.lessons_completed: Close
11:19:01.437477 [debug] [Thread-1 (]: Database Error in model lessons_completed (models/reports/lessons_completed.sql)
  MySQL Connection not available.
11:19:01.438164 [debug] [Thread-1 (]: Sending event: {'category': 'dbt', 'action': 'run_model', 'label': 'dafe694c-8ead-403a-a4f2-7fc2efedefae', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7758688fd940>]}
11:19:01.439027 [error] [Thread-1 (]: 1 of 1 ERROR creating sql incremental model reporting_reporting.lessons_completed  [ERROR in 0.51s]
11:19:01.439957 [debug] [Thread-1 (]: Finished running node model.scoolinary.lessons_completed