Re: InnoDB: Assertion failure in file ha_innodb.cc line 2180...

2002-09-26 Thread Jeremy Zawodny 

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.

I'll rebuild with Monty's latest patch and see that things work as
expected.

Thanks!

Jeremy
-- 
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

Re: InnoDB: Assertion failure in file ha_innodb.cc line 2180...

2002-09-26 Thread Jocelyn Fournier 

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.

Regards,
  Jocelyn
- 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

Re: InnoDB: Assertion failure in file ha_innodb.cc line 2180...

2002-09-26 Thread Jocelyn Fournier 

BTW, I assume Monty's change means that MyISAM tables are able to not use
filesorting on
WHERE key_name1='constant' ORDER BY key_name DESC 
type queries whereas InnoDB tables are not able to do so ?

e.g. :
mysql ALTER TABLE threadhardwarefr13 type=innodb;
mysql EXPLAIN SELECT LOWER(pseudo),date,numreponse FROM threadhardwarefr13
WHERE numeropost='18' ORDER BY numreponse DESC LIMIT 0,3;
++--+---+-+-+---+---
---++
| table  | type | possible_keys | key | key_len | ref   |
rows | Extra  |
++--+---+-+-+---+---
---++
| threadhardwarefr13 | ref  | PRIMARY   | PRIMARY |   3 | const |
39 | where used; Using filesort |
++--+---+-+-+---+---
---++
1 row in set (0.00 sec)

mysql ALTER TABLE threadhardwarefr13 type=myisam;
Query OK, 1387 rows affected (3.54 sec)
Records: 1387  Duplicates: 0  Warnings: 0

mysql EXPLAIN SELECT LOWER(pseudo),date,numreponse FROM threadhardwarefr13
WHERE numeropost='18' ORDER BY numreponse DESC LIMIT 0,3;
++--+---+-+-+---+---
---++
| table  | type | possible_keys | key | key_len | ref   |
rows | Extra  |
++--+---+-+-+---+---
---++
| threadhardwarefr13 | ref  | PRIMARY   | PRIMARY |   3 | const |
77 | where used |
++--+---+-+-+---+---
---++
1 row in set (0.00 sec)

Regards,
  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 8: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.

 Regards,
   Jocelyn
 - 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

Re: InnoDB: Assertion failure in file ha_innodb.cc line 2180...

2002-09-26 Thread Heikki Tuuri 

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

Re: InnoDB: Assertion failure in file ha_innodb.cc line 2180...

2002-09-26 Thread Jocelyn Fournier 

Yes, I've also tested the LIKE 'abc%' but forgot to include it in my mail :)

CREATE TABLE `threadhardwarefr12` (
  `numeropost` mediumint(8) unsigned NOT NULL default '0',
  `numreponse` int(10) unsigned NOT NULL default '0',
  `pseudo` varchar(35) NOT NULL default '',
  `date` datetime NOT NULL default '-00-00 00:00:00',
  PRIMARY KEY  (`pseudo`)
) TYPE=InnoDB

mysql SELECT LOWER(pseudo),date,numreponse FROM threadhardwarefr12 WHERE
pseudo LIKE 'kyt%' ORDER BY pseudo;
+---+-++
| LOWER(pseudo) | date| numreponse |
+---+-++
| kytine| 2002-09-18 20:37:31 |   1360 |
| kytoune   | 2002-09-18 20:42:20 |   1361 |
+---+-++
2 rows in set (0.00 sec)

Regards,
  Jocelyn

