Search code examples
sequelpro

current_timestamp() vs CURRENT_TIMESTAMP for default value on TIMESTAMP column (mariadb:latest/mysql, laravel, sequel pro)

What is the difference between CURRENT_TIMESTAMP and current_timestamp() ?

I'm using laravel, and in the laravel migration file for my Tasks table I have this:

$table->timestamp('created_at')->useCurrent();
$table->timestamp('updated_at')->useCurrent()->useCurrentOnUpdate();

For my database container, I am using a local docker setup with: mariadb:latest (seems to be bringing up version 10.8.3-MariaDB-1:10.8.3+maria~jammy (mariadb.org binary distribution) )

and the weird thing is..

I have Sequel Pro open, and when trying to manually insert a record ( for testing purposes ) through the sequel pro interface and it is failing with the following error:

Incorrect datetime value: 'current_timestamp()' for column .. created_at..

Incorrect datetime value: 'current_timestamp()' for column..

Notice when I click to add a new row, the defaults are 'current_timestamp()' current_timestamp() default

If I manually change these defaults to 'CURRENT_TIMESTAMP' instead of 'current_timestamp()' it seems to work:

enter image description here

The function/call or lower case version of CURRENT_TIMESTAMP does not work...

If I add a new row programatically / with laravel:

$newTask = new Task();
$newTask->title = 'testing';
$newTask->save();

the row is inserted properly ( with the current timestamp values.. ):

Where is this problem at?

  • Laravel side/configuration?
  • Could it be the 'mariadb:latest' bringing up a bug?
  • Could it be a sequel pro bug ?

This is the create table definition btw:

CREATE TABLE `tasks` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Solution

  • While developing this question, I have decided to download MySql Workbench and try inserting the values there through the MySql Workbench interface,

    and it seems to work, and it seems that it is because MySql Workbench simply runs INSERT queries:

    INSERT INTO `tasks_pabloserver_db`.`tasks` (`title`) VALUES ('teeest');
INSERT INTO `tasks_pabloserver_db`.`tasks` (`title`) VALUES ('test444');

    which work and insert the proper default/timestamp values. I looked at the table structure in MySql Workbench and the default value is 'current_timestamp()' and not 'CURRENT_TIMESTAMP', and it still works so it cannot be the database version I guess.

    So then I tried to run these same INSERT statements in Sequel Pro and it also worked properly, so my conclusion is that Sequel Pro interface has a bug and that is to blame.