SQL DB Error - Field 'ID' not exist in mapserver.exe (SOLVED)

[rakuzas]

Hello,

I'm not sure why this keep appear in my map-server.exe console.. It appears everytime someone pickup any item or drop any item.. I already recheck my settings, empty database and also already import log_upgrade sql file.. But still shows this..

I just drop all the database inside logs database.. And import again log.sql.. But I notice that.. the picklog table is empty and and still got this error..

Can someone please help pinpoint me where exactly this problem occur? Already using latest rathena btw.. Also disabled debug beta things in sourcecode which I think already default by now..



My current log_athena.conf: 

//--------------------------------------------------------------
// rAthena Log Configuration File
//--------------------------------------------------------------
// Note 1: Value is a config switch (on/off, yes/no or 1/0)
// Note 2: Value is in percents (100 means 100%)
// Note 3: Value is a bit field. If no description is given,
//         assume unit types (1: Pc, 2: Mob, 4: Pet, 8: Homun)
//--------------------------------------------------------------

// Enable Logs? (Note 3)
// 0x000000 - Don't log at all
// 0x000001 - (T) Log trades
// 0x000002 - (V) Log vending transactions
// 0x000004 - (P) Log items drop/picked by players
// 0x000008 - (L) Log items drop/looted by monsters
// 0x000010 - (S) Log NPC transactions (buy/sell)
// 0x000020 - (N) Log Script transactions (items deleted/acquired through quests)
// 0x000040 - (D) Log items stolen from mobs (Steal/Gank)
// 0x000080 - (C) Log player-used items (consumables/pet&hom&merc food/items used for skills&attacks)
// 0x000100 - (O) Log produced/ingredient items
// 0x000200 - (U) Log MVP prize items
// 0x000400 - (A) Log player created/deleted items (through @/# commands)
// 0x000800 - (R) Log items placed/retrieved from storage.
// 0x001000 - (G) Log items placed/retrieved from guild storage.
// 0x002000 - (E) Log mail system transactions.
// 0x004000 - (I) Log auction system transactions.
// 0x008000 - (B) Log buying store transactions
// 0x010000 - (X) Log all other transactions (rentals expiring/inserting cards/items removed by item_check/
//           rings deleted by divorce/pet egg (un)hatching/pet armor (un)equipping/Weapon Refine skill/Remove Trap skill)
// 0x020000 - ($) Log cash transactions
// 0x040000 - (K) Log account bank transactions
// 0x080000 - (F) Removed bound items when guild/party is broken
// 0x100000 - (Y) Roulette Lottery
// 0x200000 - (Z) Merged items from item mergers process.
// 0x400000 - (Q) Log items given from quest-granted drops.
// Example: Log trades+vending+script items+created items: 1+2+32+1024 = 1059
// Please note that moving items from inventory to cart and back is not logged by design.
enable_logs: 0xFFFFFF

// Use MySQL Logs? [SQL Version Only] (Note 1)
sql_logs: yes

// LOGGING FILTERS
// =============================================================
// if any condition is true then the item will be logged
// 0 = Don't log at all
// 1 = Log any item
// Advanced Filter Bits by item type: ||
// 0002 - Healing items (0)
// 0004 - Etc Items(3) + Arrows (10)
// 0008 - Usable Items(2) + Lures,Scrolls(11) + Usable Cash Items(18)
// 0016 - Weapon(4)
// 0032 - Shields,Armor,Headgears,Accessories,etc(5)
// 0064 - Cards(6)
// 0128 - Pet Accessories(8) + Eggs(7) (well, monsters don't drop 'em but we'll use the same system for ALL logs)
// 0256 - Log expensive items ( >= price_items_log)
// 0512 - Log big amount of items ( >= amount_items_log)
// 1024 - Log refined items (if their refine >= refine_items_log )
// 2048 - Log rare items (if their drop chance <= rare_items_log )

// Examples: (log filters)
// log_filter: 1    = logs ANY items
// log_filter: 2    = logs only HEALING items
// log_filter: 4    = logs only Etc Items and Arrows
// log_filter: 64   = logs only Cards
// log_filter: 322  = logs only Healing items, Cards and those items which price is >= price_items_log
// log_filter: 4080 = logs all items (including all rare, big amount) exept healing, etc, arrows and useble ones
log_filter: 1

