MySQL Deterministic Procedures

Question

As a general rule, should I be declaring all my stored procedures with the DETERMINISTIC keyword if they are indeed deterministic?

It seems to me that the majority of stored procedures will be deterministic. Am I right in thinking that the only non-deterministic procedures are ones that call non-deterministic functions like RAND() or CURDATE()?

Anyway, the reason I'm asking is because when I use the Data Restore feature within MySQL Workbench I get this error:

ERROR 1418 (HY000) at line 1209: 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)

Is it better practice to simply set the 'log_bin_trust_function_creators=1' instead? Bear in mind that I have 50+ stored procedures.

Jack Douglas · Answer 1 · 2018-07-26T09:44:59.573

It makes sense to use the deterministic keyword for procedures that are really deterministic, because:

Declaring a deterministic routine as NONDETERMINISTIC might diminish performance by causing available optimizations not to be used.

But:

Am I right in thinking that the only non-deterministic procedures are ones that call non-deterministic functions like RAND() or CURDATE()?

No, a procedure that reads data from the database and bases it's result on that data (in addition to it's inputs) is also non-deterministic (because that data might change between calls).

It is worth pointing out that whether a procedure is DETERMINISTIC or not needs to be considered in a replication context:

When you create a stored function, you must declare either that it is deterministic or that it does not modify data. Otherwise, it may be unsafe for data recovery or replication.

although:

If row-based or mixed binary logging is in use, the statement is accepted and replicated even if the function was defined without the DETERMINISTIC keyword.

RolandoMySQLDBA · Answer 2 · 2011-07-25T15:52:48.133

log_bin_trust_function_creators

This is simply an ace in the hole when migrating Stored Procedures. The DETERMINISTIC property was added to to two things:

Protect consistency of called stored procedures that are stored in binary logs
Save developers the headache of going back and adding the property

The error message simply reared its ugly head because binary logging is enabled and there are stored procedures present. Either disable binary logging, mark the stored procedures as deterministic.

Here is something quick-and-dirty you can do to all the stored procedures without having to edit the scripts: Update mysql.proc and do this:

UPDATE mysql.proc SET is_deterministic = 'YES';

I tried it on one in MySQL 5.5.12 for Windows

mysql> select db,name from mysql.proc;
+--------+-----------------------+
| db     | name                  |
+--------+-----------------------+
| lovesh | LoadMyData            |
| stuff  | DoesUserHaveEditPrivs |
| stuff  | LoadSampleData        |
| stuff  | MakeTables            |
| stuff  | ShowLast40            |
| test   | CreateSampleTable     |
| test   | CreateSampleTables    |
| test   | GetMissingIntegers    |
| test   | GetTestTableCounts    |
| test   | ImportWeeklyBatch     |
| test   | InsertName            |
| test   | LoadSampleTables      |
| test   | MigrateColumn         |
+--------+-----------------------+
13 rows in set (0.00 sec)

mysql> select db,name,is_deterministic from mysql.proc ;
+--------+-----------------------+------------------+
| db     | name                  | is_deterministic |
+--------+-----------------------+------------------+
| test   | InsertName            | NO               |
| test   | MigrateColumn         | NO               |
| test   | GetMissingIntegers    | NO               |
| test   | CreateSampleTable     | NO               |
| test   | CreateSampleTables    | NO               |
| test   | LoadSampleTables      | NO               |
| test   | ImportWeeklyBatch     | NO               |
| test   | GetTestTableCounts    | NO               |
| stuff  | MakeTables            | NO               |
| stuff  | ShowLast40            | NO               |
| stuff  | LoadSampleData        | NO               |
| stuff  | DoesUserHaveEditPrivs | NO               |
| lovesh | LoadMyData            | NO               |
+--------+-----------------------+------------------+
13 rows in set (0.00 sec)

mysql> update mysql.proc set is_deterministic='YES' where db='lovesh';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select db,name,is_deterministic from mysql.proc ;
+--------+-----------------------+------------------+
| db     | name                  | is_deterministic |
+--------+-----------------------+------------------+
| test   | InsertName            | NO               |
| test   | MigrateColumn         | NO               |
| test   | GetMissingIntegers    | NO               |
| test   | CreateSampleTable     | NO               |
| test   | CreateSampleTables    | NO               |
| test   | LoadSampleTables      | NO               |
| test   | ImportWeeklyBatch     | NO               |
| test   | GetTestTableCounts    | NO               |
| stuff  | MakeTables            | NO               |
| stuff  | ShowLast40            | NO               |
| stuff  | LoadSampleData        | NO               |
| stuff  | DoesUserHaveEditPrivs | NO               |
| lovesh | LoadMyData            | YES              |
+--------+-----------------------+------------------+
13 rows in set (0.00 sec)

