无法更改表，获取错误1067默认值无效 [英] Cannot alter table, getting error 1067 Invalid default value

问题描述

我从运行在linux上的一个mysql服务器还原了一个运行在windows上的mysql数据库。一切似乎都很好，直到我尝试在其中一个表上运行ALTER TABLE。如果我尝试ALTER任何东西，我得到的错误是：
错误代码：1067.'creation_date'的默认值无效

现在奇怪的是，该转储文件已成功还原并创建了表。如果我使用Workbench获取表的create语句，我会看到：

 `creation_date` datetime NOT NULL DEFAULT'0000-00 -00 00:00:00'，
  

对于导致问题的列。 >

CREATE TABLE语句如何成功，现在我无法执行更改？

解决方案

sql_mode和'NO_ZERO_DATE'的一些背景： http://dev.mysql.com/doc/refman/5.1/en/sql-mode.html

您提到已还原表从另一个服务器。如果您使用mysqldump，那么您的问题的答案是，在加载转储SQL时，mysql将关闭NO_ZERO_DATE模式。 Mysqldump（在5.5中测试）将此行放在转储SQL的顶部：

  / *！40101 SET @ OLD_SQL_MODE = @ @SQL_MODE，SQL_MODE ='NO_AUTO_VALUE_ON_ZERO'* /; 
  

加载转储时，它还会关闭外键检查和其他有用的东西。

  / *！40014 SET @OLD_UNIQUE_CHECKS = @@ UNIQUE_CHECKS，UNIQUE_CHECKS = 0 * /; 
 / *！40014 SET @OLD_FOREIGN_KEY_CHECKS = @@ FOREIGN_KEY_CHECKS，FOREIGN_KEY_CHECKS = 0 * /; 
  

这就解释了为什么你能够恢复表，但是当尝试改变你必须更严格地玩规则。如果你真的要重写这个，你可以在你的alter table之前尝试一下：

  mysql> SET SESSION sql_mode =''; 
  

然后alter table。

I restored a mysql database from a mysql server running on linux to one running on windows. Everything seemed fine until I tried to run an ALTER TABLE on one of the tables. The error I get if I try to ALTER anything is: Error Code: 1067. Invalid default value for 'creation_date'

Now the strange thing is that dump file successfully restored and created the table. If I use Workbench to get the create statement for the table I see:

`creation_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

For the column which is causing an issue.

How can the CREATE TABLE statement succeed in such a way that I cannot perform alters on it now?

解决方案

Some background on sql_mode and 'NO_ZERO_DATE': http://dev.mysql.com/doc/refman/5.1/en/sql-mode.html

You mentioned that you restored the tables from another server. If you used mysqldump then the answer to your question is that mysql turned off 'NO_ZERO_DATE' mode while loading the dumped SQL. Mysqldump (tested in 5.5) puts this line at the top of the dumped SQL:

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

It also turns off foreign key checks and other useful things while loading the dump.

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

This explains why you were able to restore the tables, but when trying to alter you must play by stricter rules. If you really want to override this you can try the following just before your alter table:

mysql> SET SESSION sql_mode='';

Then alter table.

这篇关于无法更改表，获取错误1067默认值无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！