search

GOCDB / gocdb-failover-scripts

Scripts and doc for hosting a failover
1 stars 2 forks source link

MariaDB import not atomic #15

Open gregcorbett opened 2 years ago

gregcorbett commented 2 years ago

Given the structure of the MariaDB dump file, below, each table in turn is dropped and recreated.

DROP TABLE IF EXISTS `countries`;
CREATE TABLE `countries` (...);
INSERT INTO `countries` VALUES (...);

The import will error out if one of these SQL commands fail.

This would lead to an inconsistent database, with some of the database having been updated (i.e. the commands run before the error) and some of it not having been updated (i.e. commands that didn't run because an earlier command failed). It could also result in a table not exisiting or having no data (i.e. the table whose CREATE or INSERT commands failed).

Ideally, the import would be atomic and either successed at importing all the new data, or roll back to the previous state before the import was attempted.

tofu-rocketry commented 2 years ago

Do transactions work with drop table?