Thanks Javier,
Automated routine that has worked for years without fail, failed on that
computer this morning. All it was doing zipping unload files to a zip file
and aborted 10% of the way through. Just ordered a new development
computer. Too many mission critical operations on this 5 year old XP
computer.
I always work with at least one backup and alter database local. Tend to
reload to Elec407.rx?, then Elec509.rx? etc. (4:07 PM and 5:09 PM) and keep
all reloaded files with documentation in case I decide to back up a step or
two. Even a 3+gig DB reloads quickly these days. When I am done and fully
tested then I unload all and rebuild the DB that way. I like how it puts all
the views at the bottom and can look for errors while it runs.
Results from RScope of the Friday version of database (before autochk errors
appear due to altering table):
Check Database Info: 0 Errors for the dbinfo block.
Check Structure: All tables: 0 errors found for the selected tables.
Check Data: All tables: Went through all of the system tables without error
but...
This is copied from the middle of the scan:
Checking the structure for the table WOParts
Column scn_part
Column qty
Column lotnum
Column WOCtrlnum
Column MultiCtrlNum
Column vdesc
Column CustID
Checking the structure for the table WOPartsDetail
Column qtydet
Column partctrlnum
Column MultiCtrlNum
0 errors found for the selected tables
______________________________________________________________
(row address 1774314689, column 7 of table 158) with the actual row.
ERROR
The column pointer contained in LOB value (12210176) is inconsistent
(row address 1774314813, column 7 of table 158) with the actual row.
ERROR
The column pointer contained in LOB value (12214272) is inconsistent
(row address 1774315061, column 7 of table 158) with the actual row.
ERROR
The column pointer contained in LOB value (12218368) is inconsistent
(row address 1774315185, column 7 of table 158) with the actual row.
ERROR
The column pointer contained in LOB value (12222464) is inconsistent
(row address 1774315309, column 7 of table 158) with the actual row.
It appears to have found problems but I am not sure of which table because
it skipped listing the table.
When I find out more I will post.
Kenny
-----Original Message-----
From: [email protected] [mailto:[email protected]] On Behalf Of Javier
Valencia
Sent: Monday, April 09, 2012 12:36 AM
To: RBASE-L Mailing List
Subject: [RBASE-L] - RE: strange database errors
Kenny,
Sound like your database was corrupted. Normally, when you do:
UNLOAD ALL
The resulting file has the code to build the tables first, add data next and
then build the indices and comments.
UNLOAD STRUCTURE
will back up the same items minus the data.
If your database was corrupted (incorrect index information) then the
"unloaded" file will also be incorrect and when it tries to re-create the
index with the incorrect data you will get error messages, and you will only
be able to rebuild the database the way you did it.
Whenever I modify a database structure, I always make separate backups of
structure and data, so I can rebuild the database one step at the time, if
necessary.
When I need to modify a database structure, I normally download the database
to a local computer, make the changes with only one user (or in single user
mode), test the database and then reload it to the server. This has worked
well for me and I have not had problems with corruption in a while.
Did you try looking at your database with R:Scope?
Javier,
Javier Valencia, PE
O: 913-829-0888
H: 913-397-9605
C: 913-915-3137
-----Original Message-----
From: [email protected] [mailto:[email protected]] On Behalf Of Kenny Camp
Sent: Sunday, April 08, 2012 7:22 PM
To: RBASE-L Mailing List
Subject: [RBASE-L] - RE: strange database errors
Does this look familiar?
R>reload electro
Reloading data for SYS_COMMENTS 186 rows reloaded
-ERROR- Table SYS_COMMENTS has an incorrect number of rows. ( 450)
Reloading data for SYS_DEFAULTS 48 rows reloaded
-ERROR- Table SYS_DEFAULTS has an incorrect number of rows. ( 450)
Reloading data for SYS_RULES 12 rows reloaded
-ERROR- Table SYS_RULES has an incorrect number of rows. ( 450)
Reloading data for SYS_VIEWS 64 rows reloaded
Reloading data for SYS_COMPUTED 66 rows reloaded
-ERROR- Table SYS_COMPUTED has an incorrect number of rows. ( 450)
Reloading data for SYS_PASSWORDS 0 rows reloaded
Reloading data for SYS_CONSTRAINTS 116 rows reloaded
-ERROR- Table SYS_CONSTRAINTS has an incorrect number of rows. ( 450)
Reloading data for SYS_FORMS 0 rows reloaded
Reloading data for SYS_REPORTS 0 rows reloaded
Reloading data for SYS_LABELS 0 rows reloaded
Interesting (and ugly) coincidence, late last week I discovered the same
exact issue with my largest database. I do not know what caused it (maybe
my development computer I use remotely I have been having problems with
lately corrupted something) but I spent most of this weekend working to
eliminate the issue.
The problem appears to have been a corrupted system table?, but I am not
sure. I thought I had exceeded the maximum width of a table, but I don't
believe that now, because I removed many columns and reloaded and the
problems remained. I will give you the short version of what I think fixed
it.
When I would alter my customer table all FKs would be broken (referencing a
non-existent table with a temp name) as a result of failing to finish the
save of the new table properly.
The way (I think) I fixed it was to unload structure and break it into
pieces to build tables and views and stop there. Then I added the data.
Then I added a primary key and saved the table and all 626,000 rows were
gone. Tried again with error messages on and got a constraint error from
the PK saying the rows must be unique. Tried again with the empty table and
said append table2 to table1 where limit = 1. I got the "must be unique"
error. One row created was not unique??? Really????
So I deleted the table and created it brand new (no PKs or FKs at this time
so I could do this). Then I added the PK and appended the 636000 rows
successfully. Then I traced through the rest of the unloaded structure file
and I noticed that the PK on the table referenced a wrong table name. This
is an error that I had found earlier during the weekend and could not
explain. Add a column to the invoice table and it would say that "rows in
the customer table must be unique!"
ALTER TABLE `Invoice` ADD PRIMARY KEY +
(`invctrlnum` ) +
('Values for rows in customer must be unique. (2738)',+ 'Cannot delete -
values exist in another table. (2509)',+ 'Cannot update - values exist in
another table. (2717)')
Also I noticed that the str.unl file would show:
ALTER TABLE `podetail` ADD FOREIGN KEY + ( `VPOSCN` )+ REFERENCES `polist`
Note the above should be followed by the two error messages. I found a
number of these.
Moral of story: I replaced two problem tables with fresh built tables and
added the data and the PKs manually, then traced line by line through the
creation of indexes carefully looking for errors in the unload structure
file (create index part) before running them one at a time (F10) and
aborting if I found a problem.
I just got home from fixing(?) everything and so far testing seems to have
everything OK. Needs further testing.
This was not fun at all, but while I was working through this I got an error
twice saying something like "disk error - please check disk and files".
This happend both local and over the network. I changed computers and
worked locally to fix the issue.
If I discover anything else I will post.
Kenny Camp
-----Original Message-----
From: [email protected] [mailto:[email protected]] On Behalf Of Dave McCann
Sent: Friday, April 06, 2012 10:18 PM
To: RBASE-L Mailing List
Subject: [RBASE-L] - strange database errors
I've been up against a wall with a recent database problem on one of my
projects, hoping I can get some ideas.
Recently my database began having this issue, whenever I go to add a column
or make pretty much any structural change, I get an ERROR - A foreign key
references a table not known to be referenced (1242), followed by an ERROR -
The number of rows counted was not expected.
I have been able to UNLOAD my structure and recreate the database with
seemingly no errors, database checks out OK with AUTOCHK and R:Scope
afterwards, but as soon as I go to edit the structure the errors return.
Running R:Scope the errors are invalid row count errors in SYS_RULES,
SYS_CONSTRAINTS, and SYS_COMMENTS. I suspected something wrong with a
constraint or rule because I've made adjustments in those areas in the last
month and since the initial error indicates a problem with a foreign key.
Any ideas about how I could go about narrowing down the specific
problem? Since everything was valid when setup, and again when
structure was recreated just not quite sure why/how this could be happening.
--
Dave McCann
