4.0.8 handling case with broken index

Hi,
I was trying to delete a row from mysql.db table:

How-To-Repeat:

mysql> select * from db where user like "%guelden%" and db like "%Ncrassa_work>
+---------------+------------------+---------+-------------+ -------------+-------------+-------------+-------------+---- -------+------------+-----------------+------------+-------- ----+-----------------------+------------------+
| Host | Db | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv |
+---------------+------------------+---------+-------------+ -------------+-------------+-------------+-------------+---- -------+------------+-----------------+------------+-------- ----+-----------------------+------------------+
| 146.107.217.% | Ncrassa_work_int | guelden | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | N | N |
| 127.0.0.1 | Ncrassa_work_int | guelden | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | N | N |
+---------------+------------------+---------+-------------+ -------------+-------------+-------------+-------------+---- -------+------------+-----------------+------------+-------- ----+-----------------------+------------------+
2 rows in set (0.12 sec)

mysql>

mysql> delete from db where user="guelden" and db="Ncrassa_work_int";
Query OK, 0 rows affected (0.03 sec)

mysql> delete from db where user like "guelden" and db="Ncrassa_work_int";
Query OK, 0 rows affected (0.10 sec)

mysql> delete from db where user like "guelden" and db like "Ncrassa_work_int";
Query OK, 0 rows affected (0.03 sec)

mysql> delete from db where user like "%guelden%" and db like "%Ncrassa_work_i>
ERROR 1034: Incorrect key file for table: 'db'. Try to repair it
mysql>

mysql> explain select * from db where user like "%guelden%";
+-------+------+---------------+------+---------+------+---- --+-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+------+---------------+------+---------+------+---- --+-------------+
| db | ALL | NULL | NULL | NULL | NULL | 1931 | Using where |
+-------+------+---------------+------+---------+------+---- --+-------------+
1 row in set (0.03 sec)

mysql>

First of all, what's wrong with my sql syntax?

Second, the table is really broken:

-rw-rw---- 1 mysql mysql 0 Mar 12 2002 Upd.MYD
-rw-rw---- 1 mysql mysql 1024 Mar 12 2002 Upd.MYI
-rw-rw---- 1 mysql mysql 8576 Mar 12 2002 Upd.frm
-rw-rw---- 1 mysql mysql 0 Dec 12 16:21 columns_priv.MYD
-rw-rw---- 1 mysql mysql 1024 Dec 12 16:21 columns_priv.MYI
-rw-rw---- 1 mysql mysql 8778 Dec 12 16:21 columns_priv.frm
-rw-rw---- 1 mysql mysql 357235 Jan 15 14:52 db.MYD
-rw-rw---- 1 mysql mysql 113664 Jan 16 18:37 db.MYI
-rw-rw---- 1 mysql mysql 9088 Dec 12 16:21 db.frm
-rw-rw---- 1 mysql mysql 0 Dec 12 16:21 func.MYD
-rw-rw---- 1 mysql mysql 1024 Dec 12 16:21 func.MYI
-rw-rw---- 1 mysql mysql 8641 Dec 12 16:21 func.frm
-rw-rw---- 1 mysql mysql 0 Dec 12 16:21 host.MYD
-rw-rw---- 1 mysql mysql 1024 Dec 12 16:21 host.MYI
-rw-rw---- 1 mysql mysql 9064 Dec 12 16:21 host.frm
-rw-rw---- 1 mysql mysql 7868 Dec 12 16:21 tables_priv.MYD
-rw-rw---- 1 mysql mysql 5120 Dec 12 16:21 tables_priv.MYI
-rw-rw---- 1 mysql mysql 8877 Dec 12 16:21 tables_priv.frm
-rw-rw---- 1 mysql mysql 3804 Dec 13 14:02 user.MYD
-rw-rw---- 1 mysql mysql 2048 Dec 13 14:02 user.MYI
-rw-rw---- 1 mysql mysql 9806 Dec 12 16:21 user.frm
# myisamchk -dvv db.MYI

MyISAM file: db.MYI
Record format: Fixed length
Character set: latin1 (8)
File-version: 1
Creation time: 2002-12-12 16:21:05
Status: crashed
Data records: 1931 Deleted blocks: 0
Datafile parts: 1931 Deleted data: 0
Datafile pointer (bytes): 4 Keyfile pointer (bytes): 3
Datafile length: 357235 Keyfile length: 108544
Max datafile length: 794568949758 Max keyfile length: 17179868159
Recordlength: 185

table description:
Key Start Len Index Type Rec/key Root Blocksize
1 2 60 unique char packed stripped 0 29696 1024
62 96 char stripped 0
158 16 char stripped 1
2 158 16 multip. char packed stripped 0 102400 1024

Field Start Length Nullpos Nullbit Type
1 1 1
2 2 60
3 62 96
4 158 16
5 174 1
6 175 1
7 176 1
8 177 1
9 178 1
10 179 1
11 180 1
12 181 1
13 182 1
14 183 1
15 184 1
16 185 1
#


Rebuilding the index helped, but I'd like to note this because the error should be propagated
also in other cases. I dont know why I had to inlude all those LIKE and `%' tricks to get
them executed.

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

Re: 4.0.8 handling case with broken index

On Thu, 16 Jan 2003, Martin MOKREJ© wrote:

> Hi,
> I was trying to delete a row from mysql.db table:
>
> How-To-Repeat:

[...]

> mysql> delete from db where user like "%guelden%" and db like "%Ncrassa_work_i>
> ERROR 1034: Incorrect key file for table: 'db'. Try to repair it
> mysql>

> # myisamchk -dvv db.MYI
>
> MyISAM file: db.MYI
> Record format: Fixed length
> Character set: latin1 (8)
> File-version: 1
> Creation time: 2002-12-12 16:21:05
> Status: crashed

[...]

> Rebuilding the index helped, but I'd like to note this because the error should be propagated
> also in other cases. I dont know why I had to inlude all those LIKE and `%' tricks to get
> them executed.

There was actually one more table corrupt, but I've fixed that now, so it doesn't seem to be related
to the previous case (ERROR 1034 above):

#myisamchk tables_priv.MYI
Checking MyISAM file: tables_priv.MYI
Data records: 28 Deleted blocks: 0
myisamchk: warning: Table is marked as crashed
- check file-size
myisamchk: warning: Size of indexfile is: 5120 Should be: 4096
- check key delete-chain
- check record delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2
myisamchk: error: Can't read indexpage from filepos: -1
MyISAM-table 'tables_priv.MYI' is corrupted
Fix it using switch "-r" or "-o"
#
--
Martin Mokrejs ,
PGP5.0i key is at http://www.natur.cuni.cz/~mmokrejs
MIPS / Institute for Bioinformatics
GSF - National Research Center for Environment and Health
Ingolstaedter Landstrasse 1, D-85764 Neuherberg, Germany
tel.: +49-89-3187 3683 , fax: +49-89-3187 3585

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

Re: 4.0.8 handling case with broken index

On Thursday 16 January 2003 20:55, Martin MOKREJÅ  wrote:
> Hi,
> I was trying to delete a row from mysql.db table:

You have everything OK with your SQL syntax but it looks like
index for "db" table is corrupt.

Like "%guelden%" avoided using index for finding the rows. It was
only updated on delete stage where corruption was discovered



