Commcare-export - Error: 'Row size too large (> 8126)

Hi,

We are using commcare-export tool to export data to MySQL database. We have
a table in which there are 252 columns, and on exporting this data, we are
getting following error from commcare-export tool.

The error message is:

sqlalchemy.exc.OperationalError: (_mysql_exceptions.OperationalError)
(1118, 'Ro

w size too large (> 8126). Changing some columns to TEXT or BLOB may help.
In cu

rrent row format, BLOB prefix of 0 bytes is stored inline.’) [SQL: u’ALTER
TABLE

  • MyTable MODIFY MyColumn TEXT NULL’]*

Please suggest possible resolution.

··· -- Thanks, Ajay

HI Ajay

I suggest you follow the hints in the error message:

  • change any long varchar columns to TEXT
··· On 23 January 2017 at 15:12, Ajay Singh wrote:

Hi,

We are using commcare-export tool to export data to MySQL database. We
have a table in which there are 252 columns, and on exporting this data, we
are getting following error from commcare-export tool.

The error message is:

sqlalchemy.exc.OperationalError: (_mysql_exceptions.OperationalError)
(1118, 'Ro

w size too large (> 8126). Changing some columns to TEXT or BLOB may
help. In cu

rrent row format, BLOB prefix of 0 bytes is stored inline.’) [SQL:
u’ALTER TABLE

  • MyTable MODIFY MyColumn TEXT NULL’]*

Please suggest possible resolution.


Thanks,
Ajay


You received this message because you are subscribed to the Google Groups
"CommCare Developers" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to commcare-developers+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.


Simon Kelly
Senior Engineer | Dimagi South Africa

Thanks Simon for prompt reply,

But it is the case of error in export command only, where we are passing
path of query file.

So in the export command (commcare-export), where can we put TEXT data type
for long varchar fields. During this process commcare-export tool
automatically assigns varchar(32) or TEXT data type from its own
logic/algorithm.

Please suggest.

··· -- Thanks, *Ajay Singh* *Corporate** Renaissance **Group* Cell (India): +91-9711628763​ Email: ajay.singh@crgoup.co.in

[image: CR Group] http://www.crgroup.com/[image: Rapid-Fire Business
Intelligence & Analytics] http://www.tableau.com/

On Mon, Jan 23, 2017 at 6:48 PM, Simon Kelly skelly@dimagi.com wrote:

HI Ajay

I suggest you follow the hints in the error message:

  • change any long varchar columns to TEXT

On 23 January 2017 at 15:12, Ajay Singh ajay.singh@crgroup.co.in wrote:

Hi,

We are using commcare-export tool to export data to MySQL database. We
have a table in which there are 252 columns, and on exporting this data, we
are getting following error from commcare-export tool.

The error message is:

sqlalchemy.exc.OperationalError: (_mysql_exceptions.OperationalError)
(1118, 'Ro

w size too large (> 8126). Changing some columns to TEXT or BLOB may
help. In cu

rrent row format, BLOB prefix of 0 bytes is stored inline.’) [SQL:
u’ALTER TABLE

  • MyTable MODIFY MyColumn TEXT NULL’]*

Please suggest possible resolution.


Thanks,
Ajay


You received this message because you are subscribed to the Google Groups
"CommCare Developers" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to commcare-developers+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.


Simon Kelly
Senior Engineer | Dimagi South Africa


You received this message because you are subscribed to a topic in the
Google Groups “CommCare Developers” group.
To unsubscribe from this topic, visit https://groups.google.com/d/
topic/commcare-developers/jEyaobJgzdA/unsubscribe.
To unsubscribe from this group and all its topics, send an email to
commcare-developers+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Hi Ajay

The export tool already makes all VARCHAR columns longer than 256 chars
into TEXT columns so I don’t think there is anything else the tool can do.

This StackOverflow question http://stackoverflow.com/a/25373052/632517
suggests some options:

  • Increase the value of the “innodb_log_file_size” parameter in your
    MySQL config file (“my.ini”)
  • Set “innodb_strict_mode” to “0” in your MySQL config file (“my.ini”)
  • Change to using MyISAM table formats
··· On 23 January 2017 at 15:25, Ajay Singh wrote:

Thanks Simon for prompt reply,

But it is the case of error in export command only, where we are passing
path of query file.

So in the export command (commcare-export), where can we put TEXT data
type for long varchar fields. During this process commcare-export tool
automatically assigns varchar(32) or TEXT data type from its own
logic/algorithm.

Please suggest.


Thanks,
Ajay Singh
Corporate* Renaissance *Group
Cell (India): +91-9711628763 <+91%2097116%2028763>​
Email: ajay.singh@crgoup.co.in

[image: CR Group] http://www.crgroup.com/[image: Rapid-Fire Business
Intelligence & Analytics] http://www.tableau.com/

