I have a very strange issue that I am unable to figure out for several days now. I have done a lot of testing so many of the possible root causes are now excluded, which leaves room for the really "exotic" possibilities and I need some help with fresh ideas because I am stuck now.
Some background: A website source files and database (both identical) are installed on two servers Wamp and Lamp.
The issue that I face is for the website queries related to Search Results. The search queries are built from two SQL DB tables using LEFT JOIN. The join is done by an entry ID parameter.
This is an example of one of the search queries:
$tables = $tblprefix."vehicles AS v
LEFT JOIN ".$tblprefix."vehicles_car AS vc on v.id = vc.v_id LEFT JOIN ".$tblprefix."models AS model on v.model = model.id";
}
else {
$fields = " v.*, vc.exterior_color";
The search queries themselves are correct and work perfectly on both servers so this is just as example.
The different scenarios - from a CSV file I upload entries for the main DB table called "vehicles". When after this upload, a search is performed the results show all uploaded entries, i.e. all works correct. I have tired to add more than 27,000 rows and all are displayed without a glitch. Then I go on and start uploading the entries for the second table "vehicles_car". When there are until about 200-215 entries inserted all works correct.
Now the issue - when I insert more than 210-220 entries in the second table, the Search queries suddenly show "No Result" but only for the website installed on the Lamp server. The website on Wamp works no matter how many entries are loaded in the two tables. For some reason only the queries on Lamp server do not work and only if the second table has more that 200+ entries.
Note: the number of the table entries when "No results" are shown vary - it works for 215 entries, then I insert one more - shows "No results", then I delete this last entry and it continues to show "No results". Delete one more - "No results", keep deleting more entries from the second table and it suddenly shows the correct search results again. Really inconsistent behavior.
The strangest thing is that I exported the entire DB from the Lamp server, when queries showing "No results" and imported the DB into the Wamp server. And it works there!!!
So any ideas - what might be the issue (I suspect it is something in the DB) that might be causing the queries to work on one server and not work on the other (and only when more than certain number of rows exist in the second joind table)??
Lamp Server - SQL 5.5.32 InnoDB, phpMyAdmin - 2.8.0.1
Wamp Server - SQL 5.6.14 InnoDB, phpMyAdmin - 4.0.9
Any fresh ideas will be appreciated because I am really really stuck!!!
Thank you!
UPDATE: I just emptied all columns with special characters and replaced them with the cell values of the first row for both tables (where possible only - ID auto increment cells for example not changed).
The same behavior is observed on the Lamp server with the difference that now the SQL query shows "No results" on different number of rows added in the second table. First try added 2037 rows - "No results". Deleted last row - "No Results", deleted one more, all fine (at 2035 rows). Add same row (2036) again all fine, add new row (2037) - all fine. Keep on adding rows with INSERT query one by one all fine, now at row 2039 and search results work correct. Where can this inconsistent behavior be coming from? Any "variable" limit on number of queries with Left Join that the LAMP server can process since this is shared hosting environment? What else can it be?
UPDATE2: I am now inclined to think that this has something to do with the hosting provider service, rather then the queries or the DB themselves. Keep on investigating.
