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.
Subject
Views
Written By
Posted
9315
April 04, 2005 04:30PM
5513
May 06, 2005 03:15AM
5466
May 13, 2005 04:23AM
Re: what's the limit on number of tables in MERGE table?
5250
November 09, 2005 01:19PM
4826
November 10, 2005 05:17AM
4433
November 10, 2005 08:24AM
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.