Maximum statement execution time exceeded

I keep getting this error on the site whether it is through the search form or attempting to browse a product page.

Fatal error: 3024 - Query execution was interrupted, maximum statement execution time exceeded select p., pi.name, pi.short_description, m.name as manufacturer_name, pp.price, pc.campaign_price, if(pc.campaign_price, pc.campaign_price, if(pc.campaign_price, pc.campaign_price, pp.price)) as final_price, ( if(p.id = 'OPEN BOX Acer Predator Helios 300 Gaming Laptop 15.6 Inch Full', 10, 0) + (match(pi.name) against ('OPEN BOX Acer Predator Helios 300 Gaming Laptop 15.6 Inch Full' in boolean mode)) + (match(pi.short_description) against ('OPEN BOX Acer Predator Helios 300 Gaming Laptop 15.6 Inch Full' in boolean mode) / 2) + (match(pi.description) against ('OPEN BOX Acer Predator Helios 300 Gaming Laptop 15.6 Inch Full' in boolean mode) / 3) + if(pi.name like '%OPEN BOX Acer Predator Helios 300 Gaming Laptop 15.6 Inch Full%', 3, 0) + if(pi.short_description like '%OPEN BOX Acer Predator Helios 300 Gaming Laptop 15.6 Inch Full%', 2, 0) + if(pi.description like '%OPEN BOX Acer Predator Helios 300 Gaming Laptop 15.6 Inch Full%', 1, 0) + if(p.keywords like '%OPEN BOX Acer Predator Helios 300 Gaming Laptop 15.6 Inch Full%', 1, 0) + if(p.code regexp 'OPEN BOX Acer Predator Helios 300 Gaming Laptop 15\.6 Inch Full', 5, 0) + if(p.sku regexp 'OPEN BOX Acer Predator Helios 300 Gaming Laptop 15\.6 Inch Full', 5, 0) + if(p.mpn regexp 'OPEN BOX Acer Predator Helios 300 Gaming Laptop 15\.6 Inch Full', 5, 0) + if(p.gtin regexp 'OPEN BOX Acer Predator Helios 300 Gaming Laptop 15\.6 Inch Full', 5, 0) + if (p.id in ( select product_id from lc_products_options_stock where sku regexp 'OPEN BOX Acer Predator Helios 300 Gaming Laptop 15\.6 Inch Full' ), 5, 0) ) as relevance from ( select id, code, mpn, gtin, sku, manufacturer_id, default_category_id, keywords, image, recommended_price, tax_class_id, quantity, sold_out_status_id, views, purchases, date_updated, date_created from lc_products where status and (date_valid_from is null or date_valid_from = '2024-01-12 11:45:20') ) p left join lc_products_info pi on (pi.product_id = p.id and pi.language_code = 'en') left join lc_manufacturers m on (m.id = p.manufacturer_id) left join ( select product_id, if(GBP, GBP 1, GBP) as price from lc_products_prices ) pp on (pp.product_id = p.id) left join ( select product_id, min(if(GBP, GBP * 1, GBP)) as campaign_price from lc_products_campaigns where (start_date is null or start_date = '2024-01-12 11:45:20') group by product_id ) pc on (pc.product_id = p.id) left join lc_sold_out_statuses ss on (p.sold_out_status_id = ss.id) where (p.quantity > 0 or ss.hidden != 1) having relevance > 0 order by relevance desc; in ~/includes/library/lib_database.inc.php (Line 196)
any idea why:
https://repairs.bosecom.org.uk
the search system is now working but still needs work.

