Anope Support > 2.0.x/1.9.x Support

[MySQL] irc2sql anope_server not proper updating?

<< < (2/2)

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: ---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       |                |
+--------------+---------------+------+-----+---------+----------------+
--- End code ---

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

--- End code ---
So if i place the version in, it will look like this:

--- Code: ---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
--- End code ---

So again, i also put in the currentusers which will end in:

--- Code: ---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)
--- End code ---
(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: ---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)
--- End code ---


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)

Navigation

[0] Message Index

[*] Previous page