i have a magento 2 running on nginx and PHP7 with about 45.000 products - just simple products, no additional attributes, Default attribute set. The problem is: products with a high id, about more than 16.500 (entity_id) are not showing. It is definitely not because of settings like visibility, product_online, stock, category assignment etc. - i checked everything, deactivated all caches, deleted the cache/ and page_cache/-directorys, reindexed via console times and times. Also i deactivated all external modules.
When opening a product with a high id, which should normally be shown, via directly entering the url to the frontend product page with the urlkey, it appears and is buyable. When i search the product via product search in frontend, it appears and i can buy it! But when going to the category which it is assigned to, it does not appear. no matter if i just assigned 1 or 2 or more categories. Doesnt matter if it is a first level or in a subtree. There are about 500 categories with subcategories (all under the root-cat which is set for the store). When i try to move a product which is working to any other category, it keeps working and is showing in the new category. If i move a product which is not working, it doesn't matter in which category - it does never appear, in no category. I tried to clone a working product (with a lower id) and just change a number in the sku and save again - it gets a high id and is not working! when i clone all fields of a working product to a not working product, the product keeps on not working. if i clone data from a not working product to a working product, it keeps working...! It is NOT a setting thing.
I analysed all product- and category-related database tables and compared working with not working products in a category, also all index tables - all is set the same!
I changed limits in php, memory_limit is over 2G (!), post_max_size is 100M, time_limit is set to hours, i just have no solution. I get no error message.
In the admin everything works fine and is showing correct. When looking in the backend, i can see that there are e.g. 230 products in a category. When i go to the frontend, there are only 8! when i sort the assigned products of a category in admin category manager, i can see there are 8 products with ids below ID ~16.500, the other 222 are with ids bigger than ~16.500. all these products are sent to google shopping via export modules, are all showing correctly in products grid and category assigned products list, in the csv-import/export... everything is fine!
Unfortenately, this is a live system and the problem is there since beginning, so i have not all products left. in the range there is product ID 16533 which is not working (and everyting greater) and i have product id 16483 which is working (and all products with lower id). I don't know how to add a product with ids between this, without killing the system (the entity_id of a product is used in so many tables...). So i cannot say which is the real last id which is still working, just that it must be about 16500. i googled all the ids betwenn 16483 and 16533 with keywords like "magento 2 not showing category" - there seems to be just nobody on this planet which seems to have the same problem :-(
I isolated the load-method on /vendor/magento/module-catalog/Model/ResourceModel/Product/Attribute/Collection.php which just gives back 8 products for this example category (while testing i'm working with just this category now, its easier to debug. but all categorys have the same problem). The DB query of this method works with temp tables, so i cannot test this... i even tried to delete these 8 products from the category to suspend it is a problem with one of these products. The Collection PHP-Model is really complicated, i don't know how to go on, the debug_backtrace-Function doesnt work, just gives me a blank page... This costed me more than 4 days now :-( Perhaps someone has an idea or a hint, where to continue with finding the error... Thank you.
UPDATE (10mins after sending):
I now modified the SQL query of /vendor/magento/module-catalog/Model/ResourceModel/Product/Attribute/Collection.php::load (removed the search_tmp) and posted it to phpmyadmin - it returns all 230 products which are assigned to the category! It MUST be filtered after this point! I compared every field of working and not working products - there is NO difference! tax class, prices, all the same type (different prices etc., yes, but same tax class, NULL-Fields etc.)...
The only thing i removed to test is:
INNER JOIN `search_tmp_57ed0f6b27ee36_24981671` AS `search_result`
ON e.entity_id = search_result.entity_id
It must be an error with big entity_ids... does anybody have a store with more than 16500 products with magento 2 CE? Thank you.
UPDATE 2 (1h after sending): Additional Info: All products was imported via the csv manager of magento. it was the same amount (about 45.000) from beginning. The only other thing we did was adding products via the admin system manually sometimes 1 or 2 for testing purposes.
UPDATE 3 (next day):
I found out that the category view is using the search model. so perhaps the thing i removed, with the temp sql table, is the solution? Perhaps somebody knows how i can remove this? No matter if hardcoded to try first. Reference this: https://github.com/magento/magento2/issues/4045
I tried to find the issue "MAGETWO-53563" there, but cannot find this in bug tracker...
debug_stacktraceas the latter won't give you much information, plus you'll be able to put breakpoints where needed and have watchers.