Error 1034: 136 when fixing table from Create Index on long table

[Chris Stoughton]

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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php