MySQL errors from demo data

jobloggs

https://editor.datatables.net/download/
Server version: 5.5.45-cll - MySQL Community Server (GPL)

Hi All, the following 3 tables are throwing errors when importing. Any help would be appreciated.

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;

1067 - Invalid default value for 'updated_date' ...

INSERT INTO `datatables_demo`
        ( id, first_name, last_name, age, position, salary, start_date, extn, email, office, seq ) 

Unknown column 'title' in 'field list'

CREATE TABLE `datatables_demo` (
    `id`         int(10) NOT NULL auto_increment,
    `first_name` varchar(250) NOT NULL default '',
    `last_name`  varchar(250) NOT NULL default '',
    `position`   varchar(250) NOT NULL default '',
    `email`      varchar(250) NOT NULL default '',
    `office`     varchar(250) NOT NULL default '',
    `start_date` datetime default CURRENT_TIMESTAMP,
    `age`        int(8),
    `salary`     int(8),
    `seq`        int(8),
    `extn`       varchar(8) NOT NULL default '',
    PRIMARY KEY  (`id`),
    INDEX (`seq`)
);

1067 - Invalid default value for 'start_date'

allan

Hi,

The SQL used in the example assumes MySQL v5.6 or newer. I'll add a catch into the code to note that.

To allow it to work with v5.5 simply change the datetime column types to timestamp.

Regards,
Allan

allan

Little update here - I've just changed the demo SQL for MySQL to use a trigger rather than CURRENT_TIMESTAMP. That restores compatibility with MySQL 5.6. This change will be included in Editor 1.5.4.

Allan

jobloggs

many thanks Allan, I did research a bit more and contacted my host. we won't see an update fore a couple of months.