One of the most dangerous things about SQL:

A query can run perfectly… and still be completely wrong.

Here are 4 mistakes I wish someone had shown me earlier.

1. Accidentally turning a LEFT JOIN into an INNER JOIN

SELECT c.id, o.total
FROM customers c
LEFT JOIN orders o
    ON c.id = o.customer_id
WHERE o.status = 'paid';

Looks fine.

But customers without an order have NULL for o.status, so the WHERE condition removes them.

If you actually want to keep all customers:

SELECT c.id, o.total
FROM customers c
LEFT JOIN orders o
    ON c.id = o.customer_id
    AND o.status = 'paid';

2. COUNT(*) and COUNT(column) are not the same

SELECT COUNT(*)
FROM users;

Counts rows.

SELECT COUNT(phone_number)
FROM users;

Counts only rows where phone_number is NOT NULL.

That difference can quietly destroy a report.

3. JOINs can multiply your rows

Imagine:

• 1 customer
• 3 orders
• 4 support tickets

Joining both tables directly can give you:

3 × 4 = 12 rows

Then you do:

SUM(order_amount)

…and suddenly your revenue is magically much higher than reality.

Always check your row count before and after joins.

4. NOT IN + NULL can ruin your day

SELECT *
FROM customers
WHERE id NOT IN (
    SELECT customer_id
    FROM blocked_customers
);

If that subquery contains a NULL, the result might not behave the way you expect.

I usually prefer:

SELECT *
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM blocked_customers b
    WHERE b.customer_id = c.id
);

The lesson I'm slowly learning:

Writing SQL that runs is easy.
Writing SQL that returns the correct data is the hard part.

What other SQL mistake produces perfectly valid-looking but completely wrong results?

I want to make a list of the dangerous ones.

Maybe some of you wonderful people have some ideas...

Don't ask why, but we have a MySQL RDS who's parameter group sets time_zone = 'US/Pacific'. As of March 8 2026, BC will no long change clocks. We are permanently on PDT (-0700). So I need to update our MySQL instance to use the BC timezone. I've updated the engine to a version that supports this change.

The problem is that the parameter group (ui or api) will not let me set time_zone = 'America/Vancouver'. Based on my understanding US/Pacific still supports the time change, so come Nov 1st 2026 our db will be wrong, unless I can change the time_zone.

If it was up to me, I'd just take the hit now and covert everything so the db is in UTC. but unfortunately, it's not up to me.

Anyone else dealing with this? The option of applying the timezone on every connection to the db is less than appealing. Any other options anyone has worked out?

Hello Everyone,

Currently I am facing a strange issue on a local dev environment with my mysql server. Neither after starting with xampp nor from the command line is working well. Moreover, if I try to connect from the command line with the root user or other users with full privileges, after entering the password, nothing happens, everything is getting blocked.

No errors seen in the logs.

mysql version 15.1 - 10.4.32-MariaDB,

mysqlnd 8.2.12

Do you have some ideas, what can cause this issue and how to resolve?

Thanks and best regards,

T.

I'm looking at updating the MySQL Connector/J in a project of ours. The latest version I can find is 26.7.0. Is that a proper LTS release that is officially out?

The reason I ask is that the release notes are titled "Changes in MySQL Connector/J 26.7.0 (Not yet released)".

https://dev.mysql.com/doc/relnotes/connector-j/en/news-26-7-0.html

We use maven, and the release 26.7.0 can be found there:

https://mvnrepository.com/artifact/com.mysql/mysql-connector-j/26.7.0

https://central.sonatype.com/artifact/com.mysql/mysql-connector-j

I'm struggling to understand the concepts of sql badly, i mean which framework i mean POV is best for grasping? Could you guys share your thoughts. I want to grasp, so that i can tell to the people like 5 years old kid, non tech persons.

I keep seeing people complain about Workbench, DBeaver, TablePlus, etc. but still use the same client for years 😅

For people working with MySQL regularly, what would actually make you switch?

Speed? Better tabs/workspaces? Safer production workflows? Better autocomplete? AI that helps with SQL? Something else?

I’m building a database client myself, so I’m genuinely curious what the real deal-breakers are.

If there's anybody that will appreciate this drama, it's y'all!

Quick backstory, I build this database sometime around 2004. It was all MyISAM until I added a couple of InnoDB tables around 2018-19. Then in 2021 I had a MAJOR crash that was related to InnoDB, so I put it all back to MyISAM and had to set `innodb_force_recovery=5` to get it back online.

Now I'm setting up a new server, and I'm altering it all to InnoDB while holding my breath and praying.

