dbeatty10/dbt-mysql

dbt-mysql contains all of the code enabling dbt to work with MySQL and MariaDB

dbt-mysql

This plugin ports dbt functionality to MySQL and MariaDB.

This is an experimental plugin:

• We have not tested it extensively
• Storage engines other than the default of InnoDB are untested
• Only tested with dbt-tests-adapter with the following:
  • MySQL 5.7
  • MySQL 8.0
  • MariaDB 10.5
• Compatiblity with other dbt packages (like dbt_utils) is also untested
• Supported Python Versions are: 3.6, 3.7, 3.8 & 3.9

Please read these docs carefully and use at your own risk. Issues and PRs welcome!

Table of Contents

• Installation
• Supported features
  • MySQL 5.7 configuration gotchas
• Configuring your profile
• Notes
• Running Tests
• Reporting bugs and contributing code

Installation

This plugin can be installed via pip:

$ python -m pip install dbt-mysql

Supported features

MariaDB 10.5	MySQL 5.7	MySQL 8.0	Feature
✅	✅	✅	Table materialization
✅	✅	✅	View materialization
✅	✅	✅	Incremental materialization
✅	❌	✅	Ephemeral materialization
✅	✅	✅	Seeds
✅	✅	✅	Sources
✅	✅	✅	Custom data tests
✅	✅	✅	Docs generate
🤷	🤷	✅	Snapshots

Notes:

• Ephemeral materializations rely upon Common Table Expressions (CTEs), which are not supported until MySQL 8.0
• MySQL 5.7 has some configuration gotchas that affect snapshots (see below).

MySQL 5.7 configuration gotchas

dbt snapshots might not work properly due to automatic initialization and updating for TIMESTAMP if:

• the output of SHOW VARIABLES LIKE 'sql_mode' includes NO_ZERO_DATE

A solution is to include the following in a *.cnf file:

[mysqld]
explicit_defaults_for_timestamp = true
sql_mode = "ALLOW_INVALID_DATES,{other_sql_modes}"

where {other_sql_modes} is the rest of the modes from the SHOW VARIABLES LIKE 'sql_mode' output.

Configuring your profile

A dbt profile can be configured to run against MySQL using configuration example below.

Use type: mysql for MySQL 8.x, type: mysql5 for MySQL 5.x, and type: mariadb for MariaDB.

Example entry for profiles.yml:

your_profile_name:
  target: dev
  outputs:
    dev:
      type: mysql
      server: localhost
      port: 3306
      schema: analytics
      username: your_mysql_username
      password: your_mysql_password
      ssl_disabled: True
      charset: utf8mb4
      collation: utf8mb4_0900_ai_ci

Option	Description	Required?	Example
type	The specific adapter to use	Required	mysql, mysql5 or mariadb
server	The server (hostname) to connect to	Required	yourorg.mysqlhost.com
port	The port to use	Optional	3306
schema	Specify the schema (database) to build models into	Required	analytics
username	The username to use to connect to the server	Required	dbt_admin
password	The password to use for authenticating to the server	Required	correct-horse-battery-staple
ssl_disabled	Set to enable or disable TLS connectivity to mysql5.x	Optional	True or False
charset	Specify charset to be used by a connection	Optional	utf8mb4
collation	Set to enable or disable TLS connectivity to mysql5.x	Optional	utf8mb4_0900_ai_ci

Notes

Conflicting terminology is used between:

• dbt
• Database management systems (DBMS) like MySQL, Postgres, and Snowflake
• metadata in the ANSI-standard information_schema

The conflicts include both:

• the same word meaning different things
• different words meaning the same thing

For example, a "database" in MySQL is not the same as a "database" in dbt, but it is equivalent to a "schema" in Postgres 🤯.

dbt-mysql uses the dbt terms. The native MySQL verbiage is restricted to SQL statements.

This cross-walk aligns the terminology:

information_schema	dbt (and Postgres)	MySQL
catalog	database	undefined / not implemented
schema	schema	database
relation (table/view)	relation (table/view)	relation (table/view)
column	column	column

Additionally, many DBMS have relation names with three parts whereas MySQL has only two. E.g., a fully-qualified table name in Postgres is database.schema.table versus database.table in MySQL. The missing part in MySQL is the information_schema "catalog".

