MySQL Bugs: #46300: Partitioned table: LOAD DATA INFILE "stalls"

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

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

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.

table create statement

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

data (tab separated)

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

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

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);

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.

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".

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

Bogdan, please answer Omer's question.

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.

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".

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.

same as this?  http://bugs.mysql.com/bug.php?id=51851

I did reproduced with 5.1.46 (regular, not cluster)
started test with 5.1.50 will report the result

same test fails on 5.5.3, I was unable to reproduce the test on 5.5.4 nor 5.5.5