Register to post in forums, or Log in to your existing account
 

Play RetroMUD
Post new topic  Reply to topic     Home » Forums » CMUD Beta Forum
ReedN
Wizard


Joined: 04 Jan 2006
Posts: 1279
Location: Portland, Oregon
PostPosted: Tue Oct 05, 2010 2:41 pm   

[3.30] [Resolved] Serious Map DB Corruption of the NoteId field
 
There is some corruption in my map file (specifically the NoteId fields) that occurred in one of the latest betas and perhaps even the latest version.

Problem: I had gotten a crash from Cmud when mapping new rooms telling me "SQL Error: PRIMARY KEY must be unique."

Zugg e-mailed me saying I might want to run the repair option of the map conversion utility on my file. When I did it found that there were many Room Note's with the same database key. Browsing my database with a viewer I found that there were 9 entry's with "1" as the key, 8 entries with '2' as the key, etc.

I've noted the following as I've investigated:
1) This has happened very recently. I ran the repair on a map I had archived and it didn't find any issues. So this has definitely been caused in the last few months.
2) The rooms affected are rooms that I've been recently remapping with the new GMCP data. This is more evidence a recent version has done this.
3) The Map Conversion Utility detects the errors, but doesn't correctly repair them. The repair discards the entries that are duplicate instead of renaming them to a unique entry.

The error seems to be that the pointer for next key value for NoteId resets each time I start Cmud (it starts advancing from 1 each restart). When I create new rooms after a restart it gives it id 1 then 2, then 3, etc. Then I restart Cmud and it begins at 1 all over again. I've very concerned that this issue still exists in the latest version since nothing has been called out earlier regarding this.

It might not be a bad idea for some of the others who have used the new GMCP to start remapping to run their map through the conversion utility to see if they detect any errors. If my reasoning is correct you'll see this if you've added new rooms and use the roomnote field.

Since the Conversion Utility doesn't repair this I'm at in a quandary at what to do. I could tediously repair this by hand or I might write a script to attempt to do this. The overhanging gloom about either prospect is that it may reoccur at any time until addressed.


Last edited by ReedN on Fri Oct 15, 2010 6:20 pm; edited 3 times in total		 Reply with quote
ReedN
Wizard


Joined: 04 Jan 2006
Posts: 1279
Location: Portland, Oregon
PostPosted: Tue Oct 05, 2010 2:54 pm   
 
I've done a little more research in my map database and I've found that indeed the new key pointer isn't correct for the NoteTbl. The VersTbl field has the next new key number for each of the fields. All my other fields are correctly set to point to the next new object. Only for the NoteTbl field pointer do I see that it is pointing to key 1.

This incorrect pointer will cause a new roomnote to be assigned key 1 and then key 2 causing the type of issue I've observed.
Reply with quote
Zugg
MASTER


Joined: 25 Sep 2000
Posts: 23379
Location: Colorado, USA
PostPosted: Tue Oct 05, 2010 3:58 pm   
 
Thanks for checking more into this ReedN. From the crash dump there wasn't any way for me to tell *which* field had the primary key problem. Narrowing it down to the NoteTbl is a great help. I'll add that to the bug list and see what I can do with it.
Reply with quote
ReedN
Wizard


Joined: 04 Jan 2006
Posts: 1279
Location: Portland, Oregon
PostPosted: Tue Oct 05, 2010 5:24 pm   
 
I'll be interested to see how wide-spread this is. I haven't done anything exotic in my scripts besides start to use GMCP and the Cmud built-in functions for altering the RoomNote, RoomFlag, etc, so I'd be willing to bet there are others with this issue as well.

If I were to posit a cause from what I can observe I'd say that something in the new GMCP code is causing the VersTbl's NoteTbl pointer to be reset to 1 each time Cmud starts. It's about the only thing that I can think of that fits both my actions (mapping several new rooms in existing zones over the course of various Cmud restarts) and the data (repeated sequential numbering starting at 1 each time).

Thanks for looking into this and be sure to let me know if you need anything else from my end.
Reply with quote
ReedN
Wizard


Joined: 04 Jan 2006
Posts: 1279
Location: Portland, Oregon
PostPosted: Sun Oct 10, 2010 1:58 am   
 
I've been updating my Perl map fix script to repair this damage.

I've noticed something odd in the difference between the %roomload values internal to Cmud and the values in the Flags field inside the database:

Code:
        # Bit Description         Cmud_Int_Bit Database_Bit Database_Value
        # Reload description           2           4           8
        # Reload exits                 3           5           16
        # Reload name                  1           6           32
        # MUD Room Number              4           11          1024


It's interesting that internal to Cmud the bits used are 1, 2, 3, and 4 for the fields while the actual database uses bits 6, 4, 5, and 11 respectively.

Why the difference internal vs. external? I didn't see any of the other bits being used for anything else that I could notice, so I don't see the need for the bit reordering.
Reply with quote
Zugg
MASTER


Joined: 25 Sep 2000
Posts: 23379
Location: Colorado, USA
PostPosted: Sun Oct 10, 2010 3:54 pm   
 
That is correct. The database Flags field has several other internal flag values that I didn't want people messing with. The %roomload just exposes the "useful" flags.
Reply with quote
ReedN
Wizard


Joined: 04 Jan 2006
Posts: 1279
Location: Portland, Oregon
PostPosted: Mon Oct 11, 2010 1:24 am   
 
I've updated my script to look for the new and nefarious map errors and found the following errors.

Code:
   Summary of: Achaea.dbm
==================================================
      Zone: Quantity =    217, Largest Id =    231 (   14 delta), NextPointer=   232 (Correct)
    Object: Quantity =  23255, Largest Id =  23431 (  176 delta), NextPointer= 23432 (Correct)
      Note: Quantity =  23221, Largest Id =  27715 ( 4494 delta), NextPointer=     1 (Error)
      Exit: Quantity =  88667, Largest Id =  89292 (  625 delta), NextPointer= 89293 (Correct)

            Check Zone Key Uniqueness:    217 /    217 completed (     0 corrected,   0 seconds)
          Check Object Key Uniqueness:  23255 /  23255 completed (     0 corrected,   0 seconds)
            Check Note Key Uniqueness:  23221 /  23221 completed (   126 corrected,   0 seconds)
            Check Exit Key Uniqueness:  88667 /  88667 completed (     0 corrected,   0 seconds)
   Check that ZoneID in DrawTbl Exist:     88 /     88 completed (     0 corrected,   0 seconds)
    Check that ObjID in NoteTbl Exist:  23221 /  23221 completed (     2 corrected,   1 seconds)
   Check that Parent in ZoneTbl Exist:    217 /    217 completed (     0 corrected,   0 seconds)
     Check ObjectTbl for Valid RefNum:  23255 /  23255 completed (    11 corrected,   0 seconds)
     Check ObjectTbl for Valid IconID:  23255 /  23255 completed (     0 corrected,   0 seconds)
 Check that ZoneID in ObjectTbl Exist:  23255 /  23255 completed (     0 corrected,   1 seconds)
          Check Exit Object Integrity:  88667 /  88667 completed (     1 corrected,   4 seconds)
            Check Exit Stub Integrity:  88667 /  88667 completed (     6 corrected,   0 seconds)
      Check ExitTbl for Valid DirType:  88667 /  88667 completed (     2 corrected,   0 seconds)
          Check Exit Return Integrity:  88665 /  88667 completed (    46 corrected,   5 seconds)

 Renumbering VersTbl: Done

   Summary of: Achaea_new.dbm
==================================================
      Zone: Quantity =    217, Largest Id =    231 (   14 delta), NextPointer=   232 (Correct)
    Object: Quantity =  23255, Largest Id =  23431 (  176 delta), NextPointer= 23432 (Correct)
      Note: Quantity =  23220, Largest Id =  27841 ( 4621 delta), NextPointer= 27842 (Correct)
      Exit: Quantity =  88665, Largest Id =  89291 (  626 delta), NextPointer= 89292 (Correct)

 Commit Database: Done
 Vacuum Database: Done


I found that the NoteTbl was the only table prone to getting its pointer set back to 1 and creating duplicate keys. I searched for like corruption in the other tables, even inducing this error in the ObjectTbl, ExitTbl, and ZoneTbl to see what would happen. Cmud auto-corrected for it in all cases except for the NoteTbl.

Your Mapconvert program's repair will detect the duplicate keys, but it will not correct them. When I ran my map through it with duplicate keys it simply discarded the duplicates, which I didn't consider optimal. I used my script to make the duplicates unique by using a secondary identification (ObjID) to differentiate them.

Besides searching and fixing 13 different types of map corruption I added some other items such as making the renumbering optional, accounting for Vnums, and greatly reducing the runtime to around 20 seconds on my computer. I'll post the code so you can compare with your own maps. It makes a copy of the map before doing anything so it is safe to run directly on a map.


Last edited by ReedN on Wed Oct 13, 2010 8:03 am; edited 1 time in total		 Reply with quote
ReedN
Wizard


Joined: 04 Jan 2006
Posts: 1279
Location: Portland, Oregon
PostPosted: Mon Oct 11, 2010 1:25 am   
 
Code:

#!c:\perl\bin\perl

# Unbuffer STDOUT
$| = 1;

# Usage
$usage = "
$0 <db_filename> [-renumber] [-keepvnum] [-h]

Checks CMud Maps for errors and consistency.  Optionally can renumber the keys so they are in a more logical and contiguous ordering.

-renumber     Renumbers the keys of the ObjectTbl, NoteTbl, ExitTbl, and ZoneTbl.
-keepvnum     Do not change VNum values when reordering.

";

use DBI;
use File::Copy;
use Getopt::Long;

# Options Handling
$Getopt::Long::ignorecase = 1;
$Getopt::Long::autoabbrev = 1;
&GetOptions(
            "renumber"  => \$renumber,
            "keepvnum"  => \$keepvnum,
            "h|help"
           );
if ($opt_h or $opt_h) { print $usage; exit 0; }

$filename = @ARGV[0];
$new_file = $filename;
$new_file =~ s/\./_new./;
copy($filename, $new_file);

$dbh = DBI->connect("dbi:SQLite:$new_file", "", "", {
       PrintError => 0,
       AutoCommit => 0
    }) || die "Cannot connect: $DBI::errstr";

$dbh->do("PRAGMA cache_size = 25000");
$dbh->do("PRAGMA synchronous = OFF");
$dbh->do("PRAGMA journal_mode = OFF");
$dbh->do("PRAGMA locking_mode = EXCLUSIVE");
$dbh->do("PRAGMA temp_store = MEMORY");

open LOG, ">Processing_log.txt" or die "Cannot open Processing_log.txt\n";

($largest_zone_id, $num_zone_records) = &get_stats("ZoneId", "ZoneTbl");
($largest_object_id, $num_object_records) = &get_stats("ObjId", "ObjectTbl");
($largest_note_id, $num_note_records) = &get_stats("NoteId", "NoteTbl");
($largest_exit_id, $num_exit_records) = &get_stats("ExitId", "ExitTbl");
($ZonePtr, $ObjPtr, $NotePtr, $ExitPtr) = &dereference_array($dbh->selectall_arrayref("SELECT ZoneID, ObjID, NoteID, ExitID FROM VersTbl WHERE VersId = 1"));

@zoneId_array = &dereference_array($dbh->selectall_arrayref('SELECT ZoneId FROM ZoneTbl ORDER BY Name ASC'));
foreach (@zoneId_array) { $zoneId{$_}++;}
@objId_array = &dereference_array($dbh->selectall_arrayref('SELECT ObjId FROM ObjectTbl ORDER BY ZoneId ASC'));
foreach (@objId_array) { $objId{$_}++;}
@noteId_array = &dereference_array($dbh->selectall_arrayref('SELECT NoteId FROM NoteTbl ORDER BY ObjID ASC'));
foreach (@noteId_array) { $noteId{$_}++;}
@exitId_array = &dereference_array($dbh->selectall_arrayref('SELECT ExitId FROM ExitTbl ORDER BY FromID ASC'));
foreach (@exitId_array) { $exitId{$_}++;}



print "\n   Summary of: $filename\n";
print "==================================================\n";
&print_stats("Zone",   $largest_zone_id,   $num_zone_records,   $ZonePtr);
&print_stats("Object", $largest_object_id, $num_object_records, $ObjPtr);
&print_stats("Note",   $largest_note_id,   $num_note_records,   $NotePtr);
&print_stats("Exit",   $largest_exit_id,   $num_exit_records,   $ExitPtr);


#######
####### Start Integrity Checks
#######

###
### Zone Key Uniqueness
###
### This hasn't been an issue, but better to check.
$total_operations = $num_zone_records;
$progress = 0;
$corrections = 0;
$progress_phrase = "Check Zone Key Uniqueness";
$start_time = time();
printf("\n %36s: %6i / %6i completed (%6i corrected)",$progress_phrase, $progress, $total_operations, $corrections);
foreach (sort {$zoneId{$b} <=> $zoneId{$a}} keys %zoneId)
{
    # Sorting decending order of multiple keys.  Discontinue loop when I reach 1 key per number.
    last if ($zoneId{$_} == 1);
   
    my @line1_array = &dereference_array($dbh->selectall_arrayref("SELECT Name FROM ZoneTbl WHERE ZoneId = $_"));
    foreach $duplicate (@line1_array)
    {
        $largest_zone_id++;
        $dbh->do("UPDATE ZoneTbl SET ZoneId = $largest_zone_id WHERE ZoneId = $_ and Name = $duplicate");
        print LOG "Zone Key Uniqueness: In ZoneTbl, changing ZoneId ($_) Name ($duplicate) to ZoneId ($largest_zone_id) because it wasn't unique.\n";
        $zoneId{$largest_zone_id}++;
        $corrections++;
    }
    delete $zoneId{$_};
    printf("\r %36s: %6i / %6i completed (%6i corrected)",$progress_phrase, ++$progress, $total_operations, $corrections);
}
printf("\r %36s: %6i / %6i completed (%6i corrected, %3i seconds)",$progress_phrase, $total_operations, $total_operations, $corrections, time()-$start_time);
# Refresh Zone List after changes
($largest_zone_id, $num_zone_records) = &get_stats("ZoneId", "ZoneTbl");
@zoneId_array = &dereference_array($dbh->selectall_arrayref('SELECT ZoneId FROM ZoneTbl ORDER BY Name ASC'));


