SQL error?

[anjasoleil0 0]

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 8]

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,

Edited March 17, 2016 by maqc1

[anjasoleil0 0]

 

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 8]

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

[anjasoleil0 0]

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 8]

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 0]

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 8]

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 =).

Edited March 20, 2016 by maqc1