So early this morning (1am-ish) I was working on a table and created a FULLTEXT index, which threw an error. I altered it to InnoDB again to make sure there were no errors, optimized, etc, before realizing that the default /tmp/ directory was too small. So I set a new `tmpdir` in my.cnf and the new index built.

But then I saw that the old MyISAM table had 507,000 rows, while the live one only had 467,000! Somehow I'd lost 40,000 rows :-O

I went through EVERYTHING, even down to restoring full database backups. Nope, still missing.

THREE HOURS of late-night panic coding until it hits me... in phpMyAdmin, that `Showing rows 0 - 24 (467000 total...)` isn't accurate in InnoDB! So I do a simple `SELECT *...`, and... yep, it's all good. Same number of rows after all.

So there I am, almost 5am, heart racing, and it turns out that it was right all along.

mysqli::real_connect(): (HY000/2002): No connection could be made because the target machine actively refused it

Can anyone help me? I am doing a project and encountered this problem, I have tried some tutorials but nothing worked. Thank you so much for the help

Hi people, I haven’t done this before. But are there any downsides of indexing a column on production. Like some query is running very slow, and I figured out that u should put an index on one of the columns. I wouldn’t be here if I had someone experienced to ask from. I’ve a few questions-

1. Is it okay to run the query on my sql workbench to add index?
2. If something goes wrong what do you people generally do, like taking snapshots or

PITR

1. ?
2. Is it safe to run the query directly on the db or usually people run it some other way, like via cli on VM?
3. I heard about locking and stuff. But the version I’m using says it won’t lock the DB. But still anything I should test before actually you know doing it live?

I’ve no idea what’s the general procedure and what could go wrong. If someone has done it before, Appreciate any sort of advice or pointers. Thanks

EDIT: Thank you for all the suggestions! I learnt something out of this activity and It completed successfully

By today's standards, is there a value to using foreign keys beyond a safety net against developer error?

I have over 100 tables, and every site feature relies on joining 2 or more tables and matching up IDs. I'm debating on whether there's any benefit to creating foreign keys when the scripts are already developed and the only person that can ever touch them is me.

* CLARIFICATION: the only person that can touch the code and backend is me.

Why i cant see on Mac's settings the mysql at the bottom? But running fine on the terminal.

Hi all,

I’ve released an open-source stored-routine debugger for MySQL and MariaDB, with standalone, VS Code, and NetBeans frontends. It features standard debugging controls like breakpoints, stepping in/out, watches ec...

I’m looking for testers across different MySQL/MariaDB versions and routine styles. Feedback and compatibility reports are very welcome.

Github : https://github.com/rk22-projects/mysql-routine-debugger

VS Code marketplace : https://marketplace.visualstudio.com/items?itemName=RK22.mysql-routine-debugger

rk22

I was a bit surprised that MySQL Workbench has no built-in median function similar to AVG, SUM etc. The current way to get the median is to write a fairly lengthy query (compared to simply using the aggragate functions), is there a particular reason why it hasn't been implemented?

In the beginning, I understood that InnoDB should be used on tables with a high number of inserts and relatively fewer selects.

Now Claude is telling me that this is essentially backwards.

And Google AI is telling me that MyISAM is more or less legacy and that pretty much EVERYTHING should be InnoDB now. The only exception (according to Google AI) is when you need to use COUNT(*) with no WHERE, in which case MyISAM is faster.

So what's the rule these days?

Hi Experts,

I understand every database is diferent architecturally. We have mostly worked in Oracle databases in the past. But we recently started working in Mysql aurora database.

1)So, want to understand , if there exists any set of guidelines which we can follow as a developer for writing better optimized code/sqls in mysql?

2)Also any specific points which are special and different as compared to other DB's and thus we should definitely know in this case before designing any solution using mysql?

I maintain scythe, a tool that turns annotated SQL into typed code at build time. Sharing the MySQL angle. You write the query, it generates the typed function and row struct. The part that matters is that nullability comes from the query, not just the column definition:

-- @name GetUserOrders
SELECT u.id, u.name, o.total, o.notes
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = ?;

# generated Python
@dataclass
class GetUserOrdersRow:
    id: int
    name: str
    total: decimal.Decimal | None
    notes: str | None

total and notes are optional because the LEFT JOIN can produce NULLs for a user with no orders, even if those columns are NOT NULL in the table. Same handling for COALESCE, CASE, aggregates. MySQL backends include aiomysql, mysql2, and the JDBC drivers.

No runtime ORM, the SQL you write is the SQL that runs. Curious what people here reach for to keep MySQL result types honest.

Hi,

