Terraform: can not create database using mysql_database resource, missing port in address error

[prit342]

Hi,

I am unable to create MySQL database using the mysql provider which uses mysql_database resource. Looks like a bug in 0.7.0. The error that I am getting is:

dial tcp: missing port in address ${aws_db_instance.mysql_rds.endpoint}

Adding prot 3306 to the endpoint attribute leads to the following error:

• dial tcp: lookup ${aws_db_instance.mysql_rds.endpoint}: invalid domain name

Terraform Version

Terraform v0.7.0

Affected Resource(s)

• mysql_database
  

  Terraform Configuration Files

##### RDS details

resource "aws_security_group" "rds_test" {
  name        = "main_rds_sg"
  description = "Allowallinboundtraffic"
  vpc_id      = "${var.vpc_id}"

  ingress {
    from_port   = 0
    to_port     = 65535
    protocol    = "TCP"
    cidr_blocks = ["10.0.0.0/8"]
  }

  egress {
    from_port   = 0
    to_port     = 0
    protocol    = "-1"
    cidr_blocks = ["0.0.0.0/0"]
  }

  tags {
    Name = "rds_access"
  }
}

resource "aws_db_subnet_group" "rds_access" {
  name        = "${var.environment}-db-access"
  description = "db-subnet-group-for-${var.environment}"
  subnet_ids  = ["${aws_subnet.vpc_subnet.*.id}"]
}

## MySQL RDS instance
resource "aws_db_instance" "mysql_rds" {
  allocated_storage      = "20"
  engine                 = "mysql"
  engine_version         = "5.6.27"
  instance_class         = "db.m4.large"
  identifier             = "${var.environment}-rds1"
  storage_type           = "gp2"
  multi_az               = false
  publicly_accessible    = false
  username               = "root"
  password               = "${var.db_root_pw}"
  db_subnet_group_name   = "${aws_db_subnet_group.rds_access.name}"
  vpc_security_group_ids = ["${aws_security_group.rds_test.id}"]
}

## configure the mysql server using terraform 
provider "mysql" {
  endpoint = "${aws_db_instance.mysql_rds.endpoint}"
  username = "${aws_db_instance.mysql_rds.username}"
  password = "${aws_db_instance.mysql_rds.password}"
}

resource "mysql_database" "web_transfer" {
  name = "web_transfer"
}

Debug Output

https://gist.github.com/prit342/05e9310845fed35823c565bc4f422f35

Panic Output

None as terraform doesn't crash but stops execution

Expected Behavior

The databases should have been created by the database provider.

Actual Behavior

Terraform exists with the following error :+1:

Error refreshing state: 1 error(s) occurred:
* dial tcp: missing port in address ${aws_db_instance.mysql_rds.endpoint}

Steps to Reproduce

Please list the steps required to reproduce the issue, for example:

1. terraform apply

[apparentlymart]

Hi @prit342! Sorry you're having trouble here.

Terraform currently has a limitation where it can't create an entire stack like this where a resource has to be created in order to configure another provider... it fails because the "mysql" provider needs to be instantiated in order to produce the plan, but the aws_db_instance.mysql_rds resource hasn't yet been created.

The workaround for the moment is to force Terraform to create the aws_db_instance resource in isolation first, e.g. like this:

terraform plan -target="aws_db_instance.mysql_rds" -out=tfplan
terraform apply tfplan
terraform plan -out=tfplan
terraform apply tfplan

This workflow allows Terraform to create the resources one "layer" at a time, resolving the sequencing problem I described above.

In the long run we'd like to solve this more cleanly and one proposal for it is #4149. That proposal would effectively make Terraform do the above workaround automatically when it detects it cannot plan everything as a single step. In the mean time I'm going to close this issue, but feel free to reopen it if you have trouble with the above workaround.

[thomaschaaf]

As of 0.8.0 this seems to have stopped working. @apparentlymart could you try and see if your suggestion still works?

[blalor]

This is particularly frustrating because this is literally the exact use case posited in the MySQL provider documentation: https://www.terraform.io/docs/providers/mysql/index.html

[apparentlymart]

Hmm I haven't tried it recently but I can't think of anything that's changed in 0.8 or 0.9 that would've broken my suggestion above.

Agreed that this is frustrating; I've been using Terraform for multi-layer architecture for some time and this has been an ongoing frustration. I'd like to spend some more time on #4149 in the near future since I did have a reasonable prototype of this a while ago but it got invalidated by internal refactoring for 0.9 that should (in theory) make this easier to implement.

[uday3110]

Is there any update to this ? Mine is still broken with v0.9.5. @mitchellh we at Upwork are trying to get our entire infrastructure to move under terraform. We would appreciate some help here.

https://www.terraform.io/docs/providers/mysql/index.html

[apparentlymart]

Hi all! Sorry for the ongoing issues here.

Enough has changed since I offered that workaround above that we should probably start fresh with this one. If the above workaround isn't working in 0.9, it would be helpful to have a new top-level issue discussing that, so we can dig in with the full context. While the _full_ fix of #4149 is still some ways out, I expect we could get to the bottom of why that workaround isn't working and make it or something similar work again as a stopgap.

