| Bug #19464 | Out of memory error with "Select ... into outfile" | ||
|---|---|---|---|
| Submitted: | 1 May 2006 18:59 | Modified: | 13 Mar 2007 11:39 |
| Reporter: | Anthony Willard (Basic Quality Contributor) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 5.0.18 | OS: | Windows (Win2K3) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | qc | ||
[1 May 2006 18:59]
Anthony Willard
[1 May 2006 19:31]
MySQL Verification Team
Thank you for the bug report. Please start the mysql client with the option --quick and try again. Thanks in advance.
[1 May 2006 19:50]
Anthony Willard
I get the error using the mysql client, Query Browser and via the .NET connector. I'm not producing output to a client, but "select ... into outfile". The process errors out creating the output file on the server.
[1 May 2006 20:10]
MySQL Verification Team
Thank you for the feedback. What I meant is i.e.: C:\mysql\bin>mysql -uroot --quick we Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 to server version: 5.0.20-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> select * from wez into outfile "c:/temp/my.sql"; Query OK, 65536 rows affected (2.81 sec) mysql>
[1 May 2006 20:50]
Anthony Willard
That actually produced output. My problem arose from using the .NET Connector. Is there another set of parameters I should be using to get it to perform like "--quick" on mysql? What about Query Browser? Is this not a problem with the server itself?
[2 May 2006 19:16]
Tonci Grgin
Hi Anthony. A quote from manual: "The SELECT ... INTO OUTFILE statement is intended primarily to let you very quickly dump a table to a text file on the server machine. If you want to create the resulting file on some client host other than the server host, you cannot use SELECT ... INTO OUTFILE. In that case, you should instead use a command such as mysql -e "SELECT ..." > file_name to generate the file on the client host."
It seems that you're getting entire resultset to client datareader which is unnecessary. Consider following code that works (dump 3.5 mil. rows into 103MB file):
using System;
using MySql.Data.MySqlClient;
using MySql.Data.Types;
namespace ConsoleApplication1
{
/// <summary>
/// Summary description for Class1.
/// </summary>
class Class1
{
/// <summary>
/// The main entry point for the application.
/// </summary>
public Class1()
{
Bug19464();
System.Console.ReadLine();
}
[STAThread]
static void Main(string[] args)
{
new Class1();
}
public void Bug19464()
{
Console.WriteLine("19464 output");
Console.WriteLine("----------------------");
Console.WriteLine("\n\n\n");
string Sql = "select * into outfile 'mpcjenik.dmp' from mpcjenik";
String myConnectionString = "server=munja;uid=root;pwd=;database=ducan129;";
MySqlConnection Conn = new MySqlConnection();
Conn.ConnectionString = myConnectionString;
MySqlCommand Cmd = new MySqlCommand();
try
{
Conn.Open();
Cmd.CommandText = Sql;
Cmd.Connection = Conn;
Cmd.ExecuteScalar();
//or use Cmd.ExecuteNonQuery();
System.Console.WriteLine("DONE\n");
}
catch (MySqlException ex)
{
System.Console.WriteLine("Exception\n" + ex.Message);
}
finally
{
if (Conn != null)
{
Conn.Dispose();
}
if (Cmd != null)
{
Cmd.Dispose();
}
}
}
}
If you want dump on client machine you can either call mysqldump from your application or write you own class which fetches and writes record to outfile.
If this answer is satisfactory, you can close the bug report.
[2 May 2006 19:49]
Anthony Willard
I am using code very similar to that provided (I'm using ExecuteNonQuery() ). I am trying to dump the data to the server host, not a separate client. Even running my sample SQL statement using mysql, I get an error. See below: awillard@awillard-dev ~/input/Reporting/temp $ mysql -h ga-warehouse -u root --password=abc123 warehouse < test.sql ERROR 5 (HY000) at line 1: Out of memory (Needed 2294084 bytes) awillard@awillard-dev ~/input/Reporting/temp $ mysql --quick -h ga-warehouse -u root --password=abc123 warehouse < test.sql ERROR 5 (HY000) at line 1: Out of memory (Needed 2294084 bytes) -- body of test.sql select ClaimNumber, FILLER1, ChargeNumber, BillingProviderTIN, FILLER3, ProviderLastName, ProviderFirstName, ProviderMiddleInitial, ProviderBillingAddress1, ProviderBillingCity, ProviderBillingState, ProviderBillingZIP, EmployeeSSN, EmployeeLastName, EmployeeFirstName, EmployeeMiddleInitial, f_facts_date( EmployeeDOB ), EmployeeSex, EmployeeAddress1, EmployeeCity, EmployeeState, EmployeeZIP, DependentNumber, ClaimantLastName, ClaimantFirstName, ClaimantMiddleInit, f_facts_date( ClaimantDOB ), ClaimantSex, ClaimantRelationship, FILLER4, GroupID, DivisionID, DepartmentID, TypeOfClaim, PlanNumber, CoverageCode, NetworkID, CaseNumber, FILLER5, TypeofPayment, f_facts_money( EmployeePaidAmount ), ProviderPatientNumber, PreauthorizationCode, OtherCarrierIDCOB, f_facts_date( InitialDisabilityDate ), f_facts_date( NextMedicalReviewDate) , f_facts_date( EmployeeReturntoWorkDate ), ClaimAdjusterID, BenefitLevel, NonBillingProviderCode1, NonBillingProviderID1, NonBilingProviderCode2, NonBillingProviderID2, FLEXClaim, IssuingBank1, IssueCheck1, f_facts_money( ClaimPortion1 ), IssuingBank2, IssueCheck2, f_facts_money( CheckPortion2 ), IssuingBank3, IssueCheck3, f_facts_money( CheckPortion3 ), DocumentCode, f_facts_date( ReceivedDate ), AssociatedDocNumbers, DiskVolumeID, Comments1, CheckHandlingFlag, COBFlag, f_facts_date( FirstConsultDate ), AccidentCode, SpecialClaimCode, f_facts_date( PatientAdmitDate ), f_facts_date( PatientReleaseDate ), PatientAdmitHour, PatientReleaseHour, f_facts_money( RepricedAmount ), RepriceSource, TypeofHospitalBill, ProcedureCode1, f_facts_date( ProcedureDate1 ), ProcedureCode2, f_facts_date( ProceedureDate2 ), FILLER6, ProcessingBenefitCode, PlaceofService, PrimaryDiagnosisCode, HospitalConfinementNumber, ProcedureCode, ProcedureCodeModifier, right( f_facts_money( NumberofTreatmentUnits ), 5 ), f_facts_date( TreatmentStartDate ), f_facts_date( TreatmentCompleteDate ), f_facts_money( ProviderBillTotal ), f_facts_money( DisallowedAmount1 ), IneligibleReasonCode, f_facts_money( PPODiscountApplied ), f_facts_money( DeductibleTakenAmount ), f_facts_money( CoinsuranceDeductedAmount ), right( f_facts_money( ConsurancePercentage ), 3 ), f_facts_money( PaymentSavedAmount ), f_facts_money( PaymentWithheldAmount ), WithholdReasonCode, f_facts_money( TotalPaidAmount ), f_facts_date( PaidDate ), f_facts_date( PaymentDueDate ), FILLER7, ChargeAdjusterID, f_facts_date( ChargeProcessedDate ), ChargeStatus, StatusReason, StatusUpdateTime, f_facts_money( COBCarrierAllowedAmount ), f_facts_money( COBCarrierPaidAmount ), NDCC, ToothQuadrant, ToothSurfaceTreated, CutbackType, f_facts_money( ReinsuranceExclusionAmount ), f_facts_money( CarrierPaidAmount ), UserDefinedSpecialChargeCode, HospitalSpecialRevenueCode, DiagnosisCode1, DiagnosisCode2, DiagnosisCode3, DiagnosisCode4, f_facts_money( AllowableRepricedAmount ), f_facts_money( PaymentWithheldAmount2 ), WithholdReasonCode2, f_facts_money( DisallowedAmount2 ), IneligibleReasonCode2, Comments2, Comments3, Comments4, BillingProviderInternalKey, AdmissionType, AdmissionSource, MedicalRecordNumber, PatientStatus, DRG, OccuranceSpanCode1, f_facts_date( OccuranceSpanFromDate1 ), f_facts_date( OccuranceSpanEndDate1 ), OccuranceSpanCode2, f_facts_date( OccuranceSpanFromDate2 ), f_facts_date( OccuranceSpanEndDate2 ), OccuranceCode1, f_facts_date( OccuranceDate1 ), OccuranceCode2, f_facts_date( OccuranceDate2 ), left( BYTEFILL, 107 ) into outfile "D:\\InputFiles\\Warehouse\\dump\\arris0306.dat" fields terminated by "" lines terminated by "\n" from facts_claims where paiddate between '2006-1-1' and '2006-3-31' and groupid = 'ARRIS' ; -- end body of test.sql The various functions just provide formatting of the numbers and dates to meet the exported definition. Let me know if you need more information, I'm happy to provide it.
[2 May 2006 20:14]
Tonci Grgin
Hi Anthony. ERROR 5 is OS I/O error. Could it be that you're out of disk space or something?
[2 May 2006 20:45]
Anthony Willard
The file to build will be about 22MB in size; the disk has 50GB free.
[3 May 2006 6:12]
Tonci Grgin
Anthony, can you please supply small but complete test case reproducing the error you get every time it is run? The test should contain DDL, datadump of tables in question, server variables and, if you're not running from mysql client, connector/NET version, NET fw version and other relevant data.
[3 May 2006 14:34]
Anthony Willard
I posted a GZipped ZIP file with 3 modules: - a mysqldump of a test database containing test data (25,000 data rows) and supporting user functions. - a status dump showing the variables using "show variables" and "show global variables" - a query that can produce the error when ran using the mysql tool. Let me know if you need other information.
[4 May 2006 21:35]
Tonci Grgin
Anthony, thanks for great test case. I want to do some more tests before verifying this issue.
[9 May 2006 7:01]
Tonci Grgin
Hi Anthony. I was able to verify this bug on WIN32 platform as described with latest BK sources, 5.0.22. RAM allocation rising skyhigh resulting in server crash after dumping 21661 rows in 32512KB file. However, on Linux Suse (latest BK sources), test passed. 25000 rows dumped in 36.6 MB file. mysql> select version(); +--------------+ | version() | +--------------+ | 5.0.22-debug | +--------------+ 1 row in set (0.04 sec) Since I could not find workaround on WIN32, I'm raising severity to S1.
[5 Jul 2006 8:45]
Jake Anderson
just confirming that this problem also exists when not using any functions just a straight "select data into outfile" I have about 1.5M records in table, table is ~400mb in size or so (Call detail reccords) I select data into outfile pulling about 20 fields out (of about 60) memory use gets to > 700mb then it dies. I may be able to submit data if it would help but its about 60 mb compressed. select verstion(); = "5.0.22-community-nt"
[5 Jul 2006 8:48]
Jake Anderson
The machine in question has 1GB of ram and 2GB of swap, i havent monitored its swap use during the process, but even so its still using double the data size in physical ram. I don't think its swapping too much I don't hear any thrashing.
[5 Jul 2006 9:12]
Jake Anderson
as an aid to anyone else who happens to find this bug and needs an "instant" work around (sorta kinda) http://tlug.dnho.net/?q=node/209 has a method of producing a CSV file from mysql output mysql -uexampleuser -pletmein exampledb -B -e "select * from \`person\`;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > filename.csv
[4 Oct 2006 18:55]
Anthony Willard
I recently upgraded to 5.0.24a-community-max-nt-log as the result of another bug report relating to an Out of Memory error. That error is corrected with the newest version. I checked this issue as it is still open and it appears to be resolved as well. Something got fixed relating to the "Out of Memory" conditions. Can you please confirm that a change was made to address this issue and that it is not "dumb luck" that it works with the latest version?
[13 Mar 2007 11:39]
Tonci Grgin
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.
If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at
http://dev.mysql.com/doc/en/installing-source.html
Explanation:
Anthony, I can't repeat reported, and verified, behavior any more using 5.0.38BK. The DMP file is 37.525KB in size and has 25000 records:
24999 01123456789 SMOOTH MISTER 123 Main Street Anytown IL60000123456789JONES ALEX 01011950M456 Main St. Anytown IL6000001JONES JANE 02021951F01 ABCGROUP 1 MED1 40 A 000000000 AAA NET 000000000 000000000 000000000MED 05032006 000000000 123 BEN 11 781 781 00 001000101200601012006000000100000000000 000000000000000000000000000000000000000000000000 00000010005022006 AAA 05012006 000000000000000000 000000000000000000 781 000000000000000000 000000000
25000 01123456789 SMOOTH MISTER 123 Main Street Anytown IL60000123456789JONES ALEX 01011950M456 Main St. Anytown IL6000001JONES JANE 02021951F01 ABCGROUP 1 MED1 40 A 000000000 AAA NET 000000000 000000000 000000000MED 05032006 000000000 123 BEN 11 781 781 00 001000101200601012006000000100000000000 000000000000000000000000000000000000000000000000 00000010005022006 AAA 05012006 000000000000000000 000000000000000000 781 000000000000000000 000000000
