ERROR Bad character - in base64 decode

[jmccann]

This seems related to #60

I'm using a CI/CD tool called Drone and started using it with sqlite3. I'm trying to migrate to postgresql.

When using pgloader to migrate the data I see:

2016-05-26T02:57:20.711000Z ERROR Bad character - in base64 decode

It seems when it tries to migrate data in sqlite3 table like: https://github.com/drone/drone/blob/v0.4.2/store/datastore/ddl/sqlite3/1_init.sql#L50-L57

That it fails to properly decode the data. As you can see the BLOBs are public/private keys that contain '-' in them.

The postgresql schema is defined like: https://github.com/drone/drone/blob/v0.4.2/store/datastore/ddl/postgres/1_init.sql#L40-L47

Just curious if you had any thoughts on how to workaround this issue.

Thanks!

[dimitri]

The current SQLite support in pgloader considers that if a SQLite column is declared of type blob but a value in that column is reported as text by the driver, then it must be a base64 encoding. That decision is hard-coded in the parse-value function at https://github.com/dimitri/pgloader/blob/master/src/sources/sqlite/sqlite.lisp#L38.

We need to find a way to expose that decision to the user and turn the current hard-coded solution into a default value with other options.

[dimitri]

Could you share with me some sample data, ideally a SQLite file with some data in that keys table and the PostgreSQL BYTEA you expect from them. That would allow me to fix your bug. Thanks!

[jmccann]

@dimitri I don't really have any additional data to provide and I manually cleaned things up and got up and running long ago. This isn't really an issue I personally am having anymore.

Feel free to close if you want or leave open if you want to continue to track.

[dimitri]

Thanks for your input!

[darioleanbit]

Hi @dimitri is there any way to workaround this? pgloader is currently failing on a SQLite database with BLOB columns containing e.g. XML.

I don't quite get the reasoning behind the assumption of the "base64" encoding, a BLOB is used for opaque binary data, I think it's better to "leave it alone" by default.