TFS 1.X+ [Error - mysql_real_query] Query: INSERT INTO `players_online` VALUES (1)

[ForgottenNot]

I use nekiro's tfs 1.5 with multiworld system i get this error while logging in, how do i remove or fix this error console?

>> Forgotten Server Online!
GM Ratx has logged in.
[Error - mysql_real_query] Query: INSERT INTO `players_online` VALUES (1)
Message: Column count doesn't match value count at row 1
GM Ratx has logged out.
Blex has logged in.
[Error - mysql_real_query] Query: INSERT INTO `players_online` VALUES (8)
Message: Column count doesn't match value count at row 1
Blex has logged out.
Blex has logged in.
[Error - mysql_real_query] Query: INSERT INTO `players_online` VALUES (8)
Message: Column count doesn't match value count at row 1
Blex has logged out.

 

[Algorithm]

create new column in database
ALTER TABLE players_online add column count int(11) not null default 0 (might include syntax error lazy to check)

 

[lursky]

It's not about column named count.

The error “Column count doesn't match value count at row 1” means
that the number of columns does not match the number of rows in your INSERT statement.

Check your table columns, it should have only one column player_id, easiest to do is to remove it and re-add:

CREATE TABLE IF NOT EXISTS `players_online` (
  `player_id` int NOT NULL,
  PRIMARY KEY (`player_id`)
) ENGINE=MEMORY DEFAULT CHARACTER SET=utf8;

 

[slaw]

His players_online probably contains 2 columns, because he uses multiworld.

So its probably player_id, and world_id.

To fix the error, you need to find the query in your sources (lua or c++), and add world_id to it.

 

[lursky]

Adjust these queries

TFS-1.5-Downgrades/src/iologindata.cpp at fc9157b919405ecde6f30e471255be4e2746884e · nekiro/TFS-1.5-Downgrades

Alternative forgottenserver versions for older protocols support - nekiro/TFS-1.5-Downgrades

github.com

uint16_t worldId = g_gameworld.getWorldId();
if (login) {
    Database::getInstance().executeQuery(fmt::format("INSERT INTO `players_online` VALUES ({:d}, {:d})", guid, worldId));
} else {
    Database::getInstance().executeQuery(fmt::format("DELETE FROM `players_online` WHERE `player_id` = {:d} AND `world_id` = {:d}", guid, worldId));
}

TFS-1.5-Downgrades/data/globalevents/scripts/startup.lua at 8.60 · nekiro/TFS-1.5-Downgrades

Alternative forgottenserver versions for older protocols support - nekiro/TFS-1.5-Downgrades

github.com

local worldId = configManager.getNumber(configKeys.WORLD_ID)
db.query('DELETE FROM `players_online` WHERE `world_id` = '.. worldId)

 

[Algorithm]

His players_online probably contains 2 columns, because he uses multiworld.

So its probably player_id, and world_id.

To fix the error, you need to find the query in your sources (lua or c++), and add world_id to it.

Message: Column count doesn't match value count at row 1

 

[ForgottenNot]

Adjust these queries

TFS-1.5-Downgrades/src/iologindata.cpp at fc9157b919405ecde6f30e471255be4e2746884e · nekiro/TFS-1.5-Downgrades

Alternative forgottenserver versions for older protocols support - nekiro/TFS-1.5-Downgrades

github.com

uint16_t worldId = g_gameworld.getWorldId();
if (login) {
    Database::getInstance().executeQuery(fmt::format("INSERT INTO `players_online` VALUES ({:d}, {:d})", guid, worldId));
} else {
    Database::getInstance().executeQuery(fmt::format("DELETE FROM `players_online` WHERE `player_id` = {:d} AND `world_id` = {:d}", guid, worldId));
}

TFS-1.5-Downgrades/data/globalevents/scripts/startup.lua at 8.60 · nekiro/TFS-1.5-Downgrades

Alternative forgottenserver versions for older protocols support - nekiro/TFS-1.5-Downgrades

github.com

local worldId = configManager.getNumber(configKeys.WORLD_ID)
db.query('DELETE FROM `players_online` WHERE `world_id` = '.. worldId)

Hi, thank you for your quick reply @lursky i'm getting a new error on login now.

>> Forgotten Server Online!

multiworld is definedGM Ratx has logged in.
[Error - mysql_real_query] Query:
Message: Query was empty

some weird thing is tha twith your changes i cannot use this piece of code

output->addString(g_gameworld.getWorldIp(worldId));

located in:

void ProtocolLogin::getCharacterList(const std::string& accountName, const std::string& password, const std::string&, uint16_t version)

and had to replace it with a bypass? that don't know if it's working with the multiworld system but im able to log in

    output->add<uint32_t>(inet_addr(g_config.getString(ConfigManager::IP).c_str()));

because with this code i get this error

Connecting to: 127.0.0.1:7171
ERROR: protected lua call failed: C++ call failed: InputMessage eof reached
stack traceback:
    [builtin#146]: at 0x00853df0
    [C]: in function 'getString'
    /modules/gamelib/protocollogin.lua:254: in function 'parseCharacterList'
    /modules/gamelib/protocollogin.lua:181: in function </modules/gamelib/protocollogin.lua:163>

this is my full code

void ProtocolLogin::getCharacterList(const std::string& accountName, const std::string& password, const std::string&, uint16_t version)
{
    Account account;
    if (!IOLoginData::loginserverAuthentication(accountName, password, account)) {
        disconnectClient("Account name or password is not correct.", version);
        return;
    }


    auto output = OutputMessagePool::getOutputMessage();

    //Update premium days
    //Game::updatePremium(account);

    const std::string& motd = g_config.getString(ConfigManager::MOTD);
    if (!motd.empty()) {
        //Add MOTD
        output->addByte(0x14);

        std::ostringstream ss;
        ss << g_game.getMotdNum() << "\n" << motd;
        output->addString(ss.str());
    }

    output->addByte(0x64);

    uint8_t size = std::min<size_t>(std::numeric_limits<uint8_t>::max(), account.characters.size());
    output->addByte(size);

#ifdef MULTIWORLD_SYSTEM
    //Add char list
    printf("multiworld is defined");

    for (uint8_t i = 0; i < size; i++) {
        output->addString(account.characters[i].name);

        const uint16_t worldId = account.characters[i].worldid;

        output->addString(g_gameworld.getWorldName(worldId));
        //world envia ip 255.255.255
        //output->add<uint32_t>(inet_addr(g_gameworld.getWorldIp(worldId)));
        //output->addString(g_gameworld.getWorldIp(worldId));//supuesto fix
        //output->add<uint16_t>(g_gameworld.getWorldPort(worldId));

        //sin multiworld bypass pedorro
        output->add<uint32_t>(inet_addr(g_config.getString(ConfigManager::IP).c_str()));
        output->add<uint16_t>(g_gameworld.getWorldPort(worldId));
    }
#else
    printf("multiworld is NOT defined");

    //Add char list
    for (uint8_t i = 0; i < size; i++) {
        output->addString(account.characters[i]);
        output->addString(g_config.getString(ConfigManager::SERVER_NAME));
        output->add<uint32_t>(inet_addr(g_config.getString(ConfigManager::IP).c_str()));
        output->add<uint16_t>(g_config.getNumber(ConfigManager::GAME_PORT));
    }
#endif

    // Add premium days
    output->addByte(0);
    if (g_config.getBoolean(ConfigManager::FREE_PREMIUM)) {
        output->addByte(1);
        output->add<uint32_t>(0);
    }
    else {
        output->addByte(account.premiumEndsAt > time(nullptr) ? 1 : 0);
        //output->add<uint32_t>(account.premiumEndsAt);
        output->add<uint16_t>(std::max<time_t>(0, account.premiumEndsAt - time(nullptr)) / 86400);
    }

    send(output);

    disconnect();
}

this is how updateonidlestatus looks now

void IOLoginData::updateOnlineStatus(uint32_t guid, bool login)
{
    if (g_config.getBoolean(ConfigManager::ALLOW_CLONES)) {
        return;
    }

    std::ostringstream query;
    uint16_t worldId = g_gameworld.getWorldId();
    if (login) {
        Database::getInstance().executeQuery(fmt::format("INSERT INTO `players_online` VALUES ({:d}, {:d})", guid, worldId));
    }
    else {
        Database::getInstance().executeQuery(fmt::format("DELETE FROM `players_online` WHERE `player_id` = {:d} AND `world_id` = {:d}", guid, worldId));
    }
    Database::getInstance().executeQuery(query.str());
}

startup.lua

-- Move expired bans to ban history
    local resultId = db.storeQuery("SELECT * FROM `account_bans` WHERE `expires_at` != 0 AND `expires_at` <= " .. os.time())
    if resultId ~= false then
        repeat
            local accountId = result.getNumber(resultId, "account_id")
            db.asyncQuery("INSERT INTO `account_ban_history` (`account_id`, `reason`, `banned_at`, `expired_at`, `banned_by`) VALUES (" .. accountId .. ", " .. db.escapeString(result.getString(resultId, "reason")) .. ", " .. result.getNumber(resultId, "banned_at") .. ", " .. result.getNumber(resultId, "expires_at") .. ", " .. result.getNumber(resultId, "banned_by") .. ")")
            db.asyncQuery("DELETE FROM `account_bans` WHERE `account_id` = " .. accountId)
        until not result.next(resultId)
        result.free(resultId)
    end
function onStartup()

    local worldId = configManager.getNumber(configKeys.WORLD_ID)
db.query('DELETE FROM `players_online` WHERE `world_id` = '.. worldId)
    db.asyncQuery("DELETE FROM `guild_wars` WHERE `status` = 0")
    db.asyncQuery("DELETE FROM `players` WHERE `deletion` != 0 AND `deletion` < " .. os.time())
    db.asyncQuery("DELETE FROM `ip_bans` WHERE `expires_at` != 0 AND `expires_at` <= " .. os.time())
    db.asyncQuery("DELETE FROM `market_history` WHERE `inserted` <= " .. (os.time() - configManager.getNumber(configKeys.MARKET_OFFER_DURATION)))

 

[ForgottenNot]

up