Hi all,
Today, my service threw an error look like:
Brighthouse specific error: Write operation to file or pipe failed.
at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int32& insertedId)
at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int32& insertedId)
at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId)
at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
at VC.Analytic.Loyalty.Service.VCALoyaltyService.ExecMySQL(MySqlCommand cmd)
my query look like (executed in store procedure):
SET @bh_dataformat = 'txt_variable';
SET @sql ='SELECT 0, bannerId, guid, SUM(view)v,''00-00-00''';
SET @sql =CONCAT(@sql, ' FROM `loyalty_visitor_view_sum`');
SET @sql =CONCAT(@sql, ' WHERE bannerId IN (SELECT bannerId FROM `activebanners`)');
SET @sql =CONCAT(@sql, ' GROUP BY bannerId, guid');
SET @sql = CONCAT(@sql,' INTO OUTFILE ''', pathOutFile, ''' FIELDS TERMINATED BY '','' ENCLOSED BY ''NULL''');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
The loyalty_visitor_view_sum table structure:
CREATE TABLE `loyalty_visitor_view_sum` (
`campaignId` INTEGER(11) NOT NULL DEFAULT '0',
`bannerId` INTEGER(11) NOT NULL DEFAULT '0',
`guid` BIGINT(20) NOT NULL DEFAULT '0',
`view` INTEGER(11) NOT NULL DEFAULT '0',
`dt` DATE NOT NULL DEFAULT '0000-00-00'
)ENGINE=brighthouse
CHARACTER SET 'ascii' COLLATE 'ascii_bin';
the table has got 202,171,270 data rows with 597 distinct Ad bannerIds and 5,579,521 distinct guid
The activebanners table structure:
CREATE TABLE `activebanners` (
`campaignId` INTEGER(11) NOT NULL DEFAULT '0',
`bannerId` INTEGER(11) NOT NULL DEFAULT '0'
)ENGINE=brighthouse
CHARACTER SET 'ascii' COLLATE 'ascii_bin';
the table has got only 598 data rows and 598 distinct active Ad bannerId too.
After about 2h querying time, the error thew like above. I’d know why the error threw and how to fix.
My server:
machine_type x86_64
os_name Linux
os_release 2.6.18-128.el5
cpu_num 8 CPUs
cpu_speed 2333 MHz
mem_total 32963028 KB
swap_total 9437176 KB
I’ll attach the brighhouse.ini & brighhouse.err later (I have no permission to access on these files) .
Thanks,
Thanh Nguyen.
