View Issue Details

Related ChangesetsJump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0005560SymmetricDSBugpublic2022-10-31 14:022022-11-30 19:49
Reporterqii Assigned Toelong  
Prioritynormal 
Status closedResolutionfixed 
Product Version3.12.6 
Target Version3.14.3Fixed in Version3.14.3 
Summary0005560: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Descriptiondatabase version :oracle 10g, symmetric version 3.12.
I have 3 VARCHAR2(4000) colunms in my table , insert table ( cloumn1,cloumn2,cloumn3) values(
length.size>2000,length.size>2000,length.size>2000
) sync failed by this:
ORA-06512: at line 1

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1052)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:537)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:255)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:610)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:253)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:86)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:765)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:921)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1099)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3640)
at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1384)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3687)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1165)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122)
at org.jumpmind.db.sql.JdbcSqlReadCursor.<init>(JdbcSqlReadCursor.java:100)
... 8 more
Caused by: Error : 6502, Position : 83, Sql = select /+ index(d sym_IDX_D_CHANNEL_ID) / d.data_id, d.table_name, d.event_type, dbms_lob.substr(d.row_data, 4000, 1 ) as row_data, dbms_lob.substr(d.pk_data, 4000, 1 ) as pk_data, dbms_lob.substr(d.old_data, 4000, 1 ) as old_data, d.create_time, d.trigger_hist_id, d.channel_id, d.transaction_id, d.source_node_id, d.external_data, d.node_list, d.is_prerouted from sym_data d where d.channel_id=:1 and ((d.data_id between :2 and :3 )) order by d.data_id asc , OriginalSql = select /+ index(d sym_IDX_D_CHANNEL_ID) / d.data_id, d.table_name, d.event_type, dbms_lob.substr(d.row_data, 4000, 1 ) as row_data, dbms_lob.substr(d.pk_data, 4000, 1 ) as pk_data, dbms_lob.substr(d.old_data, 4000, 1 ) as old_data, d.create_time, d.trigger_hist_id, d.channel_id, d.transaction_id, d.source_node_id, d.external_data, d.node_list, d.is_prerouted from sym_data d where d.channel_id=? and ((d.data_id between ? and ?)) order by d.data_id asc , Error Msg = ORA-06502: PL/SQL: numeric or value error: character string buffer too small

I set sync_channel .CONTAINS_BIG_LOB= 1,it work. but I do not to use it ,maybe 10000 datas can occur ones, most time used dbms_lob.substr(d.pk_data, 4000, 1 ) are ok.
i found routeService.java method routeDataForChannel() is run in exception, set isContainsLob =true , but it not always happen ,sometimes sync error . the sync data was lost when error happend. i want to used sym_data.row_data.length() to set isContainsLob true or false
Steps To Reproduce have 3 VARCHAR2(4000) colunms in my table , insert table ( cloumn1,cloumn2,cloumn3) values(
length.size>2000,length.size>2000,length.size>2000
)
TagsNo tags attached.

Activities

qii

2022-11-01 12:10

reporter   ~0002214

I set DataGapRouteReader.prepareCursor() method edit code this,
 try {
            cursor = sqlTemplate.queryForCursor(sql, dataMapper, args, types);
        } catch (RuntimeException e) {
            log.info("Failed to execute query, but will try again,", e);
            if(e.getCause().getMessage().contains("ORA-06502")){
                sql = StringUtils.replace(sql, "dbms_lob.substr(d.row_data, 4000, 1 )", "d.row_data");
                sql = StringUtils.replace(sql, "dbms_lob.substr(d.old_data, 4000, 1 )", "d.old_data");
                sql = StringUtils.replace(sql, "dbms_lob.substr(d.pk_data, 4000, 1 )", "d.pk_data");
            }
            AppUtils.sleep(1000);
            cursor = sqlTemplate.queryForCursor(sql, dataMapper, args, types);
        }
they always trigger catch :Re-attempting extraction for batch {} with contains_big_lobs temporarily enabled for channel {},is amazing, did i change it right?

jvanmeter

2022-11-02 17:25

developer   ~0002215

Yes, it looks like you implemented it correctly. The only question we have is why you have a sleep for 1 second towards the end, and that you forgot to close the first cursor before creating the second one.

qii

2022-11-03 01:32

reporter   ~0002216

its your code ,I only write this:
if(e.getCause().getMessage().contains("ORA-06502")){
                sql = StringUtils.replace(sql, "dbms_lob.substr(d.row_data, 4000, 1 )", "d.row_data");
                sql = StringUtils.replace(sql, "dbms_lob.substr(d.old_data, 4000, 1 )", "d.old_data");
                sql = StringUtils.replace(sql, "dbms_lob.substr(d.pk_data, 4000, 1 )", "d.pk_data");
            }
i dont konw why it always trigger catch :Re-attempting extraction for batch {} with contains_big_lobs temporarily enabled for channel {},and why before changed it ,The exception be catched and do not Re-attempting ,maybe you should check this code.

qii

2022-11-03 01:38

reporter   ~0002217

forgot to close the first cursor before creating the second one.? What will happen if I don't colse this,your code did not colse.

elong

2022-11-07 21:12

developer   ~0002218

If it doesn't happen often, the cursor will be closed when the connection closes, and it should be fine.

elong

2022-11-07 21:24

developer   ~0002219

Fixed for Oracle and Firebird.

Related Changesets

SymmetricDS: 3.14 f81ef1cd

2022-11-07 21:11:12

admin

Details Diff 		0005560: retry routing with contains big lobs enabled Affected Issues
0005560
mod - symmetric-core/src/main/java/org/jumpmind/symmetric/route/DataGapRouteReader.java Diff File
mod - symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/DataExtractorService.java Diff File
mod - symmetric-jdbc/src/main/java/org/jumpmind/db/platform/firebird/FirebirdJdbcSqlTemplate.java Diff File
mod - symmetric-jdbc/src/main/java/org/jumpmind/db/platform/oracle/OracleJdbcSqlTemplate.java Diff File
mod - symmetric-jdbc/src/main/java/org/jumpmind/db/platform/postgresql/PostgreSqlJdbcSqlTemplate.java Diff File
mod - symmetric-jdbc/src/main/java/org/jumpmind/db/sql/JdbcSqlTemplate.java Diff File

Issue History

Date Modified Username Field Change
2022-10-31 14:02 qii New Issue
2022-11-01 12:10 qii Note Added: 0002214
2022-11-02 17:25 jvanmeter Note Added: 0002215
2022-11-03 01:32 qii Note Added: 0002216
2022-11-03 01:38 qii Note Added: 0002217
2022-11-07 21:12 elong Note Added: 0002218
2022-11-07 21:13 elong Assigned To => elong
2022-11-07 21:13 elong Status new => assigned
2022-11-07 21:13 elong Target Version => 3.14.3
2022-11-07 21:24 elong Status assigned => resolved
2022-11-07 21:24 elong Resolution open => fixed
2022-11-07 21:24 elong Fixed in Version => 3.14.3
2022-11-07 21:24 elong Note Added: 0002219
2022-11-07 22:00 admin Changeset attached => SymmetricDS 3.14 f81ef1cd
2022-11-30 19:49 admin Status resolved => closed