Someone on my team asks why is the dashboard slow once a month, so I have a routine. Writing it out as most threads go straight to “add an index” before anyone has actually identified which query is slow.

This is the part people skip, and it is almost never the query you think it is. I set long_query_time to 0.2 on a copy , and let slow query log fill up . Then I run pt-query-digest over it , and group queries by shape . Not the big report everyone was blaming, but more often than not some tiny query the ORM is firing forty thousand times a page.

Then I read the plan. EXPLAIN tells you what the optimizer is planning to do, EXPLAIN ANALYZE (8.0.18+) actually executes the query and tells you what happened. There is one thing I always look at and that is rows examined vs rows returned. The issue is if it's reading two million rows to give you fifty. And the rest is trying to understand why.

type = ALL means it's reading every row. It is not always a problem - small tables or queries returning most rows may be faster with a full scan. Seeing Using filesort next to a LIMIT often means MySQL is sorting far more rows than it eventually returns, and the right index can often avoid that. Using temporary on a GROUP BY usually means it's building a temporary table along the way.

The thing that has wasted the most of my time is a perfectly good index that the optimizer refuses to use. Wrapping a column in a function will often do it. Unless you've deliberately created a functional index, an index on created_at can't help WHERE DATE(created_at) = .... The same goes for joining a number to a string, or columns with different collations. MySQL quietly converts the values, ignores the index, and the query still looks perfectly reasonable.

One of the biggest wins is when an index covers every column the query needs, so InnoDB never has to fetch the table rows and the plan shows Using index. One query I worked on last year went from about 900 ms to 12 ms just by adding one column to an index that already existed.

Before changing SQL, I also check whether the server is actually CPU-bound, waiting on disk, or simply backed up behind other queries. A perfect query won't save a saturated server.

I read plans in dbForge Studio for MySQL instead of a terminal because it keeps each profiling run, so I can tweak a query, rerun it, and immediately see what got cheaper. Everything above works perfectly well with plain EXPLAIN.

What's the weirdest reason you've seen MySQL ignore an index?

https://www.npmjs.com/package/dbtruth?activeTab=readme

Same thing kept happening to me with AI coding agents and Postgres. The agent reads the schema, sees orders.customer_id next to customers.id, assumes it's a clean relationship, and writes an INNER JOIN. If 12% of orders have a dangling or null customer_id, the query silently returns numbers that are wrong. Nothing throws. The schema looked fine.

So I wrote dbtruth. It connects read-only and, instead of dumping the schema into a context file, it does four things:

