Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion

osTicket v1.10 (stable) and Maintenance Release v1.9.15 are now available! Go get it now

DB Error #1366

Hi,

I've been struggling with this problem for days now, tried everything possible, and googled more than anyone could ever google :)

What is the problem?
Someone tries to send an email to [email]support@xxxx.com[/email] with an Arabic subject, when osTicket tries to fetch the message, the following error is sent to my admin email:

[INSERT INTO ost_ticket SET created=NOW() ,ticketID=162903,dept_id=4,topic_id='0',priority_id=2,email='xxxxxxx',name='xxxxxxxxxx',subject='���� �� ��� �����!!',helptopic='',phone="",phone_ext='',ip_address='xxxxxxxxxx',source='Email']

Incorrect string value: 'xD4xDFxE6xEC xE3...' for column 'subject' at row 1


While xxx are good data I changed for privacy concerns.

What version am I using?
Latest, the one got posted last month.

MySQL schema?
Also updated, checked to have the UTF support.. Edit manually to test, no problem!

More tests to make sure 100% it's only when sent from an email?
1. Tried to create a ticket using the form, with an Arabic subject, no problem at all.
2. Tried to send an email with an English subject, and Arabic body, also NO problem.

Mailers?
All.. I've tried gmail, hotmail.. same same!

Summery
This problem only presitst when the sender's subject is in Arabic.

Would appreciate any help.

Thanks.

