Hi @Lukas,
can you share the CREATE TABLE statement for the original and test table with us (SHOW CREATE TABLE)?
Did you create a snapshot (backup) of your table? https://crate.io/docs/crate/reference/en/latest/admin/snapshots.html
If yes, you should be able to restore the data from your backup. If no, I would recommend to setup backups and create a snapshot before major operations on your cluster.
Is it a bad idea to copy data with a simple
insert into table2 (select * from table 1)
Typically I would recommend to explicitly list the columns you want to copy for improved control what does happen.
insert into table2 (select * from table 1) will work as well but does have some potential pitfalls and only works reliable if schema matches exactly.
Can somebody give some insight into why this happened?
I cannot give a definitive answer. My guess would be that you run into the “Too many open files” issue while the data was copied. Due to this “too many open files” issue CrateDB was not able to open/create new files for new shards and that’s how your cluster ended up in this state.
There is another topic on “Too many open files” in our community which may give you some pointers in debugging this issue: What to do to fix "Too many open files" on a 3-node cluster with almost no data?