SQL error?

[anjasoleil0]

Error

SQL query: 

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_id` INT(11) UNSIGNED 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

MySQL said: 

#1067 - Invalid default value for 'time'

If I change the value of time, I'm afraid it's gonna cause troubles with the clientside. Any thoughts?

 

[maqc1]

According to MySQL documentation, it is possible to assign a default value to the "datetime" data type since MySQL 5.6.5. Maybe you have an older version?

In the end, it's just the default value. This value should *technically* never appear, so you could just remove the default value :

`time` DATETIME NOT NULL,

 

[anjasoleil0]

According to MySQL documentation, it is possible to assign a default value to the "datetime" data type since MySQL 5.6.5. Maybe you have an older version?

In the end, it's just the default value. This value should *technically* never appear, so you could just remove the default value :

`time` DATETIME NOT NULL,

Thanks for the reply, but it's not just that, I've been getting lots of sql errors besides that, and I'm not quite sure why, I tried the new git and my old server files, im getting the same error, perhaps I have a different version of sql? but i doubt it, 

MySQL Version: 5.7.9 --- my sql

 

[maqc1]

What do you mean by "lots of sql errors" ? Can you post a screenshot?

 

[anjasoleil0]

What do you mean by "lots of sql errors" ? Can you post a screenshot?

: DB error - Incorrect date value: '0000-00-00' for column 'birthdate' at row 1

[Debug]: at c:\users\anjo\desktop\server\src\login\account_sql.c:687 - UPDATE `login` SET `userid`=?,`user_pass`=?,`sex`=?,`email`=?,`group_id`=?,`state`=?,`unban_time`=?,`expiration_time`=?,`logincount`=?,`lastlogin`=?,`last_ip`=?,`birthdate`=?,`character_slots`=?,`pincode`=?,`pincode_change`=? WHERE `account_id` = '1'
I can run the server and all, I've already finished off the sqls and such, but I get db errors on server side, I'm afraid this would cause errors in-game. Any thoughts?

 

[maqc1]

What is the MySQL data type for the row "birthdate" in the "login" table? It should be DATE. Maybe you have it set to DATETIME?

As of in-game errors, I honestly don't know where and when is used the "birthdate" row. On my server, all accounts have a "0000-00-00" value, which means it probably isn't used.

 

[anjasoleil0]

What is the MySQL data type for the row "birthdate" in the "login" table? It should be DATE. Maybe you have it set to DATETIME?

As of in-game errors, I honestly don't know where and when is used the "birthdate" row. On my server, all accounts have a "0000-00-00" value, which means it probably isn't used.

it IS on datetime... but i haven't change it, shouldn't it be datetime by default? I checked rathena and they too use datetime. And if that's the case, shouldn't I be the only one experiencing this error? Hey thanks for the replies BTW

 

[maqc1]

Well according to Hercules' repository (https://github.com/HerculesWS/Hercules/blob/master/sql-files/main.sql), the 'birthdate' row should be DATE, not DATETIME :

CREATE TABLE IF NOT EXISTS `login` (
`account_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`userid` VARCHAR(23) NOT NULL DEFAULT '',
`user_pass` VARCHAR(32) NOT NULL DEFAULT '',
`sex` ENUM('M','F','S') NOT NULL DEFAULT 'M',
`email` VARCHAR(39) NOT NULL DEFAULT '',
`group_id` TINYINT(3) NOT NULL DEFAULT '0',
`state` INT(11) UNSIGNED NOT NULL DEFAULT '0',
`unban_time` INT(11) UNSIGNED NOT NULL DEFAULT '0',
`expiration_time` INT(11) UNSIGNED NOT NULL DEFAULT '0',
`logincount` MEDIUMINT(9) UNSIGNED NOT NULL DEFAULT '0',
`lastlogin` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
`last_ip` VARCHAR(100) NOT NULL DEFAULT '',
`birthdate` DATE NOT NULL DEFAULT '0000-00-00',
`character_slots` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
`pincode` VARCHAR(4) NOT NULL DEFAULT '',
`pincode_change` INT(11) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY (`account_id`),
KEY `name` (`userid`)
) ENGINE=MyISAM AUTO_INCREMENT=2000000;



So that's your problem. The server is trying to append DATE information in a DATETIME row. Switch it back to DATE and you should be fine =).