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

(-)a/installer/data/mysql/atomicupdate/bug-7793.sql (+14 lines)
Line 0 Link Here
1
ALTER TABLE message_queue DROP message_id;
2
3
ALTER TABLE message_queue ADD message_id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
4
5
-- $DBversion = "3.19.00.XXX";
6
-- if ( CheckVersion($DBversion) ) {
7
-- 
8
--     $dbh->do("ALTER TABLE message_queue DROP message_id" );
9
--     $dbh->do("ALTER TABLE message_queue ADD message_id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST");
10
-- 
11
--     print "Upgrade to $DBversion done (Bug 7793 - redefine the field message_id as PRIMARY KEY of message_queue)\n";
12
--     SetVersion ($DBversion);
13
-- }
14
(-)a/installer/data/mysql/updatedatabase.pl (-38 lines)
Lines 6762-6804 if ( CheckVersion($DBversion) ) { Link Here
6762
    SetVersion ($DBversion);
6762
    SetVersion ($DBversion);
6763
}
6763
}
6764
6764
6765
$DBversion = "3.19.00.XXX";
6766
if ( CheckVersion($DBversion) ) {
6767
6768
    #First make sure that there are no duplicate message_id's which might be quite impossible thanks to the AUTO_INCREMENT.
6769
    #If duplicates exists, find the next free message_id and UPDATE the duplicate message_queues with the new biggest id's.
6770
    my $sthGetDuplicatePKs = $dbh->prepare("SELECT count(message_id) as duplicate_pks, message_id ".
6771
                            "FROM message_queue GROUP BY message_id HAVING duplicate_pks > 1;");
6772
#USE THIS TO TEST           "FROM message_queue GROUP BY message_id HAVING duplicate_pks > 0;");
6773
    my $sthGetMessage_queues = $dbh->prepare("SELECT * FROM message_queue WHERE message_id = ?");
6774
    my $sthGetBiggestMessageId = $dbh->prepare("SELECT max(message_id) as message_id FROM message_queue;");
6775
    my $sthUpdateMessageQueue = $dbh->prepare("UPDATE message_queue SET message_id = ? WHERE message_id = ? AND borrowernumber = ? AND time_queued = ? AND content = ?;");
6776
6777
    #Get duplicate message_ids
6778
    $sthGetDuplicatePKs->execute;
6779
    my $message_ids = $sthGetDuplicatePKs->fetchall_arrayref({});
6780
    foreach my $mi (@$message_ids) {
6781
        if ($mi->{duplicate_pks} > 1) { #Making sure there are multiple usages of this id
6782
#USE TO TEST  if ($mi->{duplicate_pks} > 0) { #Making sure we trigger this repair routine for messages with no duplicates, just to test.
6783
            $sthGetMessage_queues->execute($mi->{message_id}); #Get all the message_queues sharing the same id.
6784
            my $duplicates = $sthGetMessage_queues->fetchall_arrayref({});
6785
            my $firstRun = 1; #On the first replace run for this set of duplicates, preserve the original id.
6786
            foreach my $duplicate (@$duplicates) {
6787
                next if $firstRun-- == 1; #Preserve the original id for the first conflicting message_queue-row
6788
                $sthGetBiggestMessageId->execute();
6789
                my $max_message_id = $sthGetBiggestMessageId->fetchrow();
6790
                $max_message_id++;
6791
                $sthUpdateMessageQueue->execute( $max_message_id, $duplicate->{message_id}, $duplicate->{borrowernumber}, $duplicate->{time_queued}, $duplicate->{content} );
6792
            }
6793
        }
6794
    }
6795
6796
    #Finally add the PRIMARY KEY
6797
    $dbh->do( "ALTER TABLE message_queue DROP KEY message_id, ADD CONSTRAINT PRIMARY KEY (message_id);" );
6798
    print "Upgrade to $DBversion done (Bug 7793 - redefine the field message_id as PRIMARY KEY of message_queue)\n";
6799
    SetVersion ($DBversion);
6800
}
6801
6802
$DBversion = "3.11.00.113";
6765
$DBversion = "3.11.00.113";
6803
if ( CheckVersion($DBversion) ) {
6766
if ( CheckVersion($DBversion) ) {
6804
    $dbh->do(q{
6767
    $dbh->do(q{
6805
- 

Return to bug 7793