Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Support MySQL 5.X as a target #988

Closed
bobvawter opened this issue Sep 3, 2024 · 5 comments · Fixed by #1075
Closed

Support MySQL 5.X as a target #988

bobvawter opened this issue Sep 3, 2024 · 5 comments · Fixed by #1075
Assignees
@noelcrl
Labels
enhancement New feature or request good first issue Good for newcomers

Comments

@bobvawter
Copy link
Contributor

These older versions of MySQL don't support CTE's, which, at a minimum, prevents the schema-inspection queries from running. There may be other unknown-unknown's in terms of SQL workload changes.
@bobvawter bobvawter added enhancement New feature or request good first issue Good for newcomers labels Sep 3, 2024
@ryanluu12345
Copy link
Contributor

My understanding here is that we need to just add a new set of queries that don't use CTEs for MySQL and then drill in the version of the database throughout the codebase so that we can know when to use the MySQL 5.7 queries

@bobvawter
Copy link
Contributor Author

Adding support in schemawatch is the first identified task.

Run TEST_TARGET_CONNECT=mysql://root:SoupOrSecret@localhost:3306/ go test ./... to validate.

@bobvawter
Copy link
Contributor Author

Possibly an opportunity for #996

@ryanluu12345
Copy link
Contributor

Yep, I see what you mean by adding support in schemwatch. I see the query templates inside of internal/target/schemawatch/dependencies.go. Based on my read, it looks like we'll just need to add a new mysql5_7 template and then update the MySQL case statement so that if we see version 5.7, we use the new query without CTEs.

@ryanluu12345
Copy link
Contributor

Regarding #996, I can take a look at that once I get this base functionality working. Then can refactor this so it applies to all templates for every dialect.

noelcrl added a commit that referenced this issue Nov 18, 2024
@noelcrl
replicator: Support MySQL 5.X as a target
9c881a4 
Updates various SQL queries, query templates, code, and tests across Replicator to not use unsupported features in older MySQL target versions like 5.7. These features include common table expressions (CTEs), VALUES DML statements, and expressions for default column values.

Adds integration tests to ensure that edge cases for the conditional upsert query templates are covered. Adds MySQL 5.7 and 5.6 as a migration backfill cases for the testing matrix.

Changes the default collation for go-sql-driver connections to older MySQL versions (< 8.0.0) to be latin1_swedish_ci, since older versions use it as the default collation instead of the default for the driver (utf8mb4_general_ci).

Resolves #988 Release Note: None
@noelcrl noelcrl self-assigned this Nov 18, 2024
noelcrl added a commit that referenced this issue Nov 18, 2024
@noelcrl
replicator: Support MySQL 5.X as a target
f7de777 
Updates various SQL queries, query templates, code, and tests across Replicator to not use unsupported features in older MySQL target versions like 5.7. These features include common table expressions (CTEs), VALUES DML statements, and expressions for default column values.

Adds integration tests to ensure that edge cases for the conditional upsert query templates are covered. Adds MySQL 5.7 and 5.6 as a migration backfill cases for the testing matrix.

Changes the default collation for go-sql-driver connections to older MySQL versions (< 8.0.0) to be latin1_swedish_ci, since older versions use it as the default collation instead of the default for the driver (utf8mb4_general_ci).

Resolves #988 Release Note: None
noelcrl added a commit that referenced this issue Nov 19, 2024
@noelcrl
replicator: Support MySQL 5.X as a target
1cde2cf 
Updates various SQL queries, query templates, code, and tests across Replicator to not use unsupported features in older MySQL target versions like 5.7. These features include common table expressions (CTEs), VALUES DML statements, and expressions for default column values.

Adds integration tests to ensure that edge cases for the conditional upsert query templates are covered. Adds MySQL 5.7 and 5.6 as a migration backfill cases for the testing matrix.

Changes the default collation for Go-MySQL-Driver connections to older MySQL versions (< 8.0.0) to be latin1_swedish_ci, since older versions use it as the default collation instead of the default for the driver (utf8mb4_general_ci).

Resolves #988 Release Note: None
noelcrl added a commit that referenced this issue Nov 19, 2024
@noelcrl
replicator: Support MySQL 5.X as a target
1ab0d98 
Updates various SQL queries, query templates, code, and tests across Replicator to not use unsupported features in older MySQL target versions like 5.7. These features include common table expressions (CTEs), VALUES DML statements, and expressions for default column values.

Adds integration tests to ensure that edge cases for the conditional upsert query templates are covered. Adds MySQL 5.7 and 5.6 as a migration backfill cases for the testing matrix.

Changes the default collation for Go-MySQL-Driver connections to older MySQL versions (< 8.0.0) to be latin1_swedish_ci, since older versions use it as the default collation instead of the default for the driver (utf8mb4_general_ci).

Resolves #988 Release Note: None
noelcrl added a commit that referenced this issue Nov 19, 2024
@noelcrl
replicator: Support MySQL 5.X as a target
d212273 
Updates various SQL queries, query templates, code, and tests across Replicator to not use unsupported features in older MySQL target versions like 5.7. These features include common table expressions (CTEs), VALUES DML statements, and expressions for default column values.

Adds integration tests to ensure that edge cases for the conditional upsert query templates are covered. Adds MySQL 5.7 and 5.6 as a migration backfill cases for the testing matrix.

Changes the default collation for Go-MySQL-Driver connections to older MySQL versions (< 8.0.0) to be latin1_swedish_ci, since older versions use it as the default collation instead of the default for the driver (utf8mb4_general_ci).

Resolves #988 Release Note: None
noelcrl added a commit that referenced this issue Nov 19, 2024
@noelcrl
replicator: Support MySQL 5.7 and 5.6 as Targets
fa424dd 
Updates various SQL queries, query templates, code, and tests across Replicator to not use unsupported features in older MySQL target versions like 5.7. These features include common table expressions (CTEs), VALUES DML statements, and expressions for default column values.

Adds integration tests to ensure that edge cases for the conditional upsert query templates are covered. Adds MySQL 5.7 and 5.6 as a migration backfill cases for the testing matrix.

Changes the default collation for Go-MySQL-Driver connections to older MySQL versions (< 8.0.0) to be latin1_swedish_ci, since older versions use it as the default collation instead of the default for the driver (utf8mb4_general_ci).

Resolves #988 Release Note: None
@noelcrl noelcrl mentioned this issue Nov 19, 2024
Merged
noelcrl added a commit that referenced this issue Nov 21, 2024
@noelcrl
replicator: Support MySQL 5.7 and 5.6 as Targets
f1216c2 
Updates various SQL queries, query templates, code, and tests across Replicator to not use unsupported features in older MySQL target versions like 5.7. These features include common table expressions (CTEs), VALUES DML statements, and expressions for default column values.

Adds integration tests to ensure that edge cases for the conditional upsert query templates are covered. Adds MySQL 5.7 and 5.6 as a migration backfill cases for the testing matrix.

Changes the default collation for Go-MySQL-Driver connections to older MySQL versions (< 8.0.0) to be latin1_swedish_ci, since older versions use it as the default collation instead of the default for the driver (utf8mb4_general_ci).

Resolves #988 Release Note: None
noelcrl added a commit that referenced this issue Nov 25, 2024
@noelcrl
replicator: Support MySQL 5.7 and 5.6 as Replication Targets
29202f8 
Updates various SQL queries, query templates, code, and tests across Replicator to not use unsupported features in older MySQL target versions like 5.7. These features include common table expressions (CTEs), VALUES syntax for temporary tables, and expressions for default column values.

Fixes bugs that appear on older versions like MySQL geometry functions complaining about placeholder string arguments not being the correct type, and MySQL JSON functions translating YEAR columns to base64 formatted strings instead of just the year number string.

