yahoo-eng-team team mailing list archive

Thread
Date
[Bug 1749640] [NEW] db sync fails for mysql while adding triggers

To: yahoo-eng-team@xxxxxxxxxxxxxxxxxxx
From: Abhishek Kekane <akekane@xxxxxxxxxx>
Date: Thu, 15 Feb 2018 05:49:51 -0000
Reply-to: Bug 1749640 <1749640@xxxxxxxxxxxxxxxxxx>
Sender: bounces@xxxxxxxxxxxxx
Public bug reported:

glance-manage db sync fails while adding triggers to the database table
with error.

Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332
os-collect-config[2239]: "DBError: (pymysql.err.InternalError) (1419,
u'You do not have the SUPER privilege and binary logging is enabled (you
*might* want to use the less safe log_bin_trust_function_creators
variable)') [SQL: u\"\\nCREATE TRIGGER insert_visibility BEFORE INSERT
ON images\\nFOR EACH ROW\\nBEGIN\\n    -- NOTE(abashmak):\\n    -- The
following IF/ELSE block implements a priority decision tree.\\n    --
Strict order MUST be followed to correctly cover all the edge
cases.\\n\\n    -- Edge case: neither is_public nor visibility
specified\\n    --            (or both specified as NULL):\\n    IF
NEW.is_public <=> NULL AND NEW.visibility <=> NULL THEN\\n        SIGNAL
SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid visibility value';\\n    --
Edge case: both is_public and visibility specified:\\n    ELSEIF
NOT(NEW.is_public <=> NULL OR NEW.visibility <=> NULL) THEN\\n
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid visibility
value';\\n    -- Inserting with is_public, set visibility
accordingly:\\n    ELSEIF NOT NEW.is_public <=> NULL THEN\\n        IF
NEW.is_public = 1 THEN\\n            SET NEW.visibility = 'public';\\n
ELSE\\n            SET NEW.visibility = 'shared';\\n        END IF;\\n
-- Inserting with visibility, set is_public accordingly:\\n    ELSEIF
NOT NEW.visibility <=> NULL THEN\\n        IF NEW.visibility = 'public'
THEN\\n            SET NEW.is_public = 1;\\n        ELSE\\n
SET NEW.is_public = 0;\\n        END IF;\\n    -- Edge case: either one
of: is_public or visibility,\\n    --            is explicitly set to
NULL:\\n    ELSE\\n        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT =
'Invalid visibility value';\\n    END IF;\\nEND;\\n\"] (Background on
this error at: http://sqlalche.me/e/2j85)",


The reason is for MySQL, using the glance-manage db_sync or glance-manage expand command requires that you either grant your glance user SUPER privileges, or run set global log_bin_trust_function_creators=1; in mysql beforehand.


