SQL Error: main.sql invalid default value for 'time'

[Johnathan]

I'm trying to import the sql-files, but I'm getting errors in the fields related to datetime. I'm using the latest version of rathena.

CREATE TABLE IF NOT EXISTS `charlog` (
  `time` datetime NOT NULL default '0000-00-00 00:00:00',
  `char_msg` varchar(255) NOT NULL default 'char select',
  `account_id` int(11) NOT NULL default '0',
  `char_num` tinyint(4) NOT NULL default '0',
  `name` varchar(23) NOT NULL default '',
  `str` int(11) unsigned NOT NULL default '0',
  `agi` int(11) unsigned NOT NULL default '0',
  `vit` int(11) unsigned NOT NULL default '0',
  `int` int(11) unsigned NOT NULL default '0',
  `dex` int(11) unsigned NOT NULL default '0',
  `luk` int(11) unsigned NOT NULL default '0',
  `hair` tinyint(4) NOT NULL default '0',
  `hair_color` int(11) NOT NULL default '0'
) ENGINE=MyISAM; 

Importing the main.sql above, I get the error: ERROR 1067 (42000) at line 271: Invalid default value for 'time'. This error occur in all files which use datetime.

Anyone can help me?

[Johnathan]

I solve this, thank for your help @Hurtsky.

I find the answer here: https://www.sitepoint.com/quick-tip-how-to-permanently-change-sql-mode-in-mysql/

Was like here:

6 hours ago, Hurtsky said:

add this to your my.ini/my.cnf

sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

after [mysqld] for temporary fix

1

 

But I added more things. The newer version of MySql doesn't enable the use of zeros in date fields (date, datetime and etc). So I activated it again, to do this, I run inside MySql:

select @@sql_mode

I copied the configs, removing the tags NO_ZERO_IN_DATE,NO_ZERO_DATE.  After that, I pasted the new configs like @Hurtsky said.

[Hurtsky]

2 hours ago, Johnathan said:

I'm trying to import the sql-files, but I'm getting errors in the fields related to datetime. I'm using the latest version of rathena.

CREATE TABLE IF NOT EXISTS `charlog` (
  `time` datetime NOT NULL default '0000-00-00 00:00:00',
  `char_msg` varchar(255) NOT NULL default 'char select',
  `account_id` int(11) NOT NULL default '0',
  `char_num` tinyint(4) NOT NULL default '0',
  `name` varchar(23) NOT NULL default '',
  `str` int(11) unsigned NOT NULL default '0',
  `agi` int(11) unsigned NOT NULL default '0',
  `vit` int(11) unsigned NOT NULL default '0',
  `int` int(11) unsigned NOT NULL default '0',
  `dex` int(11) unsigned NOT NULL default '0',
  `luk` int(11) unsigned NOT NULL default '0',
  `hair` tinyint(4) NOT NULL default '0',
  `hair_color` int(11) NOT NULL default '0'
) ENGINE=MyISAM; 

Importing the main.sql above, I get the error: ERROR 1067 (42000) at line 271: Invalid default value for 'time'. This error occur in all files which use datetime.

Anyone can help me?

add this to your my.ini/my.cnf

sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

after [mysqld] for temporary fix

[Johnathan]

3 hours ago, Hurtsky said:

add this to your my.ini/my.cnf

sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

after [mysqld] for temporary fix

 

I added this line to /etc/mysql/my.cnf, now a get this error (including to mysqld): [ERROR] Found option without preceding group in config file /etc/mysql/my.cnf at line 20!

[Hurtsky]

6 minutes ago, Johnathan said:

I added this line to /etc/mysql/my.cnf, now a get this error (including to mysqld): [ERROR] Found option without preceding group in config file /etc/mysql/my.cnf at line 20!

not that file i guess, remove back what you add, and add at /etc/mysql/mysql.conf.d/mysqld.cnf

add at last line

[Johnathan]

31 minutes ago, Hurtsky said:

not that file i guess, remove back what you add, and add at /etc/mysql/mysql.conf.d/mysqld.cnf

add at last line

1

Still getting the same error =/

[Hurtsky]

glad that help you