Use DELETE FROM instead of TRUNCATE for MySQL

[antigremlin]

Resolves issue #584

In MySQL, TRUNCATE statements cannot be rolled back, at least for 5.7:

• https://dev.mysql.com/doc/refman/5.7/en/truncate-table.html
• https://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html

This is another attempt at fixing, as the previous PR #585 has stalled. The credit goes to @martinarrieta

[antigremlin]

The first PR mentioned above accidentally rearranged imports. This PR only changes the one line necessary. I'd be happy to see this merged soon as this is a critical bug. It nearly did damage to one of my databases.

[coveralls]

Pull Request Test Coverage Report for Build 1560897359

• 2 of 2 (100.0%) changed or added relevant lines in 1 file are covered.
• 12 unchanged lines in 1 file lost coverage.
• Overall coverage increased (+0.06%) to 57.8%

---

Files with Coverage Reduction	New Missed Lines	%
source/migration.go	12	13.64%

Totals
Change from base Build 1424470324:	0.06%
Covered Lines:	3731
Relevant Lines:	6455

---

💛 - Coveralls

[dhui]

Per #584 (comment), the transaction needs to be serializable

[antigremlin]

Per #584 (comment), the transaction needs to be serializable

This PR fixes a very dangerous bug: If the migration process gets killed at the wrong moment, it leaves the version table empty (the truncate was successful, but the insert was interrupted). Next thing that happens is that the tool tries to reapply all the migrations from the beginning, which can be disastrous.

I can mark it as serializable. But why do you think it changes anything? According to https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html serializable only affects SELECT statements, not DELETE and INSERT ones.

[dhui]

I can mark it as serializable. But why do you think it changes anything? According to https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html serializable only affects SELECT statements, not DELETE and INSERT ones.

Please do. For safety, we should use the most strict isolation level. There's nearly no downside and setting the migration version doesn't need to be performant. e.g. we're happy to pay for any overhead between REPEATABLE READ and SERIALIZABLE for the extra safety.
This protects against other instances of migrate reading the migration version, which may later update the version.

[obalunenko]

@antigremlin Can you rebase your branch on base branch and update the PR? 🙏🏿

[antigremlin]

@antigremlin Can you rebase your branch on base branch and update the PR? 🙏🏿

My apologies. Was going to do that and didn't notice the PR is already merged.
Great to see this in. Thank you @dhui!