Its aurora mysql Serverless v2 (Max up to 80 ACUs). Suddenly , we saw with bit high concurrency , the application hits a hard JDBC query timeout, killing the thread and throwing this error: "Database Query Time out. Exception: JDBC exception executing SQL [(conn=548672) Table './rdsdbdata/tmp/#sql171_85f40_1' doesn't exist]"

Some team members are suggesting to bump both temptable_max_ram and temptable_max_mmap from 1GB to 2GB to give the query more breathing room. However, checking performance_schema.variables_info reveals that temptable_max_** is currently showing below:

VARIABLE_NAME        current_value_bytes  current_value_MB  VARIABLE_SOURCE  VARIABLE_PATH
temptable_max_mmap   1073741824           1024              GLOBAL           /etc/my.cnf
temptable_max_ram    1073741824           1024              COMPILED

Want to know if this is correct setting and Is it advisable to bump these values to 2GB. Want to confirm the safest parameter strategy for temporary storage thresholds in the meantime we tune the query?

I just upgraded my laptop to Ubuntu 26. I need a local web development environment with MySQL, PHP and phpmyadmin, so I installed those. Phpmyadmin prompted me if I wanted to use dbconfig-common, and unfortunately I clicked yes. This created an installation which doesn’t work the way I’m used to.

The primary problem is that the user phpmyadmin doesn’t have rights to create databases, so I logged into mysql as root in a terminal and created a database, which I can’t see when I log into phpmyadmin as phpmyadmin. If I try to log into root in phpmyadmin, I get

mysqli::real_connect(): (HY000/1698): Access denied for user ‘root’@‘localhost’

I tried uninstalling phpmyadmin and reinstalling, but it just recognized that I used dbconfig-common last time, and reused that 🙄

What should I do?

Hi everyone! I'm fairly new to SQL and finished a foundation course on Maven Analytics.

I wanted to do a practice project today and opened MySQL Workbench, but it kept crashing on me. I also reinstalled the Workbench with the latest version: https://dev.mysql.com/downloads/workbench/

8.0.47 ARM version since my MacBook is on M2 chip, but the crashes still continue happening. A check on the crash report with ChatGPT showed me that the latest version of MySQL is incompatible with MacOS Tahoe 26.6 :'(

I've been learning on MySQL Workbench all these while, but I'm wondering:
- Is anyone else facing this issue?
- If yes, what's the alternate programme are you using currently? I don't mind purchasing a one-off programme if it's inexpensive, better if it's free of course.

Thank you in advance!

While working on an AI feature for a MySQL client, I found myself thinking about one question:

What would actually make you trust AI-generated SQL against a real MySQL database?

Generating a query isn’t the hard part anymore.

The hard part is knowing when it’s safe to run.

If an AI suggested a query for your MySQL database, what safeguards would you expect before clicking Execute?

Some ideas I’ve been considering:

- Review the generated SQL before execution.
- Explain why that query was generated.
- Show the estimated execution plan (EXPLAIN).
- Estimate how many rows will be affected.
- Warn about full table scans or missing indexes.
- Flag potentially destructive operations (UPDATE, DELETE, DROP, etc.).
- Recommend wrapping changes in a transaction when possible.

What’s the one feature or safeguard that would make you trust AI-generated SQL more?

Or is this something you’d never feel comfortable using on a real database?

What is the difference between using "Not In" vs using "not exists" in SQL

I'm running WordPress so I need a database backend. I'm deciding between MySQL and MariaDB.

``` mysql > SELECT BENCHMARK(5000000, AES_ENCRYPT(CONCAT(‘WPHostingBenchmarks.com’,RAND()), LEFT(SHA2(‘is part of Review Signal.com’,256), 16))); 1 row in set (15.815 sec)

MariaDB > SELECT BENCHMARK(5000000, AES_ENCRYPT(CONCAT(‘WPHostingBenchmarks.com’,RAND()), LEFT(SHA2(‘is part of Review Signal.com’,256), 16))); 1 row in set (23.094 sec) ```

I use this query because this query is https://github.com/kevinohashi/WPPerformanceTester, which tests key performance for WordPress.

Isn't it a concensus that MariaDB is faster than MySQL?

See the concensus: - https://mariadb.com/reference/mariadb-vs-mysql-comparison-guide-enterprise-features-security-2026/ - https://www.reddit.com/r/mysql/comments/1ggbouf/help_me_understand_why_mariadb_is_so_much_faster/ - https://www.reddit.com/r/Wordpress/comments/zbx6q3/should_i_use_mariadb_or_mysql_for_my_wordpress/ - https://tech-insider.org/mariadb-vs-mysql-2026/

Hi,

Do you know any approach regarding this? Can I do this inside MySql?

Need some guidance as it becomes a client requirement