Table copy

On 2/4/25 11:51 AM, Andy Hartman wrote:

I'm copying a large table from mssql to PG using SImplysql and in the
Log I see this message

2025-02-04 14:42:17.975 EST [4796] ERROR:  unexpected EOF on client
connection with an open transaction

The above pretty much spells it out.

Something messed with the connection from the client.

You need to look at Postgres and system logs to see if you can track
down what?

FYI, more information would be helpful:

1) MS SQL version.

2) Postgres version.

3) SimplySQL version.

4) The complete copy command.

5) The amount of data being transferred.

6) The OS'es and their versions on both ends.

2025-02-04 14:42:17.975 EST [4796] CONTEXT:  COPY sqlt_data_1_2022_03,
line 208274199, column tagid
2025-02-04 14:42:17.975 EST [4796] STATEMENT:  COPY sqlt_data_1_2022_03
(tagid, intvalue, floatvalue, stringvalue, datevalue, dataintegrity,
t_stamp) FROM STDIN (FORMAT BINARY)
2025-02-04 14:42:18.158 EST [4796] FATAL:  terminating connection
because protocol synchronization was lost
2025-02-04 14:42:18.265 EST [4796] LOG:  could not send data to client:
An established connection was aborted by the software in your host machine.
it then dies with a data stream error.I have copied other tables on this
size with no problems.

 THanks.

--
Adrian Klaver
adrian.klaver@aklaver.com

mssql - 2016
PG - 16.1
latest release for Simplysql
Invoke-SqlBulkCopy -SourceConnectionName "src" -SourceTable
"sqlt_data_1_2022_03" -DestinationConnectionName "dst" -DestinationTable
"sqlt_data_1_2022_03" -BatchSize 10000 -Notify

2.4 billion records -- I have down other tables of same size no problems

mssql OS Windows Server 2019
PG OS Windows Server 2022

Table:
[image: image.png]

On Tue, Feb 4, 2025 at 3:02 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 2/5/25 05:40, Andy Hartman wrote:

mssql - 2016
PG - 16.1
latest release for Simplysql

Latest is a relative term and requires anyone in the future coming
across this thread to work out what you are talking about. For that
person the current version is 2.1.0.

Invoke-SqlBulkCopy -SourceConnectionName "src" -SourceTable
"sqlt_data_1_2022_03" -DestinationConnectionName "dst" -DestinationTable
"sqlt_data_1_2022_03" -BatchSize 10000 -Notify

2.4 billion records  -- I have down other tables of same size no problems

Same version of SimplySql?

Same source and destination databases?

Did you look at the Postgres and system logs to see if there was
relevant information?

mssql OS Windows Server 2019
PG OS Windows Server 2022

Table:
image.png

--
Adrian Klaver
adrian.klaver@aklaver.com

[6992]: ERROR: unexpected EOF on client connection with an open transaction 2025-02-05 12:19:44.919 EST [6992] CONTEXT: COPY sqlt_data_1_2022_03, line 24431524, column dataintegrity 2025-02-05 12:19:44.919 EST [6992] STATEMENT: COPY sqlt_data_1_2022_03 (tagid, intvalue, floatvalue, stringvalue, datevalue, dataintegrity, t_stamp) FROM STDIN (FORMAT BINARY) 2025-02-05 12:19:44.919 EST [6992] FATAL: terminating connection because protocol synchronization was lost
2025-02-05 12:19:44.919 EST [6992]ERROR: unexpected EOF on client connection with an open transaction 2025-02-05 12:19:44.919 EST [6992] CONTEXT: COPY sqlt_data_1_2022_03, line 24431524, column dataintegrity 2025-02-05 12:19:44.919 EST [6992] STATEMENT: COPY sqlt_data_1_2022_03 (tagid, intvalue, floatvalue, stringvalue, datevalue, dataintegrity, t_stamp) FROM STDIN (FORMAT BINARY) 2025-02-05 12:19:44.919 EST [6992] FATAL: terminating connection because protocol synchronization was lost CONTEXT: COPY sqlt_data_1_2022_03, line
24431524, column dataintegrity
2025-02-05 12:19:44.919 EST [6992]ERROR: unexpected EOF on client connection with an open transaction 2025-02-05 12:19:44.919 EST [6992] CONTEXT: COPY sqlt_data_1_2022_03, line 24431524, column dataintegrity 2025-02-05 12:19:44.919 EST [6992] STATEMENT: COPY sqlt_data_1_2022_03 (tagid, intvalue, floatvalue, stringvalue, datevalue, dataintegrity, t_stamp) FROM STDIN (FORMAT BINARY) 2025-02-05 12:19:44.919 EST [6992] FATAL: terminating connection because protocol synchronization was lost STATEMENT: COPY sqlt_data_1_2022_03
(tagid, intvalue, floatvalue, stringvalue, datevalue, dataintegrity,
t_stamp) FROM STDIN (FORMAT BINARY)
2025-02-05 12:19:44.919 EST [6992]ERROR: unexpected EOF on client connection with an open transaction 2025-02-05 12:19:44.919 EST [6992] CONTEXT: COPY sqlt_data_1_2022_03, line 24431524, column dataintegrity 2025-02-05 12:19:44.919 EST [6992] STATEMENT: COPY sqlt_data_1_2022_03 (tagid, intvalue, floatvalue, stringvalue, datevalue, dataintegrity, t_stamp) FROM STDIN (FORMAT BINARY) 2025-02-05 12:19:44.919 EST [6992] FATAL: terminating connection because protocol synchronization was lost FATAL: terminating connection because
protocol synchronization was lost