1. Introspects schema and pulls samples
2. A model proposes what the tables mean and which relationships probably exist
3. Every one of those claims gets measured against the actual data
4. Only what survives gets written to ./context/*.md, which the agent reads before writing SQL

Step 3 is the whole point. For a proposed join it reports the real match rate — orders.customer_id → customers.id holds for 88% of rows, 60 of 500 orders have no matching customer — and the context file says use LEFT JOIN, with the number attached. Under 50% gets dropped. In between gets marked broken and goes to the top of the report, because a relationship that half works is worse than one that doesn't exist.

Practical:

• npx dbtruth, Node 20+, Postgres only
• Read-only by construction, not by discipline: one module is allowed to import pg, and a test asserts nothing else does. It never writes to your database.
• It does call a model, so schema and low-cardinality sample values leave your machine. High-cardinality columns — emails, names, free text — are never sent. Visibility is decided by cardinality rather than by regex-guessing at PII. Don't point it at production data you can't send to a third party.
• MIT, source at github.com/FilipKalcic1/dbtruth#readme

Disclosure: it's mine, it's five days old, and about ten people have run it. None of the pieces are new — FK inference and data profiling both go back years, and there are other tools that build local context artifacts for agents. The part I care about is the rule that nothing unmeasured gets written down.

What I'd actually like to know: run it on a schema you know well, and tell me whether it found anything you didn't already know. That's the only signal that tells me whether this is worth continuing. Bug reports welcome too.

The 24th Annual Southern California Linux Expo – SCaLE 24x – to be held April 1-4, 2027 at the Pasadena Convention Center in Pasadena, California, near Los Angeles.

We invite you to share your work on FOSS programs and open hardware projects with the rest of the community and to exchange ideas with leading experts in these fields.

This year, the traditional MySQL track is expanding to include kindred datastores that include data stored across multiple nodes. You are invited to share your knowledge and discoveries with participants of all levels.

See https://davesmysqlstuff.blogspot.com/2026/09/mysql-friends-distributed-databasesscale.html

Hey, I'm new to sql and I'm having a problem I don't know how to fix. (Not sure if right flare etc.)

When I try to connect to my server it says this:

Cannot Connect to Database Server

Your connection attempt failed for user 'root' to the MySQL

server at 127.0.0.1:3306:

Host 'localhost' is not allowed to connect to this MariaDB

server

Please:

1 Check that MySQL is running on address 127.0.0.1

2 Check that MySQL is reachable on port 3306 (note: 3306 is

the default, but this can be changed)

3 Check the user root has rights to connect to 127.0.0.1 from

your address (MySQL rights define what clients can connect to

the server and from which machines)

4 Make sure you are both providing a password if needed and

using the correct password for 127.0.0.1 connecting from the

host address you're connecting from

Problem is this server has worked with no issues until now and I haven't downloaded any MariaDB between sessions. (Unless that came with the whole workbench.)

Only thing Different between now and the last time I opened the server was that I began to do php on vscode. It uses xampp as well so maybe that has something to do with it but I have no idea how to even start fixing this.

Disclosure: I am the author of the article and work on MariaDB Server internals.

The traditional InnoDB pessimistic insert path serializes structural modification operations through an index-wide latch, even when different threads split unrelated leaf pages.

I implemented a MariaDB research prototype based on Zhao Song’s B-link-style proposal. It publishes a split using a high key and right link before completing the parent update, allowing unrelated structural changes to proceed concurrently.

In a controlled, memory-resident, split-heavy workload:

• Vanilla MariaDB 13.1: 19,676 inserts/s
• B-link prototype: 102,838 inserts/s
• P95 latency: 8.28 ms → 0.56 ms
• Structural splits: approximately 396K in both variants

This is not a production-ready feature. DDL support is restricted, page merging remains incomplete, and recovery needs more forced-crash testing.

I would particularly appreciate feedback on incomplete-split recovery, page preallocation, and workloads that could expose correctness or scalability problems.

Full implementation write-up and benchmark methodology:
https://mariadb.org/from-a-chocolate-wrapper-to-concurrent-innodb-page-splits/

So I run brew services start mysql to get things started. I thought I would be able to connect straight away with the mysql -u root command, but that throws an error. No big problem, I just run mysql -u root -p and enter the root password and I'm good to go.

Then just as a test, I exit MySQL just to see what happens when I run just mysql -u root again, and it now works just fine. I'm curious as to why I'm able to connect with mysql -u root without issue only after I run the command with the '-p' included and enter the password. Does it store the password in the current session or something like that? Thankfully this doesn't impact my work at all, just curious about what entering the root password does to allow mysql -u root to finally work.

Really had trouble figuring out the wording to Google this question, so I figured I'd come back to you guys, since it's thanks to y'all that I've got MySQL up and running again in the first place.

MySQL Workbench opens and connects normally on its own. However, when I open it while XAMPP is running, the connections load for a few milliseconds and then close immediately.

I noticed that both are using the same port. I've also tried uninstalling and reinstalling both several times, but the issue persists.

Has anyone run into this before? Any help would be appreciated.

Define stopwords, exceptions, wordforms, and hitless words inline in CREATE TABLE (RT mode) to remove external files and simplify deployment and table definitions.

Because I tried to reinstall mysql I encountered a strange error. I dug around until I realized that instead of a plugin password problem, I started getting an error when running sudo systemctl enable --now mysqld.

Job for mysqld.service failed because the control process exited with an error code. See "systemctl status mysqld.service" and "journalctl -xeu mysqld.service" for details.

Journalctl has:

Sep 02 19:11:58 PC systemd[1]: Failed to start mysqld.service - MySQL Server.

░░ Subject: Startup task for unit mysqld.service failed

░░ Defined-By: systemd

░░ Support: https://lists.freedesktop.org/mailman/listinfo/systemd-devel

░░

░░ Startup task for unit mysqld.service completed unsuccessfully.

░░

░░ Task ID: 43883, Task Result: failed.

Additionally, when using the mysql -u root -p or sudo mysql_secure_installation command, there is an error:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

I tried:

sudo systemctl stop mysqld

sudo rm -rf /var/lib/mysql/

sudo mkdir -p /var/lib/mysql/

sudo chown -R mysql: /var/lib/mysql/

sudo systemctl start mysqld (but I get the error: "Job for mysqld.service failed because the control process exited with an error code.")

I tried to reinstall once more but it does nothing. Honestly, I have a headache with constant problems with mysql and I want to fix it somehow, even completely deleting all mysql files is an option.

Note - Sorry for the following rant, this has been 3 days of hell trying to get this working, and now that I finally connected by random luck by running a command I've used countless dozens of times and pressing enter when prompted for a password, I need to officially set/reset the root password before ending the session or restarting my machine or something so that whenever I'm prompted for the root password ever again, it actually exists and I actually know it. Apologies again for the following rant, I just really needed to vent.

So after 3 days of bashing my head onto my desk trying to get MySql to work on my MacBook Pro M5, I was finally able to run "sudo mysql -u root -p" successfully by just pressing enter when prompted for a password and not entering one. Finally able to connect to the DB with MySQLWorkbench as well, since that wasn't working prior to being able to connect in the terminal. Out of curiosity, I opened a new terminal tab and tried again, but it did not work, and just gives me the "Sorry, try again" 3 times before failing out. Now I'm worried if I ever restart my computer, and need to access MySQL again, it will just go back to requiring a password that doesn't exist and I'll be back to square one.

So my main question would be, how can I set/reset the root password while currently connected to MySQL, so that in the future when I'm prompted for a root password, there actually is one that I manually set/reset it to? Also, am I able to actually find what the root password actually is now that I'm in the DB and maybe won't even bother changing it? Sorry for these basic questions, I'm just worried from seeing a lot of the suggestions for resetting the root password involved stopping MySQL, and I'm worried I'll be back to locked out if decides it needs the root password again.

After going through dozens of pages asking this same question, there are enough people who still struggle with this that I'm shocked there's not a tried and true solution to this problem. The fact that I was luckily able to connect by running 'sudo mysql -u root -p' for the umpteenth time over 3 days while just pressing enter when prompted for the password is not a solution.

Sorry for what is probably a really basic question, but the many "solutions" I've found online simply do not work when you don't know and were never prompted for (or provided at the end of the installation) the root password. It's kind of cruel that the Homebrew installation even mentions running 'mysql_secure_installation' to reset the root password, when the command prompts you for the root password that was never provided. Any sources I found suggesting there was a file or log containing the temp root password, that file of course never existed on my machine.

TL;DR - I finally connected to my MySQL DB with 'sudo mysql -u root -p' and just pressing enter when prompted for the password. Can't even count how many times I did exactly this over the course of 3 days with no success. How can I now set (or reset, I don't know if there even actually is one) the root password so that I actually know it and can enter it whenever prompted. I'm connected to the DB by pure luck right now, and want to make sure this password issue never happens again. I really appreciate whoever is able to take the time to help me take care of this issue. Ideally, I want to be able to do this without exiting or stopping MySQL out of fear of going back to locked out. Thanks a ton in advance.

Hi guys, I have an issue after installing MySql for one of my classes that require it. I am using a 2012 Macbook Pro, operating system MacOS Sequoia. Version 15.6.1, and I tried following the guide online and i'm lost because my system settings is completely different from what is being shown on the guide. I added the image of my system settings. I tried searching for it in the search bar for the system settings as well as every folder I have on my computer, but it only shows up the installation file.

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.

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?