Hi Heikki,
The query doesn't crash anymore, but the ORDER BY doesn't work at all (I
tested it on a table with data).
How-to-repeat :
INSERT INTO threadhardwarefr13 (pseudo,date,numreponse) VALUES
('kytine','2002-09-18 20:37:31','1360'),('joce','2002-09-18
20:42:20','1361'),('kytine','2002-09-18 20:46:32','1362');
mysql> SELECT LOWER(pseudo),date,numreponse FROM threadhardwarefr13 WHERE
numeropost='0' ORDER BY numreponse DESC 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.00 sec)
mysql> SELECT LOWER(pseudo),date,numreponse FROM threadhardwarefr13 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.00 sec)
Regards,
Jocelyn
----- Original Message -----
From: "Heikki Tuuri" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Wednesday, September 25, 2002 7:09 PM
Subject: Re: InnoDB: Assertion failure in file ha_innodb.cc line 2180...
> Jeremy, Jocelyn,
>
> can you try the following this patch?
>
> The flag which bans MySQL using a descending cursor to calculate
>
> column LIKE 'jhghj%' ORDER BY column DESC
>
> queries was apparently put to the wrong place in ha_innodb.h. The
assertion
> I had added to 4.0.4 revealed this hidden bug.
>
> The patch fixes also the crash submitted by Jocelyn Fournier earlier
today:
> ...
> CREATE TABLE `threadhardwarefr13` (
> `numeropost` mediumint(8) unsigned NOT NULL default '0',
> `numreponse` int(10) unsigned NOT NULL auto_increment,
> `pseudo` varchar(35) NOT NULL default '',
> `date` datetime NOT NULL default '0000-00-00 00:00:00',
> PRIMARY KEY (`numeropost`,`numreponse`),
> UNIQUE KEY `numreponse` (`numreponse`),
> KEY `pseudo` (`pseudo`,`numeropost`)
> ) TYPE=InnoDB;
>
> SELECT LOWER(pseudo),date FROM threadhardwarefr13 WHERE
numeropost='166498'
> ORDER BY numreponse DESC LIMIT 0,3;
>
> -> assertion failure at line 2180 of ha_innodb.cc
> ...
>
> I have to check why MySQL uses that flag in Jocelyn's query. It does not
> have a LIKE 'gggg%' clause.
>
> Thank you for the bug reports,
>
> Heikki
>
>
> ChangeSet
> 1.1317 02/09/25 19:02:46 [EMAIL PROTECTED] +2 -0
> ha_innodb.cc, ha_innodb.h:
> Move to the right place the flag which bans use of
HA_READ_PREFIX_LAST;
> add
> diagnostics and remove the assertion on line 2180 of ha_innodb.cc
>
> sql/ha_innodb.cc
> 1.110 02/09/25 19:02:06 [EMAIL PROTECTED] +6 -1
> Move to the right place the flag which bans use of
HA_READ_PREFIX_LAST;
> add
> diagnostics and remove the assertion on line 2180 of ha_innodb.cc
>
> sql/ha_innodb.h
> 1.44 02/09/25 19:02:03 [EMAIL PROTECTED] +2 -1
> Move to the right place the flag which bans use of
HA_READ_PREFIX_LAST;
> add
> diagnostics and remove the assertion on line 2180 of ha_innodb.cc
>
> # 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.109/sql/ha_innodb.cc Tue Sep 24 17:11:56 2002
> +++ 1.110/sql/ha_innodb.cc Wed Sep 25 19:02:06 2002
> @@ -2179,7 +2179,12 @@
> case HA_READ_AFTER_KEY: return(PAGE_CUR_G);
> case HA_READ_BEFORE_KEY: return(PAGE_CUR_L);
> case HA_READ_PREFIX: return(PAGE_CUR_GE);
> - case HA_READ_PREFIX_LAST: ut_a(0); return(PAGE_CUR_LE);
> + case HA_READ_PREFIX_LAST:
> + ut_print_timestamp(stderr);
> + fprintf(stderr,
> + " InnoDB: Warning: Using HA_READ_PREFIX_LAST\n");
> + return(PAGE_CUR_LE);
> +
> /* InnoDB does not yet support ..PREFIX_LAST!
> We have to add a new search flag
> PAGE_CUR_LE_OR_PREFIX to InnoDB. */
>
> --- 1.43/sql/ha_innodb.h Fri Sep 20 23:26:10 2002
> +++ 1.44/sql/ha_innodb.h Wed Sep 25 19:02:03 2002
> @@ -82,6 +82,7 @@
> HA_PRIMARY_KEY_IN_READ_INDEX |
> HA_DROP_BEFORE_CREATE |
> HA_NO_PREFIX_CHAR_KEYS |
> + HA_NOT_READ_PREFIX_LAST |
> HA_TABLE_SCAN_ON_INDEX),
> last_dup_key((uint) -1),
> start_of_scan(0)
> @@ -96,7 +97,7 @@
> ulong index_flags(uint idx) const
> {
> return (HA_READ_NEXT | HA_READ_PREV | HA_READ_ORDER |
> - HA_KEY_READ_ONLY | HA_NOT_READ_PREFIX_LAST);
> + HA_KEY_READ_ONLY);
> }
> uint max_record_length() const { return HA_MAX_REC_LENGTH; }
> uint max_keys() const { return MAX_KEY; }
>
>
> ----- Original Message -----
> From: "Jeremy Zawodny" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> Sent: Wednesday, September 25, 2002 6:19 PM
> Subject: InnoDB: Assertion failure in file ha_innodb.cc line 2180...
>
>
> > Heikki and others,
> >
> > One of my slaves is dying frequently. It runs MySQL 4.0.4 (pulled
> > yesterday) on FreeBSD. I'm getting an assertion failure on line 2180,
> > which appears to be:
> >
> > case HA_READ_PREFIX_LAST: ut_a(0); return(PAGE_CUR_LE);
> >
> > What can I do to help debug this?
> >
> > Thanks,
> >
> > Jeremy
> > --
> > Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo!
> > <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/
> >
> > MySQL 3.23.51: up 50 days, processed 1,063,578,875 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
