Error code 1295 this command is not supported in the prepared statement protocol yet - Исправление ошибок и поиск оптимальных решений проблем

You can’t use PREPARE to run LOAD DATA INFILE.

The list of statements that you can run with PREPARE are documented in this page: https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html under the subheading «SQL Syntax Allowed in Prepared Statements». Note this list may be different in earlier versions of MySQL.

Because you can’t use PREPARE, you can’t do the method you’re using by setting a variable and making a dynamic SQL statement.

But you can run LOAD DATA INFILE without using PREPARE. You have to interpolate the filename into the statement using shell variable substitution and then run it as a direct SQL statement.

Your update.sql file might look like this:

LOAD DATA LOCAL INFILE '%spacename%' INTO TABLE tmp 
FIELDS TERMINATED BY ',' LINES TERMINATED BY 'n';

Then you can substitute your shell variable into the file and run the result this way:

sed s/%spacename%/$1/ update.sql | 
  mysql -h "localhost" -u "root" "-pmypassword" "mydb"

Another simpler way is to use mysqlimport, except this requires that the input filename be the same as your table name. You can either rename your input file to match the table you want to load into (which you call tmp), or else create a symbolic link:

ln -s $1 /tmp/tmp.list
mysqlimport --local -h "localhost" -u "root" "-pmypassword" "mydb" /tmp/tmp.list
rm -f /tmp/tmp.list

The «.list» extension is ignored by mysqlimport, so you can use any file extension, or none.

Источник

Hey, so bit of an explanation and background what is going on here…

Typically databases offer two protocols: the text and the binary protocol. Text protocol allows one to send queries, such as SELECT 1 or DROP TABLE "Users", and the data is sent as text (as the name implies). The binary protocol is more interesting here, because it allows us to prepare queries, such as SELECT ?, store the queries to the server and execute them with different parameters. It saves lots of time when repeating similar queries, but more importantly it allows us to pass the parameters as they are to the database, which then escapes them and prevents any SQL injection attacks from happening.

Basically what queryRaw and executeRaw do is they both take the parameters out from the query, replacing them with placeholders; therefore protecting from injections. But, this then leads to an interesting problem:

Some of the queries cannot be executed using the binary protocol!

So good examples would be BEGIN, COMMIT and ROLLBACK, that in most databases expect one to use the text protocol, meaning we get to see errors like this when using the executeRaw or queryRaw APIs.

What we could think about is, for example, implementing a new command executeBatch. As the name implies, it would be meant for batch executions, which again are usually only allowed using the text protocol (except in the Microsoft SQL Server, which allows preparing multiple queries at once).

So we could do something like:

client.executeBatch(
    "BEGIN; INSERT INTO `cats` (name, age) VALUES ('Musti', 7); COMMIT;"
);

Now, this being a text protocol, one big problem with it is we cannot parameterize any of the user-provided values. An API that puts the user and the safety of the system at risk by forcing to check every parameter for escaping is not really the best API. It is possible, but I’m not that convinced of its usefulness.

Источник

Syntax

PREPARE stmt_name FROM preparable_stmt

Syntax
Description
1. Oracle Mode
Permitted Statements
Example
See Also

Description

The PREPARE statement prepares a statement and assigns it a name,
stmt_name, by which to refer to the statement later. Statement names
are not case sensitive. preparable_stmt is either a string literal or a user variable (not a local variable, an SQL expression or a subquery) that contains the text of the statement. The text must
represent a single SQL statement, not multiple statements. Within the
statement, «?» characters can be used as parameter markers to indicate
where data values are to be bound to the query later when you execute
it. The «?» characters should not be enclosed within quotes, even if
you intend to bind them to string values. Parameter markers can be used
only where expressions should appear, not for SQL keywords,
identifiers, and so forth.

The scope of a prepared statement is the session within which it is
created. Other sessions cannot see it.

If a prepared statement with the given name already exists, it is
deallocated implicitly before the new statement is prepared. This means
that if the new statement contains an error and cannot be prepared, an
error is returned and no statement with the given name exists.

Prepared statements can be PREPAREd and EXECUTEd in a stored procedure, but not in a stored function or trigger. Also, even if the statement is PREPAREd in a procedure, it will not be deallocated when the procedure execution ends.

A prepared statement can access user-defined variables, but not local variables or procedure’s parameters.

If the prepared statement contains a syntax error, PREPARE will fail. As a side effect, stored procedures can use it to check if a statement is valid. For example:

