[MDEV-28344] sys.ps_setup_save and dependent procedures fail with ER_ILLEGAL_HA_CREATE_OPTION Created: 2022-04-18  Updated: 2022-05-04  Resolved: 2022-05-04

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Temporary, Performance Schema
Affects Version/s: 10.7, 10.8
Fix Version/s: 10.7.4, 10.8.3, 10.9.1

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Anel Husakovic
Resolution: Fixed Votes: 0
Labels: regression


 Description    

call sys.ps_setup_save(0);

10.7 1866fb05

 
mysqltest: At line 1: query 'call sys.ps_setup_save(0)' failed: ER_ILLEGAL_HA_CREATE_OPTION (1478): Table storage engine 'PERFORMANCE_SCHEMA' does not support the create option 'TEMPORARY'

Correspondingly, procedures which call it also fail (at least sys.diagnostics).

The problem was introduced by this commit in 10.7.1:

commit f7216fa63d69448c3de1532a1dd197d0f28faefd (HEAD)
 
Author: Anel Husakovic
 
Date:   Wed Jun 10 09:07:15 2020 +0200
 
 
 
    MDEV-12914: Engine for temporary tables which are implicitly created as RocksDB is substituted silently



 Comments   
Comment by Anel Husakovic [ 2022-04-19 ]

cvicentiu, I was thinking this would be enough to for the bug but no, still ER_ILLEGAL_HA_CREATE_OPTION error.

 git diff
 
diff --git a/scripts/sys_schema/after_setup.sql b/scripts/sys_schema/after_setup.sql
 
index 833f84c4120..92ad195f6d5 100644
 
--- a/scripts/sys_schema/after_setup.sql
 
+++ b/scripts/sys_schema/after_setup.sql
 
@@ -14,5 +14,6 @@
 
 -- Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301  USA
 
 
 SET @@sql_log_bin = @sql_log_bin;
 
+SET @@default_tmp_storage_engine = @default_tmp_storage_engine;
 
 use mysql;
 
 
diff --git a/scripts/sys_schema/before_setup.sql b/scripts/sys_schema/before_setup.sql
 
index c0634d69e5c..09e71508ce5 100644
 
--- a/scripts/sys_schema/before_setup.sql
 
+++ b/scripts/sys_schema/before_setup.sql
 
@@ -16,6 +16,8 @@
 
 SET NAMES utf8;
 
 SET @sql_log_bin = @@sql_log_bin;
 
 SET sql_log_bin = 0;
 
+SET @default_tmp_storage_engine = @@default_tmp_storage_engine;
 
+SET default_tmp_storage_engine = MyISAM;
 
 
 CREATE DATABASE IF NOT EXISTS sys DEFAULT CHARACTER SET utf8;

  • I have tried as a workaround to disable the HTON_TEMPORARY for PS, but it leads to ER_CANT_CREATE_TABLE - meaning that it cannot create the table, so the command
    from diagnostics.sql will also fail when trying to create temporary table LIKE, that will use peformance_schema engine, that is not allowed. 

    CREATE TEMPORARY TABLE tmp_setup_actors LIKE performance_schema.setup_actors;

So IMHO the patch f7216fa63d69448c3de1532a1dd197d0f28faefd exposed the bug in sys_schema in general since sys_scheme used PERFORMANCE_SCHEMA as an temporary storage engine, to create the temporary tables and that is not what PS is allowing and older patch just prevents that (note previous we had silently substitution with MyISAM).
Also changing the code to use TABLE instead of TEMPORARY is not supported by the PS engine

--- a/scripts/sys_schema/procedures/ps_setup_reload_saved.sql
 
+++ b/scripts/sys_schema/procedures/ps_setup_reload_saved.sql
 
@@ -133,10 +133,10 @@ BEGIN
 
                              'YES')
 
      WHERE THREAD_ID NOT IN (SELECT THREAD_ID FROM tmp_threads);
 
 
-    DROP TEMPORARY TABLE tmp_setup_actors;
 
-    DROP TEMPORARY TABLE tmp_setup_consumers;
 
-    DROP TEMPORARY TABLE tmp_setup_instruments;
 
-    DROP TEMPORARY TABLE tmp_threads;
 
+    DROP TABLE tmp_setup_actors;
 
+    DROP TABLE tmp_setup_consumers;
 
+    DROP TABLE tmp_setup_instruments;
 
+    DROP TABLE tmp_threads;
 
 
     SELECT RELEASE_LOCK('sys.ps_setup_save') INTO v_lock_result;
 
 
diff --git a/scripts/sys_schema/procedures/ps_setup_save.sql b/scripts/sys_schema/procedures/ps_setup_save.sql
 
index a5a2197e935..e5fd9385a2d 100644
 
--- a/scripts/sys_schema/procedures/ps_setup_save.sql
 
+++ b/scripts/sys_schema/procedures/ps_setup_save.sql
 
@@ -75,15 +75,15 @@ BEGIN
 
     SELECT GET_LOCK('sys.ps_setup_save', in_timeout) INTO v_lock_result;
 
 
     IF v_lock_result THEN
 
-        DROP TEMPORARY TABLE IF EXISTS tmp_setup_actors;
 
-        DROP TEMPORARY TABLE IF EXISTS tmp_setup_consumers;
 
-        DROP TEMPORARY TABLE IF EXISTS tmp_setup_instruments;
 
-        DROP TEMPORARY TABLE IF EXISTS tmp_threads;
 
-
 
-        CREATE TEMPORARY TABLE tmp_setup_actors LIKE performance_schema.setup_actors;
 
-        CREATE TEMPORARY TABLE tmp_setup_consumers LIKE performance_schema.setup_consumers;
 
-        CREATE TEMPORARY TABLE tmp_setup_instruments LIKE performance_schema.setup_instruments;
 
-        CREATE TEMPORARY TABLE tmp_threads (THREAD_ID bigint unsigned NOT NULL PRIMARY KEY, INSTRUMENTED enum('YES','NO') NOT NULL);
 
+        DROP TABLE IF EXISTS tmp_setup_actors;
 
+        DROP TABLE IF EXISTS tmp_setup_consumers;
 
+        DROP TABLE IF EXISTS tmp_setup_instruments;
 
+        DROP TABLE IF EXISTS tmp_threads;
 
+
 
+        CREATE TABLE tmp_setup_actors LIKE performance_schema.setup_actors;
 
+        CREATE TABLE tmp_setup_consumers LIKE performance_schema.setup_consumers;
 
+        CREATE TABLE tmp_setup_instruments LIKE performance_schema.setup_instruments;
 
+        CREATE TABLE tmp_threads (THREAD_ID bigint unsigned NOT NULL PRIMARY KEY, INSTRUMENTED enum('YES','NO') NOT NULL);

Leads to 

mysqltest: At line 1: query 'call sys.ps_setup_save(0)' failed: ER_CANT_CREATE_TABLE (1005): Can't create table `sys`.`tmp_setup_actors` (errno: 131 "Command not supported by the engine")

P.S. you should run cmake . && make for each change.
Is it possible to use views here? Otherwise I would vote to disable tests pr_diagnostic.

Comment by Anel Husakovic [ 2022-04-22 ]

Hi wlad can you please review https://github.com/mariadb/server/tree/bb-10.7-anel-performance_schema_regresion

Comment by Vladislav Vaintroub [ 2022-04-25 ]

Looks good to me.

Comment by Anel Husakovic [ 2022-05-04 ]

Pushed with 56fd0d7b0613be

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