David J. Potter writes:
> Hello,
> Until recently I have spent most of my time using the major databases such
> as Oracle, DB2, Sybase etc. I am new to MySQL. At first I was excited
> about such a low cost alternative such as MySQL, and I have found Mysql to
> be a great database for very small databases, but for large databases it has
> been a nightmare due to constant table corruption that is happening many
> times on a daily basis. The corruption is so bad that sometimes even
> myisamchk -r won't fix the data and I have to use --safe-recover.
> Hopefully someone can give me some guidance as I really do not want to go
> back to using those proprietary and expensive database systems. I am not
> able to move to an Unix operating system because the DSL modem being used
> requires Windows and also for other reasons. My setup is very simple with
> only computer, the database resides on that computer, just one Mysql
> database server, and only one database with one table.
>
> Here are the specific details:
>
> An example of the error message I get: "[TCX][MyODBC]Incorrect key file for
> table: 'domain'. Try to repair it"
>
> This happens while inserting a large number rows at high speed being read
> from a text file. It crashes usually around the 1,000,000th inserted row
> into a table.
>
> Hardware: Atlas CS9000
> Operating system: Windows 98 Second Edition 4.10.2222A
> CPU: One Intel Pentium III/Coppermine Processor 800Mhz
> Motherboard: Shuttle AV11 VIA 693A Motherboard with PC133 & UDMA66 Support
> Memory 128MB PC-133 SDRAM
>
> Server version 3.23.32
> Protocol version 10
> Connection localhost via TCP/IP
> TCP port 3306
> Uptime: 19 hours 50 min 34 sec
> Threads: 2 Questions: 4551989 Slow queries: 0 Opens: 13 Flush tables: 1
> Open tables: 0 Queries per second avg: 63.723
>
> Here is the contents of the mysql.err error log:
>
> C:/MYSQL/bin/mysqld-opt.exe: ready for connections
> 010207 23:32:23 C:/MYSQL/bin/mysqld-opt.exe: Normal shutdown
> 010207 23:32:23 C:/MYSQL/bin/mysqld-opt.exe: Shutdown Complete
> C:/MYSQL/bin/mysqld-opt.exe: ready for connections
>
> mysqladmin status gives:
>
> Uptime: 71416 Threads: 2 Questions: 4551985 Slow queries: 0 Opens: 13
> Flush tables: 1 Open tables: 0 Queries per second avg: 63.739
>
> +--------------------------+----------+
> | Variable_name | Value |
> +--------------------------+----------+
> | Aborted_clients | 0 |
> | Aborted_connects | 0 |
> | Bytes_received | 0 |
> | Bytes_sent | 0 |
> | Connections | 8 |
> | Created_tmp_disk_tables | 0 |
> | Created_tmp_tables | 0 |
> | Created_tmp_files | 0 |
> | Delayed_insert_threads | 0 |
> | Delayed_writes | 0 |
> | Delayed_errors | 0 |
> | Flush_commands | 1 |
> | Handler_delete | 0 |
> | Handler_read_first | 1 |
> | Handler_read_key | 0 |
> | Handler_read_next | 0 |
> | Handler_read_prev | 0 |
> | Handler_read_rnd | 0 |
> | Handler_read_rnd_next | 8 |
> | Handler_update | 0 |
> | Handler_write | 4544830 |
> | Key_blocks_used | 7764 |
> | Key_read_requests | 38159880 |
> | Key_reads | 3032717 |
> | Key_write_requests | 9364345 |
> | Key_writes | 9364345 |
> | Max_used_connections | 1 |
> | Not_flushed_key_blocks | 0 |
> | Not_flushed_delayed_rows | 0 |
> | Open_tables | 0 |
> | Open_files | 0 |
> | Open_streams | 0 |
> | Opened_tables | 13 |
> | Questions | 4551966 |
> | Select_full_join | 0 |
> | Select_full_range_join | 0 |
> | Select_range | 0 |
> | Select_range_check | 0 |
> | Select_scan | 0 |
> | Slave_running | OFF |
> | Slave_open_temp_tables | 0 |
> | Slow_launch_threads | 0 |
> | Slow_queries | 0 |
> | Sort_merge_passes | 0 |
> | Sort_range | 0 |
> | Sort_rows | 0 |
> | Sort_scan | 0 |
> | Threads_cached | 0 |
> | Threads_created | 7 |
> | Threads_connected | 2 |
> | Threads_running | 1 |
> | Uptime | 71310 |
> +--------------------------+----------+
>
> +-------------------------+-------------------------------------------------
> ----------------------------------------------------------------------------
> ---------------------------------------------------------------------+
> | Variable_name | Value
> |
> +-------------------------+-------------------------------------------------
> ----------------------------------------------------------------------------
> ---------------------------------------------------------------------+
> | ansi_mode | OFF
> |
> | back_log | 50
> |
> | basedir | c:\mysql\
> |
> | 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 |
> | concurrent_insert | ON
> |
> | connect_timeout | 5
> |
> | 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
> |
> | have_bdb | NO
> |
> | have_gemini | NO
> |
> | have_innobase | NO
> |
> | have_isam | YES
> |
> | have_raid | NO
> |
> | have_ssl | NO
> |
> | init_file |
> |
> | interactive_timeout | 28800
>
> |
> | join_buffer_size | 131072
> |
> | key_buffer_size | 8388600
> |
> | language | c:\mysql\share\english\
> |
> | large_files_support | ON
> |
> | log | OFF
> |
> | log_update | OFF
> |
> | log_bin | OFF
> |
> | log_slave_updates | OFF
> |
> | long_query_time | 10
> |
> | low_priority_updates | OFF
> |
> | lower_case_table_names | 1
> |
> | max_allowed_packet | 1048576
> |
> | max_binlog_cache_size | 4294967295
> |
> | 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_tmp_tables | 32
> |
> | max_write_lock_count | 4294967295
> |
> | myisam_recover_options | OFF
> |
> | 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 | c:\mysql\data\.pid
> |
> | port | 3306
> |
> | protocol_version | 10
> |
> | record_buffer | 131072
> |
> | query_buffer_size | 0
> |
> | safe_show_database | OFF
> |
> | server_id | 0
> |
> | skip_locking | ON
> |
> | skip_networking | OFF
> |
> | skip_show_database | OFF
> |
> | slow_launch_time | 2
> |
> | socket | MySQL
> |
> | sort_buffer | 2097144
> |
> | table_cache | 64
> |
> | table_type | MYISAM
> |
> | thread_cache_size | 0
> |
> | thread_stack | 65536
> |
> | timezone | Pacific Standard Time
> |
> | tmp_table_size | 1048576
> |
> | tmpdir | c:\windows\temp\
> |
> | version | 3.23.32
> |
> | wait_timeout | 28800
> |
> +-------------------------+-------------------------------------------------
> ----------------------------------------------------------------------------
> ---------------------------------------------------------------------+
Hi!
Table corruption is quite rare these days.
>From the above output we can hardly deduce why should corruption
occur.
Especially puzzling is that both bytes_sent and received are both
zero.
If you can make a ZIP file containing a dump of the table, plus text
file and command that you used we might find out what is happening.
Regards,
Sinisa
____ __ _____ _____ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|____
^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^
/*/ \*\ Developers Team
---------------------------------------------------------------------
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
