Hi, I am trying version 4 with myIsam tables. (I am almost ready to move to some other hopefully safer type ). The problem is I get table corruption.
I have a large database(21000 tables, but the whole /var/lib/mysql/mydatabase directory is less than 800MB) database. I know this is strange, but there is a lot of work to be done per table and it is a waste to have to search every time I filled these tables in using some mysqldumps. Then I have a script to process them. It all goes well, until it gets a corrupted table. Corruption is, error 127 myisamchk -r claims to recover only 1/4 of the records. Even so, when I try to look at them ,the table remains corrupted(127) I go to /var/lib/mysql/mydatabase and erase this table (the .frm, .MYI and .MYD files. ) then I recreate the table (justthe definition) from file 1330.sql No complaints. Then I connect to the database and get the COUNT It says 23, which was the count I believe before the corruption EVEN THOUGH 1330.sql has NO data, just the table definitions. Then I try to SELECT a field and it says error 127. Could this be related to my large # of tables? Are there any settings say in my.cnf to change? ............................. myuser@quality5:~/BACKUPN > mysql -u myuser -p CDR < 1330.sql Enter password: myuser@quality5:~/BACKUPN > mysql -u myuser -pxxxxxx CDR Welcome to the MySQL monitor. Commands end with ; or \\g. Your MySQL connection id is 479 to server version: 4.0.0-alpha-log Type 'help;' or '\\h' for help. Type '\\c' to clear the buffer. mysql> SELECT COUNT(*) FROM TABLE_1330_1; +----------+ | COUNT(*) | +----------+ | 23 | +----------+ 1 row in set (0.01 sec) mysql> SELECT file FROM TABLE_1330_1; ERROR 1030: Got error 127 from table handler mysql> Here is my my.cnf (system is: 1 PIII x1000MHz 1 37GB SCSI HD 1GB RAM AHA29160N SCSI controller-does this matter? /var/log/messages has nothing relevant # Example mysql config file for very large systems. # # This is for large system with memory of 1G-2G where the system runs mainly # MySQL. # # You can copy this file to # /etc/mf.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options (in this # installation this directory is /var/lib/mysql) or # ~/.my.cnf to set user-specific options. # # One can in this file use all long options that the program supports. # If you want to know which options a program support, run the program # with --help option. # The following options will be passed to all MySQL clients [client] #password = your_password port = 3306 socket = /var/lib/mysql/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock skip-locking set-variable = key_buffer=384M set-variable = max_allowed_packet=1M set-variable = table_cache=512 set-variable = sort_buffer=2M set-variable = record_buffer=2M set-variable = thread_cache=8 set-variable = thread_concurrency=2 # Try number of CPU's*2 set-variable = myisam_sort_buffer_size=64M log-bin server-id = 1 # Uncomment the following if you are using BDB tables #set-variable = bdb_cache_size=384M #set-variable = bdb_max_lock=100000 # Point the following paths to different dedicated disks #tmpdir = /tmp/ #log-update = /path-to-dedicated-directory/hostname [mysqldump] quick set-variable = max_allowed_packet=256M [mysql] no-auto-rehash #safe-updates # Remove the comment character if you are not familiar with SQL [isamchk] set-variable = key_buffer=256M set-variable = sort_buffer=256M set-variable = read_buffer=2M set-variable = write_buffer=2M [myisamchk] set-variable = key_buffer=256M set-variable = sort_buffer=256M set-variable = read_buffer=2M set-variable = write_buffer=2M [mysqlhotcopy] interactive-timeout Any ideas or any suggestions?? P.S. If I change to InnoDB or something will these error 127 messages disappear? Thanks, S.Alexiou --------------------------------------------------------------------- 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
