search

xornand / alfresco-business-reporting

Automatically exported from code.google.com/p/alfresco-business-reporting
0 stars 0 forks source link

Mysql maximum row size hit and error not reported #29

Closed GoogleCodeExporter closed 8 years ago

GoogleCodeExporter commented 8 years ago 
1.Define a model that has folder subtype with over 132 text fields or several 
different types that have in total over 132 different text fields.
2.Create a couple of folders of this types
3.Install alfresco reporting
4.Try to harvest all data

The harvesting ends silently, but the "folder" table is not correctly populated 
because the maximum row size for Mysql was hit. This is a general problem, not 
"folder" specific.

I know the problem is row size because I debugged the module and I even managed 
to get the screenshot attached here, but the Alfresco log did not report 
anything.

So the first an easily fixable problem is that database exceptions shouldn't be 
swallowed like that, they should be at least logged and preferably the user 
should be notified. I think this error happens in line 750 of AlfrescoReporting 
class, when calling extendTable.

The second and not so easy to fix problem is the row limit itself.

This happens to me with around 60 fields, you don't need the 132 I stated but 
with 132 will happen for sure because each field is represented as a 
VARCHAR(500) and the row size limit in mysql is around 65500 bytes. I'm using 
UTF-8 so probably each field is already taking 1500bytes 
(http://dev.mysql.com/doc/refman/5.5/en/column-count-limit.html)

The only "easy" way I see to solve this problem is creating alfresco text  
fields as TEXT. TEXT fields take a fixed small size (12 bytes) in the row 
because the data itself is not in the row. With this change no limit should be 
hit until well over 5000 fields. Not sure though if there will be any 
significant performance drop due to this change.

Original issue reported on code.google.com by igor...@gmail.com on 13 Nov 2013 at 12:48

Attachments:

GoogleCodeExporter commented 8 years ago 
Are you sure you use Alfresco in a correct manner? It si a document management 
system right, not a replacement for a database system :-)

I agree on not giving a nice meaningful exception...

Having said that, this is foreseen behaviour. Look at 
https://code.google.com/p/alfresco-business-reporting/wiki/AdvancedInstallation#
Exclude_properties_from_appearing_in_the_reporting_database  you can either 
exclude columns from the reporting tables, or tweak the size of individual 
columns. Each Alfresco text field claims a VARCHAR(700). You can adjust this on 
the level of an individual property name.

Then again... Did you model this number of properties against a single Alfresco 
type?? Please also consider splitting your business objects over multiple 
tables. Either on TYPE or on having a particular aspect (or not).  This way you 
limit the exotic number of properties/columns in a single table. 

Remind that the setup as I ship it is a default one, working for all initial 
Alfresco installs. For this number of properties/columns it does not make sense 
to maintain a table like 'folders' or 'documents'. Please create meaningful 
tables for business objects that define your domain. That is what the tool is 
made for!

Please let me know if this allows you to continue working... I will not fix the 
error message in the older versions. I will consider it in the current version, 
although I am not sure about the impact. (I will create a new ticket for this 
particular issue then...)

Original comment by tjarda.p...@incentro.com on 13 Nov 2013 at 1:06

GoogleCodeExporter commented 8 years ago 
Hello Tjarda:

First of all thanks for such a fast and diligent answer. 

The Alfresco where I'm trying is a development Alfresco and I have a mix of 
models from different tests and developments, that's why I have such a lot of 
models. I'm aware that Alfresco is not the replacemente for a database, in fact 
quite often I have to explain this to customers who pretend to use it like this.

But IMHO in quite customized installations having over a dozen different types 
or aspects with 4 to 6 metadata fields doesn't seem such a strange scenario. 
But this can probably be easily handled with the configuration options you 
pointed in the link, I wasn't aware of this information.

By the way, thak you very much for all the hard work behind this project. I'm 
still in early testing it but I find the approach of taking data to a "normal" 
precalculation database really useful.

Original comment by igor...@gmail.com on 13 Nov 2013 at 2:08