Docs: Features: Routines

Support for managing routines (stored procedures and functions) is present in both Skeema Community Edition and Skeema Premium Edition. Extra functionality for stripping DEFINER clauses is present only in Premium, but aside from that feature, Community and Premium contain equivalent support for routines.

For additional advice and recipes on managing routines with Skeema, be sure to read our comprehensive blog post on versioning and deploying stored routines.

Options

The ignore-proc and ignore-func regex options may be used to force Skeema to ignore particular procedures or functions, or even all routines entirely.

To restrict or forbid the usage of routines entirely, see lint-has-routine.

Several options relate to DEFINER user clauses. These options affect routines, as well as other types of stored objects. See definer management for more information.

Privileges

In order to manage routines, Skeema’s database user needs the CREATE ROUTINE and ALTER ROUTINE privileges on all relevant databases. Additional global privileges may be necessary to see bodies of routines that have a different definer than the current user. See the privileges section of the requirements documentation.

If you wish to manage routines that have a different DEFINER than Skeema’s database user, SUPER privileges may be required in the Community edition of Skeema. Premium Skeema products add support for stripping DEFINER clauses to improve usability with non-superusers.

If your database server has binary logging enabled, SUPER privileges may be necessary for Skeema’s database user in order to manipulate functions, unless you have enabled the global server variable log_bin_trust_function_creators. This requirement affects functions but not stored procedures.

Manipulating routines

When modifying an existing routine, Skeema uses different syntax in MySQL vs MariaDB:

  • MySQL does not support CREATE OR REPLACE for atomic modifications to existing routines, so Skeema must emit a DROP followed by a re-CREATE. This is considered an unsafe action (requiring --allow-unsafe to proceed) as there may be a split-second period where the routine does not exist.
  • MariaDB supports CREATE OR REPLACE to atomically modify an existing routine. This syntax is used automatically in Skeema Premium v1.6+ or Skeema Community v1.9+. Since this operation is atomic, it is not considered an unsafe action.
  • Skeema v1.11+ now considers changes to a routine’s parameters or return type to be unsafe, even in MariaDB. These modifications often break application queries, and cause deploy-order challenges between applications and schema changes.
  • When only modifying a routine’s characteristics (SQL SECURITY; CONTAINS/READS/MODIFIES SQL; COMMENT clauses), Skeema v1.11+ uses ALTER PROCEDURE / ALTER FUNCTION syntax if possible. This is a safe, atomic change in both MySQL and MariaDB.

By default, skeema diff and skeema push do not examine the creation-time sql_mode or db_collation associated with a routine. To add these comparisons, use the compare-metadata option.

Dropping a routine is always considered a destructive action, requiring the --allow-unsafe option.

Unsupported features

Skeema does not support management of native or loadable UDFs, which are written in C++ and compiled into shared libraries.

MariaDB’s routine PACKAGEs are not supported yet by Skeema. If this feature would be useful to you, please consider filing a feature request.