Bug #15049 MySQL Innodb crash on multiple select with
Submitted: 18 Nov 2005 12:37 Modified: 23 Nov 2005 17:42
Reporter: Ady Wicaksono Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:4.1.9.Max OS:Linux (RedHat Linux 9)
Assigned to: Heikki Tuuri CPU Architecture:Any

[18 Nov 2005 12:37] Ady Wicaksono
Description:
I have MySQL with about 12 billion rows on 1 table InnoDB

when i try to create 2 process, each select count(*) on the same table.... after a long time about 30 minutes
it crashed :(

1. first process is running from MySQL shell>
2. second process is created by Perl application using perl-DBD-MySQL-2.1021-3

ANy information?

Log file :

051116 20:27:22InnoDB: Assertion failure in thread 2366216768 in file srv0srv.c line 1873
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html
InnoDB: about forcing recovery.
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=402653184
read_buffer_size=12578816
max_used_connections=31
max_connections=910
threads_connected=11
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1782208 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=(nil)
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0x8d0992ec, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x8115587
0x4004a618
(nil)
0x420de407
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trac
e. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

Number of processes running now: 0
051116 20:27:23  mysqld restarted
InnoDB: ##########################################################
InnoDB:                          WARNING!
InnoDB: The log sequence number in ibdata files is higher
InnoDB: than the log sequence number in the ib_logfiles! Are you sure
InnoDB: you are using the right ib_logfiles to start up the database?
InnoDB: Log sequence number in ib_logfiles is 33 1628260918, log
InnoDB: sequence numbers stamped to ibdata file headers are between
InnoDB: 0 0 and 192 460914688.
InnoDB: ##########################################################
051116 20:27:24  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
051116 20:27:24  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 33 1628260918.
InnoDB: Doing recovery: scanned up to log sequence number 33 1628260918
051116 20:27:24  InnoDB: Flushing modified pages from the buffer pool...
051116 20:27:24  InnoDB: Started; log sequence number 33 1628260918
/usr/sbin/mysqld-max: ready for connections.
Version: '4.1.9-Max'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Official MySQL RPM

How to repeat:
see description

Thx
[18 Nov 2005 12:45] Ady Wicaksono
SOrry, i forget to add innodb monitor output belows:

=====================================
051116 22:02:20 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 16 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 689509, signal count 689307
--Thread 1103972416 has waited at ../include/btr0btr.ic line 28 for 808.00 seconds the semaphore:
S-lock on RW-latch at 0x88cdd6b8 created in file buf0buf.c line 469
a writer (thread id 1105434432) has reserved it in mode  exclusive
number of readers 0, waiters flag 1
Last time read locked in file buf0flu.c line 562
Last time write locked in file buf0buf.c line 1674
Mutex spin waits 419984, rounds 1239101, OS waits 28526
RW-shared spins 1461558, OS waits 645128; RW-excl spins 23827, OS waits 13550
------------
TRANSACTIONS
------------
Trx id counter 0 184018
Purge done for trx's n:o < 0 184014 undo n:o < 0 0
History list length 4
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 19807, OS thread id 1104364992
MySQL thread id 13, query id 4527 10.1.30.70 root
---TRANSACTION 0 0, not started, process no 19807, OS thread id 1104168512
MySQL thread id 12, query id 3416 10.1.30.70 root
---TRANSACTION 0 0, not started, process no 19807, OS thread id 1105630912
MySQL thread id 6, query id 2236 10.1.30.70 root
---TRANSACTION 0 183957, not started, process no 19807, OS thread id 1105237952
MySQL thread id 2, query id 1112 10.1.30.70 root
---TRANSACTION 0 184017, ACTIVE 809 sec, process no 19807, OS thread id 1103972416 fetching rows, thread declared inside Inno
DB 400
mysql tables in use 1, locked 0
MySQL thread id 1, query id 4547 localhost.localdomain 127.0.0.1 root Sending data
select count(*) from 200510_t_outgoing_sms
Trx read view will not see trx with id >= 0 184018, sees < 0 184016
---TRANSACTION 0 184016, ACTIVE 2843 sec, process no 19807, OS thread id 1105434432 fetching rows, thread declared inside Inn
oDB 85
mysql tables in use 1, locked 0
MySQL thread id 11, query id 2277 localhost.localdomain 127.0.0.1 root Sending data
SELECT COUNT(*) AS J FROM 200510_t_outgoing_sms WHERE out_sms_typePremium like '%ESIA%'
Trx read view will not see trx with id >= 0 184017, sees < 0 184017
--------
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
138816 OS file reads, 319219 OS file writes, 132517 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 21333, seg size 21335, is empty
Ibuf for space 0: size 1, free list len 21333, seg size 21335,
3036633 inserts, 3036633 merged recs, 195773 merges
Hash table size 4425293, used cells 7698, node heap has 9 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 33 2490109135
Log flushed up to   33 2490109135
Last checkpoint at  33 2490109135
0 pending log writes, 0 pending chkp writes
25557 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 1216985802; in additional pool allocated 6322176
Buffer pool size   65536
Free buffers       1
Database pages     65526
Modified db pages  0
Pending reads 1
Pending writes: LRU 0, flush list 0, single page 0
Pages read 1265480, created 54370, written 469896
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
--------------
ROW OPERATIONS
--------------
2 queries inside InnoDB, 0 queries in queue
Main thread process no. 19807, id 2374605248, state: waiting for server activity
Number of rows inserted 1434622, updated 0, deleted 0, read 53272048
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
InnoDB: ###### Diagnostic info printed to the standard error stream
InnoDB: Error: semaphore wait has lasted > 600 seconds
InnoDB: We intentionally crash the server, because it appears to be hung.
051116 22:02:27InnoDB: Assertion failure in thread 2366216768 in file srv0srv.c line 1873
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html
InnoDB: about forcing recovery.
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=402653184
read_buffer_size=12578816
max_used_connections=6
max_connections=910
threads_connected=6
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1782208 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=(nil)
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0x8d0992ec, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x8115587
0x4004a618
(nil)
0x420de407
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trac
e. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

Number of processes running now: 0
051116 22:02:27  mysqld restarted
InnoDB: ##########################################################
InnoDB:                          WARNING!
InnoDB: The log sequence number in ibdata files is higher
InnoDB: than the log sequence number in the ib_logfiles! Are you sure
InnoDB: you are using the right ib_logfiles to start up the database?
InnoDB: Log sequence number in ib_logfiles is 33 2490109135, log
InnoDB: sequence numbers stamped to ibdata file headers are between
InnoDB: 0 0 and 192 460914688.
InnoDB: ##########################################################
051116 22:02:27  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
051116 22:02:28  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 33 2490109135.
InnoDB: Doing recovery: scanned up to log sequence number 33 2490109135
051116 22:02:28  InnoDB: Flushing modified pages from the buffer pool...
051116 22:02:28  InnoDB: Started; log sequence number 33 2490109135
/usr/sbin/mysqld-max: ready for connections.
Version: '4.1.9-Max'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Official MySQL RPM
051118 13:51:14 [Note] /usr/sbin/mysqld-max: Normal shutdown
[18 Nov 2005 12:48] Ady Wicaksono
again, i do apoligize
this is my system information

RAM: 4Gbytes
$ free
             total       used       free     shared    buffers     cached
Mem:       4116840    4098284      18556          0      64128    2699160
-/+ buffers/cache:    1334996    2781844
Swap:      4192924      60896    4132028

/etc/my.cnf

[client]
port            = 3306
socket          = /var/lib/mysql/mysql.sock

# The MySQL server
[mysqld]
port               = 3306
socket             = /var/lib/mysql/mysql.sock
max_connections    = 910
max_connect_errors = 9999999
skip-locking
key_buffer         = 384M
max_allowed_packet = 4M
table_cache        = 2049
sort_buffer_size   = 12M
read_buffer_size   = 12M
myisam_sort_buffer_size = 128M
thread_cache       = 48
query_cache_size   = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

# Master Replication
# ------------------
#log-bin
#server-id=1

# My Inno DB tables
# -----------------
# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir =
innodb_data_file_path = /data1/ibdata1:2000M;/data1/ibdata2:2000M;/data1/ibdata3:2000M;/data1/ibdata4:2000M;/data2/ibdata5:2000M;/data2/ibdata6:2000M;/data2/ibdata7:2000M;/data2/ibdata8:2000M;/data3/ibdata9:2000M;/data3/ibdata10:2000M;/data3/ibdata11:2000M;/data3/ibdata12:2000M;/data4/ibdata13:2000M;/data4/ibdata14:2000M;/data4/ibdata15:2000M;/data4/ibdata16:2000M;/data5/ibdata17:2000M;/data5/ibdata18:2000M;/data5/ibdata19:2000M;/data5/ibdata20:2000M;/data1/ibdata21:1000M;/data2/ibdata22:1000M;/data3/ibdata23:1000M;/data4/ibdata24:1000M;/data5/ibdata25:1000M;/data1/ibdata26:200M;/data2/ibdata27:400M;/data3/ibdata28:400M;/data4/ibdata29:400M;

# ------ OLD ---
innodb_log_group_home_dir = /data1
innodb_log_arch_dir = /data1
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 1024M
innodb_additional_mem_pool_size = 60M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 100M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

CPU

processor       : 0
vendor_id       : GenuineIntel
cpu family      : 15
model           : 4
model name      : Intel(R) Xeon(TM) CPU 3.00GHz
stepping        : 1
cpu MHz         : 2992.808
cache size      : 1024 KB
physical id     : 0
siblings        : 2
fdiv_bug        : no
hlt_bug         : no
f00f_bug        : no
coma_bug        : no
fpu             : yes
fpu_exception   : yes
cpuid level     : 5
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm lm
bogomips        : 5976.88

processor       : 1
vendor_id       : GenuineIntel
cpu family      : 15
model           : 4
model name      : Intel(R) Xeon(TM) CPU 3.00GHz
stepping        : 1
cpu MHz         : 2992.808
cache size      : 1024 KB
physical id     : 0
siblings        : 2
fdiv_bug        : no
hlt_bug         : no
f00f_bug        : no
coma_bug        : no
fpu             : yes
fpu_exception   : yes
cpuid level     : 5
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm lm
bogomips        : 5976.88

processor       : 2
vendor_id       : GenuineIntel
cpu family      : 15
model           : 4
model name      : Intel(R) Xeon(TM) CPU 3.00GHz
stepping        : 1
cpu MHz         : 2992.808
cache size      : 1024 KB
physical id     : 3
siblings        : 2
fdiv_bug        : no
hlt_bug         : no
f00f_bug        : no
coma_bug        : no
fpu             : yes
fpu_exception   : yes
cpuid level     : 5
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm lm
bogomips        : 5976.88

processor       : 3
vendor_id       : GenuineIntel
cpu family      : 15
model           : 4
model name      : Intel(R) Xeon(TM) CPU 3.00GHz
stepping        : 1
cpu MHz         : 2992.808
cache size      : 1024 KB
physical id     : 3
siblings        : 2
fdiv_bug        : no
hlt_bug         : no
f00f_bug        : no
coma_bug        : no
fpu             : yes
fpu_exception   : yes
cpuid level     : 5
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm lm
bogomips        : 5976.88
[18 Nov 2005 18:34] Valeriy Kravchuk
Thank you for a problem report. Please, send the SHOW CREATE TABLE results for the table in question. 

Can you try to upgrade to newer, 4.1.15 e.g. version of MySQL, by the way? Your  4.1.9 is a bit old.
[18 Nov 2005 21:28] Heikki Tuuri
Ady,

this thread is apparently reading in a data page to the buffer pool:

"
---TRANSACTION 0 184016, ACTIVE 2843 sec, process no 19807, OS thread id
1105434432 fetching rows, thread declared inside Inn
oDB 85
mysql tables in use 1, locked 0
MySQL thread id 11, query id 2277 localhost.localdomain 127.0.0.1 root Sending
data
SELECT COUNT(*) AS J FROM 200510_t_outgoing_sms WHERE out_sms_typePremium like
'%ESIA%'
"

But the number of pending reads to the buffer pool stays at 1. The thread must be hung somewhere processing the I/O. Since there are no pending preads reported in the output, it cannot be hung inside a pread() call.

Ady, I remember you having problems with the OS or the hardware in the past. Is this computer reliable?

Regards,

Heikki
[23 Nov 2005 12:07] Ady Wicaksono
I try to upgrade to MySQL 5.0.15 and the found that the problem has been fixed. I use Dell Power Edge 2850 and i think it's good machine with double Intell  Xeon(TM) CPU 3.00GHz (Hyperthread) and 4 Gbytes RAM.

------------- MySQL Version --------------
mysql> \s
--------------
mysql  Ver 14.12 Distrib 5.0.15, for pc-linux-gnu (i686) using readline 5.0

Connection id:          1190
Current database:       smsserver
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.0.15-max
Protocol version:       10
Connection:             127.0.0.1 via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
TCP port:               3306
Uptime:                 5 days 4 hours 59 min 56 sec

Threads: 6  Questions: 323268  Slow queries: 0  Opens: 0  Flush tables: 2  Open tables: 108  Queries per second avg: 0.718

------------- MySQL Version --------------

And i try with 2 session to count data on table

------------ session 1 using mysql shell ------------
mysql> select count(*) from 200510_t_outgoing_sms;
+----------+
| count(*) |
+----------+
| 13312397 |
+----------+
1 row in set (1 min 42.62 sec)

------------------------------------------------------------------

---------------- session 2 using perl client ---------------- 
$ perl something
---------------- session 2 using perl client ---------------- 

--- InnoDB status
====================================
051123 18:53:43 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 43 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 731119, signal count 640824
--Thread 2389438400 has waited at ../include/btr0btr.ic line 28 for 0.00 seconds the semaphore:
S-lock on RW-latch at 0x4087f0e8 created in file buf0buf.c line 485
a writer (thread id 2389438400) has reserved it in mode  exclusive
number of readers 0, waiters flag 1
Last time read locked in file btr0pcur.c line 247
Last time write locked in file buf0buf.c line 1688
--Thread 2386465856 has waited at ../include/btr0btr.ic line 28 for 0.00 seconds the semaphore:
S-lock on RW-latch at 0x4087f0e8 created in file buf0buf.c line 485
a writer (thread id 2389438400) has reserved it in mode  exclusive
number of readers 0, waiters flag 1
Last time read locked in file btr0pcur.c line 247
Last time write locked in file buf0buf.c line 1688
Mutex spin waits 356141, rounds 500490, OS waits 2617
RW-shared spins 1472577, OS waits 727827; RW-excl spins 318, OS waits 305
------------------------
LATEST FOREIGN KEY ERROR
------------------------
051121 14:21:25 Transaction:
TRANSACTION 0 189254, ACTIVE 0 sec, process no 21180, OS thread id 2389438400 inserting
mysql tables in use 1, locked 1
6 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 628, query id 97609 localhost 127.0.0.1 root update
insert into t_group_access_right (group_id,modul_id,read_access, write_access) values (1,511,'TRUE','TRUE')
Foreign key constraint fails for table `smsservice/t_group_access_right`:
,
  CONSTRAINT `0_2483` FOREIGN KEY (`modul_id`) REFERENCES `t_app_modul` (`app_id`) ON DELETE CASCADE ON UPDATE CASCADE
Trying to add in child table, in index `modul_id` tuple:
DATA TUPLE: 2 fields;
 0: len 4; hex 800001ff; asc     ;; 1: len 4; hex 8000046b; asc    k;;

But in parent table `smsservice/t_app_modul`, in index `PRIMARY`,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 12; 1-byte offsets; info bits 0
 0: len 4; hex 80000208; asc     ;; 1: len 6; hex 000000021eb6; asc       ;; 2: len 7; hex 000006a5440729; asc     D );; 3: len 8; hex 4d6f62696c652038; asc Mobile 8;; 4: len 0; hex ; asc ;; 5: len 0; hex ; asc ;; 6: len 4; hex 80000003; asc     ;; 7: len 1; hex 23; asc #;; 8: len 4; hex 00000016; asc     ;; 9: len 4; hex 8000004c; asc    L;; 10: len 4; hex 80000005; asc     ;; 11: len 4; hex 800000aa; asc     ;;

