There seems some buffer limitation when use sqlcmd to run query

[25bobby]

There seems some kind of buffer limitation when using the sqlcmd tool to run query.

Problem:
When I use sqlcmd to run restore database sql query, it will hang if I keep the STATS=5 keyword in the query (see manual_testing_setup_script.sql below). Also, when use the sqlcmd to run query files with bunch of update query statement, I can only run 4 update statements at a time and it will hang at the fifth update statement.

Reproduce:
Below is the log when try to reproduce the issue. In the Dockerfile, besides copies the sql query file and download the sqlcmd to the container, I will start the server and run a simple restore database query but when it does that, it will hang and stop at 30 percent processed.

For this test, I used Microsoft northwind.bak file.

=> [internal] load .dockerignore                                                                                                                                                         0.0s
 => => transferring context: 2B                                                                                                                                                           0.0s
 => [internal] load metadata for mcr.microsoft.com/azure-sql-edge:latest                                                                                                                  0.0s
 => CACHED [ 1/15] FROM mcr.microsoft.com/azure-sql-edge:latest                                                                                                                           0.0s
 => [internal] load build context                                                                                                                                                         0.0s
 => => transferring context: 758B                                                                                                                                                         0.0s
 => [2/8] COPY --chown=mssql:root manual_testing_setup_script.sql /backup/manual_testing_setup_script.sql                                                                                 0.0s
 => [3/8] COPY --chown=mssql:root northwind.bak /backup/northwind.bak                                                                                                                     0.0s
 => [4/8] COPY --chown=mssql:root restore_db_test.sh /backup/restore_db_test.sh                                                                                                           0.0s
 => [5/8] RUN chmod a+rwx /backup/restore_db_test.sh                                                                                                                                      0.2s
 => [6/8] RUN chmod g+rwx /backup/restore_db_test.sh                                                                                                                                      0.2s
 => [7/8] RUN if [ ! -d /opt/mssql-tools/bin ] ; then         echo '\"/opt/mssql-tools/bin\"" directory not found, download the sqlcmd tool...' >> /tmp/arm64.log         && mkdir -p /o  3.0s
 => [8/8] RUN /backup/restore_db_test.sh                                                                                                                                                276.4s
 => => # 5 percent processed.                                                                                                                                                                 
 => => # 11 percent processed.                                                                                                                                                                
 => => # 15 percent processed.                                                                                                                                                                 
 => => # 20 percent processed.                                                                                                                                                                 
 => => # 26 percent processed.                                                                                                                                                                 
 => => # 30 percent processed.                                                                                                                                                                 

this is my sql query in the manual_testing_setup_script.sql file

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 2048;
GO
RECONFIGURE;
GO

RESTORE DATABASE [northwind] 
FROM  
	DISK = N'/backup/northwind.bak' 
WITH  
  FILE = 1,  
  MOVE N'northwind' TO N'/var/opt/mssql/data/northwind.mdf',
  MOVE N'northwind_log' TO N'/var/opt/mssql/data/northwind.LDF',
	NOUNLOAD,
  STATS = 5
GO

If I removed the STATS = 5 in my query, the build will succeed.

Below is the content in my restore_db_test.sh file

export ACCEPT_EULA=Y
export SA_PASSWORD='password1@'
export MSSQL_SA_PASSWORD='password1@'

echo "Starting up sql server..."
/opt/mssql/bin/sqlservr > /backup/startup.log &

echo "  Waiting a few seconds for sql server to start..."
sleep 30

echo "Run sql server setup ....."
/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -d master -i /backup/manual_testing_setup_script.sql

The same issue also happened when I try to run multiple DB update statements. I will need to break them down into smaller batches, then it can run successfully without hanging.

Finally, the same issue doesn't happen when I use the Azure Data Studio sqlcmd tool to run the query or to restore the database.

Thank you for your time and help. Please let me know if you need more information.

Original question: #36 (reply in thread)

[shueybubbles]

opened a PR for the driver fix denisenkom/go-mssqldb#723

[25bobby]

Thank you very much for the fix. Btw, do you know when the release build might be ready if the PR is checked in? Thanks.

[shueybubbles]

I can turn around a new sqlcmd release fairly quickly once go-mssqldb takes the fix.

[25bobby]

Sounds great. Please post a comment when the new release is ready. Thanks.

[shueybubbles]

Please try v0.4.0

[25bobby]

Thank you.