Keith Devens .com

Saturday, August 27, 2005

MySQL Reference Manual :: A.2.8 MySQL server has gone away. If you get that error while trying to do a large import, change the max_allowed_packet setting to something big enough to be able to cover your largest SQL statement in the import file. This is usually a problem when you export your data with MySQL's extended SQL insert format, since an entire table results in just one insert statement.

¶ (0) Tags: [MySQL]

Tuesday, March 9, 2004

Boo MySQL

I'm normalizing some database tables more than I was hoping to get away with. I would have gotten away with it too, if it wasn't for you meddling kids... er, if I didn't need the ability to rename what I was planning to make a key.

Because MySQL doesn't support subqueries (if there was ever a case of worse-is-better, it's PHP and MySQL), the following very simple query:

UPDATE Bookmark_Keywords SET Bookmark_Keyword_Id = (

   SELECT Bookmark_Keyword_Id

   FROM Bookmark_Keyword_Info

   WHERE Bookmark_Keyword_Name = Bookmark_Keyword

)

is going to require that I write procedural code to update the table. Bust.

Update: For future reference, here's the code I had to write:


<?php
$sql = 'SELECT DISTINCT Bookmark_Keyword_Info.Bookmark_Keyword_Id id, Bookmark_Keyword_Name name
   FROM Bookmark_Keyword_Info
   INNER JOIN Bookmark_Keywords ON(Bookmark_Keyword_Name = Bookmark_Keyword)
   ORDER BY Bookmark_Keyword_Name'; //reformatted to fit layout
$rs_id = mysql_query($sql, $conn) or die("Couldn't select your keywords: ".mysql_error());
while($row = mysql_fetch_assoc($rs_id)){
    $sql = "UPDATE Bookmark_Keywords
        SET Bookmark_Keyword_Id = $row[id]
        WHERE Bookmark_Keyword = '$row[name]'"; // reformatted to fit layout
    print_r($row);
    echo '<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;';
    print_r($sql);
    mysql_query($sql, $conn) or die("Couldn't update your table: ".mysql_error());
    echo '<br>';
}
?>

Any formatting in there (print_r, echo), etc., was for me to see that the right thing was going to happen before I uncommented the mysql_query line. And of course, this is just "one-off" code... for instance, I didn't addslashes() on $row[name] because I knew none of my names had slashes or quotes in them.