Hi.
First about that missing row:
First check how many rows you get by doing "SELECT * FROM checking":
Either manually in the mysql client or by using the php function that
returns the number or rows in the result set (probably something like
mysql_num_rows)
[I am not sure if you meant this when you said you checked this with
the command line client: Did you "SELECT *" or "SELECT COUNT(*)"?].
If it is 379, you definitely have a corrupted index or table, since
"SELECT *" or "SELECT COUNT(*)" are showing differnt results.
If it is 380, you have a row in the result set, where neither
($row[credit]<>0) nor ($row[debit]<>0) is true. In that case, your
conditions in the while clause just do not trigger anything. (You
could have checked out easily even without mysql_num_rows by adding
another counter just for the number of loops your program stays in the
while clause.)
Now about the corrupted table:
You did not mention how you replicated the database on the second
server, by copying the binary files or by dumping and inserting SQL
statements? If you copied the binary data and something was corrupt,
the replicated database will also be corrupt, of course.
It sounds quite strange to me, that MySQL would corrupt new created
database.
Could you try to run isamchk -e (without -R1,R2). If an index is
corrupt, I guess there is a good chance that the -R option will do
even more damage (ordering according to an corrupt index!).
I would suggest, that you dump your entire table with mysqldump. Look
how many INSERT statements you get. Then drop the table, and use the
mysql command line client to execute the dumped statements. Check if
your problem is gone then.
Bye,
Benjamin.
On Tue, Aug 03, 1999 at 11:49:59PM -0400, vanboers@stripped wrote:
> Sorry, right after I sent to mysql@stripped I received the new e-mail,
> and, really need to resolv this, so, if you get a double-post, sorry.
> Van
>
> Van wrote:
> >
> > I do my checking in a php3-mysql 3.22.24 database, and, I'm starting to
> > get corruption problems. I've been dinking with this for over an hour
> > and a half and figure, that was enough.
> > The table layouts are thus:
> > mysql> describe checking;
> > +-------------+--------------+------+-----+------------+----------------+
> > | Field | Type | Null | Key | Default | Extra
> > |
> > +-------------+--------------+------+-----+------------+----------------+
> > | id | int(10) | | PRI | 0 | auto_increment
> > |
> > | date | date | | MUL | 0000-00-00 |
> > |
> > | description | varchar(255) | YES | | NULL |
> > |
> > | debit | float(10,2) | YES | | NULL |
> > |
> > | credit | float(10,2) | YES | | NULL |
> > |
> > | checkno | int(11) | YES | | NULL |
> > |
> > +-------------+--------------+------+-----+------------+----------------+
> > 6 rows in set (0.00 sec)
> >
> > mysql> select count(*) from checking;
> > +----------+
> > | count(*) |
> > +----------+
> > | 380 |
> > +----------+
> > 1 row in set (0.00 sec)
> > My application says:
> > <?php
> > $Result = mysql_db_query($db, "SELECT * FROM checking");
> >
> > while( $row = mysql_fetch_array($Result) ) {
> > if ( $row[credit] <> 0 ) {
> > $tmp_Credit = $row[credit] + $tmp_Credit;
> > $tmp_Add_Credit_Cnt = $tmp_Add_Credit_Cnt + 1;
> > }
> > if ( $row[debit] <> 0 ) {
> > $tmp_Add_Debit_Cnt = $tmp_Add_Debit_Cnt + 1;
> > $tmp_Debit = $row[debit] + $tmp_Debit;
> > }
> > }
> >
> > $Add_Credit = number_format( $tmp_Credit, "2", ".", "," );
> > $Add_Credit_Cnt = $tmp_Add_Credit_Cnt;
> > $Add_Debit = number_format( $tmp_Debit, "2", ".", "," );
> > $Add_Debit_Cnt = $tmp_Add_Debit_Cnt;
> > mysql_free_result($result);
> > $Cur_Bal = number_format( $tmp_Credit - $tmp_Debit, "2", ".", ","
> > );
> > ?>
> >
> > And, returns:
> > Account Count Debits Count Credits Sum Debits Sum Credits
> Balance
> > Checking 329 50 $24,631.08 $25,799.99
> $1,168.91
> >
> > In other words 1 less checking record than shows in the select count(*)
> > query.
> >
> > Well, this one minor irritation is causing problems (and, I believe it's
> > a corrupted record, but, I'm not able to isolate it), since when I add a
> > new record, then do an isamchk -eR1,R2 .../checking, I get a table
> > corrupted beyond recognition error. isamchk -r recovers it, but
> > truncates the records. I've tried replicating the table in a new dbase,
> > select into outfile checking.txt, then, load data infile checking.txt,
> > and, things work great first out of the gate, but, all flies south after
> > adding a new record, then, isamchk -eR1,R2 and I get the "gee you're
> > database looks pretty messed up, dude" error, once again. I've been
> > through this 7 times on two different servers.
> >
> > Ie., I've replicated the dbase across to another server and, found the
> > same behavior (the web-app in php3 coexists on both, since my
> > workstation is the mirror for my live server) on it. In other words,
> > mysql command line shows 380 records, and, my while result script shows
> > 1 less.
> >
> > I've got a garbage record in here, and, am looking for a tip on
> > isolating it.
> >
> > Unless someone with more detached eyes, can see something I'm blatantly
> > waxing incompetent on.
> >
> > Meantime, I'll pull it all into StarOffice Calc to import and than load
> > data infile after clearing the table again, and, perhaps that will show
> > up the villain. It would be very helpful however, to be able to script
> > this out, and check for it periodically.
> >
> > Hope someone can offer a clue.
