[ERROR] mariadbd: The table 'INNODB_BUFFER_PAGE' is full

问题描述：将information_schema导出sql文件到新库中恢复，sql中的表都是临时表，存储引擎都是memory，在导入的过程中实际大量会占用临时表。

报错信息：ERROR 1114 (HY000) at line 2397 in file: 'MySQLDB-2022-10-06_22-24-01-information_schema.sql': The table 'INNODB_BUFFER_PAGE' is full

环境：mariadb10.6.4

 

 

 

 

1.排查磁盘空间是否占满

2.查询数据文件增长是否受限制

MariaDB [test01]> show variables like '%innodb_data_file_path%';
+-----------------------+------------------------+
| Variable_name | Value |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+-----------------------+------------------------+

这里如果设置文件max最大值，在内部进行大量DML，有可能也会导致 The table 'INNODB_BUFFER_PAGE' is full 问题

3.导入的都是临时表，查询临时表大小

MariaDB [(none)]> show variables like '%tmp%';
+----------------------------+----------------------+
| Variable_name              | Value                |
+----------------------------+----------------------+
| default_tmp_storage_engine |                      |
| encrypt_tmp_disk_tables    | OFF                  |
| encrypt_tmp_files          | OFF                  |
| innodb_tmpdir              |                      |
| max_tmp_tables             | 32                   |
| slave_load_tmpdir          | /tmp                 |
| tmp_disk_table_size        | 18446744073709551615 |
| tmp_memory_table_size      | 16777216             |
| tmp_table_size             | 16777216             |
| tmpdir                     | /tmp                 |
+----------------------------+----------------------+
10 rows in set (0.001 sec)

4.修改临时表大小，重启数据库，要导入的information_schema文件，所以临时表暂时调大

[mysqld]
tmp_table_size = 2048M
max_heap_table_size = 2048M

 

5.查看information_schema建表语句，使用临时表，存储引擎放在memory中。这种内存临时表在show tables时查询不到，但是可以被直接select看到，退出当前会话就会消失

DROP TABLE IF EXISTS `INNODB_BUFFER_PAGE`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TEMPORARY TABLE `INNODB_BUFFER_PAGE` (
  `POOL_ID` int(11) unsigned NOT NULL DEFAULT 0,
  `BLOCK_ID` bigint(21) unsigned NOT NULL DEFAULT 0,
  `SPACE` int(11) unsigned NOT NULL DEFAULT 0,
  `PAGE_NUMBER` int(11) unsigned NOT NULL DEFAULT 0,
  `PAGE_TYPE` varchar(64) DEFAULT NULL,
  `FLUSH_TYPE` int(11) unsigned NOT NULL DEFAULT 0,
  `FIX_COUNT` int(11) unsigned NOT NULL DEFAULT 0,
  `IS_HASHED` int(1) NOT NULL DEFAULT 0,
  `NEWEST_MODIFICATION` bigint(21) unsigned NOT NULL DEFAULT 0,
  `OLDEST_MODIFICATION` bigint(21) unsigned NOT NULL DEFAULT 0,
  `ACCESS_TIME` bigint(21) unsigned NOT NULL DEFAULT 0,
  `TABLE_NAME` varchar(1024) DEFAULT NULL,
  `INDEX_NAME` varchar(64) DEFAULT NULL,
  `NUMBER_RECORDS` bigint(21) unsigned NOT NULL DEFAULT 0,
  `DATA_SIZE` bigint(21) unsigned NOT NULL DEFAULT 0,
  `COMPRESSED_SIZE` bigint(21) unsigned NOT NULL DEFAULT 0,
  `PAGE_STATE` enum('NOT_USED','MEMORY','REMOVE_HASH','FILE_PAGE') NOT NULL,
  `IO_FIX` enum('IO_NONE','IO_READ','IO_WRITE','IO_PIN') NOT NULL,
  `IS_OLD` int(1) NOT NULL DEFAULT 0,
  `FREE_PAGE_CLOCK` bigint(21) unsigned NOT NULL DEFAULT 0
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb3;

内存临时表和磁盘是临时表

-- 内存中的临时表：
create temporary table mytrans 
(
    itemnumber int,
    groupnumber int,
    branchnumber int
)engine = memory
;

-- 创建一个同样结构的临时表 就是普通的临时表 MySQL 会默认存储引擎是 innoDB
create temporary table mytran
(
    itemnumber int,
    groupnumber int,
    branchnumber int
);

 

结论

1.information_schema可以作为sql文件导入到新的数据库中，sql文件中的表都是以内存临时表的形式存在

2.内存临时表和磁盘临时表 show tables都查询不到，但是可以被select，退出当前会话，临时表就会释放

3.可以修改tmp_table_size、max_heap_table_size,innodb_data_file_path是否受限制来解决The table 'INNODB_BUFFER_PAGE' is full的问题