MySQL: Fix Error 2013 - Lost connection to MySQL server during query

Case: You have successfully connected to a MySQL/MariaDB database. But when trying to import a large SQL file, an error occurred.

Error Code: 2013. Lost connection to MySQL server during query

Reason:

- Timeout due to slow connection or too much data (Not the server's fault)

- Memory allocated is not enough: the data fields are too large ....

Quick fix:

Increase memory for Mysql package.

Find config file

Eg:

Xampp Windows: C:\xampp\mysql\bin\my.ini

Linux: /etc/my.cnf

Edit file my.ini (my.cnf), find [mysqld] block, to change max_allowed_packet to a larger value (16, 32, or 128 M)

Eg:

[mysqld]
.....
max_allowed_packet=128M

Then restart MySQL

*Note: 

- If max_allowed_packet is not found, you can add it to your [mysqld] block yourself.

- Increasing memory capacity may affect the stability of MySQL server. So you can freely change it on your local computer, but if you are on the product server, please consider carefully. Another solution to importing large mysql files on the server is to use the command line.

Done ! Solved !

Read More

[PHP] Laravel Exception: Malformed UTF-8 characters, possibly incorrectly encoded

This error can be caused from many laravel modules such as Datatables, Json Output, Monolog ... So this is a very difficult error to analyze and fix. In this article we try to find some way to fix that bug in laravel (in the common cases).

Why did you get this error? and how to fix it?

* Case 1: In case of failure due to wrong configuration of database connection

The most common is when you connect to a second database. Developers often have a configuration that lacks the "charset" and "collation" parameters, causing the error as you see it:

Exception Message:↵↵Malformed UTF-8 characters, possibly incorrectly encoded

To fix it please configure the connection parameters are complete.

Eg:

'mysql' => array(
            'driver'    => 'mysql',
            'host'      => 'localhost',
            'database'  => 'database_name_1',
            'username'  => 'user_1',
            'password'  => 'password_1'
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
	    ....
 ),
/*mysql2 if you use two database*/
'mysql2' => array(
            'driver'    => 'mysql',
            'host'      => 'hostForDB2',
            'database'  => 'database_name_2',
            'username'  => 'user_2',
            'password'  => 'password_2'
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
	    ...
),

* Case 2: Regex Error - Caused by some regex function in route, url validate ....

PREG_BAD_UTF8_ERROR 'Malformed UTF-8 characters, possibly incorrectly encoded'

*Case 3: JSON Encode, Json output error - Caused by Monolog, Datatables Logs..

To Fix Case 2 & 3 you can try converting the input to utf-8, or remove non-utf-8 characters.

Eg1: Function to auto convert any string to utf-8 encoding

$newString = mb_convert_encoding($inputString, "UTF-8", "auto");

Eg2: fix utf-8 encoding for Datatables column

$datatable->editColumn('title', function(Post $node) {
            return mb_convert_encoding($node->title,"UTF-8", "auto");
        });

Done ! For any other questions you can leave them in the comment section at the end of this article. Thank you.

Read More