When using 'incremental materializations' getting "Use multi=True when executing multiple statements" error

[teno-justos]

Using MySQL 8.0 on AWS RDS. Table materializations work fine. However, when using "incremental materializations", I am getting Use multi=True when executing multiple statements error, since DBT automatically creates a delete and insert statements when the unique_key constraint is used in the config under the current dbt model. In the README says incremental mat. are supported. How do you achieve it?

[joeschmid]

Also running into this same issue, but when using MySQL 5.7. I observed the same behavior as described in the original description on this issue from @teno-justos: incremental materialization does run successfully if a unique_key constraint is not provided, but fails when unique_key is included in the config for the dbt model. This makes sense: including unique_key is what causes a DELETE statement to be generated before the INSERT of records from a temporary table into the target table, i.e. multiple SQL statements. Apparently the mysql connector won't execute multiple statements unless you pass multi=True to .execute().

[dbeatty10]

@teno-justos and @joeschmid thank you for noticing this error and reporting it!

I was able to reproduce the issue with a simple model like the following:

{{
    config(
        materialized='incremental',
        unique_key='id',
    )
}}

select * from {{ source('raw', 'seed') }}
{% if is_incremental() %}
where id > (select max(id) from {{ this }})
{% endif %}

I will start working on a fix.

[joeschmid]

Thanks so much @dbeatty10! If you need any help in testing a fix, we'd be happy to lend a hand on that!

Also, if it were easy to support the most recent release of dbt as part of this work that'd be great (i.e. release the fix with support for dbt 1.0.0 all at once), but don't want to add extra work -- whatever saves you time and effort.

[dbeatty10]

@joeschmid I suspect dbt 1.0.0 will take a fair amount of work, so prioritizing getting this one solved and released first.

Could you try out this release candidate and see if it fixes the issue you had with incremental models using a unique_key? dbt-mysql==0.19.0.1rc1

[joeschmid]

@dbeatty10 just tested this successfully and confirmed the fix. I was able to recreate the issue, observe it fail (with Use multi=True when executing multiple statements error), install dbt-mysql==0.19.0.1rc1 and have several incremental builds succeed for the exact same model. Thanks so much for the fix and for getting this out so quickly. Very much appreciated!