Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

COPY BINARY INTO fails on 6gb file; works fine on 3gb #3513

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments
Closed

COPY BINARY INTO fails on 6gb file; works fine on 3gb #3513

monetdb-team opened this issue Nov 30, 2020 · 0 comments

Comments

@monetdb-team
Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

Date: 2014-07-15 09:49:41 +0200
From: Clint Cummins <>
To: SQL devs <>
Version: 11.17.21 (Jan2014-SP3)
CC: @hannesmuehleisen, @njnes

Last updated: 2014-10-31 14:14:06 +0100

Comment 19904

Date: 2014-07-15 09:49:41 +0200
From: Clint Cummins <>

User-Agent: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; WOW64; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; InfoPath.3; .NET4.0C; .NET4.0E)
Build Identifier:

I am testing tables with 800 million rows.
When I try COPY BINARY INTO with a BIGINT or DOUBLE column, this is a 6.25gb file
(6,250,000 kb). I get the error message:
"failed to attach file".
It works fine with an INT column (3.125gb file), or with 4 TINYINT columns (from 4 files).

Reproducible: Always

Steps to Reproduce:

  1. Create a 6,250,000 kb file named foo.r8 with 800,000,000 DOUBLE values.
  2. create table b800m ( foo double );
  3. copy binary into b800m from ( 'c:\users\ccummins\appdata\foo.r8' );

Actual Results:

Error message: "failed to attach file"

Expected Results:

800000000 rows affected

mserver5 --version:
libpcre: 8.13 2011-08-16
openssl: OpenSSL 1.0.1b 26 Apr 2012
libxml2: 2.7.8

Server startup:
"Found 127.973 GiB available main-memory."

Comment 19908

Date: 2014-07-18 03:13:05 +0200
From: Clint Cummins <>

Created attachment 291
screenshot - crash for COPY BINARY INTO table FROM 4gb file

I4gb.i8 is a 4gb file;
COPY BINARY INTO i4gb FROM ( 'i4gb.i8' ); -- crashes mserver5.exe

I4ga.i8 is 8 bytes smaller, and COPY BINARY INTO works fine with it.

So 4gb is apparently the dividing line between files which work and
those which do not. My prior examples were 3.125gb and 6.25 gb.

Attached file: copy_binary_into_4gb_filesize_bug.gif (image/gif, 27207 bytes)
Description: screenshot - crash for COPY BINARY INTO table FROM 4gb file

Comment 19909

Date: 2014-07-18 03:15:55 +0200
From: Clint Cummins <>

I refined the bug description:
COPY BINARY INTO table FROM ( 'file' );
crashes on a 4gb file,
but works fine on a file which is 8 bytes shorter.

COPY BINARY INTO is a nice fast way to import data into MonetDB,
and I want to use it on large files like this frequently.

Comment 19953

Date: 2014-08-05 08:45:55 +0200
From: @njnes

seems to run just fine on linux, ie a windows related of build related bug.
Just to exclude the later, you are running a 64bit version of MonetDB?

Comment 19958

Date: 2014-08-06 00:34:54 +0200
From: Clint Cummins <>

(In reply to comment 3)

seems to run just fine on linux, ie a windows related of build related bug.
Just to exclude the later, you are running a 64bit version of MonetDB?

Yes, the attachment I added on 2014-07-18 shows the mserver5.exe version:
" Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked"

The mclient.exe --version doesn't mention 64bit.
Both .exe files are dated as the 2014-05-14 10:11 AM build.

Thank you for testing this on linux.
Given that it fails at the 4GB point on Windows 64,
I suppose we might suspect there is a 32-bit integer somewhere in the Windows
i/o interface which should be 64-bit?

Comment 19960

Date: 2014-08-06 10:43:52 +0200
From: @njnes

indeed we suspect the windows io interface.

Comment 19999

Date: 2014-08-07 11:31:45 +0200
From: @hannesmuehleisen

Confirmed for Jan2014-SP3 on Windows7 64 bits

sql>create table foo(i integer);
operation successful (45.181ms)
sql>copy binary into foo from ('c:\Users\hannes\2bnints');
786432000 affected row (2m 24s)
sql>create table foo2(i integer);
operation successful (110.954ms)
sql>copy binary into foo2 from ('c:\Users\hannes\1bnints');
failed to attach file c:\Users\hannes\1bnints

The last error message appeared immediately. File sizes:
1bnints 5242880000 (> 4 GB)
2bnints 3145728000 (< 4 GB)

Comment 20003

Date: 2014-08-07 14:03:54 +0200
From: MonetDB Mercurial Repository <>

Changeset 1ae0470d6038 made by Hannes Muehleisen hannes@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=1ae0470d6038

Changeset description:

Possible fix for Bug #3513

Comment 20027

Date: 2014-08-11 17:33:55 +0200
From: @hannesmuehleisen

Confirmed fixed in Jan2014 branch. You may try the test only build at http://monetdb.cwi.nl/testweb/web/52388:64939b9031c6/MonetDB5-SQL-Installer-x86_64-64939b9031c6.msi to confirm.

sql>copy binary into foo from ('c:\Users\hannes\1bnints');
1310720000 affected row (1m 15s)

Comment 20030

Date: 2014-08-12 01:15:19 +0200
From: Clint Cummins <>

(In reply to comment 8)

Confirmed fixed in Jan2014 branch. You may try the test only build at
http://monetdb.cwi.nl/testweb/web/52388:64939b9031c6/MonetDB5-SQL-Installer-
x86_64-64939b9031c6.msi to confirm.

Thanks - I confirmed that this fixes the problem.

It was very resourceful to think of trying the O_BINARY flag for this.

Comment 20354

Date: 2014-10-31 14:14:06 +0100
From: @sjoerdmullender

Oct2014 has been released.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant