materialized view population error

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

materialized view population error

sanjuro

Hello, 

I'm trying to create a materialized view that allows us to flatten out our entire collection (kept in ND tables) so it can be downloaded as a CSV.  I was able to create the materialized view but when I try to populate it, I'm getting the error pasted at the bottom of this email.  I'm wondering if I'm right in thinking that this is the result of a limit to the size of the query value within Tripal?  If so, is there a way modify the Tripal code so it will work?  If not, what might the problem be?  I'm on 7.x-2.0-rc1

This might also be a good time to ask for general feedback about this strategy.  Eventually I'm planning to make this available through Views Services as an API (I've gotten smaller queries to work this way).  Is this a reasonable way to make all of our collection data available?  It's a relatively small collection, which is why it seems like a reasonable thing to attempt.  I thought about writing a Tripal module using Tripal variables, but I wasn't sure how to plug this into the Views Services module, so I went with the materialized view instead.

Thanks!

Sanjuro


Here's the error:

Calling: tripal_populate_mview(12, 81)
WD tripal_mviews: PDOException: SQLSTATE[22001]: String data, right truncated: 7 ERROR:  value too long for  [error]
type character varying(255): INSERT INTO {datadump_pop} (SELECT 
  ndepcacc.value as collection_accession,
  ndgploc.value as locid,
  ndg.latitude as latitude,
  ndg.longitude as longitude,
  ndgpccode.value as countrycode,
  ndgppcode.value as provincecode,
  ndgpcoun.value as country,
  ndgpprov.value as province,
  ndepcdate.value as collection_date,
  ndepname.value as collector,
  ndepaddr.value as univ_address,
  ndepemail.value as email,
  ndepphone.value as phone,
  ndepnote.value as site_note,
  ndepwbook.value as workbook,
  ndepsheet.value as worksheet
   
FROM 
  chado.nd_experiment as nde 
    LEFT outer JOIN chado.nd_experimentprop as ndepcdate ON nde.nd_experiment_id = ndepcdate.nd_experiment_id
AND ndepcdate.type_id = 177
    LEFT outer JOIN chado.nd_experimentprop as ndepname ON nde.nd_experiment_id = ndepname.nd_experiment_id
AND ndepname.type_id = 178
    LEFT outer JOIN chado.nd_experimentprop as ndepaddr ON nde.nd_experiment_id = ndepaddr.nd_experiment_id
AND ndepaddr.type_id = 179
    LEFT outer JOIN chado.nd_experimentprop as ndepemail ON nde.nd_experiment_id = ndepemail.nd_experiment_id
AND ndepemail.type_id = 180
    LEFT outer JOIN chado.nd_experimentprop as ndepphone ON nde.nd_experiment_id = ndepphone.nd_experiment_id
AND ndepphone.type_id = 181
    LEFT outer JOIN chado.nd_experimentprop as ndepnote ON nde.nd_experiment_id = ndepnote.nd_experiment_id
AND ndepnote.type_id = 182
    LEFT outer JOIN chado.nd_experimentprop as ndepwbook ON nde.nd_experiment_id = ndepwbook.nd_experiment_id
AND ndepwbook.type_id = 183
    LEFT outer JOIN chado.nd_experimentprop as ndepsheet ON nde.nd_experiment_id = ndepsheet.nd_experiment_id
AND ndepsheet.type_id = 184
    LEFT outer JOIN chado.nd_experimentprop as ndepcacc ON nde.nd_experiment_id = ndepcacc.nd_experiment_id
AND ndepcacc.type_id = 155,
    
  chado.nd_geolocation as ndg
    LEFT outer JOIN chado.nd_geolocationprop as ndgploc ON ndg.nd_geolocation_id = ndgploc.nd_geolocation_id
AND ndgploc.type_id = 154
    LEFT outer JOIN chado.nd_geolocationprop as ndgpccode ON ndg.nd_geolocation_id =
ndgpccode.nd_geolocation_id AND ndgpccode.type_id = 142
    LEFT outer JOIN chado.nd_geolocationprop as ndgppcode ON ndg.nd_geolocation_id =
ndgppcode.nd_geolocation_id AND ndgppcode.type_id = 143
    LEFT outer JOIN chado.nd_geolocationprop as ndgpcoun ON ndg.nd_geolocation_id =
ndgpcoun.nd_geolocation_id AND ndgpcoun.type_id = 144
    LEFT outer JOIN chado.nd_geolocationprop as ndgpprov ON ndg.nd_geolocation_id =
ndgpprov.nd_geolocation_id AND ndgpprov.type_id = 145
    
WHERE
  nde.nd_geolocation_id = ndg.nd_geolocation_id
); Array
(
)
 in tripal_populate_mview() (line 396 of
/Users/sanjuroIvy/Sites/wildstrawberry_d7/sites/all/modules/tripal/tripal_core/api/tripal_core.mviews.api.inc).


------------------------------------------------------------------------------
Dive into the World of Parallel Programming The Go Parallel Website, sponsored
by Intel and developed in partnership with Slashdot Media, is your hub for all
things parallel software development, from weekly thought leadership blogs to
news, videos, case studies, tutorials and more. Take a look and join the
conversation now. http://goparallel.sourceforge.net/
_______________________________________________
Gmod-tripal mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-tripal
Reply | Threaded
Open this post in threaded view
|

Re: materialized view population error

adf_ncgr
Hi Sanjuro-
I can't claim to be an expert in this area, but what you're doing here looks fairly similar to some things
we've done at our site with mviews with respect to certain subclasses of feature that we expect to have
specific sets of properties.

My guess from the error message you've received is that you have some nd_experimentprop.value
that is exceeding the varchar(255) that you probably set when you defined the schema for the mview.
If you go to the mview definition page in the admin interface, you will probably see numerous elements
that look like this:
    'countrycode' => array (
      'type' => 'varchar',
      'length' => '255',
      'NOT NULL' => false,
    ),

and you may need to modify this to accommodate the length of your actual data, or maybe just set
them to be of type text in the mview so you don't have to worry about the length?

If you really want the values to get truncated in this context when too long, that may be something that
would have to be added as an option to the tripal code (I'm conjecturing).

hope that helps a bit, perhaps experts will correct me if I've unintentionally led you astray...

Andrew

On 3/25/15 2:43 PM, Sanjuro Jogdeo wrote:

Hello, 

I'm trying to create a materialized view that allows us to flatten out our entire collection (kept in ND tables) so it can be downloaded as a CSV.  I was able to create the materialized view but when I try to populate it, I'm getting the error pasted at the bottom of this email.  I'm wondering if I'm right in thinking that this is the result of a limit to the size of the query value within Tripal?  If so, is there a way modify the Tripal code so it will work?  If not, what might the problem be?  I'm on 7.x-2.0-rc1

This might also be a good time to ask for general feedback about this strategy.  Eventually I'm planning to make this available through Views Services as an API (I've gotten smaller queries to work this way).  Is this a reasonable way to make all of our collection data available?  It's a relatively small collection, which is why it seems like a reasonable thing to attempt.  I thought about writing a Tripal module using Tripal variables, but I wasn't sure how to plug this into the Views Services module, so I went with the materialized view instead.

Thanks!

Sanjuro


Here's the error:

Calling: tripal_populate_mview(12, 81)
WD tripal_mviews: PDOException: SQLSTATE[22001]: String data, right truncated: 7 ERROR:  value too long for  [error]
type character varying(255): INSERT INTO {datadump_pop} (SELECT 
  ndepcacc.value as collection_accession,
  ndgploc.value as locid,
  ndg.latitude as latitude,
  ndg.longitude as longitude,
  ndgpccode.value as countrycode,
  ndgppcode.value as provincecode,
  ndgpcoun.value as country,
  ndgpprov.value as province,
  ndepcdate.value as collection_date,
  ndepname.value as collector,
  ndepaddr.value as univ_address,
  ndepemail.value as email,
  ndepphone.value as phone,
  ndepnote.value as site_note,
  ndepwbook.value as workbook,
  ndepsheet.value as worksheet
   
FROM 
  chado.nd_experiment as nde 
    LEFT outer JOIN chado.nd_experimentprop as ndepcdate ON nde.nd_experiment_id = ndepcdate.nd_experiment_id
AND ndepcdate.type_id = 177
    LEFT outer JOIN chado.nd_experimentprop as ndepname ON nde.nd_experiment_id = ndepname.nd_experiment_id
AND ndepname.type_id = 178
    LEFT outer JOIN chado.nd_experimentprop as ndepaddr ON nde.nd_experiment_id = ndepaddr.nd_experiment_id
AND ndepaddr.type_id = 179
    LEFT outer JOIN chado.nd_experimentprop as ndepemail ON nde.nd_experiment_id = ndepemail.nd_experiment_id
AND ndepemail.type_id = 180
    LEFT outer JOIN chado.nd_experimentprop as ndepphone ON nde.nd_experiment_id = ndepphone.nd_experiment_id
AND ndepphone.type_id = 181
    LEFT outer JOIN chado.nd_experimentprop as ndepnote ON nde.nd_experiment_id = ndepnote.nd_experiment_id
AND ndepnote.type_id = 182
    LEFT outer JOIN chado.nd_experimentprop as ndepwbook ON nde.nd_experiment_id = ndepwbook.nd_experiment_id
AND ndepwbook.type_id = 183
    LEFT outer JOIN chado.nd_experimentprop as ndepsheet ON nde.nd_experiment_id = ndepsheet.nd_experiment_id
AND ndepsheet.type_id = 184
    LEFT outer JOIN chado.nd_experimentprop as ndepcacc ON nde.nd_experiment_id = ndepcacc.nd_experiment_id
AND ndepcacc.type_id = 155,
    
  chado.nd_geolocation as ndg
    LEFT outer JOIN chado.nd_geolocationprop as ndgploc ON ndg.nd_geolocation_id = ndgploc.nd_geolocation_id
AND ndgploc.type_id = 154
    LEFT outer JOIN chado.nd_geolocationprop as ndgpccode ON ndg.nd_geolocation_id =
ndgpccode.nd_geolocation_id AND ndgpccode.type_id = 142
    LEFT outer JOIN chado.nd_geolocationprop as ndgppcode ON ndg.nd_geolocation_id =
ndgppcode.nd_geolocation_id AND ndgppcode.type_id = 143
    LEFT outer JOIN chado.nd_geolocationprop as ndgpcoun ON ndg.nd_geolocation_id =
ndgpcoun.nd_geolocation_id AND ndgpcoun.type_id = 144
    LEFT outer JOIN chado.nd_geolocationprop as ndgpprov ON ndg.nd_geolocation_id =
ndgpprov.nd_geolocation_id AND ndgpprov.type_id = 145
    
WHERE
  nde.nd_geolocation_id = ndg.nd_geolocation_id
); Array
(
)
 in tripal_populate_mview() (line 396 of
/Users/sanjuroIvy/Sites/wildstrawberry_d7/sites/all/modules/tripal/tripal_core/api/tripal_core.mviews.api.inc).



------------------------------------------------------------------------------
Dive into the World of Parallel Programming The Go Parallel Website, sponsored
by Intel and developed in partnership with Slashdot Media, is your hub for all
things parallel software development, from weekly thought leadership blogs to
news, videos, case studies, tutorials and more. Take a look and join the 
conversation now. http://goparallel.sourceforge.net/


_______________________________________________
Gmod-tripal mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-tripal

-- 
...all concepts in which an entire process is semiotically concentrated
elude definition; only that which has no history is definable.

Friedrich Nietzsche

