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.
Show-Stopper Database Problem!
-
- Posts: 7525
- Joined: Sun Apr 24, 2005 12:36 am
- Contact:
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.
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.
Aware IM Support Team
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?
You say plain text attributes are either VARCHAR or TEXT. What makes the distinction, size?
-
- Posts: 7525
- Joined: Sun Apr 24, 2005 12:36 am
- Contact:
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)
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)
Aware IM Support Team
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 IM 7.1 - Windows/MS SQL
-
- Posts: 7525
- Joined: Sun Apr 24, 2005 12:36 am
- Contact:
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?
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?
Rod. Aware 8.6 (latest build), Developer Edition, on OS Linux (Ubuntu) using GUI hosted on AWS EC2, MYSQL on AWS RDS
-
- Posts: 7525
- Joined: Sun Apr 24, 2005 12:36 am
- Contact:
-
- Posts: 197
- Joined: Fri Jun 17, 2016 7:10 am
- Location: Brisbane Australia
- Contact:
Re: Show-Stopper Database Problem!
I think i found a resolution to this problem on MYSQL.
1. Add the mysql parameters to your my.cnf or equivalent file
2. ALTER the table to use ROW_FORMAT=COMPRESSED. Where BSV_TABLE is the name of your mysql table.
[/list]
Please tell me if I'm wrong before I find out the hard way
1. Add the mysql parameters to your my.cnf or equivalent file
Code: Select all
innodb_file_per_table
innodb_file_format = Barracuda
Code: Select all
ALTER TABLE BSV_TABLE
ENGINE=InnoDB
ROW_FORMAT=COMPRESSED
KEY_BLOCK_SIZE=8;
Please tell me if I'm wrong before I find out the hard way
AWS Linux, Windows Server, AIM 8.4 & 8.6
-
- Posts: 7525
- Joined: Sun Apr 24, 2005 12:36 am
- Contact:
Re: Show-Stopper Database Problem!
No idea. You could be right...
Backup your database and try.
Backup your database and try.
Aware IM Support Team
-
- Posts: 197
- Joined: Fri Jun 17, 2016 7:10 am
- Location: Brisbane Australia
- Contact:
Re: Show-Stopper Database Problem!
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."
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."
Re: Show-Stopper Database Problem!
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.
V8.8
MySQL, AWS EC2, S3
PDFtk Toolkit
MySQL, AWS EC2, S3
PDFtk Toolkit
Re: Show-Stopper Database Problem!
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.
Rod. Aware 8.6 (latest build), Developer Edition, on OS Linux (Ubuntu) using GUI hosted on AWS EC2, MYSQL on AWS RDS