These are some remarks on mysql(3.23.32) with regard to my experience with data crash and recovery. It is not meant to be negative in any sense and I am actually very thankful there is a database like Mysql around. On the other hand, if these experiences are not due to some mistake on my part and if my workarounds are reasonable, it might be worth including them in some documentation of faqs. First, it is fairly EASY to get table corruption . I am using a perl/DBI script to parse some data files and store them in Mysql tables. It looks like it is a little safer to close the Database handle before attempting say a mysqldump. To optimize queries I am storing the data in tables according to some attribute. For example if this were a grocery shop inventory, the tables would looks like: Table: Bananas item_no quantity price Table: Oranges item_no quantity price etc.. This is because I have a lot of data and I am mostly interested in issuing queries on each species separately. Needless to say, this creates a very large number of tables(more than ls or rm can handle at a time and of course also more than what myisamchk can repair in a shot) I have found that if DBI dies for some reason, then there is an almost certainity that at least some tables are corrupted. In most cases myisamchk is able to only recover part of the records (from 50 to 80-90%) For recovery,the perl script uses a system call to mysqldump every N files that are parsed. Unfortunately, this gives some problems too. Sometimes mysqldump does not complete and sometimes even though it completes, one cannot subsequently recover all data because mysql ... <backup.sql complains about duplicate primary keys. This appears to be traceable to the creation of files /var/lib/mysql/host-name-bin.xxx . These files appear to contain sql statements like what is produced by mysqldump Not only can these files get quite big, taking a lot of space in /var/lib/mysql(this is a mount point in a logical volume group) but I also found that removing the last one was essential for being able to get rid of the duplicate primary keys message. Removing these files(at least the last one) allowed the recreation of the database tables from the mysqldump obtained backups Another issue is the temporary files in TMPDIR; According to the manual one can control where these will go by editing TMPDIR in safe_mysqld; I have not found any such line in safe_mysqld I am also worried about a reported 8 hr limit(I need to run in continuous mode, i.e. get data continuously), but I have not yet experienced corruption that was definitely attributable to this. Any comments? 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
