Data corruption following a power failure.
Hi. I've noticed that when a server running MySQL crashes and comes back to life, the MyISAM tables typically need to be repaired to account for the fact that they were not flushed when the server went down. However, I've also noticed that when the database is recovered from a crash, the tables can be inconsistent with regards to the temporal relationship of the most recent updates performed on them. For example, it's possible that after a crash, UPDATE A, UPDATE B, and UPDATE C (which were performed sequentially before the crash) will not all be present in the repaired table. It may be that only updates B and C were stored to disk before the server went down. UPDATE A, which happened before the other updates, may nonetheless be missing. Is this the true behavior or does it just appear to work this way? And if so, is there any way to force MySQL to automatically save data to disk in a way that is chronologically correct? This would make it much easier for me to recover from an unexpected crash. Thanks.
Too many columns error when altering column type to ENUM?
I've seen a number of references to the Too many columns error, but I'm encountering something that appears to be different. Basically, I'm trying to change a single column in a table (that has 20 columns) from VARCHAR(255) to ENUM(...) where the enum list contains about 900 entries, each a string of about 50 characters or so. From what I understand the enum limit is about 65000, and the number of enum columns in a single table is about 255, but since I'm looking at values of 900 and ~5, respectively, I don't think I'm hitting these limits. Any ideas why I'm getting this error? I'm using version 4.0.13. Thanks.
Repeated table crashes (Errno 145). Why?
Hi, Recently I've been seeing the following kind of error message for several of the MyISAM tables in my database: General error: Can't open file: 'build_packages.MYI'. (errno: 145) Running a simple repair operation on the corrupted table will make MySQL happy again, even though the repair statement doesn't indicate that it found any corruption: mysql repair table build_packages; +++--+--+ | Table | Op | Msg_type | Msg_text | +++--+--+ | dvs.build_packages | repair | status | OK | +++--+--+ 1 row in set (0.13 sec) But unfortunately this doesn't solve the underlying problem. I'm seeing this kind error at least once a week in the system, though not always the same table. Also, the server .err file is not being updated with any useful info when these failures occur. The system is under fairly constant and heavy use. It typically has about 60 simultaneous (automated) clients at any given time, and a large percentage of the query operations are updates/inserts/deletes. There are about 15 active tables, some of which are about 200 megs. The only unusual characteristic of this setup might be the rather frequent use of replace into commands to synchronize pairs of tables. The database is running Windows 2003 server. Any hints on how I can work around this would be greatly appreciated. I don't know what kind of information will help in diagnosing this. Here's the output of show variables. If there is something else I can include please let me know. +-+- | Variable_name | Value +-+- | back_log| 50 | basedir | C:\mysql\ | bdb_cache_size | 8388572 | bdb_log_buffer_size | 32768 | bdb_home| C:\mysql\data\ | bdb_max_lock| 1 | bdb_logdir | | bdb_shared_data | OFF | bdb_tmpdir | C:\WINDOWS\TEMP\ | bdb_version | Sleepycat Software: Berkeley DB 3.2.9a: | binlog_cache_size | 32768 | bulk_insert_buffer_size | 8388608 | character_set | latin1 | concurrent_insert | ON | connect_timeout | 5 | convert_character_set | | datadir | C:\mysql\data\ | delay_key_write | ON | delayed_insert_limit| 100 | delayed_insert_timeout | 300 | delayed_queue_size | 1000 | flush | OFF | flush_time | 1800 | ft_boolean_syntax | + -()~*:| | ft_min_word_len | 4 | ft_max_word_len | 254 | ft_max_word_len_for_sort| 20 | ft_stopword_file| (built-in) | have_bdb| YES | have_crypt | NO | have_innodb | YES | have_isam | YES | have_raid | NO | have_symlink| YES | have_openssl| NO | have_query_cache| YES | init_file | | innodb_additional_mem_pool_size | 1048576 | innodb_buffer_pool_size | 8388608 | innodb_data_file_path | ibdata1:10M:autoextend | innodb_data_home_dir| | innodb_file_io_threads | 4 | innodb_force_recovery | 0 | innodb_thread_concurrency | 8 | innodb_flush_log_at_trx_commit | 0 | 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 | 8388572 | language| C:\mysql\share\english\ | large_files_support | ON | local_infile| ON | log | OFF | log_update | OFF | log_bin | ON | log_slave_updates | OFF | log_slow_queries| OFF | log_warnings| OFF | long_query_time | 10 | low_priority_updates| OFF | lower_case_table_names | ON | max_allowed_packet | 1048576 | max_binlog_cache_size | 4294967295 | max_binlog_size | 1073741824 | max_connections | 200 | max_connect_errors | 10 | max_delayed_threads
