Database MySQL strange behaviour

1

Hello everyone, I’m having a rather strange issue that’s been occurring during some tests I’m conducting. I’ve set up a MySQL database and can access it through my SpringBoot application.

However, when I perform actions like dropping a table while connected via proxy/wireguard, it seems to work fine, but the application still sees the table.

When I log into the application machine with fly ssh console and connect to the database via mysql CLI using the same credentials and the .internal host, the table is still there, as if what I did using the proxy had no effect. And I need this to work correctly.

2 Likes
2

Hi… Perhaps you could show the details of the fly proxy and local MySQL client invocations that you were using? This might be related to MySQL’s special handling of localhost, but that theory would rest on how exactly the connections were made…

3

Added databases

4

this is my toml

# fly.toml app configuration file generated for i9-database on 2024-04-17T08:33:22-03:00
#
# See https://fly.io/docs/reference/configuration/ for information about how to use this file.
#

app = 'i9-database'
primary_region = 'gru'

[build]

[processes]
  app = '--datadir /data/mysql'

[mounts]
  source = "mysqldata"
  destination = "/data"

[http_service]
  internal_port = 3306
  force_https = true
  auto_stop_machines = false
  auto_start_machines = true
  min_machines_running = 0
  processes = ['app']

[[vm]]
  memory = '2gb'
  cpu_kind = 'shared'
  cpus = 1

I connect using fly proxy 3306 -a i9-database.

It seems there’s a delay between changes made in the database and their reflection on my API. When I drop the schema, I expect to receive a negative response from the API, but initially, it returns positively. After a short wait, the change takes effect, and I receive the expected negative response from the API.

5

Hm… That rules out my original theory, then.

Maybe innodb_print_ddl_logs would help clarify things a little? :thought_balloon:

If I understand correctly, major changes to DDL handling were introduced with MySQL 8.

Aside: Did you really want the [http_service] section—and the two public IP addresses connecting into the i9-database app?

1 Like
6

I didn’t need the http_service section; I only added it for testing purposes. Now, after several days of the machine being up, it has become unreachable. I don’t know what I did wrong, haha!

7

So, perhaps I’ve found the problem. Suddenly, I couldn’t connect to my database, so we investigated and discovered that I had two machines running, which isn’t typical for MySQL. This might have led to incorrect connections or ghost connections, causing issues with connecting via the proxy in the past day. To resolve this, I cloned the machine and its volume, then ensured only one machine was running, and now everything works again.

2 Likes
8

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.