------------------------------------------------------------------------------
Dive into the World of Parallel Programming The Go Parallel Website, sponsored
by Intel and developed in partnership with Slashdot Media, is your hub for all
things parallel software development, from weekly thought leadership blogs to
news, videos, case studies, tutorials and more. Take a look and join the
conversation now. http://goparallel.sourceforge.net/
_______________________________________________
Gmod-tripal mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-tripal
Reply | Threaded
Open this post in threaded view
|

Re: materialized view population error

sanjuro
Fiddling with the array setting worked!  Thanks!

Sanjuro



On Wed, Mar 25, 2015 at 8:15 PM, Andrew Farmer <[hidden email]> wrote:
Hi Sanjuro-
I can't claim to be an expert in this area, but what you're doing here looks fairly similar to some things
we've done at our site with mviews with respect to certain subclasses of feature that we expect to have
specific sets of properties.

My guess from the error message you've received is that you have some nd_experimentprop.value
that is exceeding the varchar(255) that you probably set when you defined the schema for the mview.
If you go to the mview definition page in the admin interface, you will probably see numerous elements
that look like this:
    'countrycode' => array (
      'type' => 'varchar',
      'length' => '255',
      'NOT NULL' => false,
    ),

and you may need to modify this to accommodate the length of your actual data, or maybe just set
them to be of type text in the mview so you don't have to worry about the length?

If you really want the values to get truncated in this context when too long, that may be something that
would have to be added as an option to the tripal code (I'm conjecturing).

hope that helps a bit, perhaps experts will correct me if I've unintentionally led you astray...

Andrew


On 3/25/15 2:43 PM, Sanjuro Jogdeo wrote:

Hello, 

I'm trying to create a materialized view that allows us to flatten out our entire collection (kept in ND tables) so it can be downloaded as a CSV.  I was able to create the materialized view but when I try to populate it, I'm getting the error pasted at the bottom of this email.  I'm wondering if I'm right in thinking that this is the result of a limit to the size of the query value within Tripal?  If so, is there a way modify the Tripal code so it will work?  If not, what might the problem be?  I'm on 7.x-2.0-rc1

This might also be a good time to ask for general feedback about this strategy.  Eventually I'm planning to make this available through Views Services as an API (I've gotten smaller queries to work this way).  Is this a reasonable way to make all of our collection data available?  It's a relatively small collection, which is why it seems like a reasonable thing to attempt.  I thought about writing a Tripal module using Tripal variables, but I wasn't sure how to plug this into the Views Services module, so I went with the materialized view instead.

Thanks!

Sanjuro


Here's the error:

