root@22:17:26 [hmda]> ALTER TABLE txt_register add httpheaderinfo varchar (5000) DEFAULT ' NULL ';
ERROR 1067 (42000): Invalid default value for ' CurrentTime '
Look at the next currenttime is datetime
ROOT@22:17:33 [hmda]> Show create TABLE txt_register\g;
1. Row ***************************
Table:txt_register
Create table:create Table ' Txt_register ' (
' ID ' bigint not NULL auto_increment,
' appversion ' varchar DEFAULT NULL,
' clientip ' varchar DEFAULT NULL,
' CurrentTime ' datetime not NULL DEFAULT ' 0000-00-00 00:00:00 ',
' mac ' varchar () DEFAULT NULL,
' Mobile ' varchar DEFAULT NULL,
' Registerstatus ' varchar (5) DEFAULT NULL,
' Source ' varchar DEFAULT NULL,
' useragent ' varchar DEFAULT NULL,
' userName ' varchar DEFAULT NULL,
' customname ' varchar DEFAULT NULL,
' Sourcesys ' varchar DEFAULT NULL,
' Loadtime ' datetime DEFAULT NULL,
PRIMARY KEY (' id ', ' currenttime ')
) Engine=innodb auto_increment=57105 DEFAULT Charset=utf8
It is not possible to create the same table:
root@22:42:41 [hmda]> CREATE table XL like Txt_register;
ERROR 1067 (42000): Invalid default value for ' CurrentTime '
is not created, so guessing should be a setup problem for a parameter in the system. There seems to be nothing unusual about date and time related parameters.
The only sql_mode that can be associated with restricting SQL is limited. Sure enough: no_zero_in_date,no_zero_date These two parameter limit time cannot be 0.
Root@22:43:27 [hmda]> Show variables like ' Sql_mode ';
+---------------+---------------------------------------------------------------------------------------------- -------------------------------------------------------------------+
| variable_name | Value |
+---------------+---------------------------------------------------------------------------------------------- -------------------------------------------------------------------+
| Sql_mode | Only_full_group_by,no_auto_value_on_zero,strict_trans_tables,no_zero_in_date,no_zero_date,error_for_division_ by_zero,no_auto_create_user,no_engine_substitution |
+---------------+---------------------------------------------------------------------------------------------- -------------------------------------------------------------------+
1 row in Set (0.00 sec)
Root@22:44:42 [hmda]> Set session sql_mode= ' Only_full_group_by,no_auto_value_on_zero,strict_trans_tables,error_ For_division_by_zero,no_auto_create_user,no_engine_substitution ';
Query OK, 0 rows affected, 1 Warning (0.00 sec)
root@22:45:06 [hmda]> CREATE table XL like Txt_register;
Query OK, 0 rows affected (42.92 sec)
root@22:47:37 [hmda]> ALTER TABLE XL add httpheaderinfo varchar (5000) DEFAULT ' NULL ';
Query OK, 0 rows affected (59.00 sec)
records:0 duplicates:0 warnings:0
No_zero_date
In strict mode, do not make ' 0000-00-00 ' a legal date. You can still insert the 0 issue with the Ignore option. In a non-strict mode, the date can be accepted, but a warning is generated.
No_zero_in_date
In strict mode, do not accept the month or day part is 0 of the date. If you use the Ignore option, we insert ' 0000-00-00 ' for a similar date. In a non-strict mode, the date can be accepted, but a warning is generated.
Approach to treatment:
1, modify the Sql_mode, remove no_zero_in_date,no_zero_date These two parameters
2, modify the table default value, remove the default can also.
Alter Tabletxt_register modify currenttime datetime NOT NULL;
Alter Tabletxt_register add Httpheaderinfo varchar (10000) DEFAULT ' null ';
The reason for this should be to build the table before Sql_mode not these two values, the table after the modified Sql_mode.
