PDI Dimension Lookup / Update error on insert

19 posts / 0 new
Last post
j__c__
j__c__'s picture
Offline
Last seen: 7 months 1 week ago
Joined: Feb 7 2010
Senior Boarder

Posts: 25

J C
PDI Dimension Lookup / Update error on insert

Hi

I have Server version: 5.1.39 MySQL Community / InfiniDB Community Edition 1.0.4-3 Final (GPL) on ubuntu 9.10 64bits. I have a transformation on Pentaho Data Integrator to update a dimension table with the Dimension Lookup / Update step and I am getting the following exception
[code]Because of an error this step can't continue:
2010/05/04 17:08:23 - Actualizar dim_producto.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : Error inserting/updating row
2010/05/04 17:08:23 - Actualizar dim_producto.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : Fatal parse error in vtable mode in DMLParser
2010/05/04 17:08:23 - Actualizar dim_producto.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException:
2010/05/04 17:08:23 - Actualizar dim_producto.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : Error inserting/updating row
2010/05/04 17:08:23 - Actualizar dim_producto.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : Fatal parse error in vtable mode in DMLParser
2010/05/04 17:08:23 - Actualizar dim_producto.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) :
2010/05/04 17:08:23 - Actualizar dim_producto.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at org.pentaho.di.core.database.Database.insertRow(Database.java:1325)
2010/05/04 17:08:23 - Actualizar dim_producto.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at org.pentaho.di.core.database.Database.insertRow(Database.java:1239)
2010/05/04 17:08:23 - Actualizar dim_producto.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at org.pentaho.di.core.database.Database.insertRow(Database.java:1227)
2010/05/04 17:08:23 - Actualizar dim_producto.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at org.pentaho.di.trans.steps.dimensionlookup.DimensionLookup.dimUpdate(DimensionLookup.java:1162)
2010/05/04 17:08:23 - Actualizar dim_producto.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at org.pentaho.di.trans.steps.dimensionlookup.DimensionLookup.lookupValues(DimensionLookup.java:553)
2010/05/04 17:08:23 - Actualizar dim_producto.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy)

j__c__
j__c__'s picture
Offline
Last seen: 7 months 1 week ago
Joined: Feb 7 2010
Senior Boarder

Posts: 25

J C
Re:PDI Dimension Lookup / Update error on insert

The step failed only when there are Updated records in the Step Metrics in PDI.

Juan Carlos

dsplats
dsplats's picture
Offline
Last seen: 3 months 2 weeks ago
Joined: Feb 14 2010
Junior Boarder

Posts: 8

Daniel Splattstoesser
Re:PDI Dimension Lookup / Update error on insert

It looks like from that error log that you are having both update and insert errors.

On the options for the Lookup, are you trying to use an auto increment or use the maximum value plus 1 when you insert.

j__c__
j__c__'s picture
Offline
Last seen: 7 months 1 week ago
Joined: Feb 7 2010
Senior Boarder

Posts: 25

J C
Re:PDI Dimension Lookup / Update error on insert

Hi

I'am using table maximum + 1.

Juan Carlos

davestokes
davestokes's picture
Offline
Last seen: 3 years 3 months ago
Joined: Apr 24 2010
Gold Boarder

Posts: 116

David Stokes
Re:PDI Dimension Lookup / Update error on insert

Is there anything in the MySQL error log connected with this problem??

j__c__
j__c__'s picture
Offline
Last seen: 7 months 1 week ago
Joined: Feb 7 2010
Senior Boarder

Posts: 25

J C
Re:PDI Dimension Lookup / Update error on insert

Hi

The mysql log from 2010 May 04 is not anymore on my system, what I found is a log from 2010 May 06 and it has the following:

