MySQL Bugs: #109122: size of tmp_table_size crashed MySql Server using temp table with spatial index

Bug #109122	size of tmp_table_size crashed MySql Server using temp table with spatial index
Submitted:	17 Nov 2022 13:21	Modified:	18 Nov 2022 17:40
Reporter:	Fabio Schett	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.31	OS:	Ubuntu (Ubuntu-2204-jammy-amd64-base 5.15.0-52-generic)
Assigned to:		CPU Architecture:	x86

Description:
I call a STORED PROCEDURE which is creating a temporary table with spatial index on datatype POINT. It consumes a lot of data within a timeframe of 15 Minutes and is doing some calculation for stats.

With default setting of 16MB tmp_table_size the procedure crashes MySQL Server completely because of corrupted index. with increasing this size the procedure can be run successfully.

How to repeat:
I will provide an example how to reproduce that scenario. Unfortunately the SQL dump is about 500MB big.. I have to find a smaller repro maybe.

It's a stored procedure which is creating a temporary table with spatial index on the datatype POINT. It stores about 30k elements in the temp table.

The bug forced me to do a `--innodb-force-recovery=1`, otherwise it was not possible to start the server again.

```
[ERROR] [MY-011937] [InnoDB] [FATAL] Apparent corruption of an index page [page id: space=4243767289, page number=1] to be written to data file. We intentionally crash the server to prevent corrupt data from ending up in data files.
[ERROR] [MY-013183] [InnoDB] Assertion failure: buf0dblwr.cc:1537:ib::fatal triggered thread 139643310028544
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
2022-11-15T11:00:28Z UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
```

Suggested fix:
MySQL Server should not crash in that specific scenario, it should rollback transaction and log an error.

Hi Mr. Schett,

Thank you for your bug report.

However, it is not a bug .....

MySQL server has to crash at that point, because it tries its best not to corrupt any of its data ......

If it returned an error and continued on, any further operation would have corrupted your data, beyond repair. But, with a crash, the repair is feasible on startup.

Not a bug.

But why does it have to crash? why not returning an error and exit procedure/query? this is repeatable and not a random error caused by a hardware malfunction.

when removing the spatial index on that property it's running successfully with 16 MB tmp_table_size. so only spatial index is causing that "issue" :)

so in my opinion the reason the corruption would have occurred is a bug rather than a hardware fault. it should never reach that state with that setup. I will share a repro in a few hours.

Hi,

Please do not share reproducible test case with us.

Problem is that this error can be caused by a large  number of factors. You have hit only one of those.

If the server would not crash, your table would be corrupted beyond repair. Only due to the misconfiguration, on your side.

You have misconfigured your MySQL server and it is NOT a bug. Proper configuring is not within the scope of bug reporting. It is within a scope of support.

For details on getting support for MySQL products see http://www.mysql.com/support/
You can also check our forums (free) at http://forums.mysql.com/

Hi Mr. Schett,

This is also a question on the configuration.

Next, your conclusions about the temporary tables is wrong. That variable does not control the maximum size of the temporary table on disk. Read our Reference Manual.