CakePHP: "string data, right truncated: 1406 data too long for column at row 1"

Question

I have a PHP web application developed with CakePHP 4 an MySQL.

One of the databases the application works with is a table named trg_registros_vip that contains 77 columns. One of the columns is named trg_tipo_proyecto and is defined as a non-nullable varchar with a maximum length of 100 characters.

The table is populated by reading an excel file with SimpleXLSX (a library obtainable as shuchkin/simplexlsx by using composer). For each row in the file, starting from the second row (as the first one has the headers for the sheet), the data on each cell from left to right, starting from the first cell, is stored in an entity class named TRegistrosVip in the same order as defined for each column in the table starting from the second (since the first column is an autoincremental primary key). Then the entity is inserted in the table. The column trg_tipo_proyecto is the fifth one in the table and the data for this column is obtained from fourth column in the excel file.

However, when I try to read the excel from the application, I get the following error message: Error: [PDOException] SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'trg_tipo_proyecto' at row 1 in C:\xampp\htdocs\CPSC2\vendor\cakephp\cakephp\src\Database\Statement\MysqlStatement.php

The stacktrace for this error indicates that the error is generated in the very moment I try to insert one row in the table.

I searched in the excel file if there is a cell in the fourth column whose length is greater than 100 and got none.

I searched the table class associated to the entity class, named TRegistrosVipTable in case of a bad defined column and I got this:

$validator->scalar('trg_tipo_proyecto')
          ->maxLength('trg_tipo_proyecto', 100)
          ->requirePresence('trg_tipo_proyecto', 'create')
          ->notEmptyString('trg_tipo_proyecto');

That means the column is well defined.

I check the config/app.php file in case I would be working with a wrong database but I'm using the correct one.

Also, due to possible presence of foreign accents, I called the utf8_decode function on each value of that column in the excel file before inserting it on the table.

Considering the described above, what would be the cause of this error? Thanks in advance.

Try dumping/logging the data that you're trying to insert, so you can see exactly what data is being sent. — aynber
I implented logging in the application (it was not implemente before). The last value obtained from the excel is the word Regularización without leading and trailing spaces. This value obviously has a length lower than 100 and has an accent. The log is called after the call to utf8_decode — mrcoar
Dumb question, but are you absolutely sure that this column has a max length of 100 in the database? You're not looking at a similar-named column in a different table, or looking at a different copy of the database than Cake is referencing? — Greg Schmidt
I checked the TrgRegistrosVipTable again. The $table attribute is initialized with the string value 't_registros_vip I also checked the defaultConnectionName method and it returns the correct database. I checked the column in the database and it has a max length of 100 — mrcoar

mrcoar mrcoar · Accepted Answer · 2021-06-17T19:01:14

SOLVED

I implemented logging in the application and printed in the debug log file the value in the excel file's fourth column for each row after the call to the utf8_decode function. The error is triggered when trying to insert the word "Regularización" (without the double quotes, without leading or trending spaces and with an accent).

I removed the accent from that word in the Excel and the error dissappeared.

However, I cannot remove the accents from the Excel programmatically because client's restrinctions forbid it. So, I tested removing the call to utf8_decode and inserting the value directly without any codification or decodification. The error is not triggered anymore.

Thanks to all who have read and answered this question.

CakePHP: "string data, right truncated: 1406 data too long for column at row 1"

1 Answers