Rocksolid Light

Welcome to novaBBS (click a section below)

mail  files  register  nodelist  faq  

"Hey! Who took the cork off my lunch??!" -- W. C. Fields


rocksolid / 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

1
Subject: SQL error
From: trw
Newsgroups: rocksolid.shared.helpdesk
Organization: Dancing elephants
Date: Mon, 7 Oct 2019 12:18 UTC
Path: i2pn2.org!.POSTED!not-for-mail
From: trw...@i2pmail.org (trw)
Newsgroups: rocksolid.shared.helpdesk
Subject: SQL error
Date: Mon, 07 Oct 2019 08:18:51 -0400
Organization: Dancing elephants
Lines: 9
Message-ID: <qnfaf5$5s8$1@i2pn2.org>
Reply-To: trw <trw@i2pmail.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Injection-Date: Mon, 7 Oct 2019 12:18:47 -0000 (UTC)
Injection-Info: i2pn2.org; posting-account="def.i2p";
logging-data="6024"; mail-complaints-to="usenet@i2pn2.org"
User-Agent: FUDforum 3.0.7
X-FUDforum: 6666cd76f96956469e7be39d750cc7d9 <63239>
View all headers
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
Newsgroups: rocksolid.shared.helpdesk
Organization: Rocksolid Light
Date: Tue, 8 Oct 2019 08:47 UTC
Path: i2pn2.org!rocksolid2!.POSTED.localhost!not-for-mail
From: Retro ...@rslight.i2p (Retro Guy)
Newsgroups: rocksolid.shared.helpdesk
Subject: Re: SQL error
Date: Tue, 8 Oct 2019 08:47:34 -0000 (UTC)
Organization: Rocksolid Light
Message-ID: <2f0b1fa44fb103b4e17cd15cc9088571$1@news.novabbs.com>
References: <qnfaf5$5s8$1@i2pn2.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Injection-Date: Tue, 8 Oct 2019 08:47:34 -0000 (UTC)
Injection-Info: novabbs.com; posting-account="retrobbs1"; posting-host="localhost:127.0.0.1";
logging-data="12751"; mail-complaints-to="usenet@novabbs.com"
User-Agent: rslight (http://news.novabbs.com)
X-Spam-Checker-Version: SpamAssassin 3.4.2 (2018-09-13) on novabbs.com
X-Rslight-Site: $2y$10$0b.Vz1GuNQPk58txEM/JiOstK7DOAtfABLGBQB0MhQ5lYHwcMufu.
View all headers
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
Newsgroups: rocksolid.shared.helpdesk
Organization: Dancing elephants
Date: Tue, 8 Oct 2019 12:30 UTC
Attachments: python_import_error.jpg (image/jpeg)
Path: i2pn2.org!.POSTED!not-for-mail
From: trw...@i2pmail.org (trw)
Newsgroups: rocksolid.shared.helpdesk
Subject: Re: SQL error
Date: Tue, 08 Oct 2019 08:30:37 -0400
Organization: Dancing elephants
Lines: 19
Message-ID: <qnhvh4$mtl$1@i2pn2.org>
References: <2f0b1fa44fb103b4e17cd15cc9088571$1@news.novabbs.com>
Reply-To: trw <trw@i2pmail.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Injection-Date: Tue, 8 Oct 2019 12:30:32 -0000 (UTC)
Injection-Info: i2pn2.org; posting-account="def.i2p";
logging-data="23477"; mail-complaints-to="usenet@i2pn2.org"
User-Agent: FUDforum 3.0.7
X-FUDforum: 6666cd76f96956469e7be39d750cc7d9 <63716>
View all headers
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
Newsgroups: rocksolid.shared.helpdesk
Organization: Dancing elephants
Date: Sun, 13 Oct 2019 09:23 UTC
Path: i2pn2.org!.POSTED!not-for-mail
From: gue...@retrobbs.rocksolidbbs.com (Guest)
Newsgroups: rocksolid.shared.helpdesk
Subject: Re: SQL error
Date: Sun, 13 Oct 2019 05:23:07 -0400
Organization: Dancing elephants
Lines: 3
Message-ID: <qnuqd5$557$1@i2pn2.org>
References: <2f0b1fa44fb103b4e17cd15cc9088571$1@news.novabbs.com>
Reply-To: Guest <guest@retrobbs.rocksolidbbs.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Injection-Date: Sun, 13 Oct 2019 09:22:47 -0000 (UTC)
Injection-Info: i2pn2.org; posting-account="def.i2p";
logging-data="5287"; mail-complaints-to="usenet@i2pn2.org"
User-Agent: FUDforum 3.0.7
X-FUDforum: 6666cd76f96956469e7be39d750cc7d9 <65350>
View all headers
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
Newsgroups: rocksolid.shared.helpdesk
Organization: Dancing elephants
Date: Sun, 13 Oct 2019 11:34 UTC
Path: i2pn2.org!.POSTED!not-for-mail
From: trw...@i2pmail.org (trw)
Newsgroups: rocksolid.shared.helpdesk
Subject: Re: SQL error
Date: Sun, 13 Oct 2019 07:34:10 -0400
Organization: Dancing elephants
Lines: 2
Message-ID: <qnv22s$eu0$1@i2pn2.org>
References: <qnuqd5$557$1@i2pn2.org>
Reply-To: trw <trw@i2pmail.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Injection-Date: Sun, 13 Oct 2019 11:33:50 -0000 (UTC)
Injection-Info: i2pn2.org; posting-account="def.i2p";
logging-data="15296"; mail-complaints-to="usenet@i2pn2.org"
User-Agent: FUDforum 3.0.7
X-FUDforum: 6666cd76f96956469e7be39d750cc7d9 <65503>
View all headers
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
Newsgroups: rocksolid.shared.helpdesk
Organization: RetroBBS
Date: Mon, 14 Oct 2019 01:51 UTC
Path: i2pn2.org!rocksolid2!.POSTED.rocksolid3!not-for-mail
From: retro....@retrobbs.rocksolidbbs.com.remove-1ho-this (Retro Guy)
Newsgroups: rocksolid.shared.helpdesk
Subject: Re: SQL error
Date: Mon, 14 Oct 2019 01:51:05 +0000
Organization: RetroBBS
Message-ID: <9e4bb59398be47dd4972eec565cd2c81$1@www.rocksolidbbs.com>
References: <qnv22s$eu0$1@i2pn2.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Injection-Info: novabbs.com; posting-account="rslight.i2p"; posting-host="rocksolid3:192.241.178.238";
logging-data="4716"; mail-complaints-to="usenet@novabbs.com"
User-Agent: rslight (http://news.novabbs.com)
To: trw
X-Comment-To: trw
In-Reply-To: <qnv22s$eu0$1@i2pn2.org>
X-FTN-PID: Synchronet 3.17a-Linux Dec 29 2018 GCC 6.3.0
X-Spam-Checker-Version: SpamAssassin 3.4.2 (2018-09-13) on rocksolidbbs.com
X-Rslight-Site: $2y$10$FlGH.tUchD5ePlUa9A33Q.3mJFwt10vb9shPOJlnKc6TiIZ4415Uy
X-Gateway: retrobbs.rocksolidbbs.com [Synchronet 3.17a-Linux NewsLink 1.110]
View all headers
  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: Neodome Admin
Newsgroups: rocksolid.shared.helpdesk
Organization: Neodome
Date: Mon, 14 Oct 2019 19:46 UTC
Path: i2pn2.org!i2pn.org!news.neodome.net!.POSTED!not-for-mail
From: adm...@neodome.net (Neodome Admin)
Newsgroups: rocksolid.shared.helpdesk
Subject: Re: SQL error
Date: Mon, 14 Oct 2019 19:46:00 -0000 (UTC)
Organization: Neodome
Message-ID: <qo2j9o$5fm$1@neodome.net>
References: <qnv22s$eu0$1@i2pn2.org>
<9e4bb59398be47dd4972eec565cd2c81$1@www.rocksolidbbs.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Injection-Date: Mon, 14 Oct 2019 19:46:00 -0000 (UTC)
Injection-Info: neodome.net; mail-complaints-to="abuse@neodome.net"
User-Agent: NewsTap/5.3.5 (iPhone/iPod Touch)
Cancel-Lock: sha1:PIBfHYsSxdZ0yPxNvmior6Xvdm4=
View all headers
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
Newsgroups: rocksolid.shared.helpdesk
Organization: def2org
Date: Mon, 14 Oct 2019 20:33 UTC
Path: i2pn2.org!rocksolid2!def2!.POSTED.localhost!not-for-mail
From: anonym...@def2.anon (anonymous)
Newsgroups: rocksolid.shared.helpdesk
Subject: Re: SQL error
Date: Mon, 14 Oct 2019 20:33:18 -0000 (UTC)
Organization: def2org
Message-ID: <59e1d0e427e736cd3c0cc06c878cc8fa$1@z5bqfv5v75kxy7pj.onion>
References: <qnv22s$eu0$1@i2pn2.org> <9e4bb59398be47dd4972eec565cd2c81$1@www.rocksolidbbs.com> <qo2j9o$5fm$1@neodome.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Injection-Date: Mon, 14 Oct 2019 20:33:18 -0000 (UTC)
Injection-Info: def2.org; posting-host="localhost:127.0.0.1";
logging-data="5076"; mail-complaints-to="usenet@def2.org"
View all headers
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: Neodome Admin
Newsgroups: rocksolid.shared.helpdesk
Organization: Neodome
Date: Mon, 14 Oct 2019 22:06 UTC
Path: i2pn2.org!i2pn.org!news.neodome.net!.POSTED!not-for-mail
From: adm...@neodome.net (Neodome Admin)
Newsgroups: rocksolid.shared.helpdesk
Subject: Re: SQL error
Date: Mon, 14 Oct 2019 22:06:52 -0000 (UTC)
Organization: Neodome
Message-ID: <qo2rhs$2311$1@neodome.net>
References: <qnv22s$eu0$1@i2pn2.org>
<9e4bb59398be47dd4972eec565cd2c81$1@www.rocksolidbbs.com>
<qo2j9o$5fm$1@neodome.net>
<59e1d0e427e736cd3c0cc06c878cc8fa$1@z5bqfv5v75kxy7pj.onion>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Injection-Date: Mon, 14 Oct 2019 22:06:52 -0000 (UTC)
Injection-Info: neodome.net; mail-complaints-to="abuse@neodome.net"
User-Agent: NewsTap/5.3.5 (iPhone/iPod Touch)
Cancel-Lock: sha1:M0ytTlcATM82E35K/zzITh9e3qc=
View all headers
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
Newsgroups: rocksolid.shared.helpdesk
Organization: Dancing elephants
Date: Tue, 15 Oct 2019 16:18 UTC
Path: i2pn2.org!.POSTED!not-for-mail
From: trw...@i2pmail.org (trw)
Newsgroups: rocksolid.shared.helpdesk
Subject: Re: SQL error
Date: Tue, 15 Oct 2019 12:18:54 -0400
Organization: Dancing elephants
Lines: 11
Message-ID: <qo4rgj$dcq$1@i2pn2.org>
References: <qo2rhs$2311$1@neodome.net>
Reply-To: trw <trw@i2pmail.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Injection-Date: Tue, 15 Oct 2019 16:18:31 -0000 (UTC)
Injection-Info: i2pn2.org; posting-account="def.i2p";
logging-data="13722"; mail-complaints-to="usenet@i2pn2.org"
User-Agent: FUDforum 3.0.7
X-FUDforum: 6666cd76f96956469e7be39d750cc7d9 <67012>
View all headers
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


Subject: Re: SQL error
From: Neodome Admin
Newsgroups: rocksolid.shared.helpdesk
Organization: Neodome
Date: Tue, 15 Oct 2019 19:09 UTC
Path: i2pn2.org!i2pn.org!news.neodome.net!.POSTED!not-for-mail
From: adm...@neodome.net (Neodome Admin)
Newsgroups: rocksolid.shared.helpdesk
Subject: Re: SQL error
Date: Tue, 15 Oct 2019 19:09:54 -0000 (UTC)
Organization: Neodome
Message-ID: <qo55i2$1bge$1@neodome.net>
References: <qo2rhs$2311$1@neodome.net>
<qo4rgj$dcq$1@i2pn2.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Injection-Date: Tue, 15 Oct 2019 19:09:54 -0000 (UTC)
Injection-Info: neodome.net; mail-complaints-to="abuse@neodome.net"
User-Agent: NewsTap/5.3.5 (iPhone/iPod Touch)
Cancel-Lock: sha1:TmEP/m3zgHvBu0JAZ0Ce19bJJa8=
View all headers
trw <trw@i2pmail.org> wrote:
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.

ALTER TABLE fud30_msg CHARACTER SET utf8 COLLATE utf8mb4_unicode_ci;

Just do DB backup before altering table, of course.

--
Neodome


Subject: Re: SQL error
From: Neodome Admin
Newsgroups: rocksolid.shared.helpdesk
Organization: Neodome
Date: Tue, 15 Oct 2019 19:22 UTC
Path: i2pn2.org!i2pn.org!news.neodome.net!.POSTED!not-for-mail
From: adm...@neodome.net (Neodome Admin)
Newsgroups: rocksolid.shared.helpdesk
Subject: Re: SQL error
Date: Tue, 15 Oct 2019 19:22:00 -0000 (UTC)
Organization: Neodome
Message-ID: <qo568o$d21$1@neodome.net>
References: <qo2rhs$2311$1@neodome.net>
<qo4rgj$dcq$1@i2pn2.org>
<qo55i2$1bge$1@neodome.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Injection-Date: Tue, 15 Oct 2019 19:22:00 -0000 (UTC)
Injection-Info: neodome.net; mail-complaints-to="abuse@neodome.net"
User-Agent: NewsTap/5.3.5 (iPhone/iPod Touch)
Cancel-Lock: sha1:3Q1l0urU24vCigiFyFw+YfBsUVc=
View all headers
Neodome Admin <admin@neodome.net> wrote:
trw <trw@i2pmail.org> wrote:
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.

ALTER TABLE fud30_msg CHARACTER SET utf8 COLLATE utf8mb4_unicode_ci;

Just do DB backup before altering table, of course.


I guess I should clarify. Seems that MySQL is using utf8mb4_unicode_ci by
default but your table have utf8_unicode_ci. You cannot mix them, so you
have to alter table.

It might screw up things in other places though, if your code explicitly
using utf8_unicode_ci.

--
Neodome


Subject: Re: SQL error
From: Retro Guy
Newsgroups: rocksolid.shared.helpdesk
Organization: Rocksolid Light
Date: Tue, 15 Oct 2019 22:22 UTC
Path: i2pn2.org!rocksolid2!.POSTED.localhost!not-for-mail
From: Retro ...@rslight.i2p (Retro Guy)
Newsgroups: rocksolid.shared.helpdesk
Subject: Re: SQL error
Date: Tue, 15 Oct 2019 22:22:31 -0000 (UTC)
Organization: Rocksolid Light
Message-ID: <7d9fba36ee0ab8f122ea2afa3e50f280$1@news.novabbs.com>
References: <qo2rhs$2311$1@neodome.net> <qo4rgj$dcq$1@i2pn2.org> <qo55i2$1bge$1@neodome.net> <qo568o$d21$1@neodome.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Injection-Date: Tue, 15 Oct 2019 22:22:31 -0000 (UTC)
Injection-Info: novabbs.com; posting-account="retrobbs1"; posting-host="localhost:127.0.0.1";
logging-data="17761"; mail-complaints-to="usenet@novabbs.com"
User-Agent: rslight (http://news.novabbs.com)
X-Spam-Checker-Version: SpamAssassin 3.4.2 (2018-09-13) on novabbs.com
X-Rslight-Site: $2y$10$q3WVGx5KrPjqc8HBdkidFuIyaZlMczTjOCrXbYqIsDBQkEsNw//rK
View all headers
Neodome Admin wrote:

Neodome Admin <admin@neodome.net> wrote:
trw <trw@i2pmail.org> wrote:
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.

ALTER TABLE fud30_msg CHARACTER SET utf8 COLLATE utf8mb4_unicode_ci;

Just do DB backup before altering table, of course.


I guess I should clarify. Seems that MySQL is using utf8mb4_unicode_ci by
default but your table have utf8_unicode_ci. You cannot mix them, so you
have to alter table.

It might screw up things in other places though, if your code explicitly
using utf8_unicode_ci.

I do see that this message is (has been imported) into fudforum here: http://novabbs.i2p/index.php?t=msg&th=336564&goto=1960612&#msg_1960612

Notice the subject line is not decoded as it is here:
http://rslight.i2p/compprog/article-flat.php?id=4462&group=comp.lang.python#4462
but it is in the ff database (it imported).

Retro Guy
--
Posted on Rocksolid Light



Subject: Re: SQL error
From: Retro Guy
Newsgroups: rocksolid.shared.helpdesk
Organization: Rocksolid Light
Date: Tue, 15 Oct 2019 23:02 UTC
Path: i2pn2.org!rocksolid2!.POSTED.localhost!not-for-mail
From: Retro ...@rslight.i2p (Retro Guy)
Newsgroups: rocksolid.shared.helpdesk
Subject: Re: SQL error
Date: Tue, 15 Oct 2019 23:02:43 -0000 (UTC)
Organization: Rocksolid Light
Message-ID: <7ddfabfe10637bfdd2e78aa6953bc568$1@news.novabbs.com>
References: <qo2rhs$2311$1@neodome.net> <qo4rgj$dcq$1@i2pn2.org> <qo55i2$1bge$1@neodome.net> <qo568o$d21$1@neodome.net> <7d9fba36ee0ab8f122ea2afa3e50f280$1@news.novabbs.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Injection-Date: Tue, 15 Oct 2019 23:02:43 -0000 (UTC)
Injection-Info: novabbs.com; posting-account="retrobbs1"; posting-host="localhost:127.0.0.1";
logging-data="32205"; mail-complaints-to="usenet@novabbs.com"
User-Agent: rslight (http://news.novabbs.com)
X-Spam-Checker-Version: SpamAssassin 3.4.2 (2018-09-13) on novabbs.com
X-Rslight-Site: $2y$10$h/S0LX1ePCpTk9m/IZeQ5uTrUPK3BIgG0VgjWkv5kjTOzuI/V78Au
View all headers
Retro Guy wrote:

Neodome Admin wrote:

Neodome Admin <admin@neodome.net> wrote:
trw <trw@i2pmail.org> wrote:
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.

ALTER TABLE fud30_msg CHARACTER SET utf8 COLLATE utf8mb4_unicode_ci;

Just do DB backup before altering table, of course.


I guess I should clarify. Seems that MySQL is using utf8mb4_unicode_ci by
default but your table have utf8_unicode_ci. You cannot mix them, so you
have to alter table.

It might screw up things in other places though, if your code explicitly
using utf8_unicode_ci.

I do see that this message is (has been imported) into fudforum here: http://novabbs.i2p/index.php?t=msg&th=336564&goto=1960612&#msg_1960612

Notice the subject line is not decoded as it is here:

http://rslight.i2p/compprog/article-flat.php?id=4462&group=comp.lang.python#4462
but it is in the ff database (it imported).

Fudforum seems to roll it's own decode function in include/scripts_common.inc:
function decode_header_value($val)
which should work reasonably well, but does leave some things not decoded.

I wrote a quick test script to show some differences. If you enter an encoded header value, it will display the results from the fudforum function, php's mb_decode_mimeheader. The rslight 'combo' is just a function that runs through both mb_decode_mimeheader and quoted_printable_decode.

Maybe a mod to fudforum's decode function is useful?

Here's the script, just enter an encoded header:

http://rslight.i2p/test/decode.php

Retro Guy

--
Posted on Rocksolid Light



Subject: Re: SQL error
From: Retro Guy
Newsgroups: rocksolid.shared.helpdesk
Organization: Rocksolid Light
Date: Wed, 16 Oct 2019 06:19 UTC
Path: i2pn2.org!rocksolid2!.POSTED.localhost!not-for-mail
From: Retro ...@rslight.i2p (Retro Guy)
Newsgroups: rocksolid.shared.helpdesk
Subject: Re: SQL error
Date: Wed, 16 Oct 2019 06:19:47 -0000 (UTC)
Organization: Rocksolid Light
Message-ID: <dcfe2e27ea8e173c57a19273dc3f2519$1@news.novabbs.com>
References: <qo2rhs$2311$1@neodome.net> <qo4rgj$dcq$1@i2pn2.org> <qo55i2$1bge$1@neodome.net> <qo568o$d21$1@neodome.net> <7d9fba36ee0ab8f122ea2afa3e50f280$1@news.novabbs.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Injection-Date: Wed, 16 Oct 2019 06:19:47 -0000 (UTC)
Injection-Info: novabbs.com; posting-account="retrobbs1"; posting-host="localhost:127.0.0.1";
logging-data="16854"; mail-complaints-to="usenet@novabbs.com"
User-Agent: rslight (http://news.novabbs.com)
X-Spam-Checker-Version: SpamAssassin 3.4.2 (2018-09-13) on novabbs.com
X-Rslight-Site: $2y$10$az0Lkoc8Zf2WFs1hqgyyde7hQKwunNtaef.pvggLyUG/r9ZmyNoXK
View all headers
Retro Guy wrote:

Neodome Admin wrote:

Neodome Admin <admin@neodome.net> wrote:
trw <trw@i2pmail.org> wrote:
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.

ALTER TABLE fud30_msg CHARACTER SET utf8 COLLATE utf8mb4_unicode_ci;

Just do DB backup before altering table, of course.


I guess I should clarify. Seems that MySQL is using utf8mb4_unicode_ci by
default but your table have utf8_unicode_ci. You cannot mix them, so you
have to alter table.

It might screw up things in other places though, if your code explicitly
using utf8_unicode_ci.

I do see that this message is (has been imported) into fudforum here: http://novabbs.i2p/index.php?t=msg&th=336564&goto=1960612&#msg_1960612

Ok, the subject is decoded now on my fudforum (not entirely, but better). I made a minor change to scripts_common.inc at function decode_header_value

I don't know if this would have any impact on your situation but it's strange that my install imported it and yours didn't. I believe we're running the same version.

Retro Guy

--
Posted on Rocksolid Light



Subject: Re: SQL error
From: trww
Newsgroups: rocksolid.shared.helpdesk
Organization: Rocksolid Light
Date: Wed, 16 Oct 2019 18:22 UTC
Path: i2pn2.org!rocksolid2!.POSTED.localhost!not-for-mail
From: trw...@rslight.i2p (trww)
Newsgroups: rocksolid.shared.helpdesk
Subject: Re: SQL error
Date: Wed, 16 Oct 2019 18:22:32 -0000 (UTC)
Organization: Rocksolid Light
Message-ID: <623fda70b344f291c8c958b7f02d6a75$1@dkzerogt6z6ybhcj.onion>
References: <qo2rhs$2311$1@neodome.net> <qo4rgj$dcq$1@i2pn2.org> <qo55i2$1bge$1@neodome.net> <qo568o$d21$1@neodome.net> <7d9fba36ee0ab8f122ea2afa3e50f280$1@news.novabbs.com> <dcfe2e27ea8e173c57a19273dc3f2519$1@news.novabbs.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Injection-Date: Wed, 16 Oct 2019 18:22:32 -0000 (UTC)
Injection-Info: novabbs.com; posting-account="retrobbs1"; posting-host="localhost:127.0.0.1";
logging-data="9980"; mail-complaints-to="usenet@novabbs.com"
User-Agent: rslight (http://news.novabbs.com)
X-Spam-Checker-Version: SpamAssassin 3.4.2 (2018-09-13) on novabbs.com
X-Spam-Level: **
X-Rslight-Site: $2y$10$nQ9ZZwOXfvfw16z495MooeZHZACGT1JivHKZqCcvR9vD1Fz8V041W
View all headers
thanks for both of your help so far.

i need to check on the def3 server on the weekend, some details i do not understand yet. the messages in test have been imported, although without the rocket character in the subject (it was replaced with a "?"). but seems to work now, for some reason.

it is unclear to me why the two instances of ff act differently, but it could be connected with the underlying php versions or ini values for php.

to change the table could maybe correct the error for the one message in question, but create problems for other messages (as you already wrote, too). i think i will try to convert m.subject to a different encoding instead (tried that already, btw, but i must have had some mistake in my code). cheers

trw



--
Posted on Rocksolid Light



Subject: Re: SQL error
From: Retro Guy
Newsgroups: rocksolid.shared.helpdesk
Organization: Rocksolid Light
Date: Thu, 17 Oct 2019 10:13 UTC
Path: i2pn2.org!rocksolid2!.POSTED.localhost!not-for-mail
From: Retro ...@rslight.i2p (Retro Guy)
Newsgroups: rocksolid.shared.helpdesk
Subject: Re: SQL error
Date: Thu, 17 Oct 2019 10:13:18 -0000 (UTC)
Organization: Rocksolid Light
Message-ID: <35591d06758b0e369de8cad348bada1b$1@news.novabbs.com>
References: <qo2rhs$2311$1@neodome.net> <qo4rgj$dcq$1@i2pn2.org> <qo55i2$1bge$1@neodome.net> <qo568o$d21$1@neodome.net> <7d9fba36ee0ab8f122ea2afa3e50f280$1@news.novabbs.com> <dcfe2e27ea8e173c57a19273dc3f2519$1@news.novabbs.com> <623fda70b344f291c8c958b7f02d6a75$1@dkzerogt6z6ybhcj.onion>
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Injection-Date: Thu, 17 Oct 2019 10:13:18 -0000 (UTC)
Injection-Info: novabbs.com; posting-account="retrobbs1"; posting-host="localhost:127.0.0.1";
logging-data="1064"; mail-complaints-to="usenet@novabbs.com"
User-Agent: rslight (http://news.novabbs.com)
X-Spam-Checker-Version: SpamAssassin 3.4.2 (2018-09-13) on novabbs.com
X-Rslight-Site: $2y$10$CBWQVxHCvNWpA3RWHGVax.FxwZrEa2VagSI6Lx7ju4snTGFk1vmHq
View all headers
trww wrote:

thanks for both of your help so far.

to change the table could maybe correct the error for the one message in question, but create problems for other messages (as you already wrote, too). i think i will try to convert m.subject to a different encoding instead (tried that already, btw, but i must have had some mistake in my code).

I'm sure the reasons Neodome provided are correct about the database. It's probably the real cause of the error, but I don't know why it crashes on just that message for you, it should end up just rendering '????' as in other messages.

I did use a test ff site and applied to the database:
ALTER TABLE fud30_msg CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

After this, no messages would import. I had to revert (restore backup) to get the messages imported. So it might (might, because I don't really know) be the case that many things would need to be changed in ff or the db to support utf8mb4.

Note: I am very weak on sql stuff, don't take my comments as if I know what I'm talking about.

Retro Guy
--
Posted on Rocksolid Light



Subject: Re: SQL error
From: trww
Newsgroups: rocksolid.shared.helpdesk
Organization: Rocksolid Light
Date: Thu, 17 Oct 2019 19:47 UTC
Path: i2pn2.org!rocksolid2!.POSTED.localhost!not-for-mail
From: trw...@rslight.i2p (trww)
Newsgroups: rocksolid.shared.helpdesk
Subject: Re: SQL error
Date: Thu, 17 Oct 2019 19:47:53 -0000 (UTC)
Organization: Rocksolid Light
Message-ID: <e928176361e5fd894046101496830721$1@dkzerogt6z6ybhcj.onion>
References: <qo2rhs$2311$1@neodome.net> <qo4rgj$dcq$1@i2pn2.org> <qo55i2$1bge$1@neodome.net> <qo568o$d21$1@neodome.net> <7d9fba36ee0ab8f122ea2afa3e50f280$1@news.novabbs.com> <7ddfabfe10637bfdd2e78aa6953bc568$1@news.novabbs.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Injection-Date: Thu, 17 Oct 2019 19:47:53 -0000 (UTC)
Injection-Info: novabbs.com; posting-account="retrobbs1"; posting-host="localhost:127.0.0.1";
logging-data="2389"; mail-complaints-to="usenet@novabbs.com"
User-Agent: rslight (http://news.novabbs.com)
X-Spam-Checker-Version: SpamAssassin 3.4.2 (2018-09-13) on novabbs.com
X-Spam-Level: **
X-Rslight-Site: $2y$10$H9rCQQWTCgo9xPkUrLxOYeAnAWCs2vZ4Rw1MO6Udx7TEXX0SUhXRe
View all headers

Here's the script, just enter an encoded header:
http://rslight.i2p/test/decode.php

hey, neat, i just saw this now and tried it.

Maybe a mod to fudforum's decode function is useful?

can you post your mod here ?


cheers

trw
--
Posted on Rocksolid Light



Subject: Re: SQL error
From: Retro Guy
Newsgroups: rocksolid.shared.helpdesk
Organization: Rocksolid Light
Date: Thu, 17 Oct 2019 23:53 UTC
Path: i2pn2.org!rocksolid2!.POSTED.localhost!not-for-mail
From: Retro ...@rslight.i2p (Retro Guy)
Newsgroups: rocksolid.shared.helpdesk
Subject: Re: SQL error
Date: Thu, 17 Oct 2019 23:53:17 -0000 (UTC)
Organization: Rocksolid Light
Message-ID: <5c78ed9a316f39236050f949e3509133$1@news.novabbs.com>
References: <qo2rhs$2311$1@neodome.net> <qo4rgj$dcq$1@i2pn2.org> <qo55i2$1bge$1@neodome.net> <qo568o$d21$1@neodome.net> <7d9fba36ee0ab8f122ea2afa3e50f280$1@news.novabbs.com> <7ddfabfe10637bfdd2e78aa6953bc568$1@news.novabbs.com> <e928176361e5fd894046101496830721$1@dkzerogt6z6ybhcj.onion>
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Injection-Date: Thu, 17 Oct 2019 23:53:17 -0000 (UTC)
Injection-Info: novabbs.com; posting-account="retrobbs1"; posting-host="localhost:127.0.0.1";
logging-data="21213"; mail-complaints-to="usenet@novabbs.com"
User-Agent: rslight (http://news.novabbs.com)
X-Spam-Checker-Version: SpamAssassin 3.4.2 (2018-09-13) on novabbs.com
X-Rslight-Site: $2y$10$jhiBQ6RXuKJkjGQqbawmQ.BfcP/.k4farYBcrXN2MhSI2sKdiNfM2
View all headers
trww wrote:


Here's the script, just enter an encoded header:
http://rslight.i2p/test/decode.php

hey, neat, i just saw this now and tried it.

I've found it helpful in troubleshooting some functions. I'll leave it available here: http://rslight.i2p/decoder

Maybe a mod to fudforum's decode function is useful?

can you post your mod here ?

in /var/www/FUDforum/include/scripts_common.inc, find the function 'decode_header_value' and add this:

// Use mbstring to decode if possible
    if(extension_loaded('mbstring')) {
        return quoted_printable_decode(mb_decode_mimeheader($val));
    }

add it here:

function decode_header_value($val)
{
    // Do we need to decoded it?
    if (!preg_match("/=?/", $val)) {
        return trim($val);
    }

    // Use mbstring to decode if possible
    if(extension_loaded('mbstring')) {
        return quoted_printable_decode(mb_decode_mimeheader($val));
    }

    // Convert MIME-encoded text to UTF-8.
    if (function_exists('imap_utf8')) {
        return imap_utf8(trim($val));
    }
    ....

This requires php_mbstring, or these lines will not execute (the function will work as originally written).

Retro Guy

--
Posted on Rocksolid Light



Subject: Re: SQL error
From: trw
Newsgroups: rocksolid.shared.helpdesk
Organization: Dancing elephants
Date: Fri, 18 Oct 2019 13:18 UTC
Path: i2pn2.org!.POSTED!not-for-mail
From: trw...@i2pmail.org (trw)
Newsgroups: rocksolid.shared.helpdesk
Subject: Re: SQL error
Date: Fri, 18 Oct 2019 09:18:34 -0400
Organization: Dancing elephants
Lines: 12
Message-ID: <qoce40$q21$1@i2pn2.org>
References: <5c78ed9a316f39236050f949e3509133$1@news.novabbs.com>
Reply-To: trw <trw@i2pmail.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Injection-Date: Fri, 18 Oct 2019 13:18:57 -0000 (UTC)
Injection-Info: i2pn2.org; posting-account="def.i2p";
logging-data="26689"; mail-complaints-to="usenet@i2pn2.org"
User-Agent: FUDforum 3.0.7
X-FUDforum: 6666cd76f96956469e7be39d750cc7d9 <68688>
View all headers
php_mbstring is a prerequisite for ff as well, if i remember correctly. but this did not change anything for me.
i finally decided to get rid of the character before the subject is checked, as a temporary solution.

inserting this at line 283 in /scripts/nntp.php did the trick:

$msg_post->subject = preg_replace("/[^a-zA-Z0-9\s]/", "", $emsg->subject);

it just eliminates any char not number, letter or whitespace. a very primitive solution that i will use until i have figured it out.


cheers

trw
Posted on def3


1
rocksolid light 0.7.0
clearneti2ptor