Bug #39844 Query Crash Mysql Server 5.0.67
Submitted: 3 Oct 2008 19:36 Modified: 1 Dec 2008 20:10
Reporter: giovanni silva Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.0+ OS:Any (MS Windows SP2 - XP 5.1.2600, Linux)
Assigned to: Gleb Shchepa CPU Architecture:Any
Triage: Triaged: D1 (Critical)

[3 Oct 2008 19:36] giovanni silva
Description:
Select Query Crash Mysql Server 5.0.67.

Select:
SELECT 
codigo AS codigo, 
(SELECT nota_fiscal.codigo FROM nota_fiscal INNER JOIN detalhe_nota_fiscal ON
nota_fiscal.codigo = detalhe_nota_fiscal.nota_fiscal WHERE detalhe_nota_fiscal.codigo =
movimentacao_estoque.detalhe_nota_fiscal AND nota_fiscal.tipo = 'PDV' GROUP BY
nota_fiscal.codigo) AS codigonotapdv, 
produto AS produto, 
(select descricao from produto where produto.codigo = movimentacao_estoque.produto) AS
descproduto, 
data_validade AS datadevalidade, 
lote AS lote, 
(select unidade_medida.descricao from unidade_medida where unidade_medida.codigo =
unidade_medida) AS descunidadedemedida, 
quantidade AS quantidade, 
data_operacao AS dataoperacao, 
tipo AS tipooperacao,
ativo AS ativo
FROM movimentacao_estoque ORDER BY codigo DESC  LIMIT 0 , 200;

Direct Link for SQL SOURCE
 - http://www.codesoftwares.com.br/request.php?1
Link for SQL SOURCE
 - http://www.codesoftwares.com.br/download.php?list.2

Log:
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=9437184
read_buffer_size=65536
max_used_connections=3
max_connections=100
threads_connected=3
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 41216 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 033FA8F8=SELECT 
(SELECT nota_fiscal.codigo FROM nota_fiscal INNER JOIN detalhe_nota_fiscal ON
nota_fiscal.codigo = detalhe_nota_fiscal.nota_fiscal WHERE detalhe_nota_fiscal.codigo =
movimentacao_estoque.detalhe_nota_fiscal AND nota_fiscal.tipo = 'PDV' GROUP BY
nota_fiscal.codigo) AS codigonotapdv, 
(select descricao from produto where produto.codigo = movimentacao_estoque.produto) AS
descproduto, 
(select unidade_medida.descricao from unidade_medida where unidade_medida.codigo =
unidade_medida) AS descunidadedemedida, 
codigo AS codigo, 
produto AS produto, 
data_validade AS datadevalidade, 
lote AS lote, 
quantidade AS quantidade, 
data_operacao AS dataoperacao, 
tipo AS tipooperacao,
ativo AS ativo
FROM movimentacao_estoque ORDER BY codigo ASC  LIMIT 0 , 200
thd->thread_id=4
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

-My Machine:
Pentium 4  - 2.4 Ghz
1 GB Ram
Windows XP SP2

How to repeat:
It above installs the data base through one of links. It executes the SQL that cause the
error (above).
[3 Oct 2008 19:37] giovanni silva
Mysql.ini Config File

Attachment: my.ini (application/octet-stream, text), 8.99 KiB.

[3 Oct 2008 21:21] Giuseppe Maxia
Bug#39730 has been marked as duplicated of this one.
[7 Oct 2008 7:42] Sveta Smirnova
Thank you for the report.

Verified as described. Bug exists only in 5.0 tree.
[7 Oct 2008 7:46] Sveta Smirnova
test case

Attachment: bug39844.tar.gz (application/x-gzip, text), 181.09 KiB.

[7 Oct 2008 18:12] giovanni silva
In version 5.1 this bug exits, in version 6, it exists, but nor always.
[7 Oct 2008 19:17] giovanni silva
Exists some patch or workarround?
[16 Oct 2008 16:38] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/56402

2707 Gleb Shchepa	2008-10-16
      Bug #39844: Query Crash Mysql Server 5.0.67
      
      Server crashed during a sort order optimization
      of a dependent subquery:
      
      SELECT
          (SELECT t1.a FROM t1, t2
            WHERE t1.a = t2.b AND t2.a = t3.c
            ORDER BY t1.a)
        FROM t3;
      
      
      Bitmap of tables, that the reference to outer table
      column uses, in addition to the regular table bit
      has the OUTER_REF_TABLE_BIT bit set.
      The only_eq_ref_tables function traverses this map
      bit by bit simultaneously with join->map2table list.
      Obviously join->map2table never contains an entry
      for the OUTER_REF_TABLE_BIT pseudo-table, so the
      server crashed there.
      
      
      The only_eq_ref_tables function has been modified
      to traverse regular table bits only like the
      update_depend_map function (resetting of the
      OUTER_REF_TABLE_BIT there is enough, but
      resetting of the whole set of PSEUDO_TABLE_BITS
      is used there for sure).
[24 Oct 2008 8:42] Bugs System
Pushed into 5.0.72  (revid:gshchepa@mysql.com-20081016163717-ah4x4miph2tgotsy) (version source revid:kgeorge@mysql.com-20081020132607-0xfdc16b9p1xrd83) (pib:5)
[24 Oct 2008 20:06] Paul Dubois
Noted in 5.0.72 changelog.

The server could crash during a sort-order optimization of a
dependent subquery.

Setting report to NDI pending push into 5.1.x, 6.0.x.
[10 Nov 2008 10:52] Bugs System
Pushed into 6.0.8-alpha  (revid:gshchepa@mysql.com-20081016163717-ah4x4miph2tgotsy) (version source revid:gshchepa@mysql.com-20081016181342-093zyzjvqd7yn38l) (pib:5)
[10 Nov 2008 11:36] Bugs System
Pushed into 5.1.30  (revid:gshchepa@mysql.com-20081016163717-ah4x4miph2tgotsy) (version source revid:gshchepa@mysql.com-20081016180431-oxf0wjcpulgzhytw) (pib:5)
[10 Nov 2008 19:11] Paul Dubois
Note in 5.1.31, 6.0.8 changelogs.
[11 Nov 2008 16:28] Paul Dubois
6.0.9 changelog, not 6.0.8.
[1 Dec 2008 13:29] giovanni silva
the downloaded version 5.1.30 and managed to repeat the error, just run the query multiple times.
[1 Dec 2008 13:39] Georgi Kodinov
> the downloaded version 5.1.30 and managed to repeat the error, just run the query multiple
times.

Note that the fix is not in 5.1.30 : it's in 5.1.31.
[1 Dec 2008 20:10] giovanni silva
there is a launch date for these versions with the fix?
[19 Jan 2009 11:25] Bugs System
Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090108105244-8opp3i85jw0uj5ib) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)
[19 Jan 2009 13:03] Bugs System
Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)
[19 Jan 2009 16:09] Bugs System
Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)