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

Issue:
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`](https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html) 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.


Discussion / hints:

Fix patch:
diff --git a/.circleci/config.yml b/.circleci/config.yml
index 9c7686d..fc60b27 100644
--- a/.circleci/config.yml
+++ b/.circleci/config.yml
@@ -41,6 +41,7 @@ jobs:
       - image: mysql:5.7
         environment:
           MYSQL_ROOT_PASSWORD: dbt
+        command: [--explicit-defaults-for-timestamp=ON]
         name: mysql-server-5-7
     steps:
       - checkout
@@ -63,6 +64,7 @@ jobs:
       - image: mariadb:10.5
         environment:
           MYSQL_ROOT_PASSWORD: dbt
+        command: [--explicit-defaults-for-timestamp=ON]
         name: mariadb-server-10-5
     steps:
       - checkout
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 37b7ce8..a38eb85 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -1,7 +1,10 @@
 ## Unreleased (TBD)
+
+### Under the hood
 - Integration tests for MySQL 5.7 ([#70](https://github.com/dbeatty10/dbt-mysql/issues/70), [#71](https://github.com/dbeatty10/dbt-mysql/pull/71))
 - Integration tests for MariaDB 10.5 ([#72](https://github.com/dbeatty10/dbt-mysql/pull/72))
 - Support for MariaDB 10.5 ([#32](https://github.com/dbeatty10/dbt-mysql/issues/32), [#73](https://github.com/dbeatty10/dbt-mysql/pull/73))
+- Enable snapshot integration tests for MySQL 5.7 and MariaDB 10.5 ([#75](https://github.com/dbeatty10/dbt-mysql/issues/75), [#76](https://github.com/dbeatty10/dbt-mysql/pull/76))
 
 ## dbt-mysql 0.19.0.1 (February 19, 2022)
 - Optional `ssl_disabled` property ([#67](https://github.com/dbeatty10/dbt-mysql/pull/67))


Test patch:
diff --git a/test/integration/mariadb-10.5.dbtspec b/test/integration/mariadb-10.5.dbtspec
index d8d28ba..c00566d 100644
--- a/test/integration/mariadb-10.5.dbtspec
+++ b/test/integration/mariadb-10.5.dbtspec
@@ -17,8 +17,8 @@ sequences:
   test_dbt_base: base
   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
+  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
   test_dbt_ephemeral_data_tests: data_test_ephemeral_models
diff --git a/test/integration/mysql-5.7.dbtspec b/test/integration/mysql-5.7.dbtspec
index 9dd0749..e662157 100644
--- a/test/integration/mysql-5.7.dbtspec
+++ b/test/integration/mysql-5.7.dbtspec
@@ -18,9 +18,8 @@ sequences:
   # Ephemeral materializations not supported for MySQL 5.7
   # test_dbt_ephemeral: ephemeral
   test_dbt_incremental: incremental
-  # 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_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