###
### Object Key Uniqueness
###
### This hasn't been an issue, but better to check.
$total_operations = $num_object_records;
$progress = 0;
$corrections = 0;
$progress_phrase = "Check Object Key Uniqueness";
$start_time = time();
printf("\n %36s: %6i / %6i completed (%6i corrected)",$progress_phrase, $progress, $total_operations, $corrections);
foreach (sort {$objId{$b} <=> $objId{$a}} keys %objId)
{
    # Sorting decending order of multiple keys.  Discontinue loop when I reach 1 key per number.
    last if ($objId{$_} == 1);
   
    my @line1_array = &dereference_array($dbh->selectall_arrayref("SELECT Name FROM ObjectTbl WHERE ObjId = $_"));
    foreach $duplicate (@line1_array)
    {
        $largest_object_id++;
        $dbh->do("UPDATE ObjectTbl SET ObjId = $largest_object_id WHERE ObjId = $_ and Name = $duplicate");
        print LOG "Object Key Uniqueness: In ObjectTbl, changing ObjId ($_) Name ($duplicate) to ObjId ($largest_object_id) because it wasn't unique.\n";
        $objId{$largest_object_id}++;
        $corrections++;
    }
    delete $objId{$_};
    printf("\r %36s: %6i / %6i completed (%6i corrected)",$progress_phrase, ++$progress, $total_operations, $corrections);
}
printf("\r %36s: %6i / %6i completed (%6i corrected, %3i seconds)",$progress_phrase, $total_operations, $total_operations, $corrections, time()-$start_time);
# Refresh Object List after changes
($largest_object_id, $num_object_records) = &get_stats("ObjId", "ObjectTbl");
@objId_array = &dereference_array($dbh->selectall_arrayref('SELECT ObjId FROM ObjectTbl ORDER BY ZoneId ASC'));


###
### Note Key Uniqueness
###
### Saw a lot of these errors around v3.30
$total_operations = $num_note_records;
$progress = 0;
$corrections = 0;
$progress_phrase = "Check Note Key Uniqueness";
$start_time = time();
printf("\n %36s: %6i / %6i completed (%6i corrected)",$progress_phrase, $progress, $total_operations, $corrections);
foreach (sort {$noteId{$b} <=> $noteId{$a}} keys %noteId)
{
    # Sorting decending order of multiple keys.  Discontinue loop when I reach 1 key per number.
    last if ($noteId{$_} == 1);
   
    my @line1_array = &dereference_array($dbh->selectall_arrayref("SELECT ObjID FROM NoteTbl WHERE NoteId = $_"));
    foreach $duplicate (@line1_array)
    {
        $largest_note_id++;
        $dbh->do("UPDATE NoteTbl SET NoteId = $largest_note_id WHERE NoteId = $_ and ObjID = $duplicate");
        print LOG "Note Key Uniqueness: In NoteTbl, changing NoteId ($_) ObjID ($duplicate) to NoteId ($largest_note_id) because it wasn't unique.\n";
        $noteId{$largest_note_id}++;
        $corrections++;
    }
    delete $noteId{$_};
    printf("\r %36s: %6i / %6i completed (%6i corrected)",$progress_phrase, ++$progress, $total_operations, $corrections);
}
printf("\r %36s: %6i / %6i completed (%6i corrected, %3i seconds)",$progress_phrase, $total_operations, $total_operations, $corrections, time()-$start_time);
# Refresh Note List after changes
($largest_note_id, $num_note_records) = &get_stats("NoteId", "NoteTbl");
@noteId_array = &dereference_array($dbh->selectall_arrayref('SELECT NoteId FROM NoteTbl ORDER BY ObjID ASC'));


###
### Exit Key Uniqueness
###
### This hasn't been an issue, but better to check.
$total_operations = $num_exit_records;
$progress = 0;
$corrections = 0;
$progress_phrase = "Check Exit Key Uniqueness";
$start_time = time();
printf("\n %36s: %6i / %6i completed (%6i corrected)",$progress_phrase, $progress, $total_operations, $corrections);
foreach (sort {$exitId{$b} <=> $exitId{$a}} keys %exitId)
{
    # Sorting decending order of multiple keys.  Discontinue loop when I reach 1 key per number.
    last if ($exitId{$_} == 1);
   
    my @line1_array = &dereference_array($dbh->selectall_arrayref("SELECT ExitIdTo FROM ExitTbl WHERE ExitId = $_"));
    foreach $duplicate (@line1_array)
    {
        $largest_exit_id++;
        $dbh->do("UPDATE ExitTbl SET ExitId = $largest_exit_id WHERE ExitId = $_ and ExitIdTo = $duplicate");
        print LOG "Exit Key Uniqueness: In ExitTbl, changing ExitId ($_) ExitIdTo ($duplicate) to ExitId ($largest_exit_id) because it wasn't unique.\n";
        $exitId{$largest_exit_id}++;
        $corrections++;
    }
    delete $exitId{$_};
    printf("\r %36s: %6i / %6i completed (%6i corrected)",$progress_phrase, ++$progress, $total_operations, $corrections);
}
printf("\r %36s: %6i / %6i completed (%6i corrected, %3i seconds)",$progress_phrase, $total_operations, $total_operations, $corrections, time()-$start_time);
# Refresh Exit List after changes
($largest_exit_id, $num_exit_records) = &get_stats("ExitId", "ExitTbl");
@exitId_array = &dereference_array($dbh->selectall_arrayref('SELECT ExitId FROM ExitTbl ORDER BY FromID ASC'));

###
### DrawTbl ZoneId Validity Check
### In DrawTbl, check each DrawId to make sure ZoneId is a valid zone.
###
($largest_draw_id, $num_draw_records) = &get_stats("DrawId", "DrawTbl");
@drawId_array = &dereference_array($dbh->selectall_arrayref('SELECT DrawId FROM DrawTbl ORDER BY DrawId ASC'));
$total_operations = $num_draw_records;
$progress = 0;
$corrections = 0;
$progress_phrase = "Check that ZoneID in DrawTbl Exist";
$start_time = time();
printf("\n %36s: %6i / %6i completed (%6i corrected)",$progress_phrase, $progress, $total_operations, $corrections);
foreach (@drawId_array) {
    my @line1_array = &dereference_array($dbh->selectall_arrayref("SELECT ZoneID FROM DrawTbl WHERE DrawId = $_"));
   
    if (not exists($zoneId{$line1_array[0]})) {
        print LOG "DrawTbl Zone Integrity: Deleting DrawId ($_) from DrawTbl because it's zone isn't a valid zone ($line1_array[0]).\n";
        $dbh->do("DELETE FROM DrawTbl WHERE DrawId = $_");
        delete $drawId{$_};
        $corrections++;
    }
    printf("\r %36s: %6i / %6i completed (%6i corrected, %3i seconds)",$progress_phrase, ++$progress, $total_operations, $corrections, time()-$start_time);
}
# Refresh Draw List after changes
($largest_draw_id, $num_draw_records) = &get_stats("ZoneId", "ZoneTbl");
@drawId_array = &dereference_array($dbh->selectall_arrayref('SELECT DrawId FROM DrawTbl ORDER BY DrawId ASC'));


