[Help]Problem with AuthServer

[kyle191]

Hello there, I'm using the hasan source for patch 5375. I have been messing around with the database, I wanted to change where the Username, Password, IP, Email and LastCheck to a different Database_table. The old table was called "accounts" the new is, "user".

I have edited the AuthServer and GameServer projects (AccountTable.cs). I'm sure I've done this correct, but I am receiving an error trying to login to the server on my AuthServer Command window. This is the error;

MySql.Data.Types.MySqlConversionException: Unable to convert MySQL date/time val
ue to System.DateTime
   at MySql.Data.Types.MySqlDateTime.GetDateTime()
   at MySql.Data.MySqlClient.MySqlDataReader.GetValue(Int32 i)
   at MySql.Data.MySqlClient.MySqlDataReader.GetValues(Object[] values)
   at System.Data.ProviderBase.DataReaderContainer.CommonLanguageSubsetDataReade
r.GetValues(Object[] values)
   at System.Data.ProviderBase.SchemaMapping.LoadDataRow()
   at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
   at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable d
atatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, In
t32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
   at System.Data.Common.DataAdapter.Fill(DataSet dataSet, String srcTable, IDat
aReader dataReader, Int32 startRecord, Int32 maxRecords)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[]
 datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand co
mmand, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord,
Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
   at AuthServer.Database.MySqlReader.TryFill(MySqlCommand command) in C:\Docume
nts and Settings\Kyle Lewis\Desktop\Conquer Online P-Server\Sources\hasan\AuthSe
rver\Database\MYSQL\MySqlReader.cs:line 42
[Auth-Socket] Incomming connection to Test and password

Please could someone tell me why the database was unable to convert date and time(LastCheck), I've never came across an error like this before, So I would like to know how to fix it.

Thanks.

Edit: I've also changed, the name of the old fields, Username is now username(lowercaps), this is the same with most apart from LastActivity. I've also edited this on my project.

[Arco.]

My solution to that was to save it as a string, and read it off as a string and convert it to datetime.

[12k]

Originally Posted by Arco.

My solution to that was to save it as a string, and read it off as a string and convert it to datetime.

yupp, thats about the only way to do that. Unless you wanted to use a time in the database instead of a datetime.

[I don't have a username]

Or binary.

long bintime = DateTime.Now.ToBinary();
DateTime time = DateTime.FromBinary(bintime);

[kyle191]

Thanks for the replies people, I just really don't understand why it's got to have a fit when I just change the path and a few names of the accounts table. Isn't there a much simpler solution. Maybe I set the LastCheck database field wrong, even though I did highlight the field and copy, then pasted into my new table(using Navicat).


PS: to make it more understandable for you guys, I'm trying to combine the conquer accounts table with a forums user table, therefore when they signup to my forum, this would also sign them up to the game.

This is my AccountTable.cs.

using System;
using System.IO;
using System.Text;

namespace AuthServer.Database
{
    public class AccountTable
    {
        public enum AccountState : byte
        { ProjectManager = 4, GameMaster = 3, Player = 2, Banned = 1, DoesntExist = 0 }
        public string Username;
        public string Password;
        public string Email;
        public string IP;
        public DateTime LastCheck;
        public AccountState State;
        public uint EntityID;
        public uint AccountID;
        private bool exists = false;
        public AccountTable(string username)
        {
            this.AccountID = 0;
            this.Username = username;
            this.Password = "";
            this.IP = "";
            this.LastCheck = DateTime.Now;
            this.State = AccountState.DoesntExist;
            this.EntityID = 0;
            MySqlCommand cmd = new MySqlCommand(MySqlCommandType.SELECT);
            cmd.Select("user").Where("username", username);
            MySqlReader r = new MySqlReader(cmd );
            if (r.Read())
            {
                exists = true;
                this.AccountID = r.ReadUInt32("userid");
                this.Password = r.ReadString("password");
                this.IP = r.ReadString("ipaddress");
                this.EntityID = r.ReadUInt32("EntityID");
                this.LastCheck = DateTime.FromBinary(r.ReadInt64("LastCheck"));
                this.State = (AccountState)r.ReadByte("State");
                this.Email = r.ReadString("email");
            }
        }

        public void Save()
        {
            if (exists)
            {
                MySqlCommand cmd = new MySqlCommand(MySqlCommandType.UPDATE);
                cmd.Update("user").Set("password", Password).Set("ipaddress", IP).Set("EntityID", EntityID).Set("LastCheck", (ulong)DateTime.Now.ToBinary()).Set("State", (byte)State).Where("username", Username).Execute();
            }
            else
            {
                MySqlCommand cmd = new MySqlCommand(MySqlCommandType.INSERT);
                cmd.Insert("user").Insert("username", Username).Insert("password", Password).Insert("State", (byte)State).Execute();
            }
        }
    }
}

[12k]

Its just the way that your mysql reader works. Its trying to read it as a datetime before you even use the r.readint64, and is causing the error. If your using the typical mysqlreader most servers use, its most likely in the TryFill method. (From what I can see)

[kyle191]

I've figured out what's making me have this error. In the same Table as my conquer user, LastCheck etc I have vBulletin user tables. I have connected the users and tested them but there are two fields that are giving me this error, they are;
birthday_search Type=date Default=0000-00-00
passworddate Type=date Default=0000-00-00

I have tested if I remove those fields, conquer works but the forum obviously doesn't. How can I change this?

LastCheck Type=bigint Default=NULL Unsigned.

So basically, it's these 2 type"date" that are causing the error "Unable to convert MYSQL date/time"

[I don't have a username]

Why do you even have your forum and your conquerserver in the same db?

[kyle191]

So when people create a user on the forum it will create their game login.


Basically all I needed to do is change a few fields where conquer pulls data out of the db. That has no problem, but as I said above, with 2 db fields which are type data, seems to mess stop me from logging into the gameserver giving me an error on my AuthServer command window, Stating "Unable to convert MYSQL date/time".


Please help me lol, it's a weird problem.

[12k]

The main reason this happens, is because it cant convert a empty datetime (00:00:00) etc, to a datetime. So if you simply want to ignore those empty datetimes, add this to the end of your mysql connection string:

Allow Zero Datetime=true;

[kyle191]

Originally Posted by 12k

The main reason this happens, is because it cant convert a empty datetime (00:00:00) etc, to a datetime. So if you simply want to ignore those empty datetimes, add this to the end of your mysql connection string:

Thanks, that makes more sense now, only thing that doesn't is I'm not sure where to add it, this is my AuthServer Code;

using System;
using System.IO;
using System.Text;

namespace AuthServer.Database
{
    public class AccountTable
    {
        public enum AccountState : byte
        { ProjectManager = 4, GameMaster = 3, Player = 2, Banned = 1, DoesntExist = 0 }
        public string Username;
        public string Password;
        public string Email;
        public string IP;
        public DateTime LastCheck;
        public AccountState State;
        public uint EntityID;
        public uint AccountID;
        private bool exists = false;
        public AccountTable(string username)
        {
            this.AccountID = 0;
            this.Username = username;
            this.Password = "";
            this.IP = "";
            this.LastCheck = DateTime.Now;
            this.State = AccountState.DoesntExist;
            this.EntityID = 0;
            MySqlCommand cmd = new MySqlCommand(MySqlCommandType.SELECT);
            cmd.Select("user").Where("username", username);
            MySqlReader r = new MySqlReader(cmd );
            if (r.Read())
            {
                exists = true;
                this.AccountID = r.ReadUInt32("userid");
                this.Password = r.ReadString("password");
                this.IP = r.ReadString("ipaddress");
                this.EntityID = r.ReadUInt32("EntityID");
                this.LastCheck = DateTime.FromBinary(r.ReadInt64("LastCheck"));
                this.State = (AccountState)r.ReadByte("State");
                this.Email = r.ReadString("email");
                
            }
        }

        public void Save()
        {
            if (exists)
            {
                MySqlCommand cmd = new MySqlCommand(MySqlCommandType.UPDATE);
                cmd.Update("user").Set("password", Password).Set("ipaddress", IP).Set("EntityID", EntityID).Set("LastCheck", (ulong)DateTime.Now.ToBinary()).Set("State", (byte)State).Where("username", Username).Execute();
            }
            else
            {
                MySqlCommand cmd = new MySqlCommand(MySqlCommandType.INSERT);
                cmd.Insert("user").Insert("username", Username).Insert("password", Password).Insert("State", (byte)State).Execute();
            }
        }
    }
}

PS: the way my LastCheck field is set up, has like 19 different numbers if you have a successful login.

[12k]

You need to add it to your connection string... So use control + F, make sure the bottom box is set to Entire Solution. Then search for Server=127.0.0.1, or Server=localhost. Put it at the end of that string.

[Korvacs]

Provided that your database is hosted on the same machine as your server of course, if not then search for "= new MySqlConnection(" and then it'll either be there or the string will be referenced there.

[12k]

Originally Posted by Korvacs

Provided that your database is hosted on the same machine as your server of course, if not then search for "= new MySqlConnection(" and then it'll either be there or the string will be referenced there.

That may or may not work. Because some servers use it as the following to prevent it from conflicting with the name of the variable inside the database class.

return new MySql.Data.MySqlClient.MySqlConnection("Server=bla h blah");

So you could just search for:

MySqlConnection("Server=

[Korvacs]

Cant believe you felt the need to comment on that, well considering the fact that people may also not just have the string, you should probably just search for this:

MySqlConnection(