Support Topics Documentation Slack YouTube Blog

Can't write data: Data truncation: Data too long for column

We have a brand new XCode app that builds and runs and can read/write single pieces of data to Backendless.

However, we are trying to populate 9 rows of data (3 of one class and 6 of another) and we get a message “Item Error: Data truncation: Data too long for column ‘itemDescription’ at row 1”. There are two classes and about 20 lines of code

Edit: I narrowed this down a bit and updated the code in this post.

This issue occurs when the string being written is > 36 characters. e.g. if the string being written is 37 characters it fails, 36 characters works.

The line that prints the error includes our text

print("Item Error: (fault.message ?? "")") //error occurs here

Backendless Version (3.x / 5.x, Online / Managed / Pro )

BackendlessSwift: 5.7.6

Client SDK (REST / Android / Objective-C / Swift / JS )

Swift
macOS 10.15.3
XCode 11.3.1

Application ID

7CFF6F39-692B-D149-FF34-CE74B7A90500

Expected Behavior

Please describe the expected behavior of the issue, starting from the first action.
We expect data to be written without errors.

Actual Behavior

Please provide a description of what actually happens, working from the same starting point.
When data is written, part of the data writes, the rest of the data fails to write.,

with the below code, the invoice objects are all written successfully. However, the item objects fail to write and throw the error.

There isn’t any other code in the app other than this write and the initializaiton code - which we know works because if we comment out either the invoice or the item writing section it works with the other.

Reproducible Test Case

    let inv0 = InvoiceClass(invNum: "inv 0", custName: "Company A")
    let inv1 = InvoiceClass(invNum: "inv 1", custName: "Company B")
    let inv2 = InvoiceClass(invNum: "inv 2", custName: "Company C")

    let i0 = ItemClass(itemNum: "AAAAAAA", 
                       itemDesc: "123456789_123456789_123456789_1234567",
                       price: 1149.95) //37 chars fail
    let i1 = ItemClass(itemNum: "BBBBBBB", 
                       itemDesc: "123456789_123456789_123456789_123456", 
                       price: 12999.95) //36 chars ok


    let invoices = [inv0, inv1, inv2]

    let invoiceDataStore = Backendless.shared.data.of(InvoiceClass.self)
    invoiceDataStore.createBulk(entities: invoices, responseHandler: { savedObjects in
        print("Invoice Objects saved")
    }, errorHandler: { fault in
        print("Invoice Error: (fault.message ?? "")")
    })

    let items = [i0, i1]
    let itemDataStore = Backendless.shared.data.of(ItemClass.self)
    itemDataStore.createBulk(entities: items, responseHandler: { savedObjects in
        print("Item Objects saved")
    }, errorHandler: { fault in
        print("Item Error: (fault.message ?? "")") //error occurs here
    })

and then two classes

@objcMembers class InvoiceClass: NSObject {
    var objectId: String?
    var invoiceNumber = ""
    var customerName = ""
    
    convenience init(invNum: String, custName: String) {
        self.init()
        self.invoiceNumber = invNum
        self.customerName = custName
    }
}

@objcMembers class ItemClass: NSObject {
    var objectId: String?
    var itemNumber = ""
    var itemDescription = ""
    var itemPrice = 0.0
    
    convenience init(itemNum: String, itemDesc: String, price: Double) {
        self.init()
        self.itemNumber = itemNum
        self.itemDescription = itemDesc
        self.itemPrice = price
    }
}

Hi @jason-wooten

Our engineer will take a look at the problem as soon as possible.
However, have you tried to use our REST Console in Data Section to create these objects?

Regards, Vlad

The objects can be individually created in both the app as well as the console. Additionally, If we comment out the item creation code, all of the invoices are created without error and vice-versa; if we comment out the invoice creation code, the items are created.

The issue lies when both the three invoices as the six items are created within the same code block.

I spent some time troubleshooting this further and the code in the original question worked as I truncated the itemDesc strings in the post to save space.

To duplicate the issue, the itemDesc string needs to be greater than 36 characters.

so this

let i1 = ItemClass(itemNum: "BBBB", itemDesc: "123456789_123456789_123456789_123456", price: 12999.95) //36 chars

will work but this

let i1 = ItemClass(itemNum: "BBBB", itemDesc: "123456789_123456789_123456789_1234567", price: 12999.95) //37 chars

will fail

Hi Jay,

Could you please check what that column definition looks like in console?

Regards,
Mark

Oh - one more thing. This issue only seems to occur when the table doesn’t exist yet e.g. it only fails upon first write (creation) of that table. If I run the code once, it fails, but if I quit the app and run it again (because the table then exists) it works.