###
### NoteTbl Object Validity Check
### In NoteTbl, check ObjId is a valid room for each NoteId.
###
$total_operations = $num_note_records;
$progress = 0;
$corrections = 0;
$progress_phrase = "Check that ObjID in NoteTbl Exist";
$start_time = time();
printf("\n %36s: %6i / %6i completed (%6i corrected)",$progress_phrase, $progress, $total_operations, $corrections);
@note_check = &dereference_array($dbh->selectall_arrayref("SELECT NoteId, ObjID FROM NoteTbl"));
for (my $x=0; $x <= $#note_check; $x+=2)
{
    my $tmp_noteid = $note_check[$x];
    my $tmp_objid  = $note_check[$x+1];
    if (not exists($objId{$tmp_objid}))
    {
        print LOG "NoteTbl Object Integrity: Deleting NoteId ($tmp_noteid) from NoteTbl because it's Object isn't a valid room ($tmp_objid).\n";
        if ($tmp_noteid eq "")
        {
            $dbh->do("DELETE FROM NoteTbl WHERE NoteId is NULL");
        }
        else
        {
            $dbh->do("DELETE FROM NoteTbl WHERE NoteId = $tmp_noteid");           
        }
        delete $NoteId{$tmp_noteid};
        $corrections++;
    }
    printf("\r %36s: %6i / %6i completed (%6i corrected, %3i seconds)",$progress_phrase, ++$progress, $total_operations, $corrections, time()-$start_time);
}
undef @note_check;
# Refresh Note List after changes
($largest_note_id, $num_note_records) = &get_stats("NoteId", "NoteTbl");
@noteId_array = &dereference_array($dbh->selectall_arrayref('SELECT NoteId FROM NoteTbl ORDER BY ObjID ASC'));


###
### Zone Parent Integrity
###
$total_operations = $num_zone_records;
$progress = 0;
$corrections = 0;
$progress_phrase = "Check that Parent in ZoneTbl Exist";
$start_time = time();
printf("\n %36s: %6i / %6i completed (%6i corrected)",$progress_phrase, $progress, $total_operations, $corrections);
foreach (@zoneId_array) {
    my @line1_array = &dereference_array($dbh->selectall_arrayref("SELECT Parent FROM ZoneTbl WHERE ZoneId = $_"));
   
    if ($line1_array[0] != -1 and not exists($zoneId{$line1_array[0]})) {
        print LOG "Zone Parent Integrity: Deleting ZoneId ($_) from ZoneTbl because it's parent isn't a valid zone ($line1_array[0]).\n";
        $dbh->do("DELETE FROM ZoneTbl WHERE ZoneId = $_");
        delete $zoneId{$_};
        $corrections++;
    }
    printf("\r %36s: %6i / %6i completed (%6i corrected, %3i seconds)",$progress_phrase, ++$progress, $total_operations, $corrections, time()-$start_time);
}
# Refresh Zone List after changes
($largest_zone_id, $num_zone_records) = &get_stats("ZoneId", "ZoneTbl");
@zoneId_array = &dereference_array($dbh->selectall_arrayref('SELECT ZoneId FROM ZoneTbl ORDER BY Name ASC'));

###
### Object RefNum is Zero
###
$total_operations = $num_object_records;
$progress = $num_object_records;
$corrections = 0;
$progress_phrase = "Check ObjectTbl for Valid RefNum";
$start_time = time();
@refnum_array = &dereference_array($dbh->selectall_arrayref("SELECT ObjId FROM ObjectTbl WHERE RefNum = 0"));
foreach (@refnum_array) {
    print LOG "Object invalid RefNum: In ObjectTbl, setting RefNum (VRef) from 0 to $_ for ObjId ($_).\n";
    $dbh->do("UPDATE ObjectTbl SET RefNum = $_ WHERE ObjId = $_");
}
$corrections = $#refnum_array+1;
undef @refnum_array;
printf("\n %36s: %6i / %6i completed (%6i corrected, %3i seconds)",$progress_phrase, $progress, $total_operations, $corrections, time()-$start_time);

###
### Object IconID Invalid
###
$total_operations = $num_object_records;
$progress = $num_object_records;
$corrections = 0;
$progress_phrase = "Check ObjectTbl for Valid IconID";
$start_time = time();
@iconid_array = &dereference_array($dbh->selectall_arrayref("SELECT ObjId FROM ObjectTbl WHERE IconID is NULL"));
foreach (@iconid_array) {
    print LOG "Object IconID Check: Deleting ObjId ($_) from ObjectTbl because it is has a NULL IconID.\n";
    delete $objId{$_};
}
$corrections = $#iconid_array+1;
$dbh->do("DELETE FROM ObjectTbl WHERE IconID is NULL");
undef @iconid_array;
printf("\n %36s: %6i / %6i completed (%6i corrected, %3i seconds)",$progress_phrase, $progress, $total_operations, $corrections, time()-$start_time);
# Refresh Object List after changes
($largest_object_id, $num_object_records) = &get_stats("ObjId", "ObjectTbl");
@objId_array = &dereference_array($dbh->selectall_arrayref('SELECT ObjId FROM ObjectTbl ORDER BY ZoneId ASC'));

###
### Object Zone Integrity
###
$total_operations = $num_object_records;
$progress = 0;
$corrections = 0;
$progress_phrase = "Check that ZoneID in ObjectTbl Exist";
$start_time = time();
printf("\n %36s: %6i / %6i completed (%6i corrected)",$progress_phrase, $progress, $total_operations, $corrections);
@object_check = &dereference_array($dbh->selectall_arrayref("SELECT ObjId, ZoneID FROM ObjectTbl"));
for (my $x=0; $x <= $#object_check; $x+=2)
{
    my $tmp_objid = $object_check[$x];
    my $tmp_zoneid = $object_check[$x+1];
    if (not exists($zoneId{$tmp_zoneid}))
    {
        print LOG "Object Zone Integrity: Deleting ObjId ($tmp_objid) from ObjectTbl because it doesn't belong to a valid zone ($tmp_zoneid).\n";
        $dbh->do("DELETE FROM ObjectTbl WHERE ObjId = $tmp_objid");
        $corrections++;
        delete $objId{$tmp_objid};
    }
    printf("\r %36s: %6i / %6i completed (%6i corrected, %3i seconds)",$progress_phrase, ++$progress, $total_operations, $corrections, time()-$start_time);
}
undef @object_check;
# Refresh Object List after changes
($largest_object_id, $num_object_records) = &get_stats("ObjId", "ObjectTbl");
@objId_array = &dereference_array($dbh->selectall_arrayref('SELECT ObjId FROM ObjectTbl ORDER BY ZoneId ASC'));


###
### Exit Object Integrity
## ExitTbl:
## ExitId = Unique Exit Key      ExitIdTo = Destination Exit Key
## FromID = From ObjectTbl Key   ToID = To ObjectTbl Key
##
## ExitId ExitIdTo FromID ToID
## 19     -1       2947   2947  (Exit Stub)
## 2      4        2943   3366  (Normal Exit)
## 4      2        3366   2943  (Normal Exit)
###
$total_operations = $num_exit_records;
$progress = 0;
$corrections = 0;
$progress_phrase = "Check Exit Object Integrity";
$start_time = time();
printf("\n %36s: %6i / %6i completed (%6i corrected)",$progress_phrase, $progress, $total_operations, $corrections);
@exit_check = &dereference_array($dbh->selectall_arrayref("SELECT ExitId, FromID, ToID FROM ExitTbl"));
for (my $x=0; $x <= $#exit_check; $x+=3)
{
    my $tmp_exitid = $exit_check[$x];
    my $tmp_fromid = $exit_check[$x+1];
    my $tmp_toid   = $exit_check[$x+2];
    if (not exists($objId{$tmp_fromid})) {
        print LOG "Exit Object Integrity: Deleting ExitId ($tmp_exitid) because the FromID Object $tmp_fromid doesn't exist.\n";
        $dbh->do("DELETE FROM ExitTbl WHERE ExitId = $tmp_exitid");
        delete $exitId{$tmp_exitid};
        $corrections++;
    } elsif (not exists($objId{$tmp_toid})) {
        print LOG "Exit Object Integrity: In ExitId ($tmp_exitid), ToID of $tmp_toid is invalid, setting to $tmp_fromid (stub exit)\n";
        $dbh->do("UPDATE ExitTbl SET ToID = $tmp_fromid WHERE ExitId = $tmp_exitid");  # Set the ObjectTbl reference to itself.
        $dbh->do("UPDATE ExitTbl SET ExitIdTo = -1 WHERE ExitId = $tmp_exitid"); # Set the destination ExitTbl key to -1 (stub).
        $corrections++;
    }
    printf("\r %36s: %6i / %6i completed (%6i corrected, %3i seconds)",$progress_phrase, ++$progress, $total_operations, $corrections, time()-$start_time);

}
undef @exit_check;
# Refresh Exit List after changes
($largest_exit_id, $num_exit_records) = &get_stats("ExitId", "ExitTbl");
@exitId_array = &dereference_array($dbh->selectall_arrayref('SELECT ExitId FROM ExitTbl ORDER BY FromID ASC'));

###
### Exit Stub Integrity
###
$total_operations = $num_exit_records;
$progress = $num_exit_records;
$progress_phrase = "Check Exit Stub Integrity";
$start_time = time();
@stub_array = &dereference_array($dbh->selectall_arrayref("SELECT ExitId FROM ExitTbl WHERE FromID = ToID and ExitIdTo != -1"));
foreach (@stub_array) {
    my @stub_values = &dereference_array($dbh->selectall_arrayref("SELECT ExitId, ExitIdTo, FromID, ToID FROM ExitTbl WHERE ExitId = $_"));
    print LOG "Exit Stub Integrity: Correcting Stub ExitId ($stub_values[0]) from $stub_values[1] to -1 (Room $stub_values[2] to $stub_values[3]).\n";
}
$corrections = $#stub_array+1;
$dbh->do("UPDATE ExitTbl SET ExitIdTo = -1 WHERE FromID = ToID and ExitIdTo != -1");
undef @stub_array;
printf("\n %36s: %6i / %6i completed (%6i corrected, %3i seconds)",$progress_phrase, $progress, $total_operations, $corrections, time()-$start_time);


###
### Exit DirType Valid
###
$total_operations = $num_exit_records;
$progress = $num_exit_records;
$progress_phrase = "Check ExitTbl for Valid DirType";
$start_time = time();
@dirtype_array = &dereference_array($dbh->selectall_arrayref("SELECT ExitId FROM ExitTbl WHERE DirType = 11"));
foreach (@dirtype_array) {
    print LOG "ExitTbl Valid DirType: In ExitTbl, deleting ExitId ($_) because it has an invalid DirType.\n";
}
$corrections = $#dirtype_array+1;
$dbh->do("DELETE FROM ExitTbl WHERE DirType = 11");
undef @dirtype_array;
printf("\n %36s: %6i / %6i completed (%6i corrected, %3i seconds)",$progress_phrase, $progress, $total_operations, $corrections, time()-$start_time);

###
### Exit Duplicates
###
# $total_operations = $num_exit_records;
# $progress = 0;
# $corrections = 0;
# $progress_phrase = "Check for Exit Duplicates";
# $start_time = time();
# printf("\n %36s: %6i / %6i completed (%6i corrected)",$progress_phrase, $progress, $total_operations, $corrections);
# @exit_check = &dereference_array($dbh->selectall_arrayref("SELECT ExitId, ExitIdTo, FromID, ToID FROM ExitTbl"));
# my %exit_check;
# for (my $x=0; $x <= $#exit_check; $x+=4)
# {
#     my $from_to = $exit_check[$x+2] . $exit_check[$x+3]; # FromID . ToID
#     push(@{$exit_check{$from_to}}, @exit_check[$x..($x+3)]);
# }
# for (my $x=0; $x <= $#exit_check; $x+=4)
# {
#     my $tmp_exitid   = $exit_check[$x];
#     my $tmp_exitidto = $exit_check[$x+1];
#     my $tmp_fromid   = $exit_check[$x+2];
#     my $tmp_toid     = $exit_check[$x+3];

#     if ($tmp_fromid != $tmp_toid and $tmp_exitidto != -1) {
#         my $from_to = $tmp_toid . $tmp_fromid;  # FromID(ToID) . ToID(FromID)
#         my @dest_array = @{$exit_check{$from_to}};

#         if (@dest_array > 5) {
#             while (@dest_array) {
#                 my $exitid    = shift @dest_array;
#                 my $exitidto  = shift @dest_array;
#                 my $fromid    = shift @dest_array;
#                 my $toid      = shift @dest_array;
#                 my $dirtype   = shift @dest_array;
               
#                 if ($exitidto != -1 and not exists($exitId{$exitidto})) {
#                     print LOG "Exit Link Duplicates: Deleted duplicate exit link from ExitId ($exitid) to ExitId $tmp_exitid (Rooms $fromid to $toid) because ExitIdTo link doesn't exist.\n";
#                     $dbh->do("DELETE FROM ExitTbl WHERE ExitId = $exitid");
#                     delete $exitId{$exitid};
#                     $corrections++;
#                 }
#             }
#         }
#     }
#     printf("\r %36s: %6i / %6i completed (%6i corrected, %3i seconds)",$progress_phrase, ++$progress, $total_operations, $corrections, time()-$start_time);
# }
# undef @exit_check;
# undef %exit_check;
# # Refresh Exit List after changes
# ($largest_exit_id, $num_exit_records) = &get_stats("ExitId", "ExitTbl");
# @exitId_array = &dereference_array($dbh->selectall_arrayref('SELECT ExitId FROM ExitTbl ORDER BY FromID ASC'));

###
### Exit Return Integrity
###
$total_operations = $num_exit_records;
$progress = 0;
$corrections = 0;
$progress_phrase = "Check Exit Return Integrity";
$start_time = time();
printf("\n %36s: %6i / %6i completed (%6i corrected)",$progress_phrase, $progress, $total_operations, $corrections);
@exit_check = &dereference_array($dbh->selectall_arrayref("SELECT ExitId, ExitIdTo, FromID, ToID FROM ExitTbl"));
my %exit_check;
for (my $x=0; $x <= $#exit_check; $x+=4)
{
    my $from_to = $exit_check[$x+2] . $exit_check[$x+3]; # FromID . ToID
    push(@{$exit_check{$from_to}}, @exit_check[$x..($x+3)]);
}
for (my $x=0; $x <= $#exit_check; $x+=4)
{
    my $tmp_exitid   = $exit_check[$x];
    my $tmp_exitidto = $exit_check[$x+1];
    my $tmp_fromid   = $exit_check[$x+2];
    my $tmp_toid     = $exit_check[$x+3];

    if ($tmp_fromid != $tmp_toid) {
        my $from_to = $tmp_toid . $tmp_fromid;  # FromID(ToID) . ToID(FromID)
        my @dest_array = @{$exit_check{$from_to}};

        if (@dest_array > 0)
        {
            my $exitid    = shift @dest_array;
            my $exitidto  = shift @dest_array;
            my $fromid    = shift @dest_array;
            my $toid      = shift @dest_array;
           
            if ($tmp_exitidto != $exitid) {
                print LOG "Exit Return Integrity: Correcting ExitTbl ($tmp_exitid) field ExitIdTo corrected from $tmp_exitidto to $exitid (Room $tmp_fromid to $tmp_toid).\n";
                $dbh->do("UPDATE ExitTbl SET ExitIdTo = $exitid WHERE ExitId = $tmp_exitid");
                $corrections++;
            }
        }
        else
        {
            if ($tmp_exitidto != -1) {
                # This is a one way link that isn't properly marked with -1
                print LOG "Exit Return Integrity: No return exit found for Exit_Id $tmp_exitid (Room $tmp_fromid to $tmp_toid).  Changing ExitIdTo from $tmp_exitidto to -1.\n";
                $dbh->do("UPDATE ExitTbl SET ExitIdTo = -1 WHERE ExitId = $tmp_exitid");
                $corrections++;
            }
        }
    }
    printf("\r %36s: %6i / %6i completed (%6i corrected, %3i seconds)",$progress_phrase, ++$progress, $total_operations, $corrections, time()-$start_time);
}
undef @exit_check;
undef %exit_check;
($largest_exit_id, $num_exit_records) = &get_stats("ExitId", "ExitTbl");
@exitId_array = &dereference_array($dbh->selectall_arrayref('SELECT ExitId FROM ExitTbl ORDER BY FromID ASC'));

#######
####### Start Renumbering Section
#######

if ($renumber)
{
    ### Renumber Zones based on alphabetical order
    $zone_temp_id = $largest_zone_id + 1;
    $total_operations = $num_zone_records*2;
    $progress = 0;
    printf("\n\n    Zone Renumber: %6i / %6i", $progress, $total_operations);
    foreach (@zoneId_array) {
   &zone_renumber($_, $zone_temp_id++);
   printf("\r    Zone Renumber: %6i / %6i", ++$progress, $total_operations);
    }
    $zone_temp_id = $largest_zone_id + 1;
    for (my $x=1; $x<=$num_zone_records; $x++) {
   &zone_renumber($zone_temp_id++, $x);
   printf("\r    Zone Renumber: %6i / %6i", ++$progress, $total_operations);
    }

    # ObjectTbl ordering is dependent on the Zone ordering.
    @objId_array = &dereference_array($dbh->selectall_arrayref('SELECT ObjId FROM ObjectTbl ORDER BY ZoneId ASC'));
   
    ### Renumber Objects based on zone order (sort was done when @objId_array was created)
    $object_temp_id = $largest_object_id + 1;
    $total_operations = $num_object_records*2;
    $progress = 0;
    printf("\n  Object Renumber: %6i / %6i", $progress, $total_operations);
    foreach (@objId_array) {
   &object_renumber($_, $object_temp_id++);
   printf("\r  Object Renumber: %6i / %6i", ++$progress, $total_operations);
    }
    $object_temp_id = $largest_object_id + 1;
    for (my $x=1; $x<=$num_object_records; $x++) {
   &object_renumber($object_temp_id++, $x);
   printf("\r  Object Renumber: %6i / %6i", ++$progress, $total_operations);
    }

    # Both NoteTbl and ExitTbl ordering are dependent on the Object ordering.
    @noteId_array = &dereference_array($dbh->selectall_arrayref('SELECT NoteId FROM NoteTbl ORDER BY ObjID ASC'));
    @exitId_array = &dereference_array($dbh->selectall_arrayref('SELECT ExitId FROM ExitTbl ORDER BY FromID ASC'));

    ### Renumber Exits
    $exit_temp_id = $largest_exit_id + 1;
    $total_operations = $num_exit_records*2;
    $progress = 0;
    printf("\n    Exit Renumber: %6i / %6i", $progress, $total_operations);
    foreach (@exitId_array) {
   &exit_renumber($_, $exit_temp_id++);
   printf("\r    Exit Renumber: %6i / %6i", ++$progress, $total_operations);
    }
    $exit_temp_id = $largest_exit_id + 1;
    for (my $x=1; $x<=$num_exit_records; $x++) {
   &exit_renumber($exit_temp_id++, $x);
   printf("\r    Exit Renumber: %6i / %6i", ++$progress, $total_operations);
    }

    ### Renumber NoteTbl
    $note_temp_id = $largest_note_id + 1;
    $total_operations = $num_note_records*2;
    $progress = 0;
    printf("\n    Note Renumber: %6i / %6i", $progress, $total_operations);
    foreach (@noteId_array) {
   &note_renumber($_, $note_temp_id++);
   printf("\r    Note Renumber: %6i / %6i", ++$progress, $total_operations);
    }
    $note_temp_id = $largest_note_id + 1;
    for (my $x=1; $x<=$num_note_records; $x++) {
   &note_renumber($note_temp_id++, $x);
   printf("\r    Note Renumber: %6i / %6i", ++$progress, $total_operations);
    }
}

###
### Update the VersTbl (Contains the pointers to next object number)
###
printf("\n\n Renumbering VersTbl: ");

($largest_zone_id, $num_zone_records) = &get_stats("ZoneId", "ZoneTbl");
($largest_object_id, $num_object_records) = &get_stats("ObjId", "ObjectTbl");
($largest_note_id, $num_note_records) = &get_stats("NoteId", "NoteTbl");
($largest_exit_id, $num_exit_records) = &get_stats("ExitId", "ExitTbl");

my $zone_id = $largest_zone_id + 1;
my $object_id = $largest_object_id + 1;
my $note_id = $largest_note_id + 1;
my $exit_id = $largest_exit_id + 1;

$dbh->do("UPDATE VersTbl SET ZoneID = $zone_id");
$dbh->do("UPDATE VersTbl SET ObjID = $object_id");
$dbh->do("UPDATE VersTbl SET NoteID = $note_id");
$dbh->do("UPDATE VersTbl SET ExitID = $exit_id");
($ZonePtr, $ObjPtr, $NotePtr, $ExitPtr) = &dereference_array($dbh->selectall_arrayref("SELECT ZoneID, ObjID, NoteID, ExitID FROM VersTbl WHERE VersId = 1"));


printf("Done\n");

###
### Print a Summary
###
print "\n   Summary of: $new_file\n";
print "==================================================\n";
&print_stats("Zone",   $largest_zone_id,   $num_zone_records,   $ZonePtr);
&print_stats("Object", $largest_object_id, $num_object_records, $ObjPtr);
&print_stats("Note",   $largest_note_id,   $num_note_records,   $NotePtr);
&print_stats("Exit",   $largest_exit_id,   $num_exit_records,   $ExitPtr);

close LOG;

### Commit and Disconnect
printf("\n Commit Database: Working...");
$dbh->commit;
$dbh->disconnect;
printf("\r Commit Database: Done      \n");

#
# Compact
#
printf(" Vacuum Database: Working...");
$dbh = DBI->connect("dbi:SQLite:$new_file", "", "", {
       PrintError => 0,
       AutoCommit => 1
    }) || die "Cannot connect: $DBI::errstr";
$dbh->do("VACUUM");
printf("\r Vacuum Database: Done      \n");

$dbh->disconnect;
exit;


sub exit_renumber {
    # Do be careful when assigning that you don't assign it to a number used in the ExitIdTo field.
    my ($old_num, $new_num) = @_;
    $dbh->do("UPDATE ExitTbl   SET ExitId   = $new_num WHERE ExitId   = $old_num");
    $dbh->do("UPDATE ExitTbl   SET ExitIdTo = $new_num WHERE ExitIdTo = $old_num");
}

sub note_renumber {
    my ($old_num, $new_num) = @_;
    $dbh->do("UPDATE NoteTbl   SET NoteId   = $new_num WHERE NoteId   = $old_num");
}

sub object_renumber {
   my ($old_num, $new_num) = @_;
   $dbh->do("UPDATE ExitTbl   SET FromID = $new_num WHERE FromID = $old_num");
   $dbh->do("UPDATE ExitTbl   SET ToID   = $new_num WHERE ToID   = $old_num");
   $dbh->do("UPDATE FavTbl    SET ObjID  = $new_num WHERE ObjID  = $old_num");
   $dbh->do("UPDATE NoteTbl   SET ObjID  = $new_num WHERE ObjID  = $old_num");

        # Bit Description         Cmud_Int_Bit Database_Bit Database_Value
        # Reload description           2           4           8
        # Reload exits                 3           5           16
        # Reload name                  1           6           32
        # MUD Room Number              4           11          1024
        #
        # Only Change the RefNum (Vnum) if the bit is set indicating it is *not* a Vnum from the server.
        my @line1_array = &dereference_array($dbh->selectall_arrayref("SELECT Flags FROM ObjectTbl WHERE ObjID = $old_num"));
        my $bit11 = $line1_array[0] & 1024;
        if ($bit11 != 1024 and not $keepvnum)
        {
            $dbh->do("UPDATE ObjectTbl SET RefNum = $new_num WHERE ObjId  = $old_num");           
        }
   $dbh->do("UPDATE ObjectTbl SET ObjId  = $new_num WHERE ObjId  = $old_num");
}

sub zone_renumber {
   my ($old_num, $new_num) = @_;
   $dbh->do("UPDATE DrawTbl   SET ZoneID = $new_num WHERE ZoneID = $old_num");
   $dbh->do("UPDATE MetaTbl   SET ZoneID = $new_num WHERE ZoneID = $old_num");
   $dbh->do("UPDATE ObjectTbl SET ZoneID = $new_num WHERE ZoneID = $old_num");
   $dbh->do("UPDATE PortalTbl SET ZoneID = $new_num WHERE ZoneID = $old_num");
   $dbh->do("UPDATE ZoneTbl   SET ZoneId = $new_num WHERE ZoneId = $old_num");
   $dbh->do("UPDATE ZoneTbl   SET Parent = $new_num WHERE Parent = $old_num");
}

sub get_stats {
   my ($field, $table) = @_;
   $largest_id = $dbh->selectrow_array("SELECT MAX($field) FROM $table");
   $num_ref = $dbh->selectall_arrayref("SELECT $field FROM $table");
   $num_records = @$num_ref;
   return ($largest_id, $num_records);
}

sub print_stats {
   my ($name,$largest_id,$num_objects,$ptr) = @_;
   my $delta = $largest_id - $num_objects;
        my $status = ($largest_id >= $ptr ? "Error" : "Correct");
   printf("%10s: Quantity = %6i, Largest Id = %6i (%5i delta), NextPointer=%6i (%s)\n",$name,$num_objects,$largest_id,$delta,$ptr,$status);
}

sub dereference_array {
   my @return_array;
   my ($array) = @_;
   foreach (@$array) {
      push(@return_array, @$_);
   }
   return @return_array;
}


Last edited by ReedN on Wed Oct 13, 2010 2:46 pm; edited 2 times in total		 Reply with quote
ReedN
Wizard


Joined: 04 Jan 2006
Posts: 1279
Location: Portland, Oregon
PostPosted: Mon Oct 11, 2010 2:20 am   
 
After correcting my map with the above script I went out mapping to pick up new server VNum's for previously mapped rooms. I found that my corrected map had its NoteTbl new entry pointer corrupted again upon using it in Cmud.

I then ran it through the MapConverter Repair function and then used it in Cmud. When I did this the corruption didn't return. So there was something in my map that was somehow provoking Cmud into resetting the NoteTble new entry pointer to 1 each restart of Cmud.

So in summary, I was able to repair the symptoms of the damage caused by the pointer resetting to 1, but I wasn't able to fix whatever the root cause was. The map repair was able to fix the cause after I had sufficiently repaired the symptoms (which it wasn't able to repair).

I'll send Zugg the map so he can possibly see whatever is causing this.
Reply with quote
Zugg
MASTER


Joined: 25 Sep 2000
Posts: 23379
Location: Colorado, USA
PostPosted: Thu Oct 14, 2010 7:03 pm   
 
I looked at the map database you sent me. The problem is that the NoteTbl table doesn't have the NoteId field set as the primary key of the table. Not sure how this happened or if it is something you messed with. If the problem was caused by the map converter program, let me know. I checked my own map files and they all had the NoteId properly set as the primary key.

CMUD will have lots of problems with your file without the proper primary key field for that table.
Reply with quote
ReedN
Wizard


Joined: 04 Jan 2006
Posts: 1279
Location: Portland, Oregon
PostPosted: Thu Oct 14, 2010 7:14 pm   
 
It's not something I would have messed with. Besides which, I hadn't touched the map db outside of Cmud for over 2 years and this just started happening a few months ago with the new GMCP changes that recently went in. I went back and tested various backup maps for this issue and it all began around the GMCP time.

It was definately Cmud and not the map conversion program. The first time I used the map conversion program to repair the map db was after I noticed I had the problem.

How is a primary key set for a particular table and how can you tell what's set as the primary key? I'd like to add that particular check to my error checking and correction script so I can quickly check my map files in the future for this issue.
Reply with quote
Zugg
MASTER


Joined: 25 Sep 2000
Posts: 23379
Location: Colorado, USA
PostPosted: Thu Oct 14, 2010 9:16 pm   
 
Not sure what SQLite program you are using. In SQLite Administrator you can expand the NoteTbl table to see the fields and the primary key field will have a "key" icon next to it. In your map database, the icon next to the NoteId field is the same as the other fields. It's not an index.

The primary key is set when you first create a database. As far as I know in SQLite there isn't any way to change this on an existing database.

When CMUD creates a new map, it uses the SQL statement:
Code:
CREATE TABLE NoteTbl (
    [NoteId] INTEGER PRIMARY KEY,
    [ObjID] INTEGER DEFAULT -1,
    [Note] TEXT,
    [Category] INTEGER DEFAULT 0,
    [Flags] INTEGER DEFAULT 0,
    [UserID] INTEGER DEFAULT 0,
    [Deleted] BOOLEAN DEFAULT False,
    [Modified] TIMESTAMP)

so CMUD is definitely marking this field as the primary key. The map conversion program *should* recreate the database with this when you use the Repair option, so if the problem happens again, something else is changing this field somehow.

As I said, I cannot find this problem in any of my other map databases. So I have no idea how this could have happened to your map.

Edited: Also just tried setting Notes on various rooms in my map, and the NoteId field remained as the primary key. So I don't see anywhere in CMUD that would change this. Are you sure it isn't something one of your other repair programs might have messed up somehow?


Last edited by Zugg on Thu Oct 14, 2010 9:35 pm; edited 1 time in total		 Reply with quote
Zugg
MASTER


Joined: 25 Sep 2000
Posts: 23379
Location: Colorado, USA
PostPosted: Thu Oct 14, 2010 9:24 pm   
 
I will modify the next version of CMUD to force the proper primary key field names in the case where the primary key doesn't exist in the database. That might help prevent the "primary key must be unique" error, but you might still have mapper speed issues if the primary key index is not set properly. CMUD won't be able to fix the field to make it into a primary key...that requires recreating the entire database (like the Map Converter does), but at least this fix will allow it to fetch the proper max value of the NoteId field for use when creating new room notes.

I'd still be interested in ideas on how this field got messed up if you can figure out any ideas on your end.
Reply with quote
Zugg
MASTER


Joined: 25 Sep 2000
Posts: 23379
Location: Colorado, USA
PostPosted: Thu Oct 14, 2010 9:34 pm   
 
Also, looking more at your database, it looks like this database might have originally been converted from a *.MDB (CMUD 2.x or zMUD) map and that at some point you had used some external tool to modify it since there is a table called "XP_PROC" that zMUD/CMUD didn't add.

I ran the Map Converter program on your map database and tried the Repair button and it reported a lot of "primary key not unique" errors for NoteTbl. However, if I just clicked ok for each error and let it finish, then your NoteTbl had the NoteId field properly set as the primary key. Don't know if there was any data lost, but the NoteTbl definitely still had lots of data in it.
Reply with quote
ReedN
Wizard


Joined: 04 Jan 2006
Posts: 1279
Location: Portland, Oregon
PostPosted: Fri Oct 15, 2010 6:17 am   
 
Now that you've shown me what to look for I've gone back through my maps and looked for the primary key error. It is present in all backups I have, which only goes back to July 2009. However, despite having the primary key issue, there was no evidence that the RoomNotes NoteId were messed up. So it must have been something recent that caused it to start using the same NoteId which in turn caused CMud to display the error that alerted me to the error.

My map was created with zMud back around 2005 and it has been used in every beta version in between then and now. I honestly don't believe I could have effected this change in the map. The type of thing I'm doing is clear from the script I posted above, it's this type of stuff:

$dbh->do("UPDATE ZoneTbl SET ZoneId = $largest_zone_id WHERE ZoneId = $_ and Name = $duplicate");

I really just stick to the basics. I correct duplicates, misdirected exit links, and delete orphaned rooms and such, but I've never done anything with the structure of the database itself.

Regarding your Repair button, it simply discards the extra keys. That was one of the reasons I updated my script. I reassign those duplicates to a unique key so that they aren't lost in the repair your program performs.

At any rate, I'm glad to have this resolved. I'll keep an eye on my map and let you know if I find anything that's causing errors.
Reply with quote
Zugg
MASTER


Joined: 25 Sep 2000
Posts: 23379
Location: Colorado, USA
PostPosted: Fri Oct 15, 2010 4:09 pm   
 
Quote:
So it must have been something recent that caused it to start using the same NoteId which in turn caused CMud to display the error that alerted me to the error.

Yes, that is true. More recent versions implemented a new check to try and avoid the "primary key must be unique" error messages. I forget which version added this, but it was pretty recent. CMUD does an SQL statement like this:
Code:
SELECT max(KeyField) FROM Table

to retrieve the largest ID value for each table. To do this CMUD needs to know the name of the primary key field. And rather than hardcoding the fields, CMUD looks in the database itself to determine the name of the key field. Since this fails for the NoteTbl in your map, CMUD ends up setting the max(Keyfield) to 1 instead of the proper value.

The fix in the next version to use a hardcoded value if no primary key field is found should restore proper operation in your case.

My guess is that the problem occurs in one of the early beta versions of CMUD that converted the old zMUD map and did not properly create the primary key. As you said, it would be hard for any of your routines to do this since you can only change the primary key field when the database file is initially created.
Reply with quote
Display posts from previous:   
Post new topic   Reply to topic     Home » Forums » CMUD Beta Forum All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

© 2009 Zugg Software. Hosted by Wolfpaw.net