mysql error: Invalid default value for date

This article introduces the mysql error: Invalid default value for date, including its causes, implementation details, and relevant resources. These steps can help improve efficiency when dealing with this mysql error.

Oct 4, 2017 · 1 min read · 59 Words · -Views -Comments · Programming
🇨🇳 阅读中文版
Read with AI

Choose an AI assistant

ChatGPT Open in a new tab with the article link and prompt
Claude Copy the prompt, then open Claude

Open Claude
Trouble reading the article?

If the prompt is missing, copy the full prompt below into your AI assistant. If the AI cannot read the article, paste the visible passage you want to discuss and add your question.

Recently encountered this error during MySQL data migration. I inferred it was caused by MySQL version differences - my A and B machines have MySQL versions 5.6 and 5.7 respectively.

Cause

Googled it and found it was actually a MySQL SQL Mode issue.

Solution

Modify configuration file, restart service

# sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
sql_mode=ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Note: Removing the sql_mode configuration won’t work

Authors
Developer, digital product enthusiast, tinkerer, sharer, open source lover