PostgreSQL Cuckoo migration from 2.0.0-dev/RC2 to Cuckoo 2.0.2 fails

[seanthegeek]

We've identified a Cuckoo Sandbox 2.0-dev installation!
Reading in the old configuration..
  configuration has been migrated to the latest version!

Traceback (most recent call last):
  File "/usr/local/bin/cuckoo", line 11, in <module>
    sys.exit(main())
  File "/usr/local/lib/python2.7/dist-packages/click/core.py", line 716, in __call__
    return self.main(*args, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/click/core.py", line 696, in main
    rv = self.invoke(ctx)
  File "/usr/local/lib/python2.7/dist-packages/click/core.py", line 1060, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/usr/local/lib/python2.7/dist-packages/click/core.py", line 889, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/usr/local/lib/python2.7/dist-packages/click/core.py", line 534, in invoke
    return callback(*args, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/click/decorators.py", line 17, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/cuckoo/main.py", line 562, in import_
    import_cuckoo(ctx.parent.user, mode, path)
  File "/usr/local/lib/python2.7/dist-packages/cuckoo/apps/import_.py", line 147, in import_cuckoo
    sqldump(cfg["cuckoo"]["database"]["connection"], dirpath)
  File "/usr/local/lib/python2.7/dist-packages/cuckoo/apps/import_.py", line 74, in sqldump
    args, env = dumpcmd(dburi, dirpath)
  File "/usr/local/lib/python2.7/dist-packages/cuckoo/apps/import_.py", line 46, in dumpcmd
    l = re.match(SQLRE, dburi).groups()
AttributeError: 'NoneType' object has no attribute 'groups'
cuckoo@LXOH001CUCKO01:~$ cuckoo

  .-----------------.
  | Cuckoo Sandbox? |
  |     OH NOES!    |\  '-.__.-'
  '-----------------' \  /oo |--.--,--,--.
                         \_.-'._i__i__i_.'
                               """""""""

 Cuckoo Sandbox 2.0.1
 www.cuckoosandbox.org
 Copyright (c) 2010-2017

 Checking for updates...
 You're good to go!
CuckooDatabaseError: DB schema version mismatch: found cd31654d187, expected 181be2111077. Optionally make a backup and then apply the latest database migration(s) by running `cuckoo --cwd ~/.cuckoo migrate`.
cuckoo@LXOH001CUCKO01:~$ cuckoo --cwd ~/.cuckoo migrate
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade cd31654d187 -> 1f28e0e5aa6b, Cast Machine.resultserver_port to int (from Cuckoo 2.0-rc2 to 2.0.0)
INFO  [alembic.runtime.migration] Running upgrade 1f28e0e5aa6b -> 796174689511, Extend length of Error.message (from Cuckoo 2.0-rc2 to 2.0.0)
INFO  [alembic.runtime.migration] Running upgrade 796174689511 -> af16beb71aa7, submit table (from Cuckoo 2.0-rc2 to 2.0.0)
Traceback (most recent call last):
  File "/usr/local/bin/alembic", line 11, in <module>
    sys.exit(main())
  File "/usr/local/lib/python2.7/dist-packages/alembic/config.py", line 479, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/usr/local/lib/python2.7/dist-packages/alembic/config.py", line 473, in main
    self.run_cmd(cfg, options)
  File "/usr/local/lib/python2.7/dist-packages/alembic/config.py", line 456, in run_cmd
    **dict((k, getattr(options, k)) for k in kwarg)
  File "/usr/local/lib/python2.7/dist-packages/alembic/command.py", line 174, in upgrade
    script.run_env()
  File "/usr/local/lib/python2.7/dist-packages/alembic/script/base.py", line 407, in run_env
    util.load_python_file(self.dir, 'env.py')
  File "/usr/local/lib/python2.7/dist-packages/alembic/util/pyfiles.py", line 93, in load_python_file
    module = load_module_py(module_id, path)
  File "/usr/local/lib/python2.7/dist-packages/alembic/util/compat.py", line 79, in load_module_py
    mod = imp.load_source(module_id, path, fp)
  File "./env.py", line 59, in <module>
    run_migrations_online()
  File "./env.py", line 52, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/usr/local/lib/python2.7/dist-packages/alembic/runtime/environment.py", line 797, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/usr/local/lib/python2.7/dist-packages/alembic/runtime/migration.py", line 312, in run_migrations
    step.migration_fn(**kw)
  File "/usr/local/lib/python2.7/dist-packages/cuckoo/data-private/db_migration/versions/from_20c2_to_200_submit_table.py", line 28, in upgrade
    sa.PrimaryKeyConstraint("id")
  File "<string>", line 8, in create_table
  File "<string>", line 3, in create_table
  File "/usr/local/lib/python2.7/dist-packages/alembic/operations/ops.py", line 1098, in create_table
    return operations.invoke(op)
  File "/usr/local/lib/python2.7/dist-packages/alembic/operations/base.py", line 318, in invoke
    return fn(self, operation)
  File "/usr/local/lib/python2.7/dist-packages/alembic/operations/toimpl.py", line 101, in create_table
    operations.impl.create_table(table)
  File "/usr/local/lib/python2.7/dist-packages/alembic/ddl/impl.py", line 194, in create_table
    self._exec(schema.CreateTable(table))
  File "/usr/local/lib/python2.7/dist-packages/alembic/ddl/impl.py", line 118, in _exec
    return conn.execute(construct, *multiparams, **params)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 914, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 968, in _execute_ddl
    compiled
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
    context)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
    exc_info
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 450, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) relation "submit" already exists
 [SQL: '\nCREATE TABLE submit (\n\tid SERIAL NOT NULL, \n\ttmp_path TEXT, \n\tadded TIMESTAMP WITHOUT TIME ZONE NOT NULL, \n\tsubmit_type VARCHAR(16), \n\tdata TEXT, \n\tPRIMARY KEY (id)\n)\n\n']
>>> Error migrating your database..

[jbremer]

Yes, I believe this is caused by the fact that you ran the cuckoo command before the cuckoo migrate command which seems to create any missing tables before checking the actual database version. I'm wondering if swapping around this logic (i.e., check & create new tables rather than create new tables & check) will be a proper workaround or if the database migrations should check for the existence of the submit (and alike) tables. Thanks for reporting!

[seanthegeek]

Also, note the error that occured when I tried to import (and migrate) before running cuckoo for the first time.

[jbremer]

@seanthegeek Only just noticed, do you have any special characters in your SQL URI? Can you perhaps share it (or something that looks like it, as in, same characters in use etc) without the password of course? Seems that parsing it is where the import command failed.

[seanthegeek]

I ended up just running a clean command since it was just my dev instance. After that it worked fine with the same SQL URL

[jbremer]

@seanthegeek Yes, I understand, but I'd like to fix this issue for the next person running into the same issue - that said, could you the important pieces of the SQL URI as I mentioned above? E.g., are there non-alphabet characters in the username or something? Guess I'll have to make the regex a bit less strict anyway.

[jbremer]

With the commit above I think this should be much better now. If you'd be so kind to re-test cuckoo import (even though you have no intentions of using it), that'd be awesome! I'll have to think about the submit table exists issue a bit more, though :-)

[seanthegeek]

I'll have to stand up a new instance of legacy. Probably this weekend.

[jbremer]

Revisited & improved some more cuckoo import-related code and based on that I think we can do without any additional does submit table exist logic. If more people run into such issue I'll fix it, though, but now that the SQL regex part has been replaced by something more accurate (see also the commit above) I think we're good to go. If you test it, do let me know! Closing the issue for now as I think this is resolved now.

[ewalkup]

@jbremer I've just encountered this problem on a migration from 1.3 to 2.0.3 (overdue I know). This is on a MySQL dev instance but eventually I will need to migrate a production server and I can't delete that data. I can fix it by manually dropping the empty submit table, just seems odd it's still happening.

INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade 3aa42d870199 -> 4a04f40d4ab4, processing column (from Cuckoo 1.2 to 2.0-rc1)
INFO  [alembic.runtime.migration] Running upgrade 4a04f40d4ab4 -> 1070cd314621, taken route for a task (from Cuckoo 1.2 to 2.0-rc1)
INFO  [alembic.runtime.migration] Running upgrade 1070cd314621 -> 1583656cb935, guest status (from Cuckoo 1.2 to 2.0-rc1)
INFO  [alembic.runtime.migration] Running upgrade 1583656cb935 -> cd31654d187, machine options (from Cuckoo 1.2 to 2.0-rc1)
INFO  [alembic.runtime.migration] Running upgrade cd31654d187 -> 1f28e0e5aa6b, Cast Machine.resultserver_port to int (from Cuckoo 2.0-rc2 to 2.0.0)
INFO  [alembic.runtime.migration] Running upgrade 1f28e0e5aa6b -> 796174689511, Extend length of Error.message (from Cuckoo 2.0-rc2 to 2.0.0)
INFO  [alembic.runtime.migration] Running upgrade 796174689511 -> af16beb71aa7, submit table (from Cuckoo 2.0-rc2 to 2.0.0)
Traceback (most recent call last):
  File "/usr/local/bin/alembic", line 9, in <module>
    load_entry_point('alembic==0.8.8', 'console_scripts', 'alembic')()
  File "/usr/local/lib/python2.7/dist-packages/alembic-0.8.8-py2.7.egg/alembic/config.py", line 479, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/usr/local/lib/python2.7/dist-packages/alembic-0.8.8-py2.7.egg/alembic/config.py", line 473, in main
    self.run_cmd(cfg, options)
  File "/usr/local/lib/python2.7/dist-packages/alembic-0.8.8-py2.7.egg/alembic/config.py", line 456, in run_cmd
    **dict((k, getattr(options, k)) for k in kwarg)
  File "/usr/local/lib/python2.7/dist-packages/alembic-0.8.8-py2.7.egg/alembic/command.py", line 174, in upgrade
    script.run_env()
  File "/usr/local/lib/python2.7/dist-packages/alembic-0.8.8-py2.7.egg/alembic/script/base.py", line 407, in run_env
    util.load_python_file(self.dir, 'env.py')
  File "/usr/local/lib/python2.7/dist-packages/alembic-0.8.8-py2.7.egg/alembic/util/pyfiles.py", line 93, in load_python_file
    module = load_module_py(module_id, path)
  File "/usr/local/lib/python2.7/dist-packages/alembic-0.8.8-py2.7.egg/alembic/util/compat.py", line 79, in load_module_py
    mod = imp.load_source(module_id, path, fp)
  File "./env.py", line 59, in <module>
    run_migrations_online()
  File "./env.py", line 52, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/usr/local/lib/python2.7/dist-packages/alembic-0.8.8-py2.7.egg/alembic/runtime/environment.py", line 797, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/usr/local/lib/python2.7/dist-packages/alembic-0.8.8-py2.7.egg/alembic/runtime/migration.py", line 312, in run_migrations
    step.migration_fn(**kw)
  File "/usr/local/lib/python2.7/dist-packages/Cuckoo-2.0.3-py2.7.egg/cuckoo/data-private/db_migration/versions/from_20c2_to_200_submit_table.py", line 28, in upgrade
    sa.PrimaryKeyConstraint("id")
  File "<string>", line 8, in create_table
  File "<string>", line 3, in create_table
  File "/usr/local/lib/python2.7/dist-packages/alembic-0.8.8-py2.7.egg/alembic/operations/ops.py", line 1098, in create_table
    return operations.invoke(op)
  File "/usr/local/lib/python2.7/dist-packages/alembic-0.8.8-py2.7.egg/alembic/operations/base.py", line 318, in invoke
    return fn(self, operation)
  File "/usr/local/lib/python2.7/dist-packages/alembic-0.8.8-py2.7.egg/alembic/operations/toimpl.py", line 101, in create_table
    operations.impl.create_table(table)
  File "/usr/local/lib/python2.7/dist-packages/alembic-0.8.8-py2.7.egg/alembic/ddl/impl.py", line 194, in create_table
    self._exec(schema.CreateTable(table))
  File "/usr/local/lib/python2.7/dist-packages/alembic-0.8.8-py2.7.egg/alembic/ddl/impl.py", line 118, in _exec
    return conn.execute(construct, *multiparams, **params)
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 914, in execute
  File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 968, in _execute_ddl
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1146, in _execute_context
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
  File "build/bdist.linux-x86_64/egg/sqlalchemy/util/compat.py", line 199, in raise_from_cause
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1139, in _execute_context
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/default.py", line 450, in do_execute
  File "/usr/local/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 205, in execute
    self.errorhandler(self, exc, value)
  File "/usr/local/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
sqlalchemy.exc.OperationalError: (_mysql_exceptions.OperationalError) (1050, "Table 'submit' already exists") [SQL: u'\nCREATE TABLE submit (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\ttmp_path TEXT, \n\tadded DATETIME NOT NULL, \n\tsubmit_type VARCHAR(16), \n\tdata TEXT, \n\tPRIMARY KEY (id)\n)\n\n']
>>> Error migrating your database..

[jbremer]

@ewalkup Hi, thanks for the feedback. Did you run cuckoo -d before doing the migrations? If so, that's probably how you ended up with the submit table. As mentioned above I suppose I should take a look at dropping the empty submit table in the cuckoo migrate command.

[ewalkup]

@jbremer Yes, I did, so you're probably correct in that. There's no obvious reason not to run that first when setting stuff up, fortunately if the table is always empty it's an easy fix.