On Mon, Jan 23, 2017 at 6:48 PM, Simon Kelly skelly@dimagi.com wrote:

HI Ajay

I suggest you follow the hints in the error message:

  • change any long varchar columns to TEXT

On 23 January 2017 at 15:12, Ajay Singh ajay.singh@crgroup.co.in wrote:

Hi,

We are using commcare-export tool to export data to MySQL database. We
have a table in which there are 252 columns, and on exporting this data, we
are getting following error from commcare-export tool.

The error message is:

sqlalchemy.exc.OperationalError: (_mysql_exceptions.OperationalError)
(1118, 'Ro

w size too large (> 8126). Changing some columns to TEXT or BLOB may
help. In cu

rrent row format, BLOB prefix of 0 bytes is stored inline.’) [SQL:
u’ALTER TABLE

  • MyTable MODIFY MyColumn TEXT NULL’]*

Please suggest possible resolution.


Thanks,
Ajay


You received this message because you are subscribed to the Google
Groups “CommCare Developers” group.
To unsubscribe from this group and stop receiving emails from it, send
an email to commcare-developers+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.


Simon Kelly
Senior Engineer | Dimagi South Africa


You received this message because you are subscribed to a topic in the
Google Groups “CommCare Developers” group.
To unsubscribe from this topic, visit https://groups.google.com/d/to
pic/commcare-developers/jEyaobJgzdA/unsubscribe.
To unsubscribe from this group and all its topics, send an email to
commcare-developers+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.


Simon Kelly
Senior Engineer | Dimagi South Africa

Thanks Simon,

Will check as per your suggestion.

Thank you.

··· -- Thanks, *Ajay Singh* *Corporate** Renaissance **Group* Cell (India): +91-9711628763​ Email: ajay.singh@crgoup.co.in

[image: CR Group] http://www.crgroup.com/[image: Rapid-Fire Business
Intelligence & Analytics] http://www.tableau.com/

On Mon, Jan 23, 2017 at 7:43 PM, Simon Kelly skelly@dimagi.com wrote:

Hi Ajay

The export tool already makes all VARCHAR columns longer than 256 chars
into TEXT columns so I don’t think there is anything else the tool can do.

This StackOverflow question http://stackoverflow.com/a/25373052/632517
suggests some options:

  • Increase the value of the “innodb_log_file_size” parameter in your
    MySQL config file (“my.ini”)
  • Set “innodb_strict_mode” to “0” in your MySQL config file (“my.ini”)
  • Change to using MyISAM table formats

On 23 January 2017 at 15:25, Ajay Singh ajay.singh@crgroup.co.in wrote:

Thanks Simon for prompt reply,

But it is the case of error in export command only, where we are passing
path of query file.

So in the export command (commcare-export), where can we put TEXT data
type for long varchar fields. During this process commcare-export tool
automatically assigns varchar(32) or TEXT data type from its own
logic/algorithm.

Please suggest.


Thanks,
Ajay Singh
Corporate* Renaissance *Group
Cell (India): +91-9711628763 <+91%2097116%2028763>​
Email: ajay.singh@crgoup.co.in

[image: CR Group] http://www.crgroup.com/[image: Rapid-Fire Business
Intelligence & Analytics] http://www.tableau.com/

On Mon, Jan 23, 2017 at 6:48 PM, Simon Kelly skelly@dimagi.com wrote:

HI Ajay

I suggest you follow the hints in the error message:

  • change any long varchar columns to TEXT

On 23 January 2017 at 15:12, Ajay Singh ajay.singh@crgroup.co.in wrote:

Hi,

We are using commcare-export tool to export data to MySQL database. We
have a table in which there are 252 columns, and on exporting this data, we
are getting following error from commcare-export tool.

The error message is:

sqlalchemy.exc.OperationalError: (_mysql_exceptions.OperationalError)
(1118, 'Ro

w size too large (> 8126). Changing some columns to TEXT or BLOB may
help. In cu

rrent row format, BLOB prefix of 0 bytes is stored inline.’) [SQL:
u’ALTER TABLE

  • MyTable MODIFY MyColumn TEXT NULL’]*

Please suggest possible resolution.


Thanks,
Ajay


You received this message because you are subscribed to the Google
Groups “CommCare Developers” group.
To unsubscribe from this group and stop receiving emails from it, send
an email to commcare-developers+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.


Simon Kelly
Senior Engineer | Dimagi South Africa


You received this message because you are subscribed to a topic in the
Google Groups “CommCare Developers” group.
To unsubscribe from this topic, visit https://groups.google.com/d/to
pic/commcare-developers/jEyaobJgzdA/unsubscribe.
To unsubscribe from this group and all its topics, send an email to
commcare-developers+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.


Simon Kelly
Senior Engineer | Dimagi South Africa