[QUESTION] SQLServerBulk Copy: Unable to retrieve column metadata.

[orgesballa]

Question

I am trying to benefit from SQLServerBulkCopy to insert millions of rows in the database efficiently.

I am using Spring Boot + Spring JDBC to write the queries.

spring.datasource.url=jdbc:sqlserver://localhost:1433;database=test;encrypt=true;trustServerCertificate=true;hostNameInCertificate=*.database.windows.net;loginTimeout=30;useBulkCopyForBatchInsert=true;

Driver version: 12.8.0.jre11

The issue happens on two tables that I have. One uses Identity column and one sets the ID explicitly.

2024-08-05T16:56:52.992+02:00 DEBUG 5064 --- [app] [         task-2] c.m.s.jdbc.internals.SQLServerException  : *** SQLException: com.microsoft.sqlserver.jdbc.SQLServerException: Unable to retrieve column metadata. Unable to retrieve column metadata.

2024-08-05T16:56:52.993+02:00 DEBUG 5064 --- [app] [         task-2] c.m.s.jdbc.internals.SQLServerException  : com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.getSourceMetadata(SQLServerBulkCopy.java:1870)com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.writeToServer(SQLServerBulkCopy.java:1677)com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.writeToServer(SQLServerBulkCopy.java:641)com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(SQLServerPreparedStatement.java:2389)com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:127)com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeBatch(HikariProxyPreparedStatement.java)org.springframework.jdbc.core.JdbcTemplate.lambda$batchUpdate$4(JdbcTemplate.java:1117)org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:658)org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:701)org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:1101)com.exampe.repository.TestRepository.saveAllDataInBatch(TestRepository.java:82)java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)java.base/java.lang.reflect.Method.invoke(Method.java:580)org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:354)org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196)org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:768)org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:392)org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:768)org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:720)

Before this I see the query being executed:

2024-08-05T16:56:52.990+02:00 DEBUG 5064 --- [app] [         task-2] c.m.s.jdbc.internals.SQLServerStatement  : SQLServerStatement:59 Executing (not server cursor) select collation_name, is_computed, encryption_type from sys.columns where object_id=OBJECT_ID('DBO.MY_TABLE_NAME') order by column_id ASC

In fact the result of this is: all values NULL and is_computed false.

The columns in this table are:
ID bigint
DataFileID bigint
All others int.

    @Transactional
    public void saveAllDataInBatch(List<Data> dataList) {
        jdbcTemplate.batchUpdate("INSERT INTO DBO.MY_TABLE(id, data_file_id, weight, weight1, weight2, weight3, weight4) VALUES(?,?,?,?,?,?,?)", dataList, 100, (ps, data) -> {
            ps.setLong(1, data.getId());
            ps.setLong(2, data.getDataFileId());
            ps.setInt(3, data.getWeight());
            ps.setInt(4, data.getWeight1());
            ps.setInt(5, data.getWeight2());
            ps.setInt(6, data.getWeight3())
            ps.setInt(7, data.getWeight4());
        });
    }

I am not sure what is wrong since all limitations mentioned in the documentation are not happening.
Can someone guide me how to fix this?

[lilgreenbird]

hi @orgesballa

We are not aware of any issues with bulkcopy we have tests for bulkcopy here please take a look see what you are doing differently? If you are still having issues, please provide a schema and a standalone java repro for the issue so we can take a look.
Thanks

[Jeffery-Wasty]

Hi @orgesballa,

Are you still having issues with bulkcopy? If so, please provide a repro as mentioned above, if not, we will move forward with closing this issue.

[orgesballa]

@Jeffery-Wasty
Hi sorry for the late reply.
https://github.com/orgesballa/sql-bulk-copy-example

In this example, I tried to use the same way as in my real project. I used liquibase, kept the table the same with one FK and 5 INT columns.

The same error happened again, unable to retrieve column metadata.

If I remove the useBulkCopyForBatchInsert, the batch works as normally.

[lilgreenbird]

hi @orgesballa

Thanks for the repro we will take a look and get back to you.

[barryw-mssql]

Just an update @orgesballa. We have gotten the provided repro working and are trying to understand the reported issue.

[orgesballa]

Hi, thanks for the update.

[barryw-mssql]

The Spring framework is abstracting the problem making it difficult to determine exactly what is the issue. Is there any way you could replicate the issue calling the driver directly?

[orgesballa]

@barryw-mssql Sorry, haven't had the time. will try to provide an example today

[orgesballa]

@barryw-mssql I think I found the root cause.
While creating the example without spring, I used the same DB and the error still happened. However, when dropping the DB and creating the tables directly with query, then the insert worked.

Removing the liquibase and using directly the tables created, also makes it work for the original example.

So the issue is the way liquibase creates the tables combined with SqlServerBulkCopy introduces this error.

[barryw-mssql]

Interesting observation orgesballa. As the tables are created via SQL using the same MSSQL driver, ideally there should be no difference between how liquibase and manual SQL creates a table. Your observation that removing liquibase as the table creator indicates that it is doing something different. If you want to explore the differences between liquibase and manual table creation further, I suggest that you enable the JDBC driver logging (https://learn.microsoft.com/en-us/sql/connect/jdbc/tracing-driver-operation?view=sql-server-ver16) and inspect the SQL generated by liquibase.

Since you now have things working we will be closing this issue. Please feel free to reopen the issue if further assistance is required.

Thx,
Barry

[wooln]

@barryw-mssql I think I found the root cause. While creating the example without spring, I used the same DB and the error still happened. However, when dropping the DB and creating the tables directly with query, then the insert worked.
Removing the liquibase and using directly the tables created, also makes it work for the original example.
So the issue is the way liquibase creates the tables combined with SqlServerBulkCopy introduces this error.

I encountered the same problem when using executeBatch(). In my case, the reason is that the column names are case-sensitive. Refer to my issue #2588, PR #2589 . It is possible that there are differences in capitalization between the table created by liquibase and the one you created manually.