7

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.

Improve this question

2 Answers 2

Reset to default
6

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.

Improve this answer
10
  • This is totally wrong and extremely misleading. The deterministic flag refers to the effect that the routine has on the database so routines that only read data are always deterministic.
    – bikeman868
    Jul 25, 2018 at 6:21
  • 1
    @bikeman868 A routine is considered “deterministic” if it always produces the same result for the same input parameters, and “not deterministic” otherwise. — that doesn't seem to me to tally with your claim, do you have a reference to back it up?
    – Jack Douglas
    Jul 25, 2018 at 8:34
  • The quote you gave is missing a key phrase that has led to much confusion. It should say “A routine is considered deterministic if it always produces the same result in the database for the same input parameters”.
    – bikeman868
    Jul 25, 2018 at 14:28
  • 1
    Stop and think for a minute about why MySQL might need to know if your routine returns different data for the same inputs, why would it care? Now think about the fact that MySQL only writes the routine name and input parameters in the replication log unless you mark the routine as non deterministic in which case it writes all the changed rows instead.
    – bikeman868
    Jul 25, 2018 at 14:38
  • 1
    The docs are not well phrased and many people have arrived at the same interpretation as you, so there is much confusion about this subject. I encourage you to set up replication and examine the log files.
    – bikeman868
    Jul 26, 2018 at 6:29
2

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: [email protected]
    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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.