AAC ZnoteACC account manager accounts aren't registered on znote_account

[Lurk]

accounts that are created via account manager doesn't have znote_accounts registers which doesn't allow them to buy points, any idea how to fix this? I really want to keep my account manager

 

[Rombadr]

If you really want to keep the account manager, I believe you'll have to change the sources to insert the data into the znote tables

 

[Lurk]

hmm ok, do you know in which file I need to make the changes?

 

[Rombadr]

Could you show us where you got the sources from?

Edit: look for "createAccount" in iologindata.cpp, you'll have to check what gets inserted when you create an account using Znote AAC and do the same queries in the createAccount function

 

[Lurk]

I'm using Fir3element/3777 (https://github.com/Fir3element/3777) I'll take a look at iologindata and then edit this comment

edit:
znote creates like this:

mysql_insert("INSERT INTO `znote_accounts` (`account_id`, `ip`, `created`, `active`, `active_email`, `activekey`, `flag`) VALUES ('$account_id', '$ip', '$created', '$active', '0', '$activeKey', '$flag')");

I was thinking of this but I don't believe it is going to work... maybe a check on the player's first login to then run an sql query to populate his znote_account would be easier?

uint64_t IOLoginData::createAccount(std::string name, std::string password)
{
    _encrypt(password, false);

    Database* db = Database::getInstance();
    DBQuery query;

    query << "INSERT INTO `accounts` (`id`, `name`, `password`) VALUES (NULL, " << db->escapeString(name) << ", " << db->escapeString(password) << ")";
    query << "INSERT INTO `znote_accounts` ((`id`, `id`, `0`, `0`, `0`, `0`, `1`, `0`, `0`,`0`,`0`) <<";
    if(!db->query(query.str()))
        return 0;

    return db->getLastInsertId();
}

what do you think? here's the table

CREATE TABLE IF NOT EXISTS `znote_accounts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account_id` int(11) NOT NULL,
  `ip` bigint(20) UNSIGNED NOT NULL,
  `created` int(10) NOT NULL,
  `points` int(10) DEFAULT 0,
  `cooldown` int(10) DEFAULT 0,
  `active` tinyint(4) NOT NULL DEFAULT '0',
  `active_email` tinyint(4) NOT NULL DEFAULT '0',
  `activekey` int(11) NOT NULL DEFAULT '0',
  `flag` varchar(20) NOT NULL,
  `secret` char(16) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Do you know how I can get a player's account id with a lua script? this way I can populate his znote_accounts

 

[Rombadr]

Note: I don't really know much C++, but I believe it'd be something like this

uint64_t IOLoginData::createAccount(std::string name, std::string password)
{
    _encrypt(password, false);

    Database* db = Database::getInstance();
    DBQuery query;
    query << "INSERT INTO `accounts` (`id`, `name`, `password`) VALUES (NULL, " << db->escapeString(name) << ", " << db->escapeString(password) << ")";
    if(!db->query(query.str()))
        return 0;

    uint64_t acc_id = db->getLastInsertId();

    DBQuery z_query;
    z_query << "INSERT INTO `znote_accounts` ( `account_id`, `ip`, `created`, `flag`) VALUES (" << acc_id << ", 0, 0, '')";
    db->query(z_query.str());
    return acc_id;
}

 

[Lurk]

I get this error wwhen creating the account

[20:40:39.003] mysql_real_query(): INSERT INTO `accounts` (`id`, `name`, `password`) VALUES (NULL, 'Knnnsd', '8817b72941bea095086cbf7549d30beecc43a78c')INSERT INTO `znote_accounts` ((`id`, `id`, `0`, `0`, `0`, `0`, `1`, `0`, `0`,`0`,`0`) << - MYSQL ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INSERT INTO `znote_accounts` ((`id`, `id`, `0`, `0`, `0`, `0`, `1`, `0`, `0`,...' at line 1 (1064)

and the account manager answers "Your account could not be created, please try again" after I say which account name I want

Do you know how can I get the accountid by lua script? something like getAccountId(cid) so I can set the values to znote_account on the players first login

 

[Rombadr]

Looks like you did not copy my code.. show me what you have in that function

 

[Lurk]

yep that was my bad, I changed it but didn't save before compiling . I did it right this time but I'm getting this error

[20:54:52.747] mysql_real_query(): INSERT INTO `accounts` (`id`, `name`, `password`) VALUES (NULL, 'Umsijsnjmd', 'bfbd330b1dac02060a6fd553d0f2f21b62792495') - MYSQL ERROR: Duplicate entry 'Umsijsnjmd' for key 'name' (1062)

I can fully create the account and the character, log in and do stuff but znote_accounts is still empty

 

[Rombadr]

I edited my post, try copying it again.

 

[Lurk]

Nice, working like a charm. Maybe @Znote can add this as a tutorial for tfs 0.4?
still if you know how to get a player's account id by cid or guid it would help a lot for a script I have here

 

[Rombadr]

Probably "getPlayerAccountId(cid)"

 

[Znote]

Note: I don't really know much C++, but I believe it'd be something like this

uint64_t IOLoginData::createAccount(std::string name, std::string password)
{
    _encrypt(password, false);

    Database* db = Database::getInstance();
    DBQuery query;
    query << "INSERT INTO `accounts` (`id`, `name`, `password`) VALUES (NULL, " << db->escapeString(name) << ", " << db->escapeString(password) << ")";
    if(!db->query(query.str()))
        return 0;

    uint64_t acc_id = db->getLastInsertId();

    DBQuery z_query;
    z_query << "INSERT INTO `znote_accounts` ( `account_id`, `ip`, `created`, `flag`) VALUES (" << acc_id << ", 0, 0, '')";
    db->query(z_query.str());
    return acc_id;
}

Players probably has to be fixed as well for highscores/characterprofile to work properly on Znote AAC:

ZnoteAAC/engine/function/users.php at master · Znote/ZnoteAAC

Developement repository for the Znote AAC project. A website portal to represent and manage your Open Tibia server. - Znote/ZnoteAAC

github.com

Here are some queries that fix compatibility on accounts/players that wasn't created by Znote AAC, another alternative might be to just run these at the end of your account/player creation and just let the mysql server fix this instead of doing lots source changes:

-- Convert existing accounts in database to be Znote AAC compatible
INSERT INTO `znote_accounts` (`account_id`, `ip`, `created`, `flag`)
SELECT 
  `a`.`id` AS `account_id`, 
  0 AS `ip`, 
  UNIX_TIMESTAMP(CURDATE()) AS `created`, 
  '' AS `flag`
FROM `accounts` AS `a`
LEFT JOIN `znote_accounts` AS `z`
  ON `a`.`id` = `z`.`account_id` 
WHERE `z`.`created` IS NULL;

-- Delete duplicate account records
DELETE `d` FROM `znote_accounts` AS `d`
INNER JOIN (
  SELECT `i`.`account_id`, 
  MAX(`i`.`id`) AS `retain`
  FROM `znote_accounts` AS `i` 
  GROUP BY `i`.`account_id` 
  HAVING COUNT(`i`.`id`) > 1
) AS `x`
  ON `d`.`account_id` = `x`.`account_id`
  AND `d`.`id` != `x`.`retain`;

-- Convert existing players in database to be Znote AAC compatible
INSERT INTO `znote_players` (`player_id`, `created`, `hide_char`, `comment`)
SELECT 
  `p`.`id` AS `player_id`, 
  UNIX_TIMESTAMP(CURDATE()) AS `created`, 
  0 AS `hide_char`, 
  '' AS `comment`
FROM `players` AS `p`
LEFT JOIN `znote_players` AS `z`
  ON `p`.`id` = `z`.`player_id` 
WHERE `z`.`created` IS NULL;


-- Delete duplicate player records
DELETE `d` FROM `znote_players` AS `d`
INNER JOIN (
  SELECT `i`.`player_id`, 
  MAX(`i`.`id`) AS `retain`
  FROM `znote_players` AS `i` 
  GROUP BY `i`.`player_id` 
  HAVING COUNT(`i`.`id`) > 1
) AS `x`
  ON `d`.`player_id` = `x`.`player_id`
  AND `d`.`id` != `x`.`retain`;

 

[Lurk]

where should I add this? source? I think changing this to lua and running on the player's first login for his accountId or maybe running this once a day would work too