Too many fields in a report causes an error

[Carl Leitner]

Firstly the TANGE report i mean staff directory was having the following
elements:
Firstname, Middlename, Surname, Birth data, Designation Title, Sex, Date
of first appointment, Facility, Facility type, Diocese, Employer, Terms
of Employment, Work Telephone, Work Email, Place of Domicile/Residence

It was working ok.

When i added the following:
------------------
Firstname, Middlename, Surname, Birth data, Designation Title, Sex, Date
of first appointment, Facility, Facility type, Diocese, Employer, Terms
of Employment, Work Telephone, Work Email, Place of Domicile/Residence
-------------

Checked on the Report View

Qualification, Marital Status, Check Number, Personal File Number,
Region, Institution Name, Inst/Location, Course Name, Pension Scheme,
Leave Type, Leave Duration, Next of Kin, Next of Kin Relationship,
Registration Board, Confirmation Date into Service, Retirement Year.

and Generated the reports then it failed with the message:
 Native message: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs

[Carl Leitner (litlfred)]

Full error message is:

firstname` (255)),INDEX `person+othername` (`person+othername` (255)),INDEX `person+relg_affil` (`person+relg_affil` (255)),INDEX `person+residence` (`person+residence` (255)),INDEX `person+surname` (`person+surname` (255)),INDEX `position+department` (`position+department` (255)),INDEX `position+facility` (`position+facility` (255)),INDEX `position+job` (`position+job` (255)),INDEX `position+employer` (`position+employer` (255)),INDEX `position+pos_type` (`position+pos_type` (255)),INDEX `position+supervisor` (`position+supervisor` (255)),INDEX `position+title` (`position+title` (255)),INDEX `start_salary+start_date` (`start_salary+start_date`),INDEX `start_salary+salary` (`start_salary+salary` (255))) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
[Wed Aug 03 02:57:52 2011] [error] [client 196.46.109.199]
 Refered by : http://nz.csschris.or.tz/manage-northern/index.php/CustomReports/edit/reports
 I2CE: I2CE->raiseError (/var/lib/iHRIS/lib/4.0.15/I2CE/modules/CustomReports/lib/I2CE_CustomReport.php:469)
       I2CE_CustomReport->_generateCache (/var/lib/iHRIS/lib/4.0.15/I2CE/modules/CustomReports/lib/I2CE_CustomReport.php:437)
       I2CE_CustomReport->generateCache (/var/lib/iHRIS/lib/4.0.15/I2CE/modules/CustomReports/lib/I2CE_Page_CustomReports.php:348)
       I2CE_Page_CustomReports->actionGenerate (/var/lib/iHRIS/lib/4.0.15/I2CE/modules/CustomReports/lib/I2CE_Page_CustomReports.php:143)
       I2CE_Page_CustomReports->action (/var/lib/iHRIS/lib/4.0.15/I2CE/modules/Pages/lib/I2CE_Page.php:464)
       I2CE_Page->display (/var/lib/iHRIS/lib/4.0.15/I2CE/modules/Pages/lib/I2CE_Wrangler.php:89)
       I2CE_Wrangler->wrangle (/var/lib/iHRIS/cssc_zonal_deploy/sites/northern/pages/index.php:60)
 Doing INSERT IGNORE INTO `manage_cssc_northern`.`tmp_custom_report_staff_list[21]` SELECT `base_table`.`primary_form+id` as `primary_form+id`,`base_table`.`primary_form+parent` as `primary_form+parent`,`base_table`.`primary_form+end_date` as `primary_form+end_date`,`base_table`.`primary_form+position` as `primary_form+position`,`base_table`.`primary_form+start_date` as `primary_form+start_date`,`base_table`.`primary_form+reason` as `primary_form+reason`,`base_table`.`current_salary+id` as `current_salary+id`,`base_table`.`current_salary+parent` as `current_salary+parent`,`base_table`.`current_salary+end_date` as `current_salary+end_date`,`base_table`.`current_salary+salary` as `current_salary+salary`,`base_table`.`person+id` as `person+id`,`base_table`.`person+parent` as `person+parent`,`base_table`.`person+nationality` as `person+nationality`,`base_table`.`person+firstname` as `person+firstname`,`base_table`.`person+othername` as `person+othername`,`base_table`.`person+relg_affil` as `person+relg_affil`,`base_table`.`person+residence` as `person+residence`,`base_table`.`person+surname` as `person+surname`,`base_table`.`position+id` as `position+id`,`base_table`.`position+parent` as `position+parent`,`base_table`.`position+department` as `position+department`,`base_table`.`position+facility` as `position+facility`,`base_table`.`position+job` as `position+job`,`base_table`.`position+employer` as `position+employer`,`base_t...

[Carl Leitner (litlfred)]

We will have to investigate this. A possible workaround is to create two reports and use the "merging" feature. Note: this feature has not been fully tested lately.

[Carl Leitner (litlfred)]

The row size limit is something enforced by MySQL, so even changing the table type will not solve the problem. See:
   http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html

[Carl Leitner (litlfred)]

Data Storage Requirements are defined here:
  http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html
According to we get the following desired row size:
  varchars (255) has 89 instances at 4*255 bytes each
 datetime has 8 instances at 8 bytes each
 blob has 1 instance at 12 bytes
 binary(16) has 2 instances at 16 bytes each
 int (11) has 2 instances at 4 bytes each
which amounts to 90896 bytes which far exceeds the 65,535 byte limit.

One possible solution:
  There are 18 varchars(255) that also have an index on them... these are either the id , parentid, or those that we have a report limit associated to it. I suggest we change all the varchars (255) which do not have a key to text which requires only 12 bytes each. Doing so for this report we should have:
 varchars (255) has 18 instances at 4*255 bytes each
 text has 71 instances at 12 bytes each
 datetime has 8 instances at 8 bytes each
 blob has 1 instance at 12 bytes
 binary(16) has 2 instances at 16 bytes each
 int (11) has 2 instances at 4 bytes each
which should result in 19328 bytes.

[Carl Leitner (litlfred)]

Dear Mathayo,

I think this has been fixed by revision 2590 of I2CE.

Do you have a test server which you can try this out on? If so can you EITHER

1) use the development code and test it out

OR

2) make a backup copy of the file I2CE/modules/CustomReports/lib/I2CE_CustomReport.php that you are using,
    download the following file:
     http://bazaar.launchpad.net/~intrahealth+informatics/i2ce/4.0-dev/view/head:/modules/CustomReports/lib/I2CE_CustomReport.php
   to replace I2CE/modules/CustomReports/lib/I2CE_CustomReport.php

[josephat mathayo (jmathayo)]

Dear Carl,
Thanks for your concept. I will appreciate if we can sort out the
display of unlimited fields to the reports.

I am waiting to here from you.

Thanks
On 8/11/11, Carl Leitner <email address hidden> wrote:
> We will have to investigate this. A possible workaround is to create
> two reports and use the "merging" feature. Note: this feature has not
> been fully tested lately.
>
> ** Also affects: pmoralg
> Importance: Undecided
> Status: New
>
> ** Also affects: ihris-manage-cssc
> Importance: Undecided
> Status: New
>
> --
> You received this bug notification because you are subscribed to the bug
> report.
> https://bugs.launchpad.net/bugs/824598
>
> Title:
> Too many fields in a report causes an error
>
> To manage notifications about this bug go to:
> https://bugs.launchpad.net/i2ce/+bug/824598/+subscriptions
>

--
======================================
ICT Officer
Mathayo Josephat
Christian Social Services Commission
AfyaMtandao / Tanzania Human Resource Project
P.O Box 9433
Dar es Salaam
Tanzania.

Email: <email address hidden> /<email address hidden>
Mobile: + 255 754 940 636 / 712 191 192
website:
www.cssc.or.tz
www.afyamtandao.org
======================================

[josephat mathayo (jmathayo)]

Dear Carl,
I will test in my laptop in wknd time. because Currently i am
conducting training at APHFTA.
I will update the revision and see if it works once i add more fields
than we have been using.

Thanks

