Show-Stopper Database Problem!

[kklosson]

The nature of the data I am working with is causing my row size to exceed the 8k byte max row size limitation in MySQL. There's just a lot of text fields and a lot of narrative entries. 8k bytes is about 3 pages of text - I need more. MySQL will let you define column sizes that exceed the 8k limit but once you try to enter a record that exceeds the limit, it throws an error.

Any sort of database restructuring will not result in a logical presentation. I don't want to go there.

In my readings on various forums, I am learning that MSSQL also has the same row length limitation but can overcome when using VARCHAR(MAX) data types. I'm looking for some expert answers here, especially from Aware_Support if you can help. I do not know how AwareIM is data typing Plain Text attributes. I need to know if I'm essentially going to hit the same wall if I move to MSSQL from MySQL. I'm certainly willing to do it if that can solve my problem.

[aware_support]

Aware IM uses MySQL INNODB tables, which seem to have a limitation of 8K per row.

However, this limit does NOT include VARCHAR, TEXT and BLOB field types. All Aware IM attributes of the PlainText type use either VARCHAR or TEXT field types, whereas Document and Picture types use BLOB's. This means that an instance of your object should not exceed 8K not counting Plain Text, Document and Picture attributes.

This leaves numbers, dates, duration and single references (which are internally implemented as integer foreign keys).

I can't imagine an object that uses 8K worth of numbers and dates!

If you do need such an object and restructuring is out of the question you need to use a different database which doesn't have the 8K restriction. Check out SQL Server - I don't know whether it has any restrictions on this.

[kklosson]

Thanks for the reply. I've opted for restructuring the schema after learning more on how I can present data from other objects. I will pursue that. So this is now somewhat academic, but my BO has a little over 100 attributes. Many of them are plain text type for narrative input. But you're saying that the plain text attributes would not be counting against the 8k limit. I find otherwise because it is purely the increase of data in a plain text attribute field that presents the error.

You say plain text attributes are either VARCHAR or TEXT. What makes the distinction, size?

[aware_support]

Yes, it's the length of the attribute that you define for the PlainText attribute - if it's greater than certain value it is mapped to TEXT, otherwise VARCHAR. TEXT is definitely not counted towards this limit, otherwise everyone would be complaining about this.

If you believe MySQL documentation, VARCHAR shouldn't be counted either - your tests suggest the opposite, so maybe you shouldn't believe their documentation or maybe it's a MySQL bug (which could be fixed in some later releases - so maybe you just need to update your MySQL version)

[weboo]

I have this problem as well. I have around 10 plain text fields (1500 chars each) for narrative entries. I searched the web for solutions and someone suggested splitting the table into two, others said switch the engine to MyISAM. What is the effect on my application if I switch the storage engine to MyISAM?

[aware_support]

You CANNOT change the MySQL engine to MyISAM - Aware IM will not work with it. You either need to restructure your objects or try using a different database engine.

[weboo]

Thanks for your response. I'll restructure my objects

[RLJB]

When trying to save System Settings we keep getting this error:

Internal system errorTransaction exception Error persisting business object SystemSettings Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.


Is this related to the issues in this post?

If so, we don't have anything special (or large) in our System Settings, what would be causing this?

[aware_support]

You are probably saving the value that is too large - check the values in System Settings

[UnionSystems]

I think i found a resolution to this problem on MYSQL.

1. Add the mysql parameters to your my.cnf or equivalent file

Code: Select all

innodb_file_per_table
innodb_file_format = Barracuda

2. ALTER the table to use ROW_FORMAT=COMPRESSED. Where BSV_TABLE is the name of your mysql table.

Code: Select all

ALTER TABLE BSV_TABLE
    ENGINE=InnoDB
    ROW_FORMAT=COMPRESSED 
    KEY_BLOCK_SIZE=8;

[/list]

Please tell me if I'm wrong before I find out the hard way

[aware_support]

No idea. You could be right...

Backup your database and try.

[UnionSystems]

Seems to have worked

[Gabbitas]

UnionSystems, Thanks. This worked for me too.

Whilst researching this problem I found the following:

" It's important to note that even if you use TEXT or BLOB fields, your row size could still be over 8K (limit for InnoDB) because it stores the first 768 bytes for each field inline in the page.

The simplest way to fix this is to use the Barracuda file format with InnoDB. This basically gets rid of the problem altogether by only storing the 20 byte pointer to the text data instead of storing the first 768 bytes."

[kklosson]

Since you updated the topic (that I started), I'll state that I am wiser now and have restructured my objects to normalize better and keep table sizes as trim as possible. At the time I posted this, I was not familiar enough with AwareIM to do so. The restructuring was the right answer and I had to do a few things to implement it. But not bad.

[RLJB]

FYI - we have changed one or two tables (not the whole DB engine) to MyISAM to avoid this error and been operating fine for a few years doing this.