Changes the default collation for Go-MySQL-Driver connections to older MySQL versions (< 8.0.0) to be latin1_swedish_ci, since older versions use it as the default collation instead of the default for the driver (utf8mb4_general_ci), and certain replicator queries fail if it is not set.

Adds integration tests to ensure that edge cases for the conditional upsert query templates are covered. Adds MySQL 5.7 and 5.6 as a migration backfill cases for the testing matrix.

Fixes an issue where .gitignore ignores unintended directories.

Resolves #988 Release Note: None
noelcrl added a commit that referenced this issue Nov 26, 2024
@noelcrl
replicator: Support MySQL 5.7 as a Replication Target
e51f792 
Updates various SQL queries, query templates, code, and tests across Replicator to not use unsupported features in older MySQL target versions like 5.7. These features include common table expressions (CTEs), VALUES syntax for temporary tables, and expressions for default column values.

Fixes bugs that appear on older versions like MySQL geometry functions complaining about placeholder string arguments not being the correct type, and MySQL JSON functions translating YEAR columns to base64 formatted strings instead of just the year number string.

Changes the default collation for Go-MySQL-Driver connections to older MySQL versions (< 8.0.1) to be latin1_swedish_ci, since older versions use it as the default collation instead of the default for the driver (utf8mb4_general_ci), and certain replicator queries fail if it is not set.

Adds integration tests to ensure that more cases for the conditional upsert query templates are covered. Adds MySQL 5.7 as a migration backfill case for the testing matrix.

Fixes an issue where .gitignore ignores unintended directories.

Resolves #988 Release Note: None
noelcrl added a commit that referenced this issue Nov 26, 2024
@noelcrl
replicator: Support MySQL 5.7 as a Replication Target
4fd3cc0 
Updates various SQL queries, query templates, code, and tests across Replicator to not use unsupported features in older MySQL target versions like 5.7. These features include common table expressions (CTEs), VALUES syntax for temporary tables, and expressions for default column values.

Fixes bugs that appear on older versions like MySQL geometry functions complaining about placeholder string arguments not being the correct type, and MySQL JSON functions translating YEAR columns to base64 formatted strings instead of just the year number string.

Changes the default collation for Go-MySQL-Driver connections to older MySQL versions (< 8.0.1) to be latin1_swedish_ci, since older versions use it as the default collation instead of the default for the driver (utf8mb4_general_ci), and certain replicator queries fail if it is not set.

Adds integration tests to ensure that more cases for the conditional upsert query templates are covered. Adds MySQL 5.7 as a migration backfill case for the testing matrix.

Fixes an issue where .gitignore ignores unintended directories.

Resolves #988 Release Note: None
noelcrl added a commit that referenced this issue Nov 27, 2024
@noelcrl
replicator: Support MySQL 5.7 as a Replication Target
ed42953 
Updates various SQL queries, query templates, code, and tests across Replicator to not use unsupported features in older MySQL target versions like 5.7. These features include common table expressions (CTEs), VALUES syntax for temporary tables, and expressions for default column values.

Fixes bugs that appear on older versions like MySQL geometry functions complaining about placeholder string arguments not being the correct type, and MySQL JSON functions translating YEAR columns to base64 formatted strings instead of just the year number string.

Changes the default collation for Go-MySQL-Driver connections to older MySQL versions (< 8.0.1) to be latin1_swedish_ci, since older versions use it as the default collation instead of the default for the driver (utf8mb4_general_ci), and certain replicator queries fail if it is not set.

Adds integration tests to ensure that more cases for the conditional upsert query templates are covered. Adds MySQL 5.7 as a migration backfill case for the testing matrix.

Fixes an issue where .gitignore ignores unintended directories.

Resolves #988 Release Note: None
noelcrl added a commit that referenced this issue Nov 29, 2024
@noelcrl
replicator: Support MySQL 5.7 as a Replication Target
fd0804d 
Updates various SQL queries, query templates, code, and tests across Replicator to not use unsupported features in older MySQL target versions like 5.7. These features include common table expressions (CTEs), VALUES syntax for temporary tables, and expressions for default column values.

