GH-40 Adding longblob with column['type'] = longblob, blob binary, varbi...

[akinsgre]

Using @desfrenes suggestion from GH-40. I experience issues migrating a MySQL database with longblob fields and this solved the issue demonstrated in the following backtrace

START CREATING TABLES START - CREATING TABLE aboveground_tank_attachments Traceback (most recent call last): File "/Users/gakins/Projects/python/py-mysql2pgsql/bin/py-mysql2pgsql", line 38, in mysql2pgsql.Mysql2Pgsql(options).convert() File "/usr/local/Cellar/python/2.7.5/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/py_mysql2pgsql-0.1.6-py2.7.egg/mysql2pgsql/mysql2pgsql.py", line 31, in convert Converter(reader, writer, self.file_options, self.run_options.verbose).convert() File "/usr/local/Cellar/python/2.7.5/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/py_mysql2pgsql-0.1.6-py2.7.egg/mysql2pgsql/lib/converter.py", line 31, in convert self.writer.write_table(table) File "/usr/local/Cellar/python/2.7.5/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/py_mysql2pgsql-0.1.6-py2.7.egg/mysql2pgsql/lib/init.py", line 86, in decorated_function ret = f(_args, *_kwargs) File "/usr/local/Cellar/python/2.7.5/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/py_mysql2pgsql-0.1.6-py2.7.egg/mysql2pgsql/lib/postgres_db_writer.py", line 152, in write_table table_sql, serial_key_sql = super(PostgresDbWriter, self).write_table(table) File "/usr/local/Cellar/python/2.7.5/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/py_mysql2pgsql-0.1.6-py2.7.egg/mysql2pgsql/lib/postgres_writer.py", line 213, in write_table primary_keys, serial_key, maxval, columns = self.table_attributes(table) File "/usr/local/Cellar/python/2.7.5/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/py_mysql2pgsql-0.1.6-py2.7.egg/mysql2pgsql/lib/postgres_writer.py", line 189, in table_attributes columns.write(' %s,\n' % self.column_description(column)) File "/usr/local/Cellar/python/2.7.5/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/py_mysql2pgsql-0.1.6-py2.7.egg/mysql2pgsql/lib/postgres_writer.py", line 26, in column_description return '"%s" %s' % (column['name'], self.column_type_info(column)) File "/usr/local/Cellar/python/2.7.5/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/py_mysql2pgsql-0.1.6-py2.7.egg/mysql2pgsql/lib/postgres_writer.py", line 126, in column_type_info default, column_type = get_type(column) File "/usr/local/Cellar/python/2.7.5/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/py_mysql2pgsql-0.1.6-py2.7.egg/mysql2pgsql/lib/postgres_writer.py", line 124, in get_type raise Exception('unknown %s' % column['type']) Exception: unknown longblob

[akinsgre]

Following the advice here.. First I truncated tables with blob data and then did the migration following this advice; with a change to the sed (which didn't work on my Mac). Using this process, the file is succesfully loaded into Postgres. Obviously I'd like to translate this to the py-mysql2pgsql but I'm not sure where to incorporate it. It looks to me (and I don't know Python) that the bits that load the database are in a separate library.

http://dba.stackexchange.com/questions/4211/how-to-migrate-large-blob-table-from-mysql-to-postgresqlsed "

sed "s/0x([0-9,A-F]*),/decode('\1\', 'hex'),/g" prepg.dump > pg.dump

[kworr]

Works for me without this change.

[nikicat]

:+1: affects me too

[sibiantony]

Affects me too. Not sure if this has anything to do with MySQL version (I'm having 5.5.37) on why it doesn't show up for some people. Any ways, it works with this change.

-            elif column['type'] in ('blob', 'binary', 'varbinary'):
+            elif column['type'] in ('blob', 'binary', 'varbinary', 'tinyblob', 'mediumblob', 'longblob'):

[kworr]

Ok, you got me. I'm rejecting this pull because @sibiantony has better list of *blobs.