[code]May 6 11:44:48 farser06 ExeMgr[1898]: 48.194869 |2147483803|0|0| D 16 CAL0041: Start SQL statement: select objectid,columnlength,datatype,dictobjectid,listobjectid,treeobjectid,columnposition,scale,prec,defaultvalue from syscolumn where schema='alm_dat_far' and tablename='dim_producto' and columnname='producto_clave' --lookupOID/FE
May 6 11:44:48 farser06 ExeMgr[1898]: 48.249043 |2147483803|0|0| D 16 CAL0042: End SQL statement
May 6 11:44:48 farser06 ExeMgr[1898]: 48.294024 |2147483803|0|0| D 16 CAL0041: Start SQL statement: select objectid,columnname from syscolumn where schema='alm_dat_far' and tablename='dim_producto' --columnRIDs/FE
May 6 11:44:48 farser06 ExeMgr[1898]: 48.318697 |155|0|0| D 16 CAL0041: Start SQL statement: SELECT count(producto_clave) as contador_dim_producto#012FROM dim_producto
May 6 11:44:48 farser06 ExeMgr[1898]: 48.322478 |2147483803|0|0| D 16 CAL0042: End SQL statement
May 6 11:44:48 farser06 ExeMgr[1898]: 48.349155 |155|0|0| D 16 CAL0042: End SQL statement
May 6 11:44:48 farser06 dmlpackageproc[1948]: 48.390058 |155|0|0| D 21 CAL0041: Start SQL statement: COMMIT
May 6 11:44:48 farser06 dmlpackageproc[1948]: 48.390115 |155|0|0| D 21 CAL0042: End SQL statement
May 6 11:53:46 farser06 dmlpackageproc[1948]: 46.053822 |132|0|0| D 21 CAL0041: Start SQL statement: ROLLBACK
May 6 11:53:46 farser06 dmlpackageproc[1948]: 46.053892 |132|0|0| D 21 CAL0042: End SQL statement
May 6 11:53:48 farser06 kernel: [84019.390526] __ratelimit: 3 callbacks suppressed
May 6 11:53:48 farser06 kernel: [84019.390531] mysqld[21597]: segfault at 7f64012e1600 ip 00007f6425c91c27 sp 00007f63ff6ad400 error 4 in libgcc_s.so.1[7f6425c82000+16000]
May 6 11:54:01 farser06 DMLProc[21677]: 01.978517 |0|0|0| I 20 CAL0002: DMLProc starts bulk roll back.
May 6 11:54:01 farser06 DMLProc[21677]: 01.990178 |0|0|0| I 20 CAL0002: No table need rollback
May 6 11:54:02 farser06 messagequeue[21677]: 02.024916 |0|0|0| I 20 CAL0002: DMLProc finished bulk roll back.
May 6 11:54:02 farser06 DMLProc[21677]: 02.029755 |0|0|0| I 20 CAL0002: DMLProc starts rollbackAll transactions.
May 6 11:54:02 farser06 DMLProc[21677]: 02.072395 |0|0|0| I 20 CAL0002: DMLProc finished rollbackAll transactions.
May 6 11:57:59 farser06 ExeMgr[21652]: 59.517098 |2147483654|0|0| D 16 CAL0041: Start SQL statement: select objecti

davestokes
davestokes's picture
Offline
Last seen: 3 years 3 months ago
Joined: Apr 24 2010
Gold Boarder

Posts: 116

David Stokes
Re:PDI Dimension Lookup / Update error on insert

Juan Carlos,

Please keep us informed of your situation.

j__c__
j__c__'s picture
Offline
Last seen: 7 months 1 week ago
Joined: Feb 7 2010
Senior Boarder

Posts: 25

J C
Re:PDI Dimension Lookup / Update error on insert

Hi,

I still have the same problem. The following is the log that appears on syslog about mysql when the error appears:

[code]May 7 14:25:28 farser06 ExeMgr[21652]: 28.390231 |2147483992|0|0| D 16 CAL0041: Start SQL statement: select objectid,columnname from syscolumn where schema='alm_dat_far' and tablename='dim_producto' --columnRIDs/FE
May 7 14:25:28 farser06 ExeMgr[21652]: 28.435845 |2147483992|0|0| D 16 CAL0042: End SQL statement
May 7 14:25:28 farser06 ExeMgr[21652]: 28.481666 |344|0|0| D 16 CAL0041: Start SQL statement: SELECT count(*) FROM dim_producto WHERE producto_clave = 0
May 7 14:25:28 farser06 ExeMgr[21652]: 28.505717 |344|0|0| D 16 CAL0042: End SQL statement
May 7 14:25:28 farser06 ExeMgr[21652]: 28.532978 |344|0|0| D 16 CAL0041: Start SQL statement: SELECT producto_clave, version, producto_descripcion, producto_codigo_barras, producto_tipo_producto_para_venta, producto_familia, producto_categoria, producto_linea_categoria, producto_estado, producto_departamento_contable, producto_laboratorio, producto_fecha_alta, producto_fecha_actualizacion, fecha_desde, fecha_hasta FROM dim_producto WHERE producto_codigo_interno = ? AND ? >= fecha_desde#012 AND ? < fecha_hasta
May 7 14:25:28 farser06 ExeMgr[21652]: 28.599053 |344|0|0| D 16 CAL0042: End SQL statement
May 7 14:25:28 farser06 ExeMgr[21652]: 28.640211 |344|0|0| D 16 CAL0041: Start SQL statement: SELECT MAX(producto_clave) FROM dim_producto
May 7 14:25:28 farser06 ExeMgr[21652]: 28.657247 |344|0|0| D 16 CAL0042: End SQL statement
May 7 14:25:29 farser06 dmlpackageproc[21677]: 29.273439 |344|448|0| D 21 CAL0041: Start SQL statement: INSERT INTO dim_producto( producto_clave, version, fecha_desde, fecha_hasta, producto_codigo_interno, producto_descripcion, producto_codigo_barras, producto_tipo_producto_para_venta, producto_familia, producto_categoria, producto_linea_categoria, producto_estado, producto_departamento_contable, producto_laboratorio, producto_fecha_alta, producto_fecha_actualizacion) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
May 7 14:25:29 farser06 DMLProc[21677]: 29.273512 |344|448|0| C 20 CAL0017: DML |alm_dat_far|INSERT INTO dim_producto( producto_clave, version, fecha_desde, fecha_hasta, producto_codigo_interno, producto_descripcion, producto_codigo_barras, producto_tipo_producto_para_venta, producto_familia, producto_categoria, producto_linea_categoria, producto_estado, producto_departamento_contable, producto_laboratorio, producto_fecha_

davestokes
davestokes's picture
Offline
Last seen: 3 years 3 months ago
Joined: Apr 24 2010
Gold Boarder

Posts: 116

David Stokes
Re:PDI Dimension Lookup / Update error on insert

Can you send us your InfiniDB logs from /var/log/Calpont and the mysqld error file from /usr/local/Calpont/mysql.db.

Could you also post your create table schema and and few same insert statements? If you do not want to to post them here, please email them to dstokes AT calpont.com

j__c__
j__c__'s picture
Offline
Last seen: 7 months 1 week ago
Joined: Feb 7 2010
Senior Boarder

Posts: 25

J C
Re:PDI Dimension Lookup / Update error on insert

Hi, David

Thanks, I have sent you the logs to your email.

Juan Carlos

j__c__
j__c__'s picture
Offline
Last seen: 7 months 1 week ago
Joined: Feb 7 2010
Senior Boarder

Posts: 25

J C
Re:PDI Dimension Lookup / Update error on insert

Hi, David

I just upgraded to MySQL Community / InfiniDB Community Edition 1.0.5-1 Final (GPL). I enabled the Calpont logs, I tried the transformation and I still got the same error. I sent you the logs that you requested.

Thanks,

Juan Carlos

davestokes
davestokes's picture
Offline
Last seen: 3 years 3 months ago
Joined: Apr 24 2010
Gold Boarder

Posts: 116

David Stokes
Re:PDI Dimension Lookup / Update error on insert

Juan Carlos,

I have forwarded your emails to the development team.

--Dave

davestokes
davestokes's picture
Offline
Last seen: 3 years 3 months ago
Joined: Apr 24 2010
Gold Boarder

Posts: 116

David Stokes
Re:PDI Dimension Lookup / Update error on insert

This is now bug #578459 where single quotes in the middle of a string are not parsed correctly.

UPDATE foo SET last_name="O\'Hara' WHERE ID=123 and UPDATE foo SET last_name='O''Hara' are not parsed in the traditional MySQL fashion and the string with the single quote is not stored.

May 11, 2010 - 8:15am
j__c__
j__c__'s picture
Offline
Last seen: 7 months 1 week ago
Joined: Feb 7 2010
Senior Boarder

Posts: 25

J C
Re:PDI Dimension Lookup / Update error on insert

ok, thanks, Dave.

Juan Carlos

August 12, 2010 - 4:13am
dsplats
dsplats's picture
Offline
Last seen: 3 months 2 weeks ago
Joined: Feb 14 2010
Junior Boarder

Posts: 8

Daniel Splattstoesser
Re:PDI Dimension Lookup / Update error on insert

I'm now seeing this error in 1.5.3 using Lookup/Update in PDI. It was working for me for my dimension updates while I was still on 1.5.2. It works while inserting into the dimension tables, but not when it has to check for current rows. It seems to not even get to the actual update, just in the lookup of the row.

August 12, 2010 - 4:51am
dsplats
dsplats's picture
Offline
Last seen: 3 months 2 weeks ago
Joined: Feb 14 2010
Junior Boarder

Posts: 8

Daniel Splattstoesser
Re:PDI Dimension Lookup / Update error on insert

I've actually now downgraded InfiniDB back to 1.5.2. I don't know why it seemed to work before. I know it is not a Kettle issue because when I create the dimension table as MyISAM, it works just fine.

August 12, 2010 - 5:09am
davestokes
davestokes's picture
Offline
Last seen: 3 years 3 months ago
Joined: Apr 24 2010
Gold Boarder

Posts: 116

David Stokes
Re:PDI Dimension Lookup / Update error on insert

Do any of the logs show what the unsuccessful inserts look like? Is it an unescaped single quote, i.e. The name O'Hara shows up as 'O'Hara' instead of 'O''Hara' or 'O\'Hara'?

August 12, 2010 - 7:15am
dsplats
dsplats's picture
Offline
Last seen: 3 months 2 weeks ago
Joined: Feb 14 2010
Junior Boarder

Posts: 8

Daniel Splattstoesser
Re:PDI Dimension Lookup / Update error on insert

No, none of the columns have single quotes in them. I'm building a time dimension table. The problems don't occur when it just needs to insert. There are colons in some of the columns.

As far as I can tell from the different logs, it looks like it fails on the second select statement, and then runs a rollback from Kettle.

April 24, 2011 - 1:21am
dankins
dankins's picture
Offline
Last seen: 1 year 4 months ago
Joined: Nov 10 2010
Junior Boarder

Posts: 9

Dan Kinsley
Re:PDI Dimension Lookup / Update error on insert

Sorry about digging up an old thread, but I appear to be experiencing the same issues that dsplats is describing. Essentially I am trying to migrate all of my existing dimension transformations to use InfiniDB instead of MySQL. I am running version InfiniDB v2.1.-2 CE and PDI v4.0.1GA (although I tried on it on 4.2GA using Infinidb dialect and saw the same results) .

Here is the relevant section of the InfiniDB's debug.log:
[code]
Apr 23 08:57:18 vm-dev-dan1 ExeMgr[24969]: 18.432902 |2147483859|0|0| D 16 CAL0041: Start SQL statement: select objectid,columnname from syscolumn where schema='warehouse_test' and tablename='dim_tpn_services' --columnRIDs/FE
Apr 23 08:57:18 vm-dev-dan1 ExeMgr[24969]: 18.461984 |2147483859|0|0| D 16 CAL0042: End SQL statement
Apr 23 08:57:18 vm-dev-dan1 ExeMgr[24969]: 18.497381 |211|0|0| D 16 CAL0041: Start SQL statement: SELECT count(*) FROM dim_tpn_services
Apr 23 08:57:18 vm-dev-dan1 ExeMgr[24969]: 18.517517 |211|0|0| D 16 CAL0042: End SQL statement
Apr 23 08:57:18 vm-dev-dan1 ExeMgr[24969]: 18.528703 |211|0|0| D 16 CAL0041: Start SQL statement: SELECT service_tk, version, parent_id, sku, status, suspend_billing, price, notes, account_tk, location_tk, department_tk, user_tk, product_tk FROM dim_tpn_services WHERE service_id = 1 AND '2011-04-23 08:57:17' >= date_from AND '2011-04-23 08:57:17' < date_to
Apr 23 08:57:18 vm-dev-dan1 ExeMgr[24969]: 18.569886 |211|0|0| D 16 CAL0042: End SQL statement
Apr 23 08:57:18 vm-dev-dan1 ExeMgr[24969]: 18.570956 |211|0|0| D 16 CAL0041: Start SQL statement: SELECT service_tk, version, parent_id, sku, status, suspend_billing, price, notes, account_tk, location_tk, department_tk, user_tk, product_tk FROM dim_tpn_services WHERE service_id = 2 AND '2011-04-23 08:57:17' >= date_from AND '2011-04-23 08:57:17' < date_to
Apr 23 08:57:18 vm-dev-dan1 dmlpackageproc[24996]: 18.593639 |211|0|0| D 21 CAL0041: Start SQL statement: ROLLBACK
Apr 23 08:57:18 vm-dev-dan1 dmlpackageproc[24996]: 18.593858 |211|0|0| D 21 CAL0042: End SQL statement
Apr 23 08:57:18 vm-dev-dan1 ExeMgr[24969]: 18.598851 |211|0|0| D 16 CAL0042: End SQL statement
[/code]

Previously the step was set up to use auto-increment for the next ID, but since CE doesn't support that I changed it to select max id instead. I am curious to know if that could be the culprit.

I'll try to post a stripped down / repeatable transformation and schema soon. Has anyone else experienced this issue and found a solution?

Thanks,