Add example for GitHub Actions + Proxy

[pidgezero-one]

Question

I can't get my Github Actions runner to properly connect to my MySQL Cloud SQL instance using the cloud-sql-proxy Docker container.

These are the relevant steps of my GHA YML:

    steps:
      - name: Set up Go
        uses: actions/setup-go@v4
        with:
          go-version: '1.21'

      - name: Checkout
        uses: actions/checkout@v3

      # Setup gcloud CLI
      - uses: google-github-actions/setup-gcloud@v0
        with:
          service_account_key: ${{ secrets.AUTH_KEY }}
          project_id: ${{ env.PROJECT_ID }}
          export_default_credentials: true

      - name: Run database migrations
        run: |
          chmod -R +x .
          make proxy_db
          make migration

I can see in the action runner logs that ${{ secrets.AUTH_KEY }} and ${{ env.PROJECT_ID }} are being parsed correctly.

These are the definitions of make proxy_db and make migration in my makefile:

proxy_db:
	docker run -d \
		-p 127.0.0.1:3306:3306 \
		-v $(GOOGLE_APPLICATION_CREDENTIALS):/config \
		gcr.io/cloudsql-docker/gce-proxy:1.16 /cloud_sql_proxy \
		-instances=$(CLOUD_SQL_INSTANCE_NAME)=tcp:0.0.0.0:3306 -credential_file=/config

migration:
	@go run cmd/migration/main.go

The contents of main.go are as follows:

package main

import (
	"database/sql"
	"fmt"
	"time"

	_ "github.com/GoogleCloudPlatform/berglas/pkg/auto"
	"github.com/golang-migrate/migrate/v4"
	"github.com/golang-migrate/migrate/v4/database/mysql"
	_ "github.com/golang-migrate/migrate/v4/database/mysql"
	_ "github.com/golang-migrate/migrate/v4/source/file"
	"github.com/kelseyhightower/envconfig"
)

type variables struct {
	MySQLProxyPort int    `required:"false" envconfig:"mysql_proxy_port"`
	MySQLDB        string `required:"true" envconfig:"mysql_db"`
	MySQLUser      string `required:"true" envconfig:"mysql_user"`
	MySQLPass      string `required:"true" envconfig:"mysql_pass"`
	Env            string `envconfig:"build_env"`
}

const migrationsPath = "scripts/db/migrations"

func main() {
	var v variables
	envconfig.MustProcess("db migration", &v)

	db, err := sql.Open("mysql", getDbSource(v))
	db.SetConnMaxLifetime(time.Minute * 5)
	source := fmt.Sprintf("file://%s", migrationsPath)
	driver, err := mysql.WithInstance(db, &mysql.Config{DatabaseName: v.MySQLDB, StatementTimeout: 300 * time.Second})
	if err != nil {
		panic(err)
	}
	m, err := migrate.NewWithDatabaseInstance(source, v.MySQLDB, driver)
	if err != nil {
		panic(err)
	}

	fmt.Println("Running migrations...")
	if err := m.Up(); err != nil {
		if err == migrate.ErrNoChange {
			fmt.Println("No migrations to run.")
			return
		}

		panic(err)
	}
	fmt.Println("Migrations ran successfully.")
}

func getDbSource(v variables) string {
	tls := fmt.Sprintf("?tls=%t", v.Env == "live")
	return fmt.Sprintf(
		"%s:%s@tcp(localhost:%v)/%s%s",
		v.MySQLUser,
		v.MySQLPass,
		v.MySQLProxyPort,
		v.MySQLDB,
		tls,
	)
}

func getDbUrl(v variables) string {
	return fmt.Sprintf(
		"mysql://%s", getDbSource(v),
	)
}

From action runner logs, I can see that my environment variables (including those which are references to Google Secret Manager secrets and are processed by envconfig) are being processed correctly, so I don't believe there is a problem with my GOOGLE_APPLICATION_CREDENTIALS. My migrationsPath variable in main.go pointing to where my migrations live also doesn't contain any typos.

When this action runs in Github Actions, it downloads the container, outputs a digest message, outputs a status message, and then outputs a hash. It does not output any error messages.

It then tries to run migrations. After about a minute, the Github Action exits with these error logs:

panic: dial tcp 127.0.0.1:3306: connect: connection refused

goroutine 1 [running]:
main.main()
	/home/runner/.../cmd/migration/main.go:35 +0x465
exit status 2
make: *** [makefile:14: migration] Error 1
Error: Process completed with exit code 2.

I've also tried this with a wait script between the two commands of my GHA step, but that didn't make a difference, it says the DB is ready after 0 seconds. I think that the container is being created, but that is not running.

I've also tried using a v1 container:

docker run -d \
		-p 127.0.0.1:3306:3306 \
		-v $(GOOGLE_APPLICATION_CREDENTIALS):/config \
		gcr.io/cloudsql-docker/gce-proxy:1.16 /cloud_sql_proxy \
		-instances=$(CLOUD_SQL_INSTANCE_NAME)=tcp:0.0.0.0:3306 -credential_file=/config

but this doesn't work either, same error.

Port 3306 is open in my project's firewall settings.

What do I need to do to get it to work? On previous projects I've collaborated on, this approach works without issue for Postgres databases, but I can't get it to actually work on my own GCS project.

Code

No response

Additional Details

No response

[enocom]

Making this a feature request since this comes up now and then. Until we document this properly, try the approach shown here: #1989 (comment).

[enocom]

Note that's using Workload Identify Federation 1, but if you want to use a credential file, the approach is comparable.

[pidgezero-one]

Thank you for the suggestion! I added the commands in "Enable cloud SQL proxy to temp instance" to a new makefile command that I'm running in my workflow now, which appears to be running correctly and not erroring out, but Mysql is now giving me "unexpected EOF" errors in my migration step:

[mysql] 2024/02/21 02:00:31 packets.go:37: unexpected EOF
[mysql] 2024/02/21 02:00:31 packets.go:37: unexpected EOF
[mysql] 2024/02/21 02:00:31 packets.go:37: unexpected EOF
panic: driver: bad connection

[enocom]

Have you looked at the Proxy's logs at all to confirm it's working as intended?

[pidgezero-one]

How would I do that in a GH action runner, is there some kind of verbose logging option I can use?

[jackwotherspoon]

@pidgezero-one We now support debug logging as of v2.9.0 and beyond. You can add the flag --debug-logs flag, we also added a section to our README about it.