Suddenly MYSQL "Malformed packet" on LOAD DATA LOCAL INFILE after two years of working

Question

This has been a nightly job working for two years. PHP file imports a txt file into MYSQL database. Suddenly today getting Malformed Packet when importing a TXT file. I noticed the server has only been up 23 hours, so I think GoDaddy could have updated something.

MYSQL Ver = 5.6.43-cll-lve

local_infile = ON

Without "local" I get error, "Access denied for user", I've tried granting FILE but having issues getting mysql CLI access via SSH (never needed it before) and PHP MY Admin doesn't have access rights.

I've spent an entire day on this, and can't figure out what to do. I have tried setting the file to 777 as well.

I've tried removing the truncate part and just doing the import, culling the import file down to 3 lines, using old import files that worked before-- can't make heads or tails.

require('config_dev.php');
$path = '/home/pro/public_html/upload/IDUpload_dev.txt';
$mysqli = new mysqli($hostname,$username, $password, $dbname);

if ($mysqli->connect_error) {
  die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error);
}

$sql1 = "TRUNCATE TABLE Accounts;";

if (!$mysqli->query($sql1)) {
    echo "\nQuery execute failed: ERRNO: (" . $mysqli->errno . ") " . $mysqli->error;
} else {
    echo ("Truncated<br>");
}

$sql2 = "LOAD DATA LOCAL INFILE '".$path."' INTO TABLE Accounts
        FIELDS TERMINATED BY ','
        LINES TERMINATED BY '\n'
        IGNORE 1 LINES
        (acct,type,zip)";

if (!$mysqli->query($sql2)) {
    echo "\nQuery execute failed: ERRNO: (" . $mysqli->errno . ") " . $mysqli->error;
} else {
    echo ("Imported.");
}

mysqli_close($mysqli);

Answer 1

To use LOAD DATA INFILE LOCAL the MYSQLI_OPT_LOCAL_INFILE needs to be enabled before connection.

You should SHOW GLOBAL VARIABLES LIKE 'local_infile' to see if this is enabled on the server which is also required.

Answer 2

Make sure to give the current user privilege to access files:

Login to mysql first:

mysql -u root -p

then

GRANT FILE ON *.* TO 'username'@'localhost';

Answer 3

I had the same issue, a script that worked fine for 3 years and suddenly starts to fail with "Malformed packet". I am running on an OVH hosted-server with a private database.

Sadly, none of the solutions worked (GRANT or MYSQLI_OPT_LOCAL_INFILE).

After fiddling around, I noticed that the script worked on the test database on the same server!

I compared the two databases configuration, and the only difference I could find was on the database collation.

• On the test database, I had SELECT @@collation_database == latin1_swedish_ci
• On the production database which was no longer working, I had SELECT @@collation_database == utf8_unicode_ci

I changed the collation of the production database...

ALTER DATABASE DB_PROD_NAME CHARACTER SET latin1 COLLATE latin1_swedish_ci;

And it worked! (even though all tables uses utf8_unicode_ci)

Well, it works but all characters are mangled.

But it forces me to look into encoding issue, which lead me to the proper solution.

Using LOAD DATA LOCAL INFILE ... CHARACTER SET 'utf8mb4' ... I was able to get the real error:

ERROR : Incorrect string value: '\xF0\x9F\x93\x8B' for column ...

The \xF0\x9F\x93\x8B value is actually a perfectly fine UTF8-encoded character: the clipboard emoji

However, it is defined on 4 bytes (like most emojis).

As stated earlier, my tables and columns use the collation utf8_unicode_ci. So it should be good right? Except... not! The utf8 implementation of mySQL is deeply flawed as it only allows to represents UTF8 up to 3 bytes! See these links for more background.

The new and correct modern implementation of UTF8 in mySQL is called utf8mb4.

So the final solution was simply to migrate all tables and columns to the utf8mb4_unicode_ci collation and voila! Problem solved.

In the end, the biggest problem was understanding what exactly this very misleading "Malformed packet" error message meant.

Answer 4

In my case the solution was really simple.

I totally agree with @danblack answer.

I added this line to my PHP script:

@mysqli_options($con, MYSQLI_OPT_LOCAL_INFILE, true);

Just before the Insert script.