Suggestions adding new databases/tables to MySql CDC monitoring

[Revanth P]

Hi All,             
  Environment:                                                                                                                        
  - Debezium 3.4.1.Final, MySQL connector                                                                                   
  - Schema history + offset storage: JDBC (MySQL)                                                                      - MySQL cluster: ~800 databases, ~24,000 tables          
  - snapshot.mode=no_data                                                                                                              
   

  We frequently need to add new databases and tables to table.include.list. This requires a connector restart with the updated list.

                                                                                                                            
  Issue 1 — store.only.captured.tables.ddl=true (current setting):                                                 When the connector restarts with new tables added, we get:                                           Encountered change event for table <unmonitored_table> whose schema isn't known to this connecto. This happens because unmonitored tables with active DML appear in the binlog. Since their DDL was never stored (due to store.only.captured.tables.ddl=true), the connector crashes.                                                                                                                                                                                      
  Issue 2 — store.only.captured.tables.ddl=false (attempted fix):                                                Setting this to false resolves the crash, but on startup (especially with snapshot.mode=recovery to rebuild schema history), Debezium reads and stores DDL for all ~24,000 tables across ~800 databases. This takes hours and causes MySQL memory pressure during the process
                                                                                                    

 Any recommended patterns for managing table.include.list changes in large MySQL clusters?                                        

Thanks in advance,
Revanth P     

[Chris Cranford]

Hi -

This is one of the most common issues users often face when needing to adjust the include list after-the-fact. 

Besides the most obvious you mentioned below by using the default `store.only.captured.tables.ddl=false`, users have been known to use a secondary temporary connector with your main connector shutdown down. This temporary connector uses the `recovery` snapshot mode with its own offsets and topic prefix to reprime the schema history topic, using the new updated table include list. However, there is a caveat, and that is you can only use `recovery` as long as from the main connector's offset position to present time, there have been no DDL changes. If there have been or you cannot guarantee there have not been any DDL changes, this approach isn't viable as you'll end up in an inconsistent state between change events and the expected table schema structure.

The main important things to remember:

1. The temp connector cannot share the main connector's offsets nor prefix. It should be considered an isolated, throw-away connector.
2. While you can point the temp connector the main connector's schema history topic, you may prefer to create an entirely new topic, keeping the old one, and simply reconfigure the main connector to use the new re-primed history topic after the temp connector has done its job.
3. Once the schema history topic is re-primed, stop the temp connector, reconfigure/restart the main connector, and it should resume.

That said, this is something that occurs quite frequently, and all the steps for users to take to address this wrinkle is involved, as you can see. There is #1602 [1] that was raised recently with an idea where we could take advantage of a combination of blocking and/or incremental snapshots to address this concern for users (see phases 2 and 3). If anyone is interested in exploring this item's phases, contributions are always welcomed!

Thanks,
-cc

[1]: https://github.com/debezium/dbz/issues/1602

--
You received this message because you are subscribed to the Google Groups "debezium" group.
To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/debezium/af25ded2-6d49-4ec6-91aa-b5f7005a972fn%40googlegroups.com.