RE: mysql dump problems, no data dumped
Hi Rick, ok sorry, heres a bit more detail, and I see some more useful stuff I didn't see before too (I still duno whats wrong but guess will be helpful to those more knowledgeable!). I believe its using InnoDB for the tables, previously I was seeing an error 24 which from digging around can be related to too many open files, so I then tried with single transaction mode and get the successful backup exit status, but in verbose mode I see that there are obvious issues. mysqldump -v --single-transaction --socket=/usr/AdventNet/ME/OpManager/mysql/mysql.socket OpManagerDB -- Connecting to localhost... -- MySQL dump 10.11 -- -- Host: localhostDatabase: OpManagerDB -- -- -- Server version 4.0.18-pro /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- Retrieving table structure for table ADGRAPHS... -- Skipping dump data for table 'ADGRAPHS', it has no fields -- Retrieving table structure for table ADMonitorRuleList... -- Skipping dump data for table 'ADMonitorRuleList', it has no fields -- Retrieving table structure for table ADServiceRuleList... -- Skipping dump data for table 'ADServiceRuleList', it has no fields -- Retrieving table structure for table ADServicesDefinition... -- Skipping dump data for table 'ADServicesDefinition', it has no fields -- Retrieving table structure for table ADWMIPolledData... -- Skipping dump data for table 'ADWMIPolledData', it has no fields . . -- Disconnecting from localhost... /*!40103 SET [EMAIL PROTECTED] */; /*!40101 SET [EMAIL PROTECTED] */; /*!40014 SET [EMAIL PROTECTED] */; /*!40014 SET [EMAIL PROTECTED] */; /*!40111 SET [EMAIL PROTECTED] */; -- Dump completed on 2008-10-30 19:06:25 Without single transaction I get this: mysqldump --socket=/usr/AdventNet/ME/OpManager/mysql/mysql.socket OpManagerDB -- MySQL dump 10.11 -- -- Host: localhostDatabase: OpManagerDB -- -- -- Server version 4.0.18-pro /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; mysqldump: Got error: 1017: Can't find file: './OpManagerDB/WANIntfTypes.frm' (errno: 24) when using LOCK TABLES This file mentioned does exist in the location where all the datafiles for the OpManagerDB DB are stored: file WANIntfTypes.frm WANIntfTypes.frm: MySQL table definition file Version 7 Thanks, Andy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ANN: SQL Maestro for MySQL 8.10 released
Hi! SQL Maestro Group announce the release of SQL Maestro for MySQL 8.10, a complete Windows GUI solution for MySQL server administration and database development. http://www.sqlmaestro.com/products/mysql/maestro/ New features: = 1. An ability of using the LOAD DATA INFILE command has been incorporated into the Import Data wizard. This feature can speed up the import process up to 10 times. 2. Get SQL Dump Wizard and Extract Database Wizard now can generate multi-row INSERT statements. 3. Starting with this version it is possible to use the lookup editor for a column, which is linked by a foreign key with a (single) column from another table. The editor displays the contents of parent table's columns within a dropdown window. 4. A possibility of displaying data in the master-detail form has been incorporated into the Table Editor (multiple detail pages can be displayed for a single master row). 5. Filtering, grouping, and sorting features become available for TEXT columns. 6. Database Designer settings: the Show hints option has been added. Also it is now possible to select the default graphic file format to export diagrams as images. 7. Some other new features. Full press release is available at: http://www.sqlmaestro.com/news/company/5554/ Background information: === SQL Maestro Group is engaged in developing complete database admin and management tools for MySQL, Oracle, MS SQL Server, DB2, PostgreSQL, SQL Anywhere, SQLite, Firebird and MaxDB providing the highest performance, scalability and reliability to meet the requirements of today's database applications. Thank you for your attention. Sincerely yours, SQL Maestro Group http://www.sqlmaestro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I don' t know difference between myisam_sort_buffer_size and myisam_max_sort_file_size
From dev.mysql.com: myisam_max_sort_file_size : The maximum size of the temporary file that MySQL is allowed to use while re-creating a MyISAM index (during REPAIR TABLE, ALTER TABLE, or LOAD DATA INFILE). If the file size would be larger than this value, the index is created using the key cache instead, which is slower. The value is given in bytes. The default value is 2GB. If MyISAM index files exceed this size and disk space is available, increasing the value may help performance. myisam_sort_buffer_size: The size of the buffer that is allocated when sorting MyISAM indexes during a REPAIR TABLE or when creating indexes with CREATE INDEX or ALTER TABLE. The maximum allowable setting for myisam_sort_buffer_size is 4GB. (Default is 8MB) Simply: one's a buffer, one's a limit on the temp file size used for sorting. Which you might need to increase depends entirely on which limitation you're running into... if the current index files for the relevant tables on disk exceed 2GB, you might want to increase myisam_max_sort_file_size. Otherwise, myisam_sort_buffer_size might be better. Jake On Fri, Oct 31, 2008 at 12:20 AM, Moon's Father [EMAIL PROTECTED] wrote: Because alter table and repair table are both affected by myisam_sort_buffer_size or myisam_max_sort_file_size, I'm in confusion then. Anybody can tell me which to be adjusted when I want to improve the performance of my index operation. Thanks. -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Basic information: MySQL Table crash?
hi, someone else had a similar question, but didn't get any useful reply here on the list - I'll give it another try. :-) I need some basic understanding of MySQL table crashes. Maybe you can help me, I think it's so basic, no one ever wrote about it. (Until now! ;)) The mysql manual just says 'if you have a crashed table you can repair it.' Well... 1. What IS a table crash? What file/relation/... gets corrupted? And how does table recovery basically work? 2. If you had a crashed table: Where would you start looking for the reason? And where would you look next? 3. What precautions can one take to avoid a table crash? Are there any symptoms that show up when a table crash is about to happen? (like: out of swap memory, disk full, too much system load, ...?) Any comments/hints/links appreciated, thanks in advance, regards, Roman -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Basic information: MySQL Table crash?
Roman Eberle wrote: hi, someone else had a similar question, but didn't get any useful reply here on the list - I'll give it another try. :-) I need some basic understanding of MySQL table crashes. Maybe you can help me, I think it's so basic, no one ever wrote about it. (Until now! ;)) The mysql manual just says 'if you have a crashed table you can repair it.' Well... 1. What IS a table crash? What file/relation/... gets corrupted? And how does table recovery basically work? 2. If you had a crashed table: Where would you start looking for the reason? And where would you look next? 3. What precautions can one take to avoid a table crash? Are there any symptoms that show up when a table crash is about to happen? (like: out of swap memory, disk full, too much system load, ...?) Any comments/hints/links appreciated, thanks in advance, regards Roman #1 reason is powering off the computer without a proper shutdown. Get a UPS. -- Gerald L. Clark Sr. V.P. Development Supplier Systems Corporation Unix since 1982 Linux since 1992 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I don' t know difference between myisam_sort_buffer_size and myisam_max_sort_file_size
Thanks for your patient replay. This means the myisam_max_sort_file_size is the limitation of myisam_sort_buffer_size. And they are both implemented to improve the DDL statements. On Fri, Oct 31, 2008 at 11:45 PM, Jake Maul [EMAIL PROTECTED] wrote: From dev.mysql.com: myisam_max_sort_file_size : The maximum size of the temporary file that MySQL is allowed to use while re-creating a MyISAM index (during REPAIR TABLE, ALTER TABLE, or LOAD DATA INFILE). If the file size would be larger than this value, the index is created using the key cache instead, which is slower. The value is given in bytes. The default value is 2GB. If MyISAM index files exceed this size and disk space is available, increasing the value may help performance. myisam_sort_buffer_size: The size of the buffer that is allocated when sorting MyISAM indexes during a REPAIR TABLE or when creating indexes with CREATE INDEX or ALTER TABLE. The maximum allowable setting for myisam_sort_buffer_size is 4GB. (Default is 8MB) Simply: one's a buffer, one's a limit on the temp file size used for sorting. Which you might need to increase depends entirely on which limitation you're running into... if the current index files for the relevant tables on disk exceed 2GB, you might want to increase myisam_max_sort_file_size. Otherwise, myisam_sort_buffer_size might be better. Jake On Fri, Oct 31, 2008 at 12:20 AM, Moon's Father [EMAIL PROTECTED] wrote: Because alter table and repair table are both affected by myisam_sort_buffer_size or myisam_max_sort_file_size, I'm in confusion then. Anybody can tell me which to be adjusted when I want to improve the performance of my index operation. Thanks. -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
