Your browser was unable to load all of the resources. They may have been blocked by your firewall, proxy or browser configuration.
Press Ctrl+F5 or Ctrl+Shift+R to have your browser try again.

QuickBuild

Changing database from MySQL to Oracle. #3269

#1
absalom1 ·
I'm planning to change database from MySQL to Oracle.
During my restoring backup xml file to Oracle, I'm facing SQL error as below.

2015-06-25 16:21:38,781 WARN - SQL Error: 24816, SQLState: 99999
2015-06-25 16:21:38,781 ERROR - ORA-24816: Expanded non LONG bind data supplied after actual LONG or LOB column

So I enabled hibernate logging and found the columns of QB_BUILD table are inserted by ordering of alphabet.

Hibernate:
insert
into
QB_BUILD
(QB_BEGIN_DATE, QB_CANCELLER_ID, QB_CONFIGURATION_ID, QB_DESCRIPTION, QB_DURATION, QB_ERROR_MESSAGE, QB_PROMOTED_FROM_ID, QB_REAL_PORT, QB_REAL_URL, QB_REPOSITORY_RUNTIMES, QB_REQUESTER_ID, QB_SCHEDULED, QB_STATUS, QB_STATUS_DATE, QB_STEP_RUNTIMES, QB_VARIABLE_VALUES, QB_VERSION, QB_WAIT_DURATION, QB_ID)
values
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

I would like to know how to sort the column by size(?) or ordering LOB datatypes at the end of insert/update query statements.
  • replies 13
  • views 12974
  • stars 0
#2
robinshen ADMIN ·
We tested with QB6 and Oracle 12c XE and they are working fine. Which Oracle and QB version are you using?
#3
absalom1 ·
Quickbuild version 5.0.39 and Oracle DB 12 and ojdbc7.jar are used.
#4
robinshen ADMIN ·
Maybe it is a JDBC driver issue. Please try out ojdbc14.jar to see if it works.
#5
absalom1 ·
ojdbc14.jar does not work with Oracle12g and returns below error message.<!-- s:-( --><img src="{SMILIES_PATH}/icon_sad.gif" alt=":-(" title="Sad" /><!-- s:-( -->

1) Error in custom provider, java.lang.RuntimeException: java.sql.SQLException: ORA-28040: No matching authentication protocol
#6
robinshen ADMIN ·
Sorry my mistake, ojdbc14.jar is not intended to be used with Oracle 12c. Can you please send your database backup zip (taken from QB administration page) to [robin AT pmease DOT com]? I am not able to reproduce this issue here.
#7
absalom1 ·
I'm sorry but I can't send you our backup files because there are many important information.

Could this information help you to find a clue of this problem?

[QB_BUILD table schema]
+------------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+---------------+------+-----+---------+-------+
| QB_ID | bigint(20) | NO | PRI | NULL | |
| QB_BEGIN_DATE | datetime | NO | MUL | NULL | |
| QB_DESCRIPTION | longtext | YES | | NULL | |
| QB_DURATION | bigint(20) | YES | MUL | NULL | |
| QB_ERROR_MESSAGE | varchar(2048) | YES | | NULL | |
| QB_REAL_PORT | varchar(255) | YES | | NULL | |
| QB_REAL_URL | varchar(255) | YES | | NULL | |
| QB_REPOSITORY_RUNTIMES | longblob | NO | | NULL | |
| QB_SCHEDULED | bit(1) | NO | | NULL | |
| QB_STATUS | int(11) | NO | MUL | NULL | |
| QB_STATUS_DATE | datetime | NO | | NULL | |
| QB_STEP_RUNTIMES | longblob | NO | | NULL | |
| QB_VARIABLE_VALUES | longblob | NO | | NULL | |
| QB_VERSION | varchar(1024) | YES | MUL | NULL | |
| QB_WAIT_DURATION | bigint(20) | YES | | NULL | |
| QB_CANCELLER_ID | bigint(20) | YES | MUL | NULL | |
| QB_CONFIGURATION_ID | bigint(20) | NO | MUL | NULL | |
| QB_PROMOTED_FROM_ID | bigint(20) | YES | MUL | NULL | |
| QB_REQUESTER_ID | bigint(20) | YES | MUL | NULL | |
+------------------------+---------------+------+-----+---------+-------+


[Build.java]
@Column(length=65535)
@Lob
private String description;

@Column(length=255)
private String realUrl;

@Column(length=255)
private String realPort;


[Hibernate.properties]
hibernate.dialect=org.hibernate.dialect.Oracle10gDialect
hibernate.connection.driver_class=oracle.jdbc.driver.OracleDriver
hibernate.connection.url=jdbc:oracle:thin:@10.252.62.222:1521:orcl
hibernate.connection.username=qbtest
hibernate.connection.password=qbtest

hibernate.connection.autocommit=true
hibernate.c3p0.min_size=100
hibernate.c3p0.max_size=255
hibernate.c3p0.max_statements=50
hibernate.c3p0.idle_test_period=600

hibernate.show_sql=false
hibernate.format_sql=true
hibernate.use_sql_comments=false

javax.persistence.validation.mode=none
hibernate.validator.apply_to_ddl=false
hibernate.hbm2ddl.auto=update

hibernate.cache.provider_class=org.hibernate.cache.EhCacheProvider
hibernate.cache.use_query_cache=true

[Builds.xml]
<com.pmease.quickbuild.model.Build revision="0.0">
<id>3302875</id>
<configuration>9649</configuration>
<version>XXXXXXXX</version>
<requester>7116</requester>
<scheduled>false</scheduled>
<status>SUCCESSFUL</status>
<statusDate>2014-12-11T14:23:09.000+09:00</statusDate>
<beginDate>2014-12-11T14:23:07.000+09:00</beginDate>
<duration>1256</duration>
<waitDuration>111031</waitDuration>
<description>XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX</description>
<stepRuntimes>
<entry>XXXXX</entry>
<entry>XXXXX</entry>
<entry>XXXXX</entry>
.....
</stepRuntimes>
<repositoryRuntimes>
<entry>XXXXX</entry>
<entry>XXXXX</entry>
</repositoryRuntimes>
<realUrl>https://xxx.xxx.xxx.xxx</realUrl>
<realPort>7710</realPort>
<variableValues>
<entry>XXXXX</entry>
<entry>XXXXX</entry>
<entry>XXXXX</entry>
.....
</variableValues>
</com.pmease.quickbuild.model.Build>
#8
robinshen ADMIN ·
This does not help unfortunately. Can you please set up a blank new QB instance and get it connected to your Oralce instance and run some build to see if it exhibit the same issue?
#9
absalom1 ·
I have changed Build class to increase version size to 1024 and a build with 1008 characters has problem and I can't understand why it returns error message related with LOB <!-- s:-( --><img src="{SMILIES_PATH}/icon_sad.gif" alt=":-(" title="Sad" /><!-- s:-( -->
#10
robinshen ADMIN ·
Can you please use shorter value of build version and do not modify QB source to see if it works? The build version is not assumed to take that long string (and can cause many display issues across QB page).
#11
absalom1 ·
I have tested with short build version and it worked.
We have modified several places related with display also <!-- s:-) --><img src="{SMILIES_PATH}/icon_smile.gif" alt=":-)" title="Smile" /><!-- s:-) -->
I think I need to contact Oracle support. Thanks a lot <!-- s:-) --><img src="{SMILIES_PATH}/icon_smile.gif" alt=":-)" title="Smile" /><!-- s:-) -->
#12
absalom1 ·
FYI.
More than 980 characters of Build version causes another error on recording Audit.
ORA-12899: value too large for column "QBTEST"."QB_AUDIT"."QB_ACTION" (actual: 1034, maximum: 1024)