So that column definition is:

STRING | MAX LENGTH: AUTO

and a screenshot when it’s 36 characters in length. If itemDesc is 37 chars, it fails and the ItemClass is created with the same definition but no data.

Could you please also attach a screenshot of the SCHEMA tab (that’s where the actual column definition is). And the CONFIGURATION tab too.

Thanks,
Mark

We will investigate the use case with automatic column creation. You can edit string column definition and get rid of the autosize property (which may impact performance for apps with a lot of data). It is recommended to set the column width to accommodate the largest string you may ever get.

How do we turn that off/set that from the API in Swift? We didn’t see it in the guide - I am sure we overlooked it as we are still going through the docs.

During development we’re constantly deleting and updating object schema so it’s a frequent process.

As I pointed out here, schema management functions are not part of our SDK for Swift (or Android/JS/.NET/Flutter).

Regards,
Mark

This was marked as ‘Solved’ but we need some additional direction during development if we select Backendless as our enterprise solution to migrate to.

As pointed out in another thread

The Swift SDK has no functionality for creating/deleting/renaming tables and columns

But creating columns and tables is currently part of the SDK so does that mean it is going to be removed?

For planning and evaluation purposes the messaging is unclear as the code we’ve written so far creates tables and columns as laid out in the documentation but that statement says it’s not supported. Saving Data Objects

Backendless creates tables automatically, however, if you prefer, you can create your tables and define the schema using Backendless console.

For evaluation purposed we need to understand the roadmap - is the Dynamic Schema Definition feature going to be removed? The documentation clearly says it should be turned off for performance reasons

It is recommended to disable this option for performance reasons

but it’s really critical (for us) during development as ‘performance’ is low importance and convenience is high importance.

Additionally, is it correct to say that Backendless models should only be created in the console with autosize turned off and column widths set to accommodate the largest string?

Just trying to get direction here as if that’s the case it obviously impacts development cycles as it’s a lot more work to craft structures and relationships between models in the console - every time a change is needed it will involve logging into the web interface, making the change the match the updated code, models etc.

Thank you

Let me try to clarify the confusion. The creation of tables and columns happens within the platform itself, it is entirely the backend feature. It happens only if the “Dynamic schema definition” option is turned on. Again, this functionality is not part of the SDK (which we consider the client-side), it is part of the backend.

The dynamic schema definition is not going to be removed.

It is recommended that the schema is defined within Backendless console. This way you have greater control over what the columns are defined as. For example, if the columns are created through “dynamic schema definition”, there is no way to specify various options such as the size of the String columns, indexes, default values, validators, etc. However, if this level of detail is not important at this point, you can drive schema creation/modification through your code.

Please ask any questions you might have. We want you to feel comfortable with the platform and find the most optimal way to develop with it.

Regards,
Mark

Hello @jason-wooten

We just updated our cloud servers by fixing the problem described in your first post. Could you tell us if the fix works well?

Regards,
Inna

I would say it’s kind of fixed and something else is broken.

When I run the exact same code, I now get this message

Item Error: Table ‘ItemClass’ must be present and it schema must be fully configured before bulk create.

Invoice Error: Table ‘InvoiceClass’ must be present and it schema must be fully configured before bulk create.

I checked my console and Enable dynamic schema definition is still turned on but doesn’t appear to be working.

If I use the console and manually create each table and schema and run the code it appears to work.

However, that’s super inefficient during development as every time there’s a change to an object (table), it will have to be manually deleted, recreated and have schema added. With 50 properties per object (table) and 30 tables, that’s obviously not going to work.

Hi @jason-wooten,

I believe the bulkCreate call requires that the schema is present. What you can do is issue an API call to save one object first, which will create the schema, followed by the bulkCreate call.

Regards,
Mark

@mark-piller

Ok, thanks for that info.

Keep in mind that I am running the exact same code as shown in the original question - no changes at all. That bulk create error didn’t appear previously when that code was used and successfully created the table on the fly using the bulkCreate function (as long as it was <= 36 chars)

The good news is that we can write more than 36 characters to a field so at least that was fixed.

Thanks, @jason-wooten. I will reopen the internal ticket to investigate the behavior of bulkCreate.

Regards,
Mark

Is this issue still being investigated?

Hi, Jayson

We discussed the behaviour of bulkCreate.
It will not take into consideration Dynamic Schema option in order to ensure high performance (as you may expect from bulk operations).
As Mark said before, you may save one object with the properties you need before calling bulk methods.

Also

  • the default column length for type STRING is 250 chars now.
  • the autosize will be removed (but will stay on already create columns), and we recommend to disable it at all.