MYSQL ERROR

MYSQL ERROR

hubonehubone Posts: 13Questions: 4Answers: 0

When I imported the MYSQL database: https://editor.datatables.net/examples/sql/mysql.sql
I get this error

SQL query:

CREATE TABLE users(

id MEDIUMINT( 8 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
title VARCHAR( 255 ) DEFAULT NULL ,
first_name VARCHAR( 255 ) DEFAULT NULL ,
last_name VARCHAR( 255 ) DEFAULT NULL ,
phone VARCHAR( 100 ) DEFAULT NULL ,
city VARCHAR( 50 ) DEFAULT NULL ,
zip VARCHAR( 10 ) DEFAULT NULL ,
updated_date DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
registered_date DATETIME DEFAULT NULL ,
active BOOLEAN DEFAULT NULL ,
manager INT DEFAULT NULL ,
site INT DEFAULT NULL ,
image INT DEFAULT NULL ,
shift_start TIME DEFAULT NULL ,
shift_end TIME DEFAULT NULL ,
PRIMARY KEY ( id )
) AUTO_INCREMENT =1;

MySQL said: Documentation

1067 - Invalid default value for 'updated_date'

Answers

  • allanallan Posts: 63,498Questions: 1Answers: 10,471 Site admin

    Could you change the column type from datetime to be timestamp - that will allow the SQL to run on MySQL 5.5 (which is presumably what you are running if you are seeing this error). The next release of Editor will include demo SQL that is compatible with MySQL 5.5 by default.

    Allan

  • hubonehubone Posts: 13Questions: 4Answers: 0

    now I get this
    SQL query:

    CREATE TABLE users(

    id MEDIUMINT( 8 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
    title VARCHAR( 255 ) DEFAULT NULL ,
    first_name VARCHAR( 255 ) DEFAULT NULL ,
    last_name VARCHAR( 255 ) DEFAULT NULL ,
    phone VARCHAR( 100 ) DEFAULT NULL ,
    city VARCHAR( 50 ) DEFAULT NULL ,
    zip VARCHAR( 10 ) DEFAULT NULL ,
    updated_date TIMESTAMP DEFAULT NULL ,
    registered_date TIMESTAMP DEFAULT NULL ,
    active BOOLEAN DEFAULT NULL ,
    manager INT DEFAULT NULL ,
    site INT DEFAULT NULL ,
    image INT DEFAULT NULL ,
    shift_start TIME DEFAULT NULL ,
    shift_end TIME DEFAULT NULL ,
    PRIMARY KEY ( id )
    ) AUTO_INCREMENT =1;

    MySQL said: Documentation

    1067 - Invalid default value for 'updated_date'

  • allanallan Posts: 63,498Questions: 1Answers: 10,471 Site admin

    I released Editor 1.5.4 yesterday. Could you try downloading that version and try running the SQL that is included there please? That should work well with older versions of MySQL.

    Regards,
    Allan

  • hubonehubone Posts: 13Questions: 4Answers: 0

    It worked! Thanks

This discussion has been closed.