[DM-7050] LTD Keeper: Use Google Cloud Platform SQL - Jira

Details

Type: Story
Status: Done
Resolution: Done
Fix Version/s: None
Component/s: Stack Documentation and UX
Labels:
- lsst-the-docs
- ltd-keeper

Story Points:
5
Epic Link:
sqre-f16-ltd-prod-1
Team:
SQuaRE

Description

Currently LTD Keeper uses a sqlite DB. This ticket will migrate that DB to Google’s Cloud Platform’s managed SQL. This solution provides automatic backups, and provides flexibility to run multiple ltd-keeper pods. Google’s SQL makes sense since LTD Keeper is run on Google Cloud Platform.

Attachments

Issue Links

relates to

DM-11760 Update LTD Keeper's Cloud SQL Proxy to the latest (1.10) release

Done

DM-5892 Configure Backups for the LTD Keeper DB

Invalid

Activity

Ascending order - Click to sort in descending order

6 older comments

Hide

Permalink

Jonathan Sick added a comment - 29/Jul/16 7:50 PM - edited

Solution to above is to remember to put the the DB password in the DB URI.

Also, I’ve enabled the API for our google cloud project.

Show

Jonathan Sick added a comment - 29/Jul/16 7:50 PM - edited Solution to above is to remember to put the the DB password in the DB URI. Also, I’ve enabled the API for our google cloud project.

Hide

Permalink

Jonathan Sick added a comment - 30/Jul/16 9:33 AM

Error while running ./run.py db upgrade on cloud sql instance:

uwsgi@keeper-mgmt:/ltd-keeper$ ./run.py db upgrade

INFO  [alembic.runtime.migration] Context impl MySQLImpl.

INFO  [alembic.runtime.migration] Will assume non-transactional DDL.

INFO  [alembic.runtime.migration] Running upgrade  -> aa9e2d2736c8, initial

Traceback (most recent call last):

  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context

    context)

  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute

    cursor.execute(statement, parameters)

  File "/usr/local/lib/python3.5/site-packages/pymysql/cursors.py", line 167, in execute

    result = self._query(query)

  File "/usr/local/lib/python3.5/site-packages/pymysql/cursors.py", line 323, in _query

    conn.query(q)

  File "/usr/local/lib/python3.5/site-packages/pymysql/connections.py", line 836, in query

    self._affected_rows = self._read_query_result(unbuffered=unbuffered)

  File "/usr/local/lib/python3.5/site-packages/pymysql/connections.py", line 1020, in _read_query_result

    result.read()

  File "/usr/local/lib/python3.5/site-packages/pymysql/connections.py", line 1303, in read

    first_packet = self.connection._read_packet()

  File "/usr/local/lib/python3.5/site-packages/pymysql/connections.py", line 982, in _read_packet

    packet.check_error()

  File "/usr/local/lib/python3.5/site-packages/pymysql/connections.py", line 394, in check_error

    err.raise_mysql_exception(self._data)

  File "/usr/local/lib/python3.5/site-packages/pymysql/err.py", line 120, in raise_mysql_exception

    _check_mysql_exception(errinfo)

  File "/usr/local/lib/python3.5/site-packages/pymysql/err.py", line 115, in _check_mysql_exception

    raise InternalError(errno, errorvalue)

