MERGE tables row count bug

SEND-PR: -*- send-pr -*-
SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as
SEND-PR: will all comments (text enclosed in `<' and `>').
SEND-PR:
From: kjmunn@eragen.com
To: mysql@lists.mysql.com
Subject: MERGE tables not reporting correct row count

>Description:
This report contains a doc bug (ommission) and a software bug.

We have a couple of tables with row counts approaching 2^32.
Although this LIMIT CANNOT BE FOUND ANYWHERE IN THE
DOCUMENTATION,
we experimentally determined that MAX_ROWS cannot be >2^32-1.

As a possible work around to the 32-bit limit, we tried using
MERGE
tables. We started by creating two identical MyISAM tables
(Table1,Table2) and populating them with 2^32-1 rows each.
(All rows were identical in order to make things easy.)
We then created a MERGE table as the union of Table1 and Table2.

Based on the description of the MERGE tables, we expected it to
be
the "sum of its parts". However, the ROW COUNT FOR THE MERGE
TABLE
DID NOT EQUAL THE SUM OF THE ROW COUNTS FOR THE 'MERGED' TABLES.

I would also appreciate knowing if and when I can expect
MAX_ROWS
to change from a 32-bit limit to a 64-bit limit.
>How-To-Repeat:
mysql> SHOW TABLES;
+-------------------+
| Tables_in_test |
+-------------------+
| MergeTable1Table2 |
| Table1 |
| Table2 |
+-------------------+
3 rows in set (0.00 sec)

mysql> SHOW CREATE TABLE Table1;

+--------+-------------------------------------------------- --------------------
-------------------------+
| Table | Create
Table
|

+--------+-------------------------------------------------- --------------------
-------------------------+
| Table1 | CREATE TABLE `Table1` (
`Id` int(11) NOT NULL default '0'
) TYPE=MyISAM MAX_ROWS=4294967295 |

+--------+-------------------------------------------------- --------------------
-------------------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE Table2;

+--------+-------------------------------------------------- --------------------
-------------------------+
| Table | Create
Table
|

+--------+-------------------------------------------------- --------------------
-------------------------+
| Table2 | CREATE TABLE `Table2` (
`Id` int(11) NOT NULL default '0'
) TYPE=MyISAM MAX_ROWS=4294967295 |

+--------+-------------------------------------------------- --------------------
-------------------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE MergeTable1Table2;

+-------------------+--------------------------------------- --------------------
-----------------------------------------------------+
| Table | Create
Table
|

+-------------------+--------------------------------------- --------------------
-----------------------------------------------------+
| MergeTable1Table2 | CREATE TABLE `MergeTable1Table2` (
`Id` int(11) NOT NULL default '0'
) TYPE=MRG_MyISAM UNION=(Table1,Table2) |

+-------------------+--------------------------------------- --------------------
-----------------------------------------------------+
1 row in set (0.00 sec)

(Tables populated via mysqlimport of a file containing 2^32
"1"'s.)

mysql> SELECT COUNT(*) FROM Table1;
+------------+
| COUNT(*) |
+------------+
| 4294967295 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM Table2;
+------------+
| COUNT(*) |
+------------+
| 4294967295 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM MergeTable1Table2;
+------------+
| COUNT(*) |
+------------+
| 4294967294 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM MergeTable1Table2 WHERE Id=1;
+------------+
| COUNT(*) |
+------------+
| 8589934590 |
+------------+
1 row in set (59 min 23.29 sec)

>Fix:
UNKNOWN (Suggest changing to 64-bit row limitation.)
>Submitter-Id:
>Originator: Kyle Munn
>Organization:
EraGen Biosciences
>MySQL support: none
>Synopsis: Row count on a MERGE table does not equal sum of row counts for merged tables.
>Severity: serious
>Priority: medium
>Category: mysql
>Class: sw-bug & doc-bug
>Release: mysql-3.23.49a (Official MySQL Binary)

>Environment:

System: Linux pinotnoir 2.4.7-64GB-SMP #7 SMP Mon Oct 1 06:49:22 EDT
2001 i686 unknown
Architecture: i686

Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
/usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i486-suse-linux/2.95.2/specs
gcc version 2.95.2 19991024 (release)
Compilation info: CC='gcc' CFLAGS='-Wimplicit -Wreturn-type
-Wid-clash-51 -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts
-Wformat -Wimplicit-function-dec -Wimplicit-int -Wparentheses
-Wsign-compare -Wwrite-strings -Wunused -mcpu=pentiumpro -O3
-fno-omit-frame-pointer' CXX='gcc' CXXFLAGS='-Wimplicit -Wreturn-type
-Wid-clash-51 -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts
-Wformat -Wimplicit-function-dec -Wimplicit-int -Wparentheses
-Wsign-compare -Wwrite-strings -Woverloaded-virtual -Wextern-inline
-Wsign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor
-felide-constructors -fno-exceptions -fno-rtti -mcpu=pentiumpro -O3
-fno-omit-frame-pointer' LDFLAGS=''
LIBC:
-rwxr-xr-x 1 root root 1382179 Jan 19 2001 /lib/libc.so.6
-rw-r--r-- 1 root root 2585872 Jan 19 2001 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Jan 19 2001 /usr/lib/libc.so
-rw-r--r-- 1 root root 851660 Jan 19 2001
/usr/lib/libc-client.a
lrwxrwxrwx 1 root root 19 Apr 15 16:51
/usr/lib/libc-client.so -> libc-client.so.2000
-rwxr-xr-x 1 root root 714876 Jan 19 2001
/usr/lib/libc-client.so.2000
Configure command: ./configure --prefix=/usr/local/mysql
--enable-assembler --with-extra-charsets=complex
--enable-thread-safe-client --with-mysqld-ldflags=-all-static
--with-client-ldflags=-all-static
--with-other-libc=/usr/local/mysql-glibc '--with-comment=Official MySQL
Binary' --prefix=/usr/local/mysql --with-extra-charset=complex
--enable-thread-safe-client --enable-local-infile 'CFLAGS=-Wimplicit
-Wreturn-type -Wid-clash-51 -Wswitch -Wtrigraphs -Wcomment -W
-Wchar-subscripts -Wformat -Wimplicit-function-dec -Wimplicit-int
-Wparentheses -Wsign-compare -Wwrite-strings -Wunused -mcpu=pentiumpro
-O3 -fno-omit-frame-pointer' 'CXXFLAGS=-Wimplicit -Wreturn-type
-Wid-clash-51 -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts
-Wformat -Wimplicit-function-dec -Wimplicit-int -Wparentheses
-Wsign-compare -Wwrite-strings -Woverloaded-virtual -Wextern-inline
-Wsign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor
-felide-constructors -fno-exceptions -fno-rtti -mcpu=pentiumpro -O3
-fno-omit-frame-pointer' CXX=gcc

************************************************************ ***************
Kyle J. Munn Email: kjmunn@EraGen.com
EraGen Biosciences Phone: 215.750.0483
http://www.EraGen.com Fax: 215.750.0483

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12552@lists.mysql.com
To unsubscribe, e-mail

MERGE tables row count bug

Hi!

>>>>> "Kyle" == Kyle J Munn writes:

Kyle> From: kjmunn@eragen.com
Kyle> To: mysql@lists.mysql.com
Kyle> Subject: MERGE tables not reporting correct row count

>> Description:
Kyle> This report contains a doc bug (ommission) and a software bug.

Kyle> We have a couple of tables with row counts approaching 2^32.
Kyle> Although this LIMIT CANNOT BE FOUND ANYWHERE IN THE
Kyle> DOCUMENTATION,
Kyle> we experimentally determined that MAX_ROWS cannot be >2^32-1.

This is true for a default 32-bit MySQL binary.
This can be changed by recompiling MySQL with the define -DBIG_TABLES
or by using MySQL on a 64 bit os.

(This should work but is not extensively tested)

Kyle> As a possible work around to the 32-bit limit, we tried using
Kyle> MERGE
Kyle> tables. We started by creating two identical MyISAM tables
Kyle> (Table1,Table2) and populating them with 2^32-1 rows each.
Kyle> (All rows were identical in order to make things easy.)
Kyle> We then created a MERGE table as the union of Table1 and Table2.

Kyle> Based on the description of the MERGE tables, we expected it to
Kyle> be
Kyle> the "sum of its parts". However, the ROW COUNT FOR THE MERGE
Kyle> TABLE
Kyle> DID NOT EQUAL THE SUM OF THE ROW COUNTS FOR THE 'MERGED' TABLES.

Sergei is looking into this and we will try to fix this ASAP.
You will get an email as soon as this is done.

Kyle> I would also appreciate knowing if and when I can expect
Kyle> MAX_ROWS
Kyle> to change from a 32-bit limit to a 64-bit limit.

The easyest way is to recompile MySQL with the above option:

CFLAGS=-DBIG_TABLES CXXFLAGS=-DBIG_TABLES ./configure

Regards,
Monty

--
For technical support contracts, goto https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Michael Widenius
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO
/_/ /_/\_, /___/\___\_\___/ Helsinki, Finland
<___/ www.mysql.com

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12558@lists.mysql.com
To unsubscribe, e-mail

Re: MERGE tables row count bug

Hi!

On Sep 18, Michael Widenius wrote:
>
> Kyle> Based on the description of the MERGE tables, we expected it to be
> Kyle> the "sum of its parts". However, the ROW COUNT FOR THE MERGE TABLE
> Kyle> DID NOT EQUAL THE SUM OF THE ROW COUNTS FOR THE 'MERGED' TABLES.
>
> Sergei is looking into this and we will try to fix this ASAP.
> You will get an email as soon as this is done.

The problem was that BIG_TABLES option applied to MERGE tables too, so
row counter was limited to 32 bits (the actual table wasn't, of course).

I fixed it so MERGE always uses 64 bits for row conter, independent
from BIG_TABLES mode.

> The easyest way is to recompile MySQL with the above option:
>
> CFLAGS=-DBIG_TABLES CXXFLAGS=-DBIG_TABLES ./configure

Regards,
Sergei

--
MySQL Development Team
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12607@lists.mysql.com
To unsubscribe, e-mail

Re: MERGE tables row count bug

Michael Widenius wrote:

> Kyle> This report contains a doc bug (ommission) and a software bug.
>
> Kyle> We have a couple of tables with row counts approaching 2^32.
> Kyle> Although this LIMIT CANNOT BE FOUND ANYWHERE IN THE
> Kyle> DOCUMENTATION,
> Kyle> we experimentally determined that MAX_ROWS cannot be >2^32-1.
>
> This is true for a default 32-bit MySQL binary.
> This can be changed by recompiling MySQL with the define -DBIG_TABLES
> or by using MySQL on a 64 bit os.
>
> (This should work but is not extensively tested)
>
>
> Kyle> I would also appreciate knowing if and when I can expect
> Kyle> MAX_ROWS
> Kyle> to change from a 32-bit limit to a 64-bit limit.
>
> The easyest way is to recompile MySQL with the above option:
>
> CFLAGS=-DBIG_TABLES CXXFLAGS=-DBIG_TABLES ./configure
>
> Regards,
> Monty
>
>


I recompiled and installed mysql-3.23.56 using the parameters specified above
but am still unable to create a table with more than 2^32-1 rows.

This is not very well documented so any help and/or guidance would be greatly
appreciated.

For reference:

uname -a
Linux cacofonix 2.4.18-4GB-SMP #22 SMP Mon Jan 20 11:39:30 EST 2003 i686 unknown

mysql> CREATE TABLE tmpTestMaxRows(Id int NOT NULL default 0, Id2 INT NOT NULL
default 0) TYPE=MyISAM PACK_KEYS=1 MAX_ROWS=18446744073709551615 AVG_ROW_LENGTH=13;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW TABLE STATUS LIKE 'tmpTestMaxRows';
+----------------+--------+------------+------+------------- ---+-------------+
-----------------+--------------+-----------+--------------- -+----------------
-----+---------------------+------------+------------------- ------------------
--------------+---------+
| Name | Type | Row_format | Rows | Avg_row_length | Data_length |
Max_data_length | Index_length | Data_free | Auto_increment | Create_time
| Update_time | Check_time | Create_options
| Comment |
+----------------+--------+------------+------+------------- ---+-------------+
-----------------+--------------+-----------+--------------- -+----------------
-----+---------------------+------------+------------------- ------------------
--------------+---------+
| tmpTestMaxRows | MyISAM | Fixed | 0 | 0 | 0 |
38654705663 | 1024 | 0 | NULL | 2003-03-28
11:46:38 | 2003-03-28 11:46:38 | NULL | max_rows=4294967295
avg_row_length=13 pack_keys=1 | |
+----------------+--------+------------+------+------------- ---+-------------+
-----------------+--------------+-----------+--------------- -+----------------
-----+---------------------+------------+------------------- ------------------
--------------+---------+


Kyle

************************************************************ ***************
Kyle J. Munn Email: kjmunn@EraGen.com
EraGen Biosciences Phone: 215.750.0483
http://www.EraGen.com
Fax: 215.750.0483


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: MERGE tables row count bug

Kyle J Munn writes:
>
> >
>
>
> I recompiled and installed mysql-3.23.56 using the parameters specified above
> but am still unable to create a table with more than 2^32-1 rows.
>
> This is not very well documented so any help and/or guidance would be greatly
> appreciated.
>
> For reference:
>
> uname -a
> Linux cacofonix 2.4.18-4GB-SMP #22 SMP Mon Jan 20 11:39:30 EST 2003 i686 unknown
>
> mysql> CREATE TABLE tmpTestMaxRows(Id int NOT NULL default 0, Id2 INT NOT NULL
> default 0) TYPE=MyISAM PACK_KEYS=1 MAX_ROWS=18446744073709551615 AVG_ROW_LENGTH=13;
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> SHOW TABLE STATUS LIKE 'tmpTestMaxRows';
> +----------------+--------+------------+------+------------- ---+-------------+
> -----------------+--------------+-----------+--------------- -+----------------
> -----+---------------------+------------+------------------- ------------------
> --------------+---------+
> | Name | Type | Row_format | Rows | Avg_row_length | Data_length |
> Max_data_length | Index_length | Data_free | Auto_increment | Create_time
> | Update_time | Check_time | Create_options
> | Comment |
> +----------------+--------+------------+------+------------- ---+-------------+
> -----------------+--------------+-----------+--------------- -+----------------
> -----+---------------------+------------+------------------- ------------------
> --------------+---------+
> | tmpTestMaxRows | MyISAM | Fixed | 0 | 0 | 0 |
> 38654705663 | 1024 | 0 | NULL | 2003-03-28
> 11:46:38 | 2003-03-28 11:46:38 | NULL | max_rows=4294967295
> avg_row_length=13 pack_keys=1 | |
> +----------------+--------+------------+------+------------- ---+-------------+
> -----------------+--------------+-----------+--------------- -+----------------
> -----+---------------------+------------+------------------- ------------------
> --------------+---------+
>
>
> Kyle
>
> ************************************************************ ***************
> Kyle J. Munn Email: kjmunn@EraGen.com
> EraGen Biosciences Phone: 215.750.0483
> http://www.EraGen.com
> Fax: 215.750.0483

Hi!

PLease check whether you have this defined :

#define SIZEOF_OFF_T 8


Also try using larger AVG_ROW_LENGTH.

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com



--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: MERGE tables row count bug

Sinisa Milivojevic wrote:

> Kyle J Munn writes:
>
>>
>>I recompiled and installed mysql-3.23.56 using the parameters specified above
>>but am still unable to create a table with more than 2^32-1 rows.
>>
>>This is not very well documented so any help and/or guidance would be greatly
>>appreciated.
>>
>>For reference:
>>
>>uname -a
>>Linux cacofonix 2.4.18-4GB-SMP #22 SMP Mon Jan 20 11:39:30 EST 2003 i686 unknown
>>
>>mysql> CREATE TABLE tmpTestMaxRows(Id int NOT NULL default 0, Id2 INT NOT NULL
>>default 0) TYPE=MyISAM PACK_KEYS=1 MAX_ROWS=18446744073709551615 AVG_ROW_LENGTH=13;
>>Query OK, 0 rows affected (0.00 sec)
>>
>>mysql> SHOW TABLE STATUS LIKE 'tmpTestMaxRows';
>>+----------------+--------+------------+------+----------- -----+-------------+
>>-----------------+--------------+-----------+------------- ---+----------------
>>-----+---------------------+------------+----------------- --------------------
>>--------------+---------+
>>| Name | Type | Row_format | Rows | Avg_row_length | Data_length |
>>Max_data_length | Index_length | Data_free | Auto_increment | Create_time
>> | Update_time | Check_time | Create_options
>> | Comment |
>>+----------------+--------+------------+------+----------- -----+-------------+
>>-----------------+--------------+-----------+------------- ---+----------------
>>-----+---------------------+------------+----------------- --------------------
>>--------------+---------+
>>| tmpTestMaxRows | MyISAM | Fixed | 0 | 0 | 0 |
>> 38654705663 | 1024 | 0 | NULL | 2003-03-28
>>11:46:38 | 2003-03-28 11:46:38 | NULL | max_rows=4294967295
>>avg_row_length=13 pack_keys=1 | |
>>+----------------+--------+------------+------+----------- -----+-------------+
>>-----------------+--------------+-----------+------------- ---+----------------
>>-----+---------------------+------------+----------------- --------------------
>>--------------+---------+
>>
>>
>>Kyle
>>
>>********************************************************** *****************
>>Kyle J. Munn Email: kjmunn@EraGen.com
>>EraGen Biosciences Phone: 215.750.0483
>>http://www.EraGen.com
>> Fax: 215.750.0483
>>
>
> Hi!
>
> PLease check whether you have this defined :
>
> #define SIZEOF_OFF_T 8
>
>
> Also try using larger AVG_ROW_LENGTH.
>


In the source code for mysql-3.23.56, I see SIZEOF_OFF_T defined in several
locations. From your comment, I'm not certain whether the assigned value should
be '8' or something else.

../config.log:#define SIZEOF_OFF_T 8
../config.h:#define SIZEOF_OFF_T 8
../include/my_global.h:#define SYSTEM_SIZEOF_OFF_T 8
../include/my_config.h:#define SIZEOF_OFF_T 8
../include/global.h:#define SYSTEM_SIZEOF_OFF_T 8

Can you please clarify your statement?


In addition, I'm not certain how changing the AVG_ROW_LENGTH will affect the
maximum number of rows. My investigations indicate that MAX_ROWS is limited by
2^32 no matter what I set AVG_ROW_LENGTH to.

Kyle

************************************************************ ***************
Kyle J. Munn Email: kjmunn@EraGen.com
EraGen Biosciences Phone: 215.750.0483
http://www.EraGen.com
Fax: 215.750.0483


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: MERGE tables row count bug

Kyle J Munn writes:
>
>
> In the source code for mysql-3.23.56, I see SIZEOF_OFF_T defined in several
> locations. From your comment, I'm not certain whether the assigned value should
> be '8' or something else.
>
> ./config.log:#define SIZEOF_OFF_T 8
> ./config.h:#define SIZEOF_OFF_T 8
> ./include/my_global.h:#define SYSTEM_SIZEOF_OFF_T 8
> ./include/my_config.h:#define SIZEOF_OFF_T 8
> ./include/global.h:#define SYSTEM_SIZEOF_OFF_T 8
>
> Can you please clarify your statement?
>
>
> In addition, I'm not certain how changing the AVG_ROW_LENGTH will affect the
> maximum number of rows. My investigations indicate that MAX_ROWS is limited by
> 2^32 no matter what I set AVG_ROW_LENGTH to.
>
> Kyle
>

Yes, it should be 8.

I failed to mention to you that our frm format currently does not
allow more then 4G to be stored as number of rows.

Try to set avg_row_length really high (regardless of the actual
length) so that offset is 8 bytes long (which can be seen with
myisamchk -dvv) and try to insert more then 4G rows. Should work.

I don't now about 3.23, but it should work with 4.0.

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com



--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org