MySQL Table Corruption

[camisade]

Despite word of MySQL's stability, I've run into a situation where I keep seeing table index corruption and I just CANNOT figure out where it's coming from.

Platform:
MySQL v3.23.43
MyODBC v2.50.38
Windows 2000 Server
IIS 5
ASP / VBScript using ADODB for DB interactions
Developing using Macromedia UltraDev with as little hand-scripting as possible. :-)

Interaction with the table:
One web server on a machine hosting pages that are used only for accepting customer orders. Each page has a couple of recordset queries (to tables I'm NOT having any problems with), used to populate drop down list boxes and an insert record behavior (which inserts into the table that's developing repeated index corruptions).

Another web server hosts the customer support web app. The pages that interact with this problem table include several recordset queries, one of which is against this problem table, and an update record behavior which support reps use to review and update order records in this table.

All connections that are opened are closed. However, the liklihood of the index corruption seems greater if a record is queried or updated within moments of it being inserted....

Any ideas why this might be happening?

The recordset query that reads a record from this table into dynamic fields on a detail page is:

<%
set CustomerDetail = Server.CreateObject("ADODB.Recordset")
CustomerDetail.ActiveConnection = MM_conn_STRING
'the next statement is all on one line
CustomerDetail.Source = "SELECT * FROM tab_customer WHERE custid = " + Replace(CustomerDetail__MMColParam, "'", "''") + ""

CustomerDetail.CursorType = 0
CustomerDetail.CursorLocation = 2
CustomerDetail.LockType = 3
CustomerDetail.Open()
CustomerDetail_numRows = 0
' populate variables (fake column headers):
myvar1 = CustomerDetail.Fields("fieldone")
myvar2 = CustomerDetail.Fields("fieldtwo")
myvar3 = CustomerDetail.Fields("fieldthree")
CustomerDetail.Close()
%>

And here's the update code block:
<%
If (Not MM_abortEdit) Then
' execute the update
Set MM_editCmd = Server.CreateObject("ADODB.Command")
MM_editCmd.ActiveConnection = MM_editConnection
MM_editCmd.CommandText = MM_editQuery
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close
<%

Any ideas, anyone?

[seanf]

Other people have posted problems with MySQL on win 2000 here, but typically I can't find you a good example! Have you tried looking at the MySQL website? Are you using mysqld-nt?

Sean

[camisade]

Originally posted by seanf
Other people have posted problems with MySQL on win 2000 here, but typically I can't find you a good example! Have you tried looking at the MySQL website? Are you using mysqld-nt?

Sean

I think I'm MEMORIZING the MySQL docs, trying to hunt this down. :-)

This message was moved, but it's not PHP, it's ASP, so I'll probably try asking in that forum (don't hesitate to answer here, though--I'll definitely read your reply!).

I think I've narrowed the problem down: It seems to be caused by an insert statement not properly closing the database. At any rate, this is from the MySQl Manual:

If you get the following warning from CHECK TABLE or myisamchk: 

# clients is using or hasn't closed the table properly

this means that this counter has come out of sync. This doesn't mean that the table is corrupted, but means that you should at least do a check on the table to verify that it's okay.

This is what I get if I do an insert into the table that's getting corrupted, then do a "CHECK TABLE tab_customer;" within a few seconds afterward.

When that happens, queries subsequently run against the table return errors.

Here's the code I'm using to do the record insert. It's pretty straightforward (the code block is actually created by MacroMedia UltraDev:

    Set MM_editCmd = Server.CreateObject("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_editConnection
    MM_editCmd.CommandText = MM_editQuery
    MM_editCmd.Execute
    MM_editCmd.ActiveConnection.Close

...where (of course) MM_editConnection is the connection string and MM_editQuery is the SQl statement that handles the insert.

Data IS being correctly inserted. So, why wouldn't the table get closed properly?

Thanks for any help that any of you MySQL masters might give!

-Kevin