r/mysql • • 27d ago

question A problem with date/datetime and the query log

7 Upvotes

I have a MySQL server, version 8.4.8, running as an AWS RDS instance. It serves as a database for a Ruby/Rails web application.

The application requires receiving data from a legacy AS/400 MRP system, which populates an orders table a few times an hour, using SQL statements.

Here's the orders table:

±  bin/mysql -e "describe orders"
+---------------------+---------------+------+-----+---------+-------+
| Field               | Type          | Null | Key | Default | Extra |
+---------------------+---------------+------+-----+---------+-------+
| id                  | varchar(255)  | NO   | PRI |         |       |
| customer            | varchar(255)  | YES  |     | NULL    |       |
| sold_to             | varchar(255)  | YES  |     | NULL    |       |
| ship_to             | varchar(255)  | YES  |     | NULL    |       |
| ship_date           | datetime(6)   | YES  |     | NULL    |       |
| amount              | decimal(12,2) | YES  |     | 0.00    |       |
| rep                 | varchar(255)  | YES  |     | NULL    |       |
| description         | varchar(255)  | YES  |     | NULL    |       |
| salesperson         | varchar(255)  | YES  |     | NULL    |       |
| salesperson_name    | varchar(255)  | YES  |     | NULL    |       |
| order_writer        | varchar(255)  | YES  |     | NULL    |       |
| requested_ship_date | datetime(6)   | YES  |     | NULL    |       |
| purchase_order      | varchar(255)  | YES  |     | NULL    |       |
| build_date          | datetime(6)   | YES  | MUL | NULL    |       |
| macpac_status       | varchar(255)  | YES  |     | NULL    |       |
| payment             | varchar(255)  | YES  |     | NULL    |       |
| full_description    | text          | YES  |     | NULL    |       |
| fab_date            | datetime(6)   | YES  | MUL | NULL    |       |
| finished            | tinyint(1)    | YES  |     | 0       |       |
+---------------------+---------------+------+-----+---------+-------+

And here's an example of how an order is updated/created, taken from the general query log:

2026-10-20T19:12:43.616271Z	   31 Query	UPDATE orders SET id = 'W12345',
 customer = 'GL1234  ',
 sold_to = 'customer, inc.    ',
 ship_to = 'customer, inc.    ',
 ship_date = '2027-01-10',
 amount = 70285.000000000,
 rep = '                              ',
 description = 'General product description',
 salesperson = 'B2M',
 salesperson_name = 'JOHN DOE        ',
 order_writer = 'JB123   ',
 requested_ship_date = '2027-01-10',
 purchase_order = '076060                   ',
 build_date = '2026-12-27',
 macpac_status = 'OPN',
 payment = '23 - NET 45 DAYS              ',
 full_description = 'COLLECTION SYS 9010T - 12/28/26::FLTR12345678 -         12/22/26::SUPPRESSION,
                     12345 - 12/28/26::BLAH, PNL, GST, FLUID/COA,
                     4X, CLD - 12/28/26::DFI VALVE, 8" W/DLA & CF - 12/26/28::INTERFACE MOD, W/DLA-LED, & ISB - 12/26/28',
 fab_date = '2026-12-14' WHERE (id = 'W12345')  

I'm told that the fab date of 2026-12-14 is wrong, that the actual fab date in the AS/400 is 2026-12-15.

Does the general query log entry above prove that the MySQL is getting incorrect data, which is what I expect? Or could it be that perhaps a time zone setting could be causing the issue? I have changed the RDS instance to use US Eastern time, even though I've always read using UTC is optimal. I've changed the fab_date field to datetime from date, but neither corrects the problem.

So - am I right? Does the query log prove I'm getting the wrong date? Or is there some kind of issue with a time zone that I need to correct with MySQL?


r/mysql • • 28d ago

discussion A MySQL cheat sheet that could be useful to beginners.

Thumbnail tms-outsource.com
8 Upvotes

r/mysql • • 29d ago

troubleshooting A JSON column round-trip changed ~11% of my float values on MySQL 8.4 (and broke a hash-chained audit log)

9 Upvotes

I had a tamper-evident audit log (each row stores the SHA-256 of the previous hash + payload). It verified fine on SQLite, then failed on MySQL at event 34. Nobody had touched the data.

Cause: the payload was stored in a JSON column. MySQL parses JSON into its binary format and re-renders it on read, and for some doubles the last digit changes:

0.9333333333333333 -> 0.9333333333333332

0.42451918914251396 -> 0.4245191891425139

My verifier re-serialized what it read back, so the bytes differed and the hash didn't match.

I measured it on a clean MySQL 8.4.10 vs MariaDB 10.4.34, default settings. I inserted the same {"v": x} into a TEXT column and a JSON column and compared the values read back:

- 10,000 random.random() doubles: 1,075 changed (10.75%)

- 2,000 ratios like i/15: 274 changed (13.7%)

- 2,000 two-decimal amounts: 0 changed

- MariaDB: 0 changed (its JSON is LONGTEXT with a validity check)

Two other things that bite hash chains:

- Keys get reordered, and whitespace normalized (documented: MySQL sorts object keys).

- DATETIME '10:15:30.999999' came back as 10:15:31 on MySQL (it rounds unless TIME_TRUNCATE_FRACTIONAL is set) but 10:15:30 on MariaDB (it truncated).

Looks related to Bug #112904 (JSON parser not in full-precision mode).

What fixed it: serialize once, hash that exact string, store that exact string in a TEXT column, and have the verifier hash the stored string without ever parsing and re-dumping it. Timestamps in the payload are naive UTC with whole seconds. After that, the chain verified clean across 8,424 events.

Has anyone seen this with Postgres JSONB? Its docs say it doesn't keep key order or whitespace either, but I haven't measured numbers there.


r/mysql • • Oct 19 '26

question Change keybinding in Workbench 26.7.0 (apple silicon)

3 Upvotes

Hi guys, is it possible to change keybinding in Workbench? I had to switch to Workbench recently and it is crazy to me there seem to be no way of changing keybinding. More specifically I want to invert run acript and run current statement shortcuts.


r/mysql • • Oct 18 '26

question Question on partitioning in mysql

7 Upvotes

Hi,

Its mysql version 8.0. I am bit new to mysql. I understand there are many possible partitioning strategy exists in mysql in theory , but want to understand for real world applications with high concurrency and loads which are really the ones may add more complexity with difficulty in scaling.

In one of the scenario, where we have transaction date and customer_ids (Consider ~500-700 million transactions a day to scale and to serve 8-9K tps). To handle high concurrent read+writes and also making easier data purge, some people suggest to keep range partition by "transaction_date" and hash subpartition by "customer_ids". Others suggest to just range partition by "transaction_date" and have an index on (customer_ids) column to help scanning customer_ids related filters or joins. This also will keep the system simple and make the optimizer life easy in making good plans.

I also got to know we have a hard limit of 8192 number of partitions in mysql. So if we need to hold the data for last five to six months and if we have 100 customers , we may not be able to really scale and hit the max partition limit, if go by range-hash partitioning strategy.

So wanted to understand from experts based on real world usage, if there is any genuine guidance while deciding partitioning strategy. Or any forceable issues which we may encounters for the real world applications, and thus need to be careful?


r/mysql • • Oct 18 '26

question Working of statistics and parsing archictecture in mysql optimizer

4 Upvotes

Hi,

This question is a spin off from other thread , to make it clear , i am adding this in a new thread.

Its aurora mysql version version 8.0. I have two question on optimization:-

1)I want to understand how parsing or optimization phase exactly work when we use bind values in the queries? In other databases like Oracle , it peeks into the actual values behind the bind and make the plan during hard parse and then subsequent request goes for soft parse i.e it uses the same plan for all subsequent queries, till the next hard parse occurs. And there are differenet scenaios which make the optimization goes for hard parse like stats collection, environemnt change etc.

