Phantom records

[hlcianfagna]

Create a MySQL source with:

CREATE SCHEMA source;
create table source.test2 (
  mypk int not null,
  field1 text,
  primary key (mypk)
);

Create a CrateDB materialization with:

• hard deletes
• use mysql_demo as alternative schema, database, and database_shema
• NO delta updates

Run on MySQL:

insert into source.test2 select 1 as mypk, 'abc' as field1;
insert into source.test2 select 2 as mypk, 'def' as field1;
delete from source.test2  where mypk=1;
update source.test2 set field1='UPDATED' where mypk=2;
delete from source.test2  where mypk=2;

The table on MySQL will now be empty.
The table on CrateDB will still have the record with mypk=2

If we try deleting the CrateDB materialization,
and manually emptying the table,
and then recreating the CrateDB materialization again (even with a different name)
the record with mypk=1 sometimes will appear out of nowhere (even if the process is repeated manually deleting the left behind flow tables)