Repository: jupyterhub/jupyterhub
Pull request: Add db-upgrade test
This test is not run with pytest because it involves creating an additional virtualenv with older jupyterhub.

Fixes two migration bugs:

1. stamp the revision in `upgrade-db` to ensure that the right starting revision is used.
2. workaround issue in mysql, where an extra step is needed to allow dropping the `_server_id` column

closes #1448

Problem statement:
Error when running jupyterhub upgrade-db
**How to reproduce the issue**
Run `jupyterhub upgrade-db --db=mysql+pymysql://db_url_xxx`

**What you expected to happen**
DB is upgraded for 0.8

**What actually happens**
```
INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> 19c0846f6344, base revision for 0.5
INFO  [alembic.runtime.migration] Running upgrade 19c0846f6344 -> eeb276e51423, auth_state
Traceback (most recent call last):
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context
    context)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/default.py", line 470, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.5/dist-packages/pymysql/cursors.py", line 166, in execute
    result = self._query(query)
  File "/usr/local/lib/python3.5/dist-packages/pymysql/cursors.py", line 322, in _query
    conn.query(q)
  File "/usr/local/lib/python3.5/dist-packages/pymysql/connections.py", line 856, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/local/lib/python3.5/dist-packages/pymysql/connections.py", line 1057, in _read_query_result
    result.read()
  File "/usr/local/lib/python3.5/dist-packages/pymysql/connections.py", line 1340, in read
    first_packet = self.connection._read_packet()
  File "/usr/local/lib/python3.5/dist-packages/pymysql/connections.py", line 1014, in _read_packet
    packet.check_error()
  File "/usr/local/lib/python3.5/dist-packages/pymysql/connections.py", line 393, in check_error
    err.raise_mysql_exception(self._data)
  File "/usr/local/lib/python3.5/dist-packages/pymysql/err.py", line 107, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.InternalError: (1060, "Duplicate column name 'auth_state'")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/local/bin/alembic", line 11, in <module>
    load_entry_point('alembic==0.9.5', 'console_scripts', 'alembic')()
  File "/usr/local/lib/python3.5/dist-packages/alembic/config.py", line 479, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/usr/local/lib/python3.5/dist-packages/alembic/config.py", line 473, in main
    self.run_cmd(cfg, options)
  File "/usr/local/lib/python3.5/dist-packages/alembic/config.py", line 456, in run_cmd
    **dict((k, getattr(options, k, None)) for k in kwarg)
  File "/usr/local/lib/python3.5/dist-packages/alembic/command.py", line 254, in upgrade
    script.run_env()
  File "/usr/local/lib/python3.5/dist-packages/alembic/script/base.py", line 425, in run_env
    util.load_python_file(self.dir, 'env.py')
  File "/usr/local/lib/python3.5/dist-packages/alembic/util/pyfiles.py", line 93, in load_python_file
    module = load_module_py(module_id, path)
  File "/usr/local/lib/python3.5/dist-packages/alembic/util/compat.py", line 64, in load_module_py
    module_id, path).load_module(module_id)
  File "<frozen importlib._bootstrap_external>", line 396, in _check_name_wrapper
  File "<frozen importlib._bootstrap_external>", line 817, in load_module
  File "<frozen importlib._bootstrap_external>", line 676, in load_module
  File "<frozen importlib._bootstrap>", line 268, in _load_module_shim
  File "<frozen importlib._bootstrap>", line 693, in _load
  File "<frozen importlib._bootstrap>", line 673, in _load_unlocked
  File "<frozen importlib._bootstrap_external>", line 673, in exec_module
  File "<frozen importlib._bootstrap>", line 222, in _call_with_frames_removed
  File "/usr/local/lib/python3.5/dist-packages/jupyterhub/alembic/env.py", line 70, in <module>
    run_migrations_online()
  File "/usr/local/lib/python3.5/dist-packages/jupyterhub/alembic/env.py", line 65, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/usr/local/lib/python3.5/dist-packages/alembic/runtime/environment.py", line 836, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/usr/local/lib/python3.5/dist-packages/alembic/runtime/migration.py", line 330, in run_migrations
    step.migration_fn(**kw)
  File "/usr/local/lib/python3.5/dist-packages/jupyterhub/alembic/versions/eeb276e51423_auth_state.py", line 21, in upgrade
    op.add_column('users', sa.Column('auth_state', JSONDict))
  File "<string>", line 8, in add_column
  File "<string>", line 3, in add_column
  File "/usr/local/lib/python3.5/dist-packages/alembic/operations/ops.py", line 1565, in add_column
    return operations.invoke(op)
  File "/usr/local/lib/python3.5/dist-packages/alembic/operations/base.py", line 318, in invoke
    return fn(self, operation)
  File "/usr/local/lib/python3.5/dist-packages/alembic/operations/toimpl.py", line 123, in add_column
    schema=schema
  File "/usr/local/lib/python3.5/dist-packages/alembic/ddl/impl.py", line 172, in add_column
    self._exec(base.AddColumn(table_name, column, schema=schema))
  File "/usr/local/lib/python3.5/dist-packages/alembic/ddl/impl.py", line 118, in _exec
    return conn.execute(construct, *multiparams, **params)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/base.py", line 945, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/base.py", line 1002, in _execute_ddl
    compiled
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context
    context)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/base.py", line 1402, in _handle_dbapi_exception
    exc_info
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/util/compat.py", line 186, in reraise
    raise value.with_traceback(tb)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context
    context)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/default.py", line 470, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.5/dist-packages/pymysql/cursors.py", line 166, in execute
    result = self._query(query)
  File "/usr/local/lib/python3.5/dist-packages/pymysql/cursors.py", line 322, in _query
    conn.query(q)
  File "/usr/local/lib/python3.5/dist-packages/pymysql/connections.py", line 856, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/local/lib/python3.5/dist-packages/pymysql/connections.py", line 1057, in _read_query_result
    result.read()
  File "/usr/local/lib/python3.5/dist-packages/pymysql/connections.py", line 1340, in read
    first_packet = self.connection._read_packet()
  File "/usr/local/lib/python3.5/dist-packages/pymysql/connections.py", line 1014, in _read_packet
    packet.check_error()
  File "/usr/local/lib/python3.5/dist-packages/pymysql/connections.py", line 393, in check_error
    err.raise_mysql_exception(self._data)
  File "/usr/local/lib/python3.5/dist-packages/pymysql/err.py", line 107, in raise_mysql_exception
    raise errorclass(errno, errval)
sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1060, "Duplicate column name 'auth_state'") [SQL: 'ALTER TABLE users ADD COLUMN auth_state TEXT']
[E 2017-09-25 21:15:42.980 JupyterHub app:1527]
    Traceback (most recent call last):
      File "/usr/local/lib/python3.5/dist-packages/jupyterhub/app.py", line 1525, in launch_instance_async
        yield self.start()
      File "/usr/local/lib/python3.5/dist-packages/jupyterhub/app.py", line 1436, in start
        self.subapp.start()
      File "/usr/local/lib/python3.5/dist-packages/jupyterhub/app.py", line 185, in start
        dbutil.upgrade(hub.db_url)
      File "/usr/local/lib/python3.5/dist-packages/jupyterhub/dbutil.py", line 80, in upgrade
        ['alembic', '-c', alembic_ini, 'upgrade', revision]
      File "/usr/lib/python3.5/subprocess.py", line 271, in check_call
        raise CalledProcessError(retcode, cmd)
    subprocess.CalledProcessError: Command '['alembic', '-c', '/tmp/tmptsm58x_8/alembic.ini', 'upgrade', 'head']' returned non-zero exit status 1
```
**Share what version of JupyterHub you are using**
Jupyterhub 0.7.2 ->0.8.0rc2

