Rocksolid Light

Welcome to Rocksolid Light

register   nodelist   faq  


rocksolid / rocksolid.shared.helpdesk / Re: SQL error

SubjectAuthor
* SQL errortrw
`* Re: SQL errorRetro Guy
 +- Re: SQL errortrw
 `* Re: SQL errorGuest
  `* Re: SQL errortrw
   `* Re: SQL errorRetro Guy
    `* Re: SQL errorNeodome Admin
     `* Re: SQL erroranonymous
      `* Re: SQL errorNeodome Admin
       `* Re: SQL errortrw
        `* Re: SQL errorNeodome Admin
         `* Re: SQL errorNeodome Admin
          `* Re: SQL errorRetro Guy
           +* Re: SQL errorRetro Guy
           |`* Re: SQL errortrww
           | `* Re: SQL errorRetro Guy
           |  `- Re: SQL errortrw
           `* Re: SQL errorRetro Guy
            `* Re: SQL errortrww
             `- Re: SQL errorRetro Guy

Subject: SQL error
From: trw@i2pmail.org (trw)
Newsgroups: rocksolid.shared.helpdesk
Organization: Dancing elephants
Date: Mon, 7 Oct 2019 12:18 UTC
I get a sql error when importing messages from comp.lang.python into fudforum, i believe it is always the same message the triggers it. the sql error log shows this:

HY000: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '='
Query: SELECT m.id, m.thread_id FROM fud30_msg m INNER JOIN fud30_thread t ON m.thread_id=t.id WHERE t.forum_id=123 AND m.subject='[poliastro-dev] ANN: poliastro 0.13.0 released πŸš€' ORDER BY m.post_stamp

I guess it has something to do with an outlandish encoding in this message. Question is, how to fix ? Thoughts ?

cheers

trw
Posted on def3


Subject: Re: SQL error
From: Retro Guy@rslight.i2p (Retro Guy)
Newsgroups: rocksolid.shared.helpdesk
Organization: Rocksolid Light
Date: Tue, 8 Oct 2019 08:47 UTC
trw wrote:

I get a sql error when importing messages from comp.lang.python into
fudforum, i believe it is always the same message the triggers it. the sql error log shows this:

HY000: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and
(utf8mb4_general_ci,COERCIBLE) for operation '='
Query: SELECT m.id, m.thread_id FROM fud30_msg m INNER JOIN fud30_thread t
ON m.thread_id=t.id WHERE t.forum_id=123 AND m.subject='[poliastro-dev] ANN: poliastro 0.13.0 released πŸš€' ORDER BY m.post_stamp

I guess it has something to do with an outlandish encoding in this message.
Question is, how to fix ? Thoughts ?

Is ff continuing to import messages after this one, or halting import for the group? I know that nntp.inc in its original form would halt processing a group and never retrieve newer messages if just one message failed (I sent you a fix 14 May, 2019 for this), so if it's halting, we need to find where in the code it decides to do this.

If it continues past the message, I wouldn't worry about it, you just lose one message.

Have you run database maintenance on your ff database recently?

If the db is ok, and its not proceeding to import messages, we need to find where this is happening. The fact they decided to halt on nntp errors leads me to think they may have had the same mindset with db errors.

Retro Guy
--
Posted on Rocksolid Light



Subject: Re: SQL error
From: trw@i2pmail.org (trw)
Newsgroups: rocksolid.shared.helpdesk
Organization: Dancing elephants
Date: Tue, 8 Oct 2019 12:30 UTC
Attachments: python_import_error.jpg (image/jpeg)
Is ff continuing to import messages after this one, or halting import for the group?

No, there is an error message that there is an sql error, then the group is dumped, and not even the counter is updated (so next time all old messages are imported again).

I know that nntp.inc in its original form would halt processing a group and never retrieve newer messages if just one message failed (I

I applied the patch, but to no avail. I believe this is not happening in nntp.inc, and has in fact nothing to with the nntp import as such, just with the further processing of the message.

If it continues past the message, I wouldn't worry about it, you just lose one message.

Maybe I can set the counter manually so that it start past that message.

Have you run database maintenance on your ff database recently?

Yes, after this incident, but nothing came up.


It has to do with two different collations that let a sql statement crash. One of the collations is express, the other is implicit. I need to find that sql statement and correct it, I guess, to really fix it.

(attached a screenshot of the error message)

Posted on def3


Attachments: python_import_error.jpg 
Subject: Re: SQL error
From: guest@retrobbs.rocksolidbbs.com (Guest)
Newsgroups: rocksolid.shared.helpdesk
Organization: Dancing elephants
Date: Sun, 13 Oct 2019 09:23 UTC
Can you post the message-id of the offending message? I'd like to take a look at it.

In get_fud_reply_id, does it even send the subject or body with the query (in this particular line)? It looks like it only uses $data ($emsg->reply_to_msg_id). Maybe we can take a close look at the message header and see if anything stands out. It does seem that something is sent with the query that the sql server doesn't like.

Posted on def3


Subject: Re: SQL error
From: trw@i2pmail.org (trw)
Newsgroups: rocksolid.shared.helpdesk
Organization: Dancing elephants
Date: Sun, 13 Oct 2019 11:34 UTC
wish I could but I canceled the message (which did not help,btw).
you can find it by doing:
grep -R '[poliastro-dev]' /var/spool/news/articles/comp/lang/python/
Posted on def3


Subject: Re: SQL error
From: retro.guy@retrobbs.rocksolidbbs.com.remove-1ho-this (Retro Guy)
Newsgroups: rocksolid.shared.helpdesk
Organization: RetroBBS
Date: Mon, 14 Oct 2019 01:51 UTC
  To: trw
trw wrote:

wish I could but I canceled the message (which did not help,btw).
you can find it by doing:
grep -R '[poliastro-dev]' /var/spool/news/articles/comp/lang/python/

Strange, I can't find that in my inn spool(s)


--
Posted on RetroBBS



Subject: Re: SQL error
From: admin@neodome.net (Neodome Admin)
Newsgroups: rocksolid.shared.helpdesk
Organization: Neodome
Date: Mon, 14 Oct 2019 19:46 UTC
Retro Guy <retro.guy@retrobbs.rocksolidbbs.com.remove-1ho-this> wrote:
  To: trw
trw wrote:

wish I could but I canceled the message (which did not help,btw).
you can find it by doing:
grep -R '[poliastro-dev]' /var/spool/news/articles/comp/lang/python/

Strange, I can't find that in my inn spool(s)



Here it is:

<http://neodome.net/cgi-bin/retrieve?message_id=%3Cmailman.293.1565494212.29302.python-list%40python.org%3E&submit=Submit>

Seems that Subject header is properly encoded. Is it really supposed to be
decoded in SQL query?

--
Neodome


Subject: Re: SQL error
From: anonymous@def2.anon (anonymous)
Newsgroups: rocksolid.shared.helpdesk
Organization: def2org
Date: Mon, 14 Oct 2019 20:33 UTC
Is it really supposed to be decoded in SQL query?

Well, the forum software checks (among other things) if the subject is known, in order to generate threads. So I guess, it has to be decoded for that. I have to look more closely at the code.

cheers

trw
Posted on def2




Subject: Re: SQL error
From: admin@neodome.net (Neodome Admin)
Newsgroups: rocksolid.shared.helpdesk
Organization: Neodome
Date: Mon, 14 Oct 2019 22:06 UTC
anonymous <anonymous@def2.anon> wrote:
Is it really supposed to be decoded in SQL query?

Well, the forum software checks (among other things) if the subject is
known, in order to generate threads.

That would be better to do with encoded header, because in encoded form
it’s pure ASCII (as required by RFC.) Probably will have to keep two forms
of headers in DB, encoded and decoded.

So I guess, it has to be decoded for
that. I have to look more closely at the code.

cheers

trw
Posted on def2






--
Neodome


Subject: Re: SQL error
From: trw@i2pmail.org (trw)
Newsgroups: rocksolid.shared.helpdesk
Organization: Dancing elephants
Date: Tue, 15 Oct 2019 16:18 UTC
That would be better to do with encoded header, because in encoded form it's pure ASCII (as required by RFC.)

I agree on the principle, there is no added value in the decoding. And it might be a good way to avoid these conflicts. Also, it might be quite complicated to implement it, I have to check which functions would need reprogramming. :)

What I don't understand is why there is an assumed collation in the sql query, just on account of the string. Bet it is only a little change to turn it off, if you know how.



cheers

trw  Posted on def3


Pages:12
rocksolid light 0.6.5e
clearnet i2p tor