We are running kea DHCP server with MySQL DB and today we encounter following error and after googling everyone saying it could be MySQL bug so wanted to see if it's really a bug or implementation issue.

MariaDB-server-10.2.15-1.el7.centos.x86_64 (3 node galera cluster)

2020-03-07 01:59:16.484 INFO  [kea-dhcp4.leases/29796] DHCP4_LEASE_ALLOC [hwtype=1 00:0c:29:ae:77:cb], cid=[no info], tid=0x80b9c710: lease has been allocated
2020-03-07 02:04:29.625 ERROR [kea-dhcp4.alloc-engine/29796] ALLOC_ENGINE_V4_ALLOC_ERROR [hwtype=1 00:19:85:f0:1c:89], cid=[no info], tid=0xd7790768: error during attempt to allocat
e an IPv4 address: unable to execute for <UPDATE lease4 SET address = ?, hwaddr = ?, client_id = ?, valid_lifetime = ?, expire = ?, subnet_id = ?, fqdn_fwd = ?, fqdn_rev = ?, hostna
me = ?, state = ? WHERE address = ?>, reason: Incorrect datetime value: '2020-03-08 02:04:29' for column 'expire' at row 1 (error code 1292)
  • what type is the column expire - SHOW CREATE TABLE lease4? 10.2.15 is quite old and there 16 release fixes of the 10.2 series. – danblack Mar 8 '20 at 22:01
  • may i know reason for down vote? – Satish Mar 9 '20 at 20:43
  • 1
    Don't worry about the down vote. Enough people think its good enough. Based on the answer, it wasn't something I thought about and was a good question. – danblack Mar 10 '20 at 0:06

This is likely to be a problem with daylight saving time: In the US (and possibly other places) the hour between 02:00am and 03:00am on 8th March 2020 does not exist because the clocks are moved forward one hour at 02:00am to 03:00am.

  • So it's mysql bug or something else? – Satish Mar 9 '20 at 20:42
  • @Satish - no, assuming you really are in the affected time zone, then it's not a MySQL bug. It might be a misconfiguration - perhaps your OS has a different time zone than the MySQL server? The question you have to ask yourself is: Why did kea DHCP server think that was the time, when that is a time that doesn't exist? – dbdemon Mar 9 '20 at 20:51
  • I believe OS automatically convert time no matter which TIMEZONE you live, I have kea running on UTC timezone and MySQL running on EDT timezone. – Satish Mar 9 '20 at 20:57
  • @Satish I'm no expert on this, but EDT observes DST whereas UTC doesn't. You may need to put them in the same time zone or do something else clever to ensure this doesn't happen again. – dbdemon Mar 9 '20 at 21:03
  • Now i will put both of them in UTC but just trying to understand how things can wrong with daylight saving, i would love to run TEST in lab and see if i can reproduce this error. – Satish Mar 9 '20 at 21:18

Use TIMESTAMP instead of DATETIME. That converts your system time into UTC for storing, then converts back when fetching.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.