SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Evangelist ldivinag's Avatar
    Join Date
    Jan 2005
    Location
    N37 33* W122 3*
    Posts
    414
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question problem with an INSERT string that's too large?

    doing a multi insert via a single INSERT command for mysql (5.0.x).

    when i echo the SQl statement, it's around 336k text file.

    Code:
    $result = mysql_query ($INS_sql2, $link);
    if (!$result) {
        echo ('INSERT CCC error: ' . mysql_error());
        echo ("SQL2: " . $INS_sql2);
    	echo "</BR>\n";
        die;
    }
    when i do that, the error occurs saying it truncates around the 143 row.

    now, there are over 800 rows to be inserted. so for a test, i only processed 1-100 rows.

    that insert goes fine.

    i process 1-144, sure enough, it dies with the same truncate error.

    now what's weird is if i copy and paste that 336k text file into my fav mysql client (SQLyog), it process it just fine.

    i looked at the PHP's string variable page and it says it can handle very large strings.

    so somewhere is an issue with string size...?

    help.....
    leo d.

  2. #2
    SitePoint Enthusiast thenk83's Avatar
    Join Date
    Sep 2005
    Location
    Hacienda Heights, CA
    Posts
    49
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Post the actual query code.

  3. #3
    SitePoint Evangelist ldivinag's Avatar
    Join Date
    Jan 2005
    Location
    N37 33* W122 3*
    Posts
    414
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it's a single INSERT statement:
    Code:
    INSERT INTO table (35 columns) VALUES (35 values), (35 values),...,(35 values);

    but like i said, when i copy and pasted it into a text file, it's around 336k big.

    i then tried the command line mysql client and that also choked on around the 143rd row.

    so i'm thinking on a single statement, MYSQL has a limit.

    i dont know what SQLyog is doing on how it can process it and pass it along...

    weird.

    worse case scenario is breaking down the single statement into multiple statements... sigh....
    leo d.

  4. #4
    SitePoint Enthusiast thenk83's Avatar
    Join Date
    Sep 2005
    Location
    Hacienda Heights, CA
    Posts
    49
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    and on the 143rd row.. all the values match the columns exactly?

  5. #5
    SitePoint Evangelist ldivinag's Avatar
    Join Date
    Jan 2005
    Location
    N37 33* W122 3*
    Posts
    414
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    darn it...

    i made the column an INT. since i am converting an EXCEL file using a excel reader script, one row had a FLOAT.

    and hence the issue.

    thanks for that tip.
    leo d.

  6. #6
    SitePoint Enthusiast thenk83's Avatar
    Join Date
    Sep 2005
    Location
    Hacienda Heights, CA
    Posts
    49
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah! Ya.. that will kill it for ya . Glad you got it fixed!
    Mmmmm .NET...PHP...CF...
    AIM: THENK83
    MSN: thenk83@hotmail.com

  7. #7
    . shoooo... silver trophy logic_earth's Avatar
    Join Date
    Oct 2005
    Location
    CA
    Posts
    9,013
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)
    I would split that kind of table up into different tables based on the data or groups of data. Break everything down to it atomic level until it is no longer able to be standalone.

    For example: Firstname lastname can be broken in two columns, Firstname and lastname of course. each part is able to be understood when standalone.
    Logic without the fatal effects.
    All code snippets are licensed under WTFPL.



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
  •