On 8/11/11, Carl Leitner <email address hidden> wrote:
> Dear Mathayo,
>
> I think this has been fixed by revision 2590 of I2CE.
>
> Do you have a test server which you can try this out on? If so can you
> EITHER
>
> 1) use the development code and test it out
>
> OR
>
> 2) make a backup copy of the file
> I2CE/modules/CustomReports/lib/I2CE_CustomReport.php that you are using,
> download the following file:
>
> http://bazaar.launchpad.net/~intrahealth+informatics/i2ce/4.0-dev/view/head:/modules/CustomReports/lib/I2CE_CustomReport.php
> to replace I2CE/modules/CustomReports/lib/I2CE_CustomReport.php
>
>
> ** Changed in: pmoralg
> Status: New => In Progress
>
> ** Changed in: i2ce
> Status: New => In Progress
>
> ** Changed in: ihris-manage-cssc
> Status: New => In Progress
>
> ** Changed in: pmoralg
> Importance: Undecided => High
>
> ** Changed in: i2ce
> Importance: Undecided => High
>
> ** Changed in: ihris-manage-cssc
> Importance: Undecided => High
>
> ** Changed in: pmoralg
> Assignee: (unassigned) => Carl Leitner (litlfred)
>
> ** Changed in: i2ce
> Assignee: (unassigned) => Carl Leitner (litlfred)
>
> ** Changed in: ihris-manage-cssc
> Assignee: (unassigned) => Carl Leitner (litlfred)
>
> --
> You received this bug notification because you are a member of iHRIS
> CSSC Tanzania, which is subscribed to ihris-manage-cssc.
> https://bugs.launchpad.net/bugs/824598
>
> Title:
> Too many fields in a report causes an error
>
> To manage notifications about this bug go to:
> https://bugs.launchpad.net/i2ce/+bug/824598/+subscriptions
>

--
======================================
ICT Officer
Mathayo Josephat
Christian Social Services Commission
AfyaMtandao / Tanzania Human Resource Project
P.O Box 9433
Dar es Salaam
Tanzania.

Email: <email address hidden> /<email address hidden>
Mobile: + 255 754 940 636 / 712 191 192
website:
www.cssc.or.tz
www.afyamtandao.org
======================================

[Carl Leitner (litlfred)]

I would make sure you are running 4.0.15 or 4.0.16 on the site you are testing this on, just to make sure we don't pick up some issues due to version mismatches.

[josephat mathayo (jmathayo)]

Dear Carl,

On 8/15/11, Carl Leitner <email address hidden> wrote:
> I would make sure you are running 4.0.15 or 4.0.16 on the site you are
> testing this on, just to make sure we don't pick up some issues due to
> version mismatches.
>
> --
> You received this bug notification because you are a member of iHRIS
> CSSC Tanzania, which is subscribed to ihris-manage-cssc.
> https://bugs.launchpad.net/bugs/824598
>
> Title:
> Too many fields in a report causes an error
>
> To manage notifications about this bug go to:
> https://bugs.launchpad.net/i2ce/+bug/824598/+subscriptions
>

--
======================================
ICT Officer
Mathayo Josephat
Christian Social Services Commission
AfyaMtandao / Tanzania Human Resource Project
P.O Box 9433
Dar es Salaam
Tanzania.

Email: <email address hidden> /<email address hidden>
Mobile: + 255 754 940 636 / 712 191 192
website:
www.cssc.or.tz
www.afyamtandao.org
======================================

[josephat mathayo (jmathayo)]

Dear Carl

I am using 4.0.15
Sorry for delay in testing i was a bit tight.

I tested. This is the code for the new changes to the file:
/var/lib/iHRIS/lib/4.0.15/I2CE/modules/CustomerReports/lib/

http://pastebin.com/XZnWBiSn

After saving and restarting apache the generate the big reports i am
getting this kind of errors:
http://pastebin.com/c2PSkKM7

See also the file it was before i changed to
http://bazaar.launchpad.net/~intrahealth+informatics/i2ce/4.0-dev/revision/2590

Here is the old file before edited to 2590 revision.

It was just testing in my laptop and not the live site

On 8/15/11, Carl Leitner <email address hidden> wrote:
> I would make sure you are running 4.0.15 or 4.0.16 on the site you are
> testing this on, just to make sure we don't pick up some issues due to
> version mismatches.
>
> --
> You received this bug notification because you are a member of iHRIS
> CSSC Tanzania, which is subscribed to ihris-manage-cssc.
> https://bugs.launchpad.net/bugs/824598
>
> Title:
> Too many fields in a report causes an error
>
> To manage notifications about this bug go to:
> https://bugs.launchpad.net/i2ce/+bug/824598/+subscriptions
>