Actual logs:
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "+++ [[ -n 0 ]]",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "+++ glance-manage db_sync",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "/usr/lib/python2.7/site-packages/oslo_db/sqlalchemy/enginefacade.py:1334: OsloDBDeprecationWarning: EngineFacade is deprecated; please use oslo_db.sqlalchemy.enginefacade",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  expire_on_commit=expire_on_commit, _conf=conf)",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "INFO  [alembic.runtime.migration] Context impl MySQLImpl.",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "INFO  [alembic.runtime.migration] Will assume non-transactional DDL.",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "INFO  [alembic.runtime.migration] Running upgrade  -> liberty, liberty initial",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "INFO  [alembic.runtime.migration] Running upgrade liberty -> mitaka01, add index on created_at and updated_at columns of 'images' table",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "INFO  [alembic.runtime.migration] Running upgrade mitaka01 -> mitaka02, update metadef os_nova_server",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "INFO  [alembic.runtime.migration] Running upgrade mitaka02 -> ocata_expand01, add visibility to images",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "CRITI [glance] Unhandled error",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "Traceback (most recent call last):",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/bin/glance-manage\", line 10, in <module>",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    sys.exit(main())",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/glance/cmd/manage.py\", line 535, in main",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    return CONF.command.action_fn()",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/glance/cmd/manage.py\", line 367, in sync",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    self.command_object.sync(CONF.command.version)",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/glance/cmd/manage.py\", line 160, in sync",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    self.expand()",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/glance/cmd/manage.py\", line 215, in expand",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    self._sync(version=expand_head)",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/glance/cmd/manage.py\", line 175, in _sync",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    alembic_command.upgrade(a_config, version)",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/alembic/command.py\", line 254, in upgrade",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    script.run_env()",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/alembic/script/base.py\", line 425, in run_env",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    util.load_python_file(self.dir, 'env.py')",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/alembic/util/pyfiles.py\", line 81, in load_python_file",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    module = load_module_py(module_id, path)",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/alembic/util/compat.py\", line 141, in load_module_py",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    mod = imp.load_source(module_id, path, fp)",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/glance/db/sqlalchemy/alembic_migrations/env.py\", line 88, in <module>",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    run_migrations_online()",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/glance/db/sqlalchemy/alembic_migrations/env.py\", line 83, in run_migrations_online",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    context.run_migrations()",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"<string>\", line 8, in run_migrations",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/alembic/runtime/environment.py\", line 836, in run_migrations",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    self.get_context().run_migrations(**kw)",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/alembic/runtime/migration.py\", line 330, in run_migrations",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    step.migration_fn(**kw)",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/glance/db/sqlalchemy/alembic_migrations/versions/ocata_expand01_add_visibility.py\", line 151, in upgrade",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    _add_triggers(migrate_engine)",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/glance/db/sqlalchemy/alembic_migrations/versions/ocata_expand01_add_visibility.py\", line 133, in _add_triggers",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    ERROR_MESSAGE))",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"<string>\", line 8, in execute",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"<string>\", line 3, in execute",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/alembic/operations/ops.py\", line 1856, in execute",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    return operations.invoke(op)",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/alembic/operations/base.py\", line 319, in invoke",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    return fn(self, operation)",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/alembic/operations/toimpl.py\", line 161, in execute_sql",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    execution_options=operation.execution_options",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/alembic/ddl/impl.py\", line 121, in execute",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    self._exec(sql, execution_options)",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/alembic/ddl/impl.py\", line 118, in _exec",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    return conn.execute(construct, *multiparams, **params)",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py\", line 948, in execute",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    return meth(self, multiparams, params)",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib64/python2.7/site-packages/sqlalchemy/sql/elements.py\", line 269, in _execute_on_connection",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    return connection._execute_clauseelement(self, multiparams, params)",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py\", line 1060, in _execute_clauseelement",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    compiled_sql, distilled_params",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py\", line 1200, in _execute_context",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    context)",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py\", line 1409, in _handle_dbapi_exception",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    util.raise_from_cause(newraise, exc_info)",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib64/python2.7/site-packages/sqlalchemy/util/compat.py\", line 203, in raise_from_cause",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    reraise(type(exception), exception, tb=exc_tb, cause=cause)",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py\", line 1193, in _execute_context",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib64/python2.7/site-packages/sqlalchemy/engine/default.py\", line 507, in do_execute",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    cursor.execute(statement, parameters)",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/pymysql/cursors.py\", line 166, in execute",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    result = self._query(query)",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/pymysql/cursors.py\", line 322, in _query",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    conn.query(q)",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/pymysql/connections.py\", line 856, in query",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    self._affected_rows = self._read_query_result(unbuffered=unbuffered)",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/pymysql/connections.py\", line 1057, in _read_query_result",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    result.read()",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/pymysql/connections.py\", line 1340, in read",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    first_packet = self.connection._read_packet()",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/pymysql/connections.py\", line 1014, in _read_packet",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    packet.check_error()",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/pymysql/connections.py\", line 393, in check_error",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    err.raise_mysql_exception(self._data)",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/pymysql/err.py\", line 107, in raise_mysql_exception",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    raise errorclass(errno, errval)",
Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "DBError: (pymysql.err.InternalError) (1419, u'You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)') [SQL: u\"\\nCREATE TRIGGER insert_visibility BEFORE INSERT ON images\\nFOR EACH ROW\\nBEGIN\\n    -- NOTE(abashmak):\\n    -- The following IF/ELSE block implements a priority decision tree.\\n    -- Strict order MUST be followed to correctly cover all the edge cases.\\n\\n    -- Edge case: neither is_public nor visibility specified\\n    --            (or both specified as NULL):\\n    IF NEW.is_public <=> NULL AND NEW.visibility <=> NULL THEN\\n        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid visibility value';\\n    -- Edge case: both is_public and visibility specified:\\n    ELSEIF NOT(NEW.is_public <=> NULL OR NEW.visibility <=> NULL) THEN\\n        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid visibility value';\\n    -- Inserting with is_public, set visibility accordingly:\\n    ELSEIF NOT NEW.is_public <=> NULL THEN\\n        IF NEW.is_public = 1 THEN\\n            SET NEW.visibility = 'public';\\n        ELSE\\n            SET NEW.visibility = 'shared';\\n        END IF;\\n    -- Inserting with visibility, set is_public accordingly:\\n    ELSEIF NOT NEW.visibility <=> NULL THEN\\n        IF NEW.visibility = 'public' THEN\\n            SET NEW.is_public = 1;\\n        ELSE\\n            SET NEW.is_public = 0;\\n        END IF;\\n    -- Edge case: either one of: is_public or visibility,\\n    --            is explicitly set to NULL:\\n    ELSE\\n        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid visibility value';\\n    END IF;\\nEND;\\n\"] (Background on this error at: http://sqlalche.me/e/2j85)",

