Hi,
I have another example of this strange behaviour. I've managed to dump
most of the table and then after certain row got the error 127. This
machine is linux 2.2.17 in this particular example operating on table
close to the 2GB limit.
mysql Ver 11.15 Distrib 3.23.44, for pc-linux-gnu (i686)
I'm running following command:
From my copy script I got:
/usr/local/mysql/bin/mysqldump: Error 1030: Got error 127 from table handler when
dumping table 'intragenome_data' at row: 5585
When checking the table on the target host, where I thought would be empty table, it
appeared that the table in fine and contains first 5585 rows and contains data as
expected.
mysql> describe intragenome_data;
+------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| id | int(11) | | PRI | 0 | |
| contig_data_id | int(11) | | MUL | 0 | |
| contig_data_code | varchar(100) | | MUL | | |
| code | varchar(100) | | MUL | | |
| descr | varchar(255) | YES | MUL | NULL | |
| dat | longtext | YES | | NULL | |
| created | varchar(100) | | | | |
+------------------+--------------+------+-----+---------+-------+
7 rows in set (0.02 sec)
mysql>
Then I checked the table on source host (from which I got the sql dump
data) the logfile and table itself:
011209 05:48:19 mysqld started
/usr/local/mysql/libexec/mysqld: ready for connections
011210 14:47:02 Warning: Found 24646 of 0 rows when repairing
'./Burkholderia_pseudomallei_K96243/known3d_data'
011211 13:09:28 read_const: Got error 127 when reading table ./hemi_prot/prot_data
011211 13:09:59 read_const: Got error 127 when reading table ./hemi_prot/prot_data
011211 13:11:27 read_const: Got error 127 when reading table ./hemi_prot/prot_data
011211 13:11:32 read_const: Got error 127 when reading table ./hemi_prot/prot_data
011211 13:11:32 read_const: Got error 127 when reading table ./hemi_prot/prot_data
011211 13:11:32 read_const: Got error 127 when reading table ./hemi_prot/prot_data
011211 13:11:33 read_const: Got error 127 when reading table ./hemi_prot/prot_data
011211 13:11:33 read_const: Got error 127 when reading table ./hemi_prot/prot_data
011211 13:15:43 read_const: Got error 127 when reading table
./Listeria_monocytogenes_EGD/orf_data
011211 13:16:03 read_const: Got error 127 when reading table
./Listeria_monocytogenes_EGD/orf_data
011213 11:37:32 read_const: Got error 127 when reading table
./Saccharomyces_cerevisiae/funcat_data
011213 11:41:40 read_const: Got error 127 when reading table
./Saccharomyces_cerevisiae/funcat_data
011213 19:14:13 read_const: Got error 127 when reading table
./Agrobacterium_tumefaciens_C58_pAT/blast_data
011213 19:23:21 read_const: Got error 127 when reading table
./Agrobacterium_tumefaciens_C58_pAT/blast_data
011219 16:47:28 read_const: Got error 127 when reading table ./Spombe/blast_data
020102 19:50:28 read_const: Got error 127 when reading table
./Pasteurella_multocida_PM70/blast_data
020103 15:32:09 read_const: Got error 127 when reading table
./Agrobacterium_tumefaciens_C58_pAT/funcat_data
020104 16:39:01 Warning: Found 5585 of 5586 rows when repairing
'./Arabidopsis_thaliana/intragenome_data'
---^ yes, I already had this problems with this table 2 days ago and
ran REPAIR on it. No-one should modify the table, no-one should even run
myisampack/myisamchk/flush-tables commands, so this time I'm rather sure
only mysqld is involved.
020107 12:12:33 read_const: Got error 127 when reading table
./Arabidopsis_thaliana/intragenome_data
kulan$ mysql Arabidopsis_thaliana
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 97806 to server version: 3.23.44-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> check table intragenome_data;
+---------------------------------------+-------+----------+---------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text
| |
+---------------------------------------+-------+----------+---------------------------------------------------------------+
| Arabidopsis_thaliana.intragenome_data | check | warning | Table is marked as
|crashed |
| Arabidopsis_thaliana.intragenome_data | check | error | Size of datafile is:
|2147483647 Should be: 2152589024 |
| Arabidopsis_thaliana.intragenome_data | check | error | Corrupt
| |
+---------------------------------------+-------+----------+---------------------------------------------------------------+
3 rows in set (0.04 sec)
mysql> repair table intragenome_data;
performed in the background:
mysql@kulan$ ls -la /home/mysql/Arabidopsis_thaliana/intragenome_data.*
-rw-r----- 1 mysql mysql 2147483647 Jan 7 12:04
/home/mysql/Arabidopsis_thaliana/intragenome_data.MYD
-rw-r----- 1 mysql mysql 264192 Jan 7 21:31
/home/mysql/Arabidopsis_thaliana/intragenome_data.MYI
-rw-rw---- 1 mysql mysql 2146637252 Jan 7 21:31
/home/mysql/Arabidopsis_thaliana/intragenome_data.TMD
-rw-r----- 1 mysql mysql 8744 Dec 28 14:49
/home/mysql/Arabidopsis_thaliana/intragenome_data.frm
mysql@kulan$
mysql> repair table intragenome_data;
+---------------------------------------+--------+----------+------------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text
| |
+---------------------------------------+--------+----------+------------------------------------------------------------------------+
| Arabidopsis_thaliana.intragenome_data | repair | info | Found block that points
|outside data file at 2144311480 |
| Arabidopsis_thaliana.intragenome_data | repair | info | Found link that points
|at 2151408748 (outside data file) at 2146615640 |
| Arabidopsis_thaliana.intragenome_data | repair | info | Found link that points
|at 2152191404 (outside data file) at 2146622148 |
| Arabidopsis_thaliana.intragenome_data | repair | info | Found link that points
|at 2150176552 (outside data file) at 2146637780 |
| Arabidopsis_thaliana.intragenome_data | repair | warning | Number of rows changed
|from 5624 to 5620 |
| Arabidopsis_thaliana.intragenome_data | repair | status | OK
| |
+---------------------------------------+--------+----------+------------------------------------------------------------------------+
6 rows in set (28 min 43.36 sec)
mysql>
after the REPAIR command finished:
mysql@kulan$ ls -la /home/mysql/Arabidopsis_thaliana/intragenome_data.*
-rw-r----- 1 mysql mysql 2146637252 Jan 7 21:31
/home/mysql/Arabidopsis_thaliana/intragenome_data.MYD
-rw-r----- 1 mysql mysql 247808 Jan 7 21:51
/home/mysql/Arabidopsis_thaliana/intragenome_data.MYI
-rw-r----- 1 mysql mysql 8744 Dec 28 14:49
/home/mysql/Arabidopsis_thaliana/intragenome_data.frm
mysql@kulan$
mysql> check table intragenome_data;
+---------------------------------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------------------------------+-------+----------+----------+
| Arabidopsis_thaliana.intragenome_data | check | status | OK |
+---------------------------------------+-------+----------+----------+
1 row in set (4 min 48.74 sec)
mysql> select count(*) from intragenome_data;
+----------+
| count(*) |
+----------+
| 5620 |
+----------+
1 row in set (0.02 sec)
mysql>
Remember, that I received through mysqldump rows 0-5584 successfully.
Any ideas? Please Cc: me in replies. Thank you.
> Hi,
> once I've reported here problems that somehow randomly we get these
> errors in mysql's log file. It looks, that when user drops a table, a
> table isn't dropped or somehow remains in cache. The when user tries to
> create the table again from scratch, he get's the error 127.
> Please note that the error is clearly NOT loggeg in mysqld's error
> logfile. Flushing logs or hosts or threads doesn't help to get this error
> written to disk.
> >From what has the user reported, from such a dropped & improperly
> recreated table he can only select using the first column and doesn't get
> the data from other columns at all. While I was flushing table cache few
> times per day, he found that possibly this fixed his problem.
> I'm curious why he gets the error when adding index to table's column.
> Why not when trying to create the table already?
>
>
> We are trying to get more out from this. ;)
>
> kulan$ tail /usr/local/mysql/log/kulan.gsf.de.err
> 011211 13:11:32 read_const: Got error 127 when reading table
> ./hemi_prot/prot_data
> 011211 13:11:33 read_const: Got error 127 when reading table
> ./hemi_prot/prot_data
> 011211 13:11:33 read_const: Got error 127 when reading table
> ./hemi_prot/prot_data
> 011211 13:15:43 read_const: Got error 127 when reading table
> ./Listeria_monocytogenes_EGD/orf_data
> 011211 13:16:03 read_const: Got error 127 when reading table
> ./Listeria_monocytogenes_EGD/orf_data
> 011213 11:37:32 read_const: Got error 127 when reading table
> ./Saccharomyces_cerevisiae/funcat_data
> 011213 11:41:40 read_const: Got error 127 when reading table
> ./Saccharomyces_cerevisiae/funcat_data
> 011213 19:14:13 read_const: Got error 127 when reading table
> ./Agrobacterium_tumefaciens_C58_pAT/blast_data
> 011213 19:23:21 read_const: Got error 127 when reading table
> ./Agrobacterium_tumefaciens_C58_pAT/blast_data
> 011219 16:47:28 read_const: Got error 127 when reading table
> ./Spombe/blast_data
> kulan$ /home/w3/pedant2/mips/PEDANT2/soft/PEDANT2/PERL/parse.pl -table
> intergenome -db Saccharomyces_cerevisiae
> DBD::mysql::st execute failed: Got error 127 from table handler at
> /home/w3/pedant2/mips/PEDANT2/soft/PEDANT2/PERL/Database_Pro_MySQL.pm line
> 292.
> Can not execute SQL statement: 'alter table intergenome add index (hitcode)'
> at /home/w3/pedant2/mips/PEDANT2/soft/PEDANT2/PERL/Database_Pro_MySQL.pm
> line 292
> Database_Pro_MySQL::PrepEx('Database_Pro_MySQL=HASH(0x8594fc8)',
> 'alter table intergenome add index (hitcode)') called at
> /home/w3/pedant2/mips/PEDANT2/soft/PEDANT2/PERL/Database_Pro_MySQL.pm line
> 277
> Database_Pro_MySQL::AddIndex('Database_Pro_MySQL=HASH(0x8594fc8)',
> 'intergenome', 'hitcode') called at
> /home/w3/pedant2/mips/PEDANT2/soft/PEDANT2/PERL/Parse_Pro.pm line 393
> Parse_Pro::_IndexTable('Parse_Pro=HASH(0x841b2d8)') called at
> /home/w3/pedant2/mips/PEDANT2/soft/PEDANT2/PERL/Parse_Pro.pm line 373
> Parse_Pro::_SetTable('Parse_Pro=HASH(0x841b2d8)') called at
> /home/w3/pedant2/mips/PEDANT2/soft/PEDANT2/PERL/Parse_Pro.pm line 307
> Parse_Pro::new('Parse_Pro', 'SET', 'Saccharomyces_cerevisiae',
> 'METH', 'intergenome') called at
> /home/w3/pedant2/mips/PEDANT2/soft/PEDANT2/PERL/parse.pl line 177
> Database handle destroyed without explicit disconnect.
> kulan$ tail /usr/local/mysql/log/kulan.gsf.de.err
> 011211 13:11:32 read_const: Got error 127 when reading table
> ./hemi_prot/prot_data
> 011211 13:11:33 read_const: Got error 127 when reading table
> ./hemi_prot/prot_data
> 011211 13:11:33 read_const: Got error 127 when reading table
> ./hemi_prot/prot_data
> 011211 13:15:43 read_const: Got error 127 when reading table
> ./Listeria_monocytogenes_EGD/orf_data
> 011211 13:16:03 read_const: Got error 127 when reading table
> ./Listeria_monocytogenes_EGD/orf_data
> 011213 11:37:32 read_const: Got error 127 when reading table
> ./Saccharomyces_cerevisiae/funcat_data
> 011213 11:41:40 read_const: Got error 127 when reading table
> ./Saccharomyces_cerevisiae/funcat_data
> 011213 19:14:13 read_const: Got error 127 when reading table
> ./Agrobacterium_tumefaciens_C58_pAT/blast_data
> 011213 19:23:21 read_const: Got error 127 when reading table
> ./Agrobacterium_tumefaciens_C58_pAT/blast_data
> 011219 16:47:28 read_const: Got error 127 when reading table
> ./Spombe/blast_data
> kulan$
>
>
>
> --
> Martin Mokrejs - PGP5.0i key is at http://www.natur.cuni.cz/~mmokrejs
> MIPS / Institute for Bioinformatics <http://mips.gsf.de>
> GSF - National Research Center for Environment and Health
> Ingolstaedter Landstrasse 1, D-85764 Neuherberg, Germany
> tel.: +49-89-3187 3616 , 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 <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
--
Martin Mokrejs - PGP5.0i key is at http://www.natur.cuni.cz/~mmokrejs
MIPS / Institute for Bioinformatics <http://mips.gsf.de>
GSF - National Research Center for Environment and Health
Ingolstaedter Landstrasse 1, D-85764 Neuherberg, Germany
tel.: +49-89-3187 3616 , 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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