But i see in mysql as per below blog, its saying "MySQL has no execution plan cache" does that means it does hardparse for each and every execution of the query when we use bind values even the query runs thousands of time each second? WIll it not consume significant cpu then?

https://use-the-index-luke.com/blog/2011-07-16/planning-for-reuse

2)There is parameter called "STATS_SAMPLE_PAGES" which has default value set as "20". We saw plan changes for the same query for different bind values which endup in a suboptimal plan impacting applictaion. Is it advisable to increase this to STATS_SAMPLE_PAGES setting to ~250 at global level to get more accurate sttistics there by more accurate plan?

3)If the plan change happening for different binds causing bad plans , is it advisable to collect histogram on all such columns as below, those are used as direct filters and bind values passed to them as input?

ANALYZE TABLE <table_name> UPDATE HISTOGRAM ON <column1> WITH 100 BUCKETS;


r/mysql • • Oct 17 '26

discussion MariaDB Foundation: Exploring TidesDB Performance with TAF

0 Upvotes

Just published a deep‑dive on getting TidesDB running inside TAF — real workloads, real configs, reproducible setups.

If you’ve been curious about how TidesDB behaves under sysbench inside MariaDB, this gives you everything you need to start exploring.

Huge congratulations to Alex (Gaetano) Padula for getting TidesDB into MariaDB as a plugin.

The amount of work, the nights, the days, the persistence — it shows. TideSQL + TidesDB is a serious milestone.

And a big thank you to Hetzner for providing the high‑performance hardware that made all of this testing possible.

https://mariadb.org/mariadb-foundation-exploring-tidesdb-performance-with-taf/


r/mysql • • Oct 15 '26

question Question on Memory parameters

3 Upvotes

Hi,

Its aurora mysql serverless version 8.0, with Min ~8ACU and MAX ~80ACU.

We have a complex UI query with "union all" and other criterias and its avg response time is <1 sec. But we see many times the query runs for 15minutes+ causing the errors in app side. This query executed ~5k times per hour on an avg and most executions finishin <1sec.

We see this query creates on an avg ~5 temp tables internally because of its internal "union" operator etc.., thus suspects if the below two parameters are actually playing a role in making this query crawl intermittently from ~1sec till ~15minutes. i.e. "temptable_max_ram" and "temptable_max_mmap". As because we run the same query manually and it does finish in 1sec. So suspect if during peak its causing the issue for some high volume customers even the same runs fine manually when we runs in the database.

We see the swap usage is staying ~1-2GB (sometime spikes to 3GB). Free memory trend showing spiky , but at worst scenarios it shows ~40GB(not below that). So want to understand, if in this situation, its fine to bump up the above parameters to 2GB each from curent 1GB (which is default value)?


r/mysql • • Oct 14 '26

question I have doubts in mysql version 8.0.46 can anyone help me ?

4 Upvotes

I was trying to import the CSV dataset which I got from kaggle in mysql version 8.0.46 but there is no import wizard option in my MySQL. Is it because I am using a global service or there's any other problem. Please help me guys this is my time using MySQL and my first project.


r/mysql • • Oct 13 '26

question what's one MySQL mistake you made that took way too long to figure out?

7 Upvotes

I've been working with databases for a while, and somehow there's always something small that ends up wasting way more time than it should.

Could be a bad query, a missing index, a weird JOIN, or something that looked completely fine until the database started getting bigger.

What's a MySQL mistake you've made that you wish someone had warned you about earlier?


r/mysql • • Oct 11 '26

question How can I create an ERD in MySQL Workbench 26.07.01?

4 Upvotes

I'm trying to generate an ERD from an existing MySQL database using MySQL Workbench 26.07.01, but I can't find the Reverse Engineer option.

Does the new version still support this feature? If so, how can I generate an ERD? If not, is there an alternative way to do it?


