MySQL Forums
Forum List  »  Stored Procedures

NOT DETERMINISTIC Function + Replication
Posted by: Joshua O'Dea
Date: May 27, 2016 01:23AM

Hi everyone,

I have done a lot of reading on this subject and come to the conclusion that I have a function that is technically not deterministic, but could/should be replication safe (???).

I'm running mixed mode binary logging for master/slave replication and have the following sequence generation structure:

---------------------------------------------------------------

CREATE TABLE `core_sequence` (
`name` varchar(32) NOT NULL,
`next` int(11) NOT NULL DEFAULT '1',
`inc` smallint(6) NOT NULL DEFAULT '1',
PRIMARY KEY (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

DELIMITER ;;
CREATE FUNCTION `NextSequence`(vname VARCHAR(32))
RETURNS int(11)
NOT DETERMINISTIC
MODIFIES SQL DATA
BEGIN
UPDATE `core_sequence`
SET `next` = (@next := `next`) + `inc`
WHERE `name` = vname;

RETURN @next;
END ;;
DELIMITER ;

---------------------------------------------------------------

The general consensus seems to be that a function is replication safe if, given two identical master/slave database snapshots, the comparative state of the two databases are still identical after the function call.

Is my understanding correct? And either way will the above work in my environment?

Thanks heaps,
Josh

Options: ReplyQuote


Subject
Views
Written By
Posted
NOT DETERMINISTIC Function + Replication
4776
May 27, 2016 01:23AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.