MySQL Bugs: #21675: engine=archive 2GB file limit, when reached mysqld restarts on any query

Bug #21675	engine=archive 2GB file limit, when reached mysqld restarts on any query
Submitted:	16 Aug 2006 18:11	Modified:	4 Oct 2006 19:06
Reporter:	Jeff C	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.24/5.0BK	OS:	Linux (RHEL4)
Assigned to:	Sergey Vojtovich	CPU Architecture:	Any

Description:
The docs say this bug was fixed, however I'm having issues..
(See : http://bugs.mysql.com/bug.php?id=15787  -- closed)

My table using "engine=archive" hit 2GB in size and I never noticed.  I did not realize engine=archive is capped at 2GB (it's not an OS limitation, and I did not find it documented).

Once the table hit 2GB...

I ran : mysqlcheck -CqA and mysqld went down.  After testing, I ran: mysql> check table archivetable quick;  <mysql restart (no stack trace)>

Selecting from the table, mysqld restarts.  The only solution was to "rm -f" the table and recreate it as myiasm.

archive works great on 2GB or less.

Thanks,
Jeff

How to repeat:
create table whatever (yourchoice) engine=archive;

<get the data size to be >2GB>

run: check table whatever quick;

Now mysqld will restart whenever you reference this table anywhere.

select/delete/check  ... I did not try drop or truncate tho.

Thank you for the bug report. Indeed still limited to 2GB:

miguel@light:~> dbs/5.0/bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.25-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like "%have_ar%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_archive  | YES   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> create database myarch; use myarch;
Query OK, 1 row affected (0.00 sec)

Database changed
mysql>  create table whatever (yourchoice blob,
    ->  mychoice blob) engine=archive;
Query OK, 0 rows affected (0.10 sec)

mysql> show create table whatever\G
*************************** 1. row ***************************
       Table: whatever
Create Table: CREATE TABLE `whatever` (
  `yourchoice` blob,
  `mychoice` blob
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

insert into whatever values ( LOAD_FILE("/home/miguel/dbs/5.1/libexec/mysqld"), LOAD_FILE("/home/miguel/dbs/5.1/libexec/mysqld"));
<cut>
mysql> insert into whatever  select * from whatever;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>                                                                

The server shutdown with the below message:

060816 19:23:01 [Note] libexec/mysqld: ready for connections.
Version: '5.0.25-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
File size limit exceeded
miguel@light:~/dbs/5.0>

miguel@light:~/dbs/5.0> ls -la var/myarch/whatever.ARZ
-rw-rw---- 1 miguel users 2147483647 2006-08-16 20:06 var/myarch/whatever.ARZ

Miguel, Jeff,

where did you get mysql binaries? Or you have compiled it from sources? Could you also send ldd mysqld output?

This problem was fixed in bundled zlib, but there is no way to fix it in system installed zlib. And I suspect your binaries are linked against system installed zlib.

Thanks!

Sergey,

I compiled the sources with BUILD/compile-pentium-debug-max --prefix=...

the above compilation isn't linked against our zlib stuff?, if not could be
necessary to document how to compile against the patched zilb lib?

Thanks.

I download your RPM's from mysql.com/doc -> downloads -> 5.0 -> RHEL4 RPM's, and then generic dynamically linked MAX rpm

/usr/sbin/mysqld:
        librt.so.1 => /lib/tls/librt.so.1 (0x00551000)
        libpthread.so.0 => /lib/tls/libpthread.so.0 (0x003e7000)
        libdl.so.2 => /lib/libdl.so.2 (0x003e1000)
        libcrypt.so.1 => /lib/libcrypt.so.1 (0x006e3000)
        libnsl.so.1 => /lib/libnsl.so.1 (0x0076e000)
        libm.so.6 => /lib/tls/libm.so.6 (0x003bc000)
        libc.so.6 => /lib/tls/libc.so.6 (0x0028f000)
        /lib/ld-linux.so.2 (0x00276000)

/usr/sbin/mysqld-max:
        librt.so.1 => /lib/tls/librt.so.1 (0x00551000)
        libz.so.1 => /usr/lib/libz.so.1 (0x003fb000)
        libpthread.so.0 => /lib/tls/libpthread.so.0 (0x003e7000)
        libdl.so.2 => /lib/libdl.so.2 (0x003e1000)
        libcrypt.so.1 => /lib/libcrypt.so.1 (0x006e3000)
        libnsl.so.1 => /lib/libnsl.so.1 (0x0076e000)
        libm.so.6 => /lib/tls/libm.so.6 (0x003bc000)
        libc.so.6 => /lib/tls/libc.so.6 (0x0028f000)
        /lib/ld-linux.so.2 (0x00276000)

Thanks for your comments. We will fix the crash. Also we probably must link max against bundled zlib and document this issue.

BUILD/compile-pentium-debug-max --with-zlib-dir=bundled should fix the problem with custom builds.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/11912

ChangeSet@1.2246, 2006-09-14 11:19:11+05:00, svoj@april.(none) +1 -0
  BUG#21675 - engine=archive 2GB file limit, when reached mysqld restarts on any query
  
  If mysqld is linked against system installed zlib (which is likely compiled w/o
  LFS) and archive table exceedes 2G, mysqld will likely be terminated with SIGXFSZ.
  
  Prior to actual write perform a check if there is space in data file. This fixes
  abnormal process termination with SIGXFSZ.
  
  No test case for this bugfix.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/12266

ChangeSet@1.2246, 2006-09-20 14:54:26+05:00, svoj@mysql.com +2 -0
  BUG#21675 - engine=archive 2GB file limit, when reached mysqld restarts on any query
  
  If mysqld is linked against system installed zlib (which is likely compiled w/o
  LFS) and archive table exceedes 2G, mysqld will likely be terminated with SIGXFSZ.
  
  Prior to actual write perform a check if there is space in data file. This fixes
  abnormal process termination with SIGXFSZ.
  
  No test case for this bugfix.

Note: only 5.0 was affected. In 5.1 we use azio instead of gzio.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/12855

ChangeSet@1.2285, 2006-09-29 21:36:17+05:00, svoj@mysql.com +1 -0
  Fixed that max_zfile_size was incorrectly calculated on big-endian boxes.
  Was introduced with patch for bug#21675.

Fixed in 5.0.26.

Noted in 5.0.26.

The ARCHIVE engine incorrectly calculated the maximum file size on
big-endian machines, resulting in a server crash when the file
reached 2GB.

5.0.32-Debian_7etch5-log
I experience the sames crashes. I made a loop to populate a database with the archive engine. 

saber:/var/lib/mysql/test# du -h
2,1G    .

During this loop, mysql lost the connexion. Then I looked into phpmyadmin, but when i list the database, mysql restarts. 

Jun  9 14:47:26 saber mysqld_safe[6573]: Number of processes running now: 0
Jun  9 14:47:26 saber mysqld_safe[6575]: restarted
Jun  9 14:47:27 saber mysqld[6578]: 080609 14:47:27 [Note] /usr/sbin/mysqld: ready for connections.
Jun  9 14:47:27 saber mysqld[6578]: Version: '5.0.32-Debian_7etch5-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Debian etch distribution