mysql-4.0.10/9 results w and w/o DESC

Hi,

we upgraded a mysql-server from 3.23.52 today and spotted the following
mysterious behavior:

How-to-repeat:

mysql> SELECT * FROM post where threadid=36016 ORDER BY dateline;

RESULTS --- RESULTS

5 rows in set (0.00 sec)

mysql> SELECT * FROM post where threadid=36016 ORDER BY dateline desc;
Empty set (0.00 sec)

As you can see the same Query with the DESC-Order BY resulted zero Rows, but
should result 5 as the one w/o the desc-order.

This behavior is reproducable on mysql-4.0.10 and 4.0.9. The only difference
is that mysql-4.0.9 produces this error with other threadids in the SELECT.

Deactivating the query-cache did not fix the problem.

I hope my bug-report is useful to you. If you have any questions or need
further information don't hesitate to contact me.

Andreas
--
e-admin internet gmbh
Andreas Gietl tel +49 941 3810884
Ludwig-Thoma-Strasse 35 fax +49 89 244329104
93051 Regensburg mobil +49 171 6070008

PGP/GPG-Key unter http://www.e-admin.de/gpg.html





------------------------------------------------------------ ---------
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 bugs-thread13706@lists.mysql.com
To unsubscribe, e-mail

Re: mysql-4.0.10/9 results w and w/o DESC

Andreas Gietl writes:
> Hi,
>
> we upgraded a mysql-server from 3.23.52 today and spotted the following
> mysterious behavior:
>
> How-to-repeat:
>
> mysql> SELECT * FROM post where threadid=36016 ORDER BY dateline;
>
> RESULTS --- RESULTS
>
> 5 rows in set (0.00 sec)
>
> mysql> SELECT * FROM post where threadid=36016 ORDER BY dateline desc;
> Empty set (0.00 sec)
>
> As you can see the same Query with the DESC-Order BY resulted zero Rows, but
> should result 5 as the one w/o the desc-order.
>
> This behavior is reproducable on mysql-4.0.10 and 4.0.9. The only difference
> is that mysql-4.0.9 produces this error with other threadids in the SELECT.
>
> Deactivating the query-cache did not fix the problem.
>
> I hope my bug-report is useful to you. If you have any questions or need
> further information don't hesitate to contact me.
>
> Andreas
> --
> e-admin internet gmbh
> Andreas Gietl tel +49 941 3810884
> Ludwig-Thoma-Strasse 35 fax +49 89 244329104
> 93051 Regensburg mobil +49 171 6070008
>
> PGP/GPG-Key unter http://www.e-admin.de/gpg.html
>

Hi!

We do not have this bug reported yet.

Sometimes it can be a consequence of the corrupted index.

If it is not a case, please send us a full repeatable case with a dump
of the table, gzipped to:

ftp://support.mysql.com:/pub/mysql/secret

and let us know a filename.

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com

Join MySQL Users Conference and Expo:
http://www.mysql.com/events/uc2003/


------------------------------------------------------------ ---------
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 bugs-thread13720@lists.mysql.com
To unsubscribe, e-mail

Re: mysql-4.0.10/9 results w and w/o DESC

On Thursday 06 February 2003 16:45, you wrote:

Here are a few furhter information for you as some of you requested.

First of all: After i sent the report to you i saw, that the table was
ISAM-type. We changed it to MyISAM and it worked. So it seems to be something
in the isam-handler.

Here is the table-definition. Die table has more than 100 000 entries in it.

CREATE TABLE post (
postid int(10) unsigned NOT NULL auto_increment,
threadid int(10) unsigned NOT NULL default '0',
username varchar(50) NOT NULL default '',
userid int(10) unsigned NOT NULL default '0',
title varchar(100) NOT NULL default '',
dateline int(11) NOT NULL default '0',
pagetext mediumtext,
allowsmilie smallint(6) NOT NULL default '0',
email smallint(6) NOT NULL default '0',
signature smallint(6) NOT NULL default '0',
ipaddress varchar(16) NOT NULL default '',
iconid smallint(5) unsigned NOT NULL default '0',
visible smallint(6) NOT NULL default '0',
invisireason char(1) NOT NULL default '',
PRIMARY KEY (postid),
KEY idxdisp (threadid,dateline),
KEY threadid (threadid),
KEY dateline (dateline),
KEY userid (userid)
) TYPE=ISAM

If you need further information, contact me.

> Hi,
>
> we upgraded a mysql-server from 3.23.52 today and spotted the following
> mysterious behavior:
>
> How-to-repeat:
>
> mysql> SELECT * FROM post where threadid=36016 ORDER BY dateline;
>
> RESULTS --- RESULTS
>
> 5 rows in set (0.00 sec)
>
> mysql> SELECT * FROM post where threadid=36016 ORDER BY dateline desc;
> Empty set (0.00 sec)
>
> As you can see the same Query with the DESC-Order BY resulted zero Rows,
> but should result 5 as the one w/o the desc-order.
>
> This behavior is reproducable on mysql-4.0.10 and 4.0.9. The only
> difference is that mysql-4.0.9 produces this error with other threadids in
> the SELECT.
>
> Deactivating the query-cache did not fix the problem.
>
> I hope my bug-report is useful to you. If you have any questions or need
> further information don't hesitate to contact me.
>
> Andreas

