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
|
+-------------------------+-------------------------------------------------
----------------------------------------------------------------------------
---------------------------------------------------------------------+
---------------------------------------------------------------------
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
