View | Details | Raw Unified | Return to bug 7793
Collapse All | Expand All

(-)a/installer/data/mysql/kohastructure.sql (-1 / +1 lines)
Lines 2567-2573 CREATE TABLE `message_queue` ( Link Here
2567
  `to_address` mediumtext,
2567
  `to_address` mediumtext,
2568
  `from_address` mediumtext,
2568
  `from_address` mediumtext,
2569
  `content_type` text,
2569
  `content_type` text,
2570
  KEY `message_id` (`message_id`),
2570
  PRIMARY KEY `message_id` (`message_id`),
2571
  KEY `borrowernumber` (`borrowernumber`),
2571
  KEY `borrowernumber` (`borrowernumber`),
2572
  KEY `message_transport_type` (`message_transport_type`),
2572
  KEY `message_transport_type` (`message_transport_type`),
2573
  CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
2573
  CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
(-)a/installer/data/mysql/updatedatabase.pl (-1 / +37 lines)
Lines 10595-10600 if ( CheckVersion($DBversion) ) { Link Here
10595
    SetVersion($DBversion);
10595
    SetVersion($DBversion);
10596
}
10596
}
10597
10597
10598
$DBversion = "3.21.00.XXX";
10599
if ( CheckVersion($DBversion) ) {
10600
10601
    #First make sure that there are no duplicate message_id's which might be quite impossible thanks to the AUTO_INCREMENT.
10602
    #If duplicates exists, find the next free message_id and UPDATE the duplicate message_queues with the new biggest id's.
10603
    my $sthGetDuplicatePKs = $dbh->prepare("SELECT count(message_id) as duplicate_pks, message_id ".
10604
                            "FROM message_queue GROUP BY message_id HAVING duplicate_pks > 1;");
10605
#USE THIS TO TEST           "FROM message_queue GROUP BY message_id HAVING duplicate_pks > 0;");
10606
    my $sthGetMessage_queues = $dbh->prepare("SELECT * FROM message_queue WHERE message_id = ?");
10607
    my $sthGetBiggestMessageId = $dbh->prepare("SELECT max(message_id) as message_id FROM message_queue;");
10608
    my $sthUpdateMessageQueue = $dbh->prepare("UPDATE message_queue SET message_id = ? WHERE message_id = ? AND borrowernumber = ? AND time_queued = ? AND content = ?;");
10609
10610
    #Get duplicate message_ids
10611
    $sthGetDuplicatePKs->execute;
10612
    my $message_ids = $sthGetDuplicatePKs->fetchall_arrayref({});
10613
    foreach my $mi (@$message_ids) {
10614
        if ($mi->{duplicate_pks} > 1) { #Making sure there are multiple usages of this id
10615
#USE TO TEST  if ($mi->{duplicate_pks} > 0) { #Making sure we trigger this repair routine for messages with no duplicates, just to test.
10616
            $sthGetMessage_queues->execute($mi->{message_id}); #Get all the message_queues sharing the same id.
10617
            my $duplicates = $sthGetMessage_queues->fetchall_arrayref({});
10618
            my $firstRun = 1; #On the first replace run for this set of duplicates, preserve the original id.
10619
            foreach my $duplicate (@$duplicates) {
10620
                next if $firstRun-- == 1; #Preserve the original id for the first conflicting message_queue-row
10621
                $sthGetBiggestMessageId->execute();
10622
                my $max_message_id = $sthGetBiggestMessageId->fetchrow();
10623
                $max_message_id++;
10624
                $sthUpdateMessageQueue->execute( $max_message_id, $duplicate->{message_id}, $duplicate->{borrowernumber}, $duplicate->{time_queued}, $duplicate->{content} );
10625
            }
10626
        }
10627
    }
10628
10629
    #Finally add the PRIMARY KEY
10630
    $dbh->do( "ALTER TABLE message_queue DROP KEY message_id, ADD CONSTRAINT PRIMARY KEY (message_id);" );
10631
    print "Upgrade to $DBversion done (Bug 7793 - redefine the field message_id as PRIMARY KEY of message_queue)\n";
10632
    SetVersion ($DBversion);
10633
}
10634
10598
# DEVELOPER PROCESS, search for anything to execute in the db_update directory
10635
# DEVELOPER PROCESS, search for anything to execute in the db_update directory
10599
# SEE bug 13068
10636
# SEE bug 13068
10600
# if there is anything in the atomicupdate, read and execute it.
10637
# if there is anything in the atomicupdate, read and execute it.
10601
- 

Return to bug 7793