We have recently upgraded from MySQL 5.5 to 5.7.26.

When running the following query:

INSERT INTO table1_archive 
FROM table1 
WHERE dc between "2019-06-01 00:10:00" and "2019-06-31 23:59:59";

It is now filling up the / directory, and then failing. This was not the case in MySQL 5.5, where the same query has been running for years.

If I run explain on the query there is no mention of using temporary, so I can't see why it is suddenly using up all the / space.

Looking at the docs the only thing I can see is that

When selecting from and inserting into the same table, MySQL creates an internal temporary table to hold the rows from the SELECT

but I am using two different tables, so this shouldn't be the case.

Is there something else in MySQL 5.7 causing INSERT . . SELECT. . queries to use /, and is there something I can do (other than create a huge / directory just for these occasional queries)

the tmpdir is currently set to /tmp which is 9GB (compared to the data directory at 1.5 TB, and mounted on /var/lib/mysql). And the buffer pool is 100GB.

The table is:

CREATE TABLEtable1_archive( MIdint(11) NOT NULL AUTO_INCREMENT, FIdint(11) DEFAULT NULL, JIdint(11) DEFAULT NULL, Headvarchar(80) DEFAULT NULL, MStattinyint(3) unsigned DEFAULT NULL, Latesttinyint(3) unsigned NOT NULL DEFAULT '0', Deletedtinyint(2) unsigned DEFAULT NULL, CByint(11) DEFAULT NULL, Recint(11) unsigned DEFAULT NULL, Rec2varchar(65) DEFAULT NULL, dcdatetime DEFAULT NULL, procdatetime DEFAULT NULL, SReqdatetime DEFAULT NULL, Deldatetime DEFAULT NULL, RAtdatetime DEFAULT NULL, WRAtdatetime DEFAULT NULL, WDelAtdatetime DEFAULT NULL, Deldatetime DEFAULT NULL, Textblob, Numint(10) unsigned DEFAULT NULL, TB1longblob, TB2longblob, TB3longblob, . . . TB128longblob, Errorint(11) DEFAULT NULL, FNamevarchar(32) DEFAULT NULL, BFlongblob, NAtttinyint(3) unsigned DEFAULT NULL, EStattinyint(2) unsigned DEFAULT NULL, Erronvarchar(255) DEFAULT NULL, EErrorvarchar(255) DEFAULT NULL, Holdtinyint(2) unsigned DEFAULT NULL, PRIMARY KEY (MId), KEYFId(FId), KEYMStat(MStat), KEYCBy(CBy), KEYRec(Rec), KEYdc(dc), KEYDeleted(Deleted), KEYError(Error), KEYJId(JId), KEYEStat(EStat), KEYDel(Del), KEYHold(Hold) ) ENGINE=InnoDB DEFAULT CHARSET=latin1

n.b. I left out a load of TBn fields in the middle, as they are all identical.

The explain is:

{ "query_block": { "select_id": 1, "cost_info": { "query_cost": "11005387.01" }, "table": { "insert": true, "table_name": "table1_archive", "access_type": "ALL" }, "insert_from": { "table": { "table_name": "table1", "access_type": "range", "possible_keys": [ "Created" ], "key": "Created", "used_key_parts": [ "Created" ], "key_length": "6", "rows_examined_per_scan": 7860990, "rows_produced_per_join": 7860990, "filtered": "100.00", "index_condition": "(db1.table1.dcbetween '2019-06-01 00:10:00' and '2019-06-31 23:59:59')", "cost_info": { "read_cost": "9433189.01", "eval_cost": "1572198.00", "prefix_cost": "11005387.01", "data_read_per_join": "17G" }, "used_columns": [ "MId", "FId", "JId", "Head", "MStat", "Latest", "Deleted", "CBy", "Rec", "Rec2", "dc", "Proc", "SReq", "Del", "RAt", "WRAt", "WDelAt", "Del", "Text", "Num", "TB1", "TB2", "TB3", . . . "TB128", "Error", "FName", "BF", "NAtt", "EStat", "Erron", "EError", "Hold" ] } } } }

  • Is your table1.dc field indexed (single field or as a prefix)? – Akina Jul 10 '19 at 13:29
  • Sorry, yes it is. The field is dc datetime DEFAULT NULL. . . KEY dc ('dc'), – IGGt Jul 10 '19 at 13:36
  • Then that query should not be using a temp table. Please provide EXPLAIN FORMAT=JSON INSERT ... and SHOW CREATE TABLE table1. – Rick James Jul 11 '19 at 14:28
  • Are there any triggers on the target table? – SQLRaptor Jul 11 '19 at 15:27
  • Nope, no triggers, partitions, foreign keys etc. – IGGt Jul 11 '19 at 15:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.