I am using mysql in Os X 10.9.2 and when I try to run my php website in local I have this error:
我在Os X 10.9.2中使用mysql,当我尝试在本地运行我的php网站时出现此错误:
Invalid datetime format: 1292 Incorrect datetime value: '' for column nameOfTheColumn
无效的日期时间格式:1292日期时间值不正确:列名为'oFTheColumn''
The Error happens when I tried to insert an empty value in a datetime field. The site works fine in production and in some other locals computers, so the problem is in database and I have to be solve in my local database. Reading about the problem I read that mysql could be running in Strict mode, so I edit /usr/local/mysql/my.cnf and I comment out this line:
当我尝试在datetime字段中插入空值时发生错误。该网站在生产和其他一些本地计算机上工作正常,所以问题出在数据库中,我必须在我的本地数据库中解决。阅读有关我读过的问题,mysql可以在Strict模式下运行,所以我编辑/usr/local/mysql/my.cnf并注释掉这一行:
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
I restart computer and still is not working, but according to the documentation online and others questions, this is the way, but I would like to know What is it the exactly change I have to do to make my application works. Repeat, the code works in production, windows with xampp, etc... so can not be change because is to long and just for work in local I need the right change in MYSQL DATABASE.
我重新启动计算机仍然无法工作,但根据在线文档和其他问题,这就是方法,但我想知道为了使我的应用程序正常运行,我必须做些什么改变。重复一遍,代码在生产中运行,windows与xampp等等...所以不能改变因为很长而且只是在本地工作我需要在MYSQL DATABASE中正确的改变。
Thanks in advance!
提前致谢!
3 个解决方案
#1
8
It looks like the default sql_mode on your osx instance of MySQL is set to refuse invalid dates.
看起来MySQL的osx实例上的默认sql_mode设置为拒绝无效日期。
You could try issuing this command:
您可以尝试发出以下命令:
SET SESSION sql_mode='ALLOW_INVALID_DATES'
as you initialize your session.
在初始化会话时。
#2
0
you can change your column to allow null
您可以将列更改为允许null
ALTER TABLE table_name MODIFY datetime_column_name datetime;
Columns are nullable by default as long as a column is not declared as NOT NULL.
默认情况下,只要列未声明为NOT NULL,列就可以为空。
#3
0
At last I found the solution, in fact I was on the way, the solution is to comment out sql_mode, but the file located in /etc/my.cnf instead of /usr/local/mysql/my.cnf. If you do not comment this line, and you try to insert an empty value an error is raised, but if you comment it out, empty values becomes '0000-00-00 00:00:00'
最后我找到了解决方案,实际上我在路上,解决方法是注释掉sql_mode,但文件位于/etc/my.cnf而不是/usr/local/mysql/my.cnf。如果你不注释这一行,并且你试图插入一个空值,则会引发错误,但是如果你注释掉它,则空值变为'0000-00-00 00:00:00'
#1
8
It looks like the default sql_mode on your osx instance of MySQL is set to refuse invalid dates.
看起来MySQL的osx实例上的默认sql_mode设置为拒绝无效日期。
You could try issuing this command:
您可以尝试发出以下命令:
SET SESSION sql_mode='ALLOW_INVALID_DATES'
as you initialize your session.
在初始化会话时。
#2
0
you can change your column to allow null
您可以将列更改为允许null
ALTER TABLE table_name MODIFY datetime_column_name datetime;
Columns are nullable by default as long as a column is not declared as NOT NULL.
默认情况下,只要列未声明为NOT NULL,列就可以为空。
#3
0
At last I found the solution, in fact I was on the way, the solution is to comment out sql_mode, but the file located in /etc/my.cnf instead of /usr/local/mysql/my.cnf. If you do not comment this line, and you try to insert an empty value an error is raised, but if you comment it out, empty values becomes '0000-00-00 00:00:00'
最后我找到了解决方案,实际上我在路上,解决方法是注释掉sql_mode,但文件位于/etc/my.cnf而不是/usr/local/mysql/my.cnf。如果你不注释这一行,并且你试图插入一个空值,则会引发错误,但是如果你注释掉它,则空值变为'0000-00-00 00:00:00'