All of Software Unscripted

So it’s a dump as SQL DDL and Insert statements, but most of the data is serialized binaries?

I think so.

I think I need to visualise the schemas and get some specific data out (if it’s in there).

Looks like this

Ah, so no DDL and the inserts depend on column order?

not sure if I know enough SQL to answer that. It has a bunch of CREATE TABLE statements in the beginning.

In this case, I’d just try and run the whole script against a dockerized, uninitialized MySQL Database and see what happens

Seeing a Flyway reference in there is worrying, it’s like migratus in the Clojure world and hints that there might be additional DDL files in a programs source code somewhere

Uh oh

Ah, nevermind, if it’s a dump it shouldn’t matter

ok, phew

sorry 😄

Do you think it needs to be MySQL or can I perhaps use SQLite?

I’d use MySQL to be sure that the collation et al work, especially the binary format might not work in another DB. The bigger question might be, what the binary stores represent. If they are serialized Java Objects and you don’t have the source code anymore it might get tricky

it's referencing InnoDB in the DDL - so it's MySQL

I’ve been trying to get her to provide me with the export for 2 years… she finally delivers and it can’t be used. Now all I have is this dump 😛 will be interesting to see whether her work is completely wasted.

After a quick search, there seem to be tools that can restore deserialized java objects even if the class files are missing. Archeology time!

tried a couple of the leading bytes from the binaries against - https://en.wikipedia.org/wiki/List_of_file_signatures - no matches... which hopefully rules out serialized java objects (which start with AC ED)

thanks for the detective work, guys

I’m in. Now I just need to figure out where the magic happens.

Maybe db-xray will help explore this from repl, too

This is the stuff that they don’t tell you about when they’re trying to recruit you with fancy tutorials for using pure functions and making beautiful GUIs in few keystrokes.

This is more thrilling than most netflix productions!

For some reason I always want to find out what’s in a binary data representation if I see one 🙂

So I managed to poke around the data for a bit and as feared all of the IDs I was looking for were NULL. So my colleagues have altogether spent probably a year wasting their time...

They can’t re-export in a different manner?

No this is the raw data. The data simply isn't there. If they had exported a sample of the stuff in 2020 like I asked, this wouldn't have been an issue...

Or, reshape the data in a manner that ensures that you get what you want upon export.

Welp, that’s terrible.

Oops

eek - how do you work for a year without ever checking that you are recording what needs to be recorded ?

By ignoring the guy asking for a sample multiple times a year for 3 years.

Unfortunately. I had a feeling this would happen.

Maybe there are some relations? Have you tried looking at an ERD of the db?

Won’t help if all the fields are null of course 🙂

the work they have done is that of manually linking two wordnets, but the software they used uses its own binary IDS to link the wordnet entries. There is a table that translates this makeshift binary ID to one we know for one wordnet dataset (the English one) but all of the IDS for the Danish data are NULL. We would have discovered this early if they had produced a sample, but this sample never materialised for 3 years...

rip

Indeed

👀

Does the original data with that software still exist before export? Are the ids there correct or were they corrupted from the get go?

BTW I'd use a GUI to explore this. I personally use TablePlus on Mac - sometimes a GUI helps you see data you'd otherwise miss.

DBeaver for lnx

@UK0810AQ2 There was never any data corruption, AFAIK, just a lack of due diligence.

Better ask Jørgen.

What a sucky situation @U4P4NREBY - hope my joke works for you.

I chuckled 😉

that’s a rather classic case study… my condolences to the data loss

Thank you @U6L5FFL95. 🥲

You mean my SQL woes?

no, i've been refactoring a library which has accreted function over 7 years, to give it a nice clean interface...

doing good work

hopefully doing good work - it's open-sourced, and a distillation of all the learnings we've had working with a streams+promises system

remember the rule of spring cleaning - empty the drawer on to the kitchen floor and put things back in that you want to keep, rather than trying to remove things you don't like from the drawer i.e. in code: make a new namespace, copy stuff over bit by bit, rather than trying to cut stuff away from existing code it doesn't always work out but i find it's a better way to start. it also has the benefit that you can do multiple iterations and compare

i did exactly that on the main namespace @U0509NKGK , then ditched the old namespace once done

@U0509NKGK that is very good advice

thank you 😊

Bug in production?

it's definitely something

a memoriam to the first observable bug