23.8 Restrictions on Stored Programs — MySQL 5.7

23.8 Ограничения на хранимые программы

Эти ограничения относятся к функциям, описанным в Главе 23, Хранимые объекты.

Некоторые из отмеченных здесь ограничений применяются ко всем хранимым процедурам; то есть как к хранимым процедурам, так и к хранимым функциям. Также существуют некоторые ограничения, специфичные для хранимых функций, но не для хранимых процедур.

Ограничения для хранимых функций также применяются к триггерам. Также существуют некоторые ограничения, специфичные для триггеров.

Ограничения для хранимых процедур также применяются к DO в определениях событий планировщика событий. Также существуют некоторые ограничения, специфичные для событий.

SQL-запросы, запрещённые в хранимых процедурах

Хранимые процедуры не могут содержать произвольные SQL-запросы. Следующие запросы запрещены:

Запросы блокировки LOCK TABLES и UNLOCK TABLES.
ALTER VIEW.
LOAD DATA и LOAD XML.
SQL-подготовленные запросы (PREPARE, EXECUTE, DEALLOCATE PREPARE) могут использоваться в хранимых процедурах, но не в хранимых функциях или триггерах. Таким образом, хранимые функции и триггеры не могут использовать динамический SQL (где вы конструируете запросы как строки и затем выполняете их).
В целом, запросы, запрещённые в SQL-подготовленных запросах, также запрещены в хранимых программах. Список запросов, поддерживаемых в качестве подготовленных запросов, см. в Разделе 13.5, “Подготовленные запросы”. Исключениями являются SIGNAL, RESIGNAL и GET DIAGNOSTICS, которые не допускаются в качестве подготовленных запросов, но допускаются в хранимых программах.
Поскольку локальные переменные доступны только во время выполнения хранимой программы, ссылки на них не допускаются в подготовленных запросах, созданных внутри хранимой программы. Область видимости подготовленного запроса — текущая сессия, а не хранимая программа, поэтому запрос может быть выполнен после завершения программы, в какой момент переменные больше не будут доступны. Например, SELECT ... INTO local_var нельзя использовать в качестве подготовленного запроса. Это ограничение также относится к параметрам хранимых процедур и функций. См. Раздел 13.5.1, “Запрос PREPARE”.
Во всех хранимых программах (хранимые процедуры и функции, триггеры и события) анализатор рассматривает BEGIN [WORK] как начало блока BEGIN ... END. Чтобы начать транзакцию в этом контексте, используйте START TRANSACTION вместо этого.

Ограничения для хранимых функций

Следующие дополнительные запросы или операции запрещены внутри хранимых функций. Они разрешены внутри хранимых процедур, за исключением хранимых процедур, вызываемых из хранимой функции или триггера. Например, если вы используете FLUSH в хранимой процедуре, то эта хранимая процедура не может быть вызвана из хранимой функции или триггера.

Запросы, выполняющие явное или неявное подтверждение или откат. Поддержка этих запросов не требуется стандартом SQL, который указывает, что каждый поставщик СУБД может решить, разрешить их или нет.
Запросы, возвращающие набор результатов. Это включает SELECT запросы без INTO var_list клаузы и другие запросы, такие как SHOW, EXPLAIN и CHECK TABLE. Функция может обрабатывать набор результатов либо с помощью SELECT ... INTO var_list, либо используя курсор и FETCH запросы. См. Раздел 13.2.9.1, “Запрос SELECT ... INTO” и Раздел 13.6.6, “Курсоры”.
FLUSH запросы.
Хранимые функции не могут использоваться рекурсивно.
Хранимая функция или триггер не могут изменять таблицу, которая уже используется (для чтения или записи) запросом, вызвавшим функцию или триггер.
Если вы неоднократно ссылаетесь на временную таблицу в хранимой функции под разными псевдонимами, возникает ошибка Can't reopen table: 'tbl_name', даже если ссылки происходят в разных запросах внутри функции.
HANDLER ... READ запросы, вызывающие хранимые функции, могут привести к ошибкам репликации и запрещены.

Ограничения для триггеров

Для триггеров применяются следующие дополнительные ограничения:

Триггеры не активируются действиями внешних ключей.
При использовании репликации на основе строк триггеры на реплике не активируются запросами, исходящими от источника. Триггеры на реплике активируются при использовании репликации на основе запросов. Дополнительную информацию см. в Разделе 16.4.1.34, “Репликация и триггеры”.
Оператор RETURN не допускается в триггерах, которые не могут возвращать значение. Чтобы немедленно выйти из триггера, используйте оператор LEAVE.
Триггеры не допускаются для таблиц в базе данных mysql. Также они не допускаются для таблиц INFORMATION_SCHEMA или performance_schema. Эти таблицы фактически являются представлениями, и триггеры не допускаются для представлений.
Кэш триггеров не обнаруживает изменения метаданных базовых объектов. Если триггер использует таблицу, и таблица изменилась с момента загрузки триггера в кэш, триггер работает с устаревшими метаданными.

Возникновение конфликтов имён в хранимых процедурах

Одно и то же идентификатор может использоваться для параметра процедуры, локальной переменной и столбца таблицы. Также, одно и то же имя локальной переменной может использоваться в вложенных блоках. Например:

CREATE PROCEDURE p (i INT)
BEGIN
  DECLARE i INT DEFAULT 0;
  SELECT i FROM t;
  BEGIN
    DECLARE i INT DEFAULT 1;
    SELECT i FROM t;
  END;
END;

В таких случаях идентификатор является неоднозначным, и применяются следующие правила приоритета:

