Hanging the script when fetching from the database via Doctrine

Hello!

I am using symfony2 + doctrine. There is a table for 20,000 entries. The essence of the problem is this: for some reason I do not understand, the script hangs when sampling from this table. And the mysql logs are clean, there are no slow queries.

Table layout:

CREATE TABLE `product` ( `id` int(11) NOT NULL AUTO_INCREMENT, `id1c` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `category_id` int(11) DEFAULT NULL, `status` int(11) NOT NULL, `limit_sales` int(11) NOT NULL, `type_of_products` varchar(32) COLLATE utf8_unicode_ci NOT NULL, `exchange_rate_setting_id` int(11) DEFAULT NULL, `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `code` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `description` longtext COLLATE utf8_unicode_ci NOT NULL, `handler` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `art` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `price` decimal(10,2) NOT NULL, `price_retail` decimal(10,2) NOT NULL, `image` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `image_width` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `image_height` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `image_big` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `image_big_width` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `image_big_height` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `sort` int(11) NOT NULL, `date_updated` datetime NOT NULL, `date_created` datetime NOT NULL, PRIMARY KEY (`id`), KEY `IDX_D34A04AD12469DE2` (`category_id`), KEY `IDX_D34A04AD49BCFDD9` (`exchange_rate_setting_id`), KEY `IDX_PRODUCT_ID1C` (`id1c`), CONSTRAINT `FK_D34A04AD49BCFDD9` FOREIGN KEY (`exchange_rate_setting_id`) REFERENCES `setting` (`id`), CONSTRAINT `FK_D34A04AD12469DE2` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=18616 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 

The code that is the source of the problem:

 public function search($data) { $qb = $this->createQueryBuilder("p") ->select() ->where("p.id1c = :data") ->setParameter("data", $data); $products = $qb->getQuery() ->getResult(); return $products; } 

The application log appears in the application logs:

 [2016-09-20 00:07:43] doctrine.DEBUG: SELECT p0_.id AS id_0, p0_.id1c AS id1c_1, p0_.status AS status_2, p0_.limit_sales AS limit_sales_3, p0_.type_of_products AS type_of_products_4, p0_.name AS name_5, p0_.code AS code_6, p0_.description AS description_7, p0_.handler AS handler_8, p0_.art AS art_9, p0_.price AS price_10, p0_.price_retail AS price_retail_11, p0_.image AS image_12, p0_.image_width AS image_width_13, p0_.image_height AS image_height_14, p0_.image_big AS image_big_15, p0_.image_big_width AS image_big_width_16, p0_.image_big_height AS image_big_height_17, p0_.sort AS sort_18, p0_.date_updated AS date_updated_19, p0_.date_created AS date_created_20, p0_.category_id AS category_id_21, p0_.exchange_rate_setting_id AS exchange_rate_setting_id_22 FROM `product` p0_ WHERE p0_.id1c = ? ["00000082457"] [] [2016-09-20 00:07:43] doctrine.DEBUG: SELECT t0.id AS id_1, t0.id1c AS id1c_2, t0.parent_id1c AS parent_id1c_3, t0.code AS code_4, t0.name AS name_5, t0.description AS description_6, t0.description2 AS description2_7, t0.title AS title_8, t0.meta_h1 AS meta_h1_9, t0.meta_description AS meta_description_10, t0.retail_limit AS retail_limit_11, t0.limit_sales AS limit_sales_12, t0.type_of_products AS type_of_products_13, t0.show_retail_price AS show_retail_price_14, t0.ueprice AS ueprice_15, t0.status AS status_16, t0.sort AS sort_17, t0.parent_id AS parent_id_18 FROM category t0 WHERE t0.id = ? [1621] [] 

As I understand the doctrine gets the desired product, gets the category, and then hangs. I can not understand why? It just baffles me.

How did I understand what exactly the problem in the above code? If you comment out the lines:

  $products = $qb->getQuery() ->getResult(); 

This problem does not occur.

I would be very grateful for any advice or link!

ADD: Product Entity:

 <?php namespace Shop\CatalogBundle\Entity; use Doctrine\ORM\Mapping as ORM; use Symfony\Component\Validator\Constraints as Assert; /** * @ORM\Entity(repositoryClass="Shop\CatalogBundle\Entity\Repository\ProductRepository") * @ORM\Table(name="`product`") * @ORM\HasLifecycleCallbacks */ class Product { /** * @ORM\Id * @ORM\Column(type="integer") * @ORM\GeneratedValue(strategy="AUTO") */ protected $id; /** * @ORM\ManyToOne(targetEntity="Category", inversedBy="products") * @ORM\JoinColumn(name="category_id", referencedColumnName="id") */ protected $category; /** * @ORM\Column(type="string", length=255) */ protected $id1c; /** * @ORM\Column(type="integer", length=2) */ protected $status; /** * @ORM\Column(type="integer", length=10) */ protected $limit_sales; /** * @ORM\Column(type="string", length=32) */ protected $type_of_products; /** * @ORM\ManyToOne(targetEntity="\Shop\CoreBundle\Entity\Setting") * @ORM\JoinColumn(name="exchange_rate_setting_id", referencedColumnName="id") * @Assert\NotBlank( * message = "Необходимо установить текущий курс" * ) */ protected $exchange_rate; /** * @ORM\Column(type="string", length=255) * @Assert\NotBlank( * message = "Необходимо ввести название товара" * ) * @Assert\Length( * max = 255, * maxMessage = "Длинна названия товара должна быть менее 255 символов" * ) */ protected $name; /** * @ORM\Column(type="string", length=255) * @Assert\NotBlank( * message = "Необходимо ввести url-код товара" * ) * @Assert\Length( * max = 255, * maxMessage = "Длинна url-кода должна быть менее 255 символов" * ) */ protected $code; /** * @ORM\Column(type="text") */ protected $description; /** * @ORM\Column(type="string", length=255) */ protected $handler; /** * * @ORM\Column(type="string", length=255) */ protected $art; /** * @ORM\Column(type="decimal", precision=10, scale=2) */ protected $price; /** * @ORM\Column(type="decimal", precision=10, scale=2) */ protected $price_retail; protected $formatted_price; protected $formatted_price_retail; /** * @ORM\Column(type="string", length=255) */ protected $image; /** * @ORM\Column(type="string", length=255) */ protected $image_width; /** * * @ORM\Column(type="string", length=255) */ protected $image_height; /** * @ORM\Column(type="string", length=255) */ protected $image_big; /** * @ORM\Column(type="string", length=255) */ protected $image_big_width; /** * @ORM\Column(type="string", length=255) */ protected $image_big_height; /** * @ORM\Column(type="integer", length=10) */ protected $sort; /** * @ORM\Column(type="datetime", name="date_updated") */ protected $updated; /** * @ORM\Column(type="datetime", name="date_created") */ protected $created; public function __construct() { $this->setCreated(new \Datetime()); $this->setUpdated(new \Datetime()); } public function __toString() { return (string)$this->getId(); } public function setUpdatedValue() { $this->setUpdated(new \Datetime()); } /** * @param int $id * @return this */ public function setId($id) { $this->id = $id; return $this; } /** * @return integer */ public function getId() { return $this->id; } /** * @param string $id1c * * @return Product */ public function setId1c($id1c) { $this->id1c = $id1c; return $this; } /** * @return string */ public function getId1c() { return $this->id1c; } /** * @param integer $status * @return Product */ public function setStatus($status) { $this->status = $status; return $this; } /** * @return integer */ public function getStatus() { return $this->status; } /** * @param integer $limitSales * @return Product */ public function setLimitSales($limitSales) { $this->limit_sales = $limitSales; return $this; } /** * @return integer */ public function getLimitSales() { return $this->limit_sales; } /** * @param string $typeOfProducts * @return Product */ public function setTypeOfProducts($typeOfProducts) { $this->type_of_products = $typeOfProducts; return $this; } /** * @return string */ public function getTypeOfProducts() { return $this->type_of_products; } /** * @param string $name * @return Product */ public function setName($name) { $this->name = $name; return $this; } /** * @return string */ public function getName() { return $this->name; } /** * @param string $code * @return Product */ public function setCode($code) { $this->code = $code; return $this; } /** * @return string */ public function getCode() { return $this->code; } /** * @param string $description * @return Product */ public function setDescription($description) { $this->description = $description; return $this; } /** * @return string */ public function getDescription() { return $this->description; } /** * @param string $handler * @return Product */ public function setHandler($handler) { $this->handler = $handler; return $this; } /** * @return string */ public function getHandler() { return $this->handler; } /** * @param string $art * @return Product */ public function setArt($art) { $this->art = $art; return $this; } /** * @return string */ public function getArt() { return $this->art; } /** * @param string $price * @return Product */ public function setPrice($price) { $this->price = $price; return $this; } /** * @return string */ public function getPrice() { $exchange_rate_setting = $this->exchange_rate; if (!($exchange_rate_setting instanceof \Shop\CoreBundle\Entity\Setting)) return 0; if ($this->isUeprice()) { return $this->price * $exchange_rate_setting->getValue(); } else { return $this->price; } } /** * @param string $priceRetail * @return Product */ public function setPriceRetail($priceRetail) { $this->price_retail = $priceRetail; return $this; } /** * @return string */ public function getPriceRetail() { $exchange_rate_setting = $this->exchange_rate; if (!($exchange_rate_setting instanceof \Shop\CoreBundle\Entity\Setting)) return 0; if ($ueprice) { return $this->price_retail * $exchange_rate_setting->getValue(); } else { return $this->price_retail; } } /** * @param string $formatted_price * @return Product */ public function setFormattedPrice($formatted_price) { $this->formatted_price = $formatted_price; return $this; } /** * @return string */ public function getFormattedPrice() { return $this->formatted_price; } /** * @param string $formatted_price_retail * @return Product */ public function setFormattedPriceRetail($formatted_price_retail) { $this->formatted_price_retail = $formatted_price_retail; return $this; } /** * @return string */ public function getFormattedPriceRetail() { return $this->formatted_price_retail; } /** * @param string $image * @return Product */ public function setImage($image) { $this->image = $image; return $this; } /** * @return string */ public function getImage() { return $this->image; } /** * @param string $imageWidth * @return Product */ public function setImageWidth($imageWidth) { $this->image_width = $imageWidth; return $this; } /** * @return string */ public function getImageWidth() { return $this->image_width; } /** * @param string $imageHeight * @return Product */ public function setImageHeight($imageHeight) { $this->image_height = $imageHeight; return $this; } /** * @return string */ public function getImageHeight() { return $this->image_height; } /** * @param string $imageBig * @return Product */ public function setImageBig($imageBig) { $this->image_big = $imageBig; return $this; } /** * @return string */ public function getImageBig() { return $this->image_big; } /** * @param string $imageBigWidth * @return Product */ public function setImageBigWidth($imageBigWidth) { $this->image_big_width = $imageBigWidth; return $this; } /** * @return string */ public function getImageBigWidth() { return $this->image_big_width; } /** * @param string $imageBigHeight * @return Product */ public function setImageBigHeight($imageBigHeight) { $this->image_big_height = $imageBigHeight; return $this; } /** * @return string */ public function getImageBigHeight() { return $this->image_big_height; } /** * @param integer $sort * @return Product */ public function setSort($sort) { $this->sort = $sort; return $this; } /** * @return integer */ public function getSort() { return $this->sort; } /** * @param \DateTime $updated * @return Product */ public function setUpdated($updated) { $this->updated = $updated; return $this; } /** * @return \DateTime */ public function getUpdated() { return $this->updated; } /** * @param \DateTime $created * @return Product */ public function setCreated($created) { $this->created = $created; return $this; } /** * @return \DateTime */ public function getCreated() { return $this->created; } /** * @param \Shop\CatalogBundle\Entity\Category $category * @return Product */ public function setCategory(\Shop\CatalogBundle\Entity\Category $category = null) { $this->category = $category; return $this; } /** * @return \Shop\CatalogBundle\Entity\Category */ public function getCategory() { return $this->category; } /** * @param \Shop\CoreBundle\Entity\Setting $exchangeRate * @return Product */ public function setExchangeRate(\Shop\CoreBundle\Entity\Setting $exchangeRate = null) { $this->exchange_rate = $exchangeRate; return $this; } /** * @return \Shop\CoreBundle\Entity\Setting */ public function getExchangeRate() { return $this->exchange_rate; } /** * This method checks if price setted in ue * return int */ private function isUeprice() { $ueprice = true; $category = $this->getCategory(); if ($category instanceof \Shop\CatalogBundle\Entity\Category && $category->getUeprice() == 0) $ueprice = false; $ueprice = $category->getCategoryUeprice(); var_dump($ueprice); die; return $ueprice; } } 

Essence Category: https://github.com/GhostSt/Shop/blob/master/src/Shop/CatalogBundle/Entity/Category.php

Product Entity: https://github.com/GhostSt/Shop/blob/master/src/Shop/CatalogBundle/Entity/Product.php

Cited references to githabs because the post does not fit in length.

Full log from app / log / dev.log:

 [2016-09-20 11:24:13] request.INFO: Matched route "ShopCatalogBundle_api_catalog_product_search". {"route_parameters":{"_controller":"Shop\\CatalogBundle\\Controller\\API\\ProductController::searchIndex","_route":"ShopCatalogBundle_api_catalog_product_search"},"request_uri":"http://office.trikatushki.test/app_dev.php/api/catalog/product/search?data=00000082457"} [] [2016-09-20 11:24:13] security.DEBUG: Read existing security token from the session. {"key":"_security_main"} [] [2016-09-20 11:24:13] security.DEBUG: User was reloaded from a user provider. {"username":"GhostSt","provider":"Symfony\\Component\\Security\\Core\\User\\InMemoryUserProvider"} [] [2016-09-20 11:24:13] php.INFO: The Symfony\Component\Validator\Validator\RecursiveValidator::validateValue method is deprecated in version 2.5 and will be removed in version 3.0. Use the Symfony\Component\Validator\Validator\ValidatorInterface::validate method instead. {"type":16384,"file":"/var/www/office.trikatushki.test/vendor/symfony/symfony/src/Symfony/Component/Validator/Validator/RecursiveValidator.php","line":162,"level":28928,"stack": [{"function":"handleError","class":"Symfony\\Component\\Debug\\ErrorHandler","type":"->"},{"file":"/var/www/office.trikatushki.test/vendor/symfony/symfony/src/Symfony/Component/Validator/Validator/RecursiveValidator.php","line":162,"function":"trigger_error"},{"file":"/var/www/office.trikatushki.test/src/Shop/CatalogBundle/Service/ProductSearchService.php","line":35,"function":"validateValue","class":"Symfony\\Component\\Validator\\Validator\\RecursiveValidator","type":"->"},{"file":"/var/www/office.trikatushki.test/src/Shop/CatalogBundle/Controller/API/ProductController.php","line":23,"function":"search","class":"Shop\\CatalogBundle\\Service\\ProductSearchService","type":"->"},{"function":"searchIndex","class":"Shop\\CatalogBundle\\Controller\\API\\ProductController","type":"->"},{"file":"/var/www/office.trikatushki.test/vendor/symfony/symfony/src/Symfony/Component/HttpKernel/HttpKernel.php","line":144,"function":"call_user_func_array"},{"file":"/var/www/office.trikatushki.test/vendor/symfony/symfony/src/Symfony/Component/HttpKernel/HttpKernel.php","line":64,"function":"handleRaw","class":"Symfony\\Component\\HttpKernel\\HttpKernel","type":"->"},{"file":"/var/www/office.trikatushki.test/vendor/symfony/symfony/src/Symfony/Component/HttpKernel/DependencyInjection/ContainerAwareHttpKernel.php","line":69,"function":"handle","class":"Symfony\\Component\\HttpKernel\\HttpKernel","type":"->"},{"file":"/var/www/office.trikatushki.test/vendor/symfony/symfony/src/Symfony/Component/HttpKernel/Kernel.php","line":185,"function":"handle","class":"Symfony\\Component\\HttpKernel\\DependencyInjection\\ContainerAwareHttpKernel","type":"->"},{"file":"/var/www/office.trikatushki.test/web/app_dev.php","line":30,"function":"handle","class":"Symfony\\Component\\HttpKernel\\Kernel","type":"->"}]} [] [2016-09-20 11:24:13] doctrine.DEBUG: SELECT p0_.id AS id_0, p0_.id1c AS id1c_1, p0_.status AS status_2, p0_.limit_sales AS limit_sales_3, p0_.type_of_products AS type_of_products_4, p0_.name AS name_5, p0_.code AS code_6, p0_.description AS description_7, p0_.handler AS handler_8, p0_.art AS art_9, p0_.price AS price_10, p0_.price_retail AS price_retail_11, p0_.image AS image_12, p0_.image_width AS image_width_13, p0_.image_height AS image_height_14, p0_.image_big AS image_big_15, p0_.image_big_width AS image_big_width_16, p0_.image_big_height AS image_big_height_17, p0_.sort AS sort_18, p0_.date_updated AS date_updated_19, p0_.date_created AS date_created_20, p0_.category_id AS category_id_21, p0_.exchange_rate_setting_id AS exchange_rate_setting_id_22 FROM `product` p0_ WHERE p0_.id1c = ? ["00000082457"] [] [2016-09-20 11:24:13] doctrine.DEBUG: SELECT t0.id AS id_1, t0.id1c AS id1c_2, t0.parent_id1c AS parent_id1c_3, t0.code AS code_4, t0.name AS name_5, t0.description AS description_6, t0.description2 AS description2_7, t0.title AS title_8, t0.meta_h1 AS meta_h1_9, t0.meta_description AS meta_description_10, t0.retail_limit AS retail_limit_11, t0.limit_sales AS limit_sales_12, t0.type_of_products AS type_of_products_13, t0.show_retail_price AS show_retail_price_14, t0.ueprice AS ueprice_15, t0.status AS status_16, t0.sort AS sort_17, t0.parent_id AS parent_id_18 FROM category t0 WHERE t0.id = ? [1621] [] [2016-09-20 11:24:13] request.INFO: Matched route "ShopCatalogBundle_api_catalog_product_search". {"route_parameters":{"_controller":"Shop\\CatalogBundle\\Controller\\API\\ProductController::searchIndex","_route":"ShopCatalogBundle_api_catalog_product_search"},"request_uri":"http://office.trikatushki.test/app_dev.php/api/catalog/product/search?data=00000082457"} [] 

Total.

I localized the problem and decided. And it turned out to be my mistake and lack of knowledge.

In essence, the Category is set up by self-referenced association, there are parent and children fields. I used the search method in api and returned a serialized object.

And it turned out that during the normalization of objects, the process was fixated on category-> parent-> children-> category. It is strange that no error was displayed, but here I have a hunch. Since the children list consists of a dozen categories, the normalizer does not recognize the loop. But here I am not sure.

Many thanks to all who responded for the help!