--
e-admin internet gmbh
Andreas Gietl tel +49 941 3810884
Ludwig-Thoma-Strasse 35 fax +49 89 244329104
93051 Regensburg mobil +49 171 6070008

PGP/GPG-Key unter http://www.e-admin.de/gpg.html





------------------------------------------------------------ ---------
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 bugs-thread13723@lists.mysql.com
To unsubscribe, e-mail

Re: mysql-4.0.10/9 results w and w/o DESC

Hi Andreas,

Andreas Gietl writes:
> On Thursday 06 February 2003 16:45, you wrote:
>
> Here are a few furhter information for you as some of you requested.
>
> First of all: After i sent the report to you i saw, that the table was
> ISAM-type. We changed it to MyISAM and it worked. So it seems to be something
> in the isam-handler.
>
> Here is the table-definition. Die table has more than 100 000 entries in it.
>
> CREATE TABLE post (
> postid int(10) unsigned NOT NULL auto_increment,
> threadid int(10) unsigned NOT NULL default '0',
> username varchar(50) NOT NULL default '',
> userid int(10) unsigned NOT NULL default '0',
> title varchar(100) NOT NULL default '',
> dateline int(11) NOT NULL default '0',
> pagetext mediumtext,
> allowsmilie smallint(6) NOT NULL default '0',
> email smallint(6) NOT NULL default '0',
> signature smallint(6) NOT NULL default '0',
> ipaddress varchar(16) NOT NULL default '',
> iconid smallint(5) unsigned NOT NULL default '0',
> visible smallint(6) NOT NULL default '0',
> invisireason char(1) NOT NULL default '',
> PRIMARY KEY (postid),
> KEY idxdisp (threadid,dateline),
> KEY threadid (threadid),
> KEY dateline (dateline),
> KEY userid (userid)
> ) TYPE=ISAM
>
> If you need further information, contact me.

Sorry, but the above does not help. The problem is not just in the
table definition dump. The problem is something that has to do with
your table files (post.frm, post.ISM and post.ISD) on your machine in
the datadirectory (datadir). The index file may be broken.

Please shutdown the MySQL server, then cd to datadir, then run:

isamchk post

Please copy paste the output here.

Please include also information about your operating system,
if you are running UNIX, run 'uname -a' and include the output
here.

If 'isamchk post' does not report any errors in the table,
please make a tarball or a zip file of it and send it to:

support.mysql.com:/pub/mysql/hidden

using ftp program.

Regards,

- Jani

For technical support contracts, visit https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Jani Tolonen
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Helsinki, Finland
<___/ www.mysql.com

------------------------------------------------------------ ---------
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 bugs-thread13730@lists.mysql.com
To unsubscribe, e-mail

Re: mysql-4.0.10/9 results w and w/o DESC

Andreas Gietl writes:
> On Thursday 06 February 2003 16:45, you wrote:
>
> Here are a few furhter information for you as some of you requested.
>
> First of all: After i sent the report to you i saw, that the table was
> ISAM-type. We changed it to MyISAM and it worked. So it seems to be something
> in the isam-handler.
>
> Here is the table-definition. Die table has more than 100 000 entries in it.
>
> CREATE TABLE post (
> postid int(10) unsigned NOT NULL auto_increment,
> threadid int(10) unsigned NOT NULL default '0',
> username varchar(50) NOT NULL default '',
> userid int(10) unsigned NOT NULL default '0',
> title varchar(100) NOT NULL default '',
> dateline int(11) NOT NULL default '0',
> pagetext mediumtext,
> allowsmilie smallint(6) NOT NULL default '0',
> email smallint(6) NOT NULL default '0',
> signature smallint(6) NOT NULL default '0',
> ipaddress varchar(16) NOT NULL default '',
> iconid smallint(5) unsigned NOT NULL default '0',
> visible smallint(6) NOT NULL default '0',
> invisireason char(1) NOT NULL default '',
> PRIMARY KEY (postid),
> KEY idxdisp (threadid,dateline),
> KEY threadid (threadid),
> KEY dateline (dateline),
> KEY userid (userid)
> ) TYPE=ISAM
>
> If you need further information, contact me.

Hi!

Thank you for your bug report, but we do not provide bug fixes for
ISAM any more.

It is deprecated and replaced by MyISAM.

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com

Join MySQL Users Conference and Expo:
http://www.mysql.com/events/uc2003/


------------------------------------------------------------ ---------
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 bugs-thread13731@lists.mysql.com
To unsubscribe, e-mail