[MDEV-15369] ERROR 1114 Stored Procedure/Create Temporary Table Created: 2018-02-20  Updated: 2018-02-23  Resolved: 2018-02-22

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Data Definition - Temporary, Stored routines
Affects Version/s: 10.2.13
Fix Version/s: 10.2.14

Type: Bug Priority: Major
Reporter: Bryce Lowe Assignee: Unassigned
Resolution: Fixed Votes: 0
Labels: None
Environment:

CentOS 7 (3.10.0-693.17.1.el7.x86_64) on Virtual Box VM with 8 GB of RAM and 16 GB of storage. Note, I am also seeing the issue with a SuperMicro server running CentOS 7 (3.10.0-693.17.1.el7.x86_64) bare metal.

Attachments: Text File config.txt    

 Description   

I am attempting to move to the MariaDB 10.2 line for my production application. Previously my upgrade to 10.2 was blocked by MDEV-14857. Now in 10.2.13 this issue has been resolved, however I am running into a new problem. I have a nightly job that runs in a stored procedure that fails in MariaDB 10.2.13. The error I am getting is below:

ERROR 1114 (HY000): The table '#sql-daf_13' is fulldone

I realize that this error is normally due to a configuration issue, however look at the end of the error string, it says "done" after full.

I have narrowed down the issue using the following reproduction steps:

  1. I have uploaded a sql script to ftp.askmonty.org/private called brycejlowe-permtest.sql
  2. Install MariaDB 10.2.13 and source in the file, it creates a schema called scratch and a table called permtest1 which has about 422,000 rows in it.
  3. Execute the following statements against that table: 

    CREATE TEMPORARY TABLE scratch.temptest1 LIKE scratch.permtest1;
    		 
    INSERT INTO scratch.temptest1 SELECT * FROM scratch.permtest1;
    		 
     
    		 
    CREATE TEMPORARY TABLE scratch.temptest2 SELECT * FROM scratch.temptest1;
    		 
    ALTER TABLE scratch.temptest2 ADD INDEX `part`(`Part_no`);

The result in MariaDB 10.2.8 through 10.2.12 is a temporary table scratch.temptest2 with an index called part. However the result in 10.2.13 is the error:

ERROR 1114 (HY000): The table '#sql-daf_13' is fulldone

 Comments   
Comment by Bryce Lowe [ 2018-02-20 ]

NOTE: I am not seeing the issue in 10.1.31.

Comment by Elena Stepanova [ 2018-02-21 ]

brycejlowe,
Thanks for the report and test case.
Can we use data from brycejlowe-permtest.sql on public resources, or do you prefer it to be kept confidential?

Comment by Bryce Lowe [ 2018-02-22 ]

Feel free to make it a public resource.

Comment by Alice Sherepa [ 2018-02-22 ]

Reproducible on 10.2.13, not on 10.3.4, not on current 10.2
Message is "table is full", there are leftovers after " Stage: 1 of 2 'copy to tmp table' 19.2% of stage done", and the word "done" remains.

 
 
CREATE TABLE `t1` (
 
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 
  `Part_No` varchar(15) NOT NULL,
 
  `IQty` float DEFAULT 0,
 
  `DQty` float DEFAULT 0,
 
  `TQty` float DEFAULT 0,
 
  `OHQty` float DEFAULT 0,
 
  `OOQty` float DEFAULT 0,
 
  `RQty` float DEFAULT 0,
 
  `Level` int(10) unsigned DEFAULT 1,
 
  `Part_Type` char(1) DEFAULT '1',
 
  `Period` int(10) unsigned DEFAULT NULL,
 
  `EcoList` varchar(50) NOT NULL,
 
  `MQty` float DEFAULT NULL,
 
  `BQty` float DEFAULT NULL,
 
  PRIMARY KEY (`id`),
 
  KEY `Part` (`Part_No`),
 
  KEY `pnp` (`Part_No`,`Period`)
 
);
 
 
 
INSERT INTO t1 (id,Part_No,EcoList,MQty,BQty) SELECT seq,seq,seq,seq,seq FROM seq_1_to_1000000;
 
 
 
CREATE TEMPORARY TABLE tt1 LIKE t1;
 
INSERT INTO tt1 SELECT * FROM t1;
 
 
 
CREATE TEMPORARY TABLE tt2 SELECT * FROM tt1;
 
ALTER TABLE tt2 ADD INDEX `part`(`Part_no`);

Comment by Elena Stepanova [ 2018-02-22 ]

Apparently it was fixed in 10.2 tree just a few days ago, by this commit:

commit 852c35f571b1b7454aab5768899e2faee8f941d4
 
Author: Marko Mäkelä <marko.makela@mariadb.com>
 
Date:   Mon Feb 19 14:58:05 2018 +0200
 
 
 
    MDEV-11581 follow-up fix: Correct a condition
 
    
    fsp_fill_free_list(): Correctly determine whether the temporary
 
    tablespace file should be extended in order to respond to a
 
    page allocation request. The inverted condition was noticed
 
    by Thiru when he analyzed MDEV-13013.

Generated at Thu Feb 08 08:20:46 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.