Vishal Shetty
Reputation: 1668
General error: 1366 Incorrect string value in Laravel/Mysql
I have designed laravel app to fetch mails using php imap. In kernel I have written code to fetch all mailboxes, below is my code for fetching mails from mailbox
<?php
namespace App\Console;
use Illuminate\Console\Scheduling\Schedule;
use Illuminate\Foundation\Console\Kernel as ConsoleKernel;
use Illuminate\Http\Request;
use App\Models\Email;
use App\Models\Inbox;
use App\Models\Spam;
use App\Models\Sent;
use App\Models\Drafts;
use App\Models\Trash;
use App\Models\Junk;
use App\Models\Attachments;
use App\Models\Errorlogs;
use PhpImap\Mailbox as ImapMailbox;
use PhpImap\IncomingMail;
use PhpImap\IncomingMailAttachment;
class Kernel extends ConsoleKernel
{
protected $commands = [
//
];
public function saveMail($result,$mailboxType,$mailbox,$stream,$j,$accountId,$appUrl)
{
$i=0;
while(1)
{
try
{
if(isset($result[$i]))
{
$mail=$mailbox->getMail($result[$i],false);
$status = imap_headerinfo($stream,$j);
$status = ( $status->Unseen == 'U') ? 'unread' : 'read';
if($mailboxType=="Inbox")
$mbVar = new Inbox;
elseif($mailboxType=="Sent")
$mbVar = new Sent;
elseif($mailboxType=="Spam")
$mbVar = new Spam;
elseif($mailboxType=="Drafts")
$mbVar = new Drafts;
elseif($mailboxType=="Trash")
$mbVar = new Trash;
elseif($mailboxType=="Junk")
$mbVar = new Junk;
$mbVar->accountId = $accountId;
$mbVar->flag = 'NULL';
$mbVar->mailId = $mail->id;
$mbVar->date = $mail->date;
$mbVar->subject = $mail->subject;
$mbVar->fromName = $mail->fromName;
$mbVar->fromAddress = $mail->fromAddress;
$mbVar->to = implode(",",$mail->to);
$mbVar->cc = implode(",",$mail->cc);
$mbVar->replyTo = implode(",",$mail->replyTo);
$mbVar->textPlain = $mail->textPlain;
$mbVar->textHtml = $mail->textHtml;
$mbVar->status = $status;
$mbVar->save();
$attachments = $mail->getAttachments();
if($attachments!=NULL)
{
foreach($attachments as $attachmentData)
{
if(!is_numeric($attachmentData->id))
break;
$attachment = new Attachments;
$attachment->mailId = $mbVar->id;
$attachment->mailboxType = $mailboxType;
$attachment->attachmentId = $attachmentData->id;
$attachment->fileName = $attachmentData->name;
$attachment->filePath = $appUrl."/storage/mail-attachments/".basename($attachmentData->filePath);
$attachment->save();
}
}
$i++;
}
else
break;
}
catch(\Exception $err)
{
$errorLog = new Errorlogs;
$errorLog->errormsg = $err->getMessage();
$errorLog->save();
}
}
}
protected function schedule(Schedule $schedule)
{
$schedule->call(function (Request $request)
{
$mailBox=Email::get();
foreach ($mailBox as $data)
{
try
{
$mbox = imap_open("{saniservice.com:993/imap/ssl}", $data->email,$data->password, OP_HALFOPEN)
or die("can't connect: " . imap_last_error());
$list = imap_getmailboxes($mbox, "{saniservice.com:993/imap/ssl}", "*");
$j=1;
if(is_array($list))
{
foreach ($list as $key => $val)
{
$mailbox = new ImapMailbox($val->name,$data->email,$data->password,storage_path().'/mail-attachments');
$mailIds = $mailbox->searchMailbox('ALL');
$stream = imap_open($val->name, $data->email,$data->password);
if($val->name=="{saniservice.com:993/imap/ssl}INBOX")
{
$dbmailIds=Inbox::where('accountId',$data->id)->pluck('mailId')->toArray();
$result=array_values(array_diff($mailIds,$dbmailIds));
$this->saveMail($result,'Inbox',$mailbox,$stream,$j,$data->id,$request->url());
}
elseif ($val->name=="{saniservice.com:993/imap/ssl}INBOX.spam")
{
$dbmailIds=Spam::where('accountId',$data->id)->pluck('mailId')->toArray();
$result=array_values(array_diff($mailIds,$dbmailIds));
$this->saveMail($result,'Spam',$mailbox,$stream,$j,$data->id,$request->url());
}
elseif ($val->name=="{saniservice.com:993/imap/ssl}INBOX.Drafts")
{
$dbmailIds=Drafts::where('accountId',$data->id)->pluck('mailId')->toArray();
$result=array_values(array_diff($mailIds,$dbmailIds));
$this->saveMail($result,'Drafts',$mailbox,$stream,$j,$data->id,$request->url());
}
elseif ($val->name=="{saniservice.com:993/imap/ssl}INBOX.Sent")
{
$dbmailIds=Sent::where('accountId',$data->id)->pluck('mailId')->toArray();
$result=array_values(array_diff($mailIds,$dbmailIds));
$this->saveMail($result,'Sent',$mailbox,$stream,$j,$data->id,$request->url());
}
elseif ($val->name=="{saniservice.com:993/imap/ssl}INBOX.Junk")
{
$dbmailIds=Junk::where('accountId',$data->id)->pluck('mailId')->toArray();
$result=array_values(array_diff($mailIds,$dbmailIds));
$this->saveMail($result,'Junk',$mailbox,$stream,$j,$data->id,$request->url());
}
elseif ($val->name=="{saniservice.com:993/imap/ssl}INBOX.Trash")
{
$dbmailIds=Trash::where('accountId',$data->id)->pluck('mailId')->toArray();
$result=array_values(array_diff($mailIds,$dbmailIds));
$this->saveMail($result,'Trash',$mailbox,$stream,$j,$data->id,$request->url());
}
}
}
imap_close($mbox);
}
catch(\Exception $e)
{
$errorLog = new Errorlogs;
$errorLog->errormsg = $e->getMessage();
$errorLog->save();
}
}
})->everyMinute()->name('mailfetch')->withoutOverlapping();
}
protected function commands()
{
require base_path('routes/console.php');
}
}
I have several email id's and password stored in table, the above code will loop (say outer loop) and it will fetch each mail credential , will connect to webmail and get all list of folders i.e inbox/sent/spam/trash/drafts/junk etc
Now for each folder say for inbox, it will again loop (say inner loop) and will fetch all mails that aren't in table already.
If I don't use try catch and any error occurs the complete process halts, that's why I used try catch for both inner and outer loop.
Now if I execute code and if fetched mail has no error then it will be added to appropriate table and if there is an error then it will be added to errorlog table.
for some mails I am getting error message like this
[PDOException]
SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xA0|\xA0 Up.
..' for column 'errormsg' at row 1
ErrorLog Table Structure
Mysql Version is : 5.7.18
This shows that there is no support for the data , that I am trying to add into table .
but I see that collation is fine and I have also used longtext to store the content.
I have searched for solution on this on internet but mostly solution is answered 7-8 years back, and I think my sql version has changed a lot from the time and none fo that worked for me
I am also not sure whether, should I change longtext datatype to LongBlob.
[Illuminate\Database\QueryException]
SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xA0|\xA0 Up.
..' for column 'errormsg' at row 1 (SQL: insert into `errorlogs` (`errormsg
`, `updated_at`, `created_at`) values (SQLSTATE[HY000]: General error: 1366
Incorrect string value: '\xA0|\xA0 Up...' for column 'errormsg' at row 1 (
SQL: insert into `errorlogs` (`errormsg`, `updated_at`, `created_at`) value
s (SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xA0|\xA0
Up...' for column 'textPlain' at row 1 (SQL: insert into `inbox` (`accountI
d`, `flag`, `mailId`, `date`, `subject`, `fromName`, `fromAddress`, `to`, `
cc`, `replyTo`, `textPlain`, `textHtml`, `status`, `updated_at`, `created_a
t`) values (3, NULL, 1678, 2013-03-20 00:03:08, DHL delivery report, Cruz D
aniels, [email protected], , , Cruz Daniels, /* Mobile-specific Style
s */@media only screen and (max-device-width: 480px) { table[class=w0], td[
class=w0] { width: 0 !important; }table[class=w10], td[class=w10], img[clas
s=w10] { width:10px !important; }table[class=w15], td[class=w15], img[class
=w15] { width:5px !important; }table[class=w30], td[class=w30], img[class=w
30] { width:10px !important; }table[class=w60], td[class=w60], img[class=w6
0] { width:10px !important; }table[class=w125], td[class=w125], img[class=w
125] { width:80px !important; }table[class=w130], td[class=w130], img[class
=w130] { width:55px !important; }table[class=w140], td[class=w140], img[cla
ss=w140] { width:90px !important; }table[class=w160], td[class=w160], img[c
lass=w160] { width:180px !important; }table[class=w170], td[class=w170], im
g[class=w170] { width:100px !important; }table[class=w180], td[class=w180],
img[class=w180] { width:80px !important; }table[class=w195], td[class=w195
], img[class=w195] { width:80px !important; }table[class=w220], td[class=w2
20], img[class=w220] { width:80px !important; }table[class=w240], td[class=
w240], img[class=w240] { width:180px !important; }table[class=w255], td[cla
ss=w255], img[class=w255] { width:185px !important; }table[class=w275], td[
class=w275], img[class=w275] { width:135px !important; }table[class=w280],
td[class=w280], img[class=w280] { width:135px !important; }table[class=w300
], td[class=w300], img[class=w300] { width:140px !important; }table[class=w
325], td[class=w325], img[class=w325] { width:95px !important; }table[class
=w360], td[class=w360], img[class=w360] { width:140px !important; }table[cl
ass=w410], td[class=w410], img[class=w410] { width:180px !important; }table
[class=w470], td[class=w470], img[class=w470] { width:200px !important; }ta
ble[class=w580], td[class=w580], img[class=w580] { width:280px !important;
}table[class=w640], td[class=w640], img[class=w640] { width:300px !importan
t; }table[class*=hide], td[class*=hide], img[class*=hide], p[class*=hide],
span[class*=hide] { display:none !important; }table[class=h0], td[class=h0]
{ height: 0 !important; }p[class=footer-content-left] { text-align: center
!important; }#headline p { font-size: 30px !important; }.article-content,
#left-sidebar{ -webkit-text-size-adjust: 90% !important; -ms-text-size-adju
st: 90% !important; }.header-content, .footer-content-left {-webkit-text-si
ze-adjust: 80% !important; -ms-text-size-adjust: 80% !important;}img { heig
ht: auto; line-height: 100%;} } /* Client-specific Styles */#outlook a { pa
dding: 0; } /* Force Outlook to provide a "view in browser" button. */body
{ width: 100% !important; }.ReadMsgBody { width: 100%; }.ExternalClass { wi
dth: 100%; display:block !important; } /* Force Hotmail to display emails a
t full width *//* Reset Styles *//* Add 100px so mobile switch bar doesn't
cover street address. */body { background-color: #dedede; margin: 0; paddin
g: 0; }img { outline: none; text-decoration: none; display: block;}br, stro
ng br, b br, em br, i br { line-height:100%; }h1, h2, h3, h4, h5, h6 { line
-height: 100% !important; -webkit-font-smoothing: antialiased; }h1 a, h2 a,
h3 a, h4 a, h5 a, h6 a { color: blue !important; }h1 a:active, h2 a:active
, h3 a:active, h4 a:active, h5 a:active, h6 a:active { color: red !importa
nt; }/* Preferably not the same color as the normal header link color. The
re is limited support for psuedo classes in email clients, this was added j
ust for good measure. */h1 a:visited, h2 a:visited, h3 a:visited, h4 a:vis
ited, h5 a:visited, h6 a:visited { color: purple !important; }/* Preferably
not the same color as the normal header link color. There is limited suppo
rt for psuedo classes in email clients, this was added just for good measur
e. */ table td, table tr { border-collapse: collapse; }.yshortcuts, .yshor
tcuts a, .yshortcuts a:link,.yshortcuts a:visited, .yshortcuts a:hover, .ys
hortcuts a span {color: black; text-decoration: none !important; border-bot
tom: none !important; background: none !important;} /* Body text color for
the New Yahoo. This example sets the font of Yahoo's Shortcuts to black. *
//* This most probably won't work in all email clients. Don't include bloc
ks in email. */code { white-space: normal; word-break: break-all;}#backgr
ound-table { background-color: #dedede; }/* Webkit Elements */#top-bar { bo
rder-radius:6px 6px 0px 0px; -moz-border-radius: 6px 6px 0px 0px; -webkit-b
order-radius:6px 6px 0px 0px; -webkit-font-smoothing: antialiased; backgrou
nd-color: #c7c7c7; color: #ededed; }#top-bar a { font-weight: bold; color:
#ffffff; text-decoration: none;}#footer { border-radius:0px 0px 6px 6px; -m
oz-border-radius: 0px 0px 6px 6px; -webkit-border-radius:0px 0px 6px 6px; -
webkit-font-smoothing: antialiased; }/* Fonts and Content */body, td { font
-family: 'Helvetica Neue', Arial, Helvetica, Geneva, sans-serif; }.header-c
ontent, .footer-content-left, .footer-content-right { -webkit-text-size-adj
ust: none; -ms-text-size-adjust: none; }/* Prevent Webkit and Windows Mobil
e platforms from changing default font sizes on header and footer. */.heade
r-content { font-size: 12px; color: #ededed; }.header-content a { font-weig
ht: bold; color: #ffffff; text-decoration: none; }#headline p { color: #444
444; font-family: 'Helvetica Neue', Arial, Helvetica, Geneva, sans-serif; f
ont-size: 36px; text-align: center; margin-top:0px; margin-bottom:30px; }#h
eadline p a { color: #444444; text-decoration: none; }.article-title { font
-size: 18px; line-height:24px; color: #b0b0b0; font-weight:bold; margin-top
:0px; margin-bottom:18px; font-family: 'Helvetica Neue', Arial, Helvetica,
Geneva, sans-serif; }.article-title a { color: #b0b0b0; text-decoration: no
ne; }.article-title.with-meta {margin-bottom: 0;}.article-meta { font-size:
13px; line-height: 20px; color: #ccc; font-weight: bold; margin-top: 0;}.a
rticle-content { font-size: 13px; line-height: 18px; color: #444444; margin
-top: 0px; margin-bottom: 18px; font-family: 'Helvetica Neue', Arial, Helve
tica, Geneva, sans-serif; }.article-content a { color: #2f82de; font-weight
:bold; text-decoration:none; }.article-content img { max-width: 100% }.arti
cle-content ol, .article-content ul { margin-top:0px; margin-bottom:18px; m
argin-left:19px; padding:0; }.article-content li { font-size: 13px; line-he
ight: 18px; color: #444444; }.article-content li a { color: #2f82de; text-d
ecoration:underline; }.article-content p {margin-bottom: 15px;}.footer-cont
ent-left { font-size: 12px; line-height: 15px; color: #ededed; margin-top:
0px; margin-bottom: 15px; }.footer-content-left a { color: #ffffff; font-we
ight: bold; text-decoration: none; }.footer-content-right { font-size: 11px
; line-height: 16px; color: #ededed; margin-top: 0px; margin-bottom: 15px;
}.footer-content-right a { color: #ffffff; font-weight: bold; text-decorati
on: none; }#footer { background-color: #c7c7c7; color: #ededed; }#footer a
{ color: #ffffff; text-decoration: none; font-weight: bold; }#permission-re
minder { white-space: normal; }#street-address { color: #b0b0b0; white-spac
e: normal; }.article-content ol, .article-content ul { margin: 0 0 0 24px
; padding: 0; list-style-position: inside;}
Web Version ?|? Update preferences??|? Unsubscribe
DHL notification
Our company?s courier couldn?t make the delivery of
parcel. REASON: Postal code contai
ns an error. LOCATION OF YOUR PA
RCEL: New York DELIVERY STATUS:
sort order SERVICE: One-day Ship
ping NUMBER OF YOUR PARCEL: ETBA
KPRSU3 FEATURES: No
Label is enclosed to the letter.
Print a label and show it at your post office
. An additional information:
If the parcel isn?t received within 15
working days our company will have the right to claim compensation from you
for it?s keeping in the amount of $8.26 for each day of keeping of it.
You can find the information about th
e procedure and conditions of parcels keeping in the nearest office.
Thank you for using our services.
DHL Global
Edit your subscript
ion | Unsubscribe
, error in content, read, 2017-07-08 08:24:20, 2017-07-08 08:24:20)), 2017-
07-08 08:24:20, 2017-07-08 08:24:20)), 2017-07-08 08:24:21, 2017-07-08 08:2
4:21))
[PDOException]
SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xA0|\xA0 Up.
..' for column 'errormsg' at row 1
If I directly insert the same content into table from phpmyadmin it is accepting without any errors then why not from laravel.
Upvotes: 2
Views: 6552
Answers (1)
FosAvance
Reputation: 2469
My problem was that I use substr() instead of mb_substr(). My collation was utf8mb4_unicode_ci
Upvotes: 7
Related Questions
- Getting error when sending an email in Laravel 4.2
- LARAVEL 7 :ErrorException Undefined index
- Geting an error of 220 in laravel for mail function
- Laravel 5: Error in Sending Email
- Laravel - General error: 1366 Incorrect integer value
- Laravel mail sending returns error
- Error when sending data to Email Template in Laravel 5.1
- Retrieving Emails Using Laravel and IMAP,failed to pass variable to view
- PHP Imap - Character not allowed in mailbox name: '.'
- Laravel error : email