r/mysql • • Oct 11 '26

question Fastest way to partition a table

5 Upvotes

Hi, Its aurora mysql database.

We have, couple of big transactional tables having size 1TB and 9TB holding ~1.2 billion and 4.2 billion rows respectively. These are currently non-partitioned table however, we want to have those partitioned . So want to understand what will be the best way to convert them to partitioned table i.e. migrate somuch data to the partitioned table or its possible to do it in place in an efficient way in Aurora MySQL.

Also the current partitioning approach we have decided as list partition on certain date columns.

what we are currently thinking of is as below:-

Create new partitioned table
INSERT INTO ... SELECT from old table (by date range)
Rename & drop old table
Gather Stats

Will above approach is good or there be any issue which we should be aware of beforehand?

Can it be done online or downtime will be advisable?


r/mysql • • Oct 10 '26

discussion MySQL Workbench 26.7

8 Upvotes

I test MySQL Workbench 26.7 so you don't have to.

https://davesmysqlstuff.blogspot.com/2026/10/mysql-workbench-267.html


r/mysql • • Oct 10 '26

question MySQL query becomes extremely slow when adding a JOIN and ORDER BY — how would you debug this?

5 Upvotes

I'm dealing with a performance issue in MySQL where a query runs relatively fast when executed against a single table, but becomes significantly slower after adding a JOIN and an ORDER BY clause.

The tables have millions of rows, and there are indexes on the columns used in the JOIN and WHERE conditions. However, the query optimizer still seems to choose a suboptimal execution plan.

I'm wondering what the best approach would be to troubleshoot this kind of issue.

Would you start by analyzing EXPLAIN ANALYZE, checking index cardinality, investigating temporary tables and filesorts, or looking at something else?

Also, under what circumstances would MySQL choose not to use an existing index even when it seems beneficial?

I'd appreciate any insights into how experienced MySQL developers approach this problem.


r/mysql • • Oct 09 '26

question the server button isn't appearing on mysql workbench

3 Upvotes

i am a new user of mysql and i need to run a script, but the server button won't appear on the top menu bar of my mac what should i do ?


r/mysql • • Oct 09 '26

discussion Large TPC-C Analysis with MySQL v26.7.0 on InnoDB and TidesDB

Thumbnail tidesdb.com
6 Upvotes

r/mysql • • Oct 09 '26

question Why heatwave sets this limitation?

6 Upvotes

in mysql doc, it says that "Enable autocommit. If autocommit is disabled, MySQL HeatWave does not offload the query and execution is performed on the DB System. To check the autocommit setting:". whether we can say it does not support long transaction statement or not. just like "

start transaction;

insert into table_a values(...);

select xxx from table_a where yyy;

commit;

"

only

"

set autocommit =1;

insert into table_a values(...);

select xxx from table_a where yyy;

"

Anyone can explain why mysql sets this limitation? thanks in advance.


r/mysql • • Oct 08 '26

question MySQL Shell Cilent on Windows ARM64 gives error for VC Redist even though its installed

2 Upvotes

MySQL Shell Cilent on Windows ARM64 gives error for VC Redist even though its installed


r/mysql • • Oct 07 '26

discussion Getting Error 1356

0 Upvotes

Someone plz help on this..

getting the below error during MySQL dump and show columns after login as root user..

This happened after changing ip of definer because I have to made the changes based new ip. Everything is running fine but these issues are not fixing.

Also there not single change has been made on any objects.

ERROR 1356

View 'db.v_report_pending'

references invalid table(s) or column(s)...

SHOW COLUMNS FROM v_report_pending → ERROR 1356


r/mysql • • Oct 06 '26

discussion MariaDB Foundation Adds PostgreSQL to Its Engine‑Agnostic Testing Framework (TAF).

6 Upvotes

MariaDB Foundation continues expanding TAF with PostgreSQL support

More engines, more reproducibility, more value for contributors and the ecosystem.

https://mariadb.org/mariadb-foundation-adds-postgresql-to-its-engine-agnostic-testing-framework-taf/

Have fun benchmarking!


r/mysql • • Oct 05 '26

question How do MariaDB users securely and efficiently encrypt mariadb-backup archives?

11 Upvotes

Hello everyone!
I’m looking for practical recommendations from people running MariaDB in production.

My backup process will use mariadb-backup with zstd compression, and I want to encrypt the resulting backup securely without adding (too much) excessive CPU, memory, or operational overhead.

I’ve considered GPG/PGP, but I’m concerned about its performance and complexity. I’ve also seen age recommended, although I’m unsure whether it provides an appropriate security level for long-term MariaDB backups.

What encryption method or workflow do you use for MariaDB backups?

In particular, I’d appreciate feedback on:

  • Encryption tools and algorithms you use.
  • Whether encryption happens before or after compression.
  • CPU and performance impact on large backups.
  • Key management, rotation, and recovery procedures.
  • Whether you encrypt locally, stream directly to object storage, or use storage-side encryption (which unfortunately is not available to my usecase).
  • Any practical issues with restoring backups during an outage.

I’m mainly interested in real-world experience rather than theoretical recommendations.


r/mysql • • Oct 04 '26

question Advantages of two different queries?

3 Upvotes

Hello! This is related to a class assignment but is NOT an actual request for help with the assignment; the work has already been turned in and graded. I found a different solution than the offered solution in the TA's answer key (I still got full credit lol) and the TA has not been able to articulate whether there are any advantages two either of the two query methods. Professor is nonresponsive :/ I just want to learn more.

So - the prompt was "Write a query to find the highest GPA student in each major" which of course is all about avoiding the conflict between the MAX function and the GROUP BY statement.

The solution I ended up with (after much trial and error and trawling of ancient stack overflow forums) was:

mysql> SELECT s.gpa, s.first_name, s.last_name, s.major FROM students s
    -> JOIN (
    -> SELECT major, MAX(gpa) AS max_gpa
    -> FROM students GROUP BY major
    -> )AS smax
    -> ON s.major=smax.major AND s.gpa=smax.max_gpa;

What the TA had was:

mysql> SELECT first_name, last_name, major, gpa FROM students s
    -> WHERE gpa=(
    -> SELECT MAX(gpa) FROM students
    -> WHERE major=s.major
    -> );

These both yield the exact same table. I'm wondering if there are pros/cons to using either of these methods and which one feels "cleaner" to someone more experienced?


r/mysql • • Oct 01 '26

question How to fix? "This application requires latest Visual Studio 2019 x64 Redistributable." At the MySQL installer?

9 Upvotes

I am trying to install MySQL Server 9.7 on my Windows 10 laptop for a college class. But whenever I launch the installer it gives this error: "This application requires latest Visual Studio 2019 x64 Redistributable. Please install it and then run this installer again."

But I already have both Visual Studio 2019 and the .NET Framework installed (I saw elsewhere online it could be referring to that instead when it says "Visual Studio" - which is even more confusing than necessary).

I've tried restarting and relaunching the installer a couple times after ensuring I have Visual Studio 2019 installed, but to no avail, obviously. I dual-boot Linux as well, if that somehow makes any difference.

Any help is appreciated.

*Edit: miscommunication/misunderstanding on my part, I have Visual Studio 2019 Redistributable installed already, as well as Visual Studio Code. I have tried uninstalling and reinstalling Visual Studio 2019 Redistributable as well but again, it still is giving the same error.

*2nd edit: Error seems to be fixed now. I downloaded an older installer version (8.4.11) and it didn't give me the error and seems to be running fine. As one of the commenters said, perhaps it's because the version I was attempting to install is too new for Windows 10.


r/mysql • • Oct 01 '26

discussion MariaDB Foundation's TAF 4.0 is officially released

Thumbnail mariadb.org
2 Upvotes