MySQL about Value ' 0000-00-00 00:00:00 ' can not is represented as Java.sql.Timestam

The processing SQL query encountered a problem (the database is MySQL), there is a field definition type is datetime, and non-null no default value,

Fill the test data through the phpMyAdmin interface without regard to this field, see a small warning, insert success,

But there is an error when fetching the data:

Value ' 0000-00-00 00:00:00 ' can not is represented as Java.sql.Timestamp

JAVA connection to MySQL database, when the operation value of the timestamp type of 0 is not handled correctly, but by default throws an exception , is what you see:java.sql.SQLException:Cannot convert value ' 0000-00-00 00:00:00 ' from column 7 to TIMESTAMP. This issue is detailed in the official documentation, as described in the following links:

http://bugs.mysql.com/bug.php?id=19274

Http://dev.mysql.com/doc/refman/5.5/en/connector-j-installing-upgrading.html

There is an attribute in the JDBC connection string:zerodatetimebehavior, which can be used to configure the processing policy when this occurs with the following three property values:

L Exception: Default value, that is, throw SQL State [S1009]. Cannot convert value .... the exception;

L Converttonull: Converts the date to a NULL value;

L round: Replace with the nearest date namely 0001-01-01;

Therefore, for such exceptions, you might consider avoiding them by modifying the connection string and attaching the zerodatetimebehavior=converttonull attribute, for example:

Jdbc:mysql://localhost:3306/mydbname?zerodatetimebehavior=converttonull

From another level, this kind of abnormal triggering is also related to the operation of timestamp assignment, if you can make logical judgments in the design phase and record writing phase, avoid writing ' 0000-00-00 00:00:00 ' value, you can also avoid cannot convert The value ' 0000-00-00 00:00:00 ' from the column N to timestamp error.

MySql Host is blocked because of many connection errors; Unblock with ' mysqladmin flush-hosts ' workaround

Environment:linux,mysql5.5.21

Error: Host is blocked because of many connection errors; Unblock with ' mysqladmin flush-hosts '

Reason:

Congestion caused by the same IP in a short period of time resulting in too many (exceeding the maximum value of MySQL database max_connection_errors) interrupted database connections;

Workaround:

1, increase the allowable number of max_connection_errors (Temporary Cure):

① into MySQL database view max_connection_errors: show variables like '%max_connec_errors% ';

② Modify the number of Max_connection_errors: set global max_connect_errors = +;

③ See if the change was successful:show variables like '%max_connec_errors% ';

2, use mysqladmin flush-hosts command to clean up the Hosts file (do not know mysqladmin in which directory can use command lookup:whereis mysqladmin);

① in the found directory using the command to modify:/usr/bin/mysqladmin flush-hosts-h192.168.1.1 -p3308 -uroot-prootpwd;

Note:

Where the port number, user name, password can be added and modified as needed;

Configuration has master/slave master and slave database to the main library and from the library are modified again (I ate this loss obviously very easy several command results toss the most days);

The second step can also be done in the database, the command is as follows:flush hosts;

MySQL about Value ' 0000-00-00 00:00:00 ' can not is represented as Java.sql.Timestam