0

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.

2

Quick Solution as this is a very small Table(3078). I suggest to create a table with the correct default values and copy the records from the existing table and then rename the table. Hope this helps.

  • Yes, this is ultimately what I did, thanks – Oliver Williams Dec 14 '16 at 20:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.