Anope IRC Services

Please login or register.

Login with username, password and session length
Advanced search  

News:

Pages: [1]   Go Down

Author Topic: [m_sql_live] Nickname Recognition and Other SQL Issues  (Read 2418 times)

0 Members and 1 Guest are viewing this topic.

David

  • Anope User
  • Offline Offline
  • Posts: 2
[m_sql_live] Nickname Recognition and Other SQL Issues
« on: December 11, 2020, 07:22:28 PM »

Hello,

As part of using Anope's services, especially m_sql_live, my server has a tool where people can modify/register their IRC nicknames from a web form, which then inserts, edits, or deletes data in the nickname database. Through my understanding of m_sql_live (https://wiki.anope.org/index.php/2.0/Modules/m_sql), the database should be read to and from live, so changes to the SQL database should be readable and recognized immediately, and be available for use.

However, it appears that new nicknames inserted by the web form are not being recognized until after services restarts, even if the data in the database appears to be correct.

No logs appear to show any issues, however, personal experimentation clearly shows this is not working as intended.

Any assistance would be greatly apprecaited.

Thanks
« Last Edit: December 16, 2020, 12:02:27 AM by David »
Logged

CrazyCat

  • Anope User
  • Offline Offline
  • Posts: 240
    • French Eggdrop community
Re: [m_sql_live] New Nicknames Inserted in DB Not Recognized until Restart
« Reply #1 on: December 15, 2020, 08:38:15 AM »

Did you activate the debug mode to see the queries?

David

  • Anope User
  • Offline Offline
  • Posts: 2
Re: [m_sql_live] Nickname Recognition and Other SQL Issues
« Reply #2 on: December 16, 2020, 12:02:04 AM »

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.
Code: [Select]
[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:

Code: [Select]
[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:

Code: [Select]
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:
Code: [Select]
[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.

Code: [Select]
[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/AULkgTHL

However, 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:
Code: [Select]
[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.

Logged
Pages: [1]   Go Up