Error 1418 this function has none of deterministic no sql or reads sql data - Исправление ошибок и поиск оптимальных решений проблем

MySQL

Сегодня рассмотрим одну из популярных ошибок в MySQL. Сообщение об ошибке:

#1418 - This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

Обычно данная ошибка связана с тем, что функция является недетерминированной. Более подробную информацию можно узнать из статьи Детерминированные и недетерминированные функции.

Решить указанную ошибку можно двумя способами.

1. Отключить глобальную настройку log_bin_trust_function_creators

SET GLOBAL log_bin_trust_function_creators = 1;

2. Добавить строки между «RETURNS [TYPE OF DATA]» и «BEGIN».

LANGUAGE SQL DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER

Например:

CREATE FUNCTION test() RETURNS INT
LANGUAGE SQL DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER
BEGIN
  RETURN 1;
END

Спасибо и до новых встреч.

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

Источник

Конкретная ошибка:

При использовании mysql для создания и вызова хранимых процедур, функций и триггеров будет отображаться символ ошибки 1418.

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL,or READS SQL DATA in its declaration and binary logging is enabled(you *might* want to use the less safe log_bin_trust_function_creators variable)

После некоторых Baidu сводка выглядит следующим образом:

Потому что CREATE PROCEDURE, CREATE FUNCTION, ALTER PROCEDURE, ALTER FUNCTION, CALL, DROP PROCEDURE, DROP FUNCTION и другие операторы будут записаны в двоичный журнал и затем выполнены на подчиненном сервере. Однако неопределенная подпрограмма (хранимая процедура, функция, триггер), которая выполняет обновление, не может быть повторена.Выполнение на подчиненном сервере (относительно повторяющееся выполнение с главным сервером) может привести к тому, что восстановленные данные будут отличаться от исходных данных. Сервер отличается от основного сервера.

Чтобы решить эту проблему, MySQL требует:

На главном сервере, если подпрограмма не объявлена детерминированной или не изменяет данные, создание или замена подпрограммы будет отклонена. Это означает, что при создании подпрограммы вы должны либо объявить, что она детерминирована, либо не изменять данные.

Объявить можно двумя способами:

Первый: является ли утверждение детерминированным

ДЕТЕРМИНИСТИЧЕСКИЙ и НЕ ДЕТЕРМИНИСТИЧЕСКИЙ указывают, всегда ли подпрограмма дает одинаковый результат для заданного ввода.

Если функция не указана, значение по умолчанию НЕ ДЕТЕРМИНИСТИЧЕСКОЕ, поэтому ДЕТЕРМИНИСТИЧЕСКОЕ должно быть явно указано, чтобы объявить, что подпрограмма детерминирована. к

Здесь необходимо объяснить следующее: использование функции NOW () (или ее синонима) или функции RAND () не сделает подпрограмму недетерминированной. Для NOW () двоичный журнал включает метку времени и будет выполняться правильно. RAND () можно правильно скопировать, если он вызывается один раз в подпрограмме. Следовательно, можно считать, что отметка времени и начальное значение случайного числа являются детерминированными входными данными подпрограммы, и они одинаковы на главном и подчиненном серверах.

Второй тип: изменит ли оператор данные

СОДЕРЖИТ SQL, НЕТ SQL, ЧТЕНИЕ ДАННЫХ SQL, МОДИФИКАЦИЯ SQL используется для указания того, читает ли подпрограмма данные или записывает их.

Независимо от того, NO SQL или READS SQL DATA, следует отметить, что подпрограмма не изменяет данные, но одна из них должна быть указана явно, потому что, если таковая указана, спецификация по умолчанию — CONTAINS SQL.

По умолчанию, если разрешено принимать операторы CREATE PROCEDURE или CREATE FUNCTION, одно из DETERMINISTIC или NO SQL и READS SQL DATA должно быть указано явно, иначе возникнет ошибка 1418.

Решение:

Также есть два решения:

Первый — объявить одно из DETERMINISTIC или NO SQL и READS SQL DATA при создании подпрограммы (хранимой процедуры, функции, триггера), например:

CREATE DEFINER = CURRENT_USER PROCEDURE `NewProc`()
    DETERMINISTIC
BEGIN
 #Routine body goes here...
END;

Второй — доверять создателю подпрограммы. Запрещается создавать или изменять подпрограмму в соответствии с требованиями разрешений SUPER. Установите глобальную системную переменную log_bin_trust_routine_creators в 1. Есть три способа настройки:

1. Выполните команду SET GLOBAL log_bin_trust_function_creators = 1 на клиенте.

2. При запуске MySQL добавьте —log-bin-trust-function-creators, чтобы выбрать таланты, и установите для параметра значение 1.

3. Добавьте log-bin-trust-function-creators = 1 в раздел [mysqld] файла конфигурации MySQL my.ini или my.cnf.

Источник статьи:http://blog.sina.com.cn/s/blog_6f68845001013k8a.html

Источник

Not so long ago I was creating the following function in MySQL:

DELIMITER //CREATE FUNCTION NextVal (vname VARCHAR(30), input_tenant_id INT )
RETURNS INT
BEGIN
  UPDATE sequences
SET next = next + 1
WHERE name = vname and tenant_id= input_tenant_id;
  RETURN (SELECT next FROM sequences WHERE name = vname and tenant_id=input_tenant_id);
END
  //
  DELIMITER ;

Since code is fine, my expectation was that it will execute without any issues, but I got an error instead:

Error  Code: 1418. This function has none od DETERMINISTIC, NO SQL, or READ SQL DATA in its declaration and binary logging is enabled (you *might* want to use less safe log.)

I researched a bit to find out how to solve this issue, and this is what I found out. If we’re creating a function that does some data modification, it’s considered non-deterministic. Those are functions that do update, insert, or delete statements. Those functions have some strict checking, and this setting relaxes the checking for non-deterministic functions:

SET GLOBAL log_bin_trust_function_creators = 1;

After this setting, I didn’t have the issue I mentioned anymore.

Check also…

Read HTTP Headers in Spring REST Controller
Get health, readiness and liveness information from within an application code
Difference Between Request Scope and Session Scope in Spring
The Spring Bean Scopes Explained

Источник

Cause analysis and solution of mysql error 1418

Specific error:

Use mysql to create and call stored procedures,Functions and triggers will have an error symbol of 1418.

error 1418 (hy000):this function has none of deterministic, no sql, or reads sql data in its declaration and binary logging is enabled (you * might * want to use the less safe log_bin_trust_function_creators variable)

After some Baidu,Summarized as follows:

Because create procedure, create function, alter procedure, alter function, call, drop procedure, drop function and other statements will be written into the binary log,Then execute it on the slave.However, an indeterminate subroutine (stored procedure, function, trigger) that performs an update is non-repeatable,Execution on the slave server (relative to the master server is repeated) may cause the recovered data to be different from the original data,The slave server is different from the master server.

To solve this problem,mysql mandatory requirements:

On the master server,Unless the subroutine is declared deterministic or does not change data,Otherwise, creating or replacing subroutines will be rejected.This means that when creating a subroutine,Must either declare it deterministic,Either it doesn’t change the data.

There are two ways to declare:

The first:whether the statement is deterministic

deterministic and not deterministic indicate whether a subroutine always produces the same result for a given input.

If no feature is given,The default is not deterministic, so deterministic must be explicitly specified to declare a subroutine to be deterministic.

The point here is that using the now () function (or its synonym) or the rand () function does not make a subroutine nondeterministic.For now (), the binary log includes a timestamp and will be executed correctly.rand () can be copied correctly as long as it is called once in a subroutine.Therefore, the timestamp and random number seed can be considered as the deterministic inputs of the subroutine.They are the same on the master and slave servers.

Second:whether the statement will change the data

contains sql, no sql, reads sql data, modifies sql to indicate whether the subroutine reads or writes data.

Both no sql and reads sql data indicate that the subroutine has not changed the data.But one must be explicitly specified,Because if anyThe default designation is contains sql.

by default,If create procedure or create function statements are allowed,You must explicitly specify one of deterministic or no sql and reads sql data,Otherwise, a 1418 error will be generated.

Solution:

There are two solutions,

The first is when the subroutine (stored procedure, function, trigger) is created, declared as deterministic or no sql and reads sql data,

E.g:

create definer=current_user procedure `newproc` ()
  deterministic
begin
 #routine body goes here ...
end;

The second is the creator of the trusted subroutine, prohibit the requirement of super permissions when creating and modifying subroutines,Set the log_bin_trust_routine_creators global system variable to 1. There are three setting methods:

1.Execute set global log_bin_trust_function_creators=1 on the client;

2.When MySQL is started, add —log-bin-trust-function-creators to select the candidate and set the parameter to 1.

3. Add log-bin-trust-function-creators=1 to the [mysqld] section in the mysql configuration file my.ini or my.cnf

Источник