Hi David!
Because the web version of register & import is giving me a 500 server error even while i set the httpd.conf timeout to 99999, I tried doing the same through the import.php in the /scripts/ directory. The feeds I try to import are quite big, and with this I mean between 500MB till over a gigabyte (it are book feeds).
Even while I try importing with small feeds at the moment, the import.php doesn't do anything. It runs for half an hour (on a small feed of 2MB) and than it finishes with this message:
---
19624 | 19625 | 19626 | 19627 | 19628 | 19629 | 19630 | 19631 | 19632 | 19633 | 19634 | 19635 | 19636 | 19637 | 19638 | 19639 | 19640 | 19641 | 19642 | 19643 | 19644 | 19645 |
PHP Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/lectios/public_html/includes/database.php on line 27
PHP Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/lectios/public_html/includes/database.php on line 32
PHP Notice: Undefined offset: 0 in /home/lectios/public_html/includes/admin.php on line 465
importing luisterboek.xml...[done]
backfilling reviews... PHP Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/lectios/public_html/includes/database.php on line 27
PHP Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/lectios/public_html/includes/database.php on line 32
backfilling reviews...[done]
---
I tried this many times before today and now it added these errors. It probably has to do with me adding some $x and $y vars to show the row the script is currently adding (which is useful for keeping track progress with these big feeds). So I dont think these new errors have anything to do with this.
The database table pt_feeds shows this:
id filename registered imported products format
5 luisterboek.xml 1296852543 0 0 xml|ITEMS/ITEM
---------
David, please add this to my previous error message. I now receive a 200 server error while I added php_value max_execution_time 0 to my .htaccess;
Also I changed this:
$config_databaseDebugMode = FALSE into TRUE;
This didn't show anything unfortunately.
Also, whenever I see it is importing (till it stops with an error) it doesn't actually add anything to the database.
Any idea about all this? What might be the problem?
Hi David,
I tried both of these suggestions, still nothing seems to get into the database. Extremely weird to say the least...
Here is the processlist (from PMA):
SQL-query:
SHOW PROCESSLIST
Profiling
[ Wijzigen ] [ Creëer PHP Code ] [ Vernieuw ]
Toon volledige Queries ID Gebruiker Host Database Commando Tijd Status SQL-query
stop proces 10806 root localhost mysql Query 0 --- SHOW PROCESSLIST
---
As you can see, only the SHOW PROCESSLIST is a process, this while I am running the import.php from shell. Any idea on this? Did I configure something wrongly?
Btw, this is the PMA saying after I imported luisterboek.xml (which is relative small):
filename registered imported products format
luisterboek.xml 1296852543 1296944243 0 xml|ITEMS/ITEM
Shell:
19602 | 19603 | 19604 | 19605 | 19606 | 19607 | 19608 | 19609 | 19610 | 19611 | 19612 | 19613 | 19614 | 19615 | 19616 | 19617 | 19618 | 19619 | 19620 | 19621 | 19622 | 19623 |
19624 | 19625 | 19626 | 19627 | 19628 | 19629 | 19630 | 19631 | 19632 | 19633 | 19634 | 19635 | 19636 | 19637 | 19638 | 19639 | 19640 | 19641 | 19642 | 19643 | 19644 | 19645 |
importing luisterboek.xml...[done]
backfilling reviews...[done]
[root@www scripts]#
I am pretty sure now, it seems like no INSERT mysql is being executed through the shell import.php, is this possible? Am I missing files or smth? I dont think the import.php would run without errors in that case though.
Hi Johan,
Can you confirm that a register and trial import via /admin/ works fine?
Could you also confirm that when running import.php (command line) that you are running the command from the directory of the script and not another location?
Cheers,
David.
--
PriceTapestry.com
Hi David,
Having the same problems importing a feed on a new site - new dist. Feed has 197,000 products, but have filtered to import around 53,000. Getting the following.....
importing myfeed.csv...[53600/10]
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/...../public_html/....../includes/database.php on line 27
Register and trial import works fine, as those importing a category with around 900 products.
Any ideas on what I can do ?
Thanks
Adrian
Hi Adrian,
That sounds like it could be the rare "MySQL Server has gone away" issue. What happens is that the link is closed for some reason whilst PHP still thinks it is open. To fix this, look for the following code at line 47 of includes/database.php
$link = @mysql_connect($config_databaseServer,$config_databaseUsername,$config_databasePassword);...and REPLACE that with:
$link = @mysql_connect($config_databaseServer,$config_databaseUsername,$config_databasePassword);
mysql_ping($link);Hope this helps!
Cheers,
David.
--
PriceTapestry.com
Hi David,
Many thanks for looking at this.
Tried the mod above, but still stopping at the same point and getting the same error.
Adrian
Hi Adrian,
It may be that mysql_ping() isn't enough to re-connect - in place of the above modification instead try:
$link = @mysql_connect($config_databaseServer,$config_databaseUsername,$config_databasePassword);...and then look for the following code at line 60:
return mysql_affected_rows();...and REPLACE that with:
$retval = mysql_affected_rows();
mysql_close($link);
return $retval;Hope this helps!
Cheers,
David.
--
PriceTapestry.com
Excellent,
That's done the trick :)
Thanks again.
Adrian
Hi Johan,
When running PHP from the command line, php_value entries in .htaccess don't apply as it is only relevant to the Apache web server when running PHP as a module.
scripts/import.php already contains set_time_limit(0); so provided that your PHP configuration enables this to be set by a script you shouldn't encounter any timeout errors, and the fact that you say it runs for up to half an hour means it's probably something else.
The error output implies to me that it is the rare scenario of "MySQL Server has gone away"; which means that PHP still thought that it had a valid $link variable, but the link had in fact been closed.
To test for this condition, look for the following code in includes/database.php at line 14:
$link = @mysql_connect($config_databaseServer,$config_databaseUsername,$config_databasePassword);...and REPLACE that with:
$link = @mysql_connect($config_databaseServer,$config_databaseUsername,$config_databasePassword);mysql_ping($link);
In general terms when working with such large feeds, a significant amount of the importing time taken if importing using import.php with @MODIFIED or by a specified filename is the DELETE process.
It is therefore preferable to use @ALL of course (which TRUNCATEs the products table and so doesn't require an indexed DELETE operation), but the downside of that of course is that your site is essentially offline for the duration of the import.
To get around this, I posted a "zero downtime" modification that you can put in place to import to a temporary products table rather than the live products table, and once the import has completed the old table is dropped and the temporary table renamed to become the new live table. This modification is described in the following thread:
http://www.pricetapestry.com/node/3355
Hope this helps!
Cheers,
David.
--
PriceTapestry.com