Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

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, procedures that read data from the database are also generally non-deterministic (because that data might change between calls)

share|improve this answer
    
I love short answers that pack a wallop. +1 !!! –  RolandoMySQLDBA Jul 25 '11 at 11:52
    
@Rolando - I haven't addressed the "Data Restore" side of the question - perhaps you can shed some light? –  Jack Douglas Jul 25 '11 at 11:55
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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