incremental materialisation always error with mysql 8

[jaredchurch]

Describe the bug

1. dbt run --model hours --full-refresh works.
2. dbt run --model hours fails & table is not updated

Error message:
~/dbt/dwh# dbt run --model hours
Running with dbt=0.19.0
[WARNING]: Configuration paths exist in your dbt_project.yml file which do not apply to any resources.
There are 2 unused configuration paths:

• models.dwh.gps_ingest
• models.dwh.hours

Found 6 models, 11 tests, 0 snapshots, 0 analyses, 140 macros, 0 operations, 0 seed files, 0 sources, 0 exposures

06:20:19 | Concurrency: 1 threads (target='dev')
06:20:19 |
06:20:19 | 1 of 1 START incremental model dev_hours_ingest.hours................ [RUN]
06:20:19 | 1 of 1 ERROR creating incremental model dev_hours_ingest.hours....... [ERROR in 0.24s]
06:20:19 |
06:20:19 | Finished running 1 incremental model in 0.39s.

Completed with 1 error and 0 warnings:

Database Error in model hours (models/hours_ingest/hours.sql)
MySQL Connection not available.
compiled SQL at target/run/dwh/models/hours_ingest/hours.sql

Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

Steps To Reproduce

In as much detail as possible, please provide steps to reproduce the issue. Sample data that triggers the issue, example model code, etc is all very helpful here.

Expected behavior

expectation is no error & table update

Screenshots and log output

If applicable, add screenshots or log output to help explain your problem.

The output of dbt --version:

~/dbt/dwh# dbt --version
installed version: 0.19.0
   latest version: 0.20.0

Your version of dbt is out of date! You can find instructions for upgrading here:
https://docs.getdbt.com/docs/installation

Plugins:
  - mysql: 0.19.0
  - mysql5: 0.19.0

The operating system you're using:
uname -a
Linux etl 4.15.0-142-generic #146-Ubuntu SMP Tue Apr 13 01:11:19 UTC 2021 x86_64 x86_64 x86_64 GNU/Linux

The output of python --version:
~/dbt/dwh# python --version
Python 3.8.10

** model file**
{{
config(
materialized='incremental',
unique_key='CLOCKED_IN'
)
}}

select * from {{ model['schema'] }}.hours_ingest

dbt.log extract
2021-07-13 18:16:50.937176 (Thread-1): On model.dwh.hours: /* {"app": "dbt", "dbt_version": "0.19.0", "profile_name": "default", "target_name": "dev", "node_id": "model.dwh.hours"} */

  delete
from `dev_hours_ingest`.`hours`
where (CLOCKED_IN) in (
    select (CLOCKED_IN)
    from `dev_hours_ingest`.`hours__dbt_tmp`
);

insert into `dev_hours_ingest`.`hours` (`JOB`, `CLOCKED_IN`, `CLOCKED_OUT`, `DURATION`, `HOURLY_RATE`, `EARNINGS`, `COMMENT`, `TAGS`, `BREAKS`, `ADJUSTMENTS`, `TOTALTIMEADJUSTMENT`, `TOTALEARNINGSADJUSTMENT`, `IN_FILE_NAME`, `LOAD_TIME`)
(
   select `JOB`, `CLOCKED_IN`, `CLOCKED_OUT`, `DURATION`, `HOURLY_RATE`, `EARNINGS`, `COMMENT`, `TAGS`, `BREAKS`, `ADJUSTMENTS`, `TOTALTIMEADJUSTMENT`, `TOTALEARNINGSADJUSTMENT`, `IN_FILE_NAME`, `LOAD_TIME`
   from `dev_hours_ingest`.`hours__dbt_tmp`
);

2021-07-13 18:16:50.937656 (Thread-1): PY_SSIZE_T_CLEAN will be required for '#' formats
2021-07-13 18:16:51.015510 (Thread-1): SQL status: Unknown cursor state/status 214 in 0.08 seconds
2021-07-13 18:16:51.034101 (Thread-1): On model.dwh.hours: COMMIT
2021-07-13 18:16:51.034228 (Thread-1): Using mysql connection "model.dwh.hours".
2021-07-13 18:16:51.034325 (Thread-1): On model.dwh.hours: COMMIT
2021-07-13 18:16:51.034454 (Thread-1): MySQL error: MySQL Connection not available.
2021-07-13 18:16:51.034556 (Thread-1): On model.dwh.hours: ROLLBACK
2021-07-13 18:16:51.034661 (Thread-1): Failed to rollback model.dwh.hours
Traceback (most recent call last):
File "/python/transform/lib/python3.8/site-packages/dbt/adapters/mysql/connections.py", line 107, in exception_handler
yield
File "/python/transform/lib/python3.8/site-packages/dbt/adapters/sql/connections.py", line 79, in add_query
cursor = connection.handle.cursor()
File "/python/transform/lib/python3.8/site-packages/mysql/connector/connection_cext.py", line 560, in cursor
raise errors.OperationalError("MySQL Connection not available.")
mysql.connector.errors.OperationalError: MySQL Connection not available.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "/python/transform/lib/python3.8/site-packages/dbt/adapters/base/connections.py", line 221, in _rollback_handle
connection.handle.rollback()
File "/python/transform/lib/python3.8/site-packages/mysql/connector/connection_cext.py", line 405, in rollback
self._cmysql.rollback()
_mysql_connector.MySQLInterfaceError: Commands out of sync; you can't run this command now

Additional context

Add any other context about the problem here.