This should be a useful thread for you http://lists.mysql.com/mysql/167953
Suggests using ALTER TABLE .... MAX_ROWS=<a big num> On Fri, 23 Jul 2004 20:20:24 +0000, "J S" <[EMAIL PROTECTED]> wrote : > Hi, > > I'm trying to run the following SQL (on > mysql-max-4.0.20-ibm-aix4.3.3.0-powerpc): > > CREATE INDEX timeindex ON internet_usage (time); > CREATE INDEX urlindex ON internet_usage (urlid); > > but keep running into the following error: > > Error writing file '/proxydb/mysql/tmp/STeR39Er' (Errcode: 27) > > $ perror 27 > Error code 27: A file cannot be larger than the value set by ulimit. > > The create index gets as far as actually copying the 18GB internet_usage > table, and then does some processing (not sure what), then falls over with > the error 27. > > The message seems to be misleading because all the ulimit values are > unlimited. Also the filesystem is large-file enabled as shown below: > > $ pwd > /proxydb/mysql/data/proxy_logs > $ ls -l > total 58124344 > -rw-rw---- 1 mysql mysql 0 Jul 23 11:08 bulk_table.MYD > -rw-rw---- 1 mysql mysql 1024 Jul 23 11:08 bulk_table.MYI > -rw-rw---- 1 mysql mysql 8970 Jul 09 14:05 bulk_table.frm > -rw-rw---- 1 mysql mysql 18550068032 Jul 23 10:45 internet_usage.MYD > -rw-rw---- 1 mysql mysql 5150286848 Jul 23 11:08 internet_usage.MYI > -rw-rw---- 1 mysql mysql 8856 Jul 09 14:05 internet_usage.frm > -rw-rw---- 1 mysql mysql 380 Jul 09 14:08 url_actions.MYD > > File system name /proxydb > NEW mount point [/proxydb] > SIZE of file system (in 512-byte blocks) [131203072] > Mount GROUP [] > Mount AUTOMATICALLY at system restart? yes > + > PERMISSIONS read/write > + > Mount OPTIONS [] > + > Start Disk Accounting? no > + > Fragment Size (bytes) 4096 > Number of bytes per inode 4096 > Compression algorithm no > Large File Enabled true > Allocation Group Size (MBytes) 64 > > > I'm not sure what else could be wrong. I've tried creating another table > with the new keys and selecting * from internet_usage table but I still got > the same error there. > > > Here's some miscellaneous info which might be useful. > > mysql> show indexes from internet_usage; > +----------------+------------+----------+--------------+-------------+-- ---------+-------------+----------+--------+------+------------+---------+ > | Table | Non_unique | Key_name | Seq_in_index | Column_name | > Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | > +----------------+------------+----------+--------------+-------------+-- ---------+-------------+----------+--------+------+------------+---------+ > | internet_usage | 1 | uid | 1 | uid | A > | 23039 | NULL | NULL | | BTREE | | > +----------------+------------+----------+--------------+-------------+-- ---------+-------------+----------+--------+------+------------+---------+ > 1 row in set (0.07 sec) > > +----------------+--------+------------+-----------+----------------+---- ---------+-----------------+--------------+-----------+----------------+--- ------------------+---------------------+---------------------+------------ ---------+---------+ > | 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 | > +----------------+--------+------------+-----------+----------------+---- ---------+-----------------+--------------+-----------+----------------+--- ------------------+---------------------+---------------------+------------ ---------+---------+ > | bulk_table | MyISAM | Dynamic | 0 | 0 | > 0 | 4294967295 | 1024 | 0 | NULL | > 2004-07-09 14:05:56 | 2004-07-23 11:08:08 | NULL | > | | > | internet_usage | MyISAM | Fixed | 579689626 | 32 | > 18550068032 | 137438953471 | 5150286848 | 0 | NULL | > 2004-07-09 14:05:56 | 2004-07-23 10:45:04 | 2004-07-09 14:13:20 | > max_rows=4294967295 | | > | url_actions | MyISAM | Dynamic | 18 | 21 | > 380 | 4294967295 | 3072 | 0 | NULL | > 2004-07-09 14:05:56 | 2004-07-09 14:08:39 | NULL | > | | > | url_categories | MyISAM | Dynamic | 37 | 21 | > 780 | 4294967295 | 3072 | 0 | NULL | > 2004-07-09 14:05:56 | 2004-07-09 14:08:39 | NULL | > | | > | url_methods | MyISAM | Dynamic | 37 | 20 | > 740 | 4294967295 | 3072 | 0 | NULL | > 2004-07-09 14:05:56 | 2004-07-09 14:08:39 | NULL | > | | > | url_paths | MyISAM | Dynamic | 34840346 | 64 | > 2252492868 | 4294967295 | 1593329664 | 0 | 34840347 | > 2004-07-09 14:05:56 | 2004-07-23 09:48:49 | NULL | > | | > | url_queries | MyISAM | Dynamic | 97113 | 91 | > 8899356 | 4294967295 | 10305536 | 0 | 97114 | > 2004-07-09 14:05:56 | 2004-07-23 09:55:03 | NULL | > | | > | url_schemes | MyISAM | Dynamic | 6 | 20 | > 120 | 4294967295 | 3072 | 0 | NULL | > 2004-07-09 14:05:56 | 2004-07-09 14:08:39 | NULL | > | | > | url_servers | MyISAM | Dynamic | 576728 | 30 | > 17727392 | 4294967295 | 19723264 | 0 | 576729 | > 2004-07-09 14:05:56 | 2004-07-23 09:38:23 | NULL | > | | > | url_viruses | MyISAM | Dynamic | 1 | 20 | > 20 | 4294967295 | 3072 | 0 | NULL | > 2004-07-09 14:05:56 | 2004-07-09 14:08:39 | NULL | > | | > | url_visit | MyISAM | Fixed | 38805636 | 20 | > 776112720 | 85899345919 | 1378707456 | 0 | 38805637 | > 2004-07-09 14:05:56 | 2004-07-23 10:23:19 | NULL | > | | > | user_agents | MyISAM | Dynamic | 1487 | 72 | > 108540 | 4294967295 | 80896 | 0 | 1488 | > 2004-07-09 14:05:56 | 2004-07-23 09:56:42 | NULL | > | | > | user_table | MyISAM | Dynamic | 23041 | 20 | > 460820 | 4294967295 | 476160 | 0 | 23042 | > 2004-07-09 14:05:56 | 2004-07-23 09:57:13 | NULL | > | | > +----------------+--------+------------+-----------+----------------+---- ---------+-----------------+--------------+-----------+----------------+--- ------------------+---------------------+---------------------+------------ ---------+---------+ > > > $ ps -ef | grep mysql > mysql 29226 67682 0 Jul 18 - 2757:37 /proxydb/mysql/bin/mysqld > --defaults-extra-file=/proxydb/mysql/data/my.cnf -- basedir=/proxydb/mysql > --datadir=/proxydb/mysql/data --user=mysql > --pid-file=/proxydb/mysql/data/rsl156.pid --port=3306 > --socket=/tmp/mysql.sock --tmpdir=/proxydb/mysql/tmp > root 67682 1 0 Jul 18 - 0:00 sh > /proxydb/mysql/bin/mysqld_safe --datadir=/proxydb/mysql/data > --tmpdir=/proxydb/mysql/tmp --pid-file=/proxydb/mysql/data/rsl156.pid > > Can anyone help me out please? > > Thanks a lot, > > JS. > > _________________________________________________________________ > Stay in touch with absent friends - get MSN Messenger > http://www.msn.co.uk/messenger > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