Running `jupyter troubleshoot` from the command line, if possible, and posting
its output would also be helpful.

```
$PATH:
	/usr/local/bin
	/usr/local/sbin
	/usr/local/bin
	/usr/sbin
	/usr/bin
	/sbin
	/bin

sys.path:
	/usr/local/bin
	/srv/jupyterhub
	/usr/lib/python35.zip
	/usr/lib/python3.5
	/usr/lib/python3.5/plat-x86_64-linux-gnu
	/usr/lib/python3.5/lib-dynload
	/usr/local/lib/python3.5/dist-packages
	/usr/lib/python3/dist-packages

sys.executable:
	/usr/bin/python3

sys.version:
	3.5.3 (default, Jan 19 2017, 14:11:04)
	[GCC 6.3.0 20170118]

platform.platform():
	Linux-4.4.52+-x86_64-with-Ubuntu-17.04-zesty

which -a jupyter:
	/usr/local/bin/jupyter
	/usr/local/bin/jupyter
```

Hints from issue discussion:
same error as #1162 which is closed 
It probably can be solved by call check_db_revision before dbutil.upgrade(hub.db_url).
Got another error when running `jupyterhub upgrade-db --db=mysql+pymysql://db_url_xxx` after executed below sql to set alembic_version.
```
CREATE TABLE alembic_version (version_num VARCHAR(32) NOT NULL);
INSERT INTO alembic_version (version_num) VALUES ('af4cbdb2d13c');
```

```
[I 2017-09-26 10:29:41.865 alembic.runtime.migration migration:117] Context impl MySQLImpl.
[I 2017-09-26 10:29:41.866 alembic.runtime.migration migration:122] Will assume non-transactional DDL.
[I 2017-09-26 10:29:42.060 alembic.runtime.migration migration:327] Running upgrade af4cbdb2d13c -> 3ec6993fe20c, 0.8 changes
Traceback (most recent call last):
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context
    context)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 504, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.6/site-packages/pymysql/cursors.py", line 166, in execute
    result = self._query(query)
  File "/usr/local/lib/python3.6/site-packages/pymysql/cursors.py", line 322, in _query
    conn.query(q)
  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 856, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 1057, in _read_query_result
    result.read()
  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 1340, in read
    first_packet = self.connection._read_packet()
  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 1014, in _read_packet
    packet.check_error()
  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 393, in check_error
    err.raise_mysql_exception(self._data)
  File "/usr/local/lib/python3.6/site-packages/pymysql/err.py", line 107, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.InternalError: (1553, "Cannot drop index '_server_id': needed in a foreign key constraint")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/local/bin/alembic", line 11, in <module>
    sys.exit(main())
  File "/usr/local/lib/python3.6/site-packages/alembic/config.py", line 479, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/usr/local/lib/python3.6/site-packages/alembic/config.py", line 473, in main
    self.run_cmd(cfg, options)
  File "/usr/local/lib/python3.6/site-packages/alembic/config.py", line 456, in run_cmd
    **dict((k, getattr(options, k, None)) for k in kwarg)
  File "/usr/local/lib/python3.6/site-packages/alembic/command.py", line 254, in upgrade
    script.run_env()
  File "/usr/local/lib/python3.6/site-packages/alembic/script/base.py", line 425, in run_env
    util.load_python_file(self.dir, 'env.py')
  File "/usr/local/lib/python3.6/site-packages/alembic/util/pyfiles.py", line 93, in load_python_file
    module = load_module_py(module_id, path)
  File "/usr/local/lib/python3.6/site-packages/alembic/util/compat.py", line 64, in load_module_py
    module_id, path).load_module(module_id)
  File "<frozen importlib._bootstrap_external>", line 399, in _check_name_wrapper
  File "<frozen importlib._bootstrap_external>", line 823, in load_module
  File "<frozen importlib._bootstrap_external>", line 682, in load_module
  File "<frozen importlib._bootstrap>", line 251, in _load_module_shim
  File "<frozen importlib._bootstrap>", line 675, in _load
  File "<frozen importlib._bootstrap>", line 655, in _load_unlocked
  File "<frozen importlib._bootstrap_external>", line 678, in exec_module
  File "<frozen importlib._bootstrap>", line 205, in _call_with_frames_removed
  File "/usr/local/lib/python3.6/site-packages/jupyterhub/alembic/env.py", line 89, in <module>
    run_migrations_online()
  File "/usr/local/lib/python3.6/site-packages/jupyterhub/alembic/env.py", line 84, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/usr/local/lib/python3.6/site-packages/alembic/runtime/environment.py", line 836, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/usr/local/lib/python3.6/site-packages/alembic/runtime/migration.py", line 330, in run_migrations
    step.migration_fn(**kw)
  File "/usr/local/lib/python3.6/site-packages/jupyterhub/alembic/versions/3ec6993fe20c_encrypted_auth_state.py", line 39, in upgrade
    op.drop_column('users', '_server_id')
  File "<string>", line 8, in drop_column
  File "<string>", line 3, in drop_column
  File "/usr/local/lib/python3.6/site-packages/alembic/operations/ops.py", line 1667, in drop_column
    return operations.invoke(op)
  File "/usr/local/lib/python3.6/site-packages/alembic/operations/base.py", line 318, in invoke
    return fn(self, operation)
  File "/usr/local/lib/python3.6/site-packages/alembic/operations/toimpl.py", line 81, in drop_column
    **operation.kw
  File "/usr/local/lib/python3.6/site-packages/alembic/ddl/impl.py", line 175, in drop_column
    self._exec(base.DropColumn(table_name, column, schema=schema))
  File "/usr/local/lib/python3.6/site-packages/alembic/ddl/impl.py", line 118, in _exec
    return conn.execute(construct, *multiparams, **params)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 945, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1002, in _execute_ddl
    compiled
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context
    context)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1402, in _handle_dbapi_exception
    exc_info
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 186, in reraise
    raise value.with_traceback(tb)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context
    context)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 504, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.6/site-packages/pymysql/cursors.py", line 166, in execute
    result = self._query(query)
  File "/usr/local/lib/python3.6/site-packages/pymysql/cursors.py", line 322, in _query
    conn.query(q)
  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 856, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 1057, in _read_query_result
    result.read()
  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 1340, in read
    first_packet = self.connection._read_packet()
  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 1014, in _read_packet
    packet.check_error()
  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 393, in check_error
    err.raise_mysql_exception(self._data)
  File "/usr/local/lib/python3.6/site-packages/pymysql/err.py", line 107, in raise_mysql_exception
    raise errorclass(errno, errval)
sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1553, "Cannot drop index '_server_id': needed in a foreign key constraint") [SQL: 'ALTER TABLE users DROP COLUMN _server_id']
[E 2017-09-26 10:29:44.002 JupyterHub app:1626]
    Traceback (most recent call last):
      File "/usr/local/lib/python3.6/site-packages/jupyterhub/app.py", line 1624, in launch_instance_async
        yield self.start()
      File "/usr/local/lib/python3.6/site-packages/jupyterhub/app.py", line 1514, in start
        self.subapp.start()
      File "/usr/local/lib/python3.6/site-packages/jupyterhub/app.py", line 192, in start
        dbutil.upgrade(hub.db_url)
      File "/usr/local/lib/python3.6/site-packages/jupyterhub/dbutil.py", line 83, in upgrade
        ['alembic', '-c', alembic_ini, 'upgrade', revision]
      File "/usr/local/Cellar/python3/3.6.2/Frameworks/Python.framework/Versions/3.6/lib/python3.6/subprocess.py", line 291, in check_call
        raise CalledProcessError(retcode, cmd)
    subprocess.CalledProcessError: Command '['alembic', '-c', '/var/folders/vv/szpdwz315wd9z0h6gvw4cqcm0000gq/T/tmp5u3sy1q7/alembic.ini', 'upgrade', 'head']' returned non-zero exit status 1.
```
Here are SQL need to run before upgrading:
```
CREATE TABLE alembic_version (version_num VARCHAR(32) NOT NULL);
INSERT INTO alembic_version (version_num) VALUES ('af4cbdb2d13c');
ALTER TABLE users DROP FOREIGN KEY users_ibfk_1;
```

Commit message: test db-upgrade on travis

Patch to review:
diff --git a/.travis.yml b/.travis.yml
index ce484ce557..12370e4c3f 100644
--- a/.travis.yml
+++ b/.travis.yml
@@ -13,6 +13,7 @@ env:
 services:
   - mysql
   - postgresql
+  - docker
 
 # installing dependencies
 before_install:
@@ -29,12 +30,22 @@ before_install:
     fi
 install:
   - pip install -U pip
+  - |
+    if [[ "$TEST" == "db-upgrade" ]]; then
+      pip install pymysql psycopg2
+    fi
   - pip install --pre -r dev-requirements.txt .
   - pip freeze
 
 # running tests
 script:
-  - pytest -v --maxfail=2 --cov=jupyterhub jupyterhub/tests
+  - |
+    if [[ "$TEST" == "db-upgrade" ]]; then
+      cd test-db-upgrade
+      bash test.sh
+    else
+      pytest -v --maxfail=2 --cov=jupyterhub jupyterhub/tests
+    fi
 after_success:
   - codecov
 
@@ -47,5 +58,7 @@ matrix:
       env: JUPYTERHUB_TEST_DB_URL=mysql+mysqlconnector://root@127.0.0.1/jupyterhub
     - python: 3.6
       env: JUPYTERHUB_TEST_DB_URL=postgresql://postgres@127.0.0.1/jupyterhub
