0

I have a database (MySQL 8.0) with four timestamp columns, using the default timestamp settings, i.e. no fraction of seconds. I would like to start using fractions of a second, probably two decimals, so TIMESTAMP(2). The process that generates the data does not always provide a timestamp to column timestamp_column_3 (just renamed the columns as timestamp_column_1 to timestamp_column_4 here) and thus there are many '0000-00-00 00:00:00' in timestamp_column_3. When I tried converting the timestamp_column_1 by running the following query:

ALTER TABLE table_name MODIFY COLUMN timestamp_column TIMESTAMP(2);

I get the following response:

Error Code: 1292. Incorrect datetime value: '0000-00-00 00:00:00' for column 'timestamp_column_3' at row 74608.

So, two questions:

  1. Why does timestamp_column_3 interfere with my altering of column timestamp_column_1?
  2. How do I proceed to convert all four columns to datatype TIMESTAMP(2)?

I looked around and found this answer to a similar question. But I'd rather not modify the mode of the database as I'm not very inexperienced and this is a production database. Is there a way to adjust the timestamps in the column to the minimum allowed (I assume this is 1970-01-01 00:00:00). I don't really care about the value. Also, I don't understand why the insertion process is allowed to insert these incorrect values as the mode is the following:

STRICT_TRANS_TABLES,
NO_ZERO_IN_DATE,
NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO,
NO_ENGINE_SUBSTITUTION

The incorrect values doesn't really matter as we know they don't "exist" but of course it would be nice to have everything correct. What would then be the "correct" value instead of a ZERO_DATE?

-- EDIT -- Add some information

Version: 8.0.18-google

Table:

CREATE TABLE `event` (
  `c1` int(11) DEFAULT NULL,
  `c2` varchar(32) NOT NULL,
  `c3` varchar(32) NOT NULL,
  `c4` varchar(64) NOT NULL,
  `c5` varchar(64) NOT NULL,
  `c6` varchar(64) NOT NULL,
  `c7` varchar(64) NOT NULL,
  `c8` varchar(64) NOT NULL,
  `c9` varchar(128) DEFAULT NULL,
  `timestamp_column_1` timestamp NOT NULL,
  `timestamp_column_2` timestamp NOT NULL,
  `timestamp_column_3` timestamp NULL DEFAULT NULL,
  `timestamp_column_4` timestamp NOT NULL,
  PRIMARY KEY (`c4`),
  KEY `event-timestamp_column_1` (`timestamp_column_1`),
  KEY `event-timestamp_column_3` (`timestamp_column_3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

0, a, b, c, d, e, f, g, , 2021-02-19 07:45:30, 2021-02-19 07:45:29, 0000-00-00 00:00:00, 2021-06-03 20:11:45

The data is added through Google Storage CSV import function and timestamp_column_3 sometimes has no data in the CSV file, i.e. the column is just represented as ,, in the CSV.

Nelumbo
  • 893
  • 2
  • 9
  • 18
  • 1
    I suspect those zero dates were already in the table when the `NO_ZERO_DATE` mode was enabled. It's probably only checked when inserting/updating. Since `ALTGER TABLE` has to update every row, it detects the problem and raises the error. – Barmar Feb 10 '23 at 15:53
  • I think the only way around this is to temporarily disable that mode, do the `ALTER`, then re-enable it. This will prevent new zero dates from being added, but preserve the old ones. – Barmar Feb 10 '23 at 15:54
  • 1
    Another option is to make the column nullable, then replace all those zero dates with NULL. Or replace them with some other default date. – Barmar Feb 10 '23 at 15:54
  • 1
    Barmar is almost certainly right, but you would make it easier for people to duplicate this if you show (as text, not images) output of `show create table yourtablename;` and `select version();` and a select of one of the problematic rows – ysth Feb 10 '23 at 15:58
  • note that you can just change your session mode without affecting other users – ysth Feb 10 '23 at 16:03
  • Possible duplicate of https://stackoverflow.com/questions/35565128/mysql-incorrect-datetime-value-0000-00-00-000000 – Bill Karwin Feb 10 '23 at 16:16
  • @Barmar No change has been done to the mode as far as I know. I'm the only admin of the database in its history and this is the first time I've looked at the mode. It's a GCP hosted MySQL database, so unless they decided to update the mode automatically it ought to have been on all the time. – Nelumbo Feb 10 '23 at 19:50
  • Maybe they updated MySQL versions. The defaults depend on the version. – Barmar Feb 10 '23 at 19:52
  • @Barmar Looked it up just now, it's the default configuration for for the version (8.0, will edit post to include version). I've been using the insertion through csv files from GCP Cloud Storage, maybe that has some other mode. As I understand it mode can be session-based, also as ysth said above? – Nelumbo Feb 10 '23 at 20:05
  • @ysth I'm a bit new to databases. How would you do this in a manner that doesn't reveal any sensitive information? Just rename the columns manually in the output? What is best (or just common) practice? – Nelumbo Feb 10 '23 at 20:09
  • Yes they can be, but that's unusual, I wouldn't expect it to be done by default.' – Barmar Feb 10 '23 at 20:09
  • Column names in output can be anything you want, they're normally only seen by the application code, not users. – Barmar Feb 10 '23 at 20:10

0 Answers0