Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-16708

Unsupported commands for prepared statements

Details

    Description

      The page "Permitted statements" lists the commands that are allowed to be prepared.
      Unfortunately, there is no list which lists the commands that are currently not allowed in prepare and should be supported in future versions.

      The following list is not complete and should be expanded.

      • CREATE/ALTER/DROP PROCEDURE
      • CREATE/ALTER/DROP EVENT
      • CREATE/ALTER/DROP FUNCTION
      • DELETE FROM (bulk insert not supported)
      • LOAD DATA
      • ALTER DATABASE
      • USE
      • XA BEGIN
      • XA END
      • XA RECOVER
      • XA PREPARE
      • XA COMMIT
      • XA ROLLBACK
      • HELP

      Attachments

        Issue Links

          blocks

          Task - A task that needs to be done. CONJ-844 Performance improvement for 10.6 server

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. CONPY-90 XA test fail (SQL Alchemy)

          • Major - Major loss of function.
          • Confirmed
          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29329 Some MTR tests fail with PS protocol (missing warnings)

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30772 Inconsistency in handling empty comments in direct vs prepared execution

          • Major - Major loss of function.
          • Open
          is blocked by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24860 Incorrect behaviour of SET STATEMENT in case it is executed as a prepared statement

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25006 Failed assertion on executing EXPLAIN DELETE statement as a prepared statement

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25108 Running of the EXPLAIN EXTENDED statement produces extra warning in case it is executed in PS (prepared statement) mode

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25197 The statement set password=password('') executed in PS mode fails in case it is run by a user with expired password

          • Major - Major loss of function.
          • Closed
          is duplicated by

          Task - A task that needs to be done. MDEV-28997 Drop TRIGGERs, FUNCTIONs and ROUTINEs from prepared statement

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-10865 COLLATE keyword doesn't work in PREPARE query

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-24392 execute immediate '/*M!100601 select 10_6_only_sql */' returns error incorrectly

          • Trivial - Cosmetic problem like misspelt words or misaligned text.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25302 Attempt to set some system variables in PS mode results in issuing the error "ERROR 1615 (HY000): Prepared statement needs to be re-prepared"

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            diego dupin Diego Dupin added a comment -

            Another command that failed using binary protocol using COLLATE.
            example : 

            CREATE TABLE IF NOT EXISTS `tt` (
            		 
            `test` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL
            		 
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
            		 
             
            		 
            #Text work well
            		 
            SELECT * FROM `tt` WHERE `test` LIKE 'jj' COLLATE utf8mb4_unicode_ci;
            		 
            #will throw an error "Erreur SQL (1253) : COLLATION 'utf8mb4_unicode_ci' is not valid for CHARACTER SET 'binary'"
            		 
            PREPARE stmt FROM 'SELECT * FROM `tt` WHERE `test` LIKE ? COLLATE utf8mb4_unicode_ci';

            diego dupin Diego Dupin added a comment - Another command that failed using binary protocol using COLLATE. example : CREATE TABLE IF NOT EXISTS `tt` ( `test` varchar (500) COLLATE utf8mb4_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE =utf8mb4_unicode_ci;   #Text work well SELECT * FROM `tt` WHERE `test` LIKE 'jj' COLLATE utf8mb4_unicode_ci; #will throw an error "Erreur SQL (1253) : COLLATION 'utf8mb4_unicode_ci' is not valid for CHARACTER SET 'binary'" PREPARE stmt FROM 'SELECT * FROM `tt` WHERE `test` LIKE ? COLLATE utf8mb4_unicode_ci' ;
            serg Sergei Golubchik added a comment -

            I think we should have a generic fallback that makes all statements prepareable. Like, prepare stores the SQL statement on the server side and execute just completely executes the statement as in the normal protocol.

            Statements that return result sets need to use binary protocol.
            But luckily all not-yet-prepareable statements don't send results, so it seems.
            All statements using this fallback approach don't have to accept prepared parameters.

            This should be a fallback approach that automatically applies to all statements that are not natively prepare-able. Even new statements that can be created in the future.

            serg Sergei Golubchik added a comment - I think we should have a generic fallback that makes all statements prepareable. Like, prepare stores the SQL statement on the server side and execute just completely executes the statement as in the normal protocol. Statements that return result sets need to use binary protocol. But luckily all not-yet-prepareable statements don't send results, so it seems. All statements using this fallback approach don't have to accept prepared parameters. This should be a fallback approach that automatically applies to all statements that are not natively prepare-able. Even new statements that can be created in the future.
            georg Georg Richter added a comment -

            serg XA RECOVER returns a result set.

            georg Georg Richter added a comment - serg XA RECOVER returns a result set.
            serg Sergei Golubchik added a comment -

            oops. indeed. that makes it a bit more complex then

            serg Sergei Golubchik added a comment - oops. indeed. that makes it a bit more complex then
            sanja Oleksandr Byelkin added a comment -

            But it looks like metadata the same (depend of parameters but the same). so it is not a big problem to return them after prepare IMHO.

            sanja Oleksandr Byelkin added a comment - But it looks like metadata the same (depend of parameters but the same). so it is not a big problem to return them after prepare IMHO.
            sanja Oleksandr Byelkin added a comment - - edited

            That XA looks like do not have such complex data as ALTER/CREATE so easy can be implemented as prepared IMHO...

            actually CREATE/ALTER supported in SP, so to implement them we need only to organise "prepare" test somehow...

            so maybe it will be simpler to implement (real list of commands shoud be checked)

            sanja Oleksandr Byelkin added a comment - - edited That XA looks like do not have such complex data as ALTER/CREATE so easy can be implemented as prepared IMHO... actually CREATE/ALTER supported in SP, so to implement them we need only to organise "prepare" test somehow... so maybe it will be simpler to implement (real list of commands shoud be checked)
            shulga Dmitry Shulga added a comment -

            The following commands is not currently supported in prepared mode:

            SQLCOM_LOAD
            SQLCOM_LOCK_TABLES
            SQLCOM_UNLOCK_TABLES
            SQLCOM_CHANGE_DB
            SQLCOM_ALTER_DB
            SQLCOM_CREATE_FUNCTION
            SQLCOM_ALTER_FUNCTION
            SQLCOM_DROP_FUNCTION
            SQLCOM_CHECK
            SQLCOM_SAVEPOINT
            SQLCOM_RELEASE_SAVEPOINT
            SQLCOM_BEGIN
            SQLCOM_PURGE
            SQLCOM_PURGE_BEFORE
            SQLCOM_HA_OPEN
            SQLCOM_HA_CLOSE
            SQLCOM_EMPTY_QUERY
            SQLCOM_HELP
            SQLCOM_CREATE_PROCEDURE
            SQLCOM_CREATE_SPFUNCTION
            SQLCOM_CALL
            SQLCOM_DROP_PROCEDURE
            SQLCOM_ALTER_PROCEDURE
            SQLCOM_PREPARE
            SQLCOM_EXECUTE
            SQLCOM_DEALLOCATE_PREPARE
            SQLCOM_CREATE_VIEW
            SQLCOM_CREATE_TRIGGER
            SQLCOM_DROP_TRIGGER
            SQLCOM_XA_START
            SQLCOM_XA_END
            SQLCOM_XA_PREPARE
            SQLCOM_XA_COMMIT
            SQLCOM_XA_ROLLBACK
            SQLCOM_XA_RECOVER
            SQLCOM_ALTER_TABLESPACE
            SQLCOM_CREATE_SERVER
            SQLCOM_DROP_SERVER
            SQLCOM_ALTER_SERVER
            SQLCOM_CREATE_EVENT
            SQLCOM_ALTER_EVENT
            SQLCOM_DROP_EVENT
            SQLCOM_SIGNAL
            SQLCOM_RESIGNAL
            SQLCOM_SHOW_RELAYLOG_EVENTS
            SQLCOM_GET_DIAGNOSTICS
            SQLCOM_EXECUTE_IMMEDIATE
            SQLCOM_CREATE_PACKAGE
            SQLCOM_DROP_PACKAGE
            SQLCOM_CREATE_PACKAGE_BODY
            SQLCOM_DROP_PACKAGE_BODY
            SQLCOM_BACKUP
            SQLCOM_BACKUP_LOCK

            shulga Dmitry Shulga added a comment - The following commands is not currently supported in prepared mode: SQLCOM_LOAD SQLCOM_LOCK_TABLES SQLCOM_UNLOCK_TABLES SQLCOM_CHANGE_DB SQLCOM_ALTER_DB SQLCOM_CREATE_FUNCTION SQLCOM_ALTER_FUNCTION SQLCOM_DROP_FUNCTION SQLCOM_CHECK SQLCOM_SAVEPOINT SQLCOM_RELEASE_SAVEPOINT SQLCOM_BEGIN SQLCOM_PURGE SQLCOM_PURGE_BEFORE SQLCOM_HA_OPEN SQLCOM_HA_CLOSE SQLCOM_EMPTY_QUERY SQLCOM_HELP SQLCOM_CREATE_PROCEDURE SQLCOM_CREATE_SPFUNCTION SQLCOM_CALL SQLCOM_DROP_PROCEDURE SQLCOM_ALTER_PROCEDURE SQLCOM_PREPARE SQLCOM_EXECUTE SQLCOM_DEALLOCATE_PREPARE SQLCOM_CREATE_VIEW SQLCOM_CREATE_TRIGGER SQLCOM_DROP_TRIGGER SQLCOM_XA_START SQLCOM_XA_END SQLCOM_XA_PREPARE SQLCOM_XA_COMMIT SQLCOM_XA_ROLLBACK SQLCOM_XA_RECOVER SQLCOM_ALTER_TABLESPACE SQLCOM_CREATE_SERVER SQLCOM_DROP_SERVER SQLCOM_ALTER_SERVER SQLCOM_CREATE_EVENT SQLCOM_ALTER_EVENT SQLCOM_DROP_EVENT SQLCOM_SIGNAL SQLCOM_RESIGNAL SQLCOM_SHOW_RELAYLOG_EVENTS SQLCOM_GET_DIAGNOSTICS SQLCOM_EXECUTE_IMMEDIATE SQLCOM_CREATE_PACKAGE SQLCOM_DROP_PACKAGE SQLCOM_CREATE_PACKAGE_BODY SQLCOM_DROP_PACKAGE_BODY SQLCOM_BACKUP SQLCOM_BACKUP_LOCK

            People

              shulga Dmitry Shulga
              georg Georg Richter
              Votes:
              1 Vote for this issue
              Watchers:
              11 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.