CODEWITHSUNDEEP
mysql
Vlada
Vlada

Reputation: 104

"MySQL Error Code 1114: The table is full" keeps showing up

I'm running MySQL 8.0.23 on Windows server 2019. Two tables:

CREATE TABLE `tblp` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `datum` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `index_dat` (`datum`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

and

    CREATE TABLE `tblpdet` (
        `id` int unsigned NOT NULL,
        `katbr` varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `redid` int unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`redid`),
  KEY `Index_2` (`id`),
  KEY `idx_katbr` (`katbr`),
  CONSTRAINT `FK_tblpdet_1` FOREIGN KEY (`id`) REFERENCES `tblp` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Now, if I execute:

select katbr, min(date(datum)) 
from tblp p 
join tblpdet d on p.id = d.id
group by katbr;

I get error

Error Code: 1114. The table 'd:\tmp#sql1e5c_18_1eb' is full

If I execute:

select katbr, min(redid)
from tblpdet 
group by katbr;

then it works fine.

Result should return some 120.000 rows.

Here are global settings relevant to this issue:

> innodb_data_file_path=ibdata1:12M:autoextend
> innodb_buffer_pool_size=51539607552

Table tblp has some 5.800.000 rows, and tblpdet has some 43.000.000 rows. Data folder of MySQL is on SSD (mirrored) drive with 800GB of free space. Total RAM is 128GB; Machine has 2 processors with total of 20 cores, running only MySQL (at the moment).

Everything I read is ending up with 'not enough disk space', or wrong configuration of innodg_data_file_path. Anybody help?

Upvotes: 1

Views: 3501

Answers (1)

Lee
Lee

Reputation: 21

First post so be kind.

I have a similar issue (Error Code: 1114. The table '/tmp/#sql2cc_b_3e' is full) with selecting and joining large datasets with sort and group by. The issue is present in 8.0.24 but not 8.0.22 or prior.

I tested this by exporting the database out of 8.0.24 where I received the error and importing into 8.0.20 where the same query runs successfully. I then updated without changing any other settings to 8.0.24 and the same query fails. I have also done a fresh install of 8.0.24 and also receive the error there.

Upvotes: 2

Related Questions