Here is the
create table syntax from a legacy table:
CREATE TABLE `bais_logs` ( `lg_id` mediumint(7) unsigned NOT NULL AUTO_INCREMENT, `lg_machines_id` int(11) unsigned NOT NULL COMMENT 'unique machine id from which they are logged in', `lg_editdate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'this will not be changed with a ping; only a logout or other edit', `lg_acctname` char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'admin1' COMMENT 'as of 2012-04-01 this has not been used', `lg_stusername` char(64) CHARACTER SET latin1 NOT NULL DEFAULT '' COMMENT 'the logged in user; note st_ prefix is inaccurate as it may be a contact or other object', `lg_masterlogin` tinyint(1) unsigned NOT NULL DEFAULT '0', `lg_stemail` char(85) CHARACTER SET latin1 NOT NULL DEFAULT '' COMMENT 'email address at time of login', `lg_action` mediumint(5) NOT NULL DEFAULT '0', `lg_requesttype` mediumint(5) NOT NULL DEFAULT '0', `lg_sessionkey` char(32) CHARACTER SET latin1 NOT NULL DEFAULT '', `lg_ipaddress` char(16) CHARACTER SET latin1 NOT NULL DEFAULT '0.0.0.0', `lg_referrer` char(128) CHARACTER SET latin1 NOT NULL DEFAULT '', `lg_feed` char(64) CHARACTER SET latin1 NOT NULL DEFAULT '', `lg_lastping` datetime NOT NULL COMMENT 'last ajax ping to determine still logged in', `lg_entertime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `lg_exittime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `lg_logouttime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`lg_id`), KEY `lg_machines_id` (`lg_machines_id`), KEY `lg_acctname` (`lg_acctname`), KEY `lg_stusername` (`lg_stusername`), KEY `lg_masterlogin` (`lg_masterlogin`), KEY `lg_lastping` (`lg_lastping`) ) ENGINE=MyISAM AUTO_INCREMENT=3074 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
You will notice a whole lot of 0000-00-00 00:00:00 in there; that is no longer a valid format and entries into this table are failing. Even trying to change the field definitions is problematic with lots of "0" dates in there.
I am trying to update the dates to
1000-01-01 00:00:00 which is supposed to be valid, but I am getting the message:
update bais_logs set lg_editdate = IF(lg_editdate=0, '1000-01-01 00:00:00', lg_editdate), lg_lastping = IF(lg_lastping=0, '1000-01-01 00:00:00', lg_lastping), lg_entertime = IF(lg_entertime=0, '1000-01-01 00:00:00', lg_entertime), lg_exittime = IF(lg_exittime=0, '1000-01-01 00:00:00', lg_exittime), lg_logouttime = IF(lg_logouttime=0, '1000-01-01 00:00:00', lg_logouttime) ---------------- MySQL said: #1292 - Incorrect datetime value: '1000-01-01 00:00:00' for column 'lg_editdate' at row 373
What am I doing wrong on this? Also, how would I set
ALLOW_INVALID_DATES for now? Is this something that is done in the mysql.ini file?
NOTE: from the command line I am logging into mysql as root and entering:
SET GLOBAL sql_mode = 'traditional,allow_invalid_dates';
and it shows as set, but it doesn't make any difference; I still get the behavior above.