From c7c8efa55c2e98058b9adc358ec5da1d31ee9f15 Mon Sep 17 00:00:00 2001 From: Kyle M Hall Date: Fri, 20 Mar 2015 08:15:41 -0400 Subject: [PATCH] [SIGNED-OFF] Bug 7793: Simplify db update Since message_id is not linked to anything else in the database, we can ensure the message_id's are unique by simply dropping the column and recreating it. Signed-off-by: Bernardo Gonzalez Kriegel Moved updatedatabase to atomicupdate First patch signed. No errors --- installer/data/mysql/atomicupdate/bug-7793.sql | 14 +++++++++ installer/data/mysql/updatedatabase.pl | 37 ------------------------ 2 files changed, 14 insertions(+), 37 deletions(-) create mode 100644 installer/data/mysql/atomicupdate/bug-7793.sql diff --git a/installer/data/mysql/atomicupdate/bug-7793.sql b/installer/data/mysql/atomicupdate/bug-7793.sql new file mode 100644 index 0000000..2a1891c --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug-7793.sql @@ -0,0 +1,14 @@ +ALTER TABLE message_queue DROP message_id; + +ALTER TABLE message_queue ADD message_id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST; + +-- $DBversion = "3.19.00.XXX"; +-- if ( CheckVersion($DBversion) ) { +-- +-- $dbh->do("ALTER TABLE message_queue DROP message_id" ); +-- $dbh->do("ALTER TABLE message_queue ADD message_id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST"); +-- +-- print "Upgrade to $DBversion done (Bug 7793 - redefine the field message_id as PRIMARY KEY of message_queue)\n"; +-- SetVersion ($DBversion); +-- } + diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 14fb8a0..2274251 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -6762,43 +6762,6 @@ if ( CheckVersion($DBversion) ) { SetVersion ($DBversion); } -$DBversion = "3.19.00.XXX"; -if ( CheckVersion($DBversion) ) { - - #First make sure that there are no duplicate message_id's which might be quite impossible thanks to the AUTO_INCREMENT. - #If duplicates exists, find the next free message_id and UPDATE the duplicate message_queues with the new biggest id's. - my $sthGetDuplicatePKs = $dbh->prepare("SELECT count(message_id) as duplicate_pks, message_id ". - "FROM message_queue GROUP BY message_id HAVING duplicate_pks > 1;"); -#USE THIS TO TEST "FROM message_queue GROUP BY message_id HAVING duplicate_pks > 0;"); - my $sthGetMessage_queues = $dbh->prepare("SELECT * FROM message_queue WHERE message_id = ?"); - my $sthGetBiggestMessageId = $dbh->prepare("SELECT max(message_id) as message_id FROM message_queue;"); - my $sthUpdateMessageQueue = $dbh->prepare("UPDATE message_queue SET message_id = ? WHERE message_id = ? AND borrowernumber = ? AND time_queued = ? AND content = ?;"); - - #Get duplicate message_ids - $sthGetDuplicatePKs->execute; - my $message_ids = $sthGetDuplicatePKs->fetchall_arrayref({}); - foreach my $mi (@$message_ids) { - if ($mi->{duplicate_pks} > 1) { #Making sure there are multiple usages of this id -#USE TO TEST if ($mi->{duplicate_pks} > 0) { #Making sure we trigger this repair routine for messages with no duplicates, just to test. - $sthGetMessage_queues->execute($mi->{message_id}); #Get all the message_queues sharing the same id. - my $duplicates = $sthGetMessage_queues->fetchall_arrayref({}); - my $firstRun = 1; #On the first replace run for this set of duplicates, preserve the original id. - foreach my $duplicate (@$duplicates) { - next if $firstRun-- == 1; #Preserve the original id for the first conflicting message_queue-row - $sthGetBiggestMessageId->execute(); - my $max_message_id = $sthGetBiggestMessageId->fetchrow(); - $max_message_id++; - $sthUpdateMessageQueue->execute( $max_message_id, $duplicate->{message_id}, $duplicate->{borrowernumber}, $duplicate->{time_queued}, $duplicate->{content} ); - } - } - } - - #Finally add the PRIMARY KEY - $dbh->do( "ALTER TABLE message_queue DROP KEY message_id, ADD CONSTRAINT PRIMARY KEY (message_id);" ); - print "Upgrade to $DBversion done (Bug 7793 - redefine the field message_id as PRIMARY KEY of message_queue)\n"; - SetVersion ($DBversion); -} - $DBversion = "3.11.00.113"; if ( CheckVersion($DBversion) ) { $dbh->do(q{ -- 1.7.9.5