Jump to content
  • We need you!

    You must register to discover all the features of our community!

  • 0

DEFAULT CURRENT_TIMESTAMP


Question

  • VIP
Posted (edited)

ERROR 1067 (42000) at line 243: Invalid default value for 'time'

I get this error when i am trying to run a sql file.

This is the line when it causes me the problem : `time` datetime

Spoiler

NOT NULL DEFAULT CURRENT_TIMESTAMP,

When i delete the DEFAULT CURRENT_TIMESTAMP runs successfully.

Does anyone knows how to fix it?

Edited by Crystal™ (see edit history)
Link to post

13 answers to this question

Recommended Posts

  • 0
  • VIP
13 minutes ago, Owsap said:

If you're setting your default value as '0000-00-00 00:00:00' you might want to disable STRICT_TRANS_TABLES /usr/local/my.cnf

This file does not exist /usr/local/my.cnf

Link to post
  • 0
Posted (edited)

For mysql 5.7 +

usr/local/etc/mysql/my.cnf

Add:

[mysqld]

sql-mode =

 

For mysql 5.6 -

usr/local/my.cnf

Change existing sql-mode to:

[mysqld]

sql-mode=

 

 

In both cases leave sql mode empty, just with =

Restart mysql server

 

Then run mysql_upgrade

 

:)

Edited by ridetpro (see edit history)
  • Love 1
Link to post
  • 0
  • VIP

Still can't find my.cnf

1 hour ago, ridetpro said:

For mysql 5.7 +

usr/local/etc/mysql/my.cnf

Add:

[mysqld]

sql-mode =

 

For mysql 5.6 -

usr/local/my.cnf

Change existing sql-mode to:

[mysqld]

sql-mode=

 

 

In both cases leave sql mode empty, just with =

Restart mysql server

 

Then run mysql_upgrade

 

:)

 

1 hour ago, Owsap said:

You can find the file location using ps -aux|grep mysql

 

In my case it's located @ /usr/local/my.cnf

 

OcwucJX.png

 

 ps -aux|grep mysql
 

Spoiler

mysql  676   0.0  0.1   5092   2212  -  Is   17:38      0:00.00 /bin/sh /usr/local/bin/mysqld_safe --defaults-extra-file=/var/db/mysql/my.cnf --user=mysql --d
mysql  770   0.0  4.9 257880 100768  -  I    17:38      0:13.29 /usr/local/libexec/mysqld --defaults-extra-file=/var/db/mysql/my.cnf --basedir=/usr/local --da
root  3374   0.0  0.1   4632   2128  0  I+   12:23      0:00.00 grep mysql
root  3562   0.0  0.1   4632   2124  1  S+   13:46      0:00.00 grep mysql

 

Link to post
  • 0
  • VIP
Posted (edited)

Create a new file my.cnf in your /usr/locale

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 

sql_mode=NO_ENGINE_SUBSTITUTION 

 

Edited by Owsap (see edit history)
  • Love 1
Link to post
  • 0
  • VIP
17 minutes ago, Owsap said:

Create a new file my.cnf in your /usr/locale

# For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html [mysqld] # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # These are commonly set, remove the # and set as required. # basedir = ..... # datadir = ..... # port = ..... # server_id = ..... # socket = ..... # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M sql_mode=NO_ENGINE_SUBSTITUTION

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 

sql_mode=NO_ENGINE_SUBSTITUTION 

 

same error

Link to post
  • 0
  • Administrator

You can replace all 0000-00-00 00:00:00 with 2001-01-01 00:00:00 in your sql file

logo.pngmiaw-network.png
 

Link to post
  • 0
  • VIP
Posted (edited)

Try to add ALLOW_INVALID_DATES in the my.cnf

 

Like so,

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,ALLOW_INVALID_DATES 

Then restart your MySQL

service mysql-server restart

Once you haved restarted your MySQL run the query and confirm the variable is set.

SHOW VARIABLES LIKE 'sql_mode' ;

Now I think you can add '0000-00-00 00:00:00' to dates without any problem.

Edited by Owsap (see edit history)
Link to post
  • 0
  • VIP
2 minutes ago, Owsap said:

Try to add ALLOW_INVALID_DATES in the my.cnf

 

Like so,

# For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html [mysqld] # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # These are commonly set, remove the # and set as required. # basedir = ..... # datadir = ..... # port = ..... # server_id = ..... # socket = ..... # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,ALLOW_INVALID_DATES

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,ALLOW_INVALID_DATES 

Then restart your MySQL

service mysql-server restart

service mysql-server restart

Once you haved restarted your MySQL run the query and confirm the variable is set.

SHOW VARIABLES LIKE 'sql_mode' ;

SHOW VARIABLES LIKE 'sql_mode' ;

Now I think you can add '0000-00-00 00:00:00' to dates without any problem.

I restarted mysql before,shouldi add this in my.cnf ->

SHOW VARIABLES LIKE 'sql_mode' ;

?

Link to post
  • 0
  • VIP
1 minute ago, Crystal™ said:

I restarted mysql before,shouldi add this in my.cnf ->

SHOW VARIABLES LIKE 'sql_mode' ;

?

No, that's a query you can run in Navicat, for example which you can see the variables of the sql_mode, it's just so you can confirm they were set.

Link to post
  • 0
Posted (edited)

I told you what to do, if you are not able to do it just ask your host company to do it.

Just read over and over my message... If you have 5.6 and there's no my.cnf then creare a black my.cnf in usr/locale/

 

Mysql 5.6 /usr/local/

[mysqld]
sql_mode=

mysql 5.7, edit the existing on    usr/local/etc/mysql/

[mysqld]
sql-mode=

Be careful at underscores

 

AND RESET MYSQL SERVER 

Edited by ridetpro (see edit history)
Link to post
  • 0
Posted (edited)
3 hours ago, Crystal™ said:

DEFAULT CURRENT_TIMESTAMP

I added this in those field because the old "0000/00/00 00:00" date could not be accepted in new mysql versions (no valid date).

DEFAULT CURRENT_TIMESTAMP is not supported by mysql 5.5, but since 5.6.

It returns NOW().

Edited by martysama0134 (see edit history)
  • Lucky 1
Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now


×
×
  • Create New...

Important Information

Terms of Use / Privacy Policy / Guidelines / We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.