Where is bug here?
maybe increasing memory limit for temporary files can help?

Hi Oleksander, why a 6 records query on IS produce 10 disk temp table the memory use should be less than a 1Kb

The issue is that most monitoring tools will not really look at what mariadb does behind the hood and clients may simply switch to MysQL 8.0 using innodb table dictionary without even understand that the monyog is just killing their disk !

I not sure about what is thae case here but why do i receive such jira ticket

"Anyways, additional question would your pro (or osc relase) version allow a real time/live migration from MariaDB 10.2 to MySQL 7/8?"

I don't like to hear this , and i'd like to help founding why this happen

My first guess would be that the I_S code does not realize you're selecting data only for one table from I_S.KEY_COLUMN_USAGE.
Try to use inner join instead of left join. Or even propagate constants manually, like in:

SELECT ...  FROM information_schema.COLUMNS b JOIN information_schema.KEY_COLUMN_USAGE c WHERE c.table_schema='wpmixrgen' AND c.table_name='wp_comments' AND b.table_schema='wpmixrgen' AND  b.table_name='wp_comments';

Just to see if that helps, I'm not saying you should do it in production.

Hi Sergei, good to hear from you !

You guys make my day with insert returning

The issue can be narrow to data type of COLUMNS metadata:

While data type choice can be questioned for those columns : can ENUM definition of longtext may exeed varchar max ? , i can't imagine a default or generated exxpression so long

Still we get on simple query:

show status like '%tmp%';

+-------------------------+-------+

| Variable_name           | Value |

+-------------------------+-------+

| Created_tmp_disk_tables | 71    |

MariaDB [(none)]> SELECT  CRC32(b.column_type) FROM information_schema.COLUMNS b  where b.table_schema='wpmixrgen' AND  b.table_name='wp_comments';

+----------------------+

| CRC32(b.column_type) |

+----------------------+

|           2848410689 |

MariaDB [(none)]> show status like '%tmp%';

+-------------------------+-------+

| Variable_name           | Value |

+-------------------------+-------+

| Created_tmp_disk_tables | 72    |

here NOT using

• order by,
• no join
• removed CRC32 and still get tmp table

Where does disk temp table comes in ?

Tx svar

Thanks stephane

By creating same table in aria instead of memory the disk temporay table is not created

on-disk temporary table is created to store the values of `column_type` column. because MEMORY tables cannot have BLOB columns, as you know.

This is not a bug, everything works as intended.

We want to optimize that in the future and not create temp tables in this case. But still, it's not a bug.

Thanks i did not know every request to information_schema produce extra memory table as middle man resultset i was just thinking the data can flow from memory to thread result buffer directly, if not the case of course that's not a bug

Also Monty told me once, aria was now used for temporary tables to avoid touching the disks, was just curious to notice it's not case for IS as well as this make monitoring from IS almost a no go !

Yes, every request to information_schema produces a temporary table. If all requested columns are supported by the MEMORY engine then the table is created using MEMORY engine. But if there are blob columns, the engine will be Aria.

Aria temporary tables don't necessarily go to disk, as far as I understand. They can stay in the page cache and then these pages can be discarded when a table is dropped without ever being written to disk. But if the page cache will need to free some pages, it might write temp table pages to disk.