I think I need to limit Build version to 980 characters.

I'll try to keep update if I have another problems or information while migrating to Oracle
#13
absalom1 ·
I had gave up migrating mysql DB to Oracle DB but now I have to make it work...<!-- s:-( --><img src="{SMILIES_PATH}/icon_sad.gif" alt=":-(" title="Sad" /><!-- s:-( -->
I tried migration and faced ORA-24816 again and found it is hibernate problem.

Hibernate generates insert and update DB queries by alphabet sequence but it should sort LOB rows to the rear.
https://hibernate.atlassian.net/browse/HHH-4635

Do you have any plan for upgrading hibernate version to fix this problem?

2016-01-15 09:10:11,612 DEBUG -
    /* insert com.pmease.quickbuild.model.Build
        */ insert
        into
            QB_BUILD
            (QB_BEGIN_DATE, QB_CANCELLER_ID, QB_CONFIGURATION_ID, QB_DESCRIPTION, QB_DURATION, QB_ERROR_MESSAGE, QB_PROMOTED_FROM_ID, QB_REAL_PORT, QB_REAL_URL, QB_REPOSITORY_RUNTIMES, QB_REQUESTER_ID, QB_SCHEDULED, QB_STATUS, QB_STATUS_DATE, QB_STEP_RUNTIMES, QB_VARIABLE_VALUES, QB_VERSION, QB_WAIT_DURATION, QB_ID)
        values
            (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
2016-01-15 09:10:11,612 TRACE - binding parameter [1] as [TIMESTAMP] - 2015-07-17 12:22:18.0
2016-01-15 09:10:11,612 TRACE - binding parameter [2] as [BIGINT] - <null>
2016-01-15 09:10:11,612 TRACE - binding parameter [3] as [BIGINT] - 10322
2016-01-15 09:10:11,612 TRACE - binding parameter [4] as [CLOB] - less than 4000
2016-01-15 09:10:11,612 TRACE - binding parameter [5] as [BIGINT] - 66649
2016-01-15 09:10:11,612 TRACE - binding parameter [6] as [VARCHAR] - <null>
2016-01-15 09:10:11,612 TRACE - binding parameter [7] as [BIGINT] - <null>
2016-01-15 09:10:11,612 TRACE - binding parameter [8] as [VARCHAR] - 7710
2016-01-15 09:10:11,612 TRACE - binding parameter [9] as [VARCHAR] - URL
2016-01-15 09:10:11,612 TRACE - binding parameter [11] as [BIGINT] - 7790
2016-01-15 09:10:11,612 TRACE - binding parameter [12] as [BIT] - false
2016-01-15 09:10:11,612 DEBUG - Binding '2' to parameter: 13
2016-01-15 09:10:11,613 TRACE - binding parameter [14] as [TIMESTAMP] - 2015-07-17 12:23:25.0
2016-01-15 09:10:11,613 TRACE - binding parameter [17] as [VARCHAR] - version text
2016-01-15 09:10:11,613 TRACE - binding parameter [18] as [BIGINT] - 2932
2016-01-15 09:10:11,613 TRACE - binding parameter [19] as [BIGINT] - 5698874
2016-01-15 09:10:11,625 WARN  - SQL Error: 24816, SQLState: 99999
2016-01-15 09:10:11,626 ERROR - ORA-24816: Expanded non LONG bind data supplied after actual LONG or LOB column
#14
robinshen ADMIN ·
Please file a ticket at track.pmease.com, and we will try to get it upgraded in next one or two patch releases.
Log in to reply