ALTER TABLE ... ENGINE=PBXT fails on large tables

[Brian Evans]

OS: Windows XP 32-bit
Disk space free: 33GB on NTFS
RAM size: 2GB
MySQL 5.1.34
PBXT trunk version 645
Built using Visual C++ 2008 Express

While evaluating PBXT on a development machine, I am encountering errors altering a large table from InnoDB to PBXT.

Number of rows: 1.8 million
Structure:
CREATE TABLE `audit` (
  `ID` int(11) unsigned NOT NULL auto_increment,
  `RefID` int(11) unsigned NOT NULL,
  `AuditCatagory` varchar(64) NOT NULL,
  `UserID` varchar(64) NOT NULL,
  `Descript` text NOT NULL,
  `EntryDate` date NOT NULL,
  `EntryTime` time NOT NULL,
  `Comments` text NOT NULL,
  `Details` text NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `db_index` USING BTREE (`RefID`),
  KEY `db_index_c` USING BTREE (`AuditCatagory`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Audit Entry Log'

Errors:
090521 12:59:18 [Error] WR-data fs_map_file(filesys_xt.cc:1177) errno (8): Not enough storage is available to process this command: '.\miabella\#sql-ecc_1.xtd'
090521 12:59:18 [Error] WR-data fs_map_file(filesys_xt.cc:1177)
090521 13:01:24 [Error] WR-data fs_remap_file(filesys_xt.cc:1356) errno (6): The handle is invalid: '.\miabella\#sql-ecc_1.xtd'
090521 13:01:24 [Error] WR-data fs_remap_file(filesys_xt.cc:1356)
090521 13:04:19 [Error] WR-data fs_remap_file(filesys_xt.cc:1356) errno (6): The handle is invalid: '.\miabella\#sql-ecc_1.xtd'
090521 13:04:19 [Error] WR-data fs_remap_file(filesys_xt.cc:1356)
090521 13:07:14 [Error] WR-data fs_remap_file(filesys_xt.cc:1356) errno (6): The handle is invalid: '.\miabella\#sql-ecc_1.xtd'
090521 13:07:14 [Error] WR-data fs_remap_file(filesys_xt.cc:1356)

The final error repeats until mysql is forced closed (or possibly hours later).

[Brian Evans (grknight-scent-team)]

This also happens with LOAD DATA INFILE to an empty table that is using PBXT.

[Vladimir Kolesnikov (vkolesnikov)]

Hi Brian,

thanks for the report. Do you have test data you can share?

[Brian Evans (grknight-scent-team)]

I'll have to work on that.

The file contains some confidential information that would need to be obscured.

[Brian Evans (grknight-scent-team)]

Reproducable data dump (CSV) using the structure above.

Use this SQL to pull it in:
LOAD DATA INFILE 'resultob.txt'
INTO TABLEaudit
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n';

Data was obscured by inserting a random number of 'x' into the text fields.
This created a dump nearly twice the size of the real data.

[Vladimir Kolesnikov (vkolesnikov)]

Brian,

thanks or supplying the data. FYI: this works fine for me on 5.1.30. I'm planning to make 5.1.34 checks.

[Brian Evans (grknight-scent-team)]

I think this note from Microsoft may be relevant.

    * The size of a file mapping object that is backed by a named file is limited by disk space. The size of a file view is limited to the largest available contiguous block of unreserved virtual memory. This is at most 2 GB minus the virtual memory already reserved by the process.

Source http://msdn.microsoft.com/en-us/library/aa366542(VS.85).aspx

This is also fails with Version: '5.1.35-maria-beta1-log' using lp:pbxt/stable

[Vladimir Kolesnikov (vkolesnikov)]

Yep, I know that, but I assumed this is irrelevant.

Please check if you have defined XT_USE_ROW_REC_MMAP_FILES in xt_defs.h . This controls whether the engine uses memory mapped files or not. The macro should be undefined. If it is defined, just comment it out and rebuild.

[Vladimir Kolesnikov (vkolesnikov)]

(ok, now I see this is obvious from error messages you got..)

[Brian Evans (grknight-scent-team)]

When I #ifndef XT_WIN that parameter, I get this build error and warnings:

table_xt.cc
.\table_xt.cc(3417) : warning C4002: too many actual parameters for macro 'XT_LOCK_MEMORY_PTR'
.\table_xt.cc(5041) : error C2664: 'xt_pread_fmap' : cannot convert parameter 1 from 'XTOpenFilePtr' to 'XTMapFilePtr'
        Types pointed to are unrelated; conversion requires reinterpret_cast, C-style cast or function-style cast
.\table_xt.cc(5095) : warning C4002: too many actual parameters for macro 'XT_LOCK_MEMORY_PTR'
.\table_xt.cc(5096) : warning C4390: ';' : empty controlled statement found; is this the intent?
.\table_xt.cc(5130) : warning C4390: ';' : empty controlled statement found; is this the intent?
.\table_xt.cc(5137) : warning C4390: ';' : empty controlled statement found; is this the intent?

[Vladimir Kolesnikov (vkolesnikov)]

Can you try the latest lp:pbxt ? (or /stable). It has the macro turned off and compiles well for me on win xp 32-bit visual studio-2005

[Brian Evans (grknight-scent-team)]

lp:pbxt/stable rev 653 has no more issues of this case.

It seems the commits since the bug opened have resolved this issue.

May be good to note that Windows 32bit does not like large memory files somewhere.

[Vladimir Kolesnikov (vkolesnikov)]

Thanks for checking. Yes, we'll probably need to mention this in our documentation. I close the bug.