Bug #46300 Partitioned table: LOAD DATA INFILE "stalls" - HPUX 11.23 PA RISC
Submitted: 20 Jul 2009 9:07 Modified: 21 Aug 2010 19:33
Reporter: diener jan Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.1.36-log, 5.1.46, 5.5.3 OS:HP/UX (11.23 PA 2.0, LINUX F12)
Assigned to: CPU Architecture:Any
Tags: hpux, LOAD DATA INFILE, partitioning
Triage: Triaged: D2 (Serious)

[20 Jul 2009 9:07] diener jan
Description:
When doing a LOAD DATA INFILE, the mysql client just "stalls".
The process state remains in "Saving state" (and never terminates - I have to kill the mysqld process through the OS)

IMPORTANT:
1. It works on my ubuntu
2. It works on my HPUX with a "normal" table - without partitioning
=> The problem occurs on my HPUX with the table partitioned

NOTE: (to re-gain access, I kill the db brutally)
1. after "repair table eventlog" the data is perfectly available.
2. no entry in error.log

--------------------------------------------------------------
command:
LOAD DATA INFILE '/tmp/data.text' 
INTO TABLE eventlog (<field list>);

processlist:
+----+------+-----------+-----+---------+------+--------------+------------+
| Id |User  | Host      | db  | Command | Time | State        |            |  
+----+------+-----------+-----+---------+------+--------------+------------+
| 3  | root | localhost | SMS | Query   | 385  | Saving state | LOAD DA... |
| 5  | root | localhost |     | Query   | 0    |              | show pro.. |
+----+------+-----------+-----+---------+------+--------------+------------+

table:
create TABLE eventlog (
  id bigint(20) NOT NULL auto_increment,
  type tinyint(4) NOT NULL default '0',
  si tinyint(4) NOT NULL default '0',
  error_code int(10) NOT NULL default '0',
  origin char(20) NOT NULL default '',
  destination char(20) NOT NULL default '',
  odestination char(20) NOT NULL default '',
  ...
  ts_log datetime NOT NULL,
  ts_ost datetime NOT NULL,
  ts_ast datetime NOT NULL,
  ts_delivery datetime NOT NULL,
  ...
  PRIMARY KEY  (id,ts_ast)
  ,KEY ts_log (ts_log)
  ,KEY ts_ost (ts_ost)
  ,KEY ts_ast (ts_ast)
  ,KEY origin (origin)
  ,KEY destination (destination)
  ,KEY odestination (odestination)
)
TYPE=MyISAM
DATA DIRECTORY='/db1/SMS/'
INDEX DIRECTORY='/db2/SMS/'
PARTITION BY RANGE (to_days(ts_ast)) (
   PARTITION p20070101 VALUES LESS THAN (to_days('2007-01-02'))
     DATA DIRECTORY  '/db1/SMS/'
     INDEX DIRECTORY '/db2/SMS/'
  ,PARTITION p20070102 VALUES LESS THAN (to_days('2007-01-03'))
     DATA DIRECTORY  '/db1/SMS/'
     INDEX DIRECTORY '/db2/SMS/'
  ,PARTITION p20070103 VALUES LESS THAN (to_days('2007-01-04'))
     DATA DIRECTORY  '/db1/SMS/'
     INDEX DIRECTORY '/db2/SMS/'
  ,PARTITION p20070104 VALUES LESS THAN (to_days('2007-01-05'))
     DATA DIRECTORY  '/db1/SMS/'
     INDEX DIRECTORY '/db2/SMS/'
);

How to repeat:
mysql>LOAD DATA INFILE '/tmp/data.text' INTO TABLE eventlog (<field list>);
# into a partitioned table
[20 Jul 2009 9:30] Sveta Smirnova
Thank you for the report.

Could you please provide minimum data file problem is repeatable with?

Please also check if your HP-UX contains all latest patches.
[24 Jul 2009 8:45] diener jan
table create statement

Attachment: eventlog.sql (text/plain), 1.20 KiB.

[24 Jul 2009 8:46] diener jan
data (tab separated)

Attachment: test.data (application/octet-stream, text), 654 bytes.

[24 Jul 2009 8:53] diener jan
we just applied the latest patch bundle yesterday:
  QPKAPPS               B.11.23.0906.079 Applications Patches for HP-UX 11i v2, June 2009
  QPKBASE               B.11.23.0906.079 Base Quality Pack Bundle for HP-UX 11i v2, June 2009
