OpenSUSE 13.2 x64

I assume that you are using RBR, and that by "broken indexes" you mean that statistics is not collected (I don't see anything else wrong in the output that you provided).

But what remains unclear is what you mean by "just one out of 22 tables have exactly the same problem". Do other tables not have any problem, and their statistics on slaves is identical to the master, or is it just that the stat values are anything but NULL?

From what I see, statistics is indeed not collected upon executing row events. In some cases values remain NULL, in other they are populated with some rubbish. It's the same in MySQL, only values are different.

I couldn't find in the manual anything about whether statistics in this case is supposed to be collected. serg, what do you say?

Test case to reproduce is pretty simple:

--source include/master-slave.inc

--source include/have_binlog_format_row.inc

--enable_connect_log

create table t1 (pk int auto_increment primary key, i int unsigned not null, c varchar(255), key(i), key(c)) engine=MyISAM;

load data local infile '1.csv' into table t1 (i,c);

query_vertical show index from t1;

--sync_slave_with_master

query_vertical show index from t1;

--connection master

drop table t1;

--disable_connect_log

--source include/rpl_end.inc

Output on MySQL 5.7

show index from t1;

Table   t1

Non_unique      0

Key_name        PRIMARY

Seq_in_index    1

Column_name     pk

Collation       A

Cardinality     10000

Sub_part        NULL

Packed  NULL

Null

Index_type      BTREE

Comment

Index_comment

Table   t1

Non_unique      1

Key_name        i

Seq_in_index    1

Column_name     i

Collation       A

Cardinality     1000

Sub_part        NULL

Packed  NULL

Null

Index_type      BTREE

Comment

Index_comment

Table   t1

Non_unique      1

Key_name        c

Seq_in_index    1

Column_name     c

Collation       A

Cardinality     100

Sub_part        NULL

Packed  NULL

Null    YES

Index_type      BTREE

Comment

Index_comment

show index from t1;

Table   t1

Non_unique      0

Key_name        PRIMARY

Seq_in_index    1

Column_name     pk

Collation       A

Cardinality     10000

Sub_part        NULL

Packed  NULL

Null

Index_type      BTREE

Comment

Index_comment

Table   t1

Non_unique      1

Key_name        i

Seq_in_index    1

Column_name     i

Collation       A

Cardinality     10000

Sub_part        NULL

Packed  NULL

Null

Index_type      BTREE

Comment

Index_comment

Table   t1

Non_unique      1

Key_name        c

Seq_in_index    1

Column_name     c

Collation       A

Cardinality     2000

Sub_part        NULL

Packed  NULL

Null    YES

Index_type      BTREE

I didn't say "just one table". I said "22 tables have this exact problem" - broken indexes. And yes, index without statistics (cardinality) will not be used. So in the sense it is broken.
The replication is in mixed mode, not row-based.

Vitaliy, sorry for the mix-up about 1 vs 22 tables, I misunderstood you.
For the binlog format, in this case mixed is the same row-based, because LOAD DATA INFILE is considered unsafe for SBR and hence causes an automatic switch to row format in MBR mode (it doesn't produce a warning in SBR, which is strange, but that's another story).

I don't see any bug here. The statistics is created when you do ANALYZE TABLE. Sometimes it might be created in other cases, as a side effect. You should not rely on that, just as you should not rely on SELECT * FROM table returning rows in any specific order. If you need rows to be ordered — you should use ORDER BY. If you need to update your table's statistics — you should use ANALYZE TABLE.

Please reopen. You did not read correctly. This table is created from scratch. And has all data inserted into it in one operation. This should always update table statistics. Especially that this operation is performed in exactly the same way on slave, replicating all master operations. The load data infile in mixed mode sends SDL with file contents.

Sorry for closing the issue prematurely. I've reopened it.

Still, I did understand the situation correctly. This table is created from scratch. And has all data inserted into it in one operation. This is true. But this does not mean that the statistics will be automatically updated, not at all. Usually when you bulk insert into an empty MyISAM table the statistics is updated as a convenient side-effect of the "repair-by-sort" optimization, but not always. You don't even need to use replication — create a table with only UNIQUE indexes (over nullable columns) and LOAD DATA into it, you will see that the cardinality will be NULL.

The only reliable way of updating table statistics is ANALYZE TABLE. Everything else is not guaranteed to work.

But why does it behave differently on Master and Slave servers? Almost reminds me of an old old bug that was breaking cardinality after table rename. But that was around 5.0.x.

As to claim that only "analyze table" shell update cardinality. This is not true. I could not find anything stating this in the MySQL manual. On the contrary. Everyone saying that all update operations will update it. But it might not be accurate.

When a statement inserts many rows (e.g. LOAD DATA or INSERT ... SELECT) into an empty MyISAM table, MyISAM uses bulk-insert optimization — it disables all non-UNIQUE indexes and creates them at the end of the statement using "repair-by-sort". This gives a significant speedup, as repair-by-sort can be orders of magnitude faster than inserting keys into the index one by one. Cardinality data are a byproduct of the repair-by-sort.

Note that the table must be originally empty, and this optimization applies only to UNIQUE indexes. Granted, NOT NULL UNIQUE do not need much statistics, the cardinality is always one there, but nullable UNIQUE indexes will have no statistics after a LOAD DATA. Also this optimization never updates engine independent statistics.

In your particular case, a bulk insert operation (LOAD DATA) on the master is converted into a series of single row inserts on the slave (RBR Write_row events). That's why the slave gets no index statistics. Run ANALYZE TABLE after the LOAD DATA and you'll have it.

Now, I'm starting to think that it might be possible to turn a LOAD DATA on the slave into a bulk insert operation. But it will be a new optimization, by no means a bug fix. This optimization, if at all possible, cannot be done in 10.0

I see what you saying. Yes, we are using LOAD DATA to speed up date import. And yes I know that it rebuilds all indexes after finishing load. So in RBR the data load will be significantly slower, but also indexes will not be optimal and will be missing statistics.

Think it worked for me before, several years ago, because replication was statement based then. After switching it over to mixed mode it seems most statements are replicated as RBR. Just looked at the relay log and indeed all rows for LOAD DATA are sent as RBR not original file:

mysqld-relay-bin.000589 18300829        Write_rows_v1   11      18301409        table_id: 14839

mysqld-relay-bin.000589 18301696        Write_rows_v1   11      18302311        table_id: 14839

So it's not a bug indeed. Just an implication of RBR. Thanks for clarification.