Fixes bugs that appear on older versions like MySQL geometry functions complaining about placeholder string arguments not being the correct type, and MySQL JSON functions translating YEAR columns to base64 formatted strings instead of just the year number string.

Changes the default collation for Go-MySQL-Driver connections to older MySQL versions (< 8.0.1) to be latin1_swedish_ci, since older versions use it as the default collation instead of the default for the driver (utf8mb4_general_ci), and certain replicator queries fail if it is not set.

Adds integration tests to ensure that more cases for the conditional upsert query templates are covered. Adds MySQL 5.7 as a migration backfill case for the testing matrix.

Fixes an issue where .gitignore ignores unintended directories.

Resolves #988 Release Note: None
noelcrl added a commit that referenced this issue Dec 3, 2024
@noelcrl
replicator: Support MySQL 5.7 as a Replication Target
64a20d4 
Updates various SQL queries, query templates, code, and tests across Replicator to not use unsupported features in older MySQL target versions like 5.7. These features include common table expressions (CTEs), VALUES syntax for temporary tables, and expressions for default column values.

Fixes bugs that appear on older versions like MySQL geometry functions complaining about placeholder string arguments not being the correct type, and MySQL JSON functions translating YEAR columns to base64 formatted strings instead of just the year number string.

Changes the default collation for Go-MySQL-Driver connections to older MySQL versions (< 8.0.1) to be latin1_swedish_ci, since older versions use it as the default collation instead of the default for the driver (utf8mb4_general_ci), and certain replicator queries fail if it is not set.

Adds integration tests to ensure that more cases for the conditional upsert query templates are covered. Adds MySQL 5.7 as a migration backfill case for the testing matrix.

Fixes an issue where .gitignore ignores unintended directories.

Resolves #988 Release Note: None
noelcrl added a commit that referenced this issue Dec 3, 2024
@noelcrl
replicator: Support MySQL 5.7 as a Replication Target
7783db3 
Updates various SQL queries, query templates, code, and tests across Replicator to not use unsupported features in older MySQL target versions like 5.7. These features include common table expressions (CTEs), VALUES syntax for temporary tables, and expressions for default column values.

Fixes bugs that appear on older versions like MySQL geometry functions complaining about placeholder string arguments not being the correct type, and MySQL JSON functions translating YEAR columns to base64 formatted strings instead of just the year number string.

Changes the default collation for Go-MySQL-Driver connections to older MySQL versions (< 8.0.1) to be latin1_swedish_ci, since older versions use it as the default collation instead of the default for the driver (utf8mb4_general_ci), and certain replicator queries fail if it is not set.

Adds integration tests to ensure that more cases for the conditional upsert query templates are covered. Adds MySQL 5.7 as a migration backfill case for the testing matrix.

Fixes issues for failing tests on MySQL 5.7 related to numbers with trailing zeros and JSON normalization.

Fixes an issue where .gitignore ignores unintended directories.

Resolves #988 Release Note: None
noelcrl added a commit that referenced this issue Dec 5, 2024
@noelcrl
replicator: Support MySQL 5.7 as a Replication Target
435050d 
Updates various SQL queries, query templates, code, and tests across Replicator to not use unsupported features in older MySQL target versions like 5.7. These features include common table expressions (CTEs), VALUES syntax for temporary tables, and expressions for default column values.

Fixes bugs that appear on older versions like MySQL geometry functions complaining about placeholder string arguments not being the correct type, and MySQL JSON functions translating YEAR columns to base64 formatted strings instead of just the year number string.

Changes the default collation for Go-MySQL-Driver connections to older MySQL versions (< 8.0.1) to be latin1_swedish_ci, since older versions use it as the default collation instead of the default for the driver (utf8mb4_general_ci), and certain replicator queries fail if it is not set.

Adds integration tests to ensure that more cases for the conditional upsert query templates are covered. Adds MySQL 5.7 as a migration backfill case for the testing matrix.

Fixes issues for failing tests on MySQL 5.7 related to numbers with trailing zeros and JSON normalization.

Fixes an issue where .gitignore ignores unintended directories.

Resolves #988 Release Note: None
noelcrl added a commit that referenced this issue Dec 5, 2024
@noelcrl
replicator: Support MySQL 5.7 as a Replication Target
7e9185e 
Updates various SQL queries, query templates, code, and tests across Replicator to not use unsupported features in older MySQL target versions like 5.7. These features include common table expressions (CTEs), VALUES syntax for temporary tables, and expressions for default column values.

Fixes bugs that appear on older versions like MySQL geometry functions complaining about placeholder string arguments not being the correct type, and MySQL JSON functions translating YEAR columns to base64 formatted strings instead of just the year number string.

Changes the default collation for Go-MySQL-Driver connections to older MySQL versions (< 8.0.1) to be latin1_swedish_ci, since older versions use it as the default collation instead of the default for the driver (utf8mb4_general_ci), and certain replicator queries fail if it is not set.

Adds integration tests to ensure that more cases for the conditional upsert query templates are covered. Adds MySQL 5.7 as a migration backfill case for the testing matrix.

Fixes issues for failing tests on MySQL 5.7 related to numbers with trailing zeros and JSON normalization.

Fixes an issue where .gitignore ignores unintended directories.

Resolves #988 Release Note: None
noelcrl added a commit that referenced this issue Dec 5, 2024
@noelcrl
replicator: Support MySQL 5.7 as a Replication Target
30890e2 
Updates various SQL queries, query templates, code, and tests across Replicator to not use unsupported features in older MySQL target versions like 5.7. These features include common table expressions (CTEs), VALUES syntax for temporary tables, and expressions for default column values.

Fixes bugs that appear on older versions like MySQL geometry functions complaining about placeholder string arguments not being the correct type, and MySQL JSON functions translating YEAR columns to base64 formatted strings instead of just the year number string.

Changes the default collation for Go-MySQL-Driver connections to older MySQL versions (< 8.0.1) to be latin1_swedish_ci, since older versions use it as the default collation instead of the default for the driver (utf8mb4_general_ci), and certain replicator queries fail if it is not set.

Adds integration tests to ensure that more cases for the conditional upsert query templates are covered. Adds MySQL 5.7 as a migration backfill case for the testing matrix.

Fixes issues for failing tests on MySQL 5.7 related to numbers with trailing zeros and JSON normalization.

Fixes an issue where .gitignore ignores unintended directories.

Resolves #988 Release Note: None
github-merge-queue bot pushed a commit that referenced this issue Dec 5, 2024
@noelcrl
replicator: Support MySQL 5.7 as a Replication Target
2398651 
Updates various SQL queries, query templates, code, and tests across Replicator to not use unsupported features in older MySQL target versions like 5.7. These features include common table expressions (CTEs), VALUES syntax for temporary tables, and expressions for default column values.

Fixes bugs that appear on older versions like MySQL geometry functions complaining about placeholder string arguments not being the correct type, and MySQL JSON functions translating YEAR columns to base64 formatted strings instead of just the year number string.

Changes the default collation for Go-MySQL-Driver connections to older MySQL versions (< 8.0.1) to be latin1_swedish_ci, since older versions use it as the default collation instead of the default for the driver (utf8mb4_general_ci), and certain replicator queries fail if it is not set.

Adds integration tests to ensure that more cases for the conditional upsert query templates are covered. Adds MySQL 5.7 as a migration backfill case for the testing matrix.

Fixes issues for failing tests on MySQL 5.7 related to numbers with trailing zeros and JSON normalization.

Fixes an issue where .gitignore ignores unintended directories.

Resolves #988 Release Note: None
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@noelcrl noelcrl

Labels
enhancement New feature or request good first issue Good for newcomers
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

target: Support MySQL 5.7 as a Replication Target cockroachdb/replicator
3 participants
@bobvawter @ryanluu12345 @noelcrl