Hi Patricio,
Some options are to prevent programmers/users make a "bad" queries into
the database' SQL_BIG_SELECTS = 0 | 1
The documentation say :
"If set to 0, MySQL will abort if a SELECT is attempted that probably will
take a very long time. This is useful when an inadvisable WHERE statement
has been issued. A big query is defined as a SELECT that probably will have
to examine more than max_join_size rows. The default value for a new
connection is 1 (which will allow all SELECT statements)."
For testing try this :
1)
SET SESSION SQL_BIG_SELECTS=1;
SET SESSION SQL_MAX_JOIN_SIZE=DEFAULT;
Run the query;
2) Send your results and explain of query ( explain select ....)
Regards,
Juan
On 5/22/07, Patricio A. Bruna <[EMAIL PROTECTED]> wrote:
Friends,
im having troubles with the following query:
-------------------------------------------------------
INSERT drp_volumen_venta_diaria_deposito (drp_id_sku, drp_id_deposito,
drp_volumen, drp_es_cct, drp_dia, drp_mes, drp_anno, drp_fecha_dia_cargado )
SELECT DISTINCT s.drp_id_sku, d.drp_id_deposito, vv.drp_volumen, 1,
vv.drp_dia, vv.drp_mes, vv.drp_anno,vv.drp_fecha_dia_cargado
FROM drp_volumen_venta_diaria_tmp vv, drp_sku s, drp_deposito d,
drp_mapeo_sku ms, drp_mapeo_deposito md
WHERE vv.drp_codigo_sku = ms.drp_cod_sku_odyssey AND ms.drp_cod_sku_sap =
s.drp_codigo_sku
AND REPLACE(UCASE(TRIM(vv.drp_codigo_deposito)),' ','')=
REPLACE(UCASE(TRIM(md.drp_alias_deposito_odyssey)),' ','') AND
REPLACE(UCASE(TRIM(md.drp_alias_deposito_sap)),' ','') =
REPLACE(UCASE(TRIM(d.drp_alias_deposito)),' ','')
AND
CONCAT(vv.drp_anno,if(LENGTH(vv.drp_mes)=1,CONCAT('0',vv.drp_mes),
vv.drp_mes)
, if(LENGTH(vv.drp_dia)=1,CONCAT('0',vv.drp_dia),vv.drp_dia) ) >= '
20070501 '
AND
CONCAT(vv.drp_anno,if(LENGTH(vv.drp_mes)=1,CONCAT('0',vv.drp_mes),
vv.drp_mes)
, if(LENGTH(vv.drp_dia)=1,CONCAT('0',vv.drp_dia),vv.drp_dia) ) <= '
20070515 ';
-------------------------------------------------------------
I run this query in 2 servers, devel and production, which have the same
data.
I run the query in devel without problems, but in production is not
working and give me this error:
" The SELECT would examine more rows than MAX_JOIN_SIZE. Check your WHERE
and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is ok
"
The value of MAX_JOIN_SIZE is set at: 4294967295 and SET_SQL_BIG_SELECTS
is 1.
MySQL version is 4.0.18 over Red Hat 3.
any idea why this isnt working?
thanks