DBMS	Fully-qualified relation name	Parts
Postgres	database.schema.table	3
MySQL	database.table	2

Running Tests

See tests/README.md for details on running the integration tests.

Reporting bugs and contributing code

• Want to report a bug or request a feature? See the contributing guidelines, or open an issue.

Credits

dbt-mysql borrows from dbt-spark and dbt-sqlite since Spark and SQLite also use two-part relation names.

dbt-mysql's Issues

`python -m pip` in documentation

Describe the feature

Update bare instances of pip ... to python -m pip ... instead.

Additional context

Longer: Why you should use python -m pip
Shorter: Use 'python -m pip' Everywhere

Who will this benefit?

This helps normalize the more predictable but verbose syntax over the shorter, more ubiquitous, and unpredictable syntax.

Add to list of adapters at docs.getdbt.com

Directions for adding to docs.getdbt.com:

1. Add a new file for the mysql plugin within this folder (follow the format for existing plugins)
2. Add a link to this new file in the sidebar

The new plugin should show up in a local build of the docs for dbt core.

Here are some existing examples:
https://docs.getdbt.com/docs/supported-databases/profile-mssql

Test MySQL 5.6

MySQL 5.6 doesn't end support until Feb 2021. We should test of dbt-mysql works or not for this version of MySQL.

Preferably, this testing would be automated within CI.

WITH (Common Table Expressions aka CTE) aren't supported until MySQL 8.0, so that could be a big gotcha if they are used under the covers at all.

upgrade to support dbt-core v1.1.0

https://docs.getdbt.com/guides/migration/versions/upgrading-to-v1.1

incremental materialisation always error with mysql 8

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.

Implement the new adapter testing framework

Describe the feature

Implement the adapter testing framework that is described in Upgrading to v1.1 - For maintainers of adapter plugins.

Issue with dbt-mysql installation

Hi,

Anyone encountered this issue when trying to install dbt-mysql via pip?

Collecting dbt-mysql
Using cached dbt_mysql-0.19.2-py3-none-any.whl (53 kB)
Collecting dbt-core==0.19.2
Using cached dbt_core-0.19.2-py3-none-any.whl (792 kB)
Collecting mysql-connector-python<8.1,>=8.0.0
Using cached mysql_connector_python-8.0.30-cp310-cp310-win_amd64.whl (7.9 MB)
Collecting Jinja2==2.11.2
Using cached Jinja2-2.11.2-py2.py3-none-any.whl (125 kB)
Requirement already satisfied: isodate<0.7,>=0.6 in c:\users\d1338529\appdata\local\programs\python\python310\lib\site-packages (from dbt-core==0.19.2->dbt-mysql) (0.6.1)
Requirement already satisfied: minimal-snowplow-tracker==0.0.2 in c:\users\d1338529\appdata\local\programs\python\python310\lib\site-packages (from dbt-core==0.19.2->dbt-mysql) (0.0.2)
Collecting mashumaro==2.0
Using cached mashumaro-2.0.tar.gz (25 kB)
Installing build dependencies ... done
Getting requirements to build wheel ... done
Preparing metadata (pyproject.toml) ... done
Collecting cffi<1.15,>=1.9
Using cached cffi-1.14.6.tar.gz (475 kB)
Preparing metadata (setup.py) ... error
error: subprocess-exited-with-error