// Log Items which Refine >= refine_items_log
refine_items_log: 5
// Log Items whith min drop rate <= rare_items_log
// 1 = 0.01%, 100 = 1% drop chance, etc
rare_items_log: 100
// don't log it if the current item buy price < price_items_log
price_items_log: 1000
// don't log it if the current item amount < amount_items_log
amount_items_log: 100
//=============================================================

// Log Dead Branch Usage (Note 1)
log_branch: no

// Track Zeny Changes
// Filter settings
// 0 - don't log; 1 - log any zeny changes; 2.....1000000 - minimal absolute logging zeny value
log_zeny: 1

// Track Cash Changes
// 0 - don't log
// 1 - log any changes
log_cash: 1

// Log MVP Monster Drops (Note 1)
// Outdated. Use Pick_Log instead. But this log could be useful to keep track slayed MVPs
log_mvpdrop: no

// Log AtCommands & Charcommands (Note 1)
// Only commands issued by player groups ('conf/groups.conf') with
// 'log_commands' setting set to 'true' will be logged.
log_commands: yes

// Log NPC 'logmes' commands (Note 1)
log_npc: yes

// Log CHAT (Global, Whisper, Party, Guild, Main chat, Clan) (Note 3)
// LOGGING FILTERS
// =============================================================
// 0 = Don't log at all
// 1 - Log Global messages
// 2 - Log Whisper messages
// 4 - Log Party messages
// 8 - Log Guild messages
// 16 - Log Main chat messages
// 32 - Log Clan messages
// Example:
// log_chat: 5 = logs both Whisper & Party messages
// log_chat: 8 = logs only Guild messages
// log_chat: 63 = logs everything
log_chat: 0

// Disable chat logging when WoE is running? (Note 1)
log_chat_woe_disable: no

// Log feeding
// Should pet or homunculus feeding be logged? (Note 3)
// 0: Disabled
// 1: Pet
// 2: Homunculus
log_feeding: 3

//Time-stamp format which will be printed for log file.
//Can at most be 20 characters long.
//Common formats:
// %I:%M:%S %p (hour:minute:second 12 hour, AM/PM format)
// %H:%M:%S (hour:minute:second, 24 hour format)
// %d/%b/%Y (day/Month/year)
//For full format information, consult the strftime() manual.
log_timestamp_format: %m/%d/%Y %H:%M:%S

// Logging files/tables
// Following settings specify where to log to. If 'sql_logs' is
// enabled, SQL tables are assumed, otherwise flat files.

// log_gm_db: log/atcommandlog.log
// log_branch_db: log/branchlog.log
// log_chat_db: log/chatlog.log
// log_mvpdrop_db: log/mvplog.log
// log_npc_db: log/npclog.log
// log_pick_db: log/picklog.log
// log_zeny_db: log/zenylog.log
// log_cash_db: log/cashlog.log
// log_feeding_db: log/feedinglog.log

log_gm_db: atcommandlog
log_branch_db: branchlog
log_chat_db: chatlog
log_mvpdrop_db: mvplog
log_npc_db: npclog
log_pick_db: picklog
log_zeny_db: zenylog
log_cash_db: cashlog
log_feeding_db: feedinglog

import: conf/import/log_conf.txt

my current inter_athena.conf

 

// Athena InterServer configuration.
// Contains settings shared/used by more than 1 server.

// Options for both versions

// Log Inter Connections, etc.?
log_inter: 1

// Inter Log Filename
inter_log_filename: log/inter.log

// Level range for sharing within a party
party_share_level: 50

// You can specify the codepage to use in your MySQL tables here.
// (Note that this feature requires MySQL 4.1+)
//default_codepage:

// For IPs, ideally under linux, you want to use localhost instead of 45.77.40.127
// Under windows, you want to use 45.77.40.127.  If you see a message like
// "Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)"
// and you have localhost, switch it to XXXXX

// Global SQL settings
// overridden by local settings when the hostname is defined there
// (currently only the login-server reads/obeys these settings)

// MySQL Login server
login_server_ip: XXXXX
login_server_port: 3306
login_server_id: XXXXX
login_server_pw: XXXXX
login_server_db: ragnarok
login_codepage:
login_case_sensitive: no

ipban_db_ip: XXXXX
ipban_db_port: 3306
ipban_db_id: XXXXX
ipban_db_pw: XXXXX
ipban_db_db: ragnarok
ipban_codepage:

// MySQL Character server
char_server_ip: XXXXX
char_server_port: 3306
char_server_id: XXXXX
char_server_pw: XXXXX
char_server_db: ragnarok

// MySQL Map Server
map_server_ip: XXXXX
map_server_port: 3306
map_server_id: XXXXX
map_server_pw: XXXXX
map_server_db: ragnarok

// MySQL Log Database
log_db_ip: XXXXX
log_db_port: 3306
log_db_id: XXXXX
log_db_pw: XXXXX
log_db_db: logs
log_codepage:
log_login_db: loginlog

// MySQL Reconnect Settings
// - mysql_reconnect_type:
//   1: When MySQL disconnects during runtime, the server tries to reconnect
//      mysql_reconnect_count times and shuts down if unsuccessful.
//   2: When mysql disconnects during runtime, it tries to reconnect indefinitely.
mysql_reconnect_type: 2
mysql_reconnect_count: 1

// DO NOT CHANGE ANYTHING BEYOND THIS LINE UNLESS YOU KNOW YOUR DATABASE DAMN WELL
// this is meant for people who KNOW their stuff, and for some reason want to change their
// database layout. [CLOWNISIUS]

// ALL MySQL Database Table names

// Login Database Tables
login_server_account_db: login
ipban_table: ipbanlist

// Shared
global_acc_reg_num_table: global_acc_reg_num
global_acc_reg_str_table: global_acc_reg_str

// Char Database Tables
char_db: char
hotkey_db: hotkey
scdata_db: sc_data
cart_db: cart_inventory
inventory_db: inventory
charlog_db: charlog
skill_db: skill
interlog_db: interlog
memo_db: memo
guild_db: guild
guild_alliance_db: guild_alliance
guild_castle_db: guild_castle
guild_expulsion_db: guild_expulsion
guild_member_db: guild_member
guild_skill_db: guild_skill
guild_position_db: guild_position
guild_storage_db: guild_storage
party_db: party
pet_db: pet
friend_db: friends
mail_db: mail
mail_attachment_db: mail_attachments
auction_db: auction
quest_db: quest
homunculus_db: homunculus
skill_homunculus_db: skill_homunculus
mercenary_db: mercenary
mercenary_owner_db: mercenary_owner
elemental_db: elemental
ragsrvinfo_db: ragsrvinfo
skillcooldown_db: skillcooldown
bonus_script_db: bonus_script
acc_reg_num_table: acc_reg_num
acc_reg_str_table: acc_reg_str
char_reg_str_table: char_reg_str
char_reg_num_table: char_reg_num
clan_table: clan
clan_alliance_table: clan_alliance

// Map Database Tables
buyingstore_table: buyingstores
buyingstore_items_table: buyingstore_items
item_table: item_db
renewal-item_table: item_db_re
item2_table: item_db2
renewal-item2_table: item_db2_re
item_cash_table: item_cash_db
item_cash2_table: item_cash_db2
mob_table: mob_db
renewal-mob_table: mob_db_re
mob2_table: mob_db2
renewal-mob2_table: mob_db2_re
mob_skill_table: mob_skill_db
renewal-mob_skill_table: mob_skill_db_re
mob_skill2_table: mob_skill_db2
renewal-mob_skill2_table: mob_skill_db2_re
mapreg_table: mapreg
sales_table: sales
vending_table: vendings
vending_items_table: vending_items
market_table: market
roulette_table: db_roulette

// Use SQL item_db, mob_db and mob_skill_db for the map server? (yes/no)
use_sql_db: no

inter_server_conf: inter_server.yml

import: conf/import/inter_conf.txt

my current login_athena.conf

// Athena Login Server configuration file.
// Translated by Peter Kieser <[email protected]>

// Note: "Comments" are all text on the right side of a double slash "//"
// Whatever text is commented will not be parsed by the servers, and serves
// only as information/reference.

// The login server listens on the interface with this IP address.
// NOTE: This allows you to run multiple servers on multiple interfaces
// while using the same ports for each server.
bind_ip: XXXXX
 
// Login Server Port
login_port: 6900

//Time-stamp format which will be printed before all messages.
//Can at most be 20 characters long.
//Common formats:
// %I:%M:%S %p (hour:minute:second 12 hour, AM/PM format)
// %H:%M:%S (hour:minute:second, 24 hour format)
// %d/%b/%Y (day/Month/year)
//For full format information, consult the strftime() manual.
//timestamp_format: [%d/%b %H:%M]

//If redirected output contains escape sequences (color codes)
stdout_with_ansisequence: no

//Makes server log selected message types to a file in the /log/ folder
//1: Log Warning Messages
//2: Log Error and SQL Error messages.
//4: Log Debug Messages
//Example: "console_msg_log: 7" logs all 3 kinds
//Messages logged by this overrides console_silent setting
console_msg_log: 7

// File path to store the console messages above
console_log_filepath: ./log/login-msg_log.log

//Makes server output more silent by omitting certain types of messages:
//1: Hide Information messages
//2: Hide Status messages
//4: Hide Notice Messages
//8: Hide Warning Messages
//16: Hide Error and SQL Error messages.
//32: Hide Debug Messages
//Example: "console_silent: 7" Hides information, status and notice messages (1+2+4)
console_silent: 0

// Console Commands
// Allow for console commands to be used on/off
// This prevents usage of >& log.file
console: off

// Can you use _M/_F to make new accounts on the server?
new_account: no

//If new_account is enabled, minimum length to userid and passwords should be 4?
//Must be 'Yes' unless your client uses both 'Disable 4 LetterUserID/Password' Diffs
new_acc_length_limit: yes

// Account registration flood protection system
// allowed_regs is the number of registrations allowed in time_allowed (in seconds)
allowed_regs: 1
time_allowed: 10

// Log Filename. All operations received by the server are logged in this file. 
login_log_filename: log/login.log

// To log the login server?
// NOTE: The login server needs the login logs to enable dynamic pass failure bans.
log_login: yes

// Indicate how to display date in logs, to players, etc.
date_format: %Y-%m-%d %H:%M:%S

// Required account group id to connect to server.
// -1: disabled
// 0 or more: group id
group_id_to_connect: -1

// Minimum account group id required to connect to server.
// Will not function if group_id_to_connect config is enabled.
// -1: disabled
// 0 or more: group id
min_group_id_to_connect: -1

// Which group (ID) will be denoted as the VIP group?
// Default: 5
vip_group: 5

// How many characters are allowed per account?
// You cannot exceed the limit of MAX_CHARS slots, defined in mmo.h, or chars_per_account
// will default to MAX_CHARS.
// 0 will default to the value of MIN_CHARS. (default)
chars_per_account: 0

// Max character limit increase for VIP accounts (0 to disable)
// Increase the value of MAX_CHARS if you want to increase vip_char_increase.
// Note: The amount of VIP characters = MAX_CHARS - chars_per_account.
// Note 2: This setting must be set after chars_per_account.
// -1 will default to MAX_CHAR_VIP (src/config/core.h)
vip_char_increase: -1

// Create accounts with limited time?
// -1: new accounts are created with unlimited time (default)
// 0 or more: new accounts automatically expire after the given value, in seconds
start_limited_time: -1

// Store passwords as MD5 hashes instead of plain text?
// NOTE: Will not work with clients that use <passwordencrypt>
use_MD5_passwords: no

// Ipban features
ipban_enable: yes
// Dynamic password failure ipban system
// Ban user after a number of failed attempts?
ipban_dynamic_pass_failure_ban: yes
// Interval (in minutes) to calculate how many failed attempts.
ipban_dynamic_pass_failure_ban_interval: 5
// Maximum amount of failed attempts before banning.
ipban_dynamic_pass_failure_ban_limit: 7
// Time (in minutes) for ban duration.
ipban_dynamic_pass_failure_ban_duration: 5
// Interval (in seconds) to clean up expired IP bans. 0 = disabled. default = 60.
// NOTE: Even if this is disabled, expired IP bans will be cleaned up on login server start/stop.
// Players will still be able to login if an ipban entry exists but the expiration time has already passed.
ipban_cleanup_interval: 60

// Interval (in minutes) to execute a DNS/IP update. Disabled by default.
// Enable it if your server uses a dynamic IP which changes with time.
//ip_sync_interval: 10