CREATE PROCEDURE `test_stmt`(IN sql_text TEXT)
BEGIN
        DECLARE EXIT HANDLER FOR SQLEXCEPTION
        BEGIN
                SELECT CONCAT(sql_text, ' is not valid');
        END;
        SET @SQL := sql_text;
        PREPARE stmt FROM @SQL;
        DEALLOCATE PREPARE stmt;
END;

The FOUND_ROWS() and ROW_COUNT() functions, if called immediatly after EXECUTE, return the number of rows read or affected by the prepared statements; however, if they are called after DEALLOCATE PREPARE, they provide information about this statement. If the prepared statement produces errors or warnings, GET DIAGNOSTICS return information about them. DEALLOCATE PREPARE shouldn’t clear the diagnostics area, unless it produces an error.

A prepared statement is executed with EXECUTE and released
with DEALLOCATE PREPARE.

The max_prepared_stmt_count server system variable determines the number of allowed prepared statements that can be prepared on the server. If it is set to 0, prepared statements are not allowed. If the limit is reached, an error similar to the following will be produced:

ERROR 1461 (42000): Can't create more than max_prepared_stmt_count statements 
  (current value: 0)

Oracle Mode

Permitted Statements

Prior to this, not all statements can be prepared. Only the following SQL commands are permitted:

ALTER TABLE
ANALYZE TABLE
BINLOG
CACHE INDEX
CALL
CHANGE MASTER
CHECKSUM {TABLE | TABLES}
COMMIT
{CREATE | DROP} DATABASE
{CREATE | DROP} INDEX
{CREATE | RENAME | DROP} TABLE
{CREATE | RENAME | DROP} USER
{CREATE | DROP} VIEW
DELETE
DESCRIBE
DO
EXPLAIN
FLUSH {TABLE | TABLES | TABLES WITH READ LOCK | HOSTS | PRIVILEGES | LOGS | STATUS |
MASTER | SLAVE | DES_KEY_FILE | USER_RESOURCES | QUERY CACHE | TABLE_STATISTICS |
INDEX_STATISTICS | USER_STATISTICS | CLIENT_STATISTICS}
GRANT
INSERT
INSTALL {PLUGIN | SONAME}
HANDLER READ
KILL
LOAD INDEX INTO CACHE
OPTIMIZE TABLE
REPAIR TABLE
REPLACE
RESET {MASTER | SLAVE | QUERY CACHE}
REVOKE
ROLLBACK
SELECT
SET
SET GLOBAL SQL_SLAVE_SKIP_COUNTER
SET ROLE
SET SQL_LOG_BIN
SET TRANSACTION ISOLATION LEVEL
SHOW EXPLAIN
SHOW {DATABASES | TABLES | OPEN TABLES | TABLE STATUS | COLUMNS | INDEX | TRIGGERS |
EVENTS | GRANTS | CHARACTER SET | COLLATION | ENGINES | PLUGINS [SONAME] | PRIVILEGES |
PROCESSLIST | PROFILE | PROFILES | VARIABLES | STATUS | WARNINGS | ERRORS |
TABLE_STATISTICS | INDEX_STATISTICS | USER_STATISTICS | CLIENT_STATISTICS | AUTHORS |
CONTRIBUTORS}
SHOW CREATE {DATABASE | TABLE | VIEW | PROCEDURE | FUNCTION | TRIGGER | EVENT}
SHOW {FUNCTION | PROCEDURE} CODE
SHOW BINLOG EVENTS
SHOW SLAVE HOSTS
SHOW {MASTER | BINARY} LOGS
SHOW {MASTER | SLAVE | TABLES | INNODB | FUNCTION | PROCEDURE} STATUS
SLAVE {START | STOP}
TRUNCATE TABLE
SHUTDOWN
UNINSTALL {PLUGIN | SONAME}
UPDATE

Synonyms are not listed here, but can be used. For example, DESC can be used instead of DESCRIBE.

Compound statements can be prepared too.

Note that if a statement can be run in a stored routine, it will work even if it is called by a prepared statement. For example, SIGNAL can’t be directly prepared. However, it is allowed in stored routines. If the x() procedure contains SIGNAL, you can still prepare and execute the ‘CALL x();’ prepared statement.

PREPARE supports most kinds of expressions as well, for example:

PREPARE stmt FROM CONCAT('SELECT * FROM ', table_name);

When PREPARE is used with a statement which is not supported, the following error is produced:

ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet

Example

create table t1 (a int,b char(10));
insert into t1 values (1,"one"),(2, "two"),(3,"three");
prepare test from "select * from t1 where a=?";
set @param=2;
execute test using @param;
+------+------+
| a    | b    |
+------+------+
|    2 | two  |
+------+------+
set @param=3;
execute test using @param;
+------+-------+
| a    | b     |
+------+-------+
|    3 | three |
+------+-------+
deallocate prepare test;

