Description:
GET_LOCK() in a UDF is not sequencing properly causing the function to work incorrectly. The UDF creates a new sequence record and new records are being duplicated.
I have provided the table configuration, UFD, and a shell script to simulate the problem. If you run the shell script you will find that the column 'file_number' will get a duplicate entry - but column 'id' will not be duplicated.
A duplicate entry of column 'file_number' should not happen. GET_LOCK() is called to prevent client collisions to this function yet the bash script will demonstrate that a duplication does occur leading to the conclusion there is a sequencing issue somewhere. It appears that the INSERT isn't being committed prior to the next client getting access to the lock and attempting to generate the next sequence.
Tested on:
Ubuntu 14.04.1 LTS x64
MySQL Server 5.6.20 x64 and 5.5.38 x64
How to repeat:
CREATE TABLE `lcl_file_number` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`file_number` varchar(50) NOT NULL,
`created_datetime` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `lcl_file_number_idx1` (`file_number`),
KEY `lcl_file_number_idx2` (`created_datetime`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
;
-- #######
-- insert initial starting record
INSERT INTO lcl_file_number ( file_number, created_datetime ) VALUES( 'A00000',NOW());
-- #######
DELIMITER $$
DROP FUNCTION IF EXISTS `LCLFileNumberCreate`$$
CREATE DEFINER=`root`@`%` FUNCTION `LCLFileNumberCreate`() RETURNS BIGINT(20) UNSIGNED
MODIFIES SQL DATA
MAIN: BEGIN
DECLARE mLockName VARCHAR(50) DEFAULT NULL;
DECLARE mLock BOOL DEFAULT FALSE;
DECLARE mFileNumberID BIGINT(20) UNSIGNED DEFAULT NULL;
DECLARE mFileNumber VARCHAR(50) DEFAULT NULL;
DECLARE mLetter VARCHAR(5) DEFAULT "";
DECLARE mLetterAsInt TINYINT UNSIGNED DEFAULT 0;
DECLARE mNumber INT(11) UNSIGNED DEFAULT 0;
SET mLockName = CONCAT( DATABASE() , ".LCLFileNumber" );
SET mLock = GET_LOCK( mLockName, 30 );
IF ISNULL( mLock) OR mLock = 0 THEN
RETURN NULL;
END IF;
SELECT
id, file_number
INTO
mFileNumberID, mFileNumber
FROM lcl_file_number
WHERE SUBSTR( file_number, 1, 1 ) BETWEEN 'A' AND 'Z'
AND SUBSTR( file_number, 1, 2 ) != 'ZZ'
ORDER BY id DESC
LIMIT 1;
IF ISNULL( mFileNumber ) OR mFileNumberID < 1 THEN
SET mLock = RELEASE_LOCK( mLockName );
RETURN NULL;
END IF;
SET mLetter = SUBSTR( mFileNumber, 1, 1 );
SET mNumber = SUBSTR( mFileNumber, 2 );
IF mNumber < 99999 THEN
SET mNumber = mNumber + 1;
ELSE
SET mLetterAsInt = ASCII( mLetter );
CASE mLetter
WHEN 'H' THEN SET mLetterAsInt = mLetterAsInt + 2;
WHEN 'K' THEN SET mLetterAsInt = mLetterAsInt + 2;
WHEN 'N' THEN SET mLetterAsInt = mLetterAsInt + 2;
WHEN 'Z' THEN SET mLetterAsInt = ASCII( 'A' );
ELSE SET mLetterAsInt = mLetterAsInt + 1;
END CASE;
SET mLetter = CHAR( mLetterAsInt );
SET mNumber = 1;
END IF;
SET mFileNumber = CONCAT( mLetter, RIGHT( CONCAT( "00000", mNumber ), 5 ) );
INSERT INTO lcl_file_number ( file_number, created_datetime ) VALUES( mFileNumber, NOW() );
SET mFileNumberID = LAST_INSERT_ID();
SET mLock = RELEASE_LOCK( mLockName );
IF ISNULL( mFileNumberID ) OR mFileNumberID < 1 THEN
SET mFileNumberID = NULL;
END IF;
RETURN mFileNumberID;
END MAIN$$
DELIMITER;
Suggested fix:
(shell script to test function)
#!/bin/bash
# NOTE: the ampersand after the database name - VERY IMPORTANT.
#
for ((i=1; i<= 100; i++));
do
mysql \
--host=192.168.56.50 \
--user=root \
-e "SELECT LCLFileNumberCreate()" \
test &
done
#
# (end of file)
Description: GET_LOCK() in a UDF is not sequencing properly causing the function to work incorrectly. The UDF creates a new sequence record and new records are being duplicated. I have provided the table configuration, UFD, and a shell script to simulate the problem. If you run the shell script you will find that the column 'file_number' will get a duplicate entry - but column 'id' will not be duplicated. A duplicate entry of column 'file_number' should not happen. GET_LOCK() is called to prevent client collisions to this function yet the bash script will demonstrate that a duplication does occur leading to the conclusion there is a sequencing issue somewhere. It appears that the INSERT isn't being committed prior to the next client getting access to the lock and attempting to generate the next sequence. Tested on: Ubuntu 14.04.1 LTS x64 MySQL Server 5.6.20 x64 and 5.5.38 x64 How to repeat: CREATE TABLE `lcl_file_number` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `file_number` varchar(50) NOT NULL, `created_datetime` datetime NOT NULL, PRIMARY KEY (`id`), KEY `lcl_file_number_idx1` (`file_number`), KEY `lcl_file_number_idx2` (`created_datetime`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ; -- ####### -- insert initial starting record INSERT INTO lcl_file_number ( file_number, created_datetime ) VALUES( 'A00000',NOW()); -- ####### DELIMITER $$ DROP FUNCTION IF EXISTS `LCLFileNumberCreate`$$ CREATE DEFINER=`root`@`%` FUNCTION `LCLFileNumberCreate`() RETURNS BIGINT(20) UNSIGNED MODIFIES SQL DATA MAIN: BEGIN DECLARE mLockName VARCHAR(50) DEFAULT NULL; DECLARE mLock BOOL DEFAULT FALSE; DECLARE mFileNumberID BIGINT(20) UNSIGNED DEFAULT NULL; DECLARE mFileNumber VARCHAR(50) DEFAULT NULL; DECLARE mLetter VARCHAR(5) DEFAULT ""; DECLARE mLetterAsInt TINYINT UNSIGNED DEFAULT 0; DECLARE mNumber INT(11) UNSIGNED DEFAULT 0; SET mLockName = CONCAT( DATABASE() , ".LCLFileNumber" ); SET mLock = GET_LOCK( mLockName, 30 ); IF ISNULL( mLock) OR mLock = 0 THEN RETURN NULL; END IF; SELECT id, file_number INTO mFileNumberID, mFileNumber FROM lcl_file_number WHERE SUBSTR( file_number, 1, 1 ) BETWEEN 'A' AND 'Z' AND SUBSTR( file_number, 1, 2 ) != 'ZZ' ORDER BY id DESC LIMIT 1; IF ISNULL( mFileNumber ) OR mFileNumberID < 1 THEN SET mLock = RELEASE_LOCK( mLockName ); RETURN NULL; END IF; SET mLetter = SUBSTR( mFileNumber, 1, 1 ); SET mNumber = SUBSTR( mFileNumber, 2 ); IF mNumber < 99999 THEN SET mNumber = mNumber + 1; ELSE SET mLetterAsInt = ASCII( mLetter ); CASE mLetter WHEN 'H' THEN SET mLetterAsInt = mLetterAsInt + 2; WHEN 'K' THEN SET mLetterAsInt = mLetterAsInt + 2; WHEN 'N' THEN SET mLetterAsInt = mLetterAsInt + 2; WHEN 'Z' THEN SET mLetterAsInt = ASCII( 'A' ); ELSE SET mLetterAsInt = mLetterAsInt + 1; END CASE; SET mLetter = CHAR( mLetterAsInt ); SET mNumber = 1; END IF; SET mFileNumber = CONCAT( mLetter, RIGHT( CONCAT( "00000", mNumber ), 5 ) ); INSERT INTO lcl_file_number ( file_number, created_datetime ) VALUES( mFileNumber, NOW() ); SET mFileNumberID = LAST_INSERT_ID(); SET mLock = RELEASE_LOCK( mLockName ); IF ISNULL( mFileNumberID ) OR mFileNumberID < 1 THEN SET mFileNumberID = NULL; END IF; RETURN mFileNumberID; END MAIN$$ DELIMITER; Suggested fix: (shell script to test function) #!/bin/bash # NOTE: the ampersand after the database name - VERY IMPORTANT. # for ((i=1; i<= 100; i++)); do mysql \ --host=192.168.56.50 \ --user=root \ -e "SELECT LCLFileNumberCreate()" \ test & done # # (end of file)