1

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

  • @Akina you should post your comment as an answer I think – mustaccio May 19 at 18:39
  • @akina, thank you very much buddy for replying. So i read the link you sent but unfortunately i don't understand how to make it work. If possible and if you have time, can you let me know what changes do i have to do to work that above query? – Jones smith May 19 at 18:44
  • And i have obtained this Mysql query from dbsake, i mistakenly deleted my ibdata1 file and now recovering tables from old ibd, So this is how the original table was created and hence dbsake giving me the query from .frm files. – Jones smith May 19 at 18:46
0

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
| improve this answer | |
  • Thank you very much Akina, i really appreciate the time that you gave to this. The uploadedDate issue is fixed but with lastAccessed i get this ``` ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP , deleteHash varc...' at line 17 ``` – Jones smith May 19 at 19:06
  • Here is how Phpmyadmin gives error about this prnt.sc/sjvw4h – Jones smith May 19 at 19:14
  • @Jonessmith MariaDB accepts proposed option: fiddle. Here is how Phpmyadmin gives error Remember - error message ALWAYS qoutes query text part starting from the char where problem is detected. So it is first letter in keyword DEFAULT or the gap between it and previous char. Look carefully. – Akina May 19 at 19:30
  • Thank you Akina for the quick fix, unfortunately when i ran the query it ran fine at first but suddenly lost connection to mysql, nowwhenever i run a simple query show tables; It loses connection to mysql, i will try to fix this problem and let you know. – Jones smith May 19 at 19:46
  • Every other Database is fine, i can list the tables and run every command but on this one whenever i run a command it gives me Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) ERROR: Can't connect to the server – Jones smith May 19 at 19:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.