Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-7829

Could not execute Update_rows_v1 event on table mysql.table_stats; Can't find record in 'table_stats'

        Details

          Description

          master:

          MariaDB [mysql]> show  variables like '%bin%';
+-----------------------------------------+--------------------------------------------------+
| Variable_name                           | Value                                            |
+-----------------------------------------+--------------------------------------------------+
| binlog_annotate_row_events              | ON                                               |
| binlog_cache_size                       | 32768                                            |
| binlog_checksum                         | NONE                                             |
| binlog_commit_wait_count                | 20                                               |
| binlog_commit_wait_usec                 | 500000                                           |
| binlog_direct_non_transactional_updates | OFF                                              |
| binlog_format                           | ROW                                              |
| binlog_optimize_thread_scheduling       | ON                                               |
| binlog_stmt_cache_size                  | 32768                                            |
| gtid_binlog_pos                         | 0-20077-142671                                   |
| gtid_binlog_state                       | 0-77-2218,0-20076-86398,0-76-2219,0-20077-142671 |
| innodb_api_enable_binlog                | OFF                                              |
| innodb_locks_unsafe_for_binlog          | OFF                                              |
| log_bin                                 | ON                                               |
| log_bin_trust_function_creators         | OFF                                              |
| max_binlog_cache_size                   | 18446744073709547520                             |
| max_binlog_size                         | 1073741824                                       |
| max_binlog_stmt_cache_size              | 18446744073709547520                             |
| sql_log_bin                             | ON                                               |
| sync_binlog                             | 0                                                |

show master status;select * from mysql.table_stats into outfile '/tmp/table_stats.txt'; load data infile '/tmp/table_stats.txt' REPLACE  INTO TABLE mysql.table_stats;
+------------------------------+-----------+--------------+------------------+
| File                         | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------------------+-----------+--------------+------------------+
| tulwaurt006-mysql-bin.000143 | 806528297 |              |                  |
+------------------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)

 mysqlbinlog --verbose --base64-output=DECODE-ROWS --start-position 807572906  tulwaurt006-mysql-bin.000143
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 807572906
# at 807573930
# at 807574874
# at 807575844
# at 807576798
# at 807577780
# at 807578796
# at 807579746
# at 807580714
# at 807581666
# at 807582664
# at 807583704
# at 807584716
# at 807585692
# at 807586722
# at 807587746
# at 807588730
# at 807589754
# at 807590722
# at 807591738
# at 807592720
# at 807593742
# at 807594722
# at 807595684
# at 807596646
# at 807597632
# at 807598608
# at 807599568
# at 807600596
# at 807601620
#150325 14:52:02 server id 20077  end_log_pos 807573930         Update_rows: table id 654
#150325 14:52:02 server id 20077  end_log_pos 807574874         Update_rows: table id 654
#150325 14:52:02 server id 20077  end_log_pos 807575844         Update_rows: table id 654
....
#150325 14:52:02 server id 20077  end_log_pos 807598608         Update_rows: table id 654
#150325 14:52:02 server id 20077  end_log_pos 807599568         Update_rows: table id 654
#150325 14:52:02 server id 20077  end_log_pos 807600596         Update_rows: table id 654
#150325 14:52:02 server id 20077  end_log_pos 807601620         Update_rows: table id 654
#150325 14:52:02 server id 20077  end_log_pos 807602020         Update_rows: table id 654 flags: STMT_END_F
### Row event for unknown table #654### Row event for unknown table #654### Row event for unknown table #654### Row event for unknown table #654### Row event for unknown table #654### Row event
 for unknown table #654### Row event for unknown table #654### Row event for unknown table #654### Row event for unknown table #654### Row event for unknown table #654### Row event for unknown 
table #654### Row event for unknown table #654### Row event for unknown table #654### Row event for unknown table #654### Row event for unknown table #654### Row event for unknown table #654###
 Row event for unknown table #654### Row event for unknown table #654### Row event for unknown table #654### Row event for unknown table #654### Row event for unknown table #654### Row event fo
