For some reason, I can't even select invalid dates in my table. How do I force select? I just receive:
select * from table
>> Mysql2::Error: Invalid date: 1900-00-00
I'm not trying to insert, just select. Can I set allow invalid dates in select query?
mysql --version
mysql Ver 14.14 Distrib 5.1.61, for debian-linux-gnu (i686) using readline 6.1
mysql> select @@global.sql_mode;
+-------------------+
| @@global.sql_mode |
+-------------------+
| |
Strict SQL Mode. Strict mode controls how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE . A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range.
SET SQL_MODE=”NO_AUTO_VALUE_ON_ZERO”; happens when different version of mysql is being used. When you are transfering from one server to another you should keep in mind the versions of database use in a new environment especially the mysql.
This is what I do to ignore invalid dates:
SET SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
mysql -u root -p
sql-modes
using SELECT @@GLOBAL.sql_mode;
sql-modes
using SET GLOBAL sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ALLOW_INVALID_DATES';
ALLOW_INVALID_DATES
and removes both NO_ZERO_DATE, NO_ZERO_IN_DATE
/etc/init.d/mysql start
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With