Ensure create_engine() retries the initial connection test

Before return create_engine() tests the connectivity to a DB. At this
moment a DB might be unavailable for some reason, in which case
create_engine() retries a few times. When used with MySQL, we didn't
actually retry, but instead failed on the first connectivity error
when trying to get the value of sql_mode session variable.

Closes-Bug: #1376211

Co-Authored-By: Mike Bayer <mike_mp@zzzcomputing.com>
Change-Id: I14e25cfe1ed51b0d51a94e491b7267f26e42d34e
(cherry picked from commit 01a54cc5cc43c8b497007dbb07c78dacd03c1ed2)

2 files changed, 47 insertions, 22 deletions

diff --git a/oslo/db/sqlalchemy/session.py b/oslo/db/sqlalchemy/session.py
index 882a9ea..57b5fb8 100644
--- a/oslo/db/sqlalchemy/session.py
+++ b/oslo/db/sqlalchemy/session.py
@@ -466,19 +466,27 @@ def _init_events(engine, mysql_sql_mode=None, **kw):
             cursor = dbapi_con.cursor()
             cursor.execute("SET SESSION sql_mode = %s", [mysql_sql_mode])
 
-    realmode = engine.execute("SHOW VARIABLES LIKE 'sql_mode'").fetchone()
-    if realmode is None:
-        LOG.warning(_LW('Unable to detect effective SQL mode'))
-    else:
-        realmode = realmode[1]
-        LOG.debug('MySQL server mode set to %s', realmode)
-        if 'TRADITIONAL' not in realmode.upper() and \
-            'STRICT_ALL_TABLES' not in realmode.upper():
-            LOG.warning(
-                _LW(
-                    "MySQL SQL mode is '%s', "
-                    "consider enabling TRADITIONAL or STRICT_ALL_TABLES"),
-                realmode)
+    @sqlalchemy.event.listens_for(engine, "first_connect")
+    def _check_effective_sql_mode(dbapi_con, connection_rec):
+        if mysql_sql_mode is not None:
+            _set_session_sql_mode(dbapi_con, connection_rec)
+
+        cursor = dbapi_con.cursor()
+        cursor.execute("SHOW VARIABLES LIKE 'sql_mode'")
+        realmode = cursor.fetchone()
+
+        if realmode is None:
+            LOG.warning(_LW('Unable to detect effective SQL mode'))
+        else:
+            realmode = realmode[1]
+            LOG.debug('MySQL server mode set to %s', realmode)
+            if 'TRADITIONAL' not in realmode.upper() and \
+                'STRICT_ALL_TABLES' not in realmode.upper():
+                LOG.warning(
+                    _LW(
+                        "MySQL SQL mode is '%s', "
+                        "consider enabling TRADITIONAL or STRICT_ALL_TABLES"),
+                    realmode)
 
 
 @_init_events.dispatch_for("sqlite")
diff --git a/tests/sqlalchemy/test_sqlalchemy.py b/tests/sqlalchemy/test_sqlalchemy.py
index 5ba3207..0aeeb05 100644
--- a/tests/sqlalchemy/test_sqlalchemy.py
+++ b/tests/sqlalchemy/test_sqlalchemy.py
@@ -453,19 +453,36 @@ class MysqlConnectTest(test_base.MySQLOpportunisticTestCase):
 
         log = self.useFixture(fixtures.FakeLogger(level=logging.WARN))
 
-        engine = self._fixture(sql_mode=None)
+        mysql_conn = self.engine.raw_connection()
+        self.addCleanup(mysql_conn.close)
+        mysql_conn.detach()
+        mysql_cursor = mysql_conn.cursor()
 
-        @sqlalchemy.event.listens_for(
-            engine, "before_cursor_execute", retval=True)
-        def replace_stmt(
-            conn, cursor, statement, parameters,
-            context, executemany):
+        def execute(statement, parameters=()):
             if "SHOW VARIABLES LIKE 'sql_mode'" in statement:
                 statement = "SHOW VARIABLES LIKE 'i_dont_exist'"
-            return statement, parameters
-
-        session._init_events.dispatch_on_drivername("mysql")(engine)
+            return mysql_cursor.execute(statement, parameters)
+
+        test_engine = sqlalchemy.create_engine(self.engine.url,
+                                               _initialize=False)
+
+        with mock.patch.object(
+            test_engine.pool, '_creator',
+            mock.Mock(
+                return_value=mock.Mock(
+                    cursor=mock.Mock(
+                        return_value=mock.Mock(
+                            execute=execute,
+                            fetchone=mysql_cursor.fetchone,
+                            fetchall=mysql_cursor.fetchall
+                        )
+                    )
+                )
+            )
+        ):
+            session._init_events.dispatch_on_drivername("mysql")(test_engine)
 
+            test_engine.raw_connection()
         self.assertIn('Unable to detect effective SQL mode',
                       log.output)