+    - python: 3.6
+      env: TEST=db-upgrade
   allow_failures:
     - python: nightly
diff --git a/MANIFEST.in b/MANIFEST.in
index 6e7d9ac0b7..82c847322a 100644
--- a/MANIFEST.in
+++ b/MANIFEST.in
@@ -11,6 +11,7 @@ graft jupyterhub
 graft scripts
 graft share
 graft singleuser
+graft test-db-upgrade
 
 # Documentation
 graft docs
diff --git a/jupyterhub/alembic/versions/3ec6993fe20c_encrypted_auth_state.py b/jupyterhub/alembic/versions/3ec6993fe20c_encrypted_auth_state.py
index 1ff4e64536..26c919bff1 100644
--- a/jupyterhub/alembic/versions/3ec6993fe20c_encrypted_auth_state.py
+++ b/jupyterhub/alembic/versions/3ec6993fe20c_encrypted_auth_state.py
@@ -36,6 +36,10 @@ def upgrade():
     # drop some columns no longer in use
     try:
         op.drop_column('users', 'auth_state')
+        # mysql cannot drop _server_id without also dropping
+        # implicitly created foreign key
+        if op.get_context().dialect.name == 'mysql':
+            op.drop_constraint('users_ibfk_1', 'users',  type_='foreignkey')
         op.drop_column('users', '_server_id')
     except sa.exc.OperationalError:
         # this won't be a problem moving forward, but downgrade will fail
diff --git a/jupyterhub/app.py b/jupyterhub/app.py
index 41d9c3c8e9..54a845aa64 100644
--- a/jupyterhub/app.py
+++ b/jupyterhub/app.py
@@ -23,6 +23,7 @@
 
 from jinja2 import Environment, FileSystemLoader
 
+from sqlalchemy import create_engine
 from sqlalchemy.exc import OperationalError
 
 from tornado.httpclient import AsyncHTTPClient
@@ -189,6 +190,13 @@ def start(self):
             db_file = hub.db_url.split(':///', 1)[1]
             self._backup_db_file(db_file)
         self.log.info("Upgrading %s", hub.db_url)
+        # run check-db-revision first
+        engine = create_engine(hub.db_url)
+        try:
+            orm.check_db_revision(engine)
+        except orm.DatabaseSchemaMismatch:
+            # ignore mismatch error because that's what we are here for!
+            pass
         dbutil.upgrade(hub.db_url)
 
 