--
======================================
ICT Officer
Mathayo Josephat
Christian Social Services Commission
AfyaMtandao / Tanzania Human Resource Project
P.O Box 9433
Dar es Salaam
Tanzania.

Email: <email address hidden> /<email address hidden>
Mobile: + 255 754 940 636 / 712 191 192
website:
www.cssc.or.tz
www.afyamtandao.org
======================================

[Carl Leitner (litlfred)]

thank you so much for the very detailed bug report.

Here is the main issue. I am looking into in now.

        Refered by http://ez.csschris.or.tz/index.php/CustomReports/edit/reports
        I2CE: I2CE->raiseError
        Fatal Error:Array
        (
            [type] => 4
            [message] => syntax error, unexpected '{'
            [file] => /var/lib/iHRIS/lib/4.0.15/I2CE/modules/CustomReports/lib/I2CE_CustomReport.php
            [line] => 696
        )

[Carl Leitner (litlfred)]

It is strange that you are getting a syntax error, because I have checked that there are no syntax errors and the error does not make sense with the file at revision 2590 (or later).

So I checked, and you will see that the files:
   http://pastebin.com/XZnWBiSn
and
  http://bazaar.launchpad.net/~intrahealth+informatics/i2ce/4.0-dev/view/2590/modules/CustomReports/lib/I2CE_CustomReport.php
are not the same starting at line 672.

Can you redownload the file:
  http://bazaar.launchpad.net/~intrahealth+informatics/i2ce/4.0-dev/download/2590/i2ce_customreport.ph-20080423203436-s626vf01y04q3xi5-4/I2CE_CustomReport.php
and try running the report again.

Thanks!

[josephat mathayo (jmathayo)]

Dear Carl.

I will cross check and share what happened. I am in touch. Although
this two weeks i am training the APHFTA & BAKWATA side in
collaboration with UDSM team. Thanks
On 8/17/11, Carl Leitner <email address hidden> wrote:
> thank you so much for the very detailed bug report.
>
> Here is the main issue. I am looking into in now.
>
> Refered by
> http://ez.csschris.or.tz/index.php/CustomReports/edit/reports
> I2CE: I2CE->raiseError
> Fatal Error:Array
> (
> [type] => 4
> [message] => syntax error, unexpected '{'
> [file] =>
> /var/lib/iHRIS/lib/4.0.15/I2CE/modules/CustomReports/lib/I2CE_CustomReport.php
> [line] => 696
> )
>
> --
> You received this bug notification because you are a member of iHRIS
> CSSC Tanzania, which is subscribed to ihris-manage-cssc.
> https://bugs.launchpad.net/bugs/824598
>
> Title:
> Too many fields in a report causes an error
>
> To manage notifications about this bug go to:
> https://bugs.launchpad.net/i2ce/+bug/824598/+subscriptions
>

--
======================================
ICT Officer
Mathayo Josephat
Christian Social Services Commission
AfyaMtandao / Tanzania Human Resource Project
P.O Box 9433
Dar es Salaam
Tanzania.

Email: <email address hidden> /<email address hidden>
Mobile: + 255 754 940 636 / 712 191 192
website:
www.cssc.or.tz
www.afyamtandao.org
======================================

[josephat mathayo (jmathayo)]

LinkedIn
------------

Bug,

I'd like to add you to my professional network on LinkedIn.

- Mathayo

Mathayo Josephat
ICT Consultant at MARO Technology Limited
Tanzania

Confirm that you know Mathayo Josephat:
https://www.linkedin.com/e/-n3thp6-hqkma6ch-5q/isd/19428948925/ZSMR4QJd/?hs=false&tok=3kl_pACED_w641

--
You are receiving Invitation to Connect emails. Click to unsubscribe:
http://www.linkedin.com/e/-n3thp6-hqkma6ch-5q/XIUsluXkUukXNtwvRcFfRlrk1XFHIBOHdBu80q2/goo/824598%40bugs%2Elaunchpad%2Enet/20061/I6296821729_1/?hs=false&tok=0HhNFBvWn_w641

(c) 2012 LinkedIn Corporation. 2029 Stierlin Ct, Mountain View, CA 94043, USA.