Hi David,
I'm using the uidmap.php to match EAN numbers on a database that has over 850k products. Unfortunately, the script will run for a random amount of time before the following error keeps occurring:
Warning: Empty row packet body in /home/username/public_html/folder/scripts/uidmap.php on line 28
Warning: Error while sending QUERY packet. PID=11078 in /home/username/public_html/folder/scripts/uidmap.php on line 61
Do you have any idea how to get around this problem please?
Thanks,
- Quentin
Hi David,
I've applied your fix, but the same result:
Warning: Empty row packet body in /home/username/public_html/folder/scripts/uidmap.php on line 66
Warning: Error while sending QUERY packet. PID=10169 in /home/username/public_html/folder/scripts/uidmap.php on line 82
Done.
I've read online that it could be something to with max_allowed_packet.
- Quentin
Hello Quentin,
If memory permits, try using a regular rather than buffered query, so where you have the following code beginning at line 22:
mysqli_real_query($link1,$sql1);
if ($result1 = mysqli_use_result($link1))
{
print "Phase 0: initial query complete\n";flush();
while($product = mysqli_fetch_assoc($result1))...REPLACE with:
$result1 = mysqli_query($link1,$sql1);
if (TRUE)
{
print "Phase 0: initial query complete\n";flush();
while($product = mysqli_fetch_assoc($result1))Cheers,
David.
--
PriceTapestry.com
Hi David,
Same error again:
Warning: Empty row packet body in /home/username/public_html/folder/scripts/uidmap.php on line 64
Warning: Error while sending QUERY packet. PID=21836 in /home/username/public_html/folder/scripts/uidmap.php on line 80
Done.
Here's the code that I'm using:
{code saved}
I did find this link, but I'm not sure if this would help in my case.
http://stackoverflow.com/questions/3780521/strange-mysql-error-empty-row-packet-body-when-using-mysql-fetch-object-php-5
Thanks,
- Quentin
Hi Quentin,
It looks like converting phase 1 to a normal rather than unbuffered query has done the trick at that point, so to apply the same change to phase 2, look for the following code beginning at line 60:
mysqli_real_query($link1,$sql1);
if ($result1 = mysqli_use_result($link1))
{
while($product = mysqli_fetch_assoc($result1))...and REPLACE with:
$result1 = mysqli_query($link1,$sql1);
if (TRUE)
{
while($product = mysqli_fetch_assoc($result1))Hope this helps!
Cheers,
David.
--
PriceTapestry.com
Hi David,
Marvellous! That worked great, thank you for your quick response and help.
Thanks,
- Quentin
Hi David
Hope all is well
Would there be a way to only allow uidmap.php to run for products matching when those products are above a certain price eg: £100 +?
Thanks
Phil
Hello Phil,
Sure - to apply the mapping on where price is >= 100, edit uidmap.php and look for the following code at line 77:
$sql2 = "UPDATE `".$config_databaseTablePrefix."products` SET name='".database_safe($product["name"])."',normalised_name='".database_safe($normalisedName)."',search_name='".database_safe($searchName)."' WHERE ".$config_uidField."='".database_safe($product["uid"])."'";...and REPLACE with:
$sql2 = "UPDATE `".$config_databaseTablePrefix."products` SET name='".database_safe($product["name"])."',normalised_name='".database_safe($normalisedName)."',search_name='".database_safe($searchName)."' WHERE ".$config_uidField."='".database_safe($product["uid"])."' AND price >= '100.00'";Cheers,
David.
--
PriceTapestry.com
Hi David
I gave that a go but its still importing everything ?
Thanks
Phil
Hello Phil,
The above won't affect what is imported but you can do that easily with the Price Range filter from this thread which should do the trick, using a minimum price of 100.00 and leaving maximum price blank...
Cheers,
David.
--
PriceTapestry.com
Thanks David
Sorry I will try to explain better, i was wondering if it is possible to only map products where the value is say £100 or more, i want all products imported but only product mappings created for those products matching results using uidmap.php... if its possible?
Thanks
Phil
Hi Phil,
uidmap.php could be modified so that after a mapping was applied, anything with that UID value but below your price threshold is deleted. If you wanted to give that a go, look for the following code at line 79:
mysqli_query($link2,$sql2);...and REPLACE with:
mysqli_query($link2,$sql2);
$sql2 = "DELETE FROM `".$config_databaseTablePrefix."products` WHERE ".$config_uidField."='".database_safe($product["uid"])."' AND price < '100.00'";
mysqli_query($link2,$sql2);Hope this helps!
Cheers,
David.
--
PriceTapestry.com
David,
I also have error: "Error while sending QUERY packet" with uidmap.php script.
And only 25000 products in the database. My VPS has 6GB of RAM.
I have added:
mysqli_query($link1,"SET SESSION wait_timeout = 900");
mysqli_query($link2,"SET SESSION wait_timeout = 900");without success.
When i apply (the modified wait_timeout code is still active):
$result1 = mysqli_query($link1,$sql1);
if (TRUE)
{
print "Phase 0: initial query complete\n";flush();
while($product = mysqli_fetch_assoc($result1))the script is not ending. The output is:
Working...
Phase 0: initial query complete
Hopefully you have an idea how to fix this....
David,
I have tried to run the script a second time and now it is working with this fix:
$result1 = mysqli_query($link1,$sql1);
if (TRUE)
{
print "Phase 0: initial query complete\n";flush();
while($product = mysqli_fetch_assoc($result1))
regards,
Toby.
hi Quentin,
That sounds like it may be similar to the elusive "MySQL server has gone away" issue - which occurs when PHP believes it is still connected to MySQL whilst it is not, however the section of code referred to is mysqli_fetch_assoc() call against a mysqli_real_query() result so it's strange that the MySQL connection would disappear mid-way through obtaining the result set.
The first thing I would suggest would be to put a large wait_timeout on the connections, so in uidmap.php where you have the following code beginning at line 12:
$link1 = mysqli_connect($config_databaseServer,$config_databaseUsername,$config_databasePassword,$config_databaseName);$link2 = mysqli_connect($config_databaseServer,$config_databaseUsername,$config_databasePassword,$config_databaseName);
...REPLACE with:
$link1 = mysqli_connect($config_databaseServer,$config_databaseUsername,$config_databasePassword,$config_databaseName);$link2 = mysqli_connect($config_databaseServer,$config_databaseUsername,$config_databasePassword,$config_databaseName);
mysqli_query($link1,"SET SESSION wait_timeout = 900");
mysqli_query($link2,"SET SESSION wait_timeout = 900");
Hope this helps!
Cheers,
David.
--
PriceTapestry.com