On Wed, Feb 5, 2025 at 11:15 AM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 2/5/25 9:46 AM, Andy Hartman wrote:

[6992] ERROR:  unexpected EOF on client connection with an open transaction
2025-02-05 12:19:44.919 EST [6992] CONTEXT:  COPY sqlt_data_1_2022_03,
line 24431524, column dataintegrity
2025-02-05 12:19:44.919 EST [6992] STATEMENT:  COPY sqlt_data_1_2022_03
(tagid, intvalue, floatvalue, stringvalue, datevalue, dataintegrity,
t_stamp) FROM STDIN (FORMAT BINARY)
2025-02-05 12:19:44.919 EST [6992] FATAL:  terminating connection
because protocol synchronization was lost

You need to look at the other end of the connection also, in other words
what is happening on the MS SQL Server 2016/Windows Server 2019 side?

Also is there anti-virus software running on either end?

--
Adrian Klaver
adrian.klaver@aklaver.com

nothing in log from mssql side and no anti-virus

On Wed, Feb 5, 2025 at 2:06 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

I also reduced batch size to 5000 on the last run .. I like using this
SImplySql because it's a script I can launch, so that's why I chose that
solution... I'm using it to load a History Env.

THanks again.

On Wed, Feb 5, 2025 at 4:05 PM Andy Hartman <hartman60home@gmail.com> wrote:

Could there have been a network hiccup? Or some sort of timeout?

If I needed to transfer 360GB of data, I'd probably do something old school
like:

1. write a PowerShell script to export a set of rows into a csv file, 7zip
compress it, then rsync or scp it to the target.
2. Write a bash script to decompress it then load the data into the PG
table.
3. Repeat (1) with the next set of data, and (2) until complete. Start the
second (1) while the first (2) is running.

That's how I migrated 12GB of Oracle data to PG (except of course bash, not
PowerShell).

On Wed, Feb 5, 2025 at 4:05 PM Andy Hartman <hartman60home@gmail.com> wrote:

nothing in log from mssql side and no anti-virus

On Wed, Feb 5, 2025 at 2:06 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 2/5/25 9:46 AM, Andy Hartman wrote:

[6992] ERROR: unexpected EOF on client connection with an open

transaction

2025-02-05 12:19:44.919 EST [6992] CONTEXT: COPY sqlt_data_1_2022_03,
line 24431524, column dataintegrity
2025-02-05 12:19:44.919 EST [6992] STATEMENT: COPY sqlt_data_1_2022_03
(tagid, intvalue, floatvalue, stringvalue, datevalue, dataintegrity,
t_stamp) FROM STDIN (FORMAT BINARY)
2025-02-05 12:19:44.919 EST [6992] FATAL: terminating connection
because protocol synchronization was lost

You need to look at the other end of the connection also, in other words
what is happening on the MS SQL Server 2016/Windows Server 2019 side?

Also is there anti-virus software running on either end?

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

On 2/5/25 13:05, Andy Hartman wrote:

nothing in log from mssql side and no  anti-virus

How about the Windows Server 2019 system log?

--
Adrian Klaver
adrian.klaver@aklaver.com

On 2/5/25 13:09, Andy Hartman wrote:

I also reduced batch size to 5000 on the last run  .. I like using this
SImplySql because it's a script I can launch, so that's why I chose that
solution...  I'm using it to load a History Env.

You really need to complete your thoughts, remember we have no idea what
you are seeing unless you tell us.

Reducing the batch size did what?

THanks again.

--
Adrian Klaver
adrian.klaver@aklaver.com

Reduce batch size still caused error as reported nothing in WIndows Server
log...

The SimplySql is slick because just qry from src and load to dst ... Is
there any way to somehow show more of the error in PS i tried the $error
but nothing very descriptive

On Wed, Feb 5, 2025 at 4:23 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

This is going to be a monthly process not just a 1 time exercise.

On Wed, Feb 5, 2025 at 5:58 PM Andy Hartman <hartman60home@gmail.com> wrote:

On 2/5/25 14:58, Andy Hartman wrote:

Reduce batch size still caused error as reported nothing in WIndows
Server log...

 The SimplySql is slick because just qry from src and load to dst ...
Is there any way to somehow show more of the error in PS i tried the
$error but nothing very descriptive

Slick, except for the part where it is not working.

I have no idea what is going on at this point.

Some questions:

1) I don't know where the below is coming from?:

2025-02-04 14:42:18.265 EST [4796] LOG: could not send data to client:
An established connection was aborted by the software in your host machine.

I have searched the Postgres and SimpleSql source and I cannot find it
in either.

Is the error message straight from the log or was it translated?

Where did you install Postgres from?

2) What are the exact versions for SimpleSql and the MS SQL Server and
Postgres drivers you are using?

3) What is the network setup between the source and destination databases?

4) What if you move an overall smaller quantity of data over?

On Wed, Feb 5, 2025 at 4:23 PM Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> wrote:

On 2/5/25 13:09, Andy Hartman wrote:

I also reduced batch size to 5000 on the last run  .. I like

using this

SImplySql because it's a script I can launch, so that's why I

chose that

solution...  I'm using it to load a History Env.

You really need to complete your thoughts, remember we have no idea
what
you are seeing unless you tell us.

Reducing the batch size did what?

THanks again.

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com