Uploaded image for project: 'Data Management'
  1. Data Management
  2. DM-7050

LTD Keeper: Use Google Cloud Platform SQL

    Details

      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

            Activity

            Hide
            jsick Jonathan Sick added a comment - - 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
            jsick Jonathan Sick added a comment - - 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
            jsick Jonathan Sick added a comment -

            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
            jsick Jonathan Sick added a comment - 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
            jsick Jonathan Sick added a comment -

            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
            jsick Jonathan Sick added a comment - 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
            jmatt J Matt Peterson [X] (Inactive) added a comment -

            Looks good!

            Show
            jmatt J Matt Peterson [X] (Inactive) added a comment - Looks good!
            Hide
            jsick Jonathan Sick added a comment -

            Thanks for checking J Matt Peterson [X]!

            Show
            jsick Jonathan Sick added a comment - Thanks for checking J Matt Peterson [X] !

              People

              • Assignee:
                jsick Jonathan Sick
                Reporter:
                jsick Jonathan Sick
                Reviewers:
                J Matt Peterson [X] (Inactive)
                Watchers:
                J Matt Peterson [X] (Inactive), Jonathan Sick
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Summary Panel