>
> How-To-Repeat:
>
> mysql> select * from db where user like "%guelden%" and db
> like "%Ncrassa_work>
> +---------------+------------------+---------+-------------+ --
>-----------+-------------+-------------+-------------+----- ----
>--+------------+-----------------+------------+------------ +---
>--------------------+------------------+
>
> | Host | Db | User | Select_priv |
> | Insert_priv | Update_priv | Delete_priv | Create_priv |
> | Drop_priv | Grant_priv | References_priv | Index_priv |
> | Alter_priv | Create_tmp_table_priv | Lock_tables_priv |
>
> +---------------+------------------+---------+-------------+ --
>-----------+-------------+-------------+-------------+----- ----
>--+------------+-----------------+------------+------------ +---
>--------------------+------------------+
>
> | 146.107.217.% | Ncrassa_work_int | guelden | Y | Y
> | | Y | Y | Y | Y
> | | Y | Y | Y | Y
> | | N | N | 127.0.0.1 |
> | Ncrassa_work_int | guelden | Y | Y | Y
> | | Y | Y | Y | Y
> | | Y | Y | Y | N
> | | N |
>
> +---------------+------------------+---------+-------------+ --
>-----------+-------------+-------------+-------------+----- ----
>--+------------+-----------------+------------+------------ +---
>--------------------+------------------+ 2 rows in set (0.12
> sec)
>
> mysql>
>
> mysql> delete from db where user="guelden" and
> db="Ncrassa_work_int"; Query OK, 0 rows affected (0.03 sec)
>
> mysql> delete from db where user like "guelden" and
> db="Ncrassa_work_int"; Query OK, 0 rows affected (0.10 sec)
>
> mysql> delete from db where user like "guelden" and db like
> "Ncrassa_work_int"; Query OK, 0 rows affected (0.03 sec)
>
> mysql> delete from db where user like "%guelden%" and db like
> "%Ncrassa_work_i> ERROR 1034: Incorrect key file for table:
> 'db'. Try to repair it mysql>
>
> mysql> explain select * from db where user like "%guelden%";
> +-------+------+---------------+------+---------+------+---- --
>+-------------+
>
> | table | type | possible_keys | key | key_len | ref | rows
> | | Extra |
>
> +-------+------+---------------+------+---------+------+---- --
>+-------------+
>
> | db | ALL | NULL | NULL | NULL | NULL | 1931
> | | Using where |
>
> +-------+------+---------------+------+---------+------+---- --
>+-------------+ 1 row in set (0.03 sec)
>
> mysql>
>
> First of all, what's wrong with my sql syntax?
>
> Second, the table is really broken:
>
> -rw-rw---- 1 mysql mysql 0 Mar 12 2002
> Upd.MYD -rw-rw---- 1 mysql mysql 1024 Mar 12
> 2002 Upd.MYI -rw-rw---- 1 mysql mysql 8576 Mar 12
> 2002 Upd.frm -rw-rw---- 1 mysql mysql 0 Dec
> 12 16:21 columns_priv.MYD -rw-rw---- 1 mysql mysql
> 1024 Dec 12 16:21 columns_priv.MYI -rw-rw---- 1 mysql
> mysql 8778 Dec 12 16:21 columns_priv.frm -rw-rw----
> 1 mysql mysql 357235 Jan 15 14:52 db.MYD -rw-rw----
> 1 mysql mysql 113664 Jan 16 18:37 db.MYI -rw-rw----
> 1 mysql mysql 9088 Dec 12 16:21 db.frm -rw-rw----
> 1 mysql mysql 0 Dec 12 16:21 func.MYD -rw-rw----
> 1 mysql mysql 1024 Dec 12 16:21 func.MYI
> -rw-rw---- 1 mysql mysql 8641 Dec 12 16:21
> func.frm -rw-rw---- 1 mysql mysql 0 Dec 12
> 16:21 host.MYD -rw-rw---- 1 mysql mysql 1024 Dec
> 12 16:21 host.MYI -rw-rw---- 1 mysql mysql 9064
> Dec 12 16:21 host.frm -rw-rw---- 1 mysql mysql
> 7868 Dec 12 16:21 tables_priv.MYD -rw-rw---- 1 mysql
> mysql 5120 Dec 12 16:21 tables_priv.MYI -rw-rw---- 1
> mysql mysql 8877 Dec 12 16:21 tables_priv.frm
> -rw-rw---- 1 mysql mysql 3804 Dec 13 14:02
> user.MYD -rw-rw---- 1 mysql mysql 2048 Dec 13
> 14:02 user.MYI -rw-rw---- 1 mysql mysql 9806 Dec
> 12 16:21 user.frm # myisamchk -dvv db.MYI
>
> MyISAM file: db.MYI
> Record format: Fixed length
> Character set: latin1 (8)
> File-version: 1
> Creation time: 2002-12-12 16:21:05
> Status: crashed
> Data records: 1931 Deleted blocks:
> 0 Datafile parts: 1931 Deleted data:
> 0 Datafile pointer (bytes): 4 Keyfile
> pointer (bytes): 3 Datafile length: 357235
> Keyfile length: 108544 Max datafile length:
> 794568949758 Max keyfile length: 17179868159 Recordlength:
> 185
>
> table description:
> Key Start Len Index Type Rec/key
> Root Blocksize 1 2 60 unique char packed stripped
> 0 29696 1024 62 96 char
> stripped 0 158 16 char stripped
> 1 2 158 16 multip. char packed stripped
> 0 102400 1024
>
> Field Start Length Nullpos Nullbit Type
> 1 1 1
> 2 2 60
> 3 62 96
> 4 158 16
> 5 174 1
> 6 175 1
> 7 176 1
> 8 177 1
> 9 178 1
> 10 179 1
> 11 180 1
> 12 181 1
> 13 182 1
> 14 183 1
> 15 184 1
> 16 185 1
> #
>
>
> Rebuilding the index helped, but I'd like to note this because
> the error should be propagated also in other cases. I dont
> know why I had to inlude all those LIKE and `%' tricks to get
> them executed.
>
> ------------------------------------------------------------ --
>------- 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-thread13493@lists.mysql.com To unsubscribe, e-mail
>

--
MySQL 2003 Users Conf. -> http://www.mysql.com/events/uc2003/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Peter Zaitsev
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Moscow, Russia
<___/ 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-thread13501@lists.mysql.com
To unsubscribe, e-mail

Re: 4.0.8 handling case with broken index

On Thu, 16 Jan 2003, Peter Zaitsev wrote:

> On Thursday 16 January 2003 20:55, Martin MOKREJÅ  wrote:
> > Hi,
> > I was trying to delete a row from mysql.db table:
>
> You have everything OK with your SQL syntax but it looks like
> index for "db" table is corrupt.
>
> Like "%guelden%" avoided using index for finding the rows. It was
> only updated on delete stage where corruption was discovered

OK, thanks, but why did not I get same warning when other sql commands
without the LIKE were used?

> > How-To-Repeat:

> > mysql> delete from db where user="guelden" and
> > db="Ncrassa_work_int"; Query OK, 0 rows affected (0.03 sec)

Why is not the ERROR 1034 displayed above?

> >
> > mysql> delete from db where user like "guelden" and
> > db="Ncrassa_work_int"; Query OK, 0 rows affected (0.10 sec)
> >
> > mysql> delete from db where user like "guelden" and db like
> > "Ncrassa_work_int"; Query OK, 0 rows affected (0.03 sec)
> >
> > mysql> delete from db where user like "%guelden%" and db like
> > "%Ncrassa_work_i> ERROR 1034: Incorrect key file for table:
> > 'db'. Try to repair it mysql>

> > Rebuilding the index helped, but I'd like to note this because
> > the error should be propagated also in other cases. I dont
> > know why I had to inlude all those LIKE and `%' tricks to get
> > them executed.