Since identifiers are not permitted as prepared statements parameters, sometimes it is necessary to dynamically compose an SQL statement. This technique is called dynamic SQL). The following example shows how to use dynamic SQL:

CREATE PROCEDURE test.stmt_test(IN tab_name VARCHAR(64))
BEGIN
	SET @sql = CONCAT('SELECT COUNT(*) FROM ', tab_name);
	PREPARE stmt FROM @sql;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
END;

CALL test.stmt_test('mysql.user');
+----------+
| COUNT(*) |
+----------+
|        4 |
+----------+

Use of variables in prepared statements:

PREPARE stmt FROM 'SELECT @x;';

SET @x = 1;

EXECUTE stmt;
+------+
| @x   |
+------+
|    1 |
+------+

SET @x = 0;

EXECUTE stmt;
+------+
| @x   |
+------+
|    0 |
+------+

DEALLOCATE PREPARE stmt;

Issue

I want to drop all the procedures and functions in MySQL specific Database. I am trying to use this stored procedure for achieving this.

But I am getting an error:

Error Code: 1295 This command is not supported in the prepared statement protocol yet.

Thanks in advance.

delimiter $$
CREATE PROCEDURE sp_drop_all_stored_procedures()
begin
    declare flag boolean default false;
    declare sql_drop_statement varchar(1000);
  
    declare statements cursor for SELECT
    CONCAT('DROP ',ROUTINE_TYPE, ' ', ROUTINE_NAME, ';') as stmt
    FROM information_schema.ROUTINES where ROUTINE_SCHEMA = 'DB_NAME' and 
    ROUTINE_NAME != sp_drop_all_stored_procedures;
    
    declare continue handler for not found set flag := true;

    open statements;
    statements_loop: loop
        fetch statements into sql_drop_statement;
        
        if flag then
            leave statements_loop;
        end if;

        set @drop_statement = sql_drop_statement;
        select @drop_statement;
        PREPARE stmt FROM @drop_statement;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;  
        
    end loop statements_loop;
    close statements;
    
end $$
delimiter ;

Solution

The list of SQL statements that can be executed as a prepared statement are documented here: https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html#prepared-statements-permitted (scroll down the page about two-thirds of the way).

DROP PROCEDURE and DROP FUNCTION are not among them.

But you wouldn’t be able to do it even if you avoid using a prepared statement:

mysql> create procedure d() drop procedure p;
ERROR 1357 (HY000): Can't drop or alter a PROCEDURE from within another stored routine

Because of this, I don’t think you can do this in a stored procedure. You will have to do it from a client, where you can format each SQL statement and execute it directly, not as a prepared statement.

Answered By – Bill Karwin

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Источник

Is it possible to run command to create table and trigger from a procedure?

1295 — This command is not supported in the prepared statement protocol yet

CREATE DEFINER=`username`@`%` PROCEDURE `tg_audit`(IN `table_name` varchar(100))
BEGIN
    DECLARE sqlx LONGTEXT;
    DECLARE done INT DEFAULT 0;
    DECLARE column_name VARCHAR(100);
    DECLARE cursor_column CURSOR FOR SELECT i.column_name FROM information_schema.columns i WHERE i.table_name = table_name;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;    
    
    -- DELETE TRIGGER
    SET sqlx = CONCAT('DROP TRIGGER IF EXISTS ', table_name,'_update_audit_trigger');   
    PREPARE stmt FROM sqlx;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    
    
    -- CREATE TRIGGER
    SET sqlx = CONCAT('CREATE TRIGGER ',table_name,'_update_audit_trigger AFTER UPDATE ON `',table_name,'` FOR EACH ROWn',
                                        'BEGINnn' ,
                                        'tDECLARE m_change text;n',
                                        'tSET m_change = JSON_OBJECT();nn'
    );
        
    OPEN cursor_column;
        REPEAT
        FETCH cursor_column INTO column_name;
                IF NOT done THEN
                    
                    SET sqlx = CONCAT(sqlx, 'tIF coalesce(NEW.',column_name,', '') != coalesce(OLD.',column_name,', '') THENn',
                                                        'ttSET m_change = JSON_SET(m_change, '$.',column_name,'', NEW.',column_name,');n',
                                                        'tEND IF;nn'
                                            );             
                    
                    
                END IF;
        UNTIL done END REPEAT;
    CLOSE cursor_column;
    
    SET sqlx = CONCAT(sqlx, 'END;');
        
    PREPARE stmt FROM sqlx;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END

Источник

Syntax

Contents

Description

Oracle Mode

Permitted Statements

Example

See Also

Issue

Solution