Assigning RECNUM values in MySQL tables

Problem

A customer reported a problem synchronizing two MySQL databases. One database resides on the Web and the other database resides on a PC. The databases are updated independently and each uses the auto increment RECNUM column as a unique index. When a two-way synch is performed between the databases, duplicate records are flagged because records in each database were assigned the same RECNUM value.

Solution

Eliminate the auto increment option for RECNUM and assign odd RECNUM values for one environment and even for the other.

The example below uses the CUSTOMER table from the VDF Order example and assigns odd RECNUM values starting with 51.

1. Alter the SQL table to remove the auto increment option and add a default value to the RECNUM column

Procedure AlterTable

  sql_set_stmt to "ALTER TABLE customer CHANGE COLUMN recnum recnum BIGINT(20) NOT NULL"

  sql_prepare_stmt

  sql_execute_stmt

  sql_set_stmt to "ALTER TABLE customer CHANGE COLUMN recnum recnum BIGINT(20) NOT NULL DEFAULT '0'"

  sql_prepare_stmt

  sql_execute_stmt

End_Procedure

2. Create an SQL table that will work as a sequence

Procedure CreateSequence

  sql_set_stmt to "drop table IF EXISTS `sequences`"

  sql_prepare_stmt

  sql_execute_stmt

sql_set_stmt to "CREATE TABLE `sequences` ("
sql_append_stmt to "`name` varchar(30) NOT NULL,"
sql_append_stmt to "`value` int(10) unsigned DEFAULT '0',"
sql_append_stmt to "PRIMARY KEY (`name`)) "
sql_append_stmt to "ENGINE=InnoDB DEFAULT CHARSET=latin1"
sql_prepare_stmt
sql_execute_stmt

// We only want odd numbers starting with 51 for RECNUM
sql_set_stmt to "insert into `sequences` (name,value) values ('customer',51)"
sql_prepare_stmt
sql_execute_stmt
End_Procedure

3. Create a function that returns odd values

Procedure CreateFunctions

  sql_set_stmt to "DROP FUNCTION IF EXISTS `true_function`"

  sql_prepare_stmt

  sql_execute_stmt

sql_set_stmt to "create function true_function(p_param int) returns int "
sql_append_stmt to "deterministic "
sql_append_stmt to "sql security invoker "
sql_append_stmt to "return true"
sql_prepare_stmt
sql_execute_stmt

sql_set_stmt to "DROP FUNCTION IF EXISTS `get_next_value`"
sql_prepare_stmt
sql_execute_stmt

// We only want odd numbers starting with 51 for RECNUM
sql_set_stmt to "create function get_next_value(p_name varchar(30)) returns BIGINT(20) "
sql_append_stmt to "begin "
sql_append_stmt to " declare current_val BIGINT(20); "
sql_append_stmt to " update sequences "
sql_append_stmt to " set value = value + 2 "
sql_append_stmt to " where name = p_name "
sql_append_stmt to " and true_function((@current_val := sequences.value) is not null); "
sql_append_stmt to " return @current_val; "
sql_append_stmt to "end"
sql_prepare_stmt
sql_execute_stmt
End_Procedure

4. Create the trigger that fills the RECNUM column with the odd value from the function

Procedure CreateTrigger

  sql_set_stmt to "CREATE TRIGGER customer_dfseq BEFORE INSERT ON customer "

  sql_append_stmt to "FOR EACH ROW set NEW.recnum = get_next_value('customer')"

  sql_prepare_stmt

  sql_execute_stmt

End_Procedure

5. Test the new behavior inserting and retrieving values from the table

open "customer.int" as customer

zerofile customer

For iCount From 1 to 10
Clear customer
Move iCount to customer.customer_number
Move ('Name #' + Trim(iCount)) to customer.name
Saverecord customer
Loop

Clear customer
Repeat
Find Gt customer by Index.1
If (Found) Showln 'RECNUM: ' customer.recnum ' -> ' customer.customer_number '-' customer.name
Until (Not(Found))