MySQL Forums
Forum List  »  Merge Storage Engine

Re: what's the limit on number of tables in MERGE table?
Posted by: JinNo Kim
Date: November 09, 2005 01:19PM

Under 4.0.2? it was 127 tables - the 128th would add to the spec, but would silently not be "counted"
in a show table status (or return results for the last table when queried, IIRC).

On intiial testing with 4.1.14 built from Gentoo ebuild on a hardened 2.6 system, the number is 475 -
the 476th table added yields:

./foo/bar_99.MYD' not found (Errcode: 24)

when a "show table status" is issued...


I ran these checks as root on a test box, with a fresh mysql-4.1.14 install (not normally used for DB
work - was field expedient). I played a little bit with table name length (ran one check with tables
named "bar_1_$COUNTER". - Result was the same - 476th table caused the error.


Can anybody confirm these numbers?

(script assumes root password for mysql = "pass", change as necessary or create another db user
and set that user/pass for testing).


~#mysql -ppass -e "drop database foo;"

~#mysql -ppass -e "create database foo;"

~#./tryit.sh

The script I used to check this
(*WARNING*: makes insecure use of /tmp files):

(NOTE: Completes successfully with 950 rows in merge spec if changed to "while [ $COUNTER -lt
476 ]" )


#!/bin/bash

MYSQL_DBDIR="/var/lib/mysql/foo"

export COUNTER=1
while [ $COUNTER -lt 477 ]
do

CREATE_SQL="create table bar_$COUNTER (
a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
message CHAR(20)
);"

INSERT_SQL="INSERT INTO bar_$COUNTER (message) VALUES ('Testing'),('$COUNTER');"

echo $CREATE_SQL > /tmp/tmp_create.sql
echo $INSERT_SQL > /tmp/tmp_insert.sql

mysql -ppass -D foo -h localhost -uroot < /tmp/tmp_create.sql
mysql -ppass -D foo -h localhost -uroot < /tmp/tmp_insert.sql

rm /tmp/tmp_create.sql
rm /tmp/tmp_insert.sql

export COUNTER=`expr $COUNTER + 1`
done

MERGE_SQL="create table merge_bar (
a INT NOT NULL AUTO_INCREMENT,
message CHAR(20), INDEX(a)
) TYPE=MERGE UNION=(bar_1,bar_2);"

echo $MERGE_SQL > /tmp/tmp_merge.sql

mysql -ppass -D foo -h localhost -uroot < /tmp/tmp_merge.sql

rm /tmp/tmp_merge.sql

cd $MYSQL_DBDIR

ls bar_*.MYI | awk -F '.' '{print $1}' > merge_bar.MRG

chown mysql: *
mysql -ppass -D foo -h localhost -u root -e 'flush tables;'

mysql -ppass -D foo -h localhost -u root -e 'show table status;'



Edited 1 time(s). Last edit at 11/09/2005 01:22PM by JinNo Kim.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: what's the limit on number of tables in MERGE table?
5250
November 09, 2005 01:19PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.