MySQL Out of Range Solution for DATETIME field

Affected rows: 0
Warning: #1264 Out of range value adjusted for column 'deleteon' at row 1
SQL query:
UPDATE `custtable`.`custinfo` SET `deleteon` = '2011-02-29 00:00:00' WHERE `custinfo`.`custid` =105 LIMIT 1 ;

This happened after a migration to a new SQL database due to user error.  The old database server MySQL 3.23 or 4 allowed an impossible date to be entered by a user.  As we know February 29th DOES NOT exist but the database allowed the value.  MySQL 5.0 is smarter and rejected it and ended up setting a blank value for that field.  It was hard to troubleshoot at first until we actually looked at the date :)

So the solution is to use Feb-28th or March 1st since the 29th does not exist.


Tags:

mysql, datetime, fieldaffected, rows, adjusted, column, deleteon, sql, query, update, custtable, custinfo, custid, migration, database, user, server, february, rejected, troubleshoot, feb,

Latest Articles

  • How To Install OpenProject on Centos 7 Step-by-Step Guide
  • Ubuntu Debian Linux Cannot Install Wine Solution - wine1.6 : Depends: wine1.6-i386 (= 1:1.6.2-0ubuntu14.2) but it is not installable wine1.4 : Depends: wine1.6 but it is not going to be installed
  • How To Install python 3.4 3.5 and up on Linux with wine - Working Solution
  • using Xvfb on virtual remote ssh server to have X graphical programs work
  • ssh Received disconnect from port 22:2: Too many authentication failures
  • named bind errors - DNSKEY: unable to find a DNSKEY which verifies the DNSKEY RRset and also matches a trusted key for '.'
  • OpenVZ vs LXC DIR mode poor security in LXC
  • httpd: Syntax error on line 221 of /etc/httpd/conf/httpd.conf: Syntax error on line 6 of /etc/httpd/conf.d/php.conf: Cannot load modules/libphp5.so into server: /lib64/libresolv.so.2: symbol __h_errno, version GLIBC_PRIVATE not defined in file libc.s
  • Radeon R3 GPU on Debian Crashing
  • MySQL 5.7 on Debian and Ubuntu - How To Reset Root Password
  • SSH and sshfs timeout settings keepalive
  • Linux How To Add User To Additional Group
  • Howto Set Static IP on boot in initramfs for dropbear or other purposes NFS, Linux, Debian, Ubuntu, CentOS
  • Convert and install to LUKS Encrypted Drive Ubuntu 18.04 19.10 Linux Mint and Debian Based Linux
  • Debian and Netplan
  • CentOS 8 how to restart the network!
  • CentOS 8 how to convert to a bootable mdadm RAID software array
  • ADATA USB Thumb Drive Issues
  • KMODE EXCEPTION NOT HANDLED - QEMU/KVM Won't Boot Windows 2016 or 10 Image or Physical Machine
  • Linux Mint / Ubuntu / Debian Mate Disable Guest Session and Hide Usernames on Lightdm Login screen GUI