Database troubleshooting

# Can't connect from local

The most common misunderstanding when trying to connect from a local machine, is that people overlook to first open up the SSH tunnel and then connect to the database. Graphical MySQL clients support this connection method out of the box.

You'll need to enter both: SSH access and MySQL access details. Within the fortrabbit dashboard under your App > Access, there is a small link labeled: "Show SSH tunnel info" which will reveal everything you'll need to enter in a MySQL client to connect to the remote database.

See the MySQL access guides here too.

# Missing INSERT privileges

You can not INSERT to the database any more? Likely you see something like this:

GRANT SELECT, UPDATE, DELETE, DROP, ALTER ON `{{app-env-id}}`.* TO `{{app-env-id}}`@`%`

The database of the environment is full. It exceeded the storage size available and autoscaling is not enabled. In many cases, the database grows with passive usage. What you can do:

• Upgrade the plan or enable autoscaling in the dashboard
• Query large tables, see below
• Reduce the database size by removing table rows

-- Delete table rows with conditions
DELETE FROM table_name WHERE condition;

-- Remove all rows from a table
TRUNCATE TABLE table_name;

-- Drop a table entirely
DROP TABLE table_name;

See our MySQL scaling section for some more details.

# Query large tables

How to get the size of a table in MySQL? Use the following MySQL query on your database to find out which tables are taking most space.

SELECT
  TABLE_NAME AS `Table`,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
FROM
  information_schema.TABLES
WHERE
  TABLE_SCHEMA = {{app-env-id}}
ORDER BY
  (DATA_LENGTH + INDEX_LENGTH)
DESC;

# max_user_connections error

You'll see a max_user_connections error when you've reached the max connection limit.

SQLSTATE[HY000] [1226] User 'xxxxxx' has exceeded the 'max_user_connections' resource (current value: 10)

The limit is defined by the MySQL component. Typical causes are:

• too many concurrent connections from the web
• every requests starts multiple connections to MySQL
• a background cron job or worker keeps connections open for a long time
• lingering queries that run for a long time
• MySQL GUI client opens too many connections (see below)

Figure out what's causing connections to linger → eliminate those causes.

# GUI clients eating MySQL connections

You are trying to connect to the database with a MySQL GUI, like Navicat, Workbench or Sequel Ace? Some those clients are "eating" MySQL connections like popcorn. With fortrabbit, the MySQL connections and the PHP processes are limited. If you use up all the allotted connections, it can take a little until the App recovers. If this turns out to be a problem for you, look into limiting the number of concurrent connections from your MySQL gui or the App itself.

# Access denied; missing SUPER privileges

When importing a database dump you've created with a recent version of mysqldump, you may experience errors like this:

#1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation

To prevent this error, create the dump again using the --set-gtid-purged=OFF option. If you don't use the mysqldump command line tool directly, but a GUI that relies on it, the chance is very high there is a checkbox to disable "GTID PURGED".

# Connection timeouts during large imports

• Split large dumps into smaller files
• Use --single-transaction flag for consistent snapshots

# Permission errors

• Ensure you're using correct MySQL credentials from the dashboard
• Add --set-gtid-purged=OFF to the mysqldump command to maximize compatibility
• Add --no-tablespaces to the mysqldump command to maximize compatibility
• Check that you're running commands from your local machine, not via SSH

# Encoding issues

• Add --default-character-set=utf8mb4 to both export and import commands
• Ensure your dump file is saved with UTF-8 encoding