transaction failed.

[westernmonster]

trafficstars

Here is my migration file, there is a syntax error for test intentionally . When I use command line sql-migrate up, error occurred， migrations table not changed, but two tables are created successfully.

-- +migrate Up
CREATE TABLE `users` (
    `id` bigint(20) NOT NULL,
    `login_name` varchar(50) NOT NULL,
    `password` varchar(50) NOT NULL,
    `real_name` varchar(50) NOT NULL,
    `mobile` varchar(50) NOT NULL,
    `email` varchar(50) NOT NULL,
    `role` bigint(20) NOT NULL,
    `last_login` int(11) NOT NULL,
    `last_ip` int(11) NOT NULL,
    `deleted` int(11) NOT NULL,
    `created_at` int(11) NOT NULL,
    `updated_at` int(11) NOT NULL,
    PRIMARY KEY (`id`)
);
CREATE TABLE `roles` (
    `id` bigint(20) NOT NULL,
    `name` varchar(50) NOT NULL,
    `permissions` varchar(500) NOT NULL,
    `deleted` int(11) NOT NULL,
    `created_at` int(11) NOT NULL,
    `updated_at` int(11) NOT NULL,
    PRIMARY KEY (`id`)
);

INSERT INTO roles(id, name, permissions, deleted, created_at, updated_at)
VALUES(1, 'System', '', 0, 1505874156,1505874156);

INSERT INTO users(id, login_name, password, real_name, mobile, email, role, last_login, last_ip, deleted, created_at, updated_at)
VALUES(1,'System','e10adc3949ba59abbe56e057f20f883e', 'admin', '15308188844', '[email protected]', 0, 1505874156, 'x', 0, 1505874156, 1505874156);
-- +migrate Down
DROP TABLE `users`;
DROP TABLE `roles`;

[rubenv]

Not sure which DB this is, but if it's PostgreSQL then there's nothing much we can do about that. DDL changes in PostgreSQL aren't transactional, so you can't roll them back.

Always test your migrations in development on a throwaway DB, before rolling them out.

[bluven]

The same problem happended with MySQL

Some statements cannot be rolled back. In general, these include data definition language (DDL) statements, such as those that create or drop databases, those that create, drop, or alter tables or stored routines.

You should design your transactions not to include such statements. If you issue a statement early in a transaction that cannot be rolled back, and then another statement later fails, the full effect of the transaction cannot be rolled back in such cases by issuing a ROLLBACK statement.

So I guess the best practice is one ddl per migration.

[leucos]

@rubenv I am surprised. Postgres should support transactions around DDL. Tried it yesterday and it worked (postgres11 at least). The docs seems to confirm this: https://wiki.postgresql.org/wiki/Transactional_DDL_in_PostgreSQL:_A_Competitive_Analysis

(however it definitively does not work in mysql)

[addison511]

I have Same problem in mysql,How can I solve the problem?