DEFAULT CURRENT_TIMESTAMP

[Crystal™ 25]

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 May 8, 2020 by Crystal™

[Owsap 7711]

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

Edited May 8, 2020 by Owsap

[Crystal™ 25]

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

[Owsap 7711]

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

 

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

 

Edited August 25, 2022 by Metin2 Dev
Core X - External 2 Internal

[ridetpro 114]

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 May 8, 2020 by ridetpro

[Crystal™ 25]

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

 

 

 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

 

Edited August 25, 2022 by Metin2 Dev
Core X - External 2 Internal

[Owsap 7711]

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 May 8, 2020 by Owsap

[Crystal™ 25]

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

[AZICKO 7082]

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

[Owsap 7711]

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 May 8, 2020 by Owsap

[Crystal™ 25]

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' ;

?

[Owsap 7711]

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.

[ridetpro 114]

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 May 8, 2020 by ridetpro

[martysama0134 6941]

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 May 8, 2020 by martysama0134