drlndkhib08556095 2014-08-26 17:28
浏览 89
已采纳

SQL搜索查询在不同的服务器上处理不同

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.

  • 写回答

1条回答 默认 最新

  • duanchu9914 2014-09-03 13:25
    关注

    Ok so after spending two weeks looking into the SQL queries and pushing on the hosting support to investigate the issue on their side, it turned out that there was a limit for max_join_size set to 7,000,000 which would eventually return only about 2000+ records. If the records were to exceed this limit no results are returned by the server. Nice.

    It turns out that what once used to be a great hosting service is now becoming a real pain in the *.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题