1. Home
  2. Forums Index
  3. Server Side / Databases 6:12 am May 21, 2026

Forum Moderators: open

Message Too Old, No Replies

Query won't stop!

         

steven420

3:31 am on Feb 24, 2007 (gmt 0)

10+ Year Member



Hi, I ran a script that loads a data file to a table on my database and now I can't get into plesk or do anything with my database! It has been over a day now and still nothing. the page can't be loaded. I desperately need help. I have ran the script before but without the delayed insert and without the fulltext indexs. I'm sure that the fulltext is probably the culprit but i'm not sure because i haven't used delayed insert before. I'm using php/mysql. here is an example of the script:
 
    $feed = gzopen("2613_1600216_mp.txt.gz","r"); 
    if (!$feed) { 
   die('file open failed: ' . mysql_error()); 
  } 
 if ($feed) { echo 'file opened';} 
  
     mysql_query("drop table example_temp"); 
     mysql_query("CREATE TABLE example_temp ( 
    id INT NOT NULL AUTO_INCREMENT, 
   PRIMARY KEY (id), 
   prod_id varchar(100) NOT NULL default '', 
prod_name varchar(100) NOT NULL default '', 
sku varchar(40) NOT NULL default '', 
prim_cat varchar(50) NOT NULL default '', 
prod_url varchar(255) NOT NULL default '', 
prod_image_url varchar(255) NOT NULL default '', 
buy_url varchar(255) NOT NULL default '', 
prod_desc blob NOT NULL, 
price varchar(6) NOT NULL default '0.00' 
   )") or die(' create table failed: ' . mysql_error()); 
     
     $rowNum = 0; 
     $recCount = 0; 
     
     while($data = fgetcsv($feed, 30000, "¦")){ 
      if($rowNum > 0){ 
   
    $prod_id = mysql_real_escape_string($data[0]); 
 $prod_name = mysql_real_escape_string($data[1]); 
 $sku = mysql_real_escape_string($data[2]); 
 $prim_cat = mysql_real_escape_string($data[3]); 
 $prod_url = mysql_real_escape_string($data[5]); 
 $prod_image_url = mysql_real_escape_string($data[6]); 
 $buy_url = mysql_real_escape_string($data[7]); 
 $prod_desc = mysql_real_escape_string($data[8]); 
 $price = mysql_real_escape_string($data[13]); 
 
 
    
   
  $sql = mysql_query("INSERT DELAYED INTO example_temp SET 
  prod_id =  '$prod_id', 
 prod_name =  '$prod_name', 
 sku =   '$sku', 
 prim_cat =  '$prim_cat', 
 prod_url =  '$prod_url', 
 prod_image_url = '$prod_image_url', 
 buy_url =  '$buy_url', 
 prod_desc =  '$prod_desc', 
 price =   '$price' 
 ;") or die(' insert failed: ' . mysql_error()); 
    $recCount++; 
  } 
  $rowNum++; 
 } 
     $drop = "DROP TABLE example"; 
 mysql_query($drop); 
 if (!$drop) {die('drop failed: ' . mysql_error());} 
 if ($drop) {echo 'drop example successful<br>';} 
 
    $alter = "ALTER TABLE example_temp RENAME example, ADD FULLTEXT (prod_name), ADD FULLTEXT (prod_desc);"; 
  mysql_query($alter); 
    if (!$alter) {die('alter failed: ' . mysql_error());} 
 if ($alter) {echo 'alter successful<br>';} 
 
    
     
     gzclose ($feed);

Is there a way to stop the query? also if someone could tell me how to load large files to my database in a faster way I would greatly appreciate it.

Thanks,
Steven.

Birdman

3:49 am on Feb 24, 2007 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Not sure if this is related, but your PHP has a couple small probs.

$drop = "DROP TABLE example";
mysql_query($drop);
if (!$drop) {die('drop failed: ' . mysql_error());}
if ($drop) {echo 'drop example successful<br>';}

$alter = "ALTER TABLE example_temp RENAME example, ADD FULLTEXT (prod_name), ADD FULLTEXT (prod_desc);";
mysql_query($alter);
if (!$alter) {die('alter failed: ' . mysql_error());}
if ($alter) {echo 'alter successful<br>';}

Above, you define $drop as your query, then after mysql_query() you check $drop as boolean (true/false). It will always be true because $drop never changes after the mysql_query.

You need to run your if{} on the query itself:

$drop = "DROP TABLE example";
if(mysql_query($drop)){
echo 'drop example successful<br>';
} else {
die('drop failed: ' . mysql_error());
}

Sorry I cannot help on the real issue here but you could easily get false positives with your original code.

sabai

3:02 pm on Feb 24, 2007 (gmt 0)

10+ Year Member



It is possible that you have run out of disk space. If it's shared hosting, talk to your host. If it's your own box then check yourself.

If you have access to the command line then you could try restarting mysql.

LOAD DATA INFILE is the quickest way to get a spreadsheet into a mysql DB. Check out that manual and comments online.

steven420

9:00 pm on Feb 26, 2007 (gmt 0)

10+ Year Member



Thanks for all the help. I had a bunch of processes running and have since fixed the problem but now I'm trying to use the LOAD DATA INFILE statement and having some problems. I get the error message that says 'You have an error in your SQL syntax near 'SET prod_id = .....'
This is the code:
$sql = mysql_query("LOAD DATA INFILE 'file name' INTO TABLE example 
FIELDS TERMINATED BY '¦'
LINES TERMINATED BY '\n'
SET 
  prod_id =  '$prod_id',
 prod_name =  '$prod_name',
 sku =   '$sku',
 prim_cat =  '$prim_cat',
 prod_url =  '$prod_url',
 prod_image_url = '$prod_image_url',
 buy_url =  '$buy_url',
 prod_desc =  '$prod_desc',
 price =   '$price'
 ;") or die(' insert failed: ' . mysql_error());

I'm not sure what the problem is. Any help would be great.

Thanks,
Steve.

steven420

9:45 pm on Mar 1, 2007 (gmt 0)

10+ Year Member



I have tried using phpmyadmin to load files to a table and I can get it to work but only if the file is on my computer. If the file is on my server it won't work. after i load a file from my computer to a table the statement calls the file '/tmp/phpzYWh3x'. I don't understand why the file name changed. Is the file supposed to be name like this? I read somewhere that you have to use the absolute path to the file. I tried this and it didn't work. Can anyone tell me how to load a file from my server to a table using 'LOAD DATA INFILE'. All of the search results I can find are just syntax. Even if you can just help me load a file from server to a table with phpmyadmin using the load text file feature that would help alot.

Thanks,
Steven.

 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

  1. Home
  2. Forums Index
  3. Server Side / Databases 6:12 am May 21, 2026