These type of queries are large to the naked eye but use to take milliseconds to perform. Do some performance diagnostics on your server.
Here is your query pretty printed for phpMyAdmin you can try:

  p.*, 
  pi.name, 
  pi.short_description, 
  m.name as manufacturer_name, 
  pp.price, 
  pc.campaign_price, 
  if(
    pc.campaign_price, 
    pc.campaign_price, 
    if(
      pc.campaign_price, pc.campaign_price, 
      pp.price
    )
  ) as final_price, 
  (
    if(
      p.id = 'OPEN BOX Acer Predator Helios 300 Gaming Laptop 15.6 Inch Full', 
      10, 0
    ) + (
      match(pi.name) against (
        'OPEN BOX Acer Predator Helios 300 Gaming Laptop 15.6 Inch Full' in boolean mode
      )
    ) + (
      match(pi.short_description) against (
        'OPEN BOX Acer Predator Helios 300 Gaming Laptop 15.6 Inch Full' in boolean mode
      ) / 2
    ) + (
      match(pi.description) against (
        'OPEN BOX Acer Predator Helios 300 Gaming Laptop 15.6 Inch Full' in boolean mode
      ) / 3
    ) + if(
      pi.name like '%OPEN BOX Acer Predator Helios 300 Gaming Laptop 15.6 Inch Full%', 
      3, 0
    ) + if(
      pi.short_description like '%OPEN BOX Acer Predator Helios 300 Gaming Laptop 15.6 Inch Full%', 
      2, 0
    ) + if(
      pi.description like '%OPEN BOX Acer Predator Helios 300 Gaming Laptop 15.6 Inch Full%', 
      1, 0
    ) + if(
      p.keywords like '%OPEN BOX Acer Predator Helios 300 Gaming Laptop 15.6 Inch Full%', 
      1, 0
    ) + if(
      p.code regexp 'OPEN BOX Acer Predator Helios 300 Gaming Laptop 15\\.6 Inch Full', 
      5, 0
    ) + if(
      p.sku regexp 'OPEN BOX Acer Predator Helios 300 Gaming Laptop 15\\.6 Inch Full', 
      5, 0
    ) + if(
      p.mpn regexp 'OPEN BOX Acer Predator Helios 300 Gaming Laptop 15\\.6 Inch Full', 
      5, 0
    ) + if(
      p.gtin regexp 'OPEN BOX Acer Predator Helios 300 Gaming Laptop 15\\.6 Inch Full', 
      5, 0
    ) + if (
      p.id in (
        select 
          product_id 
        from 
          lc_products_options_stock 
        where 
          sku regexp 'OPEN BOX Acer Predator Helios 300 Gaming Laptop 15\\.6 Inch Full'
      ), 
      5, 
      0
    )
  ) as relevance 
from 
  (
    select 
      id, 
      code, 
      mpn, 
      gtin, 
      sku, 
      manufacturer_id, 
      default_category_id, 
      keywords, 
      image, 
      recommended_price, 
      tax_class_id, 
      quantity, 
      sold_out_status_id, 
      views, 
      purchases, 
      date_updated, 
      date_created 
    from 
      lc_products 
    where 
      status 
      and (
        date_valid_from is null 
        or date_valid_from = '2024-01-12 11:45:20'
      )
  ) p 
  left join lc_products_info pi on (
    pi.product_id = p.id 
    and pi.language_code = 'en'
  ) 
  left join lc_manufacturers m on (m.id = p.manufacturer_id) 
  left join (
    select 
      product_id, 
      if(`GBP`, `GBP` * 1, `GBP`) as price 
    from 
      lc_products_prices
  ) pp on (pp.product_id = p.id) 
  left join (
    select 
      product_id, 
      min(
        if(`GBP`, `GBP` * 1, `GBP`)
      ) as campaign_price 
    from 
      lc_products_campaigns 
    where 
      (
        start_date is null 
        or start_date = '2024-01-12 11:45:20'
      ) 
    group by 
      product_id
  ) pc on (pc.product_id = p.id) 
  left join lc_sold_out_statuses ss on (p.sold_out_status_id = ss.id) 
where 
  (
    p.quantity > 0 
    or ss.hidden != 1
  ) 
having 
  relevance > 0 
order by 
  relevance desc;```

Looks like your site is shaping up. I'm curious to see the final make later.

Working on it, not lots to do, just these little bugs, ill migrate to the live server and good to go, i need to fix the search a bit better but so far so good. and then finally google merchant center feed. I tried you extension but dont really know where to draw out the data.

just tried in phpmyadmin, got the same error going through mysql variables, must be max_allowed_packet i think

Seemed to be the setting for max_execution_time i had it set to 600

max_execution_time = 600

However when reading the docs for mysql server 8 just now, its in milliseconds! So set it to 6000 and now, no more errors!

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_execution_time

Excellent 👍
If performance becomes a problem you can also try adding some more indexes for columns.