r/mysql

As the title says, I've built VirtualDB and I'm looking for some feedback.

The main goal is to stop having to clone a database just to spin up a dev environment or CI Pipeline. So, why not just let the reads go straight to prod, catch the writes, and merge the delta. Hope y'all can take a look and let me know if this is going down the right track.

https://virtualdb.io

Full disclaimer, this is in a very new alpha state, so if you want to pull it down and try it **Please** be careful. It should only need Read access to the source DB, so feel free to limit what it can do to the source with your own permission grants.

I've had a webhosting account for about 20 years. I just received a notice that I have 158 databases on my account that only allows 100.

So, I would guess I have many unused databases. I used to uninstall things somewhat haphazardly, and am trying to run things better.

When I look at MySQL on Cpanel, it only tells me the database name, size, and priviliged users, so I'm not sure whats safe to delete. There's a few that have 0kb so I'm guessing those can be deleted, but I'm not sure.

So, any advice on safely deleting these databases is appreciated. Thank you.

I'm writing a series of blog posts explaining how MySQL Binary Log works internally. The 8th post we cover the 3 events that are generated during DML's.

One of the annoying things about EXPLAIN on MySQL is that the capabilities depend on your server version. EXPLAIN FORMAT=JSON? Only MySQL 5.6+. EXPLAIN ANALYZE? MySQL 8.0.18+. MariaDB? Different syntax entirely — ANALYZE FORMAT=JSON instead. And if you're on something older, you get the classic tabular output and that's it.

I'm building Visual EXPLAIN into Tabularis (open-source desktop DB client, Tauri + React + Rust) and I've been spending a good chunk of time trying to make this work transparently across MySQL and MariaDB versions.

How the version detection works:

When you click Explain, Tabularis runs SELECT VERSION(), parses the result, and picks the best available format:

• MySQL 8.0.18+ → EXPLAIN ANALYZE (text tree with actual execution data)
• MySQL 5.6+ → EXPLAIN FORMAT=JSON (structured plan, estimates only)
• MariaDB 10.1+ → ANALYZE FORMAT=JSON (JSON with both estimated and actual r_* fields)
• MariaDB 10.1+ → EXPLAIN FORMAT=JSON (estimates only, when ANALYZE is off)
• Older → tabular EXPLAIN fallback

You don't configure anything. It just works — or at least, that's the goal.

The result is shown as an interactive graph — every operation is a node, connected by edges showing data flow. Nodes are color-coded by relative cost (green/yellow/red). There's also a table view with an expandable tree and detail panel, the raw output in Monaco, and an AI analysis tab that sends the plan to your AI provider for optimization suggestions.

DML protection is built in: the ANALYZE toggle is off by default for INSERT/UPDATE/DELETE, with a warning. DDL statements are blocked entirely.

What I need:

MySQL and MariaDB EXPLAIN output has a lot of version-specific quirks. The JSON structure is different between MySQL and MariaDB, the text tree format for EXPLAIN ANALYZE needs specific parsing, and there are edge cases I'm sure I haven't hit yet. I'm looking for people willing to test this against their servers — different versions, different query patterns. If the parsing breaks on a specific query, a bug report with the raw EXPLAIN output would be incredibly helpful.

Development is on the feat/visual-explain-analyze branch. Repo: GitHub.

Blog post with screenshots: https://tabularis.dev/blog/visual-explain-query-plan-analysis

Hi,

Its Mysql aurora database. We are having an OLTP application which is hosted on this mysql database, this is going to be write heavy with additional futue workload. We want to see what maximum TPS this can accomodate on this system. I understand the TPS varies from system to system based on what a transaction means etc.

However, I want to understand if any specific parameters we should tweak or statistics/metrics we should look after , for the write heavy workload testing in a mysql database to perform at its best? Any obvious issues or contention points which we should be aware of during this? Need guidance here.

Refer to the address, store, staff, and customer tables of the Sakila database. In this lab, these tables initially have the same columns as in Sakila.

Step 1. Remove the phone column from address. This column is replaced by the new strong entity.

Step 2. Implement the strong entity as a new phone table. Specify data types VARCHAR(12) for phone_type and INTEGER UNSIGNED for other columns. Specify a suitable primary key and NOT NULL constraints according to the diagram. 

Step 3. Implement the has relationships as foreign keys in customer, staff, and store. Specify UNIQUE constraints according to the diagram. Specify SET NULL for delete rules and CASCADE for update rules, as follows:

ALTER TABLE customer 
ADD FOREIGN KEY (phone_id) REFERENCES phone(phone_id) ON DELETE SET NULL ON UPDATE CASCADE;

Here are the provided tables:

-- Drop all existing tables
DROP TABLE IF EXISTS address, customer, staff, store, phone;


-- Create address, customer, staff, and store tables
CREATE TABLE address (
  address_id smallint unsigned NOT NULL AUTO_INCREMENT,
  address varchar(50) NOT NULL,
  address2 varchar(50) DEFAULT NULL,
  district varchar(20) NOT NULL,
  city_id smallint unsigned NOT NULL,
  postal_code varchar(10) DEFAULT NULL,
  phone varchar(20) NOT NULL,
  location geometry NOT NULL 
/*!80003 SRID 0 */
,
  last_update timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (address_id)
);


CREATE TABLE customer (
  customer_id smallint unsigned NOT NULL AUTO_INCREMENT,
  store_id tinyint unsigned NOT NULL,
  first_name varchar(45) NOT NULL,
  last_name varchar(45) NOT NULL,
  email varchar(50) DEFAULT NULL,
  address_id smallint unsigned NOT NULL,
  active tinyint(1) NOT NULL DEFAULT '1',
  create_date datetime NOT NULL,
  last_update timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (customer_id)
);


CREATE TABLE staff (
  staff_id tinyint unsigned NOT NULL AUTO_INCREMENT,
  first_name varchar(45) NOT NULL,
  last_name varchar(45) NOT NULL,
  address_id smallint unsigned NOT NULL,
  picture blob,
  email varchar(50) DEFAULT NULL,
  store_id tinyint unsigned NOT NULL,
  active tinyint(1) NOT NULL DEFAULT '1',
  username varchar(16) NOT NULL,
  password varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  last_update timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (staff_id)
);


CREATE TABLE store (
  store_id tinyint unsigned NOT NULL AUTO_INCREMENT,
  manager_staff_id tinyint unsigned NOT NULL,
  address_id smallint unsigned NOT NULL,
  last_update timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (store_id)
);

Here is my code:

-- Initialize database
source Initialize.sql


ALTER TABLE address DROP COLUMN phone;


CREATE TABLE phone (
    phone_id INTEGER UNSIGNED NOT NULL UNIQUE,
    country_code INTEGER UNSIGNED NOT NULL,
    phone_number INTEGER UNSIGNED NOT NULL,
    phone_type VARCHAR(12),
    PRIMARY KEY (phone_id),
    FOREIGN KEY (phone_id) REFERENCES customer (customer_id),
    FOREIGN KEY (phone_id) REFERENCES staff (staff_id),
    FOREIGN KEY (phone_id) REFERENCES store (store_id)
);


ALTER TABLE customer
ADD FOREIGN KEY (phone_id) REFERENCES phone(phone_id) ON DELETE SET NULL ON UPDATE CASCADE;



SELECT * 
FROM phone, staff, store, customer, address;

Here is the error I keep getting:

ERROR 3780 (HY000) at line 6: Referencing column 'phone_id' and referenced column 'customer_id' in foreign key constraint 'phone_ibfk_1' are incompatible.

➜

I know the error means that the referenced data subtypes are incompatable but i cannot change the provided tables, I do not know what to do

My SQL port keeps crashing. I’m using xampp suddenly start showing this port is being occupied. Then I have to rename the data folder as data old. Then copy the backup and paste it back in the data folder and then suddenly it starts working, how to fix it??????

Hey

Does anyone have good tips or a recommended dev workflow for handling SQL tickets in Jira through Cursor?

What I’m aiming for is something like this:

Open a specific Jira ticket via the Jira MCP.

Have Cursor read the ticket text/details.

Let Cursor understand what needs to be created or changed in SQL based on the ticket.

Use that understanding to implement the SQL work cleanly and consistently.

I’m especially interested in best practices around prompt structure, validation steps, and how much context to pass from Jira into Cursor so it can generate the right SQL safely.

Any examples or lessons learned would be really helpful.

After my first DB class, I got interested in building a real working one, so for fun, I created a Voyager-inspired record-keeping system for a records office to log basic activities while reviewing the crew’s adventure footage to ensure the logs are in order. I used MySQL for my database and had to trim a lot of excess because I kept getting caught up in how a real starship might record data, and some of those quirks may still be visible in my schema. This is a V1, with plans to reassess my database schema and update a lot of UI elements to look more polished and have less technical jargon on the front end.

I’m really interested in getting feedback on how users interacting with the current database might impact performance if I were to host a server and turn it into a fun live project for Star Trek fans down the road. In V1, I used a base dataset as starting information, which users can then expand on, with their own database layered over the original.

Big thanks to people like u/corship who suggested I run this into APIs, which I haven’t tried before. Any advice on the state of my initial DB is welcomed.

Raven8472/voyager-database: Star Trek Voyager LCARS-themed crew database and API project.

what is the best way to migrate MySQL cloudSQL databases (5 TB) to local system?

I want to have Gitea and Matomo talk to MySQL using that socket. Can two different processes talk to mysql using that single socket?

I built a small personal tool to improve understanding of execution order when working with queries, and I’m looking for a few people to try it and give quick feedback (5–10 mins).

If you’re already running queries in VS Code (MySQL DB) , I’d really appreciate your help 🙏

For anyone doing their first contribution to MySQL or MariaDB: start out by learning how the mysql/mariadb-test-run command works and how to efficiently rebuild the sources and re-run the test suite.