** Affects: glance
     Importance: High
     Assignee: Abhishek Kekane (abhishek-kekane)
         Status: New

** Changed in: glance
   Importance: Undecided => High

** Changed in: glance
     Assignee: (unassigned) => Abhishek Kekane (abhishek-kekane)

-- 
You received this bug notification because you are a member of Yahoo!
Engineering Team, which is subscribed to Glance.
https://bugs.launchpad.net/bugs/1749640

Title:
  db sync fails for mysql while adding triggers

Status in Glance:
  New

Bug description:
  glance-manage db sync fails while adding triggers to the database
  table with error.

  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332
  os-collect-config[2239]: "DBError: (pymysql.err.InternalError) (1419,
  u'You do not have the SUPER privilege and binary logging is enabled
  (you *might* want to use the less safe log_bin_trust_function_creators
  variable)') [SQL: u\"\\nCREATE TRIGGER insert_visibility BEFORE INSERT
  ON images\\nFOR EACH ROW\\nBEGIN\\n    -- NOTE(abashmak):\\n    -- The
  following IF/ELSE block implements a priority decision tree.\\n    --
  Strict order MUST be followed to correctly cover all the edge
  cases.\\n\\n    -- Edge case: neither is_public nor visibility
  specified\\n    --            (or both specified as NULL):\\n    IF
  NEW.is_public <=> NULL AND NEW.visibility <=> NULL THEN\\n
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid visibility
  value';\\n    -- Edge case: both is_public and visibility
  specified:\\n    ELSEIF NOT(NEW.is_public <=> NULL OR NEW.visibility
  <=> NULL) THEN\\n        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT =
  'Invalid visibility value';\\n    -- Inserting with is_public, set
  visibility accordingly:\\n    ELSEIF NOT NEW.is_public <=> NULL
  THEN\\n        IF NEW.is_public = 1 THEN\\n            SET
  NEW.visibility = 'public';\\n        ELSE\\n            SET
  NEW.visibility = 'shared';\\n        END IF;\\n    -- Inserting with
  visibility, set is_public accordingly:\\n    ELSEIF NOT NEW.visibility
  <=> NULL THEN\\n        IF NEW.visibility = 'public' THEN\\n
  SET NEW.is_public = 1;\\n        ELSE\\n            SET NEW.is_public
  = 0;\\n        END IF;\\n    -- Edge case: either one of: is_public or
  visibility,\\n    --            is explicitly set to NULL:\\n
  ELSE\\n        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid
  visibility value';\\n    END IF;\\nEND;\\n\"] (Background on this
  error at: http://sqlalche.me/e/2j85)",

  
  The reason is for MySQL, using the glance-manage db_sync or glance-manage expand command requires that you either grant your glance user SUPER privileges, or run set global log_bin_trust_function_creators=1; in mysql beforehand.

  
  Actual logs:
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "+++ [[ -n 0 ]]",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "+++ glance-manage db_sync",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "/usr/lib/python2.7/site-packages/oslo_db/sqlalchemy/enginefacade.py:1334: OsloDBDeprecationWarning: EngineFacade is deprecated; please use oslo_db.sqlalchemy.enginefacade",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  expire_on_commit=expire_on_commit, _conf=conf)",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "INFO  [alembic.runtime.migration] Context impl MySQLImpl.",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "INFO  [alembic.runtime.migration] Will assume non-transactional DDL.",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "INFO  [alembic.runtime.migration] Running upgrade  -> liberty, liberty initial",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "INFO  [alembic.runtime.migration] Running upgrade liberty -> mitaka01, add index on created_at and updated_at columns of 'images' table",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "INFO  [alembic.runtime.migration] Running upgrade mitaka01 -> mitaka02, update metadef os_nova_server",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "INFO  [alembic.runtime.migration] Running upgrade mitaka02 -> ocata_expand01, add visibility to images",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "CRITI [glance] Unhandled error",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "Traceback (most recent call last):",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/bin/glance-manage\", line 10, in <module>",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    sys.exit(main())",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/glance/cmd/manage.py\", line 535, in main",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    return CONF.command.action_fn()",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/glance/cmd/manage.py\", line 367, in sync",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    self.command_object.sync(CONF.command.version)",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/glance/cmd/manage.py\", line 160, in sync",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    self.expand()",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/glance/cmd/manage.py\", line 215, in expand",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    self._sync(version=expand_head)",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/glance/cmd/manage.py\", line 175, in _sync",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    alembic_command.upgrade(a_config, version)",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/alembic/command.py\", line 254, in upgrade",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    script.run_env()",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/alembic/script/base.py\", line 425, in run_env",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    util.load_python_file(self.dir, 'env.py')",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/alembic/util/pyfiles.py\", line 81, in load_python_file",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    module = load_module_py(module_id, path)",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/alembic/util/compat.py\", line 141, in load_module_py",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    mod = imp.load_source(module_id, path, fp)",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/glance/db/sqlalchemy/alembic_migrations/env.py\", line 88, in <module>",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    run_migrations_online()",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/glance/db/sqlalchemy/alembic_migrations/env.py\", line 83, in run_migrations_online",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    context.run_migrations()",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"<string>\", line 8, in run_migrations",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/alembic/runtime/environment.py\", line 836, in run_migrations",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    self.get_context().run_migrations(**kw)",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/alembic/runtime/migration.py\", line 330, in run_migrations",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    step.migration_fn(**kw)",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/glance/db/sqlalchemy/alembic_migrations/versions/ocata_expand01_add_visibility.py\", line 151, in upgrade",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    _add_triggers(migrate_engine)",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/glance/db/sqlalchemy/alembic_migrations/versions/ocata_expand01_add_visibility.py\", line 133, in _add_triggers",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    ERROR_MESSAGE))",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"<string>\", line 8, in execute",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"<string>\", line 3, in execute",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/alembic/operations/ops.py\", line 1856, in execute",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    return operations.invoke(op)",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/alembic/operations/base.py\", line 319, in invoke",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    return fn(self, operation)",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/alembic/operations/toimpl.py\", line 161, in execute_sql",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    execution_options=operation.execution_options",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/alembic/ddl/impl.py\", line 121, in execute",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    self._exec(sql, execution_options)",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/alembic/ddl/impl.py\", line 118, in _exec",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    return conn.execute(construct, *multiparams, **params)",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py\", line 948, in execute",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    return meth(self, multiparams, params)",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib64/python2.7/site-packages/sqlalchemy/sql/elements.py\", line 269, in _execute_on_connection",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    return connection._execute_clauseelement(self, multiparams, params)",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py\", line 1060, in _execute_clauseelement",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    compiled_sql, distilled_params",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py\", line 1200, in _execute_context",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    context)",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py\", line 1409, in _handle_dbapi_exception",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    util.raise_from_cause(newraise, exc_info)",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib64/python2.7/site-packages/sqlalchemy/util/compat.py\", line 203, in raise_from_cause",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    reraise(type(exception), exception, tb=exc_tb, cause=cause)",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py\", line 1193, in _execute_context",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib64/python2.7/site-packages/sqlalchemy/engine/default.py\", line 507, in do_execute",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    cursor.execute(statement, parameters)",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/pymysql/cursors.py\", line 166, in execute",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    result = self._query(query)",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/pymysql/cursors.py\", line 322, in _query",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    conn.query(q)",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/pymysql/connections.py\", line 856, in query",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    self._affected_rows = self._read_query_result(unbuffered=unbuffered)",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/pymysql/connections.py\", line 1057, in _read_query_result",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    result.read()",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/pymysql/connections.py\", line 1340, in read",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    first_packet = self.connection._read_packet()",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/pymysql/connections.py\", line 1014, in _read_packet",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    packet.check_error()",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/pymysql/connections.py\", line 393, in check_error",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    err.raise_mysql_exception(self._data)",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "  File \"/usr/lib/python2.7/site-packages/pymysql/err.py\", line 107, in raise_mysql_exception",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "    raise errorclass(errno, errval)",
  Feb 15 03:20:31 upstream-centos-7-2-node-rdo-cloud-tripleo-30309-6332 os-collect-config[2239]: "DBError: (pymysql.err.InternalError) (1419, u'You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)') [SQL: u\"\\nCREATE TRIGGER insert_visibility BEFORE INSERT ON images\\nFOR EACH ROW\\nBEGIN\\n    -- NOTE(abashmak):\\n    -- The following IF/ELSE block implements a priority decision tree.\\n    -- Strict order MUST be followed to correctly cover all the edge cases.\\n\\n    -- Edge case: neither is_public nor visibility specified\\n    --            (or both specified as NULL):\\n    IF NEW.is_public <=> NULL AND NEW.visibility <=> NULL THEN\\n        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid visibility value';\\n    -- Edge case: both is_public and visibility specified:\\n    ELSEIF NOT(NEW.is_public <=> NULL OR NEW.visibility <=> NULL) THEN\\n        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid visibility value';\\n    -- Inserting with is_public, set visibility accordingly:\\n    ELSEIF NOT NEW.is_public <=> NULL THEN\\n        IF NEW.is_public = 1 THEN\\n            SET NEW.visibility = 'public';\\n        ELSE\\n            SET NEW.visibility = 'shared';\\n        END IF;\\n    -- Inserting with visibility, set is_public accordingly:\\n    ELSEIF NOT NEW.visibility <=> NULL THEN\\n        IF NEW.visibility = 'public' THEN\\n            SET NEW.is_public = 1;\\n        ELSE\\n            SET NEW.is_public = 0;\\n        END IF;\\n    -- Edge case: either one of: is_public or visibility,\\n    --            is explicitly set to NULL:\\n    ELSE\\n        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid visibility value';\\n    END IF;\\nEND;\\n\"] (Background on this error at: http://sqlalche.me/e/2j85)",

To manage notifications about this bug go to:
https://bugs.launchpad.net/glance/+bug/1749640/+subscriptions
Follow ups

[Bug 1749640] Re: db sync fails for mysql while adding triggers
From: OpenStack Infra, 2018-02-21