I have 100,000+ rows of data I need to insert into a mysql table.

I have used PHP to iterate through the information and prepare it with SQL insert statements.

I cannot get mysql to swallow more than about 10,000 rows before it pukes on me.

I am using the mysql DB < filename style insert method.

Is there any way to do this that doesn't require manually giving mysql little bites of data to deal with at a time?

-Kazer

    I cannot get mysql to swallow more than about 10,000 rows before it pukes on me.

    what exactly is the problem ? is it timing out ? could you post your code also ?

    reg
    kevin

      This code produces *this data.

      $fp = fopen("talondata/harley.pb","r"); 

$content .= "insert into inventory values";

while($data = fgets($fp, 1024))
{
  $i++;
  $part = trim(substr($data,0,18));
  $desc = trim(substr($data,18,47));
  $cost = trim(substr($data,76,8));
  if($i == 200)
  {
    $content = substr($content,0,-2).";\n\n";
    $content .= "insert into inventory values";
    $i=0;
  }
  $content .= "(\"$part\",\"$desc\",\"$cost\",\"0\"),\n";
}
$content = substr($content,0,-2).";";
$content = str_replace("\\","/",$content);
fclose($fp);
$fp = fopen("harleypb.txt","w+");
fwrite($fp,$content);
fclose($fp);

      *

      insert into inventory values("AA0402.1CZ","CAP SCREW, SOCKET HEAD, 1/4-20 X 1/4    D","0.550","0"),
("AA0402.20CBL","SCREW 1/4 X 1/4 SHLD SH AL              D","2.950","0"),
...........
("AN1007.3CBL","SCREW","0.600","0"),
("AN1007.3FBL","SCREW, BUTTON HEAD","1.000","0");

      200 records of values (),() and then it goes to a new insert statement.

      At the end of the file I get:
      ERROR 2006 at line 122409: MySQL server has gone away.
      -Kazer

        Yeah thats really annoying about MySQL and long queries.

        You might be better off breaking them up into seperate SQL queries instead of one big huge one. It'll be slower... but it might not puke so much :-)

        insert into inventory values("AA0402.1CZ","CAP SCREW, SOCKET HEAD, 1/4-20 X 1/4 D","0.550","0"),

        insert into inventory values("AA0402.1CZ","CAP SCREW, SOCKET HEAD, 1/4-20 X 1/4 D","0.550","0"),

        insert into inventory values("AA0402.1CZ","CAP SCREW, SOCKET HEAD, 1/4-20 X 1/4 D","0.550","0"),

        etc....

          That is what I did on the first go around. I just got more of the same errors. Then I moved to the larger inserts.

          It sure is great and well that mySQL can handle so much information so quickly.

          How the hell do I get my information -into- it?
          Anyone have any other ideas?

          -Kaz

            hi,

            Does the SQL insert always fail at a set point or does it change? You should be checking for " (Quotes) from the file you are importing. What might be happening is you are creating an invalid SQL statement.

            There are several options you can take to get this data to insert into the database.

            1. Create error checking in your code. Possibly print each SQL statement out as it executes on the database. You should also print a statement number (have a loop with a numeric value with is incremented).

            2. Turn logging of SQL statements on, on the SQL server and review the log. If you are using MySQL you use "--log=/path/to/log" and then just "tail -f /path/to/log" to see what the database is running.

            3. Print all the SQL statements to a file and import them into the database from the console.

            Hope this helps,
            - Justin

              also try creating a csv file instead of iterating through inserts?

              Give mysql the pure data and let it deal with logistics.

                As jeremuck said - you should be using a csv or other similar raw data file and load the data using "LOAD DATA [LOCAL] INFILE 'file_name'" statement. It is significantly faster than using individual sql statements.

                Look here http://www.mysql.com/doc/en/LOAD_DATA.html for more info.

                Hope this helps

                  Write a Reply...