Thanks but that's not v helpful. Actually that was my thread! The problem
there was that I didn't have a large-file enabled filesystem. The problem I
have now is being able to create another index on a table but getting an
errcode 27 despite no ulimits and a large enabled filesystem.
>
>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" <vervoom@stripped> 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?
>unsub=araheja@stripped
> >
> >
> >
> >
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>
_________________________________________________________________
Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo
