| Bug #16047 | Lost connection to MySQL server during query | ||
|---|---|---|---|
| Submitted: | 28 Dec 2005 18:28 | Modified: | 1 Mar 2006 16:00 |
| Reporter: | Vinicius Mommensohn | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.0.16 | OS: | Linux (Fedora Core 3) |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
[28 Dec 2005 18:46]
MySQL Verification Team
I was unable to repeat with current source server:
mysql> SELECT SQL_CALC_FOUND_ROWS id, cache, url,
-> TRIM(SUBSTRING(titulo, 1, 90)) as preview_titulo,
-> SUBSTRING(conteudo, (LOCATE('$palavras[0]', conteudo) - 100), 200) as
-> preview_conteudo, meta_descricao,
-> LEFT(conteudo, 30) as preview_inicio, (
-> (0.3 * MATCH (conteudo) AGAINST ('$pesquisa' IN BOOLEAN MODE)) +
-> (1.2 * MATCH (url) AGAINST ('$pesquisa' IN BOOLEAN MODE)) +
-> (1.2 * MATCH (titulo) AGAINST ('$pesquisa' IN BOOLEAN MODE)) +
-> (2 * MATCH (meta_keywords) AGAINST ('$pesquisa' IN BOOLEAN MODE)) +
-> (1.2 * MATCH (meta_descricao) AGAINST ('$pesquisa' IN BOOLEAN MODE)) +
-> (0.05 * referencias) +
-> (0.05 * acessos)) as relevancia,
-> CONCAT_WS(' ', SUBSTRING_INDEX(SUBSTRING_INDEX(url, '/', 1), '.', -4),
-> LEFT(titulo, 25)) as preview_diferencas
-> FROM view_principal
-> WHERE MATCH (conteudo) AGAINST ('$pesquisa' IN BOOLEAN MODE) OR
-> MATCH (url) AGAINST ('$pesquisa' IN BOOLEAN MODE) OR
-> MATCH (meta_descricao) AGAINST ('$pesquisa' IN BOOLEAN MODE) OR
-> MATCH (meta_keywords) AGAINST ('$pesquisa' IN BOOLEAN MODE) OR
-> MATCH (titulo) AGAINST ('$pesquisa' IN BOOLEAN MODE)
-> GROUP BY preview_diferencas
-> HAVING relevancia > 0
-> ORDER BY relevancia DESC, id
-> LIMIT 2,0;
Empty set (0.03 sec)
mysql> select version();
+--------------+
| version() |
+--------------+
| 5.0.19-debug |
+--------------+
1 row in set (0.00 sec)
[28 Dec 2005 19:12]
Vinicius Mommensohn
Do you need the inserts to reproduce?!?
[28 Dec 2005 19:29]
MySQL Verification Team
Yes please attach a complete test case with insert and query offended. Thanks in advance.
[28 Dec 2005 19:48]
Vinicius Mommensohn
I tried to insert the file, but your system only permit to send files less than 200kb.. My SQLs, zipped, is up to 7 MB!!! So, i uploaded it in http://www.popmax.com.br/arquivos/principal.zip Tks!!
[28 Dec 2005 19:52]
Vinicius Mommensohn
My query is:
SELECT SQL_CALC_FOUND_ROWS id, cache, url, TRIM(SUBSTRING(titulo, 1, 90)) as preview_titulo, SUBSTRING(conteudo, (LOCATE('www.teracom.com.br', conteudo) - 100), 200) as preview_conteudo, meta_descricao, LEFT(conteudo, 30) as preview_inicio, ((0.3 * MATCH (conteudo) AGAINST ('+www.teracom.com.br' IN BOOLEAN MODE)) + (1.2 * MATCH (url) AGAINST ('+www.teracom.com.br' IN BOOLEAN MODE)) + (1.2 * MATCH (titulo) AGAINST ('+www.teracom.com.br' IN BOOLEAN MODE)) + (2 * MATCH (meta_keywords) AGAINST ('+www.teracom.com.br' IN BOOLEAN MODE)) + (1.2 * MATCH (meta_descricao) AGAINST ('+www.teracom.com.br' IN BOOLEAN MODE)) + (0.05 * referencias) + (0.05 * acessos)) as relevancia, CONCAT_WS(' ', SUBSTRING_INDEX(SUBSTRING_INDEX(url, '/', 1), '.', -4), LEFT(titulo, 25)) as preview_diferencas FROM view_principal WHERE MATCH (conteudo) AGAINST ('+www.teracom.com.br' IN BOOLEAN MODE) OR MATCH (url) AGAINST ('+www.teracom.com.br' IN BOOLEAN MODE) OR MATCH (meta_descricao) AGAINST ('+www.teracom.com.br' IN BOOLEAN MODE) OR MATCH (meta_keywords) AGAINST ('+www.teracom.com.br' IN BOOLEAN MODE) OR MATCH (titulo) AGAINST ('+www.teracom.com.br' IN BOOLEAN MODE) GROUP BY preview_diferencas HAVING relevancia > 0 ORDER BY relevancia DESC, id LIMIT 0, 10;
The result:
Lost connection to MySQL server during query
[28 Dec 2005 20:23]
MySQL Verification Team
Thank you for the feedback. Most probably this issue is already fixed on
our source repository:
miguel@hegel:~/dbs/5.0> bin/mysql -uroot integrador;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.19-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SELECT SQL_CALC_FOUND_ROWS id, cache, url, TRIM(SUBSTRING(titulo, 1, 90)) as
-> preview_titulo, SUBSTRING(conteudo, (LOCATE('www.teracom.com.br', conteudo) -
-> 100), 200) as preview_conteudo, meta_descricao, LEFT(conteudo, 30) as
-> preview_inicio, ((0.3 * MATCH (conteudo) AGAINST ('+www.teracom.com.br' IN
-> BOOLEAN MODE)) + (1.2 * MATCH (url) AGAINST ('+www.teracom.com.br' IN BOOLEAN
-> MODE)) + (1.2 * MATCH (titulo) AGAINST ('+www.teracom.com.br' IN BOOLEAN MODE))
-> + (2 * MATCH (meta_keywords) AGAINST ('+www.teracom.com.br' IN BOOLEAN MODE)) +
-> (1.2 * MATCH (meta_descricao) AGAINST ('+www.teracom.com.br' IN BOOLEAN MODE)) +
-> (0.05 * referencias) + (0.05 * acessos)) as relevancia, CONCAT_WS(' ',
-> SUBSTRING_INDEX(SUBSTRING_INDEX(url, '/', 1), '.', -4), LEFT(titulo, 25)) as
-> preview_diferencas FROM view_principal WHERE MATCH (conteudo) AGAINST
-> ('+www.teracom.com.br' IN BOOLEAN MODE) OR MATCH (url) AGAINST
-> ('+www.teracom.com.br' IN BOOLEAN MODE) OR MATCH (meta_descricao) AGAINST
-> ('+www.teracom.com.br' IN BOOLEAN MODE) OR MATCH (meta_keywords) AGAINST
-> ('+www.teracom.com.br' IN BOOLEAN MODE) OR MATCH (titulo) AGAINST
-> ('+www.teracom.com.br' IN BOOLEAN MODE) GROUP BY preview_diferencas HAVING
-> relevancia > 0 ORDER BY relevancia DESC, id LIMIT 0, 10;
+-------+-------------------+------------------------------------------------ <cut>
| id | cache | url <cut>
+-------+-------------------+------------------------------------------------ <cut>
| 3708 | 1135613385.544102 | webmail.teracom.com.br <cut>
| 6607 | 1135617223.416330 | www.popmax.com.br/artigos.php?canal=Teracom <cut>
| 1996 | 1135611761.925466 | www.rccmaringa.com.br/index.php?sub=ministerios <cut>
| 1019 | 1135611364.321636 | www.silkestamparia.com.br/contato.htm <cut>
| 545 | 1135610983.160078 | www.depositotiradentes.com.br/a%20empresa.htm <cut>
| 1116 | 1135611383.858371 | www.vidrofix.com.br/projetal.php <cut>
| 10706 | 1135623187.680017 | www.popmax.com.br/popmax.php?conteudo=eunafoto& <cut>
| 552 | 1135610986.844659 | www.casadaslixas.com.br/empresa/dados.htm <cut>
| 2512 | 1135611863.771301 | www.comunidade.org.br/b200211.htm <cut>
| 3295 | 1135612789.710743 | www.singramar.com.br/associados/associados_most <cut>
+-------+-------------------+-------------------------------------------------<cut>
10 rows in set (16.01 sec)
mysql>
[29 Dec 2005 10:21]
Vinicius Mommensohn
I tried with mysql 5.0.17 and it's still getting this lost connection... Only new versions of mysql has fixed that issue?
[29 Dec 2005 16:28]
Vinicius Mommensohn
This is what i got:
[root@server03 bin]# ./mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.17
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use integrador;
Database changed
mysql> SELECT SQL_CALC_FOUND_ROWS id, cache, url, TRIM(SUBSTRING(titulo, 1, 90)) as preview_titulo, SUBSTRING(conteudo, (LOC
ATE('www.teracom.com.br', conteudo) - 100), 200) as preview_conteudo, meta_descricao, LEFT(conteudo, 30) as preview_inicio, ((0.3 * MATCH (conteudo) AGAINST ('+www.teracom.com.br' IN BOOLEAN MODE)) + (1.2 * MATCH (url) AGAINST ('+www.teracom.com.br
' IN BOOLEAN MODE)) + (1.2 * MATCH (titulo) AGAINST ('+www.teracom.com.br' IN BOOLEAN MODE)) + (0.05 * referencias) + (0.05 * acessos)) as relevancia, CONCAT_WS(' ', SUBSTRING_INDEX(SUBSTRING_INDEX(url, '/', 1), '.', -4), LEFT(titulo, 25)) as preview_diferencas FROM view_principal WHERE MATCH (conteudo) AGAINST ('+www.teracom.com.br' IN BOOLEAN MODE) OR MATCH (url) AGAINST ('+www.teracom.com.br' IN BOOLEAN MODE) OR MATCH (titulo) AGAINST ('+www.teracom.com.br' IN BOOLEAN MODE) GROUP BY preview_diferencas HAVING relevancia > 0 ORDER BY relevancia DESC, id LIMIT 0, 10;
ERROR 2013 (HY000): Lost connection to MySQL server during query
----------
But, if i try this query again, it works fine:
mysql> SELECT SQL_CALC_FOUND_ROWS id, cache, url, TRIM(SUBSTRING(titulo, 1, 90)) as preview_titulo, SUBSTRING(conteudo, (LOCATE('www.teracom.com.br', conteudo) - 100), 200) as preview_conteudo, meta_descricao, LEFT(conteudo, 30) as preview_inicio, ((0.3 * MATCH (conteudo) AGAINST ('+www.teracom.com.br' IN BOOLEAN MODE)) + (1.2 * MATCH (url) AGAINST ('+www.teracom.com.br' IN BOOLEAN MODE)) + (1.2 * MATCH (titulo) AGAINST ('+www.teracom.com.br' IN BOOLEAN MODE)) + (0.05 * referencias) + (0.05 * acessos)) as relevancia, CONCAT_WS(' ', SUBSTRING_INDEX(SUBSTRING_INDEX(url, '/', 1), '.', -4), LEFT(titulo, 25)) as preview_diferencas FROM view_principal WHERE MATCH (conteudo) AGAINST ('+www.teracom.com.br' IN BOOLEAN MODE) OR MATCH (url) AGAINST ('+www.teracom.com.br' IN BOOLEAN MODE) OR MATCH (titulo) AGAINST ('+www.teracom.com.br' IN BOOLEAN MODE) GROUP BY preview_diferencas HAVING relevancia > 0 ORDER BY relevancia DESC, id LIMIT 0, 10;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 1
Current database: integrador
+------+-------------------+------------------------------------------------------+------------------------------------+----------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+------------+-----------------------------------------------------+
| id | cache | url | preview_titulo | preview_conteudo | meta_descricao | preview_inicio | relevancia | preview_diferencas |
+------+-------------------+------------------------------------------------------+------------------------------------+----------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+------------+-----------------------------------------------------+
...
+------+-------------------+------------------------------------------------------+------------------------------------+----------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+------------+-----------------------------------------------------+
10 rows in set (7.15 sec)
mysql>
[29 Dec 2005 18:09]
Vinicius Mommensohn
A new thing... when i try for the second time the query, the result is ALLWAYS 1760 rows (even with other keywords - www.popmax.com.br or www.teracom.com.br or www.eritonmotos.com.br ...) It's really strange!!
[29 Dec 2005 18:29]
MySQL Verification Team
I tested again and I was unable to repeat. For to be sure it is a fix already done in our source I will download the 5.0.17 source and compile on Fedora Core 4 and run the test. All my tests were done on Suse 10.
[30 Dec 2005 2:17]
MySQL Verification Team
Thank you for the bug report and feedback.
I was able to repeat with 5.0.17 and I will test with latest server source.
My wrong test was done when creating the view without the:
CREATE ALGORITHM=UNDEFINED DEFINER=`miguel`@`%` SQL SECURITY DEFINER VIEW
so when I did according your original test:
mysql> CREATE ALGORITHM=UNDEFINED DEFINER=`miguel`@`%` SQL SECURITY DEFINER VIEW
-> `integrador`.`view_principal` AS select `integrador`.`principal`.`id` AS `id`,
-> `integrador`.`principal`.`url` AS `url`,
-> `integrador`.`principal`.`cache` AS `cache`,
-> `integrador`.`principal`.`titulo` AS `titulo`,
-> `integrador`.`principal`.`conteudo` AS `conteudo`,
-> `integrador`.`principal`.`referencias` AS `referencias`,
-> `integrador`.`principal`.`acessos` AS `acessos`,
-> `integrador`.`principal`.`meta_keywords` AS `meta_keywords`,
-> `integrador`.`principal`.`meta_descricao` AS `meta_descricao`
-> from `integrador`.`principal`;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT SQL_CALC_FOUND_ROWS id, cache, url, TRIM(SUBSTRING(titulo, 1, 90)) as
-> preview_titulo, SUBSTRING(conteudo, (LOCATE('www.teracom.com.br', conteudo) -
-> 100), 200) as preview_conteudo, meta_descricao, LEFT(conteudo, 30) as
-> preview_inicio, ((0.3 * MATCH (conteudo) AGAINST ('+www.teracom.com.br' IN
-> BOOLEAN MODE)) + (1.2 * MATCH (url) AGAINST ('+www.teracom.com.br' IN BOOLEAN
-> MODE)) + (1.2 * MATCH (titulo) AGAINST ('+www.teracom.com.br' IN BOOLEAN MODE))
-> + (2 * MATCH (meta_keywords) AGAINST ('+www.teracom.com.br' IN BOOLEAN MODE)) +
-> (1.2 * MATCH (meta_descricao) AGAINST ('+www.teracom.com.br' IN BOOLEAN MODE)) +
-> (0.05 * referencias) + (0.05 * acessos)) as relevancia, CONCAT_WS(' ',
-> SUBSTRING_INDEX(SUBSTRING_INDEX(url, '/', 1), '.', -4), LEFT(titulo, 25)) as
-> preview_diferencas FROM view_principal WHERE MATCH (conteudo) AGAINST
-> ('+www.teracom.com.br' IN BOOLEAN MODE) OR MATCH (url) AGAINST
-> ('+www.teracom.com.br' IN BOOLEAN MODE) OR MATCH (meta_descricao) AGAINST
-> ('+www.teracom.com.br' IN BOOLEAN MODE) OR MATCH (meta_keywords) AGAINST
-> ('+www.teracom.com.br' IN BOOLEAN MODE) OR MATCH (titulo) AGAINST
-> ('+www.teracom.com.br' IN BOOLEAN MODE) GROUP BY preview_diferencas HAVING
-> relevancia > 0 ORDER BY relevancia DESC, id LIMIT 0, 10;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
Version: '5.0.17' socket: '/tmp/mysql.sock' port: 3306 Source distribution
mysqld got signal 11;
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=33554432
read_buffer_size=258048
max_used_connections=1
max_connections=100
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 83567 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd=0x9c340b0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xaa3f6f14, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x815baa7
0x177420
0xc8
0x81584de
0x811ea6e
0x80e6fc9
0x80f1953
0x81b676e
0x81a582c
0x81aa834
0x81b0594
0x81bf392
0x81c1213
0x81c197a
0x81729ee
0x817963b
0x8179c74
0x817b74e
0xc0fb80
0xa5fdee
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x9c5a260 = SELECT SQL_CALC_FOUND_ROWS id, cache, url, TRIM(SUBSTRING(titulo, 1, 90)) as
preview_titulo, SUBSTRING(conteudo, (LOCATE('www.teracom.com.br', conteudo) -
100), 200) as preview_conteudo, meta_descricao, LEFT(conteudo, 30) as
preview_inicio, ((0.3 * MATCH (conteudo) AGAINST ('+www.teracom.com.br' IN
BOOLEAN MODE)) + (1.2 * MATCH (url) AGAINST ('+www.teracom.com.br' IN BOOLEAN
MODE)) + (1.2 * MATCH (titulo) AGAINST ('+www.teracom.com.br' IN BOOLEAN MODE))
+ (2 * MATCH (meta_keywords) AGAINST ('+www.teracom.com.br' IN BOOLEAN MODE)) +
(1.2 * MATCH (meta_descricao) AGAINST ('+www.teracom.com.br' IN BOOLEAN MODE)) +
(0.05 * referencias) + (0.05 * acessos)) as relevancia, CONCAT_WS(' ',
SUBSTRING_INDEX(SUBSTRING_INDEX(url, '/', 1), '.', -4), LEFT(titulo, 25)) as
preview_diferencas FROM view_principal WHERE MATCH (conteudo) AGAINST
('+www.teracom.com.br' IN BOOLEAN MODE) OR MATCH (url) AGAINST
('+www.teracom.com.br' IN BOOLEAN MODE) OR MATCH (meta_descricao) AGAINST
('+www.teracom.com.br' IN BOOLEAN MODE) OR MATCH (
thd->thread_id=6
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.
[miguel@hegel 5.0]$
[30 Dec 2005 2:51]
MySQL Verification Team
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.
If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information
about accessing the source trees is available at
http://www.mysql.com/doc/en/Installing_source_tree.html
Additional info:
[miguel@hegel 5.0]$ bin/mysql --defaults-file=/home/miguel/dbs/5.0/var/my.cnf -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.19-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> grant all on *.* to 'miguel'@'%' identified by 'tester';
Query OK, 0 rows affected (0.01 sec)
mysql> grant all on *.* to 'miguel'@'localhost' identified by 'tester';
Query OK, 0 rows affected (0.00 sec)
mysql> source /home/miguel/dbs/5.0/var/integrador.sql
<cut>
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE ALGORITHM=UNDEFINED DEFINER=`miguel`@`%` SQL SECURITY DEFINER VIEW
-> `integrador`.`view_principal` AS select `integrador`.`principal`.`id` AS `id`,
-> `integrador`.`principal`.`url` AS `url`,
-> `integrador`.`principal`.`cache` AS `cache`,
-> `integrador`.`principal`.`titulo` AS `titulo`,
-> `integrador`.`principal`.`conteudo` AS `conteudo`,
-> `integrador`.`principal`.`referencias` AS `referencias`,
-> `integrador`.`principal`.`acessos` AS `acessos`,
-> `integrador`.`principal`.`meta_keywords` AS `meta_keywords`,
-> `integrador`.`principal`.`meta_descricao` AS `meta_descricao`
-> from `integrador`.`principal`;
Query OK, 0 rows affected (0.05 sec)
mysql> exit
Bye
[miguel@hegel 5.0]$
[miguel@hegel 5.0]$ bin/mysql --defaults-file=/home/miguel/dbs/5.0/var/my.cnf -umiguel -p integrador
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.19-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SELECT SQL_CALC_FOUND_ROWS id, cache, url, TRIM(SUBSTRING(titulo, 1, 90)) as
-> preview_titulo, SUBSTRING(conteudo, (LOCATE('www.teracom.com.br', conteudo) -
-> 100), 200) as preview_conteudo, meta_descricao, LEFT(conteudo, 30) as
-> preview_inicio, ((0.3 * MATCH (conteudo) AGAINST ('+www.teracom.com.br' IN
-> BOOLEAN MODE)) + (1.2 * MATCH (url) AGAINST ('+www.teracom.com.br' IN BOOLEAN
-> MODE)) + (1.2 * MATCH (titulo) AGAINST ('+www.teracom.com.br' IN BOOLEAN MODE))
-> + (2 * MATCH (meta_keywords) AGAINST ('+www.teracom.com.br' IN BOOLEAN MODE)) +
-> (1.2 * MATCH (meta_descricao) AGAINST ('+www.teracom.com.br' IN BOOLEAN MODE)) +
-> (0.05 * referencias) + (0.05 * acessos)) as relevancia, CONCAT_WS(' ',
-> SUBSTRING_INDEX(SUBSTRING_INDEX(url, '/', 1), '.', -4), LEFT(titulo, 25)) as
-> preview_diferencas FROM view_principal WHERE MATCH (conteudo) AGAINST
-> ('+www.teracom.com.br' IN BOOLEAN MODE) OR MATCH (url) AGAINST
-> ('+www.teracom.com.br' IN BOOLEAN MODE) OR MATCH (meta_descricao) AGAINST
-> ('+www.teracom.com.br' IN BOOLEAN MODE) OR MATCH (meta_keywords) AGAINST
-> ('+www.teracom.com.br' IN BOOLEAN MODE) OR MATCH (titulo) AGAINST
-> ('+www.teracom.com.br' IN BOOLEAN MODE) GROUP BY preview_diferencas HAVING
-> relevancia > 0 ORDER BY relevancia DESC, id LIMIT 0, 10;
<cut>
-+-----------------------------------------------------+
10 rows in set (27.44 sec)
mysql> select version();
+--------------+
| version() |
+--------------+
| 5.0.19-debug |
+--------------+
1 row in set (0.01 sec)
mysql>
[4 Jan 2006 17:38]
Vinicius Mommensohn
Hello Miguel,
I changed the server to 5.0.19-nightly-20060103 version and the query works fine now... But another problem now occurs!
I create a stored procedure and i'm getting Lost connection again. That's my S.P.
--- SP stats ---
CREATE PROCEDURE `integrador`.`pesquisa`(IN palavras VARCHAR(255), OUT titulo VARCHAR(255), OUT url VARCHAR(255), OUT descricao VARCHAR(255), OUT cache VARCHAR(255))
BEGIN
DECLARE ini, qtde, x, v_id INT;
DECLARE v_relevancia REAL;
DECLARE v_url, v_titulo, v_descricao, v_conteudo, v_diferencas VARCHAR(255);
DECLARE v_cache VARCHAR(20);
DECLARE cur_1 CURSOR FOR SELECT SQL_CALC_FOUND_ROWS id, cache, url, meta_descricao,
TRIM(SUBSTRING(titulo, 1, 90)) as preview_titulo,
SUBSTRING(conteudo, (LOCATE(palavras, conteudo) - 100), 200) as preview_conteudo,
CONCAT_WS(' ', SUBSTRING_INDEX(SUBSTRING_INDEX(url, '/', 1), '.', -4), LEFT(titulo, 30)) as preview_diferencas,
(0.3 * MATCH (conteudo) AGAINST (palavras IN BOOLEAN MODE)) +
(1.2 * MATCH (url) AGAINST (palavras IN BOOLEAN MODE)) +
(1.2 * MATCH (titulo) AGAINST (palavras IN BOOLEAN MODE)) +
(2 * MATCH (meta_keywords) AGAINST (palavras IN BOOLEAN MODE)) +
(0.3 * MATCH (meta_descricao) AGAINST (palavras IN BOOLEAN MODE)) +
(0.05 * referencias) +
(0.05 * acessos) as relevancia
FROM view_principal
WHERE MATCH (conteudo) AGAINST (palavras IN BOOLEAN MODE) OR
MATCH (url) AGAINST (palavras IN BOOLEAN MODE) OR
MATCH (meta_descricao) AGAINST (palavras IN BOOLEAN MODE) OR
MATCH (meta_keywords) AGAINST (palavras IN BOOLEAN MODE) OR
MATCH (titulo) AGAINST (palavras IN BOOLEAN MODE)
GROUP BY preview_diferencas
HAVING relevancia > 0
ORDER BY relevancia DESC, id
LIMIT 1000;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET x = qtde;
SET ini = 1;
SET qtde = 10;
SET x = 1;
OPEN cur_1;
REPEAT
FETCH cur_1 INTO v_id, v_cache, v_url, v_descricao, v_titulo, v_conteudo, v_diferencas, v_relevancia;
SET url = v_url;
SET descricao = v_descricao;
SET titulo = v_titulo;
SET cache = v_cache;
SET x = x + 1;
UNTIL x < qtde
END REPEAT;
CLOSE cur_1;
END
--- SP end ---
Because you know the problem, i decided to write it to you, in this bug.
I call the SP that way:
--- SQL query ---
call pesquisa ('popmax.com.br', @titulo, @url, @descricao, @cache);
---
Then i get the lost connection!!!
Is that a related problem?
[4 Jan 2006 17:49]
Vinicius Mommensohn
Sorry... in 5.0.19-nightly-20060103 the problem ocorrs in the select too. The bug was not fixed in that snapshot?
[4 Jan 2006 18:21]
MySQL Verification Team
That source should have the same source I tested. Could you please try to test on your side, creating the view without the definer clasue: CREATE ALGORITHM=UNDEFINED DEFINER=`popmax`@`%` so create view...... thanks in advance.
[4 Jan 2006 20:03]
Vinicius Mommensohn
I remove the view and both (sql query and stored procedure) still gets the same problem!!!
When i call the SP with "call pesquisa ('popmax', @vtitulo, @vurl, @vdescricao, @vcache);" the lost connection occurs with error nr. 2013.
Sometimes query goes success... but it allways returns 1760 rows in the result set.
[9 Jan 2006 10:56]
Vinicius Mommensohn
Hi there... So, any clue about what is going on?!?!
[9 Jan 2006 10:57]
Vinicius Mommensohn
Hi there... So, any clue about what is going on?!?! Tks in advance!!!
[11 Jan 2006 10:58]
Vinicius Mommensohn
This might help: mysqld got signal 11; 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=16777216 read_buffer_size=126976 max_used_connections=1 max_connections=100 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 41583 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x853dec8 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbf5fdcd8, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x80cc9d2 0x83353d0 0x83203c4 0x8327afe 0x80ca266 0x808af54 0x8057166 0x805fa17 0x8125172 0x811cb06 0x811cc1a 0x811ceba 0x812baaf 0x812d999 0x812dfc6 0x80e3e6d 0x80ea904 0x80eb308 0x80ec70c 0x832fd09 0x836900a New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd->query at 0x8557148 = SELECT SQL_CALC_FOUND_ROWS id, cache, url, meta_descricao, TRIM(SUBSTRING(titulo, 1, 90)) as preview_titulo, SUBSTRING(conteudo, (LOCATE('www.popmax.com.br', conteudo) - 100), 200) as preview_conteudo, CONCAT_WS(' ', SUBSTRING_INDEX(SUBSTRING_INDEX(url, '/', 1), '.', -4), LEFT(titulo, 30)) as preview_diferencas, (0.3 * MATCH (conteudo) AGAINST ('+www.popmax.com.br' IN BOOLEAN MODE)) + (1.2 * MATCH (url) AGAINST ('+www.popmax.com.br' IN BOOLEAN MODE)) + (1.2 * MATCH (titulo) AGAINST ('+www.popmax.com.br' IN BOOLEAN MODE)) + (2 * MATCH (meta_keywords) AGAINST ('+www.popmax.com.br' IN BOOLEAN MODE)) + (0.3 * MATCH (meta_descricao) AGAINST ('+www.popmax.com.br' IN BOOLEAN MODE)) + (0.05 * referencias) + (0.05 * acessos) as relevancia FROM principal WHERE MATCH (conteudo) AGAINST ('+www.popmax.com.br' IN BOOLEAN MODE) OR MATCH (url) AGAINST ('+www.popmax.com.br' IN BOOLEAN MODE) OR MATCH (meta_descricao) AGAINST ('+www.popmax.com.br' IN BOOLEAN MODE) OR MATCH (meta_keywords) AGAINST ('+www.popmax.com.br' IN BOOLEA thd->thread_id=3 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Number of processes running now: 0 060111 08:55:26 mysqld restarted
[16 Jan 2006 12:54]
Vinicius Mommensohn
I discovery something new... In the begging, i thought that the problem is with the query for something like 'www.[anything].[xxx].[br]'. But, today, something happend: The problem is with the query for the word '+www' in MATCH case. This is the log: ------ LOG FILE -------- 060116 10:48:44 [Note] /usr/local/mysql/libexec/mysqld: ready for connections. Version: '5.0.19-nightly-20060103-log' socket: '/tmp/mysql2.sock' port: 3307 Source distribution mysqld got signal 11; 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=33554432 read_buffer_size=258048 max_used_connections=1 max_connections=100 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 83567 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x85422c0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbf5fdcd8, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x80cc9d2 0x83353d0 0x83203c4 0x8327afe 0x80ca266 0x808af54 0x8057166 0x805fa17 0x8125172 0x811cb06 0x811cc1a 0x811ceba 0x812baaf 0x812d999 0x812dfc6 0x80e3e6d 0x80ea904 0x80eb308 0x80ec70c 0x832fd09 0x836900a New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd->query at 0x8561540 = SELECT SQL_CALC_FOUND_ROWS id, cache, url, meta_descricao, TRIM(SUBSTRING(titulo, 1, 90)) as preview_titulo, SUBSTRING(conteudo, (LOCATE('+"www"', conteudo) - 100), 200) as preview_conteudo, CONCAT_WS(' ', SUBSTRING_INDEX(SUBSTRING_INDEX(url, '/', 1), '.', -4), LEFT(titulo, 30)) as preview_diferencas, MATCH (conteudo, url, titulo, meta_keywords, meta_descricao) AGAINST ('+"www"' IN BOOLEAN MODE) + (0.05 * referencias) + (0.05 * acessos) as relevancia FROM principal WHERE MATCH (url, titulo, conteudo, meta_descricao, meta_keywords) AGAINST ('+"www"' IN BOOLEAN MODE) GROUP BY preview_diferencas HAVING relevancia > 0 ORDER BY relevancia DESC, id LIMIT 190, 10 thd->thread_id=4 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Number of processes running now: 0 060116 10:49:10 mysqld restarted 060116 10:49:10 [Warning] No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!! Please use '--log-bin=server03-bin' to avoid this problem. 060116 10:49:10 [Note] /usr/local/mysql/libexec/mysqld: ready for connections. Version: '5.0.19-nightly-20060103-log' socket: '/tmp/mysql2.sock' port: 3307 Source distribution ------- Log File ---------- Maybe this could help! Regards, Vinicius
[1 Feb 2006 16:00]
MySQL Verification Team
Then now you are getting a server crash? Can you provide the query involved and can I use the same dump you provided early? Thanks in advance.
[2 Mar 2006 0:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
Description: Weard problem. I get "Lost connection to MySQL server during query" when i tried to do a query in database but, if i try again the query works fine. The lost connection problem appears in some situations only... When i make a query with the word "Popmax" the query works fine... but if i try to find "www.popmax.com.br" or any "www.blablabla.com.br" the lost connection appears. How to repeat: This is the query: --- start query --- SELECT SQL_CALC_FOUND_ROWS id, cache, url, TRIM(SUBSTRING(titulo, 1, 90)) as preview_titulo, SUBSTRING(conteudo, (LOCATE('$palavras[0]', conteudo) - 100), 200) as preview_conteudo, meta_descricao, LEFT(conteudo, 30) as preview_inicio, ( (0.3 * MATCH (conteudo) AGAINST ('$pesquisa' IN BOOLEAN MODE)) + (1.2 * MATCH (url) AGAINST ('$pesquisa' IN BOOLEAN MODE)) + (1.2 * MATCH (titulo) AGAINST ('$pesquisa' IN BOOLEAN MODE)) + (2 * MATCH (meta_keywords) AGAINST ('$pesquisa' IN BOOLEAN MODE)) + (1.2 * MATCH (meta_descricao) AGAINST ('$pesquisa' IN BOOLEAN MODE)) + (0.05 * referencias) + (0.05 * acessos)) as relevancia, CONCAT_WS(' ', SUBSTRING_INDEX(SUBSTRING_INDEX(url, '/', 1), '.', -4), LEFT(titulo, 25)) as preview_diferencas FROM view_principal WHERE MATCH (conteudo) AGAINST ('$pesquisa' IN BOOLEAN MODE) OR MATCH (url) AGAINST ('$pesquisa' IN BOOLEAN MODE) OR MATCH (meta_descricao) AGAINST ('$pesquisa' IN BOOLEAN MODE) OR MATCH (meta_keywords) AGAINST ('$pesquisa' IN BOOLEAN MODE) OR MATCH (titulo) AGAINST ('$pesquisa' IN BOOLEAN MODE) GROUP BY preview_diferencas HAVING relevancia > 0 ORDER BY relevancia DESC, id LIMIT $pgini, $padrao[regs_pag]; --- end query --- This is the table: --- start table --- CREATE TABLE `principal` ( `id` bigint(20) unsigned NOT NULL auto_increment, `url` varchar(255) NOT NULL default '', `acessos` bigint(20) unsigned NOT NULL default '0', `datacadastro` datetime NOT NULL default '0000-00-00 00:00:00', `dataatualizacao` datetime NOT NULL default '0000-00-00 00:00:00', `titulo` varchar(255) NOT NULL default '', `conteudo` longtext NOT NULL, `cache` varchar(20) NOT NULL default '', `referencias` int(10) unsigned NOT NULL default '0', `meta_keywords` varchar(255) NOT NULL default '', `meta_descricao` varchar(255) NOT NULL default '', PRIMARY KEY (`id`), KEY `url` (`url`), KEY `titulo` (`titulo`), FULLTEXT KEY `full_titulo` (`titulo`), FULLTEXT KEY `full_url` (`url`), FULLTEXT KEY `full_conteudo` (`conteudo`), FULLTEXT KEY `full_meta_keywords` (`meta_keywords`), FULLTEXT KEY `full_meta_descricao` (`meta_descricao`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=1 COMMENT='Base principal do Integrador' --- end table --- This is the view: --- start view --- CREATE ALGORITHM=UNDEFINED DEFINER=`popmax`@`%` SQL SECURITY DEFINER VIEW `integrador`.`view_principal` AS select `integrador`.`principal`.`id` AS `id`, `integrador`.`principal`.`url` AS `url`, `integrador`.`principal`.`cache` AS `cache`, `integrador`.`principal`.`titulo` AS `titulo`, `integrador`.`principal`.`conteudo` AS `conteudo`, `integrador`.`principal`.`referencias` AS `referencias`, `integrador`.`principal`.`acessos` AS `acessos`, `integrador`.`principal`.`meta_keywords` AS `meta_keywords`, `integrador`.`principal`.`meta_descricao` AS `meta_descricao` from `integrador`.`principal` --- end view --- This is the my.cnf: --- start my.cnf --- [client] port = 3306 socket = /tmp/mysql.sock [mysqld] port = 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 32M max_allowed_packet = 2M table_cache = 32 sort_buffer_size = 256K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 256K query_cache_size= 32M max_connection = 100 table_cache=256 ft_min_word_len=3 innodb_data_home_dir = /usr/local/mysql/var/ innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/var/ innodb_log_arch_dir = /usr/local/mysql/var/ innodb_buffer_pool_size = 64M innodb_additional_mem_pool_size = 8M innodb_log_file_size = 16M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 innodb_thread_concurrency = 8 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash default-character-set=utf8 [isamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout --- end my.cnf --- Suggested fix: I really dont know what is going on!!! The problem starts when i create the 2 last fulltext index: full_meta_keywords and full_meta_descricao. Before it, the query was going fine!!! Sorry for the bad english!!!