Open Ynng opened 6 months ago
Ye this is a bit unfortunate, I suppose you could move the semicolon to a new line, or wrap that statement in a +goose StatementBegin
/ +goose StatementEnd
.
The SQL parser is quite basic and does the bare minimum. Open to suggestions on how this could be improved.
Certainly, I can manually adjust the SQL, but for my scenario, which involves programmatically generating migrations from mysqldump
, the task becomes more challenging.
For now, my temporary workaround is to regex my arrows from -->
to ->
, but this is obviously not a universal fix for --
.
I don't really see any solutions that doesn't require complicating ParseSQLMigration
.
Maybe we can track whether or not we are inside a string by looking for the '
character? But there are many edge cases...
Certainly, I can manually adjust the SQL, but for my scenario, which involves programmatically generating migrations from mysqldump, the task becomes more challenging.
Yep, that's an excellent example.
I don't really see any solutions that doesn't require complicating ParseSQLMigration.
Pretty much. Which gets us into the territory of writing a full-blown SQL parser, otherwise we're always fighting a new edge case. To make matters worse, there's always some subtle dialect-specific difference.
I'll keep this issue open and continue to think this through in the background.
I wonder if you could wrap your entire dumped schema with all statements within:
-- +goose Up
-- +goose StatementBegin
... your entire schema here
-- +goose StatementEnd
This tells goose
to send the entire set of querie(s) as a single semicolon-separated query. And usually this just works unless you have an extensive schema, exceed the database limit or a specific query can't be run in the same transaction.
A bit more background on these annotations can be found here:
https://pressly.github.io/goose/blog/2022/overview-sql-file/#multiple-statements
When executing SQL migrations, the parser misses the semicolon and incorrectly merges statements if a string contains "--" after a space, mistakenly interpreting it as the start of a comment. This issue occurs even though the "--" is part of a string and should not be treated as a comment. For example:
This results in syntax errors when running migrations, as seen below:
The issue appears to be in the semicolon detection logic
endsWithSemicolon(line string) bool
: https://github.com/pressly/goose/blob/d4a4dc3aa17829adf9d66d53d7a534ea59ead6be/internal/sqlparser/parser.go#L302-L321