Error "Data truncation: Data too long for column"

1 How to find the table and column details?
2 How to find the problematic form and field name?
3 Solution 1: Increase Column Size in OpenSpecimen Database
4 Solution 2: Change the Column Data Type (only MySQL DB)

This issue will be fixed in v13.x

Error: SQLException:ORA-12899: value too large for column ""OS_CATISSUEPROD"".""DE_E_13710"".""DE_A_114"" (actual: 309, maximum: 255)

The issue arises when the value received from REDCap or OpenClinica exceeds the character limit of the corresponding field created in OpenSpecimen.

Example:
If the field BX_RESULT is defined as a Text Box in REDCap/OpenClinica, the corresponding field in OpenSpecimen is created as VARCHAR2(255). When the value for BX_RESULT exceeds 255 characters, the OpenSpecimen sync fails.

The solution depends on the expected maximum length of data stored in the REDCap field:

If the value is expected to be ≤ 4096 characters, use Solution 1.
If the value is expected to be > 4096 characters, use Solution 2 (MySQL databases only).

How to find the table and column details?

Download the error log file from the sync → Check the error message for the table and column

How to find the problematic form and field name?

You can find the form and field name using the SQLs via DB console. Refer to the wiki page for more details.

The changes should be done in the OpenSpecimen database by the person who manages and has access to the DB.

Solution 1: Increase Column Size in OpenSpecimen Database

This will increase the character limit of the column.

For the VARCHAR data type, the maximum character limit is
- 4K for Oracle
- 64K for MySQL.

Run the SQL command in the database. ALTER TABLE <table_name> MODIFY COLUMN <column_name> VARCHAR(<desired_max_length>); Rerun the synchronisation for the record in REDCap or OpenClinica.

Solution 2: Change the Column Data Type (only MySQL DB)

If the data in the column is going to be more than 4K, the column type needs to be updated from VARCHAR to TEXT.

Run the SQL command in the database. ALTER TABLE <table_name> MODIFY COLUMN <column_name> TEXT; Rerun the synchronisation for the record in REDCap or OpenClinica.