homepage Welcome to WebmasterWorld Guest from 54.205.207.53
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Query won't stop!
steven420

5+ Year Member



 
Msg#: 3262317 posted 3:31 am on Feb 24, 2007 (gmt 0)

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

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 3262317 posted 3:49 am on Feb 24, 2007 (gmt 0)

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

10+ Year Member



 
Msg#: 3262317 posted 3:02 pm on Feb 24, 2007 (gmt 0)

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

5+ Year Member



 
Msg#: 3262317 posted 9:00 pm on Feb 26, 2007 (gmt 0)

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

5+ Year Member



 
Msg#: 3262317 posted 9:45 pm on Mar 1, 2007 (gmt 0)

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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved