MySQL partitioning and temporary tables-CodePudding

A large table (~10.5M rows) has been causing issues lately. I previously modified my application to use temporary tables for faster selects, but was still having issues due to UPDATE statements. Today I implemented partitions so that the writes happen more quickly, but now my temporary tables error. Its purpose is to group events, placing the first event ID of a set in the EVENT_ID column. Example: writing 4 events beginning at 1000 would result in events 1000, 1001, 1002, 1003, all with an EVENT_ID of 1000. I have tried to do away with the UPDATE statements, but that would require too much refactoring, so it is not an option. Here is the table definition:

CREATE TABLE `all_events` (
  `ID` bigint NOT NULL AUTO_INCREMENT,
  `EVENT_ID` bigint unsigned DEFAULT NULL,
  `LAST_UPDATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `EMPLOYEE_ID` int unsigned NOT NULL,
  `QUANTITY` float unsigned NOT NULL,
  `OPERATORS` float unsigned NOT NULL DEFAULT '0',
  `SECSEARNED` decimal(10,2) unsigned NOT NULL DEFAULT '0.00' COMMENT 'for all parts in QUANTITY',
  `SECSBURNED` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
  `YR` smallint unsigned NOT NULL DEFAULT (year(curdate())),
  PRIMARY KEY (`ID`,`YR`),
  KEY `LAST_UPDATE` (`LAST_UPDATE`),
  KEY `EMPLOYEE_ID` (`EMPLOYEE_ID`),
  KEY `EVENT_ID` (`EVENT_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=17464583 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
/*!50100 PARTITION BY RANGE (`YR`)
(PARTITION p2015 VALUES LESS THAN (2016) ENGINE = InnoDB,
 PARTITION p2016 VALUES LESS THAN (2017) ENGINE = InnoDB,
 PARTITION p2017 VALUES LESS THAN (2018) ENGINE = InnoDB,
 PARTITION p2018 VALUES LESS THAN (2019) ENGINE = InnoDB,
 PARTITION p2019 VALUES LESS THAN (2020) ENGINE = InnoDB,
 PARTITION p2020 VALUES LESS THAN (2021) ENGINE = InnoDB,
 PARTITION p2021 VALUES LESS THAN (2022) ENGINE = InnoDB,
 PARTITION p2022 VALUES LESS THAN (2023) ENGINE = InnoDB,
 PARTITION p2023 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

Now in my application when running a report the statement:

CREATE TEMPORARY TABLE IF NOT EXISTS ape ENGINE=MEMORY AS
SELECT * FROM all_events
WHERE LAST_UPDATE BETWEEN '2022-05-01 00:00:00' AND CURRENT_TIMESTAMP()

Produces the error: 'Specified storage engine' is not supported for default value expressions.

Is there a way to still use temporary tables with ENGINE=MEMORY, or is there another high performance engine I can use? The statement worked until the partitioning was implemented. InnoDB is the only engine my tables can be in due to the MySQL implementation, and it has been InnoDB since before partitioning.

Edit: When removing ENGINE=MEMORY it does work, but running SHOW CREATE TABLE tells me that it's using InnoDB. I would prefer the performance increase of MEMORY vs InnoDB.

CodePudding user response：

I would suppose to read the following link from dev.MySQL.com

You cannot use CREATE TEMPORARY TABLE ... LIKE to create an empty table based on the definition of a table that resides in the mysql tablespace, InnoDB system tablespace (innodb_system), or a general tablespace. The tablespace definition for such a table includes a TABLESPACE attribute that defines the tablespace where the table resides, and the aforementioned tablespaces do not support temporary tables. To create a temporary table based on the definition of such a table, use this syntax instead:

CREATE TEMPORARY TABLE new_tbl SELECT * FROM orig_tbl LIMIT 0;

So it seems the correct syntax for your case will be:

CREATE TEMPORARY TABLE ape
SELECT * FROM all_events
WHERE...

CodePudding user response：

In the current issue the problematic column is YR smallint unsigned NOT NULL DEFAULT (year(curdate())). This DEFAULT value is not legal for a column which is used in partitioning expression. The error will be "Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed ...".

And only when you fix this by removing the partitioning then you'll receive an error "'Specified storage engine' is not supported for default value expressions".

CREATE TABLE .. SELECT inherits main columns properties from source tables.

In the current issue the problematic column is YR smallint unsigned NOT NULL DEFAULT (year(curdate())) again. The column in temptable must inherit main properties, including DEFAULT expression - but this expression is not allowed for MEMORY engine.

CodePudding user response：

As the error suggests, the expression default does not work with the MEMORY storage engine.

One solution would be to remove that default from your all_events.yr column.

The other solution is to create an empty temporary table initially as an InnoDB table, then use ALTER TABLE to remove the expression default and convert to MEMORY engine before filling it with data.

Example:

mysql> create temporary table t as select * from all_events where false;

mysql> alter table t alter column yr drop default, engine=memory;

mysql> insert into t select * from all_events;