Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.2.15, 10.2(EOL), 10.3(EOL)
-
None
-
Ubuntu Xenial Server
CPU Intel 6 core + HT, 64GB RAM
Description
DROP TABLE IF EXISTS test; |
CREATE TABLE test ( |
rs VARCHAR(20) PRIMARY KEY |
) ENGINE=MyISAM DEFAULT CHARSET=latin1; |
rs is in the form "rs12364789", max. length of the string is 12; always "rs" before a number
# myisamchk --keys-used=0 -rq test.MYI
|
... FLUSH
|
# mysqlimport --local --fields_escaped_by=\\ -u root -p test_db test.tsv
|
... FLUSH
|
# myisamchk --analyze --update-state --sort_buffer_size=56G --recover --quick --quick --tmpdir=/mnt/WORK/tmp test.MYI
|
... FLUSH
|
RESULTS:
- with 480,000,000 rows everything is OK
- with 490,000,000 rows nothing will be found.
EXPLAIN says: "Impossible WHERE noticed after reading const tables"
The row can still be found with IGNORE INDEX (PRIMARY)
If I build another table with only a non unique index on `rs` , EXPLAIN says it's using the index but the query won't find anything..
A server version from 3 or 4 Months ago worked OK.
Hope it helps
Thanks
Attachments
Issue Links
- relates to
-
-
- Closed
-
-
-
- Closed
-
Activity
Sorry to come back that late.
I think now, that the culprit is myisamchk creating the indexes.
Like many recommendations on the inet for bulk import in MyIsam engine, I disable first index creation, then I create the indexes after import. Despite the manual, disabling index creation disables also the creation of the unique indexes and PRIMARYs.
As you'll see, it is myisamchk who builds the bad index.
Simply filling the table the classic way, with symultaneously index creation creates a good index, but takes a very long time compared with the first method. (This is due to another problem, mysqld not using the whole assigned myisam_sort_buffer_size to create indexes).
– FIRST METHOD (DISABLE INDEX CREATION == bad index == repair cannot repair)
MariaDB [test]> CREATE or REPLACE TABLE test (rs VARCHAR(20) PRIMARY KEY) ENGINE=MyISAM DEFAULT CHARSET=latin1; |
...
|
MariaDB [test]> FLUSH TABLES ; FLUSH TABLE_STATISTICS ; FLUSH INDEX_STATISTICS ;
|
...
|
|
|
-- as_root_in_the_shell
|
# myisamchk --keys-used=0 -rq test.MYI |
...
|
MariaDB [test]> FLUSH TABLES ; FLUSH TABLE_STATISTICS ; FLUSH INDEX_STATISTICS ;
|
...
|
MariaDB [test]> INSERT INTO test SELECT CONCAT("rs",seq) from seq_1_to_500000000; |
Query OK, 500000000 rows affected (3 min 9.23 sec) |
Records: 500000000 Duplicates: 0 Warnings: 0 |
|
|
MariaDB [test]> FLUSH TABLES ; FLUSH TABLE_STATISTICS ; FLUSH INDEX_STATISTICS ;
|
...
|
|
|
-- as_root_in_the_shell
|
time -p myisamchk --analyze --update-state --sort_buffer_size=56G --recover --quick --quick --tmpdir=/mnt/WORK/tmp test.MYI
|
- check record delete-chain
|
- recovering (with sort) MyISAM-table 'test.MYI' |
Data records: 500000000 |
- Fixing index 1 |
real 208.50 |
user 197.98 |
sys 10.49 |
|
|
MariaDB [test]> FLUSH TABLES ; FLUSH TABLE_STATISTICS ; FLUSH INDEX_STATISTICS ;
|
...
|
|
|
MariaDB [test]> SELECT rs FROM test WHERE rs= "rs490000006"; |
Empty set (0.00 sec) |
|
|
MariaDB [test]> EXPLAIN EXTENDED SELECT rs FROM test WHERE rs= "rs490000006"; |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
|
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
|
1 row in set, 1 warning (0.00 sec) |
|
|
MariaDB [test]> CHECK TABLE test;
|
+-----------------------------------+-------+----------+------------------------------------------+
|
| Table | Op | Msg_type | Msg_text |
|
+-----------------------------------+-------+----------+------------------------------------------+
|
| homo_sapiens_variation_92_37.test | check | error | Key in wrong position at page 1722248192 | |
| homo_sapiens_variation_92_37.test | check | error | Corrupt |
|
+-----------------------------------+-------+----------+------------------------------------------+
|
2 rows in set (14.45 sec) |
|
|
MariaDB [test]> REPAIR TABLE test;
|
+-----------------------------------+--------+----------+----------+
|
| Table | Op | Msg_type | Msg_text |
|
+-----------------------------------+--------+----------+----------+
|
| homo_sapiens_variation_92_37.test | repair | status | OK |
|
+-----------------------------------+--------+----------+----------+
|
|
|
MariaDB [test]> CHECK TABLE test;
|
+-----------------------------------+-------+----------+------------------------------------------+
|
| Table | Op | Msg_type | Msg_text |
|
+-----------------------------------+-------+----------+------------------------------------------+
|
| homo_sapiens_variation_92_37.test | check | error | Key in wrong position at page 1722248192 | |
| homo_sapiens_variation_92_37.test | check | error | Corrupt |
|
+-----------------------------------+-------+----------+------------------------------------------+
|
2 rows in set (14.52 sec) |
–
-- SECOND METHOD, SIMPLY INSERT DATA AND CREATE INDEX SYMULTANEOUSLY
INSERT INTO test SELECT CONCAT("rs",seq) from seq_1_to_500000000; |
Query OK, 500000000 rows affected (1 hour 15 min 44.64 sec) |
Records: 500000000 Duplicates: 0 Warnings: 0 |
|
|
FLUSH TABLES ; FLUSH TABLE_STATISTICS ; FLUSH INDEX_STATISTICS ;
|
|
|
SELECT rs FROM test WHERE rs= "rs490000006"; |
+-------------+
|
| rs |
|
+-------------+
|
| rs490000006 |
|
+-------------+
|
1 row in set (0.00 sec) |
|
|
EXPLAIN EXTENDED SELECT rs FROM test WHERE rs= "rs490000006"; |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------------+
|
| 1 | SIMPLE | test | const | PRIMARY | PRIMARY | 22 | const | 1 | 100.00 | Using index | |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------------+
|
1 row in set, 1 warning (0.01 sec) |
|
|
CHECK TABLE test;+-----------------------------------+-------+----------+----------+
|
| Table | Op | Msg_type | Msg_text |
|
+-----------------------------------+-------+----------+----------+
|
| homo_sapiens_variation_92_37.test | check | status | OK |
|
+-----------------------------------+-------+----------+----------+
|
– CONCLUSION?
Normal inserts/imports will build good tables and indexes but very slow. The time difference between the methods (75 vs. 7 minutes is important - this is a small table 
During the simple insert with symultaneously index build - mysqld will not use the assigned myisam_sort_buffer_size = 55G (my.conf is now attached) but only...
- ps aux | grep mysqld
mysql 1561 0.3 0.4 2938068 264604 ? Ssl Jun12 70:18 /usr/sbin/mysqld
Thank you
my.conf
rawi, thanks! Reproduced the problem on 10.2.15
MariaDB [test]> SELECT * from t2 order by rs desc limit 3;
|
+-------------+
|
| rs |
|
+-------------+
|
| rs500000000 |
|
| rs499999999 |
|
| rs499999998 |
|
+-------------+
|
3 rows in set (0.00 sec)
|
|
|
MariaDB [test]> SELECT rs FROM t2 WHERE rs= "rs500000000";
|
Empty set (0.00 sec)
|
|
|
MariaDB [test]> explain SELECT rs FROM t2 WHERE rs= "rs500000000";
|
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [test]> check table t2;
|
+---------+-------+----------+------------------------------------------+
|
| Table | Op | Msg_type | Msg_text |
|
+---------+-------+----------+------------------------------------------+
|
| test.t2 | check | error | Key in wrong position at page 1722248192 |
|
| test.t2 | check | error | Corrupt |
|
+---------+-------+----------+------------------------------------------+
|
2 rows in set (12.59 sec)
|
The same happens with version 10.3.9.
And the same happens adding the primary key later with
ALTER TABLE test ADD CONSTRAINT test_pk PRIMARY KEY (rs);
|
Any chance this will be some time more closely examined?
Sorry to annoy you with it
Thanks
rawi , sorry for the delay. Reproduced on 10.3.10 as well
|
|
use test;
|
CREATE TABLE t1 (rs VARCHAR(20) PRIMARY KEY) ENGINE=MyISAM DEFAULT CHARSET=latin1;
|
|
|
shutdown the server
|
|
|
/10.3.10/bin/myisamchk --keys-used=0 -rq /10.3.10/dt/test/t1.MYI
|
|
|
-------
|
use test;
|
FLUSH TABLES ; FLUSH TABLE_STATISTICS ; FLUSH INDEX_STATISTICS ;
|
INSERT INTO t1 SELECT CONCAT("rs",seq) from seq_1_to_500000000;
|
FLUSH TABLES ; FLUSH TABLE_STATISTICS ; FLUSH INDEX_STATISTICS ;
|
--------
|
|
|
/10.3.10/bin/myisamchk --analyze --update-state --sort_buffer_size=56G --recover --quick --quick --tmpdir=~/tmp /10.3.10/dt/test/t1.MYI
|
|
|
SELECT * from t2 order by rs desc limit 3;
|
SELECT rs FROM t2 WHERE rs= "rs500000000";
|
explain SELECT rs FROM t2 WHERE rs= "rs500000000";
|
|
MariaDB [test]> SELECT * from t1 order by rs desc limit 3;
|
+-------------+
|
| rs |
|
+-------------+
|
| rs500000000 |
|
| rs499999999 |
|
| rs499999998 |
|
+-------------+
|
3 rows in set (0.000 sec)
|
|
|
MariaDB [test]> SELECT rs FROM t1 WHERE rs= "rs500000000";
|
Empty set (0.000 sec)
|
|
|
MariaDB [test]> explain SELECT rs FROM t1 WHERE rs= "rs500000000";
|
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|
1 row in set (0.001 sec)
|
|
|
MariaDB [test]> select version();
|
+-----------------+
|
| version() |
|
+-----------------+
|
| 10.3.10-MariaDB |
|
+-----------------+
|
1 row in set (0.000 sec)
|
What query returns "Impossible WHERE noticed after reading const tables"? Please add also your .cnf file, thanks!
MariaDB [test]> CREATE or replace TABLE t1 (-> rs VARCHAR(20) PRIMARY KEY-> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;Query OK, 0 rows affected (0.01 sec)MariaDB [test]> insert into t1 select CONCAT("rs",seq) from seq_1_to_500000000;Query OK, 500000000 rows affected (1 hour 50.04 sec)Records: 500000000 Duplicates: 0 Warnings: 0MariaDB [test]> select rs from t1 where rs= "rs490000006";+-------------+| rs |+-------------+| rs490000006 |+-------------+1 row in set (0.00 sec)MariaDB [test]> explain extended select rs from t1 where rs= "rs490000006";+------+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+------+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------------+| 1 | SIMPLE | t1 | const | PRIMARY | PRIMARY | 22 | const | 1 | 100.00 | Using index |+------+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)MariaDB [test]> select version();+-----------------+| version() |+-----------------+| 10.2.15-MariaDB |+-----------------+1 row in set (0.00 sec)