Add basic ci tests and .gitlab-ci.yml

Also add configs for ci.

30 files changed, 523 insertions, 14 deletions

diff --git a/.gitlab-ci.yml b/.gitlab-ci.yml
new file mode 100644
index 00000000..9ec82a3c
--- /dev/null
+++ b/.gitlab-ci.yml
@@ -0,0 +1,92 @@
+stages:
+- build
+- test
+- fail
+- ok
+
+before_script:
+  - uname -a
+image: debian:stable
+
+variables:
+  MYSQL_ROOT_PASSWORD: root
+
+build_test:
+  stage: build
+  script:
+  - ./.tools/jobs/install.sh "mysql-client
+    gcc-6
+    git-core
+    make autoconf automake autopoint
+    libtool libmysqlclient-dev libz-dev libpcre3-dev"
+  - ./.tools/jobs/cloneall.sh
+  - ./.tools/jobs/build.sh "--enable-debug --enable-sanitize=full --disable-manager --enable-Werror --enable-buildbot" "--enable-werror --enable-lto --enable-sanitize"
+  image: debian:unstable
+  services:
+  - mysql:latest
+
+mysql_init_latest:
+  stage: build
+  script:
+  - ./.tools/jobs/install.sh "mysql-client"
+  - ./.tools/jobs/clonedata.sh
+  - ./.tools/jobs/initdb.sh mysql
+  image: debian:unstable
+  services:
+  - mysql:latest
+
+mariadb_init_latest:
+  stage: build
+  script:
+  - ./.tools/jobs/install.sh "mysql-client"
+  - ./.tools/jobs/clonedata.sh
+  - ./.tools/jobs/initdb.sh mariadb
+  image: debian:unstable
+  services:
+  - mariadb:latest
+
+mysql_run_latest:
+  stage: test
+  script:
+  - ./.tools/jobs/install.sh "mysql-client
+    gcc-6
+    git-core
+    make autoconf automake autopoint
+    libtool libmysqlclient-dev libz-dev libpcre3-dev"
+  - ./.tools/jobs/cloneall.sh
+  - ./.tools/jobs/initdb.sh mysql
+  - ./.tools/jobs/build.sh "--enable-debug --enable-sanitize=full --disable-manager --enable-Werror --enable-buildbot" "--enable-werror --enable-lto --enable-sanitize"
+  - ./.tools/jobs/runserver.sh mysql
+  image: debian:unstable
+  services:
+  - mysql:latest
+
+mariadb_run_latest:
+  stage: test
+  script:
+  - ./.tools/jobs/install.sh "mysql-client
+    gcc-6
+    git-core
+    make autoconf automake autopoint
+    libtool libmysqlclient-dev libz-dev libpcre3-dev"
+  - ./.tools/jobs/cloneall.sh
+  - ./.tools/jobs/initdb.sh mariadb
+  - ./.tools/jobs/build.sh "--enable-debug --enable-sanitize=full --disable-manager --enable-Werror --enable-buildbot" "--enable-werror --enable-lto --enable-sanitize"
+  - ./.tools/jobs/runserver.sh mariadb
+  image: debian:unstable
+  services:
+  - mariadb:latest
+
+ok_job:
+  stage: ok
+  script:
+  - pwd
+  - echo $CI_BUILD_REF_NAME
+  when: on_success
+
+fail_job:
+  stage: fail
+  script:
+  - pwd
+  - echo $CI_BUILD_REF_NAME
+  when: on_failure
diff --git a/.tools/conf/mariadb/battle_conf.txt b/.tools/conf/mariadb/battle_conf.txt
new file mode 100644
index 00000000..e69de29b
--- /dev/null
+++ b/.tools/conf/mariadb/battle_conf.txt
diff --git a/.tools/conf/mariadb/char_conf.txt b/.tools/conf/mariadb/char_conf.txt
new file mode 100644
index 00000000..d6545a04
--- /dev/null
+++ b/.tools/conf/mariadb/char_conf.txt
@@ -0,0 +1,8 @@
+min_char_class: 0
+max_char_class: 6
+
+// Login Server Port
+login_port: 6901
+
+// Character Server Port
+char_port: 6122
diff --git a/.tools/conf/mariadb/inter_conf.txt b/.tools/conf/mariadb/inter_conf.txt
new file mode 100644
index 00000000..08234300
--- /dev/null
+++ b/.tools/conf/mariadb/inter_conf.txt
@@ -0,0 +1,36 @@
+// For IPs, ideally under linux, you want to use localhost instead of 127.0.0.1 
+// Under windows, you want to use 127.0.0.1.  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 127.0.0.1
+
+// Global SQL settings
+// overridden by local settings when the hostname is defined there
+// (currently only the login-server reads/obeys these settings)
+sql.db_hostname: mariadb
+sql.db_port: 3306
+sql.db_username: evol
+sql.db_password: evol
+sql.db_database: evol
+sql.codepage:
+
+// MySQL Character SQL server
+char_server_ip: mariadb
+char_server_port: 3306
+char_server_id: evol
+char_server_pw: evol
+char_server_db: evol
+
+// MySQL Map SQL Server
+map_server_ip: mariadb
+map_server_port: 3306
+map_server_id: evol
+map_server_pw: evol
+map_server_db: evol
+
+// MySQL Log SQL Database
+log_db_ip: mariadb
+log_db_port: 3306
+log_db_id: evol
+log_db_pw: evol
+log_db_db: evol
+log_codepage:
diff --git a/.tools/conf/mariadb/log_conf.txt b/.tools/conf/mariadb/log_conf.txt
new file mode 100644
index 00000000..e69de29b
--- /dev/null
+++ b/.tools/conf/mariadb/log_conf.txt
diff --git a/.tools/conf/mariadb/login_conf.txt b/.tools/conf/mariadb/login_conf.txt
new file mode 100644
index 00000000..81ea90f4
--- /dev/null
+++ b/.tools/conf/mariadb/login_conf.txt
@@ -0,0 +1,2 @@
+// Login Server Port
+login_port: 6901
diff --git a/.tools/conf/mariadb/map_conf.txt b/.tools/conf/mariadb/map_conf.txt
new file mode 100644
index 00000000..cc912e2b
--- /dev/null
+++ b/.tools/conf/mariadb/map_conf.txt
@@ -0,0 +1,5 @@
+// Map Server Port
+map_port: 5122
+
+// Character Server Port
+char_port: 6122
diff --git a/.tools/conf/mariadb/msg_conf.txt b/.tools/conf/mariadb/msg_conf.txt
new file mode 100644
index 00000000..e69de29b
--- /dev/null
+++ b/.tools/conf/mariadb/msg_conf.txt
diff --git a/.tools/conf/mariadb/packet_conf.txt b/.tools/conf/mariadb/packet_conf.txt
new file mode 100644
index 00000000..e69de29b
--- /dev/null
+++ b/.tools/conf/mariadb/packet_conf.txt
diff --git a/.tools/conf/mariadb/script_conf.txt b/.tools/conf/mariadb/script_conf.txt
new file mode 100644
index 00000000..e69de29b
--- /dev/null
+++ b/.tools/conf/mariadb/script_conf.txt
diff --git a/.tools/conf/mysql/battle_conf.txt b/.tools/conf/mysql/battle_conf.txt
new file mode 100644
index 00000000..e69de29b
--- /dev/null
+++ b/.tools/conf/mysql/battle_conf.txt
diff --git a/.tools/conf/mysql/char_conf.txt b/.tools/conf/mysql/char_conf.txt
new file mode 100644
index 00000000..d6545a04
--- /dev/null
+++ b/.tools/conf/mysql/char_conf.txt
@@ -0,0 +1,8 @@
+min_char_class: 0
+max_char_class: 6
+
+// Login Server Port
+login_port: 6901
+
+// Character Server Port
+char_port: 6122
diff --git a/.tools/conf/mysql/inter_conf.txt b/.tools/conf/mysql/inter_conf.txt
new file mode 100644
index 00000000..0f4307af
--- /dev/null
+++ b/.tools/conf/mysql/inter_conf.txt
@@ -0,0 +1,36 @@
+// For IPs, ideally under linux, you want to use localhost instead of 127.0.0.1 
+// Under windows, you want to use 127.0.0.1.  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 127.0.0.1
+
+// Global SQL settings
+// overridden by local settings when the hostname is defined there
+// (currently only the login-server reads/obeys these settings)
+sql.db_hostname: mysql
+sql.db_port: 3306
+sql.db_username: evol
+sql.db_password: evol
+sql.db_database: evol
+sql.codepage:
+
+// MySQL Character SQL server
+char_server_ip: mysql
+char_server_port: 3306
+char_server_id: evol
+char_server_pw: evol
+char_server_db: evol
+
+// MySQL Map SQL Server
+map_server_ip: mysql
+map_server_port: 3306
+map_server_id: evol
+map_server_pw: evol
+map_server_db: evol
+
+// MySQL Log SQL Database
+log_db_ip: mysql
+log_db_port: 3306
+log_db_id: evol
+log_db_pw: evol
+log_db_db: evol
+log_codepage:
diff --git a/.tools/conf/mysql/log_conf.txt b/.tools/conf/mysql/log_conf.txt
new file mode 100644
index 00000000..e69de29b
--- /dev/null
+++ b/.tools/conf/mysql/log_conf.txt
diff --git a/.tools/conf/mysql/login_conf.txt b/.tools/conf/mysql/login_conf.txt
new file mode 100644
index 00000000..81ea90f4
--- /dev/null
+++ b/.tools/conf/mysql/login_conf.txt
@@ -0,0 +1,2 @@
+// Login Server Port
+login_port: 6901
diff --git a/.tools/conf/mysql/map_conf.txt b/.tools/conf/mysql/map_conf.txt
new file mode 100644
index 00000000..cc912e2b
--- /dev/null
+++ b/.tools/conf/mysql/map_conf.txt
@@ -0,0 +1,5 @@
+// Map Server Port
+map_port: 5122
+
+// Character Server Port
+char_port: 6122
diff --git a/.tools/conf/mysql/msg_conf.txt b/.tools/conf/mysql/msg_conf.txt
new file mode 100644
index 00000000..e69de29b
--- /dev/null
+++ b/.tools/conf/mysql/msg_conf.txt
diff --git a/.tools/conf/mysql/packet_conf.txt b/.tools/conf/mysql/packet_conf.txt
new file mode 100644
index 00000000..e69de29b
--- /dev/null
+++ b/.tools/conf/mysql/packet_conf.txt
diff --git a/.tools/conf/mysql/script_conf.txt b/.tools/conf/mysql/script_conf.txt
new file mode 100644
index 00000000..e69de29b
--- /dev/null
+++ b/.tools/conf/mysql/script_conf.txt
diff --git a/.tools/jobs/build.sh b/.tools/jobs/build.sh
new file mode 100755
index 00000000..45848754
--- /dev/null
+++ b/.tools/jobs/build.sh
@@ -0,0 +1,16 @@
+#!/bin/bash
+
+export CC=gcc-6
+export LOGFILE=gcc6.log
+
+source ./.tools/scripts/init.sh
+
+pwd
+cd ..
+
+build_init
+
+cd server-code
+check_error $?
+
+make_server "$1" "$2"
diff --git a/.tools/jobs/cloneall.sh b/.tools/jobs/cloneall.sh
new file mode 100755
index 00000000..79bb8d03
--- /dev/null
+++ b/.tools/jobs/cloneall.sh
@@ -0,0 +1,5 @@
+#!/bin/bash
+
+source ./.tools/scripts/init.sh
+
+do_init
diff --git a/.tools/jobs/clonedata.sh b/.tools/jobs/clonedata.sh
new file mode 100755
index 00000000..95fa77a5
--- /dev/null
+++ b/.tools/jobs/clonedata.sh
@@ -0,0 +1,5 @@
+#!/bin/bash
+
+source ./.tools/scripts/init.sh
+
+do_init_data
diff --git a/.tools/jobs/initdb.sh b/.tools/jobs/initdb.sh
new file mode 100755
index 00000000..d6c2480f
--- /dev/null
+++ b/.tools/jobs/initdb.sh
@@ -0,0 +1,23 @@
+#!/bin/bash
+
+source ./.tools/scripts/init.sh
+
+cd ../server-data
+
+export host=$1
+cd sql-files
+
+export CMD="mysql --force -u root -proot --host=${host}"
+check_error $?
+echo $CMD <./initremote.sql
+$CMD <./initremote.sql
+check_error $?
+
+export CMD="mysql -u evol -pevol --host=${host} evol"
+echo Creating tables...
+echo $CMD <main.sql
+$CMD <main.sql
+check_error $?
+echo $CMD <logs.sql
+$CMD <logs.sql
+check_error $?
diff --git a/.tools/jobs/install.sh b/.tools/jobs/install.sh
new file mode 100755
index 00000000..dd762e1a
--- /dev/null
+++ b/.tools/jobs/install.sh
@@ -0,0 +1,10 @@
+#!/bin/bash
+
+source ./.tools/scripts/init.sh
+
+update_repos
+aptget_update
+
+aptget_install $*
+
+do_init_data
diff --git a/.tools/jobs/runserver.sh b/.tools/jobs/runserver.sh
new file mode 100755
index 00000000..6bff48ab
--- /dev/null
+++ b/.tools/jobs/runserver.sh
@@ -0,0 +1,38 @@
+#!/bin/bash
+
+source ./.tools/scripts/init.sh
+
+function run_server {
+    echo "running: $1 --run-once $2"
+    $1 --run-once $2 2>runlog.txt
+    export errcode=$?
+    export teststr=$(cat runlog.txt)
+    if [[ -n "${teststr}" ]]; then
+        echo "Errors found in running server $1."
+        cat runlog.txt
+        echo "Errors found in running server $1."
+        exit 1
+    else
+        echo "No errors found for server $1."
+    fi
+    if [ ${errcode} -ne 0 ]; then
+        echo "server $1 terminated with exit code ${errcode}"
+        echo "Test failed"
+        exit 1
+    fi
+}
+
+do_init_tools
+init_configs $1
+
+cd server-data
+pwd
+ls -la
+
+run_server ./login-server
+run_server ./char-server
+
+ARGS="--load-script npc/dev/test.txt "
+ARGS="--load-plugin script_mapquit $ARGS --load-script npc/dev/ci_test.txt"
+
+run_server ./map-server "$ARGS"
diff --git a/.tools/scripts/init.sh b/.tools/scripts/init.sh
new file mode 100755
index 00000000..05dca3a8
--- /dev/null
+++ b/.tools/scripts/init.sh
@@ -0,0 +1,211 @@
+#!/bin/bash
+
+mkdir logs
+
+export dir=$(pwd)
+export ERRFILE=${dir}/logs/${LOGFILE}
+
+cat /etc/os-release
+
+rm ${ERRFILE}
+
+function check_error {
+    if [ "$1" != 0 ]; then
+        echo "Error $1"
+        exit $1
+    fi
+}
+
+function update_repos {
+    if [ "$CI_SERVER" == "" ];
+    then
+        return
+    fi
+
+    export DATA=$(cat /etc/resolv.conf|grep "nameserver 1.10.100.101")
+    if [ "$DATA" != "" ];
+    then
+        echo "Detected local runner"
+        sed -i 's!http://httpredir.debian.org/debian!http://1.10.100.103/debian!' /etc/apt/sources.list
+    else
+        echo "Detected non local runner"
+    fi
+}
+
+function gitclone1 {
+    echo git clone $2 $3
+    git clone $2 $3
+    if [ "$?" != 0 ]; then
+        echo git clone $1 $3
+        git clone $1 $3
+        return $?
+    fi
+    return $?
+}
+
+function gitclone {
+    export name1=$1/$2
+    export name2=${CI_BUILD_REPO##*@}
+    export name2=https://${name2%/*}/$2
+
+    gitclone1 "$name1" "$name2" $3
+    if [ "$?" != 0 ]; then
+        sleep 1s
+        gitclone1 "$name1" "$name2" $3
+        if [ "$?" != 0 ]; then
+            sleep 3s
+            gitclone1 "$name1" "$name2" $3
+            if [ "$?" != 0 ]; then
+                sleep 5s
+                gitclone1 "$name1" "$name2" $3
+            fi
+        fi
+    fi
+    check_error $?
+}
+
+function update_repos {
+    if [ "$CI_SERVER" == "" ];
+    then
+        return
+    fi
+
+    export DATA=$(cat /etc/resolv.conf|grep "nameserver 1.10.100.101")
+    if [ "$DATA" != "" ];
+    then
+        echo "Detected local runner"
+        sed -i 's!http://httpredir.debian.org/debian!http://1.10.100.103/debian!' /etc/apt/sources.list
+    else
+        echo "Detected non local runner"
+    fi
+}
+
+function aptget_update {
+    update_repos
+    apt-get update
+    if [ "$?" != 0 ]; then
+        sleep 1s
+        apt-get update
+        if [ "$?" != 0 ]; then
+            sleep 1s
+            apt-get update
+        fi
+    fi
+    check_error $?
+}
+
+function aptget_install {
+    apt-get -y -qq install $*
+    if [ "$?" != 0 ]; then
+        sleep 1s
+        apt-get -y -qq install $*
+        if [ "$?" != 0 ]; then
+            sleep 2s
+            apt-get -y -qq install $*
+        fi
+    fi
+    check_error $?
+}
+
+function make_server {
+    ls -la ../server-data
+    ls -la ../server-data/plugins
+    export CPPFLAGS="$CPPFLAGS -DI_AM_AWARE_OF_THE_RISK_AND_STILL_WANT_TO_RUN_HERCULES_AS_ROOT"
+    echo "autoreconf -i"
+    autoreconf -i
+    check_error $?
+    echo "./configure $*"
+    ./configure $1
+    export err="$?"
+    if [ "$err" != 0 ]; then
+        echo "Error $err"
+        echo cat config.log
+        cat config.log
+        exit $err
+    fi
+    echo "make -j2"
+    make -j2
+    check_error $?
+    echo "make -j2 plugin.script_mapquit"
+    make -j2 plugin.script_mapquit
+    check_error $?
+    make install
+    check_error $?
+
+    cd src/evol
+    echo "autoreconf -i"
+    mkdir m4
+    autoreconf -i
+    check_error $?
+    mkdir build
+    cd build
+    echo "../configure $2"
+    ../configure $2
+    check_error $?
+    echo "make -j2 V=0"
+    make -j2 V=0
+    check_error $?
+    cd ../../../..
+    ls -la server-data/plugins
+}
+
+function do_init_data {
+    mkdir shared
+    cd ..
+    rm -rf server-data
+    cp -r serverdata server-data
+    ls -la server-data
+    check_error $?
+}
+
+function do_init_tools {
+    cd ..
+    rm -rf tools
+    gitclone https://gitlab.com/evol evol-tools.git tools
+}
+
+function do_init {
+    do_init_data
+    rm -rf server-code
+    gitclone https://gitlab.com/evol hercules.git server-code
+    check_error $?
+    cd server-code/src
+    check_error $?
+    gitclone https://gitlab.com/evol evol-hercules.git evol
+    check_error $?
+    cd ../..
+    check_error $?
+    mkdir -p server-data/plugins
+}
+
+function build_init {
+    if [ "$CI_SERVER" == "" ];
+    then
+        return
+    fi
+    mkdir -p /local/bin
+    echo "#!/bin/bash" > /local/bin/id
+    echo "echo 1000" >> /local/bin/id
+    export PATH="/local/bin:$PATH"
+    chmod +x /local/bin/id
+    echo "fake id check"
+    id
+
+    cd server-code/src/evol
+    source tools/vars.sh
+    check_error $?
+    cd ../../..
+    check_error $?
+    echo $CC --version
+    $CC --version
+    check_error $?
+}
+
+function init_configs {
+    cd tools/localserver
+    ./installconfigs.sh
+    cd ../..
+    cp server-data/.tools/conf/$1/* server-data/conf/import/
+    ls -la server-data/conf/import
+    cat server-data/conf/import/inter_conf.txt
+}
diff --git a/db/constants.conf b/db/constants.conf
index 9549f3d7..a4224aa9 100644
--- a/db/constants.conf
+++ b/db/constants.conf
@@ -3607,6 +3607,9 @@ constants_db: {
 	SEX_FEMALE:	0
 	SEX_MALE:	1
 
+    // for disable warning
+    ITMCHAIN_ORE: 1
+
 /* ==================================================== */
     /**  evol constants **/
 
diff --git a/sql-files/initremote.sql b/sql-files/initremote.sql
new file mode 100644
index 00000000..fa87edb1
--- /dev/null
+++ b/sql-files/initremote.sql
@@ -0,0 +1,4 @@
+CREATE DATABASE evol;
+CREATE USER 'evol'@'%' IDENTIFIED BY 'evol';
+GRANT ALTER,CREATE,SELECT,INSERT,UPDATE,DELETE,DROP,INDEX ON `evol`.* TO 'evol'@'%';
+FLUSH PRIVILEGES;
diff --git a/sql-files/logs.sql b/sql-files/logs.sql
index ab722a2f..71c1ce86 100644
--- a/sql-files/logs.sql
+++ b/sql-files/logs.sql
@@ -43,7 +43,7 @@
 
 CREATE TABLE IF NOT EXISTS `atcommandlog` (
   `atcommand_id` MEDIUMINT(9) UNSIGNED NOT NULL AUTO_INCREMENT,
-  `atcommand_date` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
+  `atcommand_date` DATETIME NULL,
   `account_id` INT(11) UNSIGNED NOT NULL DEFAULT '0',
   `char_id` INT(11) UNSIGNED NOT NULL DEFAULT '0',
   `char_name` VARCHAR(25) NOT NULL DEFAULT '',
@@ -60,7 +60,7 @@ CREATE TABLE IF NOT EXISTS `atcommandlog` (
 
 CREATE TABLE IF NOT EXISTS `branchlog` (
   `branch_id` MEDIUMINT(9) UNSIGNED NOT NULL AUTO_INCREMENT,
-  `branch_date` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
+  `branch_date` DATETIME NULL,
   `account_id` INT(11) NOT NULL DEFAULT '0',
   `char_id` INT(11) NOT NULL DEFAULT '0',
   `char_name` VARCHAR(25) NOT NULL DEFAULT '',
@@ -76,7 +76,7 @@ CREATE TABLE IF NOT EXISTS `branchlog` (
 
 CREATE TABLE IF NOT EXISTS `chatlog` (
   `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
-  `time` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
+  `time` DATETIME NULL,
   `type` ENUM('O','W','P','G','M') NOT NULL DEFAULT 'O',
   `type_id` INT(11) NOT NULL DEFAULT '0',
   `src_charid` INT(11) NOT NULL DEFAULT '0',
@@ -96,7 +96,7 @@ CREATE TABLE IF NOT EXISTS `chatlog` (
 --
 
 CREATE TABLE IF NOT EXISTS `loginlog` (
-  `time` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
+  `time` DATETIME NULL,
   `ip` VARCHAR(15) NOT NULL DEFAULT '',
   `user` VARCHAR(23) NOT NULL DEFAULT '',
   `rcode` TINYINT(4) NOT NULL DEFAULT '0',
@@ -110,7 +110,7 @@ CREATE TABLE IF NOT EXISTS `loginlog` (
 
 CREATE TABLE IF NOT EXISTS `mvplog` (
   `mvp_id` MEDIUMINT(9) UNSIGNED NOT NULL AUTO_INCREMENT,
-  `mvp_date` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
+  `mvp_date` DATETIME NULL,
   `kill_char_id` INT(11) NOT NULL DEFAULT '0',
   `monster_id` SMALLINT(6) NOT NULL DEFAULT '0',
   `prize` INT(11) NOT NULL DEFAULT '0',
@@ -125,7 +125,7 @@ CREATE TABLE IF NOT EXISTS `mvplog` (
 
 CREATE TABLE IF NOT EXISTS `npclog` (
   `npc_id` MEDIUMINT(9) UNSIGNED NOT NULL AUTO_INCREMENT,
-  `npc_date` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
+  `npc_date` DATETIME NULL,
   `account_id` INT(11) UNSIGNED NOT NULL DEFAULT '0',
   `char_id` INT(11) UNSIGNED NOT NULL DEFAULT '0',
   `char_name` VARCHAR(25) NOT NULL DEFAULT '',
@@ -142,7 +142,7 @@ CREATE TABLE IF NOT EXISTS `npclog` (
 
 CREATE TABLE IF NOT EXISTS `picklog` (
   `id` INT(11) NOT NULL AUTO_INCREMENT,
-  `time` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
+  `time` DATETIME 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','K','Y','Z','W','Q','J','H','@','0','1','2') NOT NULL DEFAULT 'P',
   `nameid` INT(11) NOT NULL DEFAULT '0',
@@ -164,7 +164,7 @@ CREATE TABLE IF NOT EXISTS `picklog` (
 
 CREATE TABLE IF NOT EXISTS `zenylog` (
   `id` INT(11) NOT NULL AUTO_INCREMENT,
-  `time` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
+  `time` DATETIME NULL,
   `char_id` INT(11) NOT NULL DEFAULT '0',
   `src_id` INT(11) NOT NULL DEFAULT '0',
   `type` ENUM('T','V','P','M','S','N','D','C','A','E','I','B') NOT NULL DEFAULT 'S',
diff --git a/sql-files/main.sql b/sql-files/main.sql
index e2717cdc..b70b5e2c 100644
--- a/sql-files/main.sql
+++ b/sql-files/main.sql
@@ -236,7 +236,7 @@ CREATE TABLE IF NOT EXISTS `char_reg_str_db` (
 --
 
 CREATE TABLE IF NOT EXISTS `charlog` (
-  `time` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
+  `time` DATETIME NOT NULL,
   `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',
@@ -511,7 +511,7 @@ CREATE TABLE IF NOT EXISTS `homunculus` (
 --
 
 CREATE TABLE IF NOT EXISTS `interlog` (
-  `time` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
+  `time` DATETIME NULL,
   `log` VARCHAR(255) NOT NULL DEFAULT ''
 ) ENGINE=MyISAM; 
 
@@ -546,8 +546,8 @@ CREATE TABLE IF NOT EXISTS `inventory` (
 
 CREATE TABLE IF NOT EXISTS `ipbanlist` (
   `list` VARCHAR(255) NOT NULL DEFAULT '',
-  `btime` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
-  `rtime` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
+  `btime` DATETIME NULL,
+  `rtime` DATETIME NULL,
   `reason` VARCHAR(255) NOT NULL DEFAULT '',
   KEY (`list`)
 ) ENGINE=MyISAM;
@@ -567,9 +567,9 @@ CREATE TABLE IF NOT EXISTS `login` (
   `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',
+  `lastlogin` DATETIME NULL,
   `last_ip` VARCHAR(100) NOT NULL DEFAULT '',
-  `birthdate` DATE NOT NULL DEFAULT '0000-00-00',
+  `birthdate` DATE NULL,
   `character_slots` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
   `pincode` VARCHAR(4) NOT NULL DEFAULT '',
   `pincode_change` INT(11) UNSIGNED NOT NULL DEFAULT '0',