SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    865
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Incorrect key file for my table

    I just executed my php script and got the following error. I'm unsure what it means or how I fix it.

    A fatal MySQL error occured.
    Query:
    Error: (126) Incorrect key file for table '/ramdisk/mysql/tmp/#sql_5ec4_1.MYI'; try to repair it

    Here is a copy of my simple script that goes through a few database tables blowing away spam entries and such. Can someone help me decipher this error?

    Thanks!

    PHP Code:
    <?php 

    include 'toolsHeader.php';
    include 
    'db.inc.php'

    $today date("Y-m-d h:i:s");


    //////////////////////////////////////////////////////////////////
    //Delete the spam emails that were added by spambots
    //////////////////////////////////////////////////////////////////

    $spamSQL "select count(*) as total from notifications where sender = 'Unknown'";
    $results mysql_query($spamSQL) or die(mysql_error());
    $row mysql_fetch_assoc($results);
    $totalSpam $row['total'];

    if (
    $totalSpam 0)  {

    $spamNuker "delete FROM notifications WHERE sender = 'Unknown'";
    $result mysql_query($spamNuker) OR die(mysql_error());



    else {

    $totalSpam "0";

    }





    //////////////////////////////////////////////////////////////////
    //Delete bogus testimonials that were posted
    //////////////////////////////////////////////////////////////////

    $testimoniesSQL "select count(*) as total from testimonies where author = '0'";
    $results mysql_query($testimoniesSQL) or die(mysql_error());
    $row mysql_fetch_assoc($results);
    $totalTestimonies $row['total'];


    if (
    $totalTestimonies 0)  {


    $testimoniesNuker "delete FROM testimonies WHERE author = '0'";
    $result mysql_query($testimoniesNuker) OR die(mysql_error());



    else {

    $totalTestimonies "0";

    }




    //////////////////////////////////////////////////////////////////
    //Delete bogus accounts that were posted
    //////////////////////////////////////////////////////////////////

    $usersSQL "select count(*) as total FROM `users` where firstname = lastname";
    $results mysql_query($usersSQL) or die(mysql_error());
    $row mysql_fetch_assoc($results);
    $totalUsers $row['total'];


    if (
    $totalUsers 0)  {

    $usersNuker "delete FROM users WHERE firstname = lastname or zip = '123456'";
    $result mysql_query($usersNuker) OR die(mysql_error());



    else {

    $totalUsers "0";

    }




    //////////////////////////////////////////////////////////////////
    //Compose an email that tells how much spam was deleted overall
    //////////////////////////////////////////////////////////////////


    if ($totalSpam OR $totalTestimonies OR $totalUsers 0) {

    $type "Spam Removal";
    $sender "support@oil-testimonials.com";
    $recipient "robert@recordaudio.net";
    $subject "Total purged spam";
    $message "The following spam was removed from the database: 

    Emails where sender was unknown: 
    $totalSpam
    Testimonials with author set to zero: 
    $totalTestimonies
    Bogus user accounts: 
    $totalUsers";

    $createEmail "INSERT INTO notifications (date, priority, type, sender, recipient, subject, message) VALUES (

    '
    $today', 
    '2',
    '
    $type',
    '
    $sender',
    '
    $recipient', 
    '
    $subject', 
    '
    $message')";

    mysql_query($createEmail) OR die("<b>A fatal MySQL error occured</b>.\n<br />Query: " $createEmail "<br />\nError: (" mysql_errno() . ") " mysql_error()); 




    else {

    print (
    "<p>There were no spam emails to process at ths time.</p>");

    }



    //////////////////////////////////////////////////////////////////
    //Pull emails out that need to be sent, based on priority
    //////////////////////////////////////////////////////////////////


    $getEmails "SELECT u.firstname, u.lastname, n.nID, n.date, n.priority, n.type, n.sender, n.recipient, n.subject, n.message FROM users u, notifications n WHERE sent is null order by priority limit 5";

    $result mysql_query($getEmails) OR die("<b>A fatal MySQL error occured</b>.\n<br />Query: " $createEmail "<br />\nError: (" mysql_errno() . ") " mysql_error()); 

    if (
    $row mysql_fetch_array($result)) {

    do {

    $firstname    =$row["firstname"];    // Firstname of the one sending the email
    $lastname    =$row["lastname"];    // Lastname of the one sending the email
    $nID        =$row["nID"];            // ID of the stored email
    $type        =$row["type"];        // The type of email going to be sent
    $date        =$row["date"];        // The date it was put into DB
    $sender     =$row["sender"];    // Address of sender
    $recipient  =$row["recipient"];    // Address of receiver
    $subject    =$row["subject"];      // Subject of the email to go out
    $message    =$row["message"];    // Message of the email


    $senderName "$firstname $lastname";
    $senderEmail "$email";


    if (
    $senderEmail == "support@oil-testimonials.com")  {

    $senderName "Support Team";

    }

    $message stripslashes($message);
    $subject stripslashes($subject);


    print (
    "nID = $nID$date$type$recipient<br>");

    $headers =  "From: $senderName <$senderEmail>\r\n";
    $headers .= "Reply-To: $senderEmail\r\n";


    mail($recipient$subject$message$headers);


    //////////////////////////////////////////////////////////////////
    //Update the row to indicate the email has been sent
    //////////////////////////////////////////////////////////////////



    $updateRows "UPDATE notifications SET sent = '$today' where nID = $nID";
    mysql_query($updateRows) OR die("<b>A fatal MySQL error occured</b>.\n<br />Query: " $createEmail "<br />\nError: (" mysql_errno() . ") " mysql_error()); 


    } while(
    $row mysql_fetch_array($result)); //End  DO loop

    }  // End getting results from the getEmails query


    include 'toolsFooter.php';


    ?>
    Convert your dollars into silver coins. www.convert2silver.com

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    what happened when you tried to repair the table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    865
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Unfortunately, nothing. Despite this message below saying the status is ok, it still gives me the error when running the script.

    Table Op Msg_type Msg_text
    recordau_oils.notifications repair status OK

    Thank you r937 for helping me with this.
    Convert your dollars into silver coins. www.convert2silver.com

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    maybe it was one of the other tables? did you repair them as well?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    865
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I didn't do the users table earlier, but just did it now. The script still fails. When I do a more basic query, and don't involve the complexity of the notifications and users table, then everything works fine. Weird.

    Now what?
    Convert your dollars into silver coins. www.convert2silver.com

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i did some googling on that message (there are tons of hits) and it's possible you might be out of temporary disk space, which seems to be supported by the error message pointing to '/ramdisk/mysql/tmp/#sql_5ec4_1.MYI';
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    FYI i don't do php at all, but i was reading your script and noticed that one of your queries performs a cross join --
    Code:
    SELECT ...
    FROM users u, notifications n 
    WHERE sent is null 
    order by priority limit 5
    maybe if you fix this the problem goes away?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    865
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Are you saying that I should state where n.sent is null order by n.priority ?

    SELECT u.firstname, u.lastname, n.nID, n.date, n.priority, n.type, n.sender, n.recipient, n.subject, n.message FROM users u, notifications n WHERE n.sent is null order by n.priority limit 5
    Doing a cross join is bad? I have other queries like this on my site and they seem to run ok. Please help me know specifically what I did wrong.

    Thank you.
    Convert your dollars into silver coins. www.convert2silver.com

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, doing a cross join is bad here

    you forgot to join the tables on a join condition (e.g. where n.somecolumn = u.id)

    what your cross join does is match every notification with all users, and every user with all notifications

    if there are N notifications and U users, then the cross join produces N*U rows

    which would explain why you're running out of temp space...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    865
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Ok, I got my join condition in place. Not sure how I forgot that one.

    SELECT u.firstname, u.lastname, u.email, n.nID, n.date, n.priority, n.type, n.sender, n.recipient, n.subject FROM users u, notifications n WHERE u.email = n.sender order by n.priority limit 20
    I discovered that ordering by n.priority is really slowing things down. Why can't we put limit 20 first and then tell it to order by the priority? I think it would make the ordering a much less resource intense operation. Your thoughts r937?
    Convert your dollars into silver coins. www.convert2silver.com

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by busboy View Post
    Your thoughts r937?
    it just doesn't work that way

    you have to sort first, before taking the top 20

    if you took any 20, and then sorted the others, why would you bother sorting? you already took 20
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •