Print

Please login or register.
1 Hour 1 Day 1 Week 1 Month Forever
Login with username, password and session length

[DJFelipe]

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...

[genius3000]

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()`.

[DJFelipe]

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

[Jens Voss]

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

[DJFelipe]

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...

[Jens Voss]

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.

[DJFelipe]

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.. 
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)