ERROR 1067 (42000): Invalid default value for 'uploadedDate'

Question

Hi everyone i am getting an error trying to run the below query ERROR 1067 (42000): Invalid default value for 'uploadedDate'

CREATE TABLE `filenew` (
  `id` int(11) NOT NULL AUTO_INCREMENT  /* MYSQL_TYPE_LONG */,
  `originalFilename` varchar(255) CHARACTER SET utf8 NOT NULL  /* MYSQL_TYPE_VARCHAR */,
  `shortUrl` varchar(255) CHARACTER SET utf8 DEFAULT NULL  /* MYSQL_TYPE_VARCHAR */,
  `fileType` varchar(150) CHARACTER SET utf8 DEFAULT NULL  /* MYSQL_TYPE_VARCHAR */,
  `extension` varchar(10) CHARACTER SET utf8 DEFAULT NULL  /* MYSQL_TYPE_VARCHAR */,
  `fileSize` bigint(15) DEFAULT NULL  /* MYSQL_TYPE_LONGLONG */,
  `localFilePath` varchar(255) CHARACTER SET utf8 DEFAULT NULL  /* MYSQL_TYPE_VARCHAR */,
  `userId` int(11) DEFAULT NULL  /* MYSQL_TYPE_LONG */,
  `uploadedUserId` int(11) DEFAULT NULL  /* MYSQL_TYPE_LONG */,
  `totalDownload` int(11) DEFAULT NULL  /* MYSQL_TYPE_LONG */,
  `uploadedIP` varchar(45) CHARACTER SET utf8 DEFAULT NULL  /* MYSQL_TYPE_VARCHAR */,
  `uploadedDate` timestamp DEFAULT NULL  /* MYSQL_TYPE_TIMESTAMP2 */,
  `statusId` int(2) DEFAULT NULL  /* MYSQL_TYPE_LONG */,
  `status` enum('active','trash','deleted') DEFAULT 'active'  /* MYSQL_TYPE_ENUM */,
  `visits` int(11) DEFAULT '0'  /* MYSQL_TYPE_LONG */,
  `lastAccessed` timestamp DEFAULT NULL  /* MYSQL_TYPE_TIMESTAMP2 */,
  `deleteHash` varchar(32) CHARACTER SET utf8 DEFAULT NULL  /* MYSQL_TYPE_VARCHAR */,
  `folderId` int(11) DEFAULT NULL  /* MYSQL_TYPE_LONG */,
  `serverId` int(11) DEFAULT '1'  /* MYSQL_TYPE_LONG */,
  `adminNotes` text CHARACTER SET utf8 DEFAULT NULL  /* MYSQL_TYPE_BLOB */,
  `accessPassword` varchar(32) CHARACTER SET utf8 DEFAULT NULL  /* MYSQL_TYPE_VARCHAR */,
  `fileHash` varchar(32) CHARACTER SET utf8 DEFAULT NULL  /* MYSQL_TYPE_VARCHAR */,
  `minUserLevel` int(3) DEFAULT NULL  /* MYSQL_TYPE_LONG */,
  `linkedFileId` int(11) DEFAULT NULL  /* MYSQL_TYPE_LONG */,
  `keywords` varchar(255) CHARACTER SET utf8 DEFAULT NULL  /* MYSQL_TYPE_VARCHAR */,
  `description` varchar(255) CHARACTER SET utf8 DEFAULT NULL  /* MYSQL_TYPE_VARCHAR */,
  `isPublic` int(1) NOT NULL DEFAULT '1'  /* MYSQL_TYPE_LONG */,
  `total_likes` int(11) NOT NULL DEFAULT '0'  /* MYSQL_TYPE_LONG */,
  `uploadSource` enum('direct','remote','ftp','torrent','leech','webdav','api','fileimport','other') CHARACTER SET utf8 NOT NULL DEFAULT 'direct'  /* MYSQL_TYPE_ENUM */,
  `unique_hash` varchar(64) CHARACTER SET utf8 DEFAULT NULL  /* MYSQL_TYPE_VARCHAR */,
  `date_updated` datetime DEFAULT NULL  /* MYSQL_TYPE_DATETIME2 */,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_hash` (`unique_hash`),
  KEY `shortUrl` (`shortUrl`),
  KEY `originalFilename` (`originalFilename`),
  KEY `fileSize` (`fileSize`),
  KEY `visits` (`visits`),
  KEY `lastAccessed` (`lastAccessed`),
  KEY `extension` (`extension`),
  KEY `userId` (`userId`),
  KEY `statusId` (`statusId`),
  KEY `userId_2` (`userId`),
  KEY `uploadedDate` (`uploadedDate`),
  KEY `folderId` (`folderId`),
  KEY `serverId` (`serverId`),
  KEY `fileHash` (`fileHash`),
  KEY `linkedFileId` (`linkedFileId`),
  KEY `statusId_2` (`statusId`),
  KEY `uploadedUserId` (`uploadedUserId`),
  KEY `keywords` (`keywords`),
  KEY `status` (`status`),
  KEY `uploadedIP` (`uploadedIP`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

I have tried many many fixes on Google and other forums but none of them worked, few things that i have tried are

Setting in my.cnf

sql_mode=NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Trying this

SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

...and many more. If someone could please help me out in this matter, because I am not experienced in databases, then I would really appreciate the help.

I am on centos 7 and MariaDB10.3.23-MariaD

Answer 1

MariaDB (unlike MySQL) has special behavior if NULL is assigned to column that uses the TIMESTAMP data type.

If the column is assigned the NULL value in an INSERT or UPDATE query (including implicit assigning via DEFAULT), then MariaDB will automatically initialize the column's value with the current date and time. So DEFAULT NULL for TIMESTAMP column is incorrect.

Each time when you try to assign NULL to TIMESTAMP column (explicitly or implicitly) it will be assigned to CURRENT_TIMESTAMP. Disabling NO_ZERO_DATE and NO_ZERO_IN_DATE has no effect in this case.

So specify columns as:

`uploadedDate` timestamp DEFAULT CURRENT_TIMESTAMP

and

`lastAccessed` DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP