Search code examples
mysqllaravelpercona

percona timestamp default 0 not null

I have used Brew to install percona-server on my mac for local development

┌─(veilig@Jamies-MacBook-Air:s018)──────────────(~/api.foo.com)─┐
└─(13:25:%)── brew info percona-server                                                      ──(Wed,Sep03 - feature/users)─┘
percona-server: stable 5.6.19-67.0 (bottled)

I'm trying to run this query

create table `courses` (
   `id` varchar(36) not null,
   `title` varchar(255) not null,
   `created_at` timestamp default 0 not null,
   `updated_at` timestamp default 0 not null,
   `tokens` int not null default '0',
   `product_id` int null,
   `track_id` varchar(36) not null
) default character set utf8 collate utf8_unicode_ci

and its choking when it has to create the updated_at column because timestamp default 0. The sql stmt is being created from within my Laravel 4.2 PHP application.

Is there a way to relax these restrictions to make this work?

This is the exact error

veilig@localhost (api_foo_com) > create table `courses` (`id` varchar(36) not null, `title` varchar(255) not null, `created_at` timestamp default 0 not null, `updated_at` timestamp default 0 not null, `tokens` int not null default '0', `product_id` int null, `track_id` varchar(36) not null) default character set utf8 collate utf8_unicode_ci;
ERROR 1067 (42000): Invalid default value for 'created_at'

When we try this on my coworkers database, we don't encounter this issue. He's running:

mysql Ver 14.14 Distrib 5.5.38, for debian-linux-gnu (x86_64) using readline 6.2

Solution

  • Had to remove two sql-mode from my /etc/my.cnf (or wherever your MySQL config is located) to allow the laravel migrations to correctly work.

    NO_ZERO_DATE and NO_ZERO_IN_DATE