--
Martin Mokrejs ,
PGP5.0i key is at http://www.natur.cuni.cz/~mmokrejs
MIPS / Institute for Bioinformatics
GSF - National Research Center for Environment and Health
Ingolstaedter Landstrasse 1, D-85764 Neuherberg, Germany
tel.: +49-89-3187 3683 , fax: +49-89-3187 3585

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

Re: 4.0.8 handling case with broken index

On Friday 17 January 2003 12:56, Martin MOKREJÅ  wrote:
> On Thu, 16 Jan 2003, Peter Zaitsev wrote:
> > On Thursday 16 January 2003 20:55, Martin MOKREJе═ wrote:
> > > Hi,
> > > I was trying to delete a row from mysql.db table:
> >
> > You have everything OK with your SQL syntax but it looks
> > like index for "db" table is corrupt.
> >
> > Like "%guelden%" avoided using index for finding the rows.
> > It was only updated on delete stage where corruption was
> > discovered
>
> OK, thanks, but why did not I get same warning when other sql
> commands without the LIKE were used?
>
> > > How-To-Repeat:
> > >
> > > mysql> delete from db where user="guelden" and
> > > db="Ncrassa_work_int"; Query OK, 0 rows affected (0.03
> > > sec)
>
> Why is not the ERROR 1034 displayed above?

Because in this case index access pattern was a bit different so
MySQL did not run into the situation where it could detect index
corruption.

In case of index corruption MySQL is not guarantied to detect
table corruption during statement execution. The result can be
very unpredictable starting fron hang (in case tree happened to
have the loop) or deleting/updating wrong data.

This is why it is highly adviced to run MySQL with
--myisam-reocover option which will check all inproperly closed
tables to avoid possible corruption poblems.



--
MySQL 2003 Users Conf. -> http://www.mysql.com/events/uc2003/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Peter Zaitsev
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Moscow, Russia
<___/ 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-thread13507@lists.mysql.com
To unsubscribe, e-mail

Re: 4.0.8 handling case with broken index

=?iso-8859-2?Q?Martin_MOKREJ=A9?= writes:
> Hi,
> I was trying to delete a row from mysql.db table:
>
> How-To-Repeat:
>
> mysql> select * from db where user like "%guelden%" and db like
> "%Ncrassa_work>

[skip]

> also in other cases. I dont know why I had to inlude all those LIKE and `%' tricks to get
> them executed.

Hi!

I was not able to repeat a problem you reported with 4.0.10, although
I have given it several attempts.

Hope you have resolved a problem.

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / 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-thread13546@lists.mysql.com
To unsubscribe, e-mail