Rocksolid Light

Welcome to novaBBS (click a section below)

mail  files  register  newsreader  groups  login

Message-ID:  

Phasers locked on target, Captain.


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

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

1
SQL error

<qnfaf5$5s8$1@i2pn2.org>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=31&group=rocksolid.shared.helpdesk#31

 copy link   Newsgroups: rocksolid.shared.helpdesk
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>
 by: trw - 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

Re: SQL error

<2f0b1fa44fb103b4e17cd15cc9088571$1@news.novabbs.com>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=32&group=rocksolid.shared.helpdesk#32

 copy link   Newsgroups: rocksolid.shared.helpdesk
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.
 by: Retro Guy - 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

Re: SQL error

<qnhvh4$mtl$1@i2pn2.org>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=33&group=rocksolid.shared.helpdesk#33

 copy link   Newsgroups: rocksolid.shared.helpdesk
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>
 by: trw - 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 
Re: SQL error

<qnuqd5$557$1@i2pn2.org>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=35&group=rocksolid.shared.helpdesk#35

 copy link   Newsgroups: rocksolid.shared.helpdesk
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>
 by: Guest - 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

Re: SQL error

<qnv22s$eu0$1@i2pn2.org>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=36&group=rocksolid.shared.helpdesk#36

 copy link   Newsgroups: rocksolid.shared.helpdesk
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>
 by: trw - 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

Re: SQL error

<9e4bb59398be47dd4972eec565cd2c81$1@www.rocksolidbbs.com>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=37&group=rocksolid.shared.helpdesk#37

 copy link   Newsgroups: rocksolid.shared.helpdesk
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]
 by: Retro Guy - 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

Re: SQL error

<qo2j9o$5fm$1@neodome.net>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=38&group=rocksolid.shared.helpdesk#38

 copy link   Newsgroups: rocksolid.shared.helpdesk
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=
 by: Neodome Admin - 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

Re: SQL error

<59e1d0e427e736cd3c0cc06c878cc8fa$1@z5bqfv5v75kxy7pj.onion>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=39&group=rocksolid.shared.helpdesk#39

 copy link   Newsgroups: rocksolid.shared.helpdesk
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"
 by: anonymous - 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

Re: SQL error

<qo2rhs$2311$1@neodome.net>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=40&group=rocksolid.shared.helpdesk#40

 copy link   Newsgroups: rocksolid.shared.helpdesk
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=
 by: Neodome Admin - 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

Re: SQL error

<qo4rgj$dcq$1@i2pn2.org>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=41&group=rocksolid.shared.helpdesk#41

 copy link   Newsgroups: rocksolid.shared.helpdesk
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>
 by: trw - 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

Re: SQL error

<qo55i2$1bge$1@neodome.net>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=42&group=rocksolid.shared.helpdesk#42

 copy link   Newsgroups: rocksolid.shared.helpdesk
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=
 by: Neodome Admin - Tue, 15 Oct 2019 19:09 UTC

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

Re: SQL error

<qo568o$d21$1@neodome.net>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=43&group=rocksolid.shared.helpdesk#43

 copy link   Newsgroups: rocksolid.shared.helpdesk
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=
 by: Neodome Admin - Tue, 15 Oct 2019 19:22 UTC

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

Re: SQL error

<7d9fba36ee0ab8f122ea2afa3e50f280$1@news.novabbs.com>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=44&group=rocksolid.shared.helpdesk#44

 copy link   Newsgroups: rocksolid.shared.helpdesk
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
 by: Retro Guy - Tue, 15 Oct 2019 22:22 UTC

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

Re: SQL error

<7ddfabfe10637bfdd2e78aa6953bc568$1@news.novabbs.com>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=45&group=rocksolid.shared.helpdesk#45

 copy link   Newsgroups: rocksolid.shared.helpdesk
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
 by: Retro Guy - Tue, 15 Oct 2019 23:02 UTC

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

Re: SQL error

<dcfe2e27ea8e173c57a19273dc3f2519$1@news.novabbs.com>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=46&group=rocksolid.shared.helpdesk#46

 copy link   Newsgroups: rocksolid.shared.helpdesk
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
 by: Retro Guy - Wed, 16 Oct 2019 06:19 UTC

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

Re: SQL error

<623fda70b344f291c8c958b7f02d6a75$1@dkzerogt6z6ybhcj.onion>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=47&group=rocksolid.shared.helpdesk#47

 copy link   Newsgroups: rocksolid.shared.helpdesk
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
 by: trww - Wed, 16 Oct 2019 18:22 UTC

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

Re: SQL error

<35591d06758b0e369de8cad348bada1b$1@news.novabbs.com>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=48&group=rocksolid.shared.helpdesk#48

 copy link   Newsgroups: rocksolid.shared.helpdesk
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
 by: Retro Guy - Thu, 17 Oct 2019 10:13 UTC

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

Re: SQL error

<e928176361e5fd894046101496830721$1@dkzerogt6z6ybhcj.onion>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=49&group=rocksolid.shared.helpdesk#49

 copy link   Newsgroups: rocksolid.shared.helpdesk
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
 by: trww - Thu, 17 Oct 2019 19:47 UTC

>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

Re: SQL error

<5c78ed9a316f39236050f949e3509133$1@news.novabbs.com>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=50&group=rocksolid.shared.helpdesk#50

 copy link   Newsgroups: rocksolid.shared.helpdesk
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
 by: Retro Guy - Thu, 17 Oct 2019 23:53 UTC

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

Re: SQL error

<qoce40$q21$1@i2pn2.org>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=51&group=rocksolid.shared.helpdesk#51

 copy link   Newsgroups: rocksolid.shared.helpdesk
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>
 by: trw - Fri, 18 Oct 2019 13:18 UTC

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
server_pubkey.txt

rocksolid light 0.9.7
clearnet tor