From f89c052cf8484d050ebc7339210dde4b08ded220 Mon Sep 17 00:00:00 2001
From: Diogo Cordeiro <diogo@fc.up.pt>
Date: Tue, 16 Apr 2019 00:43:17 +0100
Subject: [PATCH] Set default value of datetime columns to CURRENT_TIMESTAMP
 This resolves an issue with MySQL 5.7 where the default SQL_MODE is set to
 disallow zero dates (i.e. '0000-00-00 00:00:00') Fixed thanks to Francis and
 Normandy from postActiv.

---
 db/site.sql          |  4 ++--
 extlib/DB/mysqli.php |  4 ++++
 lib/mysqlschema.php  | 27 +++++++++++++++++++++++----
 3 files changed, 29 insertions(+), 6 deletions(-)

diff --git a/db/site.sql b/db/site.sql
index 5e9693f5f7..9e33fc007c 100644
--- a/db/site.sql
+++ b/db/site.sql
@@ -18,8 +18,8 @@ create table status_network (
     
     tags text comment 'site meta-info tags (pipe-separated)',
 
-    created datetime not null comment 'date this record was created',
-    modified timestamp comment 'date this record was modified'
+    created datetime not null comment 'date this record was created' default now(),
+    modified timestamp comment 'date this record was modified' default now()
 
 ) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
 
diff --git a/extlib/DB/mysqli.php b/extlib/DB/mysqli.php
index f9ab4c3d0a..42ba23e4f9 100644
--- a/extlib/DB/mysqli.php
+++ b/extlib/DB/mysqli.php
@@ -1097,6 +1097,10 @@ class DB_mysqli extends DB_common
         }
     }
 
+    public function getVersion() {
+        return mysqli_get_server_version($this->connection);
+    }
+
     // }}}
 }
 
diff --git a/lib/mysqlschema.php b/lib/mysqlschema.php
index 30c808ab3d..e284d1eb18 100644
--- a/lib/mysqlschema.php
+++ b/lib/mysqlschema.php
@@ -271,7 +271,7 @@ class MysqlSchema extends Schema
         $engine = $this->preferredEngine($def);
         return ") ENGINE=$engine CHARACTER SET utf8mb4 COLLATE utf8mb4_bin";
     }
-    
+
     function preferredEngine($def)
     {
         /* MyISAM is no longer required for fulltext indexes, fortunately
@@ -386,7 +386,7 @@ class MysqlSchema extends Schema
         $map = array('serial' => 'int',
                      'integer' => 'int',
                      'numeric' => 'decimal');
-        
+
         $type = $column['type'];
         if (isset($map[$type])) {
             $type = $map[$type];
@@ -436,13 +436,32 @@ class MysqlSchema extends Schema
      */
     function filterDef(array $tableDef)
     {
+        $version = $this->conn->getVersion();
         foreach ($tableDef['fields'] as $name => &$col) {
             if ($col['type'] == 'serial') {
                 $col['type'] = 'int';
                 $col['auto_increment'] = true;
             }
-            if ($col['type'] == 'datetime' && isset($col['default']) && $col['default'] == 'CURRENT_TIMESTAMP') {
-                $col['type'] = 'timestamp';
+
+            // Avoid invalid date errors in MySQL 5.7+
+            if ($col['type'] == 'timestamp' && !isset($col['default'])
+                && $version >= 50605) {
+                $col['default'] = 'CURRENT_TIMESTAMP';
+            }
+            if ($col['type'] == 'datetime') {
+                // Avoid invalid date errors in MySQL 5.7+
+                if (!isset($col['default']) && $version >= 50605) {
+                    $col['default'] = 'CURRENT_TIMESTAMP';
+                }
+
+                // If we are using MySQL 5.5, convert datetime to timestamp if
+                // default value is CURRENT_TIMESTAMP. Not needed for MySQL 5.6+
+                // and MariaDB 10.0+
+                if (isset($col['default'])
+                    && $col['default'] == 'CURRENT_TIMESTAMP'
+                    && $version < 50605) {
+                    $col['type'] = 'timestamp';
+                }
             }
             $col['type'] = $this->mapType($col);
             unset($col['size']);
-- 
2.39.5