CODEWITHSUNDEEP
mysqlnode.js
Hans kristianto
Hans kristianto

Reputation: 31

Node.js with knex + Mysql migrate error while rename column cause default value

I am new in nodejs and my english is bad please help me, I am just trying to migrate my database in nodejs(express) using knex, and I'm using Mysql for database. I want to rename one field in table, and when I try to migrate my database, I got some problem that say error default value.

here is what I'm trying to do :

My migrate

exports.up = function(knex) {
    return knex.schema.table ('tbl_skills', function(table) {
        table.renameColumn('preminum_price', 'premium_price')
    })
};

Here my database structure

 Name           | Datatype | length | Default             |
    id             | INT      | 20     | No default          |
    preminum_price | DOUBLE   | 5,2    | No default          |
    insertdate     | TIMESTAMP|        | 0000-00-00 00:00:00 |
    updatedate     | TIMESTAMP|        | 0000-00-00 00:00:00 |

and this what I got when I'm trying knex migrate:latest

migration file "20191125105226_alter_tbl_skills.js" failed
migration failed with error: alter table `tbl_skills` change `preminum_price` `premium_price` double(5,2) NOT NULL - ER_INVALID_DEFAULT: Invalid default value for 'insertdate'
Error: ER_INVALID_DEFAULT: Invalid default value for 'insertdate'

I dont know to how set value for insertdate with default value. Please help

Upvotes: 3

Views: 3610

Answers (1)

tpopov
tpopov

Reputation: 201

That is most probably because of server SQL Mode - NO_ZERO_DATE.

In strict mode, don't allow '0000-00-00' as a valid date. You can still insert zero dates with the IGNORE option. When not in strict mode, the date is accepted but a warning is generated. If you have access to my.ini (mysql conf file) remove the NO_ZERO_DATA from sql-mode and restart the server.

You can check it with SHOW VARIABLES LIKE 'sql_mode'

Upvotes: 2

Related Questions