All Products
Search

This Product

    ApsaraDB RDS:What do I do if ERROR 1067 (42000): Invalid default value for 'xxx' is reported when I use ApsaraDB RDS for MySQL?

    Document Center

    ApsaraDB RDS:What do I do if ERROR 1067 (42000): Invalid default value for 'xxx' is reported when I use ApsaraDB RDS for MySQL?

    Last Updated:Nov 14, 2024

    Problem description

    The following error is reported when the TIMESTAMP field of a table in an ApsaraDB RDS for MySQL database is modified, or this field is configured during the creation of a table in an ApsaraDB RDS for MySQL database. However, this field can be modified or configured for a table with the same schema in an on-premises self-managed MySQL database.

    ERROR 1067 (42000): Invalid default value for 'xxx'

    Cause

    The occurrence of this error depends on the value of the explicit_defaults_for_timestamp parameter that controls the default behavior of fields of the TIMESTAMP type in MySQL databases. The default value of the explicit_defaults_for_timestamp parameter is OFF in ApsaraDB RDS for MySQL databases and ON in community editions of MySQL databases. Therefore, the same value of a field of the TIMESTAMP type may be allowed or not allowed in different MySQL databases based on the following rules:

    • When the value of this parameter is OFF, fields of the TIMESTAMP type cannot be set to invalid values, such as NULL or "0000-00-00 00:00:00.000000".

    • When the value of this parameter is ON, fields of the TIMESTAMP type can be set to invalid values, such as NULL or "0000-00-00 00:00:00.000000".

    Note

    For more information about the explicit_defaults_for_timestamp parameter, see MySQL official documentation.

    Solutions

    • Solution 1: Configure the parameter globally for an ApsaraDB RDS for MySQL instance.

      You can go to the ApsaraDB RDS console to set the explicit_defaults_for_timestamp parameter to ON for an ApsaraDB RDS for MySQL instance. In this case, fields of the TIMESTAMP type can be set to invalid values in all sessions connected to this instance.

    • Solution 2: Configure the parameter temporarily for a session.

      You can set the explicit_defaults_for_timestamp parameter to ON for a session. In this case, fields of the TIMESTAMP type can be set to invalid values only in this session. This setting becomes invalid after the session is terminated or disconnected.

    Reference

    For more information about how to modify parameter settings through the ApsaraDB RDS console or API, see Modify instance parameters.