r unknown table #654### Row event for unknown table #654### Row event for unknown table #654### Row event for unknown table #654### Row event for unknown table #654### Row event for unknown tab
le #654### Row event for unknown table #654### Row event for unknown table #654### Row event for unknown table #654# at 807602020
#150325 14:52:02 server id 20077  end_log_pos 807602090         Query   thread_id=1277  exec_time=0     error_code=0
SET TIMESTAMP=1427260922/*!*/;
SET @@session.pseudo_thread_id=1277/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
COMMIT
/*!*/;
# at 807602090
#150325 14:52:02 server id 20077  end_log_pos 807602128         GTID 0-20077-141913

          slave:

                        Master_Log_File: tulwaurt006-mysql-bin.000143
          Read_Master_Log_Pos: 852465402
               Relay_Log_File: tulwaurt005-relay-bin.000022
                Relay_Log_Pos: 807571992
        Relay_Master_Log_File: tulwaurt006-mysql-bin.000143
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1032
                   Last_Error: Could not execute Update_rows_v1 event on table mysql.table_stats; Can't find record in 'table_stats', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log tulwaurt006-mysql-bin.000143, end_log_pos 807572906
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 807571693
              Relay_Log_Space: 1927121418
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1032
               Last_SQL_Error: Could not execute Update_rows_v1 event on table mysql.table_stats; Can't find record in 'table_stats', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log tulwaurt006-mysql-bin.000143, end_log_pos 807572906
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 20077
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: Slave_Pos
                  Gtid_IO_Pos: 0-20077-141955

| Variable_name                           | Value                                            |
+-----------------------------------------+--------------------------------------------------+
| binlog_annotate_row_events              | ON                                               |
| binlog_cache_size                       | 32768                                            |
| binlog_checksum                         | NONE                                             |
| binlog_commit_wait_count                | 20                                               |
| binlog_commit_wait_usec                 | 50000                                            |
| binlog_direct_non_transactional_updates | OFF                                              |
| binlog_format                           | MIXED                                            |
| binlog_optimize_thread_scheduling       | ON                                               |
| binlog_stmt_cache_size                  | 32768                                            |
| gtid_binlog_pos                         | 0-20077-142746                                   |
| gtid_binlog_state                       | 0-77-2218,0-20076-86398,0-76-2219,0-20077-142746 |
| innodb_api_enable_binlog                | OFF                                              |
| innodb_locks_unsafe_for_binlog          | OFF                                              |
| log_bin                                 | ON                                               |
| log_bin_trust_function_creators         | OFF                                              |
| max_binlog_cache_size                   | 18446744073709547520                             |
| max_binlog_size                         | 1073741824                                       |
| max_binlog_stmt_cache_size              | 18446744073709547520                             |
| sql_log_bin                             | ON                                               |
| sync_binlog                             | 0                                                |


MariaDB [(none)]> show global variables like '%para%';
+-------------------------------+--------+
| Variable_name                 | Value  |
+-------------------------------+--------+
| slave_domain_parallel_threads | 10     |
| slave_parallel_max_queued     | 131072 |
| slave_parallel_threads        | 20     |

          inspired by the will to work around MDEV-7363

            Gliffy Diagrams

              Attachments

                Issue Links

                relates to

                Bug - A problem which impairs or prevents the functions of the product. MDEV-485 Statistics: Row-based replication aborts on some DDL statements if statistics is enabled

                • Major - Major loss of function.
                • Closed

                Task - A task that needs to be done. MDEV-7363 bulk engine-independent index stats manipulation restricted due to ER_WRONG_LOCK_OF_SYSTEM_TABLE

                • Minor - Minor loss of function, or other problem where easy workaround is present.
                • Open

                Task - A task that needs to be done. MDEV-7901 re-implement analyze table for low impact

                • Major - Major loss of function.
                • Stalled

                Task - A task that needs to be done. MDEV-7363 bulk engine-independent index stats manipulation restricted due to ER_WRONG_LOCK_OF_SYSTEM_TABLE

                • Minor - Minor loss of function, or other problem where easy workaround is present.
                • Open

                  Activity

                  Ascending order - Click to sort in descending order
                  Hide
                  Permalink
                  danblack Daniel Black added a comment -

                  no problems with statement based

                  show master status; set binlog_format=statement;select * from mysql.table_stats into outfile '/tmp/table_stats.txt'; load data infile '/tmp/table_stats.txt' REPLACE  INTO TABLE mysql.table_stats;
                  Show
                  danblack Daniel Black added a comment - no problems with statement based show master status; set binlog_format=statement;select * from mysql.table_stats into outfile '/tmp/table_stats.txt'; load data infile '/tmp/table_stats.txt' REPLACE INTO TABLE mysql.table_stats;
                  Hide
                  Permalink
                  elenst Elena Stepanova added a comment -

                  This is actually expected. Persistent statistic updates are not replicated (and cannot be, really); so the contents of the tables can easily be different. If you update the table directly, which you normally shouldn't, and if you aren't careful enough to set sql_log_bin to 0 while doing that, bad things will happen.

                  Show
                  elenst Elena Stepanova added a comment - This is actually expected. Persistent statistic updates are not replicated (and cannot be, really); so the contents of the tables can easily be different. If you update the table directly, which you normally shouldn't, and if you aren't careful enough to set sql_log_bin to 0 while doing that, bad things will happen.
                  Hide
                  Permalink
                  danblack Daniel Black added a comment -

                  While the replication on persistent statistics makes the assumption that the data is the same on the slaves, which is the goal for many replication architectures, I'm generally willing to take that risk compared to the consequence of a replicating analyze table blocking writes to tables for what can be 30mins+ for big tables and using a far amount of IO in the meantime. Is there another consequence to direct manipulation that I'm missing?

                  Would having engine independent stats table as engine=innodb with STATS_PERSISTENT=0 like innodb_index_stats/innodb_table_stats help?

                  CREATE TABLE `innodb_index_stats` (
...
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;

                  Perhaps this should be a feature request for ANALYZE RESULTS_TO_BINLOG TABLE .... Is that more palatable?

                  Show
                  danblack Daniel Black added a comment - While the replication on persistent statistics makes the assumption that the data is the same on the slaves, which is the goal for many replication architectures, I'm generally willing to take that risk compared to the consequence of a replicating analyze table blocking writes to tables for what can be 30mins+ for big tables and using a far amount of IO in the meantime. Is there another consequence to direct manipulation that I'm missing? Would having engine independent stats table as engine=innodb with STATS_PERSISTENT=0 like innodb_index_stats/innodb_table_stats help? CREATE TABLE `innodb_index_stats` ( ... ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0; Perhaps this should be a feature request for ANALYZE RESULTS_TO_BINLOG TABLE .... Is that more palatable?
                  Hide
                  Permalink
                  elenst Elena Stepanova added a comment - - edited

                  Well, there are lots of consequences of replicating updates to persistent stats.

                  First, a slave might be not MariaDB 10.0/10.1. It can be for example MySQL 5.6 – while it's not our main focus to maintain downgrade/crossgrade replication, we wouldn't want to break it without a good reason.
                  Second, stat tables are currently MyISAM; updating them in a transaction with InnoDB tables creates a "transactional-non-transactional" mix, which the traditional replication is consistently bad at (also, I can't say right away how exactly it will affect parallel replication, but I don't expect anything good).
                  Third, replicating stat table events won't really solve anything – ANALYZE TABLE has always been replicated as a statement, statistics or not, so you'll just get both row events and the actual ANALYZE.
                  I can probably continue. I had at least one other reason, but I removed it because it doesn't sound right and I forgot what I meant while I was writing it.

                  Of course, all arguments concern certain different replication setups. If you talk about an idealistic one – replication is strictly between the same versions or up; you always use the same binlog format; you don't do any writes on slave and the data is exactly the same; settings (statistics-wise) are exactly the same – then yes, the current implementation is definitely not optimal. Unfortunately, we can't support ideal scenarios only.

                  If you can think of a solution which will improve the current approach without breaking various scenarios we need to support, you are more than welcome to request the feature... or even implement it and submit a patch

                  Show
                  elenst Elena Stepanova added a comment - - edited Well, there are lots of consequences of replicating updates to persistent stats. First, a slave might be not MariaDB 10.0/10.1. It can be for example MySQL 5.6 – while it's not our main focus to maintain downgrade/crossgrade replication, we wouldn't want to break it without a good reason. Second, stat tables are currently MyISAM; updating them in a transaction with InnoDB tables creates a "transactional-non-transactional" mix, which the traditional replication is consistently bad at (also, I can't say right away how exactly it will affect parallel replication, but I don't expect anything good). Third, replicating stat table events won't really solve anything – ANALYZE TABLE has always been replicated as a statement, statistics or not, so you'll just get both row events and the actual ANALYZE. I can probably continue. I had at least one other reason, but I removed it because it doesn't sound right and I forgot what I meant while I was writing it. Of course, all arguments concern certain different replication setups. If you talk about an idealistic one – replication is strictly between the same versions or up; you always use the same binlog format; you don't do any writes on slave and the data is exactly the same; settings (statistics-wise) are exactly the same – then yes, the current implementation is definitely not optimal. Unfortunately, we can't support ideal scenarios only. If you can think of a solution which will improve the current approach without breaking various scenarios we need to support, you are more than welcome to request the feature... or even implement it and submit a patch
                  Hide
                  Permalink
                  elenst Elena Stepanova added a comment -

                  According to the comment above, closing as "not a bug" for now.
                  Daniel has created a new task MDEV-7901 for re-implementing ANALYZE TABLE.

                  Show
                  elenst Elena Stepanova added a comment - According to the comment above, closing as "not a bug" for now. Daniel has created a new task MDEV-7901 for re-implementing ANALYZE TABLE.

                    People

                    • Assignee:
                      Unassigned
                      Reporter:
                      danblack Daniel Black
                    • Votes:
                      0 Vote for this issue
                      Watchers:
                      2 Start watching this issue

                      Dates

                      • Due:
                        Created:
                        Updated:
                        Resolved: