Anope IRC Services

Please login or register.

Login with username, password and session length
Advanced search  

News:

Pages: [1]   Go Down

Author Topic: [MySQL] irc2sql anope_server not proper updating?  (Read 4544 times)

0 Members and 1 Guest are viewing this topic.

DJFelipe

  • Anope User
  • Offline Offline
  • Posts: 8
[MySQL] irc2sql anope_server not proper updating?
« on: October 20, 2020, 11:02:23 PM »

Hey there!

since the 2.X Version i was wondering, why my magIRC always shows servers as offline after a netsplit and reconnect.. So today i was digging around in the File 'irc2sql.cpp' at void 'IRC2SQL::OnNewServer(Server *server)'
Are there parameters missing? I dont see any parsing of the 'online' or 'link_time' values? If i get the code right, only a new server will be flagged as online but after that, it will stay offline forever?

Code: [Select]
void IRC2SQL::OnNewServer(Server *server)
{
        query = "INSERT DELAYED INTO `" + prefix + "server` (name, hops, comment, link_time, online, ulined) "
                "VALUES (@name@, @hops@, @comment@, now(), 'Y', @ulined@) "
                "ON DUPLICATE KEY UPDATE name=VALUES(name), hops=VALUES(hops), comment=VALUES(comment), "
                        "link_time=VALUES(link_time), online=VALUES(online), ulined=(ulined)";
        query.SetValue("name", server->GetName());
        query.SetValue("hops", server->GetHops());
        query.SetValue("comment", server->GetDescription());
        query.SetValue("ulined", server->IsULined() ? "Y" : "N");
        this->RunQuery(query);
}
https://github.com/anope/anope/blob/2.0/modules/extra/stats/irc2sql/irc2sql.cpp#L69


So, as the class 'Server' doesnt have a 'online' flag, i think it will be okay to assume in the query, that the server is now online.. But for the 'link_time' i have no clue...
Logged

genius3000

  • Anope User
  • Offline Offline
  • Posts: 37
Re: [MySQL] irc2sql anope_server not proper updating?
« Reply #1 on: October 21, 2020, 01:37:06 AM »

I'm not very familiar with the stats/irc2sql code or even the use of it but this looks proper. OnNewServer() is called whenever a server is added to Anope's internal handling (e.g., whenever a server connects). In that query, 'online' is set to 'Y', meaning Yes/True and 'link_time' is the output of the MySQL function `now()`.
Logged
-genius3000

DJFelipe

  • Anope User
  • Offline Offline
  • Posts: 8
Re: [MySQL] irc2sql anope_server not proper updating?
« Reply #2 on: October 21, 2020, 08:17:44 AM »

Normally i would expect this behaviour, your right..

So lets have a closer look... The void gets fired and the SQL Query will be setup, it will look like this:
Code: [Select]
INSERT DELAYED INTO `anope_server` (name, hops, comment, link_time, online, ulined) "
                "VALUES ('Testserver.Inter.NET', 0, 'No Comment', '2020-10-21 07:00:00', 'Y', 'N') "
        "ON DUPLICATE KEY UPDATE name='Testserver.Inter.NET', hops=0, comment='No Comment', "
                        "link_time='2020-10-21 07:00:00', online=VALUES(online), ulined='N'";
On the first execution, it will add a new entry into the table and say the Server is online=Y...
But on the further executions, as we have now a duplicate key, the second statement of the query will get handled, 'online' will stay at its default value of 'N' as online never gets parsed (also link_time) so the default value for that column is assumed

So from my point of view, this should fix that issue:
Code: [Select]
void IRC2SQL::OnNewServer(Server *server)
{
        query = "INSERT DELAYED INTO `" + prefix + "server` (name, hops, comment, link_time, online, ulined) "
                "VALUES (@name@, @hops@, @comment@, now(), 'Y', @ulined@) "
                "ON DUPLICATE KEY UPDATE name=VALUES(name), hops=VALUES(hops), comment=VALUES(comment), "
                        "link_time=NOW(), online='Y', ulined=(ulined)";
        query.SetValue("name", server->GetName());
        query.SetValue("hops", server->GetHops());
        query.SetValue("comment", server->GetDescription());
        query.SetValue("ulined", server->IsULined() ? "Y" : "N");
        this->RunQuery(query);
}
Im not 100% sure about the link_time, but online=Y makes definitly sense at that point... The VOID will get fired when a Server connects to the network, so it IS online
Logged

Jens Voss

  • Team
  • *
  • Offline Offline
  • Posts: 125
Re: [MySQL] irc2sql anope_server not proper updating?
« Reply #3 on: October 22, 2020, 07:29:23 AM »

Sadie committed a fix yesterday.
please checkout the latest git and try again.

Logged

DJFelipe

  • Anope User
  • Offline Offline
  • Posts: 8
Re: [MySQL] irc2sql anope_server not proper updating?
« Reply #4 on: October 22, 2020, 08:10:54 AM »

As i am running a productive environment, i can only try it at saturday night (i dont want to disturb my users :)) Maybe i will find some time to setup a testing environment and check it there...

I guess you mean this changeset? https://github.com/anope/anope/commit/a3c7f716bd256d8b8ead578b1a6bc657f31aec4d

I dont think that this will fix the issue, as Sadie only touched the 'ulined' part... Also online=VALUES(online) will fetch the current value and rewrite it.. So after a netsplit it will be online='N' and will stay 'N' after that query? Please correct me if im wrong...
Logged

Jens Voss

  • Team
  • *
  • Offline Offline
  • Posts: 125
Re: [MySQL] irc2sql anope_server not proper updating?
« Reply #5 on: October 22, 2020, 10:46:36 AM »

Ok. I'm correcting you, as requested. ;-)

VALUES() takes the values from the INSERT part of the query. (you can look it up in the mysql manual)
The "ulined=(ulined)" was definitely wrong.
Logged

DJFelipe

  • Anope User
  • Offline Offline
  • Posts: 8
Re: [MySQL] irc2sql anope_server not proper updating?
« Reply #6 on: October 22, 2020, 11:16:53 AM »

Ok that makes sense.. Never seen that function in a sql query before..

But another little progress i made in the past few minutes is this:
The query fails due to missing fields as they are defined as NOT NULL on the 'ON DUPLICATE' statement..
This is the Schema:
Code: [Select]
mysql> DESC anope_server;
+--------------+---------------+------+-----+---------+----------------+
| Field        | Type          | Null | Key | Default | Extra          |
+--------------+---------------+------+-----+---------+----------------+
| id           | int unsigned  | NO   | PRI | NULL    | auto_increment |
| name         | varchar(64)   | NO   | UNI | NULL    |                |
| hops         | tinyint       | NO   |     | NULL    |                |
| comment      | varchar(255)  | NO   |     | NULL    |                |
| link_time    | datetime      | YES  |     | NULL    |                |
| split_time   | datetime      | YES  |     | NULL    |                |
| version      | varchar(127)  | NO   |     | NULL    |                |
| currentusers | int           | NO   |     | NULL    |                |
| online       | enum('Y','N') | NO   |     | Y       |                |
| ulined       | enum('Y','N') | NO   |     | N       |                |
+--------------+---------------+------+-----+---------+----------------+

So if i execute the statement via mysql shell, it tells me that the fields 'version' and also 'currentusers' doesnt have correct default values to match the NOT-NULL setting..
Code: [Select]
mysql> INSERT DELAYED INTO `anope_server` (name, hops, comment, link_time, online, ulined) VALUES ('server1.test.net', 2, 'TestNET', now(), 'Y', 'N') ON DUPLICATE KEY UPDATE name=VALUES(name), hops=VALUES(hops), comment=VALUES(comment), link_time=VALUES(link_time), online=VALUES(online), ulined=VALUES(ulined);
ERROR 1364 (HY000): Field 'version' doesn't have a default value
So if i place the version in, it will look like this:
Code: [Select]
mysql> INSERT DELAYED INTO `anope_server` (name, hops, comment, link_time, online, ulined, version) VALUES ('server1.test.net', 2, 'TestNET', now(), 'Y', 'N','5.0.5.1') ON DUPLICATE KEY UPDATE name=VALUES(name), hops=VALUES(hops), comment=VALUES(comment), link_time=VALUES(link_time), online=VALUES(online), ulined=VALUES(ulined), version=VALUES(version);
ERROR 1364 (HY000): Field 'currentusers' doesn't have a default value

So again, i also put in the currentusers which will end in:
Code: [Select]
mysql> INSERT DELAYED INTO `anope_server` (name, hops, comment, link_time, online, ulined, version, currentusers) VALUES ('server1.test.net', 2, 'TestNET', now(), 'Y', 'N','5.0.5.1',15) ON DUPLICATE KEY UPDATE name=VALUES(name), hops=VALUES(hops), comment=VALUES(comment), link_time=VALUES(link_time), online=VALUES(online), ulined=VALUES(ulined), version=VALUES(version), currentusers=VALUES(currentusers);
Query OK, 2 rows affected, 8 warnings (0.06 sec)
(The Warnings are due to the deprecation of 'INSERT DELAYED' and 'VALUES function' (im using MySQL v8.0.21-1debian10))

I dont get it, why the mysql says it touched 2 rows but the data looks valid
Code: [Select]
mysql> SELECT * FROM anope_server;
+----+----------------------+------+----------------------------+---------------------+---------------------+---------+--------------+--------+--------+
| id | name                 | hops | comment                    | link_time           | split_time          | version | currentusers | online | ulined |
+----+----------------------+------+----------------------------+---------------------+---------------------+---------+--------------+--------+--------+
|  1 | services.test.net |    0 | Services for IRC Networks  | 2020-02-01 21:05:28 | NULL                |         |           29 | Y      | Y      |
|  2 | Hub1.test.net     |    1 | TestNET Main Network Node | 2020-02-01 21:06:11 | 2020-05-31 03:19:16 |         |           51 | Y      | N      |
|  3 | server1.test.net |    2 | TestNET                   | 2020-10-22 12:09:39 | 2020-10-22 02:37:51 | 5.0.5.1 |           15 | Y      | N      |
+----+----------------------+------+----------------------------+---------------------+---------------------+---------+--------------+--------+--------+
3 rows in set (0.00 sec)


So i also double-checked the Table Layout against the github and i can see that there are differences... Are tables not altered on a upgrade from anope?
https://github.com/anope/anope/blob/2.0/modules/extra/stats/irc2sql/tables.cpp#L75





Edit:
So after altering all tables manually to match my current version (2.0.7) SQL Schema against the sourcecode definitions, the OnNewServer Statement is finally working..  ;D
So its right that VALUES() was missing for 'ulined' (which is fixxed by Sadie) but doesnt throw an error/exception, so the query is successfully executed (without touching the ulined value).


So for me its working now... But the top-question still remains... I did not find any table-schema-compare-function in Anope, so i assume there is none? It would make sense to have one (as you can see)
« Last Edit: October 22, 2020, 01:18:55 PM by DJFelipe »
Logged
Pages: [1]   Go Up