× python setup.py egg_info did not run successfully.
│ exit code: 1
╰─> [20 lines of output]
Traceback (most recent call last):
File "", line 2, in
File "", line 34, in
File "C:\Users\d1338529\AppData\Local\Temp\pip-install-5gpn7iqn\cffi_6fac88013d854393b3527a6fa084239f\setup.py", line 127, in
if sys.platform == 'win32' and uses_msvc():
File "C:\Users\d1338529\AppData\Local\Temp\pip-install-5gpn7iqn\cffi_6fac88013d854393b3527a6fa084239f\setup.py", line 105, in uses_msvc
return config.try_compile('#ifndef _MSC_VER\n#error "not MSVC"\n#endif')
File "C:\Users\d1338529\AppData\Local\Programs\Python\Python310\lib\distutils\command\config.py", line 225, in try_compile
self._compile(body, headers, include_dirs, lang)
File "C:\Users\d1338529\AppData\Local\Programs\Python\Python310\lib\distutils\command\config.py", line 132, in _compile
self.compiler.compile([src], include_dirs=include_dirs)
File "C:\Users\d1338529\AppData\Local\Programs\Python\Python310\lib\distutils_msvccompiler.py", line 323, in compile
self.initialize()
File "C:\Users\d1338529\AppData\Local\Programs\Python\Python310\lib\distutils_msvccompiler.py", line 220, in initialize
vc_env = _get_vc_env(plat_spec)
File "C:\Users\d1338529\AppData\Local\Programs\Python\Python310\lib\site-packages\setuptools\msvc.py", line 316, in msvc14_get_vc_env
return _msvc14_get_vc_env(plat_spec)
File "C:\Users\d1338529\AppData\Local\Programs\Python\Python310\lib\site-packages\setuptools\msvc.py", line 270, in _msvc14_get_vc_env
raise distutils.errors.DistutilsPlatformError(
distutils.errors.DistutilsPlatformError: Microsoft Visual C++ 14.0 or greater is required. Get it with "Microsoft C++ Build Tools": https://visualstudio.microsoft.com/visual-cpp-build-tools/
[end of output]

note: This error originates from a subprocess, and is likely not a problem with pip.
error: metadata-generation-failed

× Encountered error while generating package metadata.
╰─> See above for output.

note: This is an issue with the package mentioned above, not pip.
hint: See above for details.

Replace pyodbc with mysql-connector-python

Describe the feature

Replace pyodbc with mysql-connector-python.

Describe alternatives you've considered

MySQL-Connector-Python was chosen since it is the official Oracle-supported driver for MySQL.

Here is a (slightly outdated) comparison of dbapi alternatives to pyodbc for MySQL:

• MySQL-Python
• mysqlclient
• OurSQL
• MySQL-Connector-Python
• PyMySQL

Note: contrary to the image above, MySQL-Connector-Python is available via PyPi.

Who will this benefit?

This lowers the barrier to entry by removing the need for installing MySQL ODBC drivers.

PyPI: relative links in README.md break in long_description

Describe the bug

PyPI: relative links in README.md break in long_description

Steps To Reproduce

Clicking on the test/README.md link in PyPi gives a 404.

Expected behavior

The desired behavior is to go to a release branch-specifc version of the docs.

Similar bug reports in other open-source projects

• dhimmel/obonet#12
• pysal/pointpats#8
• hypatia-software-org/hypatia-engine#124
• senaite/senaite.sync#61

upgrade to support dbt-core v1.2.0

We've just published the release cut of dbt-core 1.2.0, dbt-core 1.2.0rc1 (PyPI | GitHub release notes).

dbt-labs/dbt-core#5468 is an open discussion with more detailed information, and dbt-labs/dbt-core#5474 is for keeping track of the communities progress on releasing 1.2.0

Below is a checklist of work that would enable a successful 1.2.0 release of your adapter.

• migrate necessary cross-db macros into adapter and ensure they're tested accordingly
• remove any copy-and-pasted materialization (if your adapter inherits from another adapter)
• add new basic tests BaseDocsGenerate and BaseDocsGenReferences
• consider checking and testing support for Python 3.10

dbt-labs/dbt-core#5432 might make it into the second release cut in the next week, in which case, you'll also might want to:

• implement method and tests for connection retry logic

No database selected

Describe the bug

Hi I've just set up a brand new project using db init and dbt-mysql. I've setup my profiles.yaml and checked that dbt debug connects to my database properly and returns no errors.

When I run dbt run, I get the following error

How do I specify the database in dbt?

Changelog

Installation problems Could not find adapter type mysql!

--

Upgrade bleach to version 3.3.0 or later

Description

Upgrade bleach to version 3.3.0 or later

Background

pipenv graph showed the following:

twine==3.3.0
  - readme-renderer [required: >=21.0, installed: 28.0]
    - bleach [required: >=2.1.0, installed: 3.2.3]

But there is a moderate security vulnerability for versions of bleach earlier than 3.3.0.

Appropriate updates should yield something like:

twine==3.3.0
  - readme-renderer [required: >=21.0, installed: 28.0]
    - bleach [required: >=2.1.0, installed: 3.3.0]

References

• https://bugzilla.mozilla.org/show_bug.cgi?id=1689399
• https://cure53.de/fp170.pdf

Readme for GitHub and PyPi

Clearer Exception message for invalid `database`  configuration

Describe the feature

I've found myself in difficulty while trying to troubleshoot the Runtime Error "Cannot set database None in mysql" or "Cannot set database in mysql".

Indeed, I'm currently migrating some existing dbt project from a snowflake host to a mysql host and as the existing project had custom get_schema_name and get_database_name macros defined, the above error was not sufficient for me to understand that the computed schema name (returned by the former macro) was not matching the computed database name (returned by the latter macro).

By providing a richer exception, anyone encountering such issues could troubleshoot more easily where the issue comes from.

Describe alternatives you've considered

None

Additional context

None

Who will this benefit?

Anyone trying to run their existing dbt transformations with the dbt-mysql adapter for the first time.

Are you interested in contributing this feature?

Yes

Bump dbt to v0.19.0rc1

This will enable testing (and troubleshooting) against v0.19.0rc1

dbt-doris: add adapter to dbt docs's "Available Adapters" page

@qiulin creating an issue here because you don't have Issues enabled for dbt-doris

The Available Adapters page is one of the dbt community's most-visited docs pages. It would be of great benefit for first-time visitors to the dbt docs to see:

1. that this adapter is a possible option for using dbt-core, and
2. how many large the dbt ecosystem of support databases is.

dbt-labs/docs.getdbt.com#1489 exists to address this with all as-of-yet undocumented adapters.

We just released Documenting a new adapter, a new guide on how to add an adapter to the Available Adapters page. I'd love to see this adapter on that page, so feel free to reach out with any questions/blockers by either replying to this issue, or posting in the #adapter-ecosystem channel of the dbt Community Slack.

Looking forward to the contribution!

Use pytest-dbt-adapter 4.0

Describe the feature

Use pytest-dbt-adapter 4.0

Test MySQL 5.7

MySQL 5.7 doesn't end support until Oct 2023. We should test of dbt-mysql works or not for this version of MySQL.

Preferably, this testing would be automated within CI.

WITH (Common Table Expressions aka CTE) aren't supported until MySQL 8.0, so that could be a big gotcha if they are used under the covers at all.

It sounds like SQL Server doesn't support CTE either, so the dbt-sqlserver or dbt-mssql packages could be used for inspiration if needed.

dbt docs generate: Expected only one database in get_catalog

Describe the bug

I was trying to generate docs for my projects (to check out dbt's data lineage feature) but it gave the below error.

I'm not sure if I should register information_schema as another source in my source.yml file (so I could use {{ source() }})?

$ dbt docs generate
Running with dbt=0.19.0
Found 8 models, 2 tests, 0 snapshots, 0 analyses, 140 macros, 0 operations, 0 seed files, 2 sources, 0 exposures

21:41:44 | Concurrency: 4 threads (target='tst')
21:41:44 |
21:41:45 | Done.
21:41:45 | Building catalog
Encountered an error:
Compilation Error
  Expected only one database in get_catalog, found [<InformationSchema INFORMATION_SCHEMA>, <InformationSchema villo.INFORMATION_SCHEMA>]

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.

I have a file called source.yml in my models folder so I can refer to some tables using the {{ source() }} feature.

These are the first 8 lines of source.yml:

version: 2

sources:
  - name: villo
    description: The database containing all the measurements.
    database: villo

    tables:

This is my profiles.yml:

sm_villo:
  target: tst
  outputs:
    tst:
      type: mysql
      server: localhost
      user: ************
      password: ***************
      schema: villo
      threads: 4

I ran dbt docs generate.

Expected behavior

HTML documentation is generated.

Screenshots and log output

The output of dbt --version:

installed version: 0.19.0
   latest version: 0.19.0

Up to date!

Plugins:
  - postgres: 0.19.0
  - bigquery: 0.19.0
  - mysql: 0.19.0
  - snowflake: 0.19.0
  - mysql5: 0.19.0
  - redshift: 0.19.0

The operating system you're using:

Ubuntu 20.04.2 LTS (GNU/Linux 5.4.0-62-generic x86_64)

The output of python --version:

Python 3.8.5

Additional context

dbt-adapter-tests for MySQL

Describe the bug

dbt-adapter-tests assumes support for certain SQL syntax that is not supported by different versions of MySQL.

Steps To Reproduce

Something similar to the following should expose this issue (for the data_test test):

$ pip install pytest-dbt-adapter
$ pytest test/mysql.dbtspec

Expected behavior

This shouldn't fail for SQL syntax reasons.

Example

An example that doesn't work for MySQL 8.0:

select 1 as id where id = 2

The following alternative works for MySQL 8.0 but not for 5.6 or 5.7:

select 1 id where 1 = 1

This alternative works for all versions of MySQL:

select 1 id from dual where 1 = 1

Unfortunately, a simple pull request to dbt-adapter-tests won't resolve this since not all databases have a dual table. A notable example is MS SQL Server.

Snapshots tests not passing in CI for all database versions

Describe the bug

Snapshot integration tests aren't passing in continuous integration (CI). It may need custom updates to a *.cnf file within the Docker containers.

Affected versions

MySQL 8.0 is working fine. But these release versions are not:

• MySQL 5.7
• MariaDB 10.5

Additional context

dbt snapshots might not work properly due to automatic initialization and updating for TIMESTAMP if:

• the output of SHOW VARIABLES LIKE 'sql_mode' includes NO_ZERO_DATE

A solution is to include the following in a *.cnf file:

[mysqld]
explicit_defaults_for_timestamp = true
sql_mode = "ALLOW_INVALID_DATES,{other_sql_modes}"

where {other_sql_modes} is the rest of the modes from the SHOW VARIABLES LIKE 'sql_mode' output.

`mysql.connector` really wants a `database` argument at connection time

Describe the bug

Attempting to do a dbt run fails b/c the mysql.connector.connect doesn't specify a value of database as an argument, so any create table queries will fail with 1046 (3D000): No database selected.

Question

I'm wondering if this was something that happened on the switch from pyodbc to mysql.connector and the code just wasn't updated?

upgrade to support dbt-core v1.2.0

We've just published the release cut of dbt-core 1.2.0, dbt-core 1.2.0rc1 (PyPI | GitHub release notes).

dbt-labs/dbt-core#5468 is an open discussion with more detailed information, and dbt-labs/dbt-core#5474 is for keeping track of the communities progress on releasing 1.2.0

Below is a checklist of work that would enable a successful 1.2.0 release of your adapter.

• migrate necessary cross-db macros into adapter and ensure they're tested accordingly
• remove any copy-and-pasted materialization (if your adapter inherits from another adapter)
• add new basic tests BaseDocsGenerate and BaseDocsGenReferences
• consider checking and testing support for Python 3.10

dbt-labs/dbt-core#5432 might make it into the second release cut in the next week, in which case, you'll also might want to:

• implement method and tests for connection retry logic

Remove documentation for MySQL 5.6

Describe the feature

Remove documentation for MySQL 5.6 since it reached its end of life (EOL) in February 2021.

Describe alternatives you've considered

An alternative would be to keep this documentation around for some amount of time for folks still using a non-supported version.

Who will this benefit?

This will benefit people who want less documentation/instructions to read through. Folks that want to see older documentation and gotchas related to MySQL 5.6 can look back at a tagged version.

upgrade to support dbt-core v1.3.0

Background

The latest release cut for 1.3.0, dbt-core==1.3.0rc2 was published on October 3, 2022 (PyPI | Github). We are targeting releasing the official cut of 1.3.0 in time for the week of October 16 (in time for Coalesce conference).

We're trying to establish a following precedent w.r.t. minor versions:
Partner adapter maintainers release their adapter's minor version within four weeks of the initial RC being released. Given the delay on our side in notifying you, we'd like to set a target date of November 7 (four weeks from today) for maintainers to release their minor version

Timeframe	Date (intended)	Date (Actual)	Event
D - 3 weeks	Sep 21	Oct 10	dbt Labs informs maintainers of upcoming minor release
D - 2 weeks	Sep 28	Sep 28	core 1.3 RC is released
Day D	October 12	Oct 12	core 1.3 official is published
D + 2 weeks	October 26	Nov 7	dbt-adapter 1.3 is published

How to upgrade

dbt-labs/dbt-core#6011 is an open discussion with more detailed information, and dbt-labs/dbt-core#6040 is for keeping track of the community's progress on releasing 1.2.0

Below is a checklist of work that would enable a successful 1.2.0 release of your adapter.

• Python Models (if applicable)
• Incremental Materialization: cleanup and standardization
• More functional adapter tests to inherit

Field "database" of type typing.Union[str, NoneType] is missing in dbt.adapters.mysql.connections.MySQLCredentials instance

Describe the bug

A clear and concise description of what the bug is. What command did you run? What happened?
Run - dbt debug
Encountered an error:
Field "database" of type typing.Union[str, NoneType] is missing in dbt.adapters.mysql.connections.MySQLCredentials instance

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

A clear and concise description of what you expected to happen.

Screenshots and log output

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

The output of dbt --version:

installed version: 0.19.2
   latest version: 0.19.2

Up to date!

Plugins:
  - bigquery: 0.19.2
  - mysql: 0.19.0
  - mysql5: 0.19.0
  - postgres: 0.19.2
  - redshift: 0.19.2
  - snowflake: 0.19.2

The operating system you're using:
windows 10

The output of python --version:
python 3.6

Additional context

Add any other context about the problem here.

Dispatch macros for dbt-utils

Describe the feature

Implement MySQL dispatch macros for dbt-utils

Describe alternatives you've considered

Some potential alternatives:

• add implementations to the cross_db_utils folder within dbt-utils
• do nothing

Additional context

dbt-utils is probably the most popular package in the dbt hub, and a significant number of dbt users rely on its functionality for day-to-day usage of dbt.

Occurs an error when run `dbt docs generate`

Describe the bug

It occurs an error when running dbt docs generate. But dbt docs serve works fine.

Steps To Reproduce

# models/schema.yml
version: 2
sources:
- name: lake
  tables:
  - name: ae_commits
    description: ""
    columns:
    - name: hex_sha
      data_type: varchar(191)
      description: ""
      extra: ""
      quote: false
      tests: []
      tags: []
      meta: {}
    - name: analysis_id
      data_type: varchar
      description: ""
      extra: ""
      quote: false
      tests: []
      tags: []
      meta: {}
    - name: author_email
      data_type: varchar
      description: ""
      extra: ""
      quote: false
      tests: []
      tags: []
      meta: {}
    - name: dev_eq
      data_type: bigint
      description: ""
      extra: ""
      quote: false
      tests: []
      tags: []
      meta: {}
    - name: ae_project_id
      data_type: bigint
      description: ""
      extra: ""
      quote: false
      tests: []
      tags: []
      meta: {}

Expected behavior

It should generate docs without errors.

Screenshots and log output

$ dbt docs generate
08:21:23  Running with dbt=1.0.1
08:21:23  Found 1 model, 0 tests, 0 snapshots, 0 analyses, 166 macros, 0 operations, 0 seed files, 72 sources, 0 exposures, 0 metrics
08:21:23  
08:21:23  Concurrency: 1 threads (target='dev')
08:21:23  
08:21:23  Done.
08:21:23  Building catalog
08:21:23  Encountered an error while generating catalog: 'MySQLColumn' object has no attribute 'to_dict'
08:21:23  dbt encountered 1 failure while writing the catalog
08:21:23  Catalog written to /home/cong.wang/code/dbt/dbt/project/lake/target/catalog.json

The output of dbt --version:

$ dbt --version
installed version: 1.0.1
   latest version: 1.0.1

Up to date!

Plugins:
  - mysql5: 0.19.0
  - mysql: 0.19.0
  - postgres: 1.0.1
  - trino: 1.0.1

The operating system you're using:

$ cat /etc/os-release 
NAME="CentOS Linux"
VERSION="7 (Core)"
ID="centos"
ID_LIKE="rhel fedora"
VERSION_ID="7"
PRETTY_NAME="CentOS Linux 7 (Core)"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:centos:centos:7"
HOME_URL="https://www.centos.org/"
BUG_REPORT_URL="https://bugs.centos.org/"

CENTOS_MANTISBT_PROJECT="CentOS-7"
CENTOS_MANTISBT_PROJECT_VERSION="7"
REDHAT_SUPPORT_PRODUCT="centos"
REDHAT_SUPPORT_PRODUCT_VERSION="7"

The output of python --version:

$ python --version
Python 3.7.11

Additional context

# profiles.yml
lake:
  target: dev
  outputs:
    dev:
      type: mysql
      server: **
      port: ** # optional
      database: lake
      charset: utf8mb4
      schema: lake
      username: ***
      password: ***
      driver: MySQL ODBC 8.0 ANSI Driver
    prod:
      type: mysql
      server: [server/host]
      port: [port]  # optional
      schema: [schema]
      username: [username]
      password: [password]
      driver: MySQL ODBC 8.0 ANSI Driver

Misnamed adapter within incremental materialization

Describe the bug

The following is causing no known problems, but it is different than I expected. It is probably an accident that should be corrected for clarity, if nothing else.

The mysql5 adapter contains the following materialization (seen here):

{% materialization incremental, adapter='mysql' %}

But I expected:

{% materialization incremental, adapter='mysql5' %}

Continuous integration tests for MySQL 5.7

Describe the feature

Integration tests for both MySQL 5.7 and MySQL 8.0

Describe alternatives you've considered

• Test only one or the other
• Support MySQL 5.7 and 8.0 via separate adapters

Neither alternative is attractive since it is straightforward to test multiple database versions.

Additional context

MySQL 5.7 and 8.0 are sufficiently different to warrant individual testing.

Who will this benefit?

Many folks utilize MySQL 5.7, and it is officially supported until Oct 2023.

Are you interested in contributing this feature?

Yes, I will implement this feature.

Setup continuous integration (CI)

Release instructions

Clarification: Data Type display on docs serve using mysql adapter

Describe the bug

Apologies, we do not know if this is a bug yet. Hence like to check with the rest of users who may be able to help. After docs generate and serve, we see that the data type are displayed with b'xxxx'. Please see screenshot below:

We do see the same in the generated target/catalog.json file. Not sure if this is adapter specific or configuration issue but we searched and could not find a solution.

Appreciate some guidance on this. Thank you!

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

A clear and concise description of what you expected to happen.

Screenshots and log output

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

The output of dbt --version:

The operating system you're using:

Ubuntu 22.04.1 LTS

The output of python --version:
3.9.13

Additional context

Add any other context about the problem here.

Split the release instructions into more granular chunks

Specifically, separate the following into different sections:

1. Bump version
2. Package for PyPi

Implement and test dbt-mysql 0.19.0

Describe the feature

The first beta for dbt core 0.19.0 is available, so work to test the dbt-mysql adapter can begin.

Describe alternatives you've considered

An alternative is to not create a release for dbt-mysql 0.19.0. The benefit of this alternative would be requiring no work. The downside would be limiting the utility of the MySQL adapter if it doesn't progress along with dbt core.

Additional context

It is completely unknown how much time this will take.

Who will this benefit?

This will benefit anyone who uses dbt and continues to upgrade their version of dbt core.

Are you interested in contributing this feature?

I intend to implement this feature 😃

GitHub templates

Here's some example templates: dbt-spark/.github/

upgrade to support dbt-core v1.2.0

We've just published the release cut of dbt-core 1.2.0, dbt-core 1.2.0rc1 (PyPI | GitHub release notes).

dbt-labs/dbt-core#5468 is an open discussion with more detailed information, and dbt-labs/dbt-core#5474 is for keeping track of the communities progress on releasing 1.2.0

Below is a checklist of work that would enable a successful 1.2.0 release of your adapter.

• migrate necessary cross-db macros into adapter and ensure they're tested accordingly
• remove any copy-and-pasted materialization (if your adapter inherits from another adapter)
• add new basic tests BaseDocsGenerate and BaseDocsGenReferences
• consider checking and testing support for Python 3.10

dbt-labs/dbt-core#5432 might make it into the second release cut in the next week, in which case, you'll also might want to:

• implement method and tests for connection retry logic

Test if case-sensitive names are supported or not

Describe the feature

It is unknown if dbt-mysql already support case-sensitive names or not.

The following should be checked for support of case-sensitive names:

• schemas/databases
• tables/views
• columns

Describe alternatives you've considered

The alternative is to wait to see if someone runs into a problem down the road and files a bug ticket. I prefer not to defer testing responsibilities to users 😉

Additional context

The dbt-sqlite adpater needed to implement a fix to support CamelCase model names.

Who will this benefit?

This will benefit any users with MySQL databases that have case-sensitive identifiers.

Are you interested in contributing this feature?

I intend to implement this feature.

upgrade to support dbt-core v1.3.0

Background

The latest release cut for 1.3.0, dbt-core==1.3.0rc2 was published on October 3, 2022 (PyPI | Github). We are targeting releasing the official cut of 1.3.0 in time for the week of October 16 (in time for Coalesce conference).

We're trying to establish a following precedent w.r.t. minor versions:
Partner adapter maintainers release their adapter's minor version within four weeks of the initial RC being released. Given the delay on our side in notifying you, we'd like to set a target date of November 7 (four weeks from today) for maintainers to release their minor version

Timeframe	Date (intended)	Date (Actual)	Event
D - 3 weeks	Sep 21	Oct 10	dbt Labs informs maintainers of upcoming minor release
D - 2 weeks	Sep 28	Sep 28	core 1.3 RC is released
Day D	October 12	Oct 12	core 1.3 official is published
D + 2 weeks	October 26	Nov 7	dbt-adapter 1.3 is published

How to upgrade

dbt-labs/dbt-core#6011 is an open discussion with more detailed information, and dbt-labs/dbt-core#6040 is for keeping track of the community's progress on releasing 1.2.0

Below is a checklist of work that would enable a successful 1.2.0 release of your adapter.

• Python Models (if applicable)
• Incremental Materialization: cleanup and standardization
• More functional adapter tests to inherit

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

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?

Implement and test dbt-mysql 0.18.1

Update abstract methods `get_response` and `execute`

Describe the feature

Implement the changes to the abstract methods get_response and execute that are described in Upgrading to v1.1 - For maintainers of adapter plugins.

MySql5 view parenthesis

Describe the bug

A consistent failure on view creation within MySQL5.x, especially those containing UNION, throws

1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'union

A traceback to /target/run shows the offending *.sql of form:

create view schema.table__dbt_tmp as (
  (
    select *
    from schema.table_1A
  )
  union
  (
    select *
    from schema.table_1B
  )
);

Removing the first parenthetical grouping, so that the query reads

create view schema.table__dbt_tmp as 
  (
    select *
    from schema.table_1A
  )
  union
  (
    select *
    from schema.table_1B
  )

results in a working query. Likely a final parsing step just needs amending, but not certain yet where that is in the adapter stack...

Document MySQL protocol databases

Describe the feature

There are mutliple databases that are described as using the MySQL wire protocol and/or syntax including [1]:

• SingleStore
• ClickHouse
• TiDB
• PlanetScale on Vitess
• Dolt

There are probably others too. As we learn of them, it would be nice to document if any compatability testing has done against this adapter. Where applicable, redirect them to the specific adapter for the database.

Additional context

Some databases might not support crucial functionality. An example is TiDB's support for create table as ... select statements (CTAS) (#3839, #4754).

Test with current versions of MariaDB

Split MySQL 5.6 and 5.7 support from 8.0

Describe the feature

MySQL 8.0 supports CTE whereas 5.6 and 5.7 do not. Currently, the code is written without CTE to meet the lowest common denominator. If the code is split, then the 8.0 portion can utilize CTE.

Describe alternatives you've considered

1. do nothing; one adapter without using CTE features (and one PyPi distribution)
2. one repo with two different adapters (and one PyPi distribution)
3. one repo with two different adapters and two different PyPi distributions
4. two different repos (and adapters and PyPi distributions)

Option 2 (one repo with two different adapters) seems most attractive.

Who will this benefit?

The availability of CTE will benefit users of MySQL 8.0. The main use cases:

• using community packages that rely upon CTE
• reading the generated/compiled SQL

Gitignore

docker compose

Provide a docker-compose.yml file to launch database services for testing.
Provide instructions to bring those services up, shut them down, and use them in between.