Database Errors with New Setup

[jessferguson79]

Hello All,

 

Running PHP7.1 with MySQL 5.7.24. When I run the installer it fails creating several tables. 

I tried to recreate one manually but am getting the following error

 

mysql> CREATE TABLE `phpvms_acarsdata` ( `id` int(11) NOT NULL auto_increment, `pilotid` varchar(11) NOT NULL default '0', `flightnum` varchar(11) NOT NULL default '0', `pilotname` varchar(100) NOT NULL default '', `aircraft` varchar(12) NOT NULL default '', `lat` varchar(15) NOT NULL default '', `lng` varchar(15) NOT NULL default '', `heading` smallint(6) NOT NULL default '0', `alt` varchar(6) NOT NULL default '', `gs` int(11) NOT NULL default '0', `depicao` varchar(4) NOT NULL default '', `depapt` varchar(255) NOT NULL default '', `arricao` varchar(4) NOT NULL default '', `arrapt` text NOT NULL, `deptime` time NOT NULL default '00:00:00', `timeremaining` varchar(6) NOT NULL default '', `arrtime` time NOT NULL default '00:00:00', `route` TEXT NOT NULL, `route_details` TEXT NOT NULL, `distremain` varchar(6) NOT NULL default '', `phasedetail` varchar(255) NOT NULL default '', `online` varchar(10) NOT NULL default '', `messagelog` text NOT NULL, `lastupdate` datetime NOT NULL default '0000-00-00 00:00:00', `client` varchar(20) NOT NULL default '', PRIMARY KEY (`id`), KEY `pilotid` (`pilotid`) ) ENGINE=INNODB;
ERROR 1067 (42000): Invalid default value for 'lastupdate'

 

Any Thoughts?

 

--Jess

 

[jessferguson79]

Here is the complete installer output...

 

Installing the tables...

Writing "phpvms_adminlog" table... success

Writing "phpvms_awards" table... success

Writing "phpvms_awardsgranted" table... success

Writing "phpvms_fuelprices" table... success

Writing "phpvms_updates" table... success

Writing "phpvms_downloads" table... success

Writing "phpvms_expenses" table... success

Writing "phpvms_expenselog" table... success

Writing "phpvms_financedata" table... success

Writing "phpvms_acarsdata" table... failed - manually run this query: 

"CREATE TABLE `phpvms_acarsdata` ( `id` int(11) NOT NULL auto_increment, `pilotid` varchar(11) NOT NULL default '0', `flightnum` varchar(11) NOT NULL default '0', `pilotname` varchar(100) NOT NULL default '', `aircraft` varchar(12) NOT NULL default '', `lat` varchar(15) NOT NULL default '', `lng` varchar(15) NOT NULL default '', `heading` smallint(6) NOT NULL default '0', `alt` varchar(6) NOT NULL default '', `gs` int(11) NOT NULL default '0', `depicao` varchar(4) NOT NULL default '', `depapt` varchar(255) NOT NULL default '', `arricao` varchar(4) NOT NULL default '', `arrapt` text NOT NULL, `deptime` time NOT NULL default '00:00:00', `timeremaining` varchar(6) NOT NULL default '', `arrtime` time NOT NULL default '00:00:00', `route` TEXT NOT NULL, `route_details` TEXT NOT NULL, `distremain` varchar(6) NOT NULL default '', `phasedetail` varchar(255) NOT NULL default '', `online` varchar(10) NOT NULL default '', `messagelog` text NOT NULL, `lastupdate` datetime NOT NULL default '0000-00-00 00:00:00', `client` varchar(20) NOT NULL default '', PRIMARY KEY (`id`), KEY `pilotid` (`pilotid`) ) ENGINE=INNODB;"

Writing "phpvms_airlines" table... success

Writing "phpvms_aircraft" table... success

Writing "phpvms_navdata" table... success

Writing "phpvms_airports" table... success

Writing "phpvms_schedules" table... success

Writing "phpvms_news" table... failed - manually run this query: 

