SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Dec 2001
    Location
    Virginia
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL Table Corruption

    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?
    -Camisade

  2. #2
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature

  3. #3
    SitePoint Member
    Join Date
    Dec 2001
    Location
    Virginia
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code:
    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:

    Code:
        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
    -Camisade


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •