- The MySQL dialect now renders TIMESTAMP with NULL / NOT NULL in

all cases, so that MySQL 5.6.6 with the
``explicit_defaults_for_timestamp`` flag enabled will
will allow TIMESTAMP to continue to work as expected when
``nullable=False``.  Existing applications are unaffected as
SQLAlchemy has always emitted NULL for a TIMESTAMP column that
is ``nullable=True``.  fixes #3155

2 files changed, 133 insertions, 29 deletions

diff --git a/test/dialect/mysql/test_reflection.py b/test/dialect/mysql/test_reflection.py
index 99733e397..957a7eb21 100644
--- a/test/dialect/mysql/test_reflection.py
+++ b/test/dialect/mysql/test_reflection.py
@@ -7,6 +7,7 @@ from sqlalchemy.dialects.mysql import base as mysql
 from sqlalchemy.testing import fixtures, AssertsExecutionResults
 from sqlalchemy import testing
 
+
 class ReflectionTest(fixtures.TestBase, AssertsExecutionResults):
 
     __only_on__ = 'mysql'
@@ -23,13 +24,12 @@ class ReflectionTest(fixtures.TestBase, AssertsExecutionResults):
                    DefaultClause(''), nullable=False),
             Column('c2', String(10), DefaultClause('0')),
             Column('c3', String(10), DefaultClause('abc')),
-            Column('c4', TIMESTAMP, DefaultClause('2009-04-05 12:00:00'
-                   )),
+            Column('c4', TIMESTAMP, DefaultClause('2009-04-05 12:00:00')),
             Column('c5', TIMESTAMP),
             Column('c6', TIMESTAMP,
                    DefaultClause(sql.text("CURRENT_TIMESTAMP "
                                           "ON UPDATE CURRENT_TIMESTAMP"))),
-            )
+        )
         def_table.create()
         try:
             reflected = Table('mysql_def', MetaData(testing.db),
@@ -284,6 +284,55 @@ class ReflectionTest(fixtures.TestBase, AssertsExecutionResults):
         self.assert_('TABLES' in view_names)
 
     @testing.provide_metadata
+    def test_nullable_reflection(self):
+        """test reflection of NULL/NOT NULL, in particular with TIMESTAMP
+        defaults where MySQL is inconsistent in how it reports CREATE TABLE.
+
+        """
+        meta = self.metadata
+        Table('nn_t', meta)
+        testing.db.execute("""
+            CREATE TABLE nn_t (
+                x INTEGER NULL,
+                y INTEGER NOT NULL,
+                z INTEGER,
+                q TIMESTAMP NULL,
+                p TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
+                r TIMESTAMP NOT NULL,
+                s TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+                t TIMESTAMP,
+                u TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+            )
+        """)
+        eq_(
+            [
+                {
+                    "name": d['name'],
+                    "nullable": d['nullable'],
+                    "default": d['default'],
+                }
+                for d in
+                inspect(testing.db).get_columns('nn_t')
+            ],
+            [
+                {'name': 'x', 'nullable': True, 'default': None},
+                {'name': 'y', 'nullable': False, 'default': None},
+                {'name': 'z', 'nullable': True, 'default': None},
+                {'name': 'q', 'nullable': True, 'default': None},
+                {'name': 'p', 'nullable': True,
+                 'default': 'CURRENT_TIMESTAMP'},
+                {'name': 'r', 'nullable': False,
+                 'default': "'0000-00-00 00:00:00'"},
+                {'name': 's', 'nullable': False,
+                 'default': 'CURRENT_TIMESTAMP'},
+                {'name': 't', 'nullable': False,
+                 'default': "'0000-00-00 00:00:00'"},
+                {'name': 'u', 'nullable': False,
+                 'default': 'CURRENT_TIMESTAMP'},
+            ]
+        )
+
+    @testing.provide_metadata
     def test_reflection_with_unique_constraint(self):
         insp = inspect(testing.db)
 
diff --git a/test/dialect/mysql/test_types.py b/test/dialect/mysql/test_types.py
index 13425dc10..7c279ffbf 100644
--- a/test/dialect/mysql/test_types.py
+++ b/test/dialect/mysql/test_types.py
@@ -11,6 +11,7 @@ from sqlalchemy import testing
 import datetime
 import decimal
 
+
 class TypesTest(fixtures.TestBase, AssertsExecutionResults, AssertsCompiledSQL):
     "Test MySQL column types"
 
@@ -416,29 +417,66 @@ class TypesTest(fixtures.TestBase, AssertsExecutionResults, AssertsCompiledSQL):
         """Exercise funky TIMESTAMP default syntax when used in columns."""
 
         columns = [
-            ([TIMESTAMP],
+            ([TIMESTAMP], {},
              'TIMESTAMP NULL'),
-            ([mysql.MSTimeStamp],
+
+            ([mysql.MSTimeStamp], {},
              'TIMESTAMP NULL'),
+
+            ([mysql.MSTimeStamp(),
+              DefaultClause(sql.text('CURRENT_TIMESTAMP'))],
+             {},
+             "TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP"),
+
             ([mysql.MSTimeStamp,
               DefaultClause(sql.text('CURRENT_TIMESTAMP'))],
-             "TIMESTAMP DEFAULT CURRENT_TIMESTAMP"),
+             {'nullable': False},
+             "TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP"),
+
             ([mysql.MSTimeStamp,
               DefaultClause(sql.text("'1999-09-09 09:09:09'"))],
-             "TIMESTAMP DEFAULT '1999-09-09 09:09:09'"),
+             {'nullable': False},
+             "TIMESTAMP NOT NULL DEFAULT '1999-09-09 09:09:09'"),
+
+            ([mysql.MSTimeStamp(),
+              DefaultClause(sql.text("'1999-09-09 09:09:09'"))],
+             {},
+             "TIMESTAMP NULL DEFAULT '1999-09-09 09:09:09'"),
+
+            ([mysql.MSTimeStamp(),
+              DefaultClause(sql.text(
+                  "'1999-09-09 09:09:09' "
+                  "ON UPDATE CURRENT_TIMESTAMP"))],
+             {},
+             "TIMESTAMP NULL DEFAULT '1999-09-09 09:09:09' "
+             "ON UPDATE CURRENT_TIMESTAMP"),
+
             ([mysql.MSTimeStamp,
-              DefaultClause(sql.text("'1999-09-09 09:09:09' "
-                                      "ON UPDATE CURRENT_TIMESTAMP"))],
-             "TIMESTAMP DEFAULT '1999-09-09 09:09:09' "
+              DefaultClause(sql.text(
+                  "'1999-09-09 09:09:09' "
+                  "ON UPDATE CURRENT_TIMESTAMP"))],
+             {'nullable': False},
+             "TIMESTAMP NOT NULL DEFAULT '1999-09-09 09:09:09' "
+             "ON UPDATE CURRENT_TIMESTAMP"),
+
+            ([mysql.MSTimeStamp(),
+              DefaultClause(sql.text(
+                  "CURRENT_TIMESTAMP "
+                  "ON UPDATE CURRENT_TIMESTAMP"))],
+             {},
+             "TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP "
              "ON UPDATE CURRENT_TIMESTAMP"),
+
             ([mysql.MSTimeStamp,
-              DefaultClause(sql.text("CURRENT_TIMESTAMP "
-                                      "ON UPDATE CURRENT_TIMESTAMP"))],
-             "TIMESTAMP DEFAULT CURRENT_TIMESTAMP "
+              DefaultClause(sql.text(
+                  "CURRENT_TIMESTAMP "
+                  "ON UPDATE CURRENT_TIMESTAMP"))],
+             {'nullable': False},
+             "TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP "
              "ON UPDATE CURRENT_TIMESTAMP"),