"CREATE TABLE `phpvms_news` ( `id` int(11) NOT NULL auto_increment, `subject` varchar(30) NOT NULL default '', `body` text NOT NULL, `postdate` datetime NOT NULL default '0000-00-00 00:00:00', `postedby` varchar(50) NOT NULL default '', PRIMARY KEY (`id`) ) ENGINE=InnoDB ;"

Writing "phpvms_news" table... failed - manually run this query: 

"INSERT INTO `phpvms_news` (`subject`,`body`,`postdate`,`postedby`) VALUES ('Welcome to phpVMS!', 'Thanks for installing and using phpVMS!
Check out the docs for help and information on getting started on your VA.

This is just a starter skin - customize yours completely. This is just a basic, barebones example of what a skin is and how it works. Check out the crystal folder in the lib/skins directory. Make your own copy and fiddle around. For help, check out the forum, and skinning docs. Also, be sure to check out the skinning tutorials for a quick primer. The forums are also filled with plenty of helpful people for any questions you may have.

Good luck!', NOW(), 'phpVMS Installer');"

Writing "phpvms_pages" table... failed - manually run this query: 

"CREATE TABLE `phpvms_pages` ( `pageid` int(11) NOT NULL auto_increment, `pagename` varchar(30) NOT NULL default '', `filename` varchar(30) NOT NULL default '', `order` smallint(6) NOT NULL default '0', `postedby` varchar(50) NOT NULL default '', `postdate` datetime NOT NULL default '0000-00-00 00:00:00', `public` smallint(6) NOT NULL default '0', `enabled` smallint(6) NOT NULL default '1', PRIMARY KEY (`pageid`), UNIQUE KEY `pagename` (`pagename`) ) ENGINE=InnoDB ;"

Writing "phpvms_ranks" table... success

Writing "phpvms_ranks" table... success

Writing "phpvms_sessions" table... success

Writing "phpvms_pilots" table... failed - manually run this query: 

"CREATE TABLE `phpvms_pilots` ( `pilotid` int(11) NOT NULL auto_increment, `firstname` varchar(25) NOT NULL default '', `lastname` varchar(25) NOT NULL default '', `email` varchar(100) NOT NULL default '', `code` char(3) NOT NULL default '', `location` varchar(32) NOT NULL default '', `hub` varchar(4) NOT NULL default '', `password` varchar(32) NOT NULL default '', `salt` varchar(32) NOT NULL default '', `bgimage` varchar(30) NOT NULL default '', `lastlogin` date NOT NULL default '0000-00-00', `totalflights` int(11) NOT NULL default '0', `totalhours` float NOT NULL default '0', `totalpay` float NOT NULL default '0', `transferhours` float NOT NULL default '0', `rankid` INT NOT NULL DEFAULT '0', `rank` varchar(32) NOT NULL default 'New Hire', `ranklevel` INT NOT NULL DEFAULT '0', `confirmed` smallint(5) unsigned NOT NULL default '0', `retired` smallint(6) NOT NULL default '0', `joindate` datetime NOT NULL default '0000-00-00 00:00:00', `lastpirep` datetime NOT NULL default '0000-00-00 00:00:00', `lastip` VARCHAR( 25 ) NULL DEFAULT '', PRIMARY KEY (`pilotid`), KEY `code` (`code`), KEY `rank` (`rank`) ) ENGINE=InnoDB;"

Writing "phpvms_pireps" table... failed - manually run this query: 

"CREATE TABLE `phpvms_pireps` ( `pirepid` int(11) NOT NULL auto_increment, `pilotid` int(11) NOT NULL default '0', `code` char(3) NOT NULL default '', `flightnum` varchar(10) NOT NULL default '0', `depicao` varchar(4) NOT NULL default '', `arricao` varchar(4) NOT NULL default '', `route` TEXT NOT NULL, `route_details` TEXT NOT NULL, `aircraft` varchar(12) NOT NULL default '', `flighttime` varchar(10) NOT NULL default '', `flighttime_stamp` TIME NOT NULL, `distance` smallint(6) NOT NULL default '0', `landingrate` FLOAT NOT NULL DEFAULT 0.0, `submitdate` datetime NOT NULL default '0000-00-00 00:00:00', `accepted` smallint(6) NOT NULL default '0', `log` text NOT NULL, `load` INT(11) NOT NULL, `fuelused` FLOAT NOT NULL DEFAULT 0.0, `fuelunitcost` FLOAT NOT NULL DEFAULT 0.0, `fuelprice` FLOAT NOT NULL DEFAULT 5.10, `price` FLOAT NOT NULL, `flighttype` VARCHAR( 1 ) NOT NULL DEFAULT 'P', `gross` FLOAT NOT NULL, `pilotpay` FLOAT NOT NULL, `expenses` FLOAT NOT NULL, `expenselist` BLOB NOT NULL, `revenue` FLOAT NOT NULL, `source` VARCHAR(25) NOT NULL, `exported` TINYINT NOT NULL, `rawdata` TEXT NOT NULL, PRIMARY KEY (`pirepid`) ) ENGINE=InnoDB;"

Writing "phpvms_pirepcomments" table... success

Writing "phpvms_customfields" table... success

Writing "phpvms_fieldvalues" table... success

Writing "phpvms_groups" table... success

Writing "phpvms_groups" table... success

Writing "phpvms_groups" table... success

Writing "phpvms_groupmembers" table... success

Writing "phpvms_pirepfields" table... success

Writing "phpvms_pirepvalues" table... success

Writing "phpvms_bids" table... success

Writing "phpvms_settings" table... success

Writing "phpvms_fieldvalues_ibfk_1` FOREIGN KEY (`fieldid`) REFERENCES `phpvms_customfields" table... failed - manually run this query: 

"ALTER TABLE `phpvms_fieldvalues` ADD CONSTRAINT `phpvms_fieldvalues_ibfk_1` FOREIGN KEY (`fieldid`) REFERENCES `phpvms_customfields` (`fieldid`) ON DELETE CASCADE, ADD CONSTRAINT `phpvms_fieldvalues_ibfk_2` FOREIGN KEY (`pilotid`) REFERENCES `phpvms_pilots` (`pilotid`) ON DELETE CASCADE;"

Writing "phpvms_groupmembers_ibfk_1` FOREIGN KEY (`groupid`) REFERENCES `phpvms_groups" table... failed - manually run this query: 

"ALTER TABLE `phpvms_groupmembers` ADD CONSTRAINT `phpvms_groupmembers_ibfk_1` FOREIGN KEY (`groupid`) REFERENCES `phpvms_groups` (`groupid`) ON DELETE CASCADE, ADD CONSTRAINT `phpvms_groupmembers_ibfk_2` FOREIGN KEY (`pilotid`) REFERENCES `phpvms_pilots` (`pilotid`) ON DELETE CASCADE;"

Writing "phpvms_pilots_ibfk_1` FOREIGN KEY (`code`) REFERENCES `phpvms_airlines" table... failed - manually run this query: 

"ALTER TABLE `phpvms_pilots` ADD CONSTRAINT `phpvms_pilots_ibfk_1` FOREIGN KEY (`code`) REFERENCES `phpvms_airlines` (`code`) ON UPDATE CASCADE;"

Writing "phpvms_pirepcomments_ibfk_1` FOREIGN KEY (`pirepid`) REFERENCES `phpvms_pireps" table... failed - manually run this query: 

"ALTER TABLE `phpvms_pirepcomments` ADD CONSTRAINT `phpvms_pirepcomments_ibfk_1` FOREIGN KEY (`pirepid`) REFERENCES `phpvms_pireps` (`pirepid`) ON DELETE CASCADE ON UPDATE CASCADE;"

Writing "phpvms_schedules_ibfk_1` FOREIGN KEY (`code`) REFERENCES `phpvms_airlines" table... success

Writing "phpvms_settings" table... success

Writing "phpvms_settings" table... success

Writing "phpvms_settings" table... success

Writing "phpvms_settings" table... success

Writing "phpvms_settings" table... success

Writing "phpvms_settings" table... success

[jessferguson79]

OK, I resolved this. The issue is related to strict mode enforcement, which is new in MySQL 5.7

https://tecadmin.net/disable-strict-sql-mode-mysql/

^^^ Is a tutorial to resolve.