[MDEV-26525] Replication stops because too big innodb-temp-data-file Created: 2021-09-02  Updated: 2022-10-28  Resolved: 2022-10-28

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Temporary, Replication, Storage Engine - InnoDB
Affects Version/s: 10.6.4, 10.6.5, 10.6.7, 10.6.8, 10.6.9
Fix Version/s: 10.6.10, 10.7.6, 10.8.5

Type: Bug Priority: Critical
Reporter: Alex Assignee: Thirunarayanan Balathandayuthapani
Resolution: Duplicate Votes: 1
Labels: None

Issue Links:
Duplicate
is duplicated by MDEV-28240 InnoDB Temporary Tablespace (ibtmp1) ... Closed
Problem/Incident
is caused by MDEV-28183 Innodb-temp-data-file does not stop g... Closed
Relates
relates to MDEV-13013 InnoDB unnecessarily extends data files Closed

 Description   

Since upgrading from 10.4 to 10.6 we are experiencing disk-space-problems on our replication-slave, because the innodb-temp-data-file "ibtmp1" was growing and growing up to the size of the original database.

MySQL allows limiting the max size of table-space with file_name:file_size[:autoextend[:max:max_file_size]]
(https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_temp_data_file_path) which works also for MariaDB, although not specified (https://mariadb.com/kb/en/innodb-system-variables/#innodb_temp_data_file_path).

However, the file grows up to the max-size although the temporary tables are not as big and then the replication stops:
Last error: Error 'Can't create table ... (errno: 135 "No more room in record file")' on query. Default database: '...'. Query: 'CREATE TEMPORARY TABLE `...` (

Note: Replication is statemend based (binlog_format = STATEMENT)

Workaround is to restart MariaDB once the temporary file reached the max size.



 Comments   
Comment by Alex [ 2021-09-17 ]

Showing all existing temporary tables is not possible (see https://mariadb.com/kb/en/information-schema-temp_tables_info-table/), MDEV-15623 would be helpful for debugging this.

Comment by Alex [ 2021-11-26 ]

Also affects 10.6.5

Comment by Alex [ 2022-03-28 ]

Also affects 10.6.7

Comment by Alex [ 2022-10-28 ]

Has been fixed with 10.6.10 in MDEV-28240.

Ticket can be closed.

Generated at Thu Feb 08 09:45:57 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.