You may be running into file system file size "limits". You would need to make sure the file system you are using is set to handle
files larger than 4GB, in addition, you need to check that the account mysqld us running under is allowed to create files larger
than 4GB. Just because the OS and file system can handle and access large memory/file configurations, it doesn't mean the user
accounts are permitted to.
I'm guessing you are doing a sort on a large dataset and the OS is stopping you once the sort file reaching the file size limit for
the user account.
----- Original Message -----
From: "Christoph Klünter" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Friday, May 18, 2007 6:15 AM
Subject: Broken Tables, was:Memory Problems
On Tuesday 15 May 2007 14:36:45 Mathieu Bruneau wrote:
Hi, yeah, apparenlty you're running into the 32 bits memory liimt. Note
thta some memory is allocated for the OS so you don't even have the full
4GB of ram you can technically adressesed.
The 64 bits os would increase this limit to 64gb++ (on 64 bits hardware)
Good luck!
Ok, changed OS to 64bit. But didn't have any luck at all :-(
Upgrade was fine, used a dump to import all the data, and when everything was
running, I got following error:
May 16 22:47:09 sql mysqld[15259]: 070516 22:47:09 [ERROR] /usr/sbin/mysqld:
Got error 134 from storage engine
May 16 22:47:09 sql mysqld[15259]: 070516 22:47:09 [ERROR] /usr/sbin/mysqld:
Sort aborted
So I stopped the database, did a myisamchk and everythind was fine again. For
about two minutes. Then these
errors occured again. First error 134 (Record was already deleted (or record
file crashed)) and after a while
the tables could not be accessed anymore:
May 16 23:12:03 sql mysqld[15259]: 070516 23:12:03 [ERROR] /usr/sbin/mysqld:
Got error 127 from storage engine
May 16 23:12:03 sql mysqld[15259]: 070516 23:12:03 [ERROR] /usr/sbin/mysqld:
Sort aborted
Repairing the tables worked. But it would take just some minutes until the
tables got corrupted again.
I tried mysql-5.0 from debian etch (5.0.32) ,4.1 from debian sarge (4.1.11)
and 5.0 from debian-testing (5.0.38).
All the same. Any hints anybody ?
Cheers,
Christoph
mysql> show variables;
+---------------------------------+-----------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------+
| back_log | 50 |
| basedir | /usr/ |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
| concurrent_insert | ON |
| connect_timeout | 5 |
| datadir | /var/lib/mysql/ |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| expire_logs_days | 0 |
| flush | OFF |
| flush_time | 0 |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| group_concat_max_len | 1024 |
| have_archive | YES |
| have_bdb | NO |
| have_blackhole_engine | NO |
| have_compress | YES |
| have_crypt | YES |
| have_csv | YES |
| have_example_engine | NO |
| have_geometry | YES |
| have_innodb | YES |
| have_isam | YES |
| have_ndbcluster | DISABLED |
| have_openssl | NO |
| have_query_cache | YES |
| have_raid | YES |
| have_rtree_keys | YES |
| have_symlink | YES |
| init_connect | |
| init_file | |
| init_slave | |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 209715200 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_fast_shutdown | ON |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| 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_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| interactive_timeout | 28800 |
| join_buffer_size | 131072 |
| key_buffer_size | 4294963200 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/share/mysql/english/ |
| large_files_support | ON |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | OFF |
| log_error | |
| log_slave_updates | OFF |
| log_slow_queries | ON |
| log_update | OFF |
| log_warnings | 1 |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 33553408 |
| max_binlog_cache_size | 18446744073709551615 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 10 |
| max_connections | 2000 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 16777216 |
| max_insert_delayed_threads | 20 |
| max_join_size | 18446744073709551615 |
| max_length_for_sort_data | 1024 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 18446744073709551615 |
| max_sort_length | 1024 |
| max_tmp_tables | 200 |
| max_user_connections | 0 |
| max_write_lock_count | 18446744073709551615 |
| myisam_data_pointer_size | 4 |
| myisam_max_extra_sort_file_size | 2147483648 |
| myisam_max_sort_file_size | 9223372036854775807 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 134217728 |
| ndb_autoincrement_prefetch_sz | 32 |
| ndb_force_send | ON |
| ndb_use_exact_count | ON |
| ndb_use_transactions | ON |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | ON |
| open_files_limit | 10010 |
| pid_file | /var/run/mysqld/mysqld.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 67108864 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 8384512 |
| read_only | OFF |
| read_rnd_buffer_size | 16773120 |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| server_id | 0 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_net_timeout | 3600 |
| slave_transaction_retries | 0 |
| slow_launch_time | 2 |
| socket | /var/run/mysqld/mysqld.sock |
| sort_buffer_size | 536870904 |
| sql_mode | |
| storage_engine | MyISAM |
| sql_notes | ON |
| sql_warnings | ON |
| sync_binlog | 0 |
| sync_replication | 0 |
| sync_replication_slave_id | 0 |
| sync_replication_timeout | 0 |
| sync_frm | ON |
| system_time_zone | CEST |
| table_cache | 2048 |
| table_type | MyISAM |
| thread_cache_size | 150 |
| thread_stack | 131072 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| tmp_table_size | 33554432 |
| tmpdir | /tmp |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| version | 4.1.11-Debian_4sarge7-log |
| version_comment | Source distribution |
| version_compile_machine | x86_64 |
| version_compile_os | pc-linux-gnu |
| wait_timeout | 28800 |
+---------------------------------+-----------------------------+
188 rows in set (0.00 sec)
--
NMMN - New Media Markets & Networks GmbH
Geschäftsführung: Kfm. Michael Schütt
Finanzamt HH-Altona UStID DE 812 699 852 HRB 71102 Hamburg
HypoVereinsbank - BLZ 200 300 00 - Konto-Nr. 156 29 82
http://www.nmmn.com Tel.: +49 40 284 118 -0
Langbehnstrasse 6 Technische Hotline -700
22761 Hamburg Fax: -999
Rufen Sie uns kostenlos an: http://www.nmmn.com/call/technik
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