------------
TRANSACTIONS
------------
Trx id counter 0 192837
Purge done for trx's n:o < 0 192132 undo n:o < 0 0
History list length 8
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 192836, not started, process no 21180, OS thread id 2388849600
MySQL thread id 1192, query id 324336 10.1.30.70 root
---TRANSACTION 0 192823, not started, process no 21180, OS thread id 1107137728
MySQL thread id 1149, query id 317851 10.1.30.70 root
---TRANSACTION 0 192821, not started, process no 21180, OS thread id 2386858688
MySQL thread id 1148, query id 321065 10.1.30.70 root
---TRANSACTION 0 192799, not started, process no 21180, OS thread id 1104037696
MySQL thread id 1134, query id 325402 10.1.30.70 root
SHOW INNODB STATUS
---TRANSACTION 0 192831, not started, process no 21180, OS thread id 2389241920
MySQL thread id 1071, query id 323262 10.1.30.70 root
---TRANSACTION 0 192828, not started, process no 21180, OS thread id 1106744768
MySQL thread id 1068, query id 322182 10.1.30.70 root
---TRANSACTION 0 192834, ACTIVE 45 sec, process no 21180, OS thread id 2386465856 fetching rows
mysql tables in use 1, locked 0
MySQL thread id 1191, query id 323271 localhost 127.0.0.1 root Sending data
SELECT COUNT(*) AS J FROM 200510_t_outgoing_sms WHERE out_sms_typePremium like '%ESIA%'
Trx read view will not see trx with id >= 0 192835, sees < 0 192832
---TRANSACTION 0 192832, ACTIVE 55 sec, process no 21180, OS thread id 2389438400 fetching rows
mysql tables in use 1, locked 0
MySQL thread id 1190, query id 323268 localhost 127.0.0.1 root Sending data
select count(*) from 200510_t_outgoing_sms
Trx read view will not see trx with id >= 0 192833, sees < 0 192833
--------
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: doing file i/o (read thread) ev set
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 64, 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
56953 OS file reads, 1104 OS file writes, 608 OS fsyncs
1 pending preads, 0 pending pwrites
40.88 reads/s, 976124 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 20485, seg size 20487, is empty
Ibuf for space 0: size 1, free list len 20485, seg size 20487,
28 inserts, 28 merged recs, 2 merges
Hash table size 4425293, used cells 9, node heap has 1 buffer(s)
0.00 hash searches/s, 0.21 non-hash searches/s
---
LOG
---
Log sequence number 34 2722677472
Log flushed up to   34 2722677472
Last checkpoint at  34 2722677472
0 pending log writes, 0 pending chkp writes
147 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 1221196594; in additional pool allocated 6755072
Buffer pool size   65536
Free buffers       0
Database pages     65535
Modified db pages  0
Pending reads 64
Pending writes: LRU 0, flush list 0, single page 0
Pages read 1835222, created 17, written 1189
2435.71 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 939 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
3 read views open inside InnoDB
Main thread process no. 21180, id 2371983680, state: sleeping
Number of rows inserted 102, updated 3, deleted 1357, read 101297793
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 237074.28 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
[23 Nov 2005 12:10] Ady Wicaksono
Here is the SHOW CREATE TABLE Result