[24 Jul 2009 9:05] diener jan
load statement
LOAD DATA INFILE '/tmp/test.data' 
INTO TABLE eventlog (smsc,type,si,error_code,origin,destination,odestination,ml,ts_log,ts_ost,ts_ast,ts_delivery,ts_ndt,da,imsi_orig,imsi_dest,billid);
[27 Jul 2009 6:19] Sveta Smirnova
Thank you for the feedback.

I still can not repeat described behavior. Please indicate accurate package you use (filename which you downloaded) or, if you compiled MySQL yourself, provide configure options used.
[27 Aug 2009 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[2 Dec 2009 6:43] Bogdan Kecman
I just repeated the bug with:

CREATE TABLE `t1` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `active` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `f1` smallint(5) unsigned DEFAULT NULL,
  `f2` tinyint(3) unsigned DEFAULT NULL,
  `f3` smallint(5) unsigned DEFAULT NULL,
  `f4` int(10) unsigned DEFAULT NULL,
  `f5` decimal(5,2) DEFAULT NULL,
  `f6` tinyint(3) unsigned DEFAULT NULL,
  `f7` tinyint(3) unsigned DEFAULT NULL,
  `f8` tinyint(3) unsigned DEFAULT NULL,
  `f9` tinyint(3) unsigned DEFAULT NULL,
  `f10` smallint(4) unsigned DEFAULT NULL,
  `f11` mediumint(8) unsigned DEFAULT NULL,
  `f12` int(10) unsigned DEFAULT NULL,
  `f13` tinyint(3) unsigned DEFAULT NULL,
  `f14` int(10) unsigned DEFAULT NULL,
  `f15` tinyint(3) unsigned DEFAULT NULL,
  `f16` mediumint(8) unsigned DEFAULT NULL,
  `f17` decimal(5,2) DEFAULT NULL,
  `f18` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `f19` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`,`active`),
  KEY `i_1` (`f1`,`f2`,`f3`,`f4`),
  KEY `i_2` (`f3`,`f4`,`f1`,`f2`),
  KEY `i_3` (`f2`),
  KEY `i_4` (`f4`),
  KEY `i_5` (`f5`),
  KEY `i_6` (`f6`),
  KEY `i_7` (`f7`),
  KEY `i_8` (`f8`),
  KEY `i_9` (`f9`),
  KEY `i_10` (`f10`),
  KEY `i_11` (`f11`),
  KEY `i_12` (`f12`),
  KEY `i_13` (`f13`),
  KEY `i_14` (`f14`),
  KEY `i_15` (`f15`),
  KEY `i_16` (`f16`),
  KEY `i_17` (`f17`)
) ENGINE=MyISAM AUTO_INCREMENT=9644921 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY LIST (`active`)
(PARTITION p_inactive VALUES IN (0,9,8,7,6) ENGINE = MyISAM,
 PARTITION p_active VALUES IN (1,2,3,4,5) ENGINE = MyISAM) */;
/*!40101 SET character_set_client = @saved_cs_client */;

insert into t1 select * from t2;

(t2 has 9M rows and active is always=1)

The thread is stuck in "saving state"

5.1.39-ndb-7.0.9-log is the mysql version I'm using 
fedora 10 64bit is the os I'm testing on

managed to repeat it twice in a row

I managed to do it twice in a row ... will test "newer" version now
[11 Dec 2009 9:22] Sveta Smirnova
Bogdan, please answer Omer's question.
[11 Dec 2009 11:18] Bogdan Kecman
Omer, I have not tested with "normal 5.1" server code. Will be doing more tests on this as soon as I get back from vacation.
[12 Jan 2010 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[11 Aug 2010 22:02] Chris Ferraro
reopening ticket with requested info.

seen on both versions:
MySQL-server-community-5.1.38-0.rhel5.x86_64.rpm
MySQL-server-community-5.1.46-1.rhel5.x86_64.rpm

once hung, standard service stop forces close of thread but does not kill mysqld processes.  must hard kill and mysqlcheck repair before startup.

remove partitioning is current workaround.
[11 Aug 2010 23:57] Chris Ferraro
same as this?  http://bugs.mysql.com/bug.php?id=51851
[21 Aug 2010 19:33] Bogdan Kecman
I did reproduced with 5.1.46 (regular, not cluster)
started test with 5.1.50 will report the result
[21 Aug 2010 19:37] Bogdan Kecman
same test fails on 5.5.3, I was unable to reproduce the test on 5.5.4 nor 5.5.5