View Issue Details

Related ChangesetsJump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0005456SymmetricDSBugpublic2022-09-13 07:442022-11-02 17:27
Reporterqii Assigned Toelong  
Prioritynormal 
Status closedResolutionreopened 
Target Version3.14.3Fixed in Version3.14.3 
Summary0005456: ORA-24816 when sync varchar2(4000) and long in same table
DescriptionI have table has column : Long and varchar2(4000),use database oracle 11g,
it has err ora-24816 for sync when varchar2(4000).length >2000 and Long .length>4000;
Steps To Reproduce- use database oracle 11g,
- create table with LONG data type that is not the last column
- example: create table mylongclob (id integer primary key, descr2 long, descr1 varchar(4000));
- insert or update table who has has column Long and varchar2(4000), when varchar2(4000).length >2000 and Long .length>4000;
- example:
public class Test {
    public static void main(String[] args) throws Exception {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        Connection c = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:xe", "elong", "secret");
        PreparedStatement s = c.prepareStatement("insert into mylongclob (id, descr1, descr2) values (?, ?, ?)");
        s.setInt(1, 5);
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < 2100; i++) {
            sb.append("a");
        }
        StringBuilder sb2 = new StringBuilder();
        for (int i = 0; i < 4100; i++) {
            sb2.append("a");
        }
        s.setString(2, sb.toString());
        s.setString(3, sb2.toString());
        s.executeUpdate();
        s.close();
        c.close();
    }
}

- error is ORA-24816: Expanded non LONG bind data supplied after actual LONG or LOB column
Additional Informationoerr ora 24816
24816, ... "Expanded non LONG bind data supplied after actual LONG or LOB column"
// *Cause: A Bind value of length potentially > 4000 bytes follows binding for
// LOB or LONG.
// *Action: Re-order the binds so that the LONG bind or LOB binds are all
// at the end of the bind list.
TagsNo tags attached.

Activities

elong

2022-09-13 13:12

developer   ~0002192

Does it work if you use these settings?

update sym_channel set contains_big_lob = 1 where channel_id = 'default';
update sym_trigger set use_capture_lobs = 1;

Also, can you attach a log file for some more context?

qii

2022-09-14 10:25

reporter   ~0002196

no, it is a oracle bug, oracle can not update two Long type value once. you update seems update all columns, here is log: I remove some data, only err logs. you can Recurrence it again in your server.
err.log (4,125 bytes)    
2022-09-14 18:09:04,588 INFO [sync119] [DefaultDatabaseWriter] [sync119-dataloader-532] Failed to process update event in batch sync52-1555 on channel 'default'.
Failed sql was: update "USER"."TABLE" set "LONG_COLUMN" = ' more than 4000 length', "VARCHAR2(4000)" = 'more than 2000 length' where "PK" = 1
Failed raw sql was: update "USER"."TABLE" set "LONG_COLUMN" = ?, "VARCHAR2(4000)" = ? where "PK" = ?
Failed sql parameters: [,,]
Failed sql parameters types: [CLOB, VARCHAR, NUMERIC]
Failed sql state and code: 99999 (24816)
Failed pk data was: "1"
Failed row data was: ,,
 StackTraceKey.init [SqlException:2239545150] org.jumpmind.db.sql.SqlException: ORA-24816: Expanded non LONG bind data supplied after actual LONG or LOB column

	at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:308)
	at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:297)
	at org.jumpmind.db.sql.JdbcSqlTransaction.addRow(JdbcSqlTransaction.java:443)
	at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.execute(DefaultDatabaseWriter.java:1025)
	at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.update(DefaultDatabaseWriter.java:557)
	at org.jumpmind.symmetric.io.data.writer.AbstractDatabaseWriter.write(AbstractDatabaseWriter.java:183)
	at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:62)
	at org.jumpmind.symmetric.model.ProcessInfoDataWriter.write(ProcessInfoDataWriter.java:84)
	at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:62)
	at org.jumpmind.symmetric.io.data.writer.TransformWriter.write(TransformWriter.java:186)
	at org.jumpmind.symmetric.io.data.DataProcessor.forEachDataInTable(DataProcessor.java:194)
	at org.jumpmind.symmetric.io.data.DataProcessor.forEachTableInBatch(DataProcessor.java:160)
	at org.jumpmind.symmetric.io.data.DataProcessor.process(DataProcessor.java:108)
	at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$2.call(DataLoaderService.java:1045)
	at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$2.call(DataLoaderService.java:1021)
	at java.util.concurrent.FutureTask.run(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: ORA-24816: Expanded non LONG bind data supplied after actual LONG or LOB column

	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:630)
	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:564)
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1151)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:771)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:299)
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:498)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:152)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1052)
	at oracle.jdbc.driver.OracleStatement.executeSQLStatement(OracleStatement.java:1531)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1311)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3746)
	at oracle.jdbc.driver.OraclePreparedStatement.executeLargeUpdate(OraclePreparedStatement.java:3918)
	at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3897)
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:992)
	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136)
	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136)
	at org.jumpmind.db.sql.JdbcSqlTransaction.executePreparedUpdate(JdbcSqlTransaction.java:462)
	at org.jumpmind.db.sql.JdbcSqlTransaction.addRow(JdbcSqlTransaction.java:440)
	... 16 more
err.log (4,125 bytes)   

elong

2022-09-19 15:29

developer   ~0002198

I can reproduce it now. To avoid the error, I ordered the LONG column to be the last one referenced. Attaching a patch that fixes it for 3.12 through 3.14, which goes in the "patches" sub-directory. I also had to set the conflict manager to use the primary key for detecting conflicts, otherwise it puts the LONG into the "where" clause on updates and gets the same error. Here is the SQL to configure conflict manager:

INSERT INTO SYM_CONFLICT (CONFLICT_ID, SOURCE_NODE_GROUP_ID, TARGET_NODE_GROUP_ID, DETECT_TYPE, RESOLVE_TYPE, PING_BACK, RESOLVE_ROW_ONLY, CREATE_TIME, LAST_UPDATE_BY, LAST_UPDATE_TIME) VALUES ('fix-long', 'server', 'client', 'USE_PK_DATA', 'FALLBACK', 'OFF', 1, current_timestamp, 'no_user', current_timestamp);
patch-0005456.jar (8,283 bytes)

qii

2022-09-21 03:32

reporter   ~0002202

oh,thanks,I will test it.and you can try different length in clob and varchar(4000) ,like 2000<clob<4000 and varchar>2000, clob>4000 and varchar>2000, it makes different err.

qii

2022-10-17 07:52

reporter   ~0002206

hi, can you give me fix jar in version 3.12,patch-0005456.jar used in 3.14 ,I can not use, my version is 3.12 .

qii

2022-10-17 10:39

reporter   ~0002207

I set 3.14 symmetric-core/src/main/java/org/jumpmind/symmetric/model/Trigger.java method :orderColumnsForTable(Table table) copy to 3.12 .
did has other codes to copy?
update table set Long .length>4000 , set varchar2(4000).length>2000 where varchar2(4000).length>2000 pk=1 .
error is ORA-24816: Expanded non LONG bind data supplied after actual LONG or LOB column。 my oracle version is 11.2.0.1.0 and 10.2.0.1.0. symmetricds version is 3.12.

qii

2022-10-19 06:00

reporter   ~0002208

hi, I changed trigger.java in version 3.12 ,but LOB column did not in last column when update. can you fix it in 3.12?

elong

2022-10-27 18:16

developer   ~0002212

Here is the patch built for version 3.12. You also need this configuration as part of the fix:

INSERT INTO SYM_CONFLICT (CONFLICT_ID, SOURCE_NODE_GROUP_ID, TARGET_NODE_GROUP_ID, DETECT_TYPE, RESOLVE_TYPE, PING_BACK, RESOLVE_ROW_ONLY, CREATE_TIME, LAST_UPDATE_BY, LAST_UPDATE_TIME) VALUES ('fix-long', 'server', 'client', 'USE_PK_DATA', 'FALLBACK', 'OFF', 1, current_timestamp, 'no_user', current_timestamp);
patch-0005456-312.jar (8,292 bytes)

qii

2022-11-01 11:00

reporter   ~0002213

I can relpace Trigger.java method :orderColumnsForTable(Table table) 3.14 to 3.12 and rebuild sym_trigger , the Long cloumn remove to last. thanks.

Related Changesets

SymmetricDS: 3.14 8ac318fb

2022-10-10 16:59:01

admin

Details Diff 		0005456: Oracle LONG type must be last column updated Affected Issues
0005456
mod - symmetric-core/src/main/java/org/jumpmind/symmetric/model/Trigger.java Diff File

SymmetricDS: 3.14 35b9e901

2022-10-17 13:16:29

evan-miller-jumpmind

Details Diff 		0005456: Fixed NPE when checking for Oracle LONG type Affected Issues
0005456
mod - symmetric-core/src/main/java/org/jumpmind/symmetric/model/Trigger.java Diff File

Issue History

Date Modified Username Field Change
2022-09-13 07:44 qii New Issue
2022-09-13 13:12 elong Note Added: 0002192
2022-09-13 15:20 elong Assigned To => elong
2022-09-13 15:20 elong Status new => feedback
2022-09-14 10:25 qii Note Added: 0002196
2022-09-14 10:25 qii File Added: err.log
2022-09-14 10:25 qii Status feedback => assigned
2022-09-14 18:22 elong Summary canot sync varchar2(4000) and clob in same table => ORA-24816 when sync varchar2(4000) and long in same table
2022-09-14 18:22 elong Steps to Reproduce Updated View Revisions
2022-09-14 18:22 elong Additional Information Updated View Revisions
2022-09-19 15:29 elong Note Added: 0002198
2022-09-19 15:29 elong File Added: patch-0005456.jar
2022-09-21 03:32 qii Note Added: 0002202
2022-10-10 16:59 elong Status assigned => resolved
2022-10-10 16:59 elong Resolution open => fixed
2022-10-10 16:59 elong Fixed in Version => 3.14.3
2022-10-10 17:00 admin Changeset attached => SymmetricDS 3.14 8ac318fb
2022-10-17 07:52 qii Status resolved => feedback
2022-10-17 07:52 qii Resolution fixed => reopened
2022-10-17 07:52 qii Note Added: 0002206
2022-10-17 10:39 qii Note Added: 0002207
2022-10-17 10:39 qii Status feedback => assigned
2022-10-17 14:00 Changeset attached => SymmetricDS 3.14 35b9e901
2022-10-19 06:00 qii Note Added: 0002208
2022-10-27 18:16 elong Note Added: 0002212
2022-10-27 18:16 elong File Added: patch-0005456-312.jar
2022-11-01 11:00 qii Note Added: 0002213
2022-11-02 17:27 elong Status assigned => closed