Hitachi Vantara Pentaho Community Forums
Results 1 to 11 of 11

Thread: Error upgrading repository from 4.4.0 to 5.0.1

  1. #1

    Angry Error upgrading repository from 4.4.0 to 5.0.1

    I've just downloaded pdi501, duplicated my repo db, created a new connection to repository and tried to upgrade the repository using the button create or upgrade...

    The first error I encountered is
    Code:
    Unable to create or modify table R_VERSION
    
    
    Couldn't execute SQL: ALTER TABLE R_VERSION ALTER COLUMN IS_UPGRADE BIT
    
    
    Conversion failed when converting the varchar value 'N' to data type bit.
    This is obvious in my mind because the column IS_UPGRADE contains values like Y or N that, if I'm not wrong, can't be saved in a BIT field.

    Can you help me?

  2. #2
    Join Date
    Aug 2011
    Posts
    236

    Default

    Hi,

    The way I did it :-

    1) Create new empty repo using 5.0.1
    2) Export 4.4.0 repo
    3) Import into 5.0.1

    Worked for me.
    PDI 8.0.0
    MySQL - 5.6.27
    Redshift - 1.0.1485
    PostgreSQL 8.0.2
    OS - Ubuntu 10.04.2

  3. #3

    Default

    Quote Originally Posted by tnewman View Post
    Hi,

    The way I did it :-

    1) Create new empty repo using 5.0.1
    2) Export 4.4.0 repo
    3) Import into 5.0.1

    Worked for me.
    I'm going to try, thank you... I'm feeling a bit silly

  4. #4

    Default

    I tried, but the error raise up also when I try to make a new repository, could be a setting in the creation of database?
    SQL executed: ALTER TABLE R_REPOSITORY_LOG ALTER COLUMN LOG_DATE DATETIME



    SQL executed: ALTER TABLE R_VERSION ALTER COLUMN UPGRADE_DATE DATETIME




    Error while executing: INSERT INTO R_VERSION(ID_VERSION, MAJOR_VERSION, MINOR_VERSION, UPGRADE_DATE, IS_UPGRADE) VALUES (1,5,0,'2014/01/20 10:44:29.912','N')
    org.pentaho.di.core.exception.KettleDatabaseException:


    Couldn't execute SQL: INSERT INTO R_VERSION(ID_VERSION, MAJOR_VERSION, MINOR_VERSION, UPGRADE_DATE, IS_UPGRADE) VALUES (1,5,0,'2014/01/20 10:44:29.912','N')






    Conversion failed when converting the varchar value 'N' to data type bit.






    3 SQL statements executed

    org.pentaho.di.core.exception.KettleDatabaseException: Couldn't execute SQL: INSERT INTO R_VERSION(ID_VERSION, MAJOR_VERSION, MINOR_VERSION, UPGRADE_DATE, IS_UPGRADE) VALUES (1,5,0,'2014/01/20 10:44:29.912','N')


    Conversion failed when converting the varchar value 'N' to data type bit.


    at org.pentaho.di.core.database.Database.execStatement(Database.java:1432)
    at org.pentaho.di.core.database.Database.execStatement(Database.java:1380)
    at org.pentaho.di.ui.core.database.dialog.SQLEditor.exec(SQLEditor.java:362)
    at org.pentaho.di.ui.core.database.dialog.SQLEditor.access$200(SQLEditor.java:80)
    at org.pentaho.di.ui.core.database.dialog.SQLEditor$7.handleEvent(SQLEditor.java:221)
    at org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Display.runDeferredEvents(Unknown Source)
    at org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source)
    at org.pentaho.di.ui.repository.kdr.KettleDatabaseRepositoryDialog.open(KettleDatabaseRepositoryDialog.java:320)
    at org.pentaho.di.ui.repository.kdr.KettleDatabaseRepositoryDialog.open(KettleDatabaseRepositoryDialog.java:75)
    at org.pentaho.di.ui.repository.RepositoriesHelper.newRepository(RepositoriesHelper.java:117)
    at org.pentaho.di.ui.repository.controllers.RepositoriesController.newRepository(RepositoriesController.java:273)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:601)
    at org.pentaho.ui.xul.impl.AbstractXulDomContainer.invoke(AbstractXulDomContainer.java:329)
    at org.pentaho.ui.xul.impl.AbstractXulComponent.invoke(AbstractXulComponent.java:139)
    at org.pentaho.ui.xul.impl.AbstractXulComponent.invoke(AbstractXulComponent.java:123)
    at org.pentaho.ui.xul.swt.tags.SwtButton.access$300(SwtButton.java:26)
    at org.pentaho.ui.xul.swt.tags.SwtButton$2.mouseUp(SwtButton.java:87)
    at org.eclipse.swt.widgets.TypedListener.handleEvent(Unknown Source)
    at org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Display.runDeferredEvents(Unknown Source)
    at org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source)
    at org.eclipse.jface.window.Window.runEventLoop(Window.java:820)
    at org.eclipse.jface.window.Window.open(Window.java:796)
    at org.pentaho.di.ui.xul.KettleDialog.show(KettleDialog.java:53)
    at org.pentaho.di.ui.xul.KettleDialog.show(KettleDialog.java:20)
    at org.pentaho.di.ui.repository.controllers.RepositoriesController.show(RepositoriesController.java:194)
    at org.pentaho.di.ui.repository.RepositoriesDialog.show(RepositoriesDialog.java:102)
    at org.pentaho.di.ui.spoon.Spoon.selectRep(Spoon.java:7155)
    at org.pentaho.di.ui.spoon.Spoon.start(Spoon.java:7316)
    at org.pentaho.di.ui.spoon.Spoon.createContents(Spoon.java:8657)
    at org.eclipse.jface.window.Window.create(Window.java:426)
    at org.eclipse.jface.window.Window.open(Window.java:785)
    at org.pentaho.di.ui.spoon.Spoon.start(Spoon.java:8672)
    at org.pentaho.di.ui.spoon.Spoon.main(Spoon.java:625)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:601)
    at org.pentaho.commons.launcher.Launcher.main(Launcher.java:134)
    Caused by: java.sql.SQLException: Conversion failed when converting the varchar value 'N' to data type bit.
    at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
    at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
    at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
    at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
    at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:584)
    at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:546)
    at net.sourceforge.jtds.jdbc.JtdsStatement.executeImpl(JtdsStatement.java:723)
    at net.sourceforge.jtds.jdbc.JtdsStatement.execute(JtdsStatement.java:1160)
    at org.pentaho.di.core.database.Database.execStatement(Database.java:1406)
    ... 44 more

  5. #5

    Default

    I think that the problem is in the script that is produced by the Create new Repository feature:
    CREATE TABLE R_VERSION
    (
    ID_VERSION BIGINT PRIMARY KEY
    , MAJOR_VERSION INT
    , MINOR_VERSION INT
    , UPGRADE_DATE DATETIME
    , IS_UPGRADE BIT
    )
    ;


    INSERT INTO R_VERSION(ID_VERSION, MAJOR_VERSION, MINOR_VERSION, UPGRADE_DATE, IS_UPGRADE) VALUES (1,5,0,'2014/01/20 11:09:19.791','N');

    The script before declares a Table where is_upgrade is a BIT field, than tries to insert in a char value... is there some settings that allow this thing?

    I have searched in the creation script and I counted 26 occurences of BIT fields... I can make some substitution with Char(1) but I don't know if I make a mess... someone that can help me?

    Thanks
    Last edited by sanzo77; 01-20-2014 at 06:19 AM.

  6. #6

    Default

    No one that can help me?

  7. #7

    Default

    I'm facing the same issue. Any solution for that?

  8. #8

    Default

    Hi Kroma,
    I've made 2 substitutions before execute the script for the creation of the new repo. I've changed BIT in CHAR(1) in the 2 tables where the script want to insert the value 'Y' or 'N' (I've looked for these two values and found the fields to change).
    The repository now is created and I'm importing from xml the old repo (version 4.4), I'll update here if there will be some problems.

  9. #9

    Default

    I can say that everything is gone ok with the 2 subs that I've done. I can suggest you to try to do the same and test some jobs/transformations.

  10. #10
    Join Date
    Aug 2014
    Posts
    2

    Default Switching to CHAR(1) seems the correct thing to do.

    Quote Originally Posted by sanzo77 View Post
    Hi Kroma,
    I've made 2 substitutions before execute the script for the creation of the new repo. I've changed BIT in CHAR(1) in the 2 tables where the script want to insert the value 'Y' or 'N' (I've looked for these two values and found the fields to change).
    The repository now is created and I'm importing from xml the old repo (version 4.4), I'll update here if there will be some problems.
    In my case the Repository creation sql contained 'BOOLEAN' fields. I'm using biserver-ce-5.1:

    Code:
    CREATE TABLE R_VERSION
    (
      ID_VERSION BIGINT NOT NULL PRIMARY KEY
    , MAJOR_VERSION INT
    , MINOR_VERSION INT
    , UPGRADE_DATE DATETIME
    , IS_UPGRADE BOOLEAN
    )
    ;
    
    
    INSERT INTO R_VERSION(ID_VERSION, MAJOR_VERSION, MINOR_VERSION, UPGRADE_DATE, IS_UPGRADE) VALUES (1,5,0,'2014/08/29 10:57:40.468','N');

    Reading http://wiki.pentaho.com/display/EAI/...aMySQLdatabase it appears that replacing BOOLEAN fields with CHAR(1) in the Repository Creation SQL seems the correct thing to do. In MySQL 5.0 - current 5.7 BOOLEAN fields are TINYINTS and cannot accept char values.

    Wilbert

  11. #11
    Join Date
    Aug 2014
    Posts
    2

    Default

    Working through this a bit more. When I uncheck the identifier 'Supports the boolean datatype' in the Advanced tab of the database connection, Kettle is doing the right thing.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.