在Mybatis迁移工具中的一个事务中运行多个mysql语句 [英] Running multiple mysql statements in one transaction in Mybatis migration tool
问题描述
我正在使用Mybatis迁移工具来维护我们数据库的架构，但是我遇到了以下问题。
I'm using the Mybatis migration tool to maintain the schema to our database but I'm having the following problem.
目前，如果我们在迁移中使用多个语句，它们每个都在一个单独的事务中运行。因此，如果我想要更改2个表（或运行多个语句）作为功能的一部分并且其中一个中断，则必须手动还原任何首先运行的表。但是，如果所有语句都成功完成，则mybatis迁移仅在changelog表中标记为完成。
Currently, if we use multiple statements in a migration they are each run in a separate transaction. So if I want to alter 2 tables (or run multiple statements) as part of a feature and one of them breaks, any that were run first have to be manually reverted. The mybatis migration however is only marked as complete in the changelog table if all statements completed successfully.
这真是令人沮丧，因为如果整个迁移不是自治的，就无法维持一个常量的db状态。
This is really frustrating because there's no way to maintain a constant db state if the entire migration isn't autonomous.
这是我们的测试数据库的mybatis mygration的（相关）设置。
here's the (relevant) setting for mybatis mygration for our test database.
## JDBC connection properties.
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/gamealert_test?allowMultiQueries=true
username=gamealert
password=********
# If set to true, each statement is isolated
# in its own transaction. Otherwise the entire
# script is executed in one transaction.
auto_commit=false
# This controls how statements are delimited.
# By default statements are delimited by an
# end of line semicolon. Some databases may
# (e.g. MS SQL Server) may require a full line
# delimiter such as GO.
delimiter=;
full_line_delimiter=false
# This ignores the line delimiters and
# simply sends the entire script at once.
# Use with JDBC drivers that can accept large
# blocks of delimited text at once.
send_full_script=true
我添加了auto_commit = false，send_full_script = true和allowMultiQueries = true（to url）尝试将整个迁移保留在一个事务中。
I've added auto_commit=false, send_full_script=true and allowMultiQueries=true (to url) in an attempt to keep the whole migration in one transaction.
我是否需要使用mysql url参数来实现此目的？这有可能吗？似乎应该是这样。也许我们只需要为每个语句创建一个迁移，但这似乎过多了。
Are there any mysql url parameters that I need to use to allow this? Is this even possible? Seems like it should be. Maybe we just need to create one migration for each statement but that seems excessive.
以下是澄清的另一个例子
Here's a further example for clarification
示例迁移20110318154857_fix_daily_sales：
Example migration 20110318154857_fix_daily_sales:
--// fix daily_sales naming
-- Migration SQL that makes the change goes here.
ALTER TABLE `daily_sales` CHANGE COLUMN `storeId` `store_id` INT(10) UNSIGNED NOT NULL;
b0rked;
--//@UNDO
-- SQL to undo the change goes here.
... undo sql here ....
如果我运行迁移它失败因为 b0rked; 行符合预期。
migrate status显示迁移为挂起的预期。
If I run migrate up it fails because of the b0rked; line as expected.
migrate status shows the migration as pending as expected.
20110318130407 2011-03-18 17:06:24 create changelog
20110318144341 2011-03-18 17:06:30 fix schedule naming
20110318154857 ...pending... fix daily sales naming
我的数据库却应用了更改！ 不好！
my database however has the changes applied! not good!
describe daily_sales;
+-----------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| store_id | int(10) unsigned | NO | PRI | NULL | |
| sale_date | date | NO | PRI | NULL | |
| type_id | int(10) unsigned | NO | PRI | NULL | |
| tokens | int(10) unsigned | NO | | 0 | |
| dollars | double | NO | | 0 | |
+-----------+------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
有什么方法可以防止这种情况发生吗？我应该将每个语句放在迁移中并继续吗？这就是我现在所处的位置。
Is there any way to prevent this? Should I just put each statement in a migration and move on? that's where I am right now.
提前致谢。
推荐答案
DML绝不是交易性的 - 立即应用。无法回滚
DML is never transactional -- applied immediately. There is no way to roll it back
这篇关于在Mybatis迁移工具中的一个事务中运行多个mysql语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
