Hi
I forward this to you from the Debian bug tracking system as it seems to
be an upstream problem. Please keep the reply-to.
bye,
-christian-
On Wed, Oct 31, 2001 at 09:42:56PM +0000, Petr Vandrovec wrote:
> Package: mysql-server
> Version: 3.23.43-2
> Tags: upstream
>
> Hi,
> I have simple database consisting of two tables which is used for logging
> requests passed through an application http proxy. After upgrading from
> 3.23.42 to 3.23.43 I found that some selects on my tables return no data
> instead of correct ones (and after loong time).
>
> I verified that upstream 3.23.43 suffers from this problem too, while
> upstream 3.23.42 works correctly.
>
> For now I downgraded to 3.23.42, but it is possible to do any
> (nondestructive) tests on my database, but I'm afraid that I cannot
> give you direct access to data in database.
> Thanks,
> Petr Vandrovec
> [EMAIL PROTECTED]
>
>
> # My database has two tables...
> # one with http requests sent through proxy
>
> mysql> desc requests;
> +----------+----------------------------+------+-----+---------+----------------+
> | Field | Type | Null | Key | Default | Extra |
> +----------+----------------------------+------+-----+---------+----------------+
> | id | int(10) unsigned | | PRI | NULL | auto_increment |
> | reqtime | timestamp(14) | YES | MUL | NULL | |
> | method | enum('GET','POST') | | | GET | |
> | htmethod | enum('http://','https://') | | | http:// | |
> | host | varchar(40) | | MUL | | |
> | port | int(10) unsigned | | MUL | 0 | |
> | path | varchar(255) | | MUL | | |
> | org | varchar(10) | | MUL | | |
> | user | varchar(64) | | | | |
> | result | varchar(64) | | | | |
> +----------+----------------------------+------+-----+---------+----------------+
> 10 rows in set (0.00 sec)
>
> # and second with get/post params
>
> mysql> desc params;
> +----------+------------------+------+-----+---------+----------------+
> | Field | Type | Null | Key | Default | Extra |
> +----------+------------------+------+-----+---------+----------------+
> | id | int(10) unsigned | | PRI | NULL | auto_increment |
> | query_id | int(10) unsigned | | MUL | 0 | |
> | param | varchar(255) | | MUL | | |
> | value | text | YES | MUL | NULL | |
> +----------+------------------+------+-----+---------+----------------+
> 4 rows in set (0.00 sec)
>
> # and couple of indexes
>
> mysql> show index from requests;
>
>+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
> | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
>Cardinality | Sub_part | Packed | Comment |
>
>+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
> | requests | 0 | PRIMARY | 1 | id | A |
>42170 | NULL | NULL | |
> | requests | 1 | ou | 1 | org | A |
> 7 | NULL | NULL | |
> | requests | 1 | ou | 2 | user | A |
> 897 | NULL | NULL | |
> | requests | 1 | host | 1 | host | A |
> 26 | NULL | NULL | |
> | requests | 1 | path | 1 | path | A |
> 314 | NULL | NULL | |
> | requests | 1 | port | 1 | port | A |
> 2 | NULL | NULL | |
> | requests | 1 | reqtime | 1 | reqtime | A |
>42170 | NULL | NULL | |
>
>+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
> 7 rows in set (0.00 sec)
>
> mysql> show index from params;
>
>+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
> | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
>Cardinality | Sub_part | Packed | Comment |
>
>+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
> | params | 0 | PRIMARY | 1 | id | A |
>344724 | NULL | NULL | |
> | params | 1 | query_id | 1 | query_id | A |
>38302 | NULL | NULL | |
> | params | 1 | param | 1 | param | A |
>1673 | NULL | NULL | |
> | params | 1 | value | 1 | value | A |
>28727 | 50 | NULL | |
>
>+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
> 4 rows in set (0.00 sec)
>
> # and after upgrade I found that query
> # below returns zero records. On 3.23.42 query takes 0.03 secs and
> # returns 696 rows.
>
> mysql> select b.id,b.param from requests a,params b where a.org='tul' and
>a.path='/intranet/cgi/present' and a.id=b.query_id and b.param='PRESENT';
> Empty set (1.91 sec)
>
> # although if I remove " and b.param='PRESENT'" from request, 978 records is
> # found. If I use a.org='cvut' instead of 'tul', correct result is returned,
> # BTW
>
> mysql> select b.id,b.param from requests a,params b where a.org='tul' and
>a.path='/intranet/cgi/present' and a.id=b.query_id;
> +--------+--------------------+
> | id | param |
> +--------+--------------------+
> | 31704 | PRESENT |
> | 31705 | STYLE |
> | 31706 | PRESENT |
> | 31707 | STYLE |
> | 31708 | PRESENT |
> | 31709 | STYLE |
>
> [snipped ~950 lines]
>
> | 330994 | PRESENT |
> | 330995 | PRESENT |
> | 330996 | PRESENT |
> | 330997 | PRESENT |
> | 330998 | PRESENT |
> | 330999 | PRESENT |
> | 331000 | PRESENT |
> | 331001 | PRESENT |
> | 331002 | PRESENT |
> | 331003 | PRESENT |
> | 331004 | IGNORE |
> +--------+--------------------+
> 978 rows in set (0.03 sec)
>
> # and if you think that there are some invisible characters after PRESENT:
> # no, there is not such thing:
>
> mysql> select * from params b where b.id=331003 and b.param='PRESENT';
> +--------+----------+---------+----------------------------------------+
> | id | query_id | param | value |
> +--------+----------+---------+----------------------------------------+
> | 331003 | 40478 | PRESENT | DB=8,AN=4396492,FM=9,SEARCH=EW.TechEng |
> +--------+----------+---------+----------------------------------------+
> 1 row in set (0.00 sec)
>
> # and now some explains:
>
> mysql> explain select b.id,b.param from requests a,params b where a.org='tul' and
>a.path='/intranet/cgi/present' and a.id=b.query_id and b.param='PRESENT';
> +-------+-------+-----------------+-------+---------+-------+-------+------------+
> | table | type | possible_keys | key | key_len | ref | rows | Extra |
> +-------+-------+-----------------+-------+---------+-------+-------+------------+
> | a | ref | PRIMARY,ou,path | ou | 10 | const | 1061 | where used |
> | b | range | query_id,param | param | 255 | NULL | 26327 | where used |
> +-------+-------+-----------------+-------+---------+-------+-------+------------+
> 2 rows in set (0.00 sec)
>
> # on mysql 3.23.42 explain says:
>
> mysql> explain select b.id,b.param from requests a,params b where a.org='tul' and
>a.path='/intranet/cgi/present' and a.id=b.query_id and b.param='PRESENT';
> +-------+-------+-----------------+----------+---------+-------+------+------------+
> | table | type | possible_keys | key | key_len | ref | rows | Extra |
> +-------+-------+-----------------+----------+---------+-------+------+------------+
> | a | ref | PRIMARY,ou,path | ou | 10 | const | 1061 | where used |
> | b | ref | query_id,param | query_id | 4 | a.id | 9 | where used |
> +-------+-------+-----------------+----------+---------+-------+-------+------------+
> 2 rows in set (0.01 sec)
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
