CODEWITHSUNDEEP
doctrine-ormsymfony
bluppfisk
bluppfisk

Reputation: 2652

Doctrine: data too long for column (due to escaping)

I got the following error:

The 'name' column is 255 characters long, but it seems that output escaping messes it up. What did I do wrong?

An exception occurred while executing 'INSERT INTO parts (deletedAt, name, code, price, in_stock) VALUES (?, ?, ?, ?, ?)' with params [null, "\x50\x6f\x73\x74\x4e\x4c\x20\x50\x72\x69\x6f\x72\x69\x74\x79\x20\x50\x61\x6b\x6b\x65\x74\x20\x41\x61\x6e\x67\x65\x74\x65\x6b\x65\x6e\x64\x20\x42\x65\x6c\x67\x69\xeb", "1101012", "12.00", 0]:

SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'name' at row 1 500 Internal Server Error - DriverException 2 linked Exceptions: PDOException » PDOException »

        foreach ($csv as $row) { #persist every part
            $part = new Part();
            $part->setCode($row[0]);
            $part->setName($row[1]);
            $part->setPrice(preg_replace("/[^0-9.]/","", $row[2])); # extract decimal number
            $part->setInStock(($row[3])?1:0);

            $em = $this->getDoctrine()->getManager();
            $em->persist($part);
            $count++; # get number of commits
        }

        $em->flush();

from my my.ini:

[client]
port = 3306
socket = /tmp/mysql.sock
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4
no-auto-rehash

[mysqld]
port = 3306
collation-server        = utf8mb4_unicode_ci
init-connect            = 'SET NAMES utf8mb4'
character-set-server    = utf8mb4
skip-character-set-client-handshake

Upvotes: 2

Views: 1482

Answers (1)

bluppfisk
bluppfisk

Reputation: 2652

So it turned out there were two problems.

One was that Doctrine did not respect the default collation set by the MySQL server, and even a myriad of options in config.yml etc. Finally I have it looking like this, and I don't really understand why. But it works now.

charset:  utf8mb4
default_table_options:
    charset: utf8mb4
    collate: utf8mb4_unicode_ci
options:
    charset: utf8mb4
    collate: utf8mb4_unicode_ci
    1002: "SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci'"

Second was that the source of the data I was trying to load into the database (a csv file) was apparently not in utf8. Saving it into "utf8" solved the problem. Now I wonder how I can make php convert the input into the desired format.

Upvotes: 1

Related Questions