diff --git a/test-db-upgrade/docker-db.sh b/test-db-upgrade/docker-db.sh
new file mode 100644
index 0000000000..46388bcf68
--- /dev/null
+++ b/test-db-upgrade/docker-db.sh
@@ -0,0 +1,44 @@
+#!/usr/bin/env bash
+# source this file to setup postgres and mysql
+# for local testing (as similar as possible to docker)
+
+DOCKER="docker run --rm -d --name"
+
+export MYSQL_HOST=127.0.0.1
+export PGHOST=127.0.0.1
+
+set -ex
+docker rm -f hub-test-mysql hub-test-postgres 2>/dev/null || true
+
+$DOCKER hub-test-mysql -e MYSQL_ALLOW_EMPTY_PASSWORD=1 -p 3306:3306 mysql:5.7
+$DOCKER hub-test-postgres -p 5432:5432 postgres:9.5
+set +x
+
+echo -n 'waiting for postgres'
+for i in {1..60}; do
+  if psql --user postgres -c '\q' 2>/dev/null; then
+    echo 'done'
+    break
+  else
+    echo -n '.'
+    sleep 1
+  fi
+done
+
+echo -n 'waiting for mysql'
+for i in {1..60}; do
+  if mysql --user root -e '\q' 2>/dev/null; then
+    echo 'done'
+    break
+  else
+    echo -n '.'
+    sleep 1
+  fi
+done
+
+echo -e "
+Set these environment variables:
+
+    export MYSQL_HOST=127.0.0.1
+    export PGHOST=127.0.0.1
+"
\ No newline at end of file
diff --git a/test-db-upgrade/init-db.sh b/test-db-upgrade/init-db.sh
new file mode 100644
index 0000000000..2fedf08208
--- /dev/null
+++ b/test-db-upgrade/init-db.sh
@@ -0,0 +1,14 @@
+set -e
+
+MYSQL="mysql --user root -e "
+PSQL="psql --user postgres -c "
+
+set -x
+
+# drop databases if they don't exist
+$MYSQL 'DROP DATABASE jupyterhub_upgrade' 2>/dev/null || true
+$PSQL 'DROP DATABASE jupyterhub_upgrade' 2>/dev/null || true
+
+# create the databases
+$MYSQL 'CREATE DATABASE jupyterhub_upgrade CHARACTER SET utf8 COLLATE utf8_general_ci;'
+$PSQL 'CREATE DATABASE jupyterhub_upgrade;'
diff --git a/test-db-upgrade/jupyterhub_config.py b/test-db-upgrade/jupyterhub_config.py
new file mode 100644
index 0000000000..ca1684e46c
--- /dev/null
+++ b/test-db-upgrade/jupyterhub_config.py
@@ -0,0 +1,11 @@
+import os
+c.JupyterHub.cookie_secret = os.urandom(32)
+
+DB = os.environ.get('DB')
+if DB == 'mysql':
+    c.JupyterHub.db_url = 'mysql+pymysql://root@127.0.0.1/jupyterhub_upgrade'
+elif DB == 'postgres':
+    c.JupyterHub.db_url = 'postgresql://postgres@127.0.0.1/jupyterhub_upgrade'
+
+c.JupyterHub.authenticator_class = 'jupyterhub.auth.Authenticator'
+c.Authenticator.whitelist = {'alpha', 'beta', 'gamma'}
diff --git a/test-db-upgrade/old-requirements.txt b/test-db-upgrade/old-requirements.txt
new file mode 100644
index 0000000000..f943f509c8
--- /dev/null
+++ b/test-db-upgrade/old-requirements.txt
@@ -0,0 +1,4 @@
+jupyterhub-dummyauthenticator
+jupyterhub==0.7.2
+psycopg2
+pymysql
\ No newline at end of file
diff --git a/test-db-upgrade/test.sh b/test-db-upgrade/test.sh
new file mode 100644
index 0000000000..6af45a2bff
--- /dev/null
+++ b/test-db-upgrade/test.sh
@@ -0,0 +1,33 @@
+#!/usr/bin/env bash
+set -e
+
+TMPDIR=$(mktemp -d)
+ENVDIR="$TMPDIR/env"
+
+# initialize the databases
+bash "$(dirname $0)"/init-db.sh
+
+# create virtualenv with jupyterhub 0.7
+echo "creating virutalenv in $ENVDIR"
+python -m virtualenv "$ENVDIR"
+$ENVDIR/bin/pip install -q -r old-requirements.txt
+
+echo "env-jupyterhub: $($ENVDIR/bin/jupyterhub --version)"
+echo "jupyterhub-$(jupyterhub --version)"
+
+set -x
+# launch jupyterhub-0.7 token entrypoint to ensure db is initialized
+for DB in sqlite postgres mysql; do
+  export DB
+  echo -e "\n\n\nupgrade-db test: $DB"
+  "$ENVDIR/bin/jupyterhub" token alpha
+
+  # run upgrade-db
+  jupyterhub upgrade-db
+  # generate a token, for basic exercise
+  jupyterhub token beta
+
+  echo -e "\n\n$DB OK"
+done
+
+rm -rf $ENVDIR


Code review comments:
diff_hunk: @@ -0,0 +1,33 @@
+#!/usr/bin/env bash
+set -e
+
+TMPDIR=$(mktemp -d)
+ENVDIR="$TMPDIR/env"
+
+# initialize the databases
+bash "$(dirname $0)"/init-db.sh
+
+# create virtualenv with jupyterhub 0.7
+echo "creating virutalenv in $ENVDIR"
+python -m virtualenv "$ENVDIR"
path: test-db-upgrade/test.sh
text: @user1:
Do you want `venv` instead?

@author:
Good call. I started with venv and it wasn't working for some reason, so I installed `virtualenv` while I was testing and it fixed it 🤷‍♂️ . I'll probably switch back to venv once everything else is working.

@author:
Turns out that I have to use virtualenv and not venv. The reason being that `python -m venv` doesn't work if run from an env created by `virtualenv`, which is the case on Travis (and happened to be the case on my local machine as well).