Calling: tripal_populate_mview(12, 81)
WD tripal_mviews: PDOException: SQLSTATE[22001]: String data, right truncated: 7 ERROR:  value too long for  [error]
type character varying(255): INSERT INTO {datadump_pop} (SELECT 
  ndepcacc.value as collection_accession,
  ndgploc.value as locid,
  ndg.latitude as latitude,
  ndg.longitude as longitude,
  ndgpccode.value as countrycode,
  ndgppcode.value as provincecode,
  ndgpcoun.value as country,
  ndgpprov.value as province,
  ndepcdate.value as collection_date,
  ndepname.value as collector,
  ndepaddr.value as univ_address,
  ndepemail.value as email,
  ndepphone.value as phone,
  ndepnote.value as site_note,
  ndepwbook.value as workbook,
  ndepsheet.value as worksheet
   
FROM 
  chado.nd_experiment as nde 
    LEFT outer JOIN chado.nd_experimentprop as ndepcdate ON nde.nd_experiment_id = ndepcdate.nd_experiment_id
AND ndepcdate.type_id = 177
    LEFT outer JOIN chado.nd_experimentprop as ndepname ON nde.nd_experiment_id = ndepname.nd_experiment_id
AND ndepname.type_id = 178
    LEFT outer JOIN chado.nd_experimentprop as ndepaddr ON nde.nd_experiment_id = ndepaddr.nd_experiment_id
AND ndepaddr.type_id = 179
    LEFT outer JOIN chado.nd_experimentprop as ndepemail ON nde.nd_experiment_id = ndepemail.nd_experiment_id
AND ndepemail.type_id = 180
    LEFT outer JOIN chado.nd_experimentprop as ndepphone ON nde.nd_experiment_id = ndepphone.nd_experiment_id
AND ndepphone.type_id = 181
    LEFT outer JOIN chado.nd_experimentprop as ndepnote ON nde.nd_experiment_id = ndepnote.nd_experiment_id
AND ndepnote.type_id = 182
    LEFT outer JOIN chado.nd_experimentprop as ndepwbook ON nde.nd_experiment_id = ndepwbook.nd_experiment_id
AND ndepwbook.type_id = 183
    LEFT outer JOIN chado.nd_experimentprop as ndepsheet ON nde.nd_experiment_id = ndepsheet.nd_experiment_id
AND ndepsheet.type_id = 184
    LEFT outer JOIN chado.nd_experimentprop as ndepcacc ON nde.nd_experiment_id = ndepcacc.nd_experiment_id
AND ndepcacc.type_id = 155,
    
  chado.nd_geolocation as ndg
    LEFT outer JOIN chado.nd_geolocationprop as ndgploc ON ndg.nd_geolocation_id = ndgploc.nd_geolocation_id
AND ndgploc.type_id = 154
    LEFT outer JOIN chado.nd_geolocationprop as ndgpccode ON ndg.nd_geolocation_id =
ndgpccode.nd_geolocation_id AND ndgpccode.type_id = 142
    LEFT outer JOIN chado.nd_geolocationprop as ndgppcode ON ndg.nd_geolocation_id =
ndgppcode.nd_geolocation_id AND ndgppcode.type_id = 143
    LEFT outer JOIN chado.nd_geolocationprop as ndgpcoun ON ndg.nd_geolocation_id =
ndgpcoun.nd_geolocation_id AND ndgpcoun.type_id = 144
    LEFT outer JOIN chado.nd_geolocationprop as ndgpprov ON ndg.nd_geolocation_id =
ndgpprov.nd_geolocation_id AND ndgpprov.type_id = 145
    
WHERE
  nde.nd_geolocation_id = ndg.nd_geolocation_id
); Array
(
)
 in tripal_populate_mview() (line 396 of
/Users/sanjuroIvy/Sites/wildstrawberry_d7/sites/all/modules/tripal/tripal_core/api/tripal_core.mviews.api.inc).



------------------------------------------------------------------------------
Dive into the World of Parallel Programming The Go Parallel Website, sponsored
by Intel and developed in partnership with Slashdot Media, is your hub for all
things parallel software development, from weekly thought leadership blogs to
news, videos, case studies, tutorials and more. Take a look and join the 
conversation now. http://goparallel.sourceforge.net/


_______________________________________________
Gmod-tripal mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-tripal

-- 
...all concepts in which an entire process is semiotically concentrated
elude definition; only that which has no history is definable.

Friedrich Nietzsche

------------------------------------------------------------------------------
Dive into the World of Parallel Programming The Go Parallel Website, sponsored
by Intel and developed in partnership with Slashdot Media, is your hub for all
things parallel software development, from weekly thought leadership blogs to
news, videos, case studies, tutorials and more. Take a look and join the
conversation now. http://goparallel.sourceforge.net/
_______________________________________________
Gmod-tripal mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-tripal



------------------------------------------------------------------------------
Dive into the World of Parallel Programming The Go Parallel Website, sponsored
by Intel and developed in partnership with Slashdot Media, is your hub for all
things parallel software development, from weekly thought leadership blogs to
news, videos, case studies, tutorials and more. Take a look and join the
conversation now. http://goparallel.sourceforge.net/
_______________________________________________
Gmod-tripal mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-tripal