Description:
I have this issue that mysql will automatically set my NOT NULL TIMESTAMP column to "DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP".
We had this storage issue yetserday and I had to restart mysql version 5.1.49.
Suddenly the developer told me everytime he set an update to this table, the create date would automatically be updated to cirrent date.
I checked the table, and it was changed!
Then I tried to fix it changing it to NOT NULL, but mysql sets it to update it automatically again.
Then I changed the column to NULL, it worked.But when I change it to NOT NULL it sets the update again.
There's no way out. We need this column to be not null, but we don't want mysql to set the date, and we can't let it update the date automatically, it's the register date...
What is happening, please?
Thanks in advance. Below is how to reproduce everything as has been told.
Thanks in advance,
Clara
How to repeat:
-- Create the table
CREATE TABLE General_log (
genelog_cod INT NOT NULL AUTO_INCREMENT ,
genelog_registerdate TIMESTAMP NOT NULL ,
genelog_catcod INT(11) NOT NULL ,
genelog_changetype CHAR(1) NOT NULL ,
genelog_tableidtvalue INT(11) NOT NULL ,
genelog_login VARCHAR(100) NOT NULL ,
genelog_status char(1) not null,
PRIMARY KEY (genelog_cod) )
ENGINE = MyIsam;
-- When I checked it was like this:
CREATE TABLE `General_log` (
`genelog_cod` int(11) NOT NULL AUTO_INCREMENT,
`genelog_registerdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`genelog_catcod` int(11) NOT NULL,
`genelog_changetype` char(1) NOT NULL,
`genelog_tableidtvalue` int(11) NOT NULL,
`genelog_login` varchar(100) NOT NULL,
`genelog_status` char(1) NOT NULL,
PRIMARY KEY (`genelog_cod`),
KEY `genelog_idx01` (`genelog_tableidtvalue`,`genelog_changetype`)
) ENGINE=MyISAM AUTO_INCREMENT=41689 DEFAULT CHARSET=latin1
-- Change it to NULLABLE
ALTER TABLE General_log MODIFY genelog_registerdate TIMESTAMP NULL;
Query OK, 41688 rows affected (0.30 sec)
Records: 41688 Duplicates: 0 Warnings: 0
-- checking the results
show create table General_log;
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| General_log | CREATE TABLE `General_log` (
`genelog_cod` int(11) NOT NULL AUTO_INCREMENT,
`genelog_registerdate` timestamp NULL DEFAULT NULL,
`genelog_catcod` int(11) NOT NULL,
`genelog_changetype` char(1) NOT NULL,
`genelog_tableidtvalue` int(11) NOT NULL,
`genelog_login` varchar(100) NOT NULL,
`genelog_status` char(1) NOT NULL,
PRIMARY KEY (`genelog_cod`),
KEY `genelog_idx01` (`genelog_tableidtvalue`,`genelog_changetype`)
) ENGINE=MyISAM AUTO_INCREMENT=41689 DEFAULT CHARSET=latin1 |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
-- Change it to NOT NULL - no default value anyways
ALTER TABLE General_log MODIFY genelog_registerdate TIMESTAMP NOT NULL;
Query OK, 41688 rows affected (0.21 sec)
Records: 41688 Duplicates: 0 Warnings: 0
--checking the results
show create table General_log;
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| General_log | CREATE TABLE `General_log` (
`genelog_cod` int(11) NOT NULL AUTO_INCREMENT,
`genelog_registerdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`genelog_catcod` int(11) NOT NULL,
`genelog_changetype` char(1) NOT NULL,
`genelog_tableidtvalue` int(11) NOT NULL,
`genelog_login` varchar(100) NOT NULL,
`genelog_status` char(1) NOT NULL,
PRIMARY KEY (`genelog_cod`),
KEY `genelog_idx01` (`genelog_tableidtvalue`,`genelog_changetype`)
) ENGINE=MyISAM AUTO_INCREMENT=41689 DEFAULT CHARSET=latin1 |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