- Original Message -
From: Heikki Tuuri [EMAIL PROTECTED]
To: Jocelyn Fournier [EMAIL PROTECTED]; [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED];
[EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Thursday, September 26, 2002 9:28 PM
Subject: Re: InnoDB: Assertion failure in file ha_innodb.cc line 2180...


 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

Re: InnoDB: Assertion failure in file ha_innodb.cc line 2180...

2002-09-25 Thread Jocelyn Fournier 

Hi Jeremy,

Same problem for me, I submitted a bug report with a repeatable testcase
yesterday to the bugs list.
Reading the source code and changeset 1.1287, it appears this part of the
source code should be desactivated ?

(ChangeSet
  1.1287 02/09/15 02:23:53 [EMAIL PROTECTED] +2 -0
  ha_innodb.h, ha_innodb.cc:
Reverted the change which allowed use of HA_READ_PREFIX_LAST in InnoDB)

Regards,
  Jocelyn

- Original Message -
From: Jeremy Zawodny [EMAIL PROTECTED]
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Wednesday, September 25, 2002 5: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






-
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

Re: InnoDB: Assertion failure in file ha_innodb.cc line 2180...

2002-09-25 Thread Heikki Tuuri 

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 '-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 '%' 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

Re: InnoDB: Assertion failure in file ha_innodb.cc line 2180...

2002-09-25 Thread Jeremy Zawodny 

On Wed, Sep 25, 2002 at 08:09:08PM +0300, Heikki Tuuri wrote:
 Jeremy, Jocelyn,
 
 can you try the following this patch?

I'm building a new binary row.

 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.

If it helps, my 4.0.2 and 4.0.3 don't have this problem.  They all get
the same queries.  Perhaps a recent change elsewhere triggered this?

Jeremy
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
[EMAIL PROTECTED]  |  http://jeremy.zawodny.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 [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: InnoDB: Assertion failure in file ha_innodb.cc line 2180...

2002-09-25 Thread Jocelyn Fournier 

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 '-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 '%' 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

Re: InnoDB: Assertion failure in file ha_innodb.cc line 2180...

2002-09-25 Thread Heikki Tuuri 

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.

- Original Message -
From: Jocelyn Fournier [EMAIL PROTECTED]
To: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED];
[EMAIL PROTECTED]
Sent: Wednesday, September 25, 2002 9:04 PM
Subject: Re: InnoDB: Assertion failure in file ha_innodb.cc line 2180...


 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)


Now this works:


mysql 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');
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

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:46:32 |   1362 |
| joce  | 2002-09-18 20:42:20 |   1361 |
| kytine| 2002-09-18 20:37:31 |   1360 |
+---+-++
3 rows in set (0.01 sec)

mysql



ChangeSet
  1.1318 02/09/25 20:35:12 [EMAIL PROTECTED] +2 -0
  ha_innodb.h, ha_innodb.cc:
Remove the flag HA_NOT_READ_PREFIX_LAST because ORDER BY orders wrong
then;
also remove the diagnostic print to avoid cluttering the error log

  sql/ha_innodb.h
1.45 02/09/25 20:35:01 [EMAIL PROTECTED] +4 -1
Remove the flag HA_NOT_READ_PREFIX_LAST because ORDER BY orders wrong
then;
also remove the diagnostic print to avoid cluttering the error log

  sql/ha_innodb.cc
1.111 02/09/25 20:35:01 [EMAIL PROTECTED] +2 -2
Remove the flag HA_NOT_READ_PREFIX_LAST because ORDER BY orders wrong
then;
also remove the diagnostic print to avoid cluttering the error log

# 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.110/sql/ha_innodb.cc Wed Sep 25 19:02:06 2002
+++ 1.111/sql/ha_innodb.cc Wed Sep 25 20:35:01 2002
@@ -2180,9 +2180,9 @@
   case HA_READ_BEFORE_KEY: return(PAGE_CUR_L);
   case HA_READ_PREFIX:  return(PAGE_CUR_GE);
   case HA_READ_PREFIX_LAST:
-  ut_print_timestamp(stderr);
+/*  ut_print_timestamp(stderr);
 fprintf(stderr,
-  InnoDB: Warning: Using HA_READ_PREFIX_LAST\n);
+InnoDB: Warning: Using HA_READ_PREFIX_LAST\n); */
   return(PAGE_CUR_LE);

   /* InnoDB does not yet support ..PREFIX_LAST!

--- 1.44/sql/ha_innodb.h Wed Sep 25 19:02:03 2002
+++ 1.45/sql/ha_innodb.h Wed Sep 25 20:35:01 2002
@@ -81,8 +81,11 @@
  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_NOT_READ_PREFIX_LAST |
  HA_TABLE_SCAN_ON_INDEX),
last_dup_key((uint) -1),
start_of_scan(0)




-
Before posting, please check:
   http://www.mysql.com