Did you activate the debug mode to see the queries?
Hello!
I have indeed run a few debug versions, including piping out the debug log to review later. Here is what I found:
1) The database seems to (by default) store information such as MEMO_SIGNON and MEMO_RECEIVE as NULL, which causes the following error type approximately 1,170 times.
[Dec 15 22:56:48.946058 2020] Debug: Extend for nonexistent type MEMO_RECEIVE on 0x55a46a10b268
This appears to be the intended use, however it does fill up quite a large section of the logs so I have my doubts.
2) There seems to be a lot of places where TIMESTAMP is NULL. Again, this appears to be how data is stored in the DB by default but I don't know if this is actually intentional or what this should be by default set to. Example queries include the following lines:
[Dec 15 22:56:49.032728 2020] Debug: SQL-live got 619 rows for SELECT * FROM `anope_db_NickAlias` WHERE (`timestamp` >= FROM_UNIXTIME(0) OR `timestamp` IS NULL)
[Dec 15 22:56:48.944371 2020] Debug: SQL-live got 585 rows for SELECT * FROM `anope_db_NickCore` WHERE (`timestamp` >= FROM_UNIXTIME(0) OR `timestamp` IS NULL)
3) It appears anytime anope_db_NickAlias's nc row has a name with an underscore in it (ex, user_one, my_name_here, username_, etc) the database throws a fit. I don't know what the source of this is, because from what I can tell underscores ARE valid characters in Anope and therefore should be accepted in the database.
This is noticeable for me because the following line repeats several times for a user with their username with an underscore appears several (at least 12) time:
Debug: SQL-live got 0 rows for UPDATE `anope_db_NickAlias` SET `timestamp` = FROM_UNIXTIME(1608073023) WHERE `id` = 301
4) Similarly, it appears that Anope does not create every table it looks for, or it only generates/creates tables as required. Hence, the log is full of issues such as:
[Dec 15 22:56:49.104504 2020] Debug: SQL-live got error Table 'ircDB.anope_db_IgnoreData' doesn't exist for SELECT * FROM `anope_db_IgnoreData` WHERE (`timestamp` >= FROM_UNIXTIME(0) OR `timestamp` IS NULL)
[Dec 15 22:56:49.104827 2020] Debug: SQL-live got error Table 'ircDB.anope_db_LogSetting' doesn't exist for SELECT * FROM `anope_db_LogSetting` WHERE (`timestamp` >= FROM_UNIXTIME(0) OR `timestamp` IS NULL)
[Dec 15 22:56:49.105134 2020] Debug: SQL-live got error Table 'ircDB.anope_db_Memo' doesn't exist for SELECT * FROM `anope_db_Memo` WHERE (`timestamp` >= FROM_UNIXTIME(0) OR `timestamp` IS NULL)
5) There are several entries that look similar to the following, but I can't tell what they are for (some data sniped) or if they are in the proper format.
[Dec 15 22:56:53.640307 2020] Debug: SQL-live got 0 rows for INSERT INTO `anope_db_ChannelInfo` (`id`,`BS_FANTASY`,`BS_GREET`,`CS_KEEP_MODES`,`CS_NO_EXPIRE`,`CS_PRIVATE`,`CS_SECURE`,`KEEPTOPIC`,`PEACE`,`PERSIST`,`RESTRICTED`,`SECUREFOUNDER`,`SIGNKICK`,`TOPICLOCK`,`banexpire`,`bantype`,`bi`,`capsmin`,`capspercent`,`description`,`floodlines`,`floodsecs`,`founder`,`kickerdata:amsgs`,`kickerdata:badwords`,`kickerdata:bolds`,`kickerdata:caps`,`kickerdata:colors`,`kickerdata:flood`,`kickerdata:italics`,`kickerdata:repeat`,`kickerdata:reverses`,`kickerdata:underlines`,`last_modes`,`last_topic`,`last_topic_setter`,`last_topic_time`,`last_used`,`levels`,`memomax`,`name`,`repeattimes`,`time_registered`,`ttb`) VALUES (SNIPPED VALUES HERE)
5) Most directly related to the issue, is the following:
When I register a new name through the form, and change my nickname while logged in, I have something along the following happen (some personal data snipped)
Of note that DEL_FLAG is a custom added field the SQLDB - nothing could be found saying we COULDN'T add our own fields to the DB, so we did. (If this is not allowed, there really should be indicated somewhere)
Log is too long to put here. Put in Pastebin for reference:
https://pastebin.com/AULkgTHLHowever, while the name is in the DB, I lose +r on the nick change and cannot regain it. The name is never truly recognized by the server, even after os restarts and ns updates.
ns update command log:
[Dec 15 22:58:34.896637 2020] Debug: Received: :TGH52 PRIVMSG nickserv@services.[SNIPPED] :update
[Dec 15 22:58:34.897832 2020] Debug: SQL-live got 0 rows for SELECT * FROM `anope_db_BotInfo` WHERE (`timestamp` >= FROM_UNIXTIME(1608073109) OR `timestamp` IS NULL)
[Dec 15 22:58:34.898938 2020] Debug: SQL-live got 0 rows for SELECT * FROM `anope_db_NickCore` WHERE (`timestamp` >= FROM_UNIXTIME(1608073109) OR `timestamp` IS NULL)
[Dec 15 22:58:34.899930 2020] Debug: SQL-live got 3 rows for SELECT * FROM `anope_db_NickAlias` WHERE (`timestamp` >= FROM_UNIXTIME(1608073109) OR `timestamp` IS NULL)
[Dec 15 22:58:34.901354 2020] Debug: SQL-live got 0 rows for UPDATE `anope_db_NickAlias` SET `timestamp` = FROM_UNIXTIME(1608073114) WHERE `id` = 301
[Dec 15 22:58:34.902800 2020] Debug: SQL-live got 0 rows for UPDATE `anope_db_NickAlias` SET `timestamp` = FROM_UNIXTIME(1608073114) WHERE `id` = 512
[Dec 15 22:58:34.903902 2020] Debug: SQL-live got 0 rows for UPDATE `anope_db_NickAlias` SET `timestamp` = FROM_UNIXTIME(1608073114) WHERE `id` = 1108
[Dec 15 22:58:34.904142 2020] Debug: Setting correct user modes for TGH52 on #[SNIPPED] (giving modes)
[Dec 15 22:58:34.904821 2020] Debug: SQL-live got 0 rows for SELECT * FROM `anope_db_ChannelInfo` WHERE (`timestamp` >= FROM_UNIXTIME(1608073032) OR `timestamp` IS NULL)
[Dec 15 22:58:34.905284 2020] Debug: SQL-live got 0 rows for SELECT * FROM `anope_db_ChanAccess` WHERE (`timestamp` >= FROM_UNIXTIME(1608073032) OR `timestamp` IS NULL)
[Dec 15 22:58:34.905377 2020] Debug: Setting correct user modes for TGH52 on #[Channel] (giving modes)
I don't know enough about nickserv and anope's coding to know if this is the expected correct result.
I'm sorry this is so long, and if I did not include the sections of the logs needed. Please let me know what other sections I need to add to help with the diagnosis of this issue.