Implement State Backend (with locking) on MySQL/MariaDB

[dihedron]

Proposal

The current version of Terraform does not support MySQL/MariaDB as a backend for tfstate management.
After taking a look at the sources, it seems to me that the addition should be attainable with limited effort; at first glance it looks like these are the necessary changes:

1. copy the PG state backend
2. update the imports to load the MySQL support library and update all references to postgres with mysql in sql.Open instructions
3. change the datatypes for name and state to LONGTEXT (IIRC TEXT on MySQL can store up to 65k, whereas on Postgres it has no limit);
4. update the create table SQL statement to add the unique index on name contextually, in order to avoid the following create unique index statement.
5. change the Put() func in client.go so that the SQL reads

INSERT INTO %s.%s (name, data) VALUES ($1, $2)
		ON DUPLICATE KEY UPDATE data = $2`

6. modify the lock logic so that it uses select get_lock(name, timeout) ( see https://dev.mysql.com/doc/refman/5.7/en/locking-functions.html).

The code changes don't seem to be overwhelming (am I missing something???) and it looks like it may even be possible to have a single "RDBMS" backend with support for both PG and MySQL by adding a single parameter to the backend db_type = "mysql|posgres" and a few switches where the SQL syntax differs. I expect Oracle to be quite like Postgres but have not investigated yet and have no Oracle DB available at home.

I have very little spare time but I'd be very glad to invest it in implementing this feature if you think it may be useful to others too.

[romaninsh]

What advantage would this offer over encrypted S3 / DynamoDB storage?

[dihedron]

Large enterprises sometimes prefer to keep their state on premises; I guess that's what the Postgres remote state backend is for; etcv2 and etcv3 can have the same role, but they have a hard limit on key and tfstate size, so as of today one is effectively left with a limited choice. So, why not add a MySQL state backend? Some shops may even prefer MySQL over Postgres...

Terraform is not only for cloud systems, we're using it for internal systems management (AD, Oracle DB, Jenkins...) with a few custom providers and no public cloud resource involved and it plays its role nicely.

That said, I'd like to point out that S3 has a limitation when not used in the implementation provided my AWS: lack of locking (sorry, no DynamoDB on premises...). The "standard" S3 APIs only support "WORM" locks on a specific version of an object in a versioned bucket, no advisory locks. For enterprises that have bought their own appliances exporting S3 storage - BTW there's a whole market with players such as IBM, Hitachi, NetApp, Scality, EMC and many others - it's a big shortcoming.

I believe that maybe workspace locking should be considered a cross-cutting concern that might, as well as might not, be implemented by the same state backend provider. That's what I'm planning to implement next on S3 state backend once I've cut my teeth on MySQL, in the hope it will be accepted upstream someday.

[dihedron]

Please take a look at the pull request, we're here for your comments and suggestions.

[danieldreier]

@dihedron I just closed the linked PR because we are not currently accepting community-contributed state backends. I wrote up a full explanation of why in that PR, so I'll quote it here in case other folks are watching:

I really appreciate you making an effort to contribute to Terraform, and to build a MySQL state backend. Your motivation here makes sense to me - a lot of people use MySQL, and it's available just about everywhere. Unfortunately, we’re not going to be able to accept this PR, and I want to explain why, and what your options are.

When we adopt new state backends in Terraform core, we take on significant responsibility, because problems with state backends significantly impact users. At the same time, it’s very difficult for us to maintain community-contributed state backends, because we have to maintain code and handle bug reports for integrations we lack domain knowledge in. We’ve tried, and it has been unsatisfactory for our users, open source contributors, and our engineers.

For example, we’ve found that subtle locking bugs are almost inevitable: even very good engineers often miss them, and when we accept community-contributed state backends, we end up responsible for testing and validation of distributed systems that we lack domain knowledge in. This part of the codebase is relatively hard to get right, and bugs in it have an extremely high impact on users. Our experience has been that we cannot guarantee the high quality that users need when we accept third party state backends, and so we have made the difficult decision to stop accepting new community-contributed state backends.

In the future, we are hoping to build a plugin system, similar to the provider system, for allowing external contributors to build state backend plugins that are not built into Terraform core. When that is available, I hope you will build a MySQL state backend in that system.

Until we have built a plugin system, we will not be accepting any new state backends that are not maintained by HashiCorp employees, and so I am closing this pull request. We do understand why people want to store state in additional backends, so it was difficult to come to this conclusion. I apologize for how long it has taken me to respond to your PR. If you would like to discuss this further, feel free to reach out to me at [email protected] or to Petros Kolyvas, our Product Manager, at [email protected].

[ghost]

I'm going to lock this issue because it has been closed for 30 days ⏳. This helps our maintainers find and focus on the active issues.

If you have found a problem that seems similar to this, please open a new issue and complete the issue template so we can capture all the details necessary to investigate further.