Локальная переменная имеет приоритет над параметром процедуры или столбцом таблицы.
Параметр процедуры имеет приоритет над столбцом таблицы.
Локальная переменная во внутреннем блоке имеет приоритет над локальной переменной во внешнем блоке.

Поведение, при котором переменные имеют приоритет над столбцами таблиц, является нестандартным.

Особенности репликации

Использование хранимых процедур может вызывать проблемы с репликацией. Эта проблема обсуждается подробнее в Разделе 23.7, «Регистрация двоичных логов хранимых программ».

Параметр --replicate-wild-do-table=db_name.tbl_name применяется к таблицам, представлениям и триггерам. Он не применяется к хранимым процедурам и функциям, или событиям. Чтобы отфильтровать операторы, работающие с последними объектами, используйте один или несколько параметров --replicate-*-db.

Особенности отладки

Средств отладки хранимых процедур нет.

Неподдерживаемый синтаксис из стандарта SQL:2003

Синтаксис хранимых процедур MySQL основан на стандарте SQL:2003. Следующие элементы этого стандарта в настоящее время не поддерживаются:

UNDO обработчики
FOR циклы

Особенности одновременного выполнения хранимых процедур

Для предотвращения проблем взаимодействия сессий, когда клиент отправляет оператор, сервер использует моментальный снимок доступных для выполнения операторов и триггеров. То есть, сервер создаёт список процедур, функций и триггеров, которые могут быть использованы во время выполнения оператора, загружает их и затем приступает к выполнению оператора. В то время, как выполняется оператор, он не видит изменений в процедурах, выполняемых другими сессиями.

Для максимальной параллельности хранимые функции должны минимизировать побочные эффекты; в частности, обновление таблицы внутри хранимой функции может снизить одновременные операции с этой таблицей. Хранимые функции приобретают блокировки таблиц перед выполнением, чтобы избежать несоответствия в двоичном логе из-за несовпадения порядка выполнения операторов и их отображения в логе. При использовании двоичного лога на основе операторов, записываются операторы, вызывающие функцию, а не операторы, выполняемые внутри функции. Следовательно, хранимые функции, которые обновляют одни и те же базовые таблицы, не выполняются параллельно. В отличие от этого, хранимые процедуры не приобретают блокировки на уровне таблиц. Все операторы, выполняемые внутри хранимых процедур, записываются в двоичный лог, даже при использовании двоичного лога на основе операторов. См. Раздел 23.7, «Регистрация двоичных логов хранимых программ».

Ограничения планировщика событий

Следующие ограничения относятся к планировщику событий:

Имена событий обрабатываются без учёта регистра. Например, вы не можете иметь два события в одной базе данных с именами anEvent и AnEvent.
Создание, изменение или удаление события из хранимой программы запрещено, если имя события задано через переменную. Также событие не может создавать, изменять или удалять хранимые процедуры или триггеры.
Операторы DDL над событиями запрещены, пока активен оператор LOCK TABLES.
Временные интервалы YEAR, QUARTER, MONTH и YEAR_MONTH при расчёте времени события учитываются в месяцах; другие интервалы учитываются в секундах. Нет способа обеспечить выполнение событий, запланированных на один и тот же момент времени, в определённом порядке. Кроме того — из-за округления, свойств многопоточных приложений и того факта, что требуется определённое время для создания событий и их запуска — события могут быть задержаны на 1 или 2 секунды. Однако время, показанное в таблице Information Schema EVENTS в столбце LAST_EXECUTED или в таблице mysql.event в столбце last_executed, всегда точно соответствует фактическому времени выполнения события с точностью до одной секунды. (См. также Bug #16522.)
Каждое выполнение операторов, содержащихся в теле события, происходит в новом соединении; таким образом, эти операторы не влияют на счётчик операторов в текущей пользовательской сессии на сервере, такие как Com_select и Com_insert, отображаемые SHOW STATUS. Однако такие счётчики обновляются в глобальной области. (Bug #16422)
События не поддерживают времена, которые позднее конца эпохи Unix; это примерно начало 2038 года. Такие даты специально не допускаются планировщиком событий. (Bug #16396)
Ссылки на хранимые функции, загружаемые функции и таблицы в предложениях ON SCHEDULE операторов CREATE EVENT и ALTER EVENT не поддерживаются. Такие ссылки не допускаются. (См. Bug #22830 для получения дополнительной информации.)

Хранимые программы в NDB Cluster

Хотя хранимые процедуры, хранимые функции, триггеры и запланированные события поддерживаются таблицами, использующими движок хранения NDB, необходимо учитывать, что они не автоматически распространяются между серверами MySQL, выступающими в качестве узлов Cluster SQL. Это из-за следующего:

Определения хранимых процедур хранятся в таблицах в системе базы данных mysql, использующих движок хранения MyISAM, и, следовательно, не участвуют в кластеризации.
Файлы .TRN и .TRG, содержащие определения триггеров, не считываются движком хранения NDB и не копируются между узлами кластера.

Любая хранимая процедура или триггер, взаимодействующая с таблицами NDB Cluster, должна быть повторно создана путём выполнения соответствующих операторов CREATE PROCEDURE, CREATE FUNCTION или CREATE TRIGGER на каждом сервере MySQL, участвующем в кластере, где вы хотите использовать хранимую процедуру или триггер. Аналогично, любые изменения в существующих хранимых процедурах или триггерах должны быть выполнены явно на всех узлах Cluster SQL, используя соответствующие операторы ALTER или DROP на каждом сервере MySQL, обращающемся к кластеру.

Предупреждение

Не пытайтесь обойти проблему, описанную в первом пункте, преобразованием таблиц базы данных mysql для использования движка хранения NDB. Изменение системных таблиц в базе данных mysql не поддерживается и, скорее всего, приведёт к нежелательным результатам.