pymysql.err.InternalError: (1071, 'Specified key was too long; max key length is 767 bytes')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):

  File "./run.py", line 114, in <module>

    manager.run()

  File "/usr/local/lib/python3.5/site-packages/flask_script/__init__.py", line 412, in run

    result = self.handle(sys.argv[0], sys.argv[1:])

  File "/usr/local/lib/python3.5/site-packages/flask_script/__init__.py", line 383, in handle

    res = handle(*args, **config)

  File "/usr/local/lib/python3.5/site-packages/flask_script/commands.py", line 216, in __call__

    return self.run(*args, **kwargs)

  File "/usr/local/lib/python3.5/site-packages/flask_migrate/__init__.py", line 235, in upgrade

    command.upgrade(config, revision, sql=sql, tag=tag)

  File "/usr/local/lib/python3.5/site-packages/alembic/command.py", line 174, in upgrade

    script.run_env()

  File "/usr/local/lib/python3.5/site-packages/alembic/script/base.py", line 407, in run_env

    util.load_python_file(self.dir, 'env.py')

  File "/usr/local/lib/python3.5/site-packages/alembic/util/pyfiles.py", line 93, in load_python_file

    module = load_module_py(module_id, path)

  File "/usr/local/lib/python3.5/site-packages/alembic/util/compat.py", line 68, in load_module_py

    module_id, path).load_module(module_id)

  File "<frozen importlib._bootstrap_external>", line 385, in _check_name_wrapper

  File "<frozen importlib._bootstrap_external>", line 806, in load_module

  File "<frozen importlib._bootstrap_external>", line 665, 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 662, in exec_module

  File "<frozen importlib._bootstrap>", line 222, in _call_with_frames_removed

  File "migrations/env.py", line 87, in <module>

    run_migrations_online()

  File "migrations/env.py", line 80, in run_migrations_online

    context.run_migrations()

  File "<string>", line 8, in run_migrations

  File "/usr/local/lib/python3.5/site-packages/alembic/runtime/environment.py", line 797, in run_migrations

    self.get_context().run_migrations(**kw)

  File "/usr/local/lib/python3.5/site-packages/alembic/runtime/migration.py", line 312, in run_migrations

    step.migration_fn(**kw)

  File "/ltd-keeper/migrations/versions/aa9e2d2736c8_initial.py", line 30, in upgrade

    sa.UniqueConstraint('slug')

  File "<string>", line 8, in create_table

  File "<string>", line 3, in create_table

  File "/usr/local/lib/python3.5/site-packages/alembic/operations/ops.py", line 1098, in create_table

    return operations.invoke(op)

  File "/usr/local/lib/python3.5/site-packages/alembic/operations/base.py", line 318, in invoke

    return fn(self, operation)

  File "/usr/local/lib/python3.5/site-packages/alembic/operations/toimpl.py", line 101, in create_table

    operations.impl.create_table(table)

  File "/usr/local/lib/python3.5/site-packages/alembic/ddl/impl.py", line 194, in create_table

    self._exec(schema.CreateTable(table))

  File "/usr/local/lib/python3.5/site-packages/alembic/ddl/impl.py", line 118, in _exec

    return conn.execute(construct, *multiparams, **params)

  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 914, in execute

    return meth(self, multiparams, params)

  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection

    return connection._execute_ddl(self, multiparams, params)

  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 968, in _execute_ddl

    compiled

  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context

    context)

  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception

    exc_info

  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 202, in raise_from_cause

    reraise(type(exception), exception, tb=exc_tb, cause=cause)

  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 185, in reraise

    raise value.with_traceback(tb)

  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context

    context)

  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute

    cursor.execute(statement, parameters)

  File "/usr/local/lib/python3.5/site-packages/pymysql/cursors.py", line 167, in execute

    result = self._query(query)

  File "/usr/local/lib/python3.5/site-packages/pymysql/cursors.py", line 323, in _query

    conn.query(q)

  File "/usr/local/lib/python3.5/site-packages/pymysql/connections.py", line 836, in query

    self._affected_rows = self._read_query_result(unbuffered=unbuffered)

  File "/usr/local/lib/python3.5/site-packages/pymysql/connections.py", line 1020, in _read_query_result

    result.read()

  File "/usr/local/lib/python3.5/site-packages/pymysql/connections.py", line 1303, in read

    first_packet = self.connection._read_packet()

  File "/usr/local/lib/python3.5/site-packages/pymysql/connections.py", line 982, in _read_packet

    packet.check_error()

  File "/usr/local/lib/python3.5/site-packages/pymysql/connections.py", line 394, in check_error

    err.raise_mysql_exception(self._data)

  File "/usr/local/lib/python3.5/site-packages/pymysql/err.py", line 120, in raise_mysql_exception

    _check_mysql_exception(errinfo)

  File "/usr/local/lib/python3.5/site-packages/pymysql/err.py", line 115, in _check_mysql_exception

    raise InternalError(errno, errorvalue)

sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1071, 'Specified key was too long; max key length is 767 bytes') [SQL: '\nCREATE TABLE products (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tslug VARCHAR(256) NOT NULL, \n\tdoc_repo VARCHAR(256) NOT NULL, \n\ttitle VARCHAR(256) NOT NULL, \n\troot_domain VARCHAR(256) NOT NULL, \n\troot_fastly_domain VARCHAR(256) NOT NULL, \n\tbucket_name VARCHAR(256), \n\tPRIMARY KEY (id), \n\tUNIQUE (slug)\n)\n\n']

Show

Jonathan Sick added a comment - 30/Jul/16 9:33 AM Error while running ./run.py db upgrade on cloud sql instance: uwsgi@keeper-mgmt:/ltd-keeper$ ./run.py db upgrade INFO [alembic.runtime.migration] Context impl MySQLImpl. INFO [alembic.runtime.migration] Will assume non-transactional DDL. INFO [alembic.runtime.migration] Running upgrade -> aa9e2d2736c8, initial Traceback (most recent call last): File "/usr/local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context context) File "/usr/local/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute cursor.execute(statement, parameters) File "/usr/local/lib/python3.5/site-packages/pymysql/cursors.py", line 167, in execute result = self._query(query) File "/usr/local/lib/python3.5/site-packages/pymysql/cursors.py", line 323, in _query conn.query(q) File "/usr/local/lib/python3.5/site-packages/pymysql/connections.py", line 836, in query self._affected_rows = self._read_query_result(unbuffered=unbuffered) File "/usr/local/lib/python3.5/site-packages/pymysql/connections.py", line 1020, in _read_query_result result.read() File "/usr/local/lib/python3.5/site-packages/pymysql/connections.py", line 1303, in read first_packet = self.connection._read_packet() File "/usr/local/lib/python3.5/site-packages/pymysql/connections.py", line 982, in _read_packet packet.check_error() File "/usr/local/lib/python3.5/site-packages/pymysql/connections.py", line 394, in check_error err.raise_mysql_exception(self._data) File "/usr/local/lib/python3.5/site-packages/pymysql/err.py", line 120, in raise_mysql_exception _check_mysql_exception(errinfo) File "/usr/local/lib/python3.5/site-packages/pymysql/err.py", line 115, in _check_mysql_exception raise InternalError(errno, errorvalue) pymysql.err.InternalError: (1071, 'Specified key was too long; max key length is 767 bytes') The above exception was the direct cause of the following exception: Traceback (most recent call last): File "./run.py", line 114, in <module> manager.run() File "/usr/local/lib/python3.5/site-packages/flask_script/__init__.py", line 412, in run result = self.handle(sys.argv[0], sys.argv[1:]) File "/usr/local/lib/python3.5/site-packages/flask_script/__init__.py", line 383, in handle res = handle(*args, **config) File "/usr/local/lib/python3.5/site-packages/flask_script/commands.py", line 216, in __call__ return self.run(*args, **kwargs) File "/usr/local/lib/python3.5/site-packages/flask_migrate/__init__.py", line 235, in upgrade command.upgrade(config, revision, sql=sql, tag=tag) File "/usr/local/lib/python3.5/site-packages/alembic/command.py", line 174, in upgrade script.run_env() File "/usr/local/lib/python3.5/site-packages/alembic/script/base.py", line 407, in run_env util.load_python_file(self.dir, 'env.py') File "/usr/local/lib/python3.5/site-packages/alembic/util/pyfiles.py", line 93, in load_python_file module = load_module_py(module_id, path) File "/usr/local/lib/python3.5/site-packages/alembic/util/compat.py", line 68, in load_module_py module_id, path).load_module(module_id) File "<frozen importlib._bootstrap_external>", line 385, in _check_name_wrapper File "<frozen importlib._bootstrap_external>", line 806, in load_module File "<frozen importlib._bootstrap_external>", line 665, 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 662, in exec_module File "<frozen importlib._bootstrap>", line 222, in _call_with_frames_removed File "migrations/env.py", line 87, in <module> run_migrations_online() File "migrations/env.py", line 80, in run_migrations_online context.run_migrations() File "<string>", line 8, in run_migrations File "/usr/local/lib/python3.5/site-packages/alembic/runtime/environment.py", line 797, in run_migrations self.get_context().run_migrations(**kw) File "/usr/local/lib/python3.5/site-packages/alembic/runtime/migration.py", line 312, in run_migrations step.migration_fn(**kw) File "/ltd-keeper/migrations/versions/aa9e2d2736c8_initial.py", line 30, in upgrade sa.UniqueConstraint('slug') File "<string>", line 8, in create_table File "<string>", line 3, in create_table File "/usr/local/lib/python3.5/site-packages/alembic/operations/ops.py", line 1098, in create_table return operations.invoke(op) File "/usr/local/lib/python3.5/site-packages/alembic/operations/base.py", line 318, in invoke return fn(self, operation) File "/usr/local/lib/python3.5/site-packages/alembic/operations/toimpl.py", line 101, in create_table operations.impl.create_table(table) File "/usr/local/lib/python3.5/site-packages/alembic/ddl/impl.py", line 194, in create_table self._exec(schema.CreateTable(table)) File "/usr/local/lib/python3.5/site-packages/alembic/ddl/impl.py", line 118, in _exec return conn.execute(construct, *multiparams, **params) File "/usr/local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 914, in execute return meth(self, multiparams, params) File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection return connection._execute_ddl(self, multiparams, params) File "/usr/local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 968, in _execute_ddl compiled File "/usr/local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context context) File "/usr/local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception exc_info File "/usr/local/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 202, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=cause) File "/usr/local/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 185, in reraise raise value.with_traceback(tb) File "/usr/local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context context) File "/usr/local/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute cursor.execute(statement, parameters) File "/usr/local/lib/python3.5/site-packages/pymysql/cursors.py", line 167, in execute result = self._query(query) File "/usr/local/lib/python3.5/site-packages/pymysql/cursors.py", line 323, in _query conn.query(q) File "/usr/local/lib/python3.5/site-packages/pymysql/connections.py", line 836, in query self._affected_rows = self._read_query_result(unbuffered=unbuffered) File "/usr/local/lib/python3.5/site-packages/pymysql/connections.py", line 1020, in _read_query_result result.read() File "/usr/local/lib/python3.5/site-packages/pymysql/connections.py", line 1303, in read first_packet = self.connection._read_packet() File "/usr/local/lib/python3.5/site-packages/pymysql/connections.py", line 982, in _read_packet packet.check_error() File "/usr/local/lib/python3.5/site-packages/pymysql/connections.py", line 394, in check_error err.raise_mysql_exception(self._data) File "/usr/local/lib/python3.5/site-packages/pymysql/err.py", line 120, in raise_mysql_exception _check_mysql_exception(errinfo) File "/usr/local/lib/python3.5/site-packages/pymysql/err.py", line 115, in _check_mysql_exception raise InternalError(errno, errorvalue) sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1071, 'Specified key was too long; max key length is 767 bytes') [SQL: '\nCREATE TABLE products (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tslug VARCHAR(256) NOT NULL, \n\tdoc_repo VARCHAR(256) NOT NULL, \n\ttitle VARCHAR(256) NOT NULL, \n\troot_domain VARCHAR(256) NOT NULL, \n\troot_fastly_domain VARCHAR(256) NOT NULL, \n\tbucket_name VARCHAR(256), \n\tPRIMARY KEY (id), \n\tUNIQUE (slug)\n)\n\n']

Hide

Permalink

Jonathan Sick added a comment - 30/Jul/16 9:58 AM

Solution to this issue is to change length of Unicode columns to 255 characters, from 256.

At the same time, I might as well convert all String columns to Unicode columns.

Show

Jonathan Sick added a comment - 30/Jul/16 9:58 AM Solution to this issue is to change length of Unicode columns to 255 characters, from 256. At the same time, I might as well convert all String columns to Unicode columns.

Hide

Permalink

J Matt Peterson [X] (Inactive) added a comment - 04/Aug/16 4:27 PM

Looks good!

Show

J Matt Peterson [X] (Inactive) added a comment - 04/Aug/16 4:27 PM Looks good!

Hide

Permalink

Jonathan Sick added a comment - 04/Aug/16 4:33 PM

Thanks for checking J Matt Peterson [X]!

Show

Jonathan Sick added a comment - 04/Aug/16 4:33 PM Thanks for checking J Matt Peterson [X] !

Data Management

LTD Keeper: Use Google Cloud Platform SQL

Details

Description

Attachments

Issue Links

Activity

People

Dates

Jenkins