Jocelyn asked whether the file system supports files over 2 GB, and
whether there was space left on disk.
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: <stoughto@stripped>
>To: "Jocelyn Fournier" <joc@stripped>
>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 <joc@stripped>
>>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" <stoughto@stripped>
>>>To: <mysql@stripped>
>>>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.
>>>>
>>>>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
>>>>
>>>>
>>>roomw and
>>>
>>>
>>>>restarted the server.
>>>>
>>>>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:
>>>>
>>>>
>>> 0
>>>
>>>
>>>>Recordlength: 2643
>>>>
>>>>table description:
>>>>Key Start Len Index Type
>>>>bash-2.04$
>>>>
>>>>Please let me know what this error means, how to get around it,
>>>>
>>>>
>>>or what
>>>
>>>
>>>>additional information you need. Thanks!
>>>>
>>>>Here is the output of mysqladmin variable
>>>>
>>>>
>>>>
>>>+---------------------------------+---------------------------------
>>>--------
>>>--------------------------------------------------------------------
>>>--------
>>>--------------------------------------------------------------------
>>>--------
>>>--------+
>>>
>>>
>>>>| Variable_name |
>>>>Value
>>>>|
>>>>
>>>>
>>>>
>>>+---------------------------------+---------------------------------
>>>--------
>>>--------------------------------------------------------------------
>>>--------
>>>--------------------------------------------------------------------
>>>--------
>>>--------+
>>>
>>>
>>>>| back_log |
>>>>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
>>>>
>>>>
>>>gb2312 gbk
>>>
>>>
>>>>sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7
>>>>
>>>>
>>>cp1251> 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:
>>>> http://www.mysql.com/manual.php (the manual)
>>>> http://lists.mysql.com/ (the list archive)
>>>>
>>>>To request this thread, e-mail <mysql-thread122847@stripped>
>>>>To unsubscribe, e-mail
>>>>
>>>>
>>><mysql-unsubscribe-joc=presence-pc.com@stripped>
>>>
>>>
>>>>Trouble unsubscribing? Try:
>>>>
>>>>
>>>http://lists.mysql.com/php/unsubscribe.php>
>>>
>>>
>>>
>>>
>>
>>
>
>
>
