0

I am using this goose flavor. I want my migration script to roll back when there is an error. Wrapping my statements within -- +goose StatementStart and -- +goose StatementEnd did not work for me.

-- +goose Up
-- SQL in section 'Up' is executed when this migration is applied
-- +goose StatementBegin
ALTER TABLE books
    ADD COLUMN author VARCHAR(10) NOT NULL AFTER name;

UPDATE books
SET author = created_by
WHERE created > '2021-01-05';
-- +goose StatementEnd

-- +goose Down
-- SQL section 'Down' is executed when this migration is rolled back
-- +goose StatementBegin
ALTER TABLE books
    DROP COLUMN author;
-- +goose StatementEnd

This results in Error 1064: 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 'UPDATE....

Is this possible with goose? Or do I need to write only a single query in one migration file?

Malaka Gunawardhana
  • 285
  • 4
  • 19
  • think you should have only one sql statement per pair of begin/end statements. but, that does not seem to do what you think it does https://github.com/steinbacher/goose/blob/dc457c3195032dd1c3848e21dc95ed02c92f8cfa/migration_sql.go#L44 this is more a workaround because sql syntax is hard. –  Jul 24 '21 at 22:23
  • `I want my migration script to roll back when there is an error.` have you tried it already ? –  Jul 24 '21 at 22:23
  • you can read here what is going on udner the hood https://github.com/steinbacher/goose/blob/dc457c3195032dd1c3848e21dc95ed02c92f8cfa/migration_sql.go#L137 –  Jul 24 '21 at 22:25
  • 1
    finally, this is all closely related to your DB engine https://stackoverflow.com/a/4736346/4466350 –  Jul 24 '21 at 22:27

1 Answers1

0

As per the MySQL 5.7 documentation, most DDL queries cannot be executed as transactions.

So this should be not possible to done with any workaround since it is tied to underlying database type and version.

@mh-cbon thank you you for pointing out that.

Malaka Gunawardhana
  • 285
  • 4
  • 19