Comments

  • Help!

    Please help, it's urgent :(
  • Hi ihijazi,

    I had a similar problem with only certain emails coming from certain servers. My error message read like this:

    Incorrect string value: 'xA0 Plea...' for column 'message' at row 1

    I believe this all has something to do with character encoding. My solution was to change the SQL query that is causing the error like so:

    File: class.ticket.php
    Line: around 613 in v1.6
    Action: change the "message=" line to this

    ',message='.db_input(Format::striptags(preg_replace("/\xa0/"," ",preg_replace("/\s/"," ",str_ireplace("\xA0"," ",str_ireplace("=A0"," ",str_replace(chr(160)," ",$msg))))))). //Tags/code stripped...meaning client can not send in code..etc

    However I'm not sure a solution similar to this will work for you. It seems you have many characters that are encoded funny. I only had "A0" causing me problems. But hopefully this will help you find your own solution. And hopefully the developers will get this email craziness nailed down in future releases.

    Good luck!

    Mike
    ________
    marijuana strain strawberry cough
  • Still..

    Mike,

    Thanks for your reply mate. I appreciate it!!

    This didn't work though. What's driving me INSANE is it's working FINE with the message body itself as a whole in Arabic!! It goes crazy ONLY when the subject of the message is in English. And like I got a LOT of potential Arabic clients.

    :/

    Issam
  • Why don't you try some php check, that convert English name to something else ?
    or just completely remove the name & add a constant string, after that append the english name inside the body of message.

    for that you have to modify the code.

    I also tried to use this system for my customers & I have some troubles with this, but unfortunately i didn't find any support from here.
  • Issam,

    Undo the fix by Mike and apply attached patch - simply replace the affected files.

    Please let us know the outcome.
    mailenc-patch01.zip
    17K
  • :)

    PERFECT!

    That worked perfectly Peter!!

    Thanks a lot, once again :)
  • Hi Peter,

    Is this patch something I should try as well? What does it fix/address? I know my "fix" is sloppy at best.
    ________
    Honda CBR1100XX specifications
  • mrceolla;14115 said:

    Is this patch something I should try as well? What does it fix/address? I know my "fix" is sloppy at best.
    The patch fixes encoding issues on the subject for incoming and outgoing emails - Your fix won't work for all possible encoding, but it's totally up to you if you want to patch your system.
  • I'm only having encoding issues in my message body. Any idea why this is? I have changed my DB to UTF8_general_ci as was suggested in other threads, but that changed nothing.

    Might I have to recreate the DB from scratch in UTF8? Or is this problem not related to the DB?
    ________
    marijuana seeds
  • Thaks

    Work perfect for me :)

    Muito obrigado e cade brasileiros aqui agradecendo ???
  • Thank you!

    It was making me crazy! but this solve it

    Saludos desde Colombia :)
  • I had to change the format-settings of the ticket-mailbox to "Text only" on the exchange server 2007. this fixed the problem for me.
  • Não Brasileiro mas...
    ygorsoares;19605 said:
    Muito obrigado e cade brasileiros aqui agradecendo ???
    Oi... eu não sao Brasileiro mas eu mora em Floripa, estou aprendir Português agora então desculpe para mal gramaticá!

    If you come across further charset problems I have put in place some extensive fixes on our installation so feel free to ask :cool:
  • Polish chars and 'To view the message, please use an HTML compatible email viewer!'

    Hi,
    i had the problem with polish letters in subject and body text, and patch from peter saved me. good job!. i have moved osticket from test to prod and launch support :)
    BUT i have additional problems:
    1) still pipe is not working correctly with polish letters and after a lot of code analysing the problem seems is in pipe.php file. Peter fixed only problems with imap/pop3 fetching. unfortenuatly i am not programmer and tried small modification in code but without effect. I think that functions in pipe.php should be rewrited as for class.mailfetch.php. maybe there is something wrong in class.mailfetch.php too, but i think that major encoding is done on pipe.php level. Anyway proper encoding should be done before database insertion. BTW i have utf8_generali_ci and it work ok with polish chars.
    2) i have had one additional problems with e-mails in html. from my webform and forward settings i received e-mails in html. and my hosting seems to use X-Mailer: PHPMailer 5.1 (phpmailer.worxware.com) functionality to send e-mail. and such e-mail have Content-Type: multipart/alternative; boundary="b1_d61***********************" - 2 sections in boundary:

    --b1_d61*****************************
    Content-Type: text/plain; charset = "utf-8"
    Content-Transfer-Encoding: 8bit

    To view the message, please use an HTML compatible email viewer!


    --b1_d61****************************
    Content-Type: text/html; charset = "utf-8"
    Content-Transfer-Encoding: 8bit

    Ze strony ........

    Wysyla:

    Wiadomość: test po ustawieniach skrzynki

    --b1_d61*******************************--

    and osticket took body from first one as default and i had in osticket in message body text: To view the message, please use an HTML compatible email viewer!
    and the real one message in second section of boundary is missed (not parsed at all). again, i am not programmer but i 'solved' the problem just by 'making simple' function 'function getBody' in class.mailfetch.php and class.mailparse.php by commenting // properly lines: if(!($body=$this->getPart($this->struct,'text/plain'))) { and related to this line mark }
    i think i forse osticket to take and work with 'text/html' part of body and put this content into database. and this is my solution for now. But i lost possiblity to receive text plain e-mails.
    i think this function should be rewrited somehowe. Original condition check if 'text/plain' is not empty and take 'To view the message, please use an HTML compatible email viewer!' but it is not real content, just output from mailer. Maybe some other mailers put content to both sections and to avoid doubling data the function getBody look like in osticket 1.6ST, but in my case it work wrongly. better to add additional condition to check if there is sentance 'To view the message, please use an HTML compatible email viewer!' and then go 'text/html' part? if someone have the same problem or have the better solution :) please reply!

    best regards ;)
    /////////////////////////////////////
    ok, after some lecture about php and home work, i have wrote my own function and seems it works! :)

    function getBody($mid) {

    //$problem = 'To view the message, please use an HTML compatible email viewer!';

    $body ='';
    if(($this->getpart($mid,'TEXT/HTML',$this->charset)) != '')
    {
    $body = $this->getpart($mid,'TEXT/HTML',$this->charset);
    //Convert tags of interest before we striptags
    $body=str_replace("
    ", "\n", $body);
    $body=str_replace(array("
    ", "
    ", "
    ", "
    "), "\n", $body);
    $body=Format::striptags($body); //Strip tags??
    }
    elseif (!($body = $this->getpart($mid,'TEXT/PLAIN',$this->charset)))
    {
    return $body;
    }
    return $body;
    }
  • Peter or any other user, what has been edited in this patch file to make sure these arabic signs are accepted? Would this solve my problem for the symbol ° too?
  • DB Error #1366

    Hi

    OS Ticket Server : windows 2008 R2 with Apache 2.2
    Email server : Exchange 2010

    I have still the same error #1366 even applying the patch : mailenc-patch01.zip.

    Someone have the solution ?

    Thanks
  • same issue

    I'm having the same issue, except my error is: Incorrect string value: '\xA0\xA0\xA0\xA0\xA0\xA0...' for column 'message' at row 1

    The fix didn't work for me - I assume because though the subject line was fixed, "message" was not.

    Please help :-(
  • I would guess its a character encoding issue. You should probably try the solutions in this thread:

    http://osticket.com/forums/showthread.php?t=1162&highlight=umlaut
  • no luck

    no luck, but thank you for the suggestion. do you do pay support? We're dead in the water and I don't have a clue.
This discussion has been closed.