Repository: dbeatty10/dbt-mysql (language: python)

Issue:
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.


Discussion / hints:

Fix patch:
diff --git a/.circleci/config.yml b/.circleci/config.yml
index 043f4c3..fbb9612 100644
--- a/.circleci/config.yml
+++ b/.circleci/config.yml
@@ -10,24 +10,46 @@ jobs:
       - checkout
       - run: tox -e flake8,unit
 
-  integration:
+  integration-8-0:
     environment:
       DBT_INVOCATION_ENV: circle
-      DBT_MYSQL_SERVER_NAME: mysql_server
+      DBT_MYSQL_SERVER_NAME: mysql-server-8-0
     docker:
       - image: fishtownanalytics/test-container:9
       - image: mysql:8.0
         environment:
           MYSQL_ROOT_PASSWORD: dbt
-        name: mysql_server
+        name: mysql-server-8-0
     steps:
       - checkout
       - run:
           name: Wait for MySQL
-          command: dockerize -wait tcp://mysql_server:3306 -timeout 5m -wait-retry-interval 5s
+          command: dockerize -wait tcp://mysql-server-8-0:3306 -timeout 5m -wait-retry-interval 5s
       - run:
           name: Run integration tests
-          command: tox -e integration-mysql
+          command: tox -e integration-mysql-8.0
+          no_output_timeout: 1h
+      - store_artifacts:
+          path: ./logs
+
+  integration-5-7:
+    environment:
+      DBT_INVOCATION_ENV: circle
+      DBT_MYSQL_SERVER_NAME: mysql-server-5-7
+    docker:
+      - image: fishtownanalytics/test-container:9
+      - image: mysql:5.7
+        environment:
+          MYSQL_ROOT_PASSWORD: dbt
+        name: mysql-server-5-7
+    steps:
+      - checkout
+      - run:
+          name: Wait for MySQL
+          command: dockerize -wait tcp://mysql-server-5-7:3306 -timeout 5m -wait-retry-interval 5s
+      - run:
+          name: Run integration tests
+          command: tox -e integration-mysql-5.7
           no_output_timeout: 1h
       - store_artifacts:
           path: ./logs
@@ -37,6 +59,9 @@ workflows:
   test-everything:
     jobs:
       - unit
-      - integration:
+      - integration-8-0:
+          requires:
+            - unit
+      - integration-5-7:
           requires:
             - unit
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 2f06fce..2fb1a60 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -1,4 +1,5 @@
 ## Unreleased (TBD)
+- Integration tests for MySQL 5.7 ([#70](https://github.com/dbeatty10/dbt-mysql/issues/70), [#71](https://github.com/dbeatty10/dbt-mysql/pull/71))
 
 ## dbt-mysql 0.19.0.1 (February 19, 2022)
 - Optional `ssl_disabled` property ([#67](https://github.com/dbeatty10/dbt-mysql/pull/67))
diff --git a/tox.ini b/tox.ini
index 9b5f979..f7cb0b3 100644
--- a/tox.ini
+++ b/tox.ini
@@ -1,6 +1,6 @@
 [tox]
 skipsdist = True
-envlist = flake8, unit, integration-mysql
+envlist = flake8, unit, integration-mysql-8.0, integration-mysql-5.7
 
 
 [testenv:flake8]
@@ -17,7 +17,7 @@ deps =
    -r{toxinidir}/requirements.txt
    -r{toxinidir}/dev_requirements.txt
 
-[testenv:integration-mysql]
+[testenv:integration-mysql-8.0]
 basepython = python3
 commands = /bin/bash -c '{envpython} -m pytest test/integration/mysql-8.0.dbtspec'
 passenv = DBT_INVOCATION_ENV DBT_MYSQL_SERVER_NAME
@@ -25,3 +25,12 @@ deps =
    -r{toxinidir}/requirements.txt
    -r{toxinidir}/dev_requirements.txt
    -e.
+
+[testenv:integration-mysql-5.7]
+basepython = python3
+commands = /bin/bash -c '{envpython} -m pytest test/integration/mysql-5.7.dbtspec'
+passenv = DBT_INVOCATION_ENV DBT_MYSQL_SERVER_NAME
+deps =
+   -r{toxinidir}/requirements.txt
+   -r{toxinidir}/dev_requirements.txt
+   -e.


Test patch:
diff --git a/test/integration/mysql-5.7.dbtspec b/test/integration/mysql-5.7.dbtspec
index 7fecf71..9dd0749 100644
--- a/test/integration/mysql-5.7.dbtspec
+++ b/test/integration/mysql-5.7.dbtspec
@@ -1,11 +1,12 @@
 
 target:
   type: mysql5
-  port: 3307
+  port: 3306
+  database: dbt_test_{{ var('_dbt_random_suffix') }}
   schema: dbt_test_{{ var('_dbt_random_suffix') }}
   server: "{{ env_var('DBT_MYSQL_SERVER_NAME', 'localhost') }}"
-  username: "{{ env_var('DBT_MYSQL_USERNAME', 'dbt_username') }}"
-  password: "{{ env_var('DBT_MYSQL_PASSWORD', 'dbt_password') }}"
+  username: "{{ env_var('DBT_MYSQL_USERNAME', 'root') }}"
+  password: "{{ env_var('DBT_MYSQL_PASSWORD', 'dbt') }}"
 
 sequences:
 
@@ -17,8 +18,9 @@ sequences:
   # Ephemeral materializations not supported for MySQL 5.7
   # test_dbt_ephemeral: ephemeral
   test_dbt_incremental: incremental
-  test_dbt_snapshot_strategy_timestamp: snapshot_strategy_timestamp
-  test_dbt_snapshot_strategy_check_cols: snapshot_strategy_check_cols
+  # Snapshot materializations not supported for MySQL 5.7
+  # test_dbt_snapshot_strategy_timestamp: snapshot_strategy_timestamp
+  # test_dbt_snapshot_strategy_check_cols: snapshot_strategy_check_cols
   test_dbt_data_test: data_test
   test_dbt_schema_test: schema_test
   # Ephemeral materializations not supported for MySQL 5.7