// DNS Blacklist Blocking
// If enabled, each incoming connection will be tested against the blacklists 
// on the specified dnsbl_servers (comma-separated list)
use_dnsbl: no
dnsbl_servers: bl.blocklist.de, socks.dnsbl.sorbs.net
// Here are some free DNS Blacklist Services: http://en.wikipedia.org/wiki/Comparison_of_DNS_blacklists
//==============================================================================
//   dnsbl_servers                 Description
// - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
// bl.blocklist.de                 IP-Addresses who attack other servers/honeypots over SSH, FTP, IMAP, etc.
// ircbl.ahbl.org                  AHBL (open proxies, compromised machines, comment spammers)
// safe.dnsbl.sorbs.net            All zones in dnsbl.sorbs.net except "recent" and "escalations"
// sbl-xbl.spamhaus.org            Spamhaus blacklist (spammers, open proxies)
// socks.dnsbl.sorbs.net           Open SOCKS proxy servers
// tor.ahbl.org                    Current tor relay and exit nodes

// Client MD5 hash check
// If turned on, the login server will check if the client's hash matches
// the value below, and will not connect tampered clients.
// Note: see 'doc/md5_hashcheck.txt' for more details.
client_hash_check: off

// Client MD5 hashes
// The client with the specified hash can be used to log in by players with
// a group_id equal to or greater than the given value.
// If you specify 'disabled' as hash, players with a group_id greater than or
// equal to the given value will be able to log in regardless of hash (and even
// if their client does not send a hash at all.)
// Format: group_id, hash
// Note: see 'doc/md5_hashcheck.txt' for more details.
//client_hash: 0, 113e195e6c051bb1cfb12a644bb084c5
//client_hash: 10, cb1ea78023d337c38e8ba5124e2338ae
//client_hash: 99, disabled

import: conf/inter_athena.conf
import: conf/import/login_conf.txt

 

Edited November 19, 2017 by rakuzas

[Jey]

Looks like your 'id' field doesn't have a default value

id normally has the auto_increment attribute: https://github.com/rathena/rathena/blob/master/sql-files/logs.sql#L170

You either can add it by hand, or drop the table and re-add it.

[rakuzas]

5 minutes ago, Jey said:

Looks like your 'id' field doesn't have a default value

id normally has the auto_increment attribute: https://github.com/rathena/rathena/blob/master/sql-files/logs.sql#L170

You either can add it by hand, or drop the table and re-add it.

 

I tried solve this problem for weeks.. Tried drop table and re-upload logs.sql.. Already tried upload upgrade log sql.. But I did as you said.. Drop the table.. Then manually add it.. And it fixed already..  

Thank you!! 

 

CREATE TABLE IF NOT EXISTS `picklog` (
  `id` int(11) NOT NULL auto_increment,
  `time` datetime NOT NULL,
  `char_id` int(11) NOT NULL default '0',
  `type` enum('M','P','L','T','V','S','N','C','A','R','G','E','B','O','I','X','D','U','$','F','Z','Q') NOT NULL default 'P',
  `nameid` smallint(5) unsigned NOT NULL default '0',
  `amount` int(11) NOT NULL default '1',
  `refine` tinyint(3) unsigned NOT NULL default '0',
  `card0` smallint(5) unsigned NOT NULL default '0',
  `card1` smallint(5) unsigned NOT NULL default '0',
  `card2` smallint(5) unsigned NOT NULL default '0',
  `card3` smallint(5) unsigned NOT NULL default '0',
  `option_id0` smallint(5) NOT NULL default '0',
  `option_val0` smallint(5) NOT NULL default '0',
  `option_parm0` tinyint(3) NOT NULL default '0',
  `option_id1` smallint(5) NOT NULL default '0',
  `option_val1` smallint(5) NOT NULL default '0',
  `option_parm1` tinyint(3) NOT NULL default '0',
  `option_id2` smallint(5) NOT NULL default '0',
  `option_val2` smallint(5) NOT NULL default '0',
  `option_parm2` tinyint(3) NOT NULL default '0',
  `option_id3` smallint(5) NOT NULL default '0',
  `option_val3` smallint(5) NOT NULL default '0',
  `option_parm3` tinyint(3) NOT NULL default '0',
  `option_id4` smallint(5) NOT NULL default '0',
  `option_val4` smallint(5) NOT NULL default '0',
  `option_parm4` tinyint(3) NOT NULL default '0',
  `unique_id` bigint(20) unsigned NOT NULL default '0',
  `map` varchar(11) NOT NULL default '',
  `bound` tinyint(1) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  INDEX (`type`)
) ENGINE=MyISAM AUTO_INCREMENT=1;