Export Huge Table

Currently, the dbvisualizer cannot export huge tables directly into cvs file. It causes virtual memory problems. It will be nice to have the export function to export huge tables without loading them entirely into virtual memory. Following is an example to export a large table on a Vista 64bit computer with 8G RAM and set the virtual memory to be 4G. @export on; 11:05:36 [@EXPORT - 0 row(s), 0.069 secs] Command processed @export set AppendFile="clear" BinaryFileDir="" BinaryFormat="Size" BooleanFalseFormat="false" BooleanTrueFormat="true" CLOBFileDir="" CLOBFormat="Size" ClipboardName="" CsvColumnDelimiter="\t" CsvIncludeColumnHeader="true" CsvIncludeSQLCommand="" CsvRemoveNewlines="false" CsvRowCommentIdentifier="" CsvRowDelimiter="\n" DateFormat="yyyy-MM-dd" DecimalNumberFormat="Unformatted" Destination="File" Encoding="MS950" ExcelIncludeColumnHeader="true" ExcelIncludeSQLCommand="" ExcelIntroText="" ExcelTextOnly="false" ExcelTitle="DbVisualizer export output" Filename="C:\Users\chlee\test.csv" Format="SQL" HtmlIncludeSQLCommand="" HtmlIntroText="" HtmlTitle="DbVisualizer export output" ImageHeight="" ImageWidth="" NumberFormat="Unformatted" Orientation="Portrait" QuoteDuplicateEmbedded="true" QuoteTextData="Single" RowCount="-1" ShowNullAs="(null)" SqlCommanderEditor="New Editor" SqlIncludeCreateDDL="" SqlIncludeSQLCommand="" SqlRowCommentIdentifier="--" SqlSeparator=";" TableName="" TimeFormat="HH:mm:ss" TimeStampFormat="yyyy-MM-dd HH:mm:ss" XmlIncludeSQLCommand="" XmlIntroText="" XmlStyle="DbVisualizer"; 11:05:36 [@EXPORT - 0 row(s), 0.005 secs] Command processed ... Physical database connection acquired for: localhost SELECT * FROM `rands`.`cd_tbl`; 11:07:22 [SELECT - 0 row(s), 0.000 secs] Virtual Memory Problem! The application is running low on virtual memory. @export off; 11:07:22 [@EXPORT - 0 row(s), 0.000 secs] Command processed ... 4 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.074/0.000 sec [3 successful, 0 warnings, 1 errors]

I have two servers. One with MySQL 5.5.2 m2 and the other with MySQL 5.1.14 GA. My JDBC driver is ConnectorJ 5.1.12 GA. The problem occurs when export on both servers.

Dear Roger, I am not sure if I follow you instruction correctly. In the driver properties setion, I set the defaultFetchSize = -2147483648. That cause the connection error while browse around the database. An error occurred while executing the database request for: MySQL 5.5.2-m2-community MySQL-AB JDBC Driver mysql-connector-java-5.1.12 ( Revision: ${bzr.revision-id} ) Short message: Operation not allowed after ResultSet closed The command that caused the problem: SELECT TABLE_NAME, TABLE_TYPE, ENGINE, VERSION, ROW_FORMAT, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH, MAX_DATA_LENGTH, INDEX_LENGTH, DATA_FREE, AUTO_INCREMENT, CREATE_TIME, UPDATE_TIME, CHECK_TIME, TABLE_COLLATION, CHECKSUM, CREATE_OPTIONS FROM information_schema.tables WHERE table_schema = 'r225' and table_type NOT IN ('VIEW') ORDER BY TABLE_NAME Long Message: Operation not allowed after ResultSet closed Details:    Type: java.sql.SQLException    Error Code: 0    SQL State: S1000 System Information: Product: DbVisualizer Personal 7.0.4 Build: #1494 (2010/02/24 08:28) Java VM: Java HotSpot(TM) 64-Bit Server VM Java Version: 1.6.0_14 Java Vendor: Sun Microsystems Inc. OS Name: Windows Vista OS Arch: amd64 OS Version: 6.0

Dear Roger, I edited two parameters in the Driver Properties and successfully changed the recordset to be server-sided. 1. Set useCursorFetch = true 2. Set defaultFetchSize = 5000 Thanks for your kindly advise. Best regards, Chih-Hsin Lee.