You are here:  »  Automatic Product Mapping uidmap.php Errors

Active Forum Topics


Automatic Product Mapping uidmap.php Errors

Submitted by chessyb on Wed, 2016-09-07 15:08 in

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

Submitted by support on Thu, 2016-09-08 08:51

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

Submitted by chessyb on Thu, 2016-09-08 09:09

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

Submitted by support on Thu, 2016-09-08 11:15

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

Submitted by chessyb on Thu, 2016-09-08 13:02

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

Submitted by support on Thu, 2016-09-08 14:20

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

Submitted by chessyb on Thu, 2016-09-08 15:18

Hi David,

Marvellous! That worked great, thank you for your quick response and help.

Thanks,

- Quentin

Submitted by philstone on Wed, 2017-08-09 07:28

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

Submitted by support on Wed, 2017-08-09 07:46

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

Submitted by philstone on Wed, 2017-08-09 08:59

Thanks David

Will give this a go!

Submitted by philstone on Wed, 2017-08-09 19:20

Hi David

I gave that a go but its still importing everything ?

Thanks

Phil

Submitted by support on Thu, 2017-08-10 08:13

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

Submitted by philstone on Thu, 2017-08-10 21:28

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

Submitted by support on Fri, 2017-08-11 07:54

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