[uday3110]

@apparentlymart I did not check the workaround and I am sure it will work. But respectfully it a hacky solution at best. This is not something that is clean enough to be imported into a CI/CD type of solution. We have Codeship deploying our terraform code and It would be difficult for us to rework this solution in there just for this problem.

[DonBower]

Still broken as of 0.9.8
perhaps it would be easier if we had a database {name=xxx} sub command of the aws_db_instance resource?

i.e.:
resource "aws_db_instance" "opsmanager" {
depends_on = ["aws_db_subnet_group.opsmanager"]
identifier = "${var.rds-opsmanager_identifier}"
allocated_storage = "${var.rds-opsmanager_allocated_storage}"
engine = "${var.rds-opsmanager_engine}"
engine_version = "${var.rds-opsmanager_engine_version}"
instance_class = "${var.rds-opsmanager_instance_class}"
name = "${var.rds-opsmanager_db_name}"
username = "${var.rds-opsmanager_db_user}"
password = "${var.rds-opsmanager_db_passwd}"
db_subnet_group_name = "${var.rds-opsmanager_db_subnet_group}"
vpc_security_group_ids = ["${var.opsmanager_sgid}"]
storage_encrypted = "true"
publicly_accessible = "false"
iops = 1000
multi_az = "true"
backup_retention_period = "10"
skip_final_snapshot = "true"
backup_window = "00:00-00:30"

database { name = "uaa" }
database { name = "ccdb" }
}

[mcoulombe636]

Any up date on this issue?

This is extremely frustrating to not even be able to do a "terraform plan" unless some of your infrastructure is already deployed. It breaks the usefulness of a dry-run and forces you to use a hacky solution to support the most basic use cases of the mysql provider.

[bensquire]

Just tried this work around and not working for me either :( Got the error:

provider.mysql: dial tcp 10.10.4.193:3306: getsockopt: operation timed out

UPDATE: This was actually a problem with security groups, @apparentlymart 's workaround worked as described.

[CalebKing3]

I'm running into this issue, trying to get zipkin working mysql
provider.mysql: dial tcp 127.0.0.1:3306: getsockopt: connection refused

[yuranos]

Terraform currently has a limitation where it can't create an entire stack like this where a resource has to be created in order to configure another provider... it fails because the "mysql" provider needs to be instantiated in order to produce the plan, but the aws_db_instance.mysql_rds resource hasn't yet been created.

Hi @apparentlymart Based on #12393 I guess there's no issue with interpolating into a provider based on the result of a resource any longer? Even without progressive behavior.

Hmm I think this must be a 0.9 regression because I have plenty of configs using this sort of setup

Otherwise, I'm really confused by this comment in #12393.

[dgerardbmj]

For what it's worth--using v0.11.1, I can create a MySQL (actually MariaDB) RDS with a database, and a second database, in the same run. So it works now, for me at least.

The main "gotcha" was making sure I added the Terraform server to the RDS' security group for port 3306.

[najisawas]

@dgerardbmj could you share how you did this? I'm currently having the same issue, with a timeout error. Here is what i'm doing https://github.com/terraform-providers/terraform-provider-aws/issues/6438

[dgerardbmj]

The code below is from our RDS configuration (with name and password changed). It creates a MariaDB RDS with two databases called "wiki1" and "wiki2". Username is "wikiuser" and password is "password" for both--I couldn't see how to give wiki2 a different username/password.

There are many variables here that are our internal variables--but you should get the idea.

resource "aws_db_instance" "php-internal" {
identifier = "${var.product}-${var.stack}"
allocated_storage = 8
storage_type = "gp2"
engine = "mariadb"
engine_version = "10.2"
instance_class = "${var.rds_instance_class}"
name = "wiki1"
username = "wikiuser"
password = "PASSWORD"
db_subnet_group_name = "${aws_db_subnet_group.php-internal.id}"
parameter_group_name = "default.mariadb10.2"
snapshot_identifier = "${module.snapshot.snapshot_identifier}"
final_snapshot_identifier = "${var.product}-${var.stack}-${random_id.db.hex}"
vpc_security_group_ids = ["${aws_security_group.rds.id}"]

tags {
Name = "${var.product}-${var.stack}"
Product = "${var.product}"
Project = "${var.project}"
Stack = "${var.stack}"
Creator = "${var.creator}"
ManagedBy = "terraform"
}

lifecycle {
ignore_changes = ["snapshot_identifier"]
}
}

provider "mysql" {
endpoint = "${aws_db_instance.php-internal.endpoint}"
username = "${aws_db_instance.php-internal.username}"
password = "${aws_db_instance.php-internal.password}"
}

resource "mysql_database" "wiki2" {
name = "wiki2"
}

This did not work until I opened port 3306 for access by the Terraform server--I added it to the ingress for the security group for the RDS. The Terraform server has to access the RDS to create the second database.

[hashibot[bot]]

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.