0

Deploying to Heroku and having trouble uploading an SQL export from phpMyAdmin

SQL Export Settings:

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

Create Table

CREATE TABLE `posts` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `body` text NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Error Message

ERROR 1067 (42000) at line 30: Invalid default value for 'created_at'

Using Step 11 from this tutorial on scotch.io

I feel like I am having issues with different versions of MySQL.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Nathan Harris
  • 1

2 Answers2

1

https://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html

created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP. <-- it is not a function so do not use ()

Ryan H
  • 400
  • 2
  • 9
  • The only other thing I see that is strange is that you are starting a transaction ... DDL in MySQL does not support transaction. also when I run ```CREATE TABLE `posts` ( `id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `title` varchar(255) NOT NULL, `body` text NOT NULL, `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;``` on a local mysql instance it creates succesfully – Ryan H Feb 05 '21 at 21:04
  • Though when I run the above inside a transaction my local mysql does not complain about it at all ... does line 30 of your script actually reference the CREATE block of code? – Ryan H Feb 05 '21 at 21:08
  • Hi Ryan, thanks for your efforts. I found out that I was stuck in an old version of MySQL because of the heroku addon I was using called CLEARDB. I switched to JAWSDB with SQL v5.7 and used MySQL workbench to import the sql file and everything worked fine. – Nathan Harris Feb 06 '21 at 01:46
0

It appears that my version of CLEARDB was 5.5 and I could not get a newer version under a free account. Switched to JAWSDB and I am using a free account(I think) with 5.7

How to update the version of the MySQL engine in ClearDB?

Nathan Harris
  • 1