SQL BIG SELECTS — различия между версиями

Материал из Umicms
Перейти к:навигация, поиск
 
(не показано 7 промежуточных версий 1 участника)
Строка 1: Строка 1:
 
=Проблема=
 
=Проблема=
  
Исключение следующего вида: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay
+
Исключение следующего вида:  
  
<pre>
+
'''Неперехваченное исключение'''
 +
Ошибка: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET
 +
SQL_MAX_JOIN_SIZE=# if the SELECT is okay.
 +
Sql query: SELECT STRAIGHT_JOIN SQL_CALC_FOUND_ROWS SQL_CACHE DISTINCT h.id FROM cms3_hierarchy h, cms3_objects o ,
 +
cms3_object_content c1, cms3_object_content c2, cms3_object_content c3, cms3_object_content c4, cms3_object_content c5,
 +
cms3_object_content c6 ,cms3_permissions c3p
 +
WHERE h.obj_id = o.id AND h.is_deleted = '0' AND ((c1.obj_id = o.id AND c1.field_id = '9042' AND c1.float_val
 +
BETWEEN '0' AND '150000') AND (c2.obj_id = o.id AND c2.field_id = '9044' AND c2.float_val
 +
BETWEEN '0' AND '400') AND (c3.obj_id = o.id AND c3.field_id = '9028' AND (c3.int_val IN ('1'))) AND (c4.obj_id = o.id AND
 +
c4.field_id = '9030' AND (c4.int_val IN ('1'))) AND (c5.obj_id = o.id AND c5.field_id = '9033' AND (c5.int_val IN ('1')))
 +
AND (c6.obj_id = o.id AND c6.field_id = '9035' AND (c6.int_val IN ('1')))) AND h.rel IN ('23045') AND h.is_active = '1'
 +
AND h.type_id IN ('6') AND ((c3p.owner_id = '2374' AND c3p.rel_id = h.id AND level >= 1) OR (c3p.owner_id = '2373'
 +
AND c3p.rel_id = h.id AND level >= 1) OR (c3p.owner_id = '2373' AND c3p.rel_id = h.id AND level >= 1))
 +
ORDER BY h.ord LIMIT 0, 10
 +
=Решение=
 +
Требуется сделать то, что просят необходимо в файл mysql.php добавть следующую строку:
  
Неперехваченное исключение
+
'''mysql_query("SET SQL_BIG_SELECTS=1");'''
  
Ошибка: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay.
 
Sql query: SELECT STRAIGHT_JOIN SQL_CALC_FOUND_ROWS SQL_CACHE DISTINCT h.id FROM cms3_hierarchy h, cms3_objects o , cms3_object_content c1, cms3_object_content c2, cms3_object_content c3, cms3_object_content c4, cms3_object_content c5, cms3_object_content c6 ,cms3_permissions c3p
 
WHERE h.obj_id = o.id AND h.is_deleted = '0' AND ((c1.obj_id = o.id AND c1.field_id = '9042' AND c1.float_val
 
BETWEEN '0' AND '150000') AND (c2.obj_id = o.id AND c2.field_id = '9044' AND c2.float_val
 
BETWEEN '0' AND '400') AND (c3.obj_id = o.id AND c3.field_id = '9028' AND (c3.int_val IN ('1'))) AND (c4.obj_id = o.id AND c4.field_id = '9030' AND (c4.int_val IN ('1'))) AND (c5.obj_id = o.id AND c5.field_id = '9033' AND (c5.int_val IN ('1')))
 
AND (c6.obj_id = o.id AND c6.field_id = '9035' AND (c6.int_val IN ('1')))) AND h.rel IN ('23045') AND h.is_active = '1'
 
AND h.type_id IN ('6') AND ((c3p.owner_id = '2374' AND c3p.rel_id = h.id AND level >= 1) OR (c3p.owner_id = '2373'
 
AND c3p.rel_id = h.id AND level >= 1) OR (c3p.owner_id = '2373' AND c3p.rel_id = h.id AND level >= 1))
 
ORDER BY h.ord LIMIT 0, 10
 
</pre>
 
 
=Решение=
 
Требуется сделать то, что просят необходимо в файл mysql.php добавть следующую строку
 
 
<source lang="php">
 
<source lang="php">
 
<?php
 
<?php
Строка 34: Строка 36:
 
mysql_query("SET SESSION collation_connection = 'utf8_general_ci'");
 
mysql_query("SET SESSION collation_connection = 'utf8_general_ci'");
  
// именно эта стока решает проблему
+
// именно эта строка решает проблему
 
mysql_query("SET SQL_BIG_SELECTS=1");
 
mysql_query("SET SQL_BIG_SELECTS=1");
 
?>
 
?>
 
</source>
 
</source>
 +
[[Категория:Решение проблем и ошибок]]

Текущая версия на 20:56, 4 июня 2013

Проблема

Исключение следующего вида:

Неперехваченное исключение
Ошибка: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET
SQL_MAX_JOIN_SIZE=# if the SELECT is okay. 
Sql query: SELECT STRAIGHT_JOIN SQL_CALC_FOUND_ROWS SQL_CACHE DISTINCT h.id FROM cms3_hierarchy h, cms3_objects o ,
cms3_object_content c1, cms3_object_content c2, cms3_object_content c3, cms3_object_content c4, cms3_object_content c5, 
cms3_object_content c6 ,cms3_permissions c3p 
WHERE h.obj_id = o.id AND h.is_deleted = '0' AND ((c1.obj_id = o.id AND c1.field_id = '9042' AND c1.float_val 
BETWEEN '0' AND '150000') AND (c2.obj_id = o.id AND c2.field_id = '9044' AND c2.float_val 
BETWEEN '0' AND '400') AND (c3.obj_id = o.id AND c3.field_id = '9028' AND (c3.int_val IN ('1'))) AND (c4.obj_id = o.id AND 
c4.field_id = '9030' AND (c4.int_val IN ('1'))) AND (c5.obj_id = o.id AND c5.field_id = '9033' AND (c5.int_val IN ('1'))) 
AND (c6.obj_id = o.id AND c6.field_id = '9035' AND (c6.int_val IN ('1')))) AND h.rel IN ('23045') AND h.is_active = '1' 
AND h.type_id IN ('6') AND ((c3p.owner_id = '2374' AND c3p.rel_id = h.id AND level >= 1) OR (c3p.owner_id = '2373' 
AND c3p.rel_id = h.id AND level >= 1) OR (c3p.owner_id = '2373' AND c3p.rel_id = h.id AND level >= 1)) 
ORDER BY h.ord LIMIT 0, 10

Решение

Требуется сделать то, что просят необходимо в файл mysql.php добавть следующую строку:

mysql_query("SET SQL_BIG_SELECTS=1");
<?php
	error_reporting(~E_ALL);

	/* UMI.CMS mySQL - connection to database */
	mysql_connect('rv.mysql', 'rv_mysql', 'rcijhzvy') or die(mysql_fatal());
	mysql_select_db('rv_vestiyug') or die(mysql_fatal());

	mysql_query("SET NAMES utf8_general_ci");
	mysql_query("SET CHARSET utf8");

	mysql_query("SET CHARACTER SET utf8");
	mysql_query("SET SESSION collation_connection = 'utf8_general_ci'");

	// именно эта строка решает проблему
	mysql_query("SET SQL_BIG_SELECTS=1");
?>