-            ]
-        for spec, expected in columns:
-            c = Column('t', *spec)
+        ]
+        for spec, kw, expected in columns:
+            c = Column('t', *spec, **kw)
             Table('t', MetaData(), c)
             self.assert_compile(
                 schema.CreateColumn(c),
@@ -448,19 +486,20 @@ class TypesTest(fixtures.TestBase, AssertsExecutionResults, AssertsCompiledSQL):
 
     @testing.provide_metadata
     def test_timestamp_nullable(self):
-        ts_table = Table('mysql_timestamp', self.metadata,
-                            Column('t1', TIMESTAMP),
-                            Column('t2', TIMESTAMP, nullable=False),
-                    )
+        ts_table = Table(
+            'mysql_timestamp', self.metadata,
+            Column('t1', TIMESTAMP),
+            Column('t2', TIMESTAMP, nullable=False),
+            mysql_engine='InnoDB'
+        )
         self.metadata.create_all()
 
-        now = testing.db.execute("select now()").scalar()
-
         # TIMESTAMP without NULL inserts current time when passed
         # NULL.  when not passed, generates 0000-00-00 quite
         # annoyingly.
-        ts_table.insert().execute({'t1': now, 't2': None})
-        ts_table.insert().execute({'t1': None, 't2': None})
+        # the flag http://dev.mysql.com/doc/refman/5.6/en/\
+        # server-system-variables.html#sysvar_explicit_defaults_for_timestamp
+        # changes this for 5.6 if set.
 
         # normalize dates that are over the second boundary
         def normalize(dt):
@@ -470,11 +509,27 @@ class TypesTest(fixtures.TestBase, AssertsExecutionResults, AssertsCompiledSQL):
                 return now
             else:
                 return dt
-        eq_(
-            [tuple([normalize(dt) for dt in row])
-            for row in ts_table.select().execute()],
-            [(now, now), (None, now)]
-        )
+
+        with testing.db.begin() as conn:
+            now = conn.scalar("select now()")
+
+            conn.execute(
+                ts_table.insert(), {'t1': now, 't2': None})
+            conn.execute(
+                ts_table.insert(), {'t1': None, 't2': None})
+            conn.execute(
+                ts_table.insert(), {'t2': None})
+
+            eq_(
+                [tuple([normalize(dt) for dt in row])
+                 for row in conn.execute(ts_table.select())],
+                [
+                    (now, now),
+                    (None, now),
+                    (None, now)
+                ]
+            )
+
 
     def test_datetime_generic(self):
         self.assert_compile(