[MDEV-12914] Engine for temporary tables which are implicitly created as RocksDB is substituted silently Created: 2017-05-24  Updated: 2022-04-18  Resolved: 2021-07-11

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Temporary, Storage Engine - RocksDB
Affects Version/s: 10.2
Fix Version/s: 10.7.1

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Anel Husakovic
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Problem/Incident
causes MDEV-26242 Assertion `i >= 0' failed on setting ... Closed

 Description   

From RocksDB tests I can see that temporary tables are currently not supported. It produces a meaningful error message when the engine is provided explicitly:

MariaDB [test]> create temporary table t1 (i int) engine=RocksDB;
 
ERROR 1478 (HY000): Table storage engine 'ROCKSDB' does not support the create option 'TEMPORARY'

But when the engine is implied, either via default_storage_engine, or via CREATE .. LIKE, it is silently substituted by MyISAM – not only does the statement succeed, but it doesn't even produce a warning:

MariaDB [test]> create table t1 (i int) engine=RocksDB;
 
Query OK, 0 rows affected (0.11 sec)
 
 
 
MariaDB [test]> create temporary table t2 like t1;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> show create table t2;
 
+-------+-------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                    |
 
+-------+-------------------------------------------------------------------------------------------------+
 
| t2    | CREATE TEMPORARY TABLE `t2` (
 
  `i` int(11) DEFAULT NULL
 
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
 
+-------+-------------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)

(on some reason, it's MyISAM, despite default_storage_engine and default_tmp_storage_engine being InnoDB).

or,

MariaDB [test]> set default_storage_engine=RocksDB, default_tmp_storage_engine=RocksDB;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> create temporary table t2 (i int);
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> show create table t2;
 
+-------+-------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                    |
 
+-------+-------------------------------------------------------------------------------------------------+
 
| t2    | CREATE TEMPORARY TABLE `t2` (
 
  `i` int(11) DEFAULT NULL
 
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
 
+-------+-------------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)

I saw the scenario with LIKE in RocksDB tests too, but I'm not sure whether it's actually intended or was just recorded because "temporary tables don't work anyway". It doesn't look right.

 Comments   
Comment by Anel Husakovic [ 2018-03-29 ]

Hi psergey I would like to work on this issue. Is it ok with you if I try to investigate it ?

Comment by Sergei Petrunia [ 2018-04-09 ]

Hi anel, yes. Feel free to take over .

Comment by Sergei Petrunia [ 2018-06-26 ]

Ok to push after review input has been addressed

Comment by Sergey Vojtovich [ 2019-05-08 ]

serg, could you have a look at this PR? It doesn't go inline with normal storage engine substitution. Although documentation doesn't clarify how it should work for storage engines that don't support temporary tables.

Comment by Sergei Golubchik [ 2019-05-09 ]

The current implementation was done by Tomas Ulin back in 2006 (BitKeeper days), commit a5fa2d3010e.

And the behavior was explicitly documented in the patch:

# if default storage engine=ndb, temporary tables
 
# without explicit engine= should be created as myisam

But back then there was no default_tmp_storage_engine, so default_storage_engine applied to both and such a substituion was the only way to have usable temporary tables and default_storage_engine=ndb. Nowadays I'd suggest to throw an error unconditionally, no substitution. That's what happens in all other cases if you use a feature the engine does not support:

mysqltest: At line 1: query 'create table t1 (a blob) engine=memory' failed: 1163: Storage engine MEMORY doesn't support BLOB/TEXT columns

the engine isn't substituted automatically on errors, substitution only happens when the engine does not exist at all.

And also I'd suggest to issue an error when default_tmp_storage_engine is assigned to the unsupported engine. HTON_TEMPORARY_NOT_SUPPORTED is a handlerton flag, so it can be checked without an actual table, a plugin is enough for that. That is, it can be easily checked in ON_CHECK method.

Comment by Anel Husakovic [ 2020-06-10 ]

Hi serg,

PR 774 is updated throwing an error unconditionally and adding callback for ON_CHECK method for default_tmp_storage_engine.
Two things didn't know:
1. Which error to raise for HTON_TEMPORARY_NOT_SUPPORTED ?
2. I broke mtr, couldn't test the patch (test in rocksdb.create_table is done without modification of sys_vars.cc before). It is failing in bootstrap

$ ./mysql-test/mtr view_grant
 
Logging: /home/anel/mariadb/10.4/mysql-test/mysql-test-run.pl  view_grant
 
vardir: /home/anel/mariadb/builds/10.4/mysql-test/var
 
Checking leftover processes...
 
Removing old var directory...
 
Creating var directory '/home/anel/mariadb/builds/10.4/mysql-test/var'...
 
Checking supported features...
 
MariaDB Version 10.4.14-MariaDB-debug
 
 - SSL connections supported
 
 - binaries are debug compiled
 
 - binaries built with wsrep patch
 
Collecting tests...
 
Installing system database...
 
mysql-test-run: *** ERROR: Error executing mysqld --bootstrap
 
Could not install system database from /home/anel/mariadb/builds/10.4/mysql-test/var/log/bootstrap.sql
 
The /home/anel/mariadb/builds/10.4/mysql-test/var/log/bootstrap.log file contains:
 
/home/anel/mariadb/builds/10.4/sql/mysqld --no-defaults --disable-getopt-prefix-matching --bootstrap --basedir=/home/anel/mariadb/10.4 --datadir=/home/anel/mariadb/builds/10.4/mysql-test/var/install.db --plugin-dir=/home/anel/mariadb/builds/10.4/mysql-test/var/plugins --default-storage-engine=myisam --loose-skip-plugin-feedback --loose-skip-plugin-innodb --loose-skip-plugin-innodb-buffer-page --loose-skip-plugin-innodb-buffer-page-lru --loose-skip-plugin-innodb-buffer-pool-stats --loose-skip-plugin-innodb-cmp --loose-skip-plugin-innodb-cmp-per-index --loose-skip-plugin-innodb-cmp-per-index-reset --loose-skip-plugin-innodb-cmp-reset --loose-skip-plugin-innodb-cmpmem --loose-skip-plugin-innodb-cmpmem-reset --loose-skip-plugin-innodb-ft-being-deleted --loose-skip-plugin-innodb-ft-config --loose-skip-plugin-innodb-ft-default-stopword --loose-skip-plugin-innodb-ft-deleted --loose-skip-plugin-innodb-ft-index-cache --loose-skip-plugin-innodb-ft-index-table --loose-skip-plugin-innodb-lock-waits --loose-skip-plugin-innodb-locks --loose-skip-plugin-innodb-metrics --loose-skip-plugin-innodb-mutexes --loose-skip-plugin-innodb-sys-columns --loose-skip-plugin-innodb-sys-datafiles --loose-skip-plugin-innodb-sys-fields --loose-skip-plugin-innodb-sys-foreign --loose-skip-plugin-innodb-sys-foreign-cols --loose-skip-plugin-innodb-sys-indexes --loose-skip-plugin-innodb-sys-semaphore-waits --loose-skip-plugin-innodb-sys-tables --loose-skip-plugin-innodb-sys-tablespaces --loose-skip-plugin-innodb-sys-tablestats --loose-skip-plugin-innodb-sys-virtual --loose-skip-plugin-innodb-tablespaces-encryption --loose-skip-plugin-innodb-tablespaces-scrubbing --loose-skip-plugin-innodb-trx --loose-skip-plugin-partition --loose-skip-plugin-sequence --loose-skip-plugin-unix-socket --loose-skip-plugin-user-variables --loose-innodb --loose-innodb-log-file-size=5M --disable-sync-frm --tmpdir=/home/anel/mariadb/builds/10.4/mysql-test/var/tmp/ --core-file --console --lc-messages-dir=/home/anel/mariadb/builds/10.4/sql/share/ --character-sets-dir=/home/anel/mariadb/10.4/sql/share/charsets
 
2020-06-10 10:50:54 0 [Note] /home/anel/mariadb/builds/10.4/sql/mysqld (mysqld 10.4.14-MariaDB-debug) starting as process 69764 ...
 
2020-06-10 10:50:54 0 [Warning] Could not increase number of max_open_files to more than 1024 (request: 32186)
 
2020-06-10 10:50:54 0 [Warning] Changed limits: max_open_files: 1024  max_connections: 151 (was 151)  table_cache: 421 (was 2000)
 
2020-06-10 10:50:54 0 [Note] Plugin 'partition' is disabled.
 
2020-06-10 10:50:54 0 [Note] Plugin 'SEQUENCE' is disabled.
 
2020-06-10 10:50:54 0 [Note] InnoDB: Using Linux native AIO
 
2020-06-10 10:50:54 0 [Note] InnoDB: The first innodb_system data file 'ibdata1' did not exist. A new tablespace will be created!
 
2020-06-10 10:50:54 0 [Note] InnoDB: !!!!!!!! UNIV_DEBUG switched on !!!!!!!!!
 
2020-06-10 10:50:54 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
 
2020-06-10 10:50:54 0 [Note] InnoDB: Uses event mutexes
 
2020-06-10 10:50:54 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
 
2020-06-10 10:50:54 0 [Note] InnoDB: Number of pools: 1
 
2020-06-10 10:50:54 0 [Note] InnoDB: Using SSE2 crc32 instructions
 
2020-06-10 10:50:54 0 [Note] mysqld: O_TMPFILE is not supported on /home/anel/mariadb/builds/10.4/mysql-test/var/tmp/ (disabling future attempts)
 
2020-06-10 10:50:54 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
 
2020-06-10 10:50:54 0 [Note] InnoDB: Completed initialization of buffer pool
 
2020-06-10 10:50:54 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
 
2020-06-10 10:50:54 0 [Note] InnoDB: Setting file './ibdata1' size to 12 MB. Physically writing the file full; Please wait ...
 
2020-06-10 10:50:54 0 [Note] InnoDB: File './ibdata1' size is now 12 MB.
 
2020-06-10 10:50:54 0 [Note] InnoDB: Setting log file ./ib_logfile101 size to 5242880 bytes
 
2020-06-10 10:50:54 0 [Note] InnoDB: Setting log file ./ib_logfile1 size to 5242880 bytes
 
2020-06-10 10:50:54 0 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
 
2020-06-10 10:50:54 0 [Note] InnoDB: New log files created, LSN=11452
 
2020-06-10 10:50:54 0 [Note] InnoDB: Doublewrite buffer not found: creating new
 
2020-06-10 10:50:54 0 [Note] InnoDB: Doublewrite buffer created
 
2020-06-10 10:50:54 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
 
2020-06-10 10:50:54 0 [Note] InnoDB: Creating foreign key constraint system tables.
 
2020-06-10 10:50:54 0 [Note] InnoDB: Creating tablespace and datafile system tables.
 
2020-06-10 10:50:54 0 [Note] InnoDB: Creating sys_virtual system tables.
 
2020-06-10 10:50:54 0 [Note] InnoDB: Creating shared tablespace for temporary tables
 
2020-06-10 10:50:54 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
 
2020-06-10 10:50:54 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
 
2020-06-10 10:50:54 0 [Note] InnoDB: Waiting for purge to start
 
2020-06-10 10:50:54 0 [Note] InnoDB: 10.4.14 started; log sequence number 0; transaction id 7
 
2020-06-10 10:50:54 0 [Note] Plugin 'INNODB_SYS_DATAFILES' is disabled.
 
2020-06-10 10:50:54 0 [Note] Plugin 'INNODB_SYS_TABLESTATS' is disabled.
 
2020-06-10 10:50:54 0 [Note] Plugin 'INNODB_LOCKS' is disabled.
 
2020-06-10 10:50:54 0 [Note] Plugin 'INNODB_MUTEXES' is disabled.
 
2020-06-10 10:50:54 0 [Note] Plugin 'INNODB_CMPMEM' is disabled.
 
2020-06-10 10:50:54 0 [Note] Plugin 'INNODB_CMP_PER_INDEX' is disabled.
 
2020-06-10 10:50:54 0 [Note] Plugin 'INNODB_CMP' is disabled.
 
2020-06-10 10:50:54 0 [Note] Plugin 'INNODB_FT_DELETED' is disabled.
 
2020-06-10 10:50:54 0 [Note] Plugin 'INNODB_CMP_RESET' is disabled.
 
2020-06-10 10:50:54 0 [Note] Plugin 'INNODB_LOCK_WAITS' is disabled.
 
2020-06-10 10:50:54 0 [Note] Plugin 'INNODB_TABLESPACES_ENCRYPTION' is disabled.
 
2020-06-10 10:50:54 0 [Note] Plugin 'INNODB_BUFFER_PAGE_LRU' is disabled.
 
2020-06-10 10:50:54 0 [Note] Plugin 'INNODB_SYS_FIELDS' is disabled.
 
2020-06-10 10:50:54 0 [Note] Plugin 'INNODB_CMPMEM_RESET' is disabled.
 
2020-06-10 10:50:54 0 [Note] Plugin 'FEEDBACK' is disabled.
 
2020-06-10 10:50:54 0 [Note] Plugin 'INNODB_SYS_COLUMNS' is disabled.
 
2020-06-10 10:50:54 0 [Note] Plugin 'INNODB_FT_INDEX_TABLE' is disabled.
 
2020-06-10 10:50:54 0 [Note] Plugin 'INNODB_CMP_PER_INDEX_RESET' is disabled.
 
2020-06-10 10:50:54 0 [Note] Plugin 'user_variables' is disabled.
 
2020-06-10 10:50:54 0 [Note] Plugin 'INNODB_FT_INDEX_CACHE' is disabled.
 
2020-06-10 10:50:54 0 [Note] Plugin 'INNODB_SYS_FOREIGN_COLS' is disabled.
 
2020-06-10 10:50:54 0 [Note] Plugin 'INNODB_FT_BEING_DELETED' is disabled.
 
2020-06-10 10:50:54 0 [Note] Plugin 'INNODB_BUFFER_POOL_STATS' is disabled.
 
2020-06-10 10:50:54 0 [Note] Plugin 'INNODB_TRX' is disabled.
 
2020-06-10 10:50:54 0 [Note] Plugin 'INNODB_SYS_FOREIGN' is disabled.
 
2020-06-10 10:50:54 0 [Note] Plugin 'INNODB_SYS_TABLES' is disabled.
 
2020-06-10 10:50:54 0 [Note] Plugin 'INNODB_FT_DEFAULT_STOPWORD' is disabled.
 
2020-06-10 10:50:54 0 [Note] Plugin 'INNODB_FT_CONFIG' is disabled.
 
2020-06-10 10:50:54 0 [Note] Plugin 'INNODB_BUFFER_PAGE' is disabled.
 
2020-06-10 10:50:54 0 [Note] Plugin 'INNODB_SYS_TABLESPACES' is disabled.
 
2020-06-10 10:50:54 0 [Note] Plugin 'INNODB_METRICS' is disabled.
 
2020-06-10 10:50:54 0 [Note] Plugin 'INNODB_SYS_INDEXES' is disabled.
 
2020-06-10 10:50:54 0 [Note] Plugin 'INNODB_SYS_VIRTUAL' is disabled.
 
2020-06-10 10:50:54 0 [Note] Plugin 'INNODB_TABLESPACES_SCRUBBING' is disabled.
 
2020-06-10 10:50:54 0 [Note] Plugin 'INNODB_SYS_SEMAPHORE_WAITS' is disabled.
 
2020-06-10 10:50:54 0 [Note] Plugin 'unix_socket' is disabled.
 
ERROR: 1071  Specified key was too long; max key length is 1000 bytes
 
2020-06-10 10:50:54 0 [ERROR] Aborting

I'm not sure how to solve this?

Comment by Daniel Black [ 2020-10-09 ]

error messages - ER_ILLEGAL_HA is used for all other ha_check_storage_engine_flag flag. I see you've gone with ER_ILLEGAL_HA_CREATE_OPTION. Given you can't alter table to a tempoary table this might be ok. serg's call however.

On mtr break - start of debugging this provided on Zulip
e.g

gdb for the functions you've changed

 
mysql-test/mtr --mem --gdb='b my_error;b check_engine;b check_unsupported_engine;r' --boot-gdb rocksdb.create_table

Comment by Anel Husakovic [ 2020-10-27 ]

Hi serg, can you please review PR 774.
Thanks.

Comment by Anel Husakovic [ 2021-06-23 ]

cvicentiu PR 774 updated for 10.7 please review.

Comment by Vicențiu Ciorbaru [ 2021-07-07 ]

OK to push.

Comment by Anel Husakovic [ 2021-07-11 ]

Pushed with commit f7216fa63d in 10.7

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