The file system /export/data/dp20.a supports files larger than 2 GB. For example, the targetTsObj files are larger:
bash-2.04$ ls -lh targetTsObj*
-rw-rw---- 1 mysql mysql 134G Oct 20 05:37 targetTsObj.MYD
-rw-rw---- 1 mysql mysql 5.0k Oct 21 11:38 targetTsObj.MYI
-rw-rw---- 1 mysql mysql 32k Oct 18 21:05 targetTsObj.frm
And, there is still room on the disk that I *think* I am writing the index to.
bash-2.04$ df -h .
Filesystem Size Used Avail Use% Mounted on
/export/data/dp20.a 1.0T 302G 761G 29% /data/dp20.a
bash-2.04$
I have pointed tmpdir to this file system as well, and smaller databases have successfully build indices since these changes.
==> So, is there a chance that the index file is being built somewhere else, and it runs out of space? Any clues to help me track this down?
==> Should I be using innodb tables instead of myisam tables for this application?
Thanks again.
Jocelyn Fournier wrote:
According to show variables, your tmp dir is /export/data/dp20.a/tmp/.
Are you sure you have a file system which allow to create files greater than
2 GB for exemple ?
---- Original Message -----
From: <[EMAIL PROTECTED]>
To: "Jocelyn Fournier" <[EMAIL PROTECTED]>
Sent: Tuesday, October 22, 2002 1:33 AM
Subject: Re: Error 1034: 136 when fixing table from Create Index on long
table
Well, I have datadir, bdb_tmpdir, and tmpdir all pointing to
/export/data/dp20.a, which is 28% full. It is over one tera-byte.
I am worried that it is trying to write a temporary file, or an index
file, to some other location.
How can I see where the index file is written, to be sure?
Thanks!
----- Original Message -----
From: Jocelyn Fournier <[EMAIL PROTECTED]>
Date: Monday, October 21, 2002 5:37 pm
Subject: Re: Error 1034: 136 when fixing table from Create Index on long
table
Hi,
[root@forum] /usr/local/mysql/var> perror 136
Error code 136: Unknown error 136
136 = No more room in index file
Are you sure your file system can handle the size of your index
file ?
Regards,
Jocelyn
----- Original Message -----
From: "Chris Stoughton" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, October 22, 2002 12:26 AM
Subject: Error 1034: 136 when fixing table from Create Index on
long table
I am running 3.23.52-Max under Linux.roomw and
I now have a table with 54M rows:
mysql> select count(*) from targetTsObj;
+----------+
| count(*) |
+----------+
| 54549046 |
+----------+
1 row in set (0.05 sec)
Creating an index on this takes 1 hour 10 minutes, with this error:
mysql> create index targetTsObjobjId on targetTsObj (objId);
ERROR 1034: 136 when fixing table
The "describe" command shows that no index has been built.
Previously, it had trouble building this same index, and complained
about not being able to open a file in /tmp
I suspect that /tmp was not large enough, so we changed the tmpdir
variable in my.cnf to point to a file system with *plenty* of
restarted the server.0
myisamchk seems to have no complaints about this table:
bash-2.04$ myisamchk targetTsObj
Checking MyISAM file: targetTsObj
Data records: 54549046 Deleted blocks: 0
- check file-size
- check key delete-chain
- check record delete-chain
- check index reference
bash-2.04$ myisamchk -d targetTsObj
MyISAM file: targetTsObj
Record format: Fixed length
Character set: latin1 (8)
Data records: 54549046 Deleted blocks:
Recordlength: 2643or what
table description:
Key Start Len Index Type
bash-2.04$
Please let me know what this error means, how to get around it,
additional information you need. Thanks!+---------------------------------+---------------------------------
Here is the output of mysqladmin variable
--------
--------------------------------------------------------------------
--------
--------------------------------------------------------------------
--------
--------+
| Variable_name |+---------------------------------+---------------------------------
Value
|
--------
--------------------------------------------------------------------
--------
--------------------------------------------------------------------
--------
--------+
| back_log |gb2312 gbk
50
|
| basedir |
/
|
| bdb_cache_size |
8388600
|
| bdb_log_buffer_size |
262144
|
| bdb_home |
/export/data/dp20.a/data/mysql/
|
| bdb_max_lock |
10000
|
| bdb_logdir
|
|
| bdb_shared_data |
OFF
|
| bdb_tmpdir |
/export/data/dp20.a/tmp/
|
| bdb_version | Sleepycat Software: Berkeley DB
3.2.9a: (August 14,
2002)
|
| binlog_cache_size |
32768
|
| character_set |
latin1
|
| character_sets | latin1 big5 czech euc_kr
sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7cp1251> danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr
greek> win1250 croat cp1257 latin5 |
| concurrent_insert |+---------------------------------+---------------------------------
ON
|
| connect_timeout |
5
|
| datadir |
/export/data/dp20.a/data/mysql/
|
| delay_key_write |
ON
|
| delayed_insert_limit |
100
|
| delayed_insert_timeout |
300
|
| delayed_queue_size |
1000
|
| flush |
OFF
|
| flush_time |
0
|
| have_bdb |
YES
|
| have_gemini |
NO
|
| have_innodb |
DISABLED
|
| have_isam |
YES
|
| have_raid |
NO
|
| have_openssl |
NO
|
| init_file
|
|
| innodb_additional_mem_pool_size |
1048576
|
| innodb_buffer_pool_size |
8388608
|
| innodb_data_file_path
|
|
| innodb_data_home_dir
|
|
| innodb_file_io_threads |
4
|
| innodb_force_recovery |
0
|
| innodb_thread_concurrency |
8
|
| innodb_flush_log_at_trx_commit |
16777216
|
| innodb_fast_shutdown |
ON
|
| innodb_flush_method
|
|
| innodb_lock_wait_timeout |
50
|
| innodb_log_arch_dir
|
|
| innodb_log_archive |
OFF
|
| innodb_log_buffer_size |
1048576
|
| innodb_log_file_size |
5242880
|
| innodb_log_files_in_group |
2
|
| innodb_log_group_home_dir
|
|
| innodb_mirrored_log_groups |
1
|
| interactive_timeout |
28800
|
| join_buffer_size |
131072
|
| key_buffer_size |
536866816
|
| language |
/usr/share/mysql/english/
|
| large_files_support |
ON
|
| locked_in_memory |
OFF
|
| log |
OFF
|
| log_update |
OFF
|
| log_bin |
OFF
|
| log_slave_updates |
OFF
|
| log_long_queries |
OFF
|
| long_query_time |
10
|
| low_priority_updates |
OFF
|
| lower_case_table_names |
0
|
| max_allowed_packet |
1048576
|
| max_binlog_cache_size |
4294967295
|
| max_binlog_size |
1073741824
|
| max_connections |
100
|
| max_connect_errors |
10
|
| max_delayed_threads |
20
|
| max_heap_table_size |
16777216
|
| max_join_size |
4294967295
|
| max_sort_length |
1024
|
| max_user_connections |
0
|
| max_tmp_tables |
32
|
| max_write_lock_count |
4294967295
|
| myisam_max_extra_sort_file_size |
256
|
| myisam_max_sort_file_size |
2047
|
| myisam_recover_options |
0
|
| myisam_sort_buffer_size |
8388608
|
| net_buffer_length |
16384
|
| net_read_timeout |
30
|
| net_retry_count |
10
|
| net_write_timeout |
60
|
| open_files_limit |
0
|
| pid_file |
/var/run/mysqld/mysqld.pid
|
| port |
3306
|
| protocol_version |
10
|
| record_buffer |
131072
|
| record_rnd_buffer |
131072
|
| query_buffer_size |
0
|
| safe_show_database |
OFF
|
| server_id |
0
|
| slave_net_timeout |
3600
|
| skip_locking |
ON
|
| skip_networking |
OFF
|
| skip_show_database |
OFF
|
| slow_launch_time |
2
|
| socket |
/var/lib/mysql/mysql.sock
|
| sort_buffer |
2097144
|
| sql_mode |
0
|
| table_cache |
512
|
| table_type |
MYISAM
|
| thread_cache_size |
0
|
| thread_stack |
65536
|
| transaction_isolation |
READ-COMMITTED
|
| timezone |
CDT
|
| tmp_table_size |
33554432
|
| tmpdir |
/export/data/dp20.a/tmp/
|
| version |
3.23.52-Max
|
| wait_timeout |
28800
|
--------
--------------------------------------------------------------------
--------
--------------------------------------------------------------------
--------
--------+
---
------------------------------------------------------------------
Before posting, please check:<[EMAIL PROTECTED]>
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
Trouble unsubscribing? Try:http://lists.mysql.com/php/unsubscribe.php>
--------------------------------------------------------------------- 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 <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
