Description:
When trying to load tab delimited data from a named pipe into an INNODB the thread hangs. I am unable to make any changes on the table once this occurs. I have piped the same data into a text file and MySQL properly processes all the lines in the file and the thread ends. Killing the thread seems to make no difference to MySQL; I have been restarting the entire server when this occurs as I am unable to shutdown MySQL after the transaction. Also, the transactions only seem to be commited when the server is restarted. The behavior when using named pipes does not seem to be explained well in the manual and is not similar to inputting from a flat file. I have autocommit=0 at a global level,
Below is information related to the issue:
Syntax to produce issue:
LOAD DATA INFILE '/var/lib/mysql/mysql.pipe' INTO TABLE syslog.test;
mysql> SHOW PROCESSLIST;
+----+------+-----------+------+---------+------+------------------+---------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+------------------+---------------------------------------------------------------------+
| 13 | root | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST |
| 14 | root | localhost | NULL | Query | 70 | NULL | LOAD DATA INFILE '/var/lib/mysql/mysql.pipe' INTO TABLE syslog.test |
| 15 | root | localhost | NULL | Connect | 50 | Reading from net | NULL |
+----+------+-----------+------+---------+------+------------------+---------------------------------------------------------------------+
Per second averages calculated from the last 40 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 21, signal count 21
Mutex spin waits 9, rounds 140, OS waits 2
RW-shared spins 38, OS waits 19; RW-excl spins 0, OS waits 0
------------
TRANSACTIONS
------------
Trx id counter 0 9494809
Purge done for trx's n:o < 0 9494806 undo n:o < 0 0
History list length 1
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 9494807, not started, process no 2859, OS thread id 147466
MySQL thread id 13, query id 97 localhost root
SHOW ENGINE INNODB STATUS
---TRANSACTION 0 9494808, ACTIVE 41 sec, process no 2937, OS thread id 163851, thread declared inside InnoDB 301
mysql tables in use 1, locked 1
2 lock struct(s), heap size 320, undo log entries 100
MySQL thread id 14, query id 94 localhost root
LOAD DATA INFILE '/var/lib/mysql/mysql.pipe' INTO TABLE syslog.test
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
119 OS file reads, 141 OS file writes, 79 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.17 writes/s, 0.10 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 0, seg size 2, is empty
Ibuf for space 0: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 6375037, used cells 100, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 1835758456; in additional pool allocated 5332864
Buffer pool size 98304
Free buffers 98136
Database pages 167
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 155, created 12, written 345
0.00 reads/s, 0.00 creates/s, 0.22 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
1 queries inside InnoDB, 0 queries in queue
Main thread process no. 2851, id 114696, state: waiting for server activity
Number of rows inserted 719, updated 0, deleted 3619, read 7297
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
From SHOW STATUS;:
| Table_locks_immediate | 37 |
| Table_locks_waited | 0 |
| Threads_cached | 0 |
| Threads_connected | 3 |
| Threads_created | 3 |
| Threads_running | 2 |
| Uptime | 2335 |
+----------------------------+----------+
How to repeat:
Using mkfifo create a pipe and populate it with tab delimited data.
Create a table with matching columns.
Connect to mysql and issue:
LOAD DATA INFILE '/var/lib/mysql/mysql.pipe' INTO TABLE syslog.test;
