Jocelyn,
----- Original Message -----
From: "Jocelyn Fournier" <[EMAIL PROTECTED]>
To: "Heikki Tuuri" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Thursday, September 26, 2002 9:53 PM
Subject: Re: InnoDB: Assertion failure in file ha_innodb.cc line 2180...
> Heikki, Jeremy,
>
> FYI, I've just tested latest pull (including Monty's patch) without Heikki
> 1.1318 changeset (Remove the flag HA_NOT_READ_PREFIX_LAST because ORDER BY
> orders wrong then;) and now it works perfectly fine :
>
> mysql> SELECT LOWER(pseudo),date,numreponse FROM threadhardwarefr12 WHERE
> numeropost='0' ORDER BY numreponse ASC LIMIT 0,3;
> +---------------+---------------------+------------+
> | LOWER(pseudo) | date | numreponse |
> +---------------+---------------------+------------+
> | kytine | 2002-09-18 20:37:31 | 1360 |
> | joce | 2002-09-18 20:42:20 | 1361 |
> | kytine | 2002-09-18 20:46:32 | 1362 |
> +---------------+---------------------+------------+
> 3 rows in set (0.82 sec)
>
> mysql> SELECT LOWER(pseudo),date,numreponse FROM threadhardwarefr12 WHERE
> numeropost='0' ORDER BY numreponse DESC LIMIT 0,3;
> +---------------+---------------------+------------+
> | LOWER(pseudo) | date | numreponse |
> +---------------+---------------------+------------+
> | kytine | 2002-09-18 20:46:32 | 1362 |
> | joce | 2002-09-18 20:42:20 | 1361 |
> | kytine | 2002-09-18 20:37:31 | 1360 |
> +---------------+---------------------+------------+
> 3 rows in set (0.00 sec)
>
> So it seems ORDER BY + WHERE primarykey LIKE 'abc%' ORDER BY primarykey
DESC
> works fine now.
it is the LIKE 'abc%' which does not work. Your test above does not have
LIKE.
I tested now that adding the flag
HA_NOT_READ_PREFIX_LAST
back to index_flags() in ha_innodb.h seems to fix also the LIKE problem.
Thus people who compile themselves can fix also this last known bug through
the patch below. The patch probably does not make it to 4.0.4 because the
4.0.4 build may have been finished today. 4.0.3 does not have the DESC bug.
But I will add PAGE_CUR_LE_OR_EXTENDS to InnoDB-4.0.5 and remove the above
flag. Having different search methods for InnoDB and MyISAM is too
bug-prone, as we have seen.
> Regards,
> Jocelyn
Regards,
Heikki
ChangeSet
1.1323 02/09/26 21:21:44 [EMAIL PROTECTED] +1 -0
ha_innodb.h:
Put the flag HA_NOT_READ_PREFIX_LAST back to index_flags: seems to fix
also the LIKE ... DESC bug
sql/ha_innodb.h
1.46 02/09/26 21:21:34 [EMAIL PROTECTED] +1 -5
Put the flag HA_NOT_READ_PREFIX_LAST back to index_flags: seems to fix
also the LIKE ... DESC bug
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: heikki
# Host: hundin.mysql.fi
# Root: /home/heikki/mysql-4.0
--- 1.45/sql/ha_innodb.h Wed Sep 25 20:35:01 2002
+++ 1.46/sql/ha_innodb.h Thu Sep 26 21:21:34 2002
@@ -81,10 +81,6 @@
HA_NO_WRITE_DELAYED |
HA_PRIMARY_KEY_IN_READ_INDEX |
HA_DROP_BEFORE_CREATE |
- /* We should also list HA_NOT_READ_PREFIX_LAST
- here but it currently seems to break ORDER BY;
- until release 4.0.5 some LIKE 'abc%' ... DESC
- queries will not work correctly */
HA_NO_PREFIX_CHAR_KEYS |
HA_TABLE_SCAN_ON_INDEX),
last_dup_key((uint) -1),
@@ -100,7 +96,7 @@
ulong index_flags(uint idx) const
{
return (HA_READ_NEXT | HA_READ_PREV | HA_READ_ORDER |
- HA_KEY_READ_ONLY);
+ HA_KEY_READ_ONLY | HA_NOT_READ_PREFIX_LAST);
}
uint max_record_length() const { return HA_MAX_REC_LENGTH; }
uint max_keys() const { return MAX_KEY; }
> ----- Original Message -----
> From: "Heikki Tuuri" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Cc: "Jocelyn Fournier" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>;
> <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>;
> <[EMAIL PROTECTED]>
> Sent: Thursday, September 26, 2002 8:39 PM
> Subject: Re: InnoDB: Assertion failure in file ha_innodb.cc line 2180...
>
>
> > Jeremy,
> >
> > ----- Original Message -----
> > From: "Jeremy Zawodny" <[EMAIL PROTECTED]>
> > To: "Heikki Tuuri" <[EMAIL PROTECTED]>
> > Cc: "Jocelyn Fournier" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>;
> > <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>;
> > <[EMAIL PROTECTED]>
> > Sent: Thursday, September 26, 2002 8:25 PM
> > Subject: Re: InnoDB: Assertion failure in file ha_innodb.cc line 2180...
> >
> >
> > > On Wed, Sep 25, 2002 at 09:43:13PM +0300, Heikki Tuuri wrote:
> > > > Jocelyn,
> > > >
> > > > below the latest patch which puts the code as it was in 4.0.3. Some
> LIKE
> > > > 'abc%' ... DESC queries may return wrong results, but this is the
best
> > we
> > > > can get to 4.0.4.
> > > >
> > > > I have to ask Monty about the use of HA_READ_PREFIX_LAST. Other bugs
> may
> > be
> > > > lurking in the use/non-use of that search flag.
> > >
> > > FYI, my server is happy now too.
> >
> > thank you for testing the source tree! Now we found and fixed these bugs
> > before the actual release was made :).
> >
> > One bug remains: I tested yesterday that
> >
> > WHERE primarykey LIKE 'abc%' ORDER BY primarykey DESC;
> >
> > really works wrong in upcoming InnoDB-4.0.4. It only returns rows where
> > primarykey = 'abc'. But better fix that bug in 4.0.5 since it requires a
> > somewhat more fundamental change. Until that users beware!
> >
> > > I'll rebuild with Monty's latest patch and see that things work as
> > > expected.
> > >
> > > Thanks!
> > >
> > > Jeremy
> >
> > Thank you,
> >
> > Heikki
> > Innobase Oy
> >
> >
> >
> > > --
> > > Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo!
> > > <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/
> > >
> > > MySQL 3.23.51: up 51 days, processed 1,083,975,227 queries (244/sec.
> avg)
> >
> >
> >
> >
> >
>
---------------------------------------------------------------------
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
