So presumably mysqldump can export to ansi compatible format so that you can import to other vendors' databases.
However trying to import to an apache derby I face all this syntax errors. Thought I try some regex and fix things but it seems that there are more than a couple.
Is there something I'm missing about mysqldump? Tried ddlutils but couldn't make progress after step 3 with these docs which seem out of date.
Do I have any other options with that?
The mysqldump option --compatible=ansi doesn't produce a SQL script that is fully ANSI compatible, it just tries to produce a result that is more ANSI compatible: "Produce output that is more compatible with other database systems or with older MySQL servers."
Unfortunately, the SQL syntax supported by each database is a bit different. SQL is a relatively weak standard, much weaker than Javascript or even C. Some databases support compatibility features like MySQL does, or even compatibility modes (disclaimer: I'm the main author of H2), but even this is not enough sometimes.
You have multiple options: you could change the generated SQL script by hand, or you could use another tool to copy the table structure / data. One such tool is the SQuirreL DB Copy Plugin (I have never actually used it, but from what I heard it works well).