mysql> desc information_schema.routines;
+--------------------------+---------------+------+-----+---------------------+-------+
| Field                    | Type          | Null | Key | Default             | Extra |
+--------------------------+---------------+------+-----+---------------------+-------+
| SPECIFIC_NAME            | varchar(64)   | NO   |     |                     |       |
| ROUTINE_CATALOG          | varchar(512)  | NO   |     |                     |       |
| ROUTINE_SCHEMA           | varchar(64)   | NO   |     |                     |       |
| ROUTINE_NAME             | varchar(64)   | NO   |     |                     |       |
| ROUTINE_TYPE             | varchar(9)    | NO   |     |                     |       |
| DATA_TYPE                | varchar(64)   | NO   |     |                     |       |
| CHARACTER_MAXIMUM_LENGTH | int(21)       | YES  |     | NULL                |       |
| CHARACTER_OCTET_LENGTH   | int(21)       | YES  |     | NULL                |       |
| NUMERIC_PRECISION        | int(21)       | YES  |     | NULL                |       |
| NUMERIC_SCALE            | int(21)       | YES  |     | NULL                |       |
| CHARACTER_SET_NAME       | varchar(64)   | YES  |     | NULL                |       |
| COLLATION_NAME           | varchar(64)   | YES  |     | NULL                |       |
| DTD_IDENTIFIER           | longtext      | YES  |     | NULL                |       |
| ROUTINE_BODY             | varchar(8)    | NO   |     |                     |       |
| ROUTINE_DEFINITION       | longtext      | YES  |     | NULL                |       |
| EXTERNAL_NAME            | varchar(64)   | YES  |     | NULL                |       |
| EXTERNAL_LANGUAGE        | varchar(64)   | YES  |     | NULL                |       |
| PARAMETER_STYLE          | varchar(8)    | NO   |     |                     |       |
| IS_DETERMINISTIC         | varchar(3)    | NO   |     |                     |       |
| SQL_DATA_ACCESS          | varchar(64)   | NO   |     |                     |       |
| SQL_PATH                 | varchar(64)   | YES  |     | NULL                |       |
| SECURITY_TYPE            | varchar(7)    | NO   |     |                     |       |
| CREATED                  | datetime      | NO   |     | 0000-00-00 00:00:00 |       |
| LAST_ALTERED             | datetime      | NO   |     | 0000-00-00 00:00:00 |       |
| SQL_MODE                 | varchar(8192) | NO   |     |                     |       |
| ROUTINE_COMMENT          | longtext      | NO   |     | NULL                |       |
| DEFINER                  | varchar(77)   | NO   |     |                     |       |
| CHARACTER_SET_CLIENT     | varchar(32)   | NO   |     |                     |       |
| COLLATION_CONNECTION     | varchar(32)   | NO   |     |                     |       |
| DATABASE_COLLATION       | varchar(32)   | NO   |     |                     |       |
+--------------------------+---------------+------+-----+---------------------+-------+
30 rows in set (0.02 sec)

mysql> select * from information_schema.routines where routine_schema='lovesh'\G
*************************** 1. row ***************************
           SPECIFIC_NAME: LoadMyData
         ROUTINE_CATALOG: def
          ROUTINE_SCHEMA: lovesh
            ROUTINE_NAME: LoadMyData
            ROUTINE_TYPE: PROCEDURE
               DATA_TYPE:
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
          DTD_IDENTIFIER: NULL
            ROUTINE_BODY: SQL
      ROUTINE_DEFINITION: BEGIN
    DECLARE NDX INT;
    SET NDX = 0;
    WHILE NDX < 100 DO
        INSERT INTO mydata (ti_time) VALUES (NOW() - INTERVAL CEILING(14400*RAND()) SECOND
);
        SET NDX = NDX + 1;
    END WHILE;
END
           EXTERNAL_NAME: NULL
       EXTERNAL_LANGUAGE: NULL
         PARAMETER_STYLE: SQL
        IS_DETERMINISTIC: YES
         SQL_DATA_ACCESS: CONTAINS SQL
                SQL_PATH: NULL
           SECURITY_TYPE: DEFINER
                 CREATED: 2011-07-25 11:12:02
            LAST_ALTERED: 2011-07-18 22:39:34
                SQL_MODE:
         ROUTINE_COMMENT:
                 DEFINER: lwdba@127.0.0.1
    CHARACTER_SET_CLIENT: cp850
    COLLATION_CONNECTION: cp850_general_ci
      DATABASE_COLLATION: latin1_swedish_ci
1 row in set (0.03 sec)

mysql>

If you do this to all the stored procedures, this should make MySQL Workbench stop complaining.

MySQL Deterministic Procedures

2 Answers2