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: Re: SQL error
From: admin@neodome.net (Neodome Admin)
Newsgroups: rocksolid.shared.helpdesk
Organization: Neodome
Date: 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


Subject: Re: SQL error
From: admin@neodome.net (Neodome Admin)
Newsgroups: rocksolid.shared.helpdesk
Organization: Neodome
Date: 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


Subject: Re: SQL error
From: Retro Guy@rslight.i2p (Retro Guy)
Newsgroups: rocksolid.shared.helpdesk
Organization: Rocksolid Light
Date: 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



Subject: Re: SQL error
From: Retro Guy@rslight.i2p (Retro Guy)
Newsgroups: rocksolid.shared.helpdesk
Organization: Rocksolid Light
Date: 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



Subject: Re: SQL error
From: Retro Guy@rslight.i2p (Retro Guy)
Newsgroups: rocksolid.shared.helpdesk
Organization: Rocksolid Light
Date: 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



Subject: Re: SQL error
From: trww@rslight.i2p (trww)
Newsgroups: rocksolid.shared.helpdesk
Organization: Rocksolid Light
Date: 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



Subject: Re: SQL error
From: Retro Guy@rslight.i2p (Retro Guy)
Newsgroups: rocksolid.shared.helpdesk
Organization: Rocksolid Light
Date: 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



Subject: Re: SQL error
From: trww@rslight.i2p (trww)
Newsgroups: rocksolid.shared.helpdesk
Organization: Rocksolid Light
Date: 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



Subject: Re: SQL error
From: Retro Guy@rslight.i2p (Retro Guy)
Newsgroups: rocksolid.shared.helpdesk
Organization: Rocksolid Light
Date: 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



Subject: Re: SQL error
From: trw@i2pmail.org (trw)
Newsgroups: rocksolid.shared.helpdesk
Organization: Dancing elephants
Date: 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


Pages:12
rocksolid light 0.6.5e
clearnet i2p tor