CREATE TABLE `200510_t_outgoing_sms` (
  `out_sms_id` int(11) NOT NULL auto_increment,
  `out_sms_time` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `out_sms_dest` varchar(16) NOT NULL default '',
  `out_sms_msg` varchar(160) NOT NULL default '',
  `out_sms_status` tinyint(2) NOT NULL default '0',
  `out_sms_typePremium` varchar(48) NOT NULL default '',
  `out_sms_trx_id` varchar(40) NOT NULL default '',
  `out_sms_trx_date` varchar(33) NOT NULL default '',
  `out_sms_typeService_telkomsel` varchar(20) NOT NULL default '',
  `out_sms_pin_quiz` varchar(15) NOT NULL default '',
  `out_sms_quiz_keycode` varchar(20) NOT NULL default '',
  `out_sms_quiz_answer` varchar(160) NOT NULL default '',
  `in_sms_message_id` varchar(22) NOT NULL default '',
  `in_sms_time` datetime NOT NULL default '0000-00-00 00:00:00',
  `in_sms_smsc_seq_number` int(11) NOT NULL default '0',
  `out_sms_city` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`out_sms_id`),
  KEY `t_idx01` (`out_sms_typePremium`(3)),
  KEY `t_idx02` (`out_sms_quiz_keycode`(3)),
  KEY `t_idx03` (`out_sms_quiz_answer`(8)),
  KEY `t_idx04` (`out_sms_dest`(7)),
  KEY `t_idx05` (`out_sms_dest`(13),`out_sms_quiz_keycode`(15),`out_sms_quiz_answer`(10)),
  KEY `t_idx06` (`out_sms_time`),
  KEY `t_idx07` (`out_sms_time`,`out_sms_quiz_keycode`(6)),
  KEY `t_idx08` (`out_sms_trx_id`(8)),
  KEY `t_idx09` (`out_sms_trx_id`(10),`out_sms_dest`(6)),
  KEY `t_idx10` (`out_sms_status`,`out_sms_typePremium`(3)),
  KEY `t_idx11` (`in_sms_time`),
  KEY `t_idx12` (`out_sms_time`,`out_sms_typePremium`(3)),
  KEY `t_idx14` (`in_sms_smsc_seq_number`),
  KEY `t_idx13` (`out_sms_city`(7))
) ENGINE=InnoDB DEFAULT CHARSET=latin1

ANd table status (result from MySQL 5) is :
+----------------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+--------------------+--------------------------------------------------------------------------------+
| Name                       | Engine | Version | Row_format | Rows     | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation         | Checksum | Create_options     | Comment                                                                        |
+----------------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+--------------------+--------------------------------------------------------------------------------+
| 200510_t_outgoing_sms      | InnoDB |       9 | Redundant  | 12870599 |            338 |  4352638976 |               0 |   5832474624 |         0 |       13312401 | 2005-11-16 17:22:33 | NULL                | NULL                | latin1_swedish_ci |     NULL |                    | InnoDB free: 5303296 kB                                                        |
+----------------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+--------------------+--------------------------------------------------------------------------------+
[23 Nov 2005 12:12] Ady Wicaksono
Heiki, last problem exist with old machine (HP ML150) before i migrate to Dell PowerEdge 2850
[23 Nov 2005 17:42] Heikki Tuuri
Hi!

No bug has been fixed between 4.1.9 and 5.0.15 that could explain why mysqld-5.0.15 works.

The behavior may depend on some random conditions.

I am setting this bug report to the state 'Can't repeat'. If you encounter more problems, please post them here or open a new bug report.

Regards,

Heikki