LisaList2

Advanced search  

News:

2019.06.07 fixed NChat for the "Curve" theme, will eventually move it to its own page and add it to the default theme as well. Other plugins are next. see post in the Meta board for details

Pages: [1]   Go Down

Author Topic: NChat bug fixed  (Read 1738 times)

rayarachelian

  • Administrator
  • Sr. Member
  • *****
  • Karma: +14/-0
  • Offline Offline
  • Posts: 324
  • "But what's puzzling you is the nature of my game"
    • LisaEm
NChat bug fixed
« on: June 07, 2019, 09:09:03 am »

Found the bug that was keeping NChat from working, so basically this is caused by differences between postgres and mysql.at https://github.com/nvcnvn/NChat/blob/4b4e76959d88156370f2b0f13be5374e5c723393/NChat/NChatHandle.php#L140there is a WHERE clauses that looks for an empty value using double quotes:
Code: [Select]
140                         WHERE online_color <> ""',
However postfgres wants single quotes here. Was hard to track this down, had to turn on javascript console and then post a message to NChat and then click on HTTP response which had an HTML error of 'ERROR: zero-length delimited identifier at or near' - when I googled that error, I ran into https://stackoverflow.com/questions/23165282/error-zero-length-delimited-identifier-at-or-near-line-1-delete-from-regwhich mentioned that it should use single not double quotes.
I fixed the code like so:
Code: [Select]
136         if($onlineColor == NULL){
137                 $request = $smcFunc['db_query']('', '
138                         SELECT id_group, online_color
139                         FROM {db_prefix}membergroups
140                         WHERE online_color <> \'\'',
141                         array(
142                         )
143                 );
which allowed it to work.
NChat is only enabled right now on the "Curve" theme on the main page, so if you'd like to play with it you can switch themes from your Profile > Account Settings > Modify Profile > Look and Layout.
I'll enable it for the default profile as well, though perhaps I'll to add Chat to a different page on the main screen rather than to the Home page.
I suspect there are other such database errors that are caused by postgres being treated as a second class citizen. :( though to be fair, I've no idea if single quotes are ok for mysql in that code. The trouble is these database errors aren't always logged to system logs.
In this case the error was sent back to the web browser as the HTML response, but it turns out that it also made it to the SMF Admin Error log. I've seen other cases where an iPad user could not post that wasn't logged to any log, so I've no way to track it down.  :P :o :(
Perhaps I should have installed this with mysql, but it's too late now.

Logged
Fate whispers to the warrior, 'You can not withstand the storm.'  The warrior whispers back, 'I am the storm.'

8088mph

  • Newbie
  • *
  • Karma: +6/-0
  • Offline Offline
  • Posts: 9
Re: NChat bug fixed
« Reply #1 on: April 10, 2020, 12:20:46 pm »

Single quotes are the right way to do a string literal, it's the SQL standard specification. MySQL also supports double quotes as string literals but that is non-standard. Postgres tends to be more standards compliant. I am an expert in Postgres so feel free to ask me if you have database issues.
Logged

rayarachelian

  • Administrator
  • Sr. Member
  • *****
  • Karma: +14/-0
  • Offline Offline
  • Posts: 324
  • "But what's puzzling you is the nature of my game"
    • LisaEm
Re: NChat bug fixed
« Reply #2 on: April 10, 2020, 09:24:15 pm »

Single quotes are the right way to do a string literal, it's the SQL standard specification. MySQL also supports double quotes as string literals but that is non-standard. Postgres tends to be more standards compliant. I am an expert in Postgres so feel free to ask me if you have database issues.

Much appreciated. Yeah, I am a fan of postgres over mysql, I've used it, mostly from the pov of a sysadmin rather than a dba. There are updates to SMF that I need to apply at some point. Not sure what will break, so I'll try it on a test vm first, but yeah, I've seen bunches of bugs in SMF plugins that can be traced back to postgres vs mysql issues and it seems themes can be problematic too.

The most recent issue is that Apple devices, particularly iPads, but not limited to those, tend to generate UTF that postgres throws errors on.
Logged
Fate whispers to the warrior, 'You can not withstand the storm.'  The warrior whispers back, 'I am the storm.'

8088mph

  • Newbie
  • *
  • Karma: +6/-0
  • Offline Offline
  • Posts: 9
Re: NChat bug fixed
« Reply #3 on: April 10, 2020, 10:31:41 pm »

I'm more of a software engineer, tuple relational calculus is still confusing to me. I have been a de facto DBA to a 20TB production Postgres DB in the past, while doing other development at the same time. Postgres does run into some issues when you reach millions of users. Like even if your slave server has 128 CPUs and 2TB of RAM, it still won't work, because Postgres replication is single threaded.

For your case, you can easily pg_dump the entire DB. Then you can restore it to another DB on the same Postgres instance, or if you prefer to your local machine or a VM, and do all of your testing on the copy. When you go to make the changes to the main DB, if there are any problems, you can drop the database and restore from the dump.

What issues are you having with UTF? Is the DB configured to use UTF-8 encoding? You should see something like this if you type \l on a psql prompt:

postgres=# \l
                                        List of databases
         Name          |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------------------+----------+----------+-------------+-------------+-----------------------
 postgres              | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0             | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                       |          |          |             |             | postgres=CTc/postgres
 template1             | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                       |          |          |             |             | postgres=CTc/postgres
Logged

rayarachelian

  • Administrator
  • Sr. Member
  • *****
  • Karma: +14/-0
  • Offline Offline
  • Posts: 324
  • "But what's puzzling you is the nature of my game"
    • LisaEm
Re: NChat bug fixed
« Reply #4 on: July 13, 2020, 01:46:12 pm »

I'm more of a software engineer, tuple relational calculus is still confusing to me. I have been a de facto DBA to a 20TB production Postgres DB in the past, while doing other development at the same time. Postgres does run into some issues when you reach millions of users. Like even if your slave server has 128 CPUs and 2TB of RAM, it still won't work, because Postgres replication is single threaded.

For your case, you can easily pg_dump the entire DB. Then you can restore it to another DB on the same Postgres instance, or if you prefer to your local machine or a VM, and do all of your testing on the copy. When you go to make the changes to the main DB, if there are any problems, you can drop the database and restore from the dump.

What issues are you having with UTF? Is the DB configured to use UTF-8 encoding? You should see something like this if you type \l on a psql prompt:

postgres=# \l
                                        List of databases
         Name          |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------------------+----------+----------+-------------+-------------+-----------------------
 postgres              | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0             | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                       |          |          |             |             | postgres=CTc/postgres
 template1             | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                       |          |          |             |             | postgres=CTc/postgres


Possibly it's not enabled. So I built this server using a bunch of docker containers, and some supporting script. The upside is that I can restore a backup of both the SMF and the postgres containers on my laptop and mess with them as needed, but it does need some tweaking to get it to run without SSL, etc.
Logged
Fate whispers to the warrior, 'You can not withstand the storm.'  The warrior whispers back, 'I am the storm.'
Pages: [1]   Go Up