Juan Eduardo,
Great to hear about you :)
One thing you must know is that i can run this query from a mysql client,
without the insert part.
The problem only happens when is run from the J2EE (Websphere - drp)
application.
I run the query as you asked, here are some results:
+------------+-----------------+-------------+---+---------+---------+----------+-----------------------+
| drp_id_sku | drp_id_deposito | drp_volumen | 1 | drp_dia | drp_mes | drp_anno
| drp_fecha_dia_cargado |
+------------+-----------------+-------------+---+---------+---------+----------+-----------------------+
| 161 | 35 | 1.60000 | 1 | 12 | 5 | 2007
| 2007-05-12 00:00:00 |
| 161 | 20 | 1.50000 | 1 | 2 | 5 | 2007
| 2007-05-02 00:00:00 |
| 161 | 22 | 0.20000 | 1 | 11 | 5 | 2007
| 2007-05-11 00:00:00 |
| 161 | 13 | 0.20000 | 1 | 7 | 5 | 2007
| 2007-05-07 00:00:00 |
| 161 | 16 | 2.20000 | 1 | 9 | 5 | 2007
| 2007-05-09 00:00:00 |
| 161 | 35 | 4.00000 | 1 | 3 | 5 | 2007
| 2007-05-03 00:00:00 |
| 161 | 16 | 24.00000 | 1 | 4 | 5 | 2007
| 2007-05-04 00:00:00 |
| 161 | 2 | 0.20000 | 1 | 9 | 5 | 2007
| 2007-05-09 00:00:00 |
| 163 | 35 | 16.60000 | 1 | 11 | 5 | 2007
| 2007-05-11 00:00:00 |
| 163 | 36 | 2.20000 | 1 | 4 | 5 | 2007
| 2007-05-04 00:00:00 |
| 163 | 16 | -2.40000 | 1 | 8 | 5 | 2007
| 2007-05-08 00:00:00 |
| 163 | 35 | 8.80000 | 1 | 2 | 5 | 2007
| 2007-05-02 00:00:00 |
| 163 | 32 | 13.00000 | 1 | 8 | 5 | 2007
| 2007-05-08 00:00:00 |
| 163 | 34 | 7.60000 | 1 | 7 | 5 | 2007
| 2007-05-07 00:00:00 |
And the EXPLAIN:
+-------+------+---------------+------+---------+------+-------+-----------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+-------+------+---------------+------+---------+------+-------+-----------------------+
| d | ALL | NULL | NULL | NULL | NULL | 37 | Using
temporary |
| md | ALL | NULL | NULL | NULL | NULL | 32 | Using where
|
| vv | ALL | NULL | NULL | NULL | NULL | 12694 | Using where
|
| s | ALL | NULL | NULL | NULL | NULL | 104 |
|
| ms | ALL | NULL | NULL | NULL | NULL | 184 | Using where;
Distinct |
+-------+------+---------------+------+---------+------+-------+-----------------------+
5 rows in set (0.00 sec)
Any ideas?
----- "Juan Eduardo Moreno" <[EMAIL PROTECTED]> escribió:
> 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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
