MySQL Bugs: #15033: Anybody can help me out to sort out this problem

Bug #15033	Anybody can help me out to sort out this problem
Submitted:	17 Nov 2005 20:39	Modified:	25 Dec 2005 14:45
Reporter:	Parthasarathy Addagatla	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.15	OS:	Linux (Red Hat Enterprise Linux ES rele)
Assigned to:		CPU Architecture:	Any

Description:
051118 01:49:28  mysqld started
051118  1:49:28  InnoDB: Started; log sequence number 0 43655
051118  1:49:28 [Note] Recovering after a crash using mysql-bin
051118  1:49:28 [Note] Starting crash recovery...
051118  1:49:28 [Note] Crash recovery finished.
051118  1:49:28 [Warning] mysql.user table is not updated to new password format; Disabling new password usage until mysql_fix_privilege_tables is run
051118  1:49:28 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.15-standard-log'  socket: '/home/mysql/mysql.sock'  port: 3306  MySQL Community Edition - Standard (GPL)
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=8388608
read_buffer_size=1044480
max_used_connections=1
max_connections=50
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 110391 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation. 
 
thd=0x8a2d478 
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=0xaec02b94, backtrace may not be correct. 
Stack range sanity check OK, backtrace follows: 
0x8156750 
0xb75acdf8 
0x8a76b70 
0x837d455 
0x837dc10 
0x81941de 
0x818a4a4 
0x83833c3 
0x81b89a6 
0x81a951a 
0x819aa4a 
0x819b20e 
0x8197700 
0x816bd61 
0x8172880 
0x8169d03 
0x816983d
0x8168d48
0xb75a6dac
0xb74e09ea
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 trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...thd->query at 0x8a552a8 = create table report2(IBU varchar(50),Region varchar(50),ibubranch varchar(50),customer varchar(50), complainnature varchar(50),  total varchar(50) ) as SELECT complain1.IBU, complain1.region,complain1.ibubranch , complain1.customer , complain1.complainnature ,Count(*) AS total  FROM complain1 GROUP BY complain1.region, complain1.IBU,complain1.ibubranch, complain1.customer, complain1.complainnature
thd->thread_id=1 The manual page at http://www.mysql.com/doc/en/Crashing.html containsinformation that should help you find out what is causing the crash.

0x8156750 set_info__7sp_headPcUixxP14st_sp_chisticsUl + 100
0xb75acdf8 _end + -1359017700
0x8a76b70 _end + 4957844
0x837d455 __15Item_func_fieldRt4List1Z4Item + 69
0x837dc10 __17Item_func_bit_negP4Item + 20
0x81941de row_ins_foreign_report_add_err + 2110
0x818a4a4 ibuf_get_volume_buffered + 948
0x83833c3 fix_length_and_dec__13Item_str_conv + 3
0x81b89a6 pars_select_statement + 182
0x81a951a row_sel_build_prev_vers_for_mysql + 42
0x819aa4a row_unlock_for_mysql + 246
0x819b20e row_mysql_freeze_data_dictionary + 346
0x8197700 row_ins_duplicate_error_in_clust + 1776
0x816bd61 dict_table_add_to_cache + 565
0x8172880 dict_foreign_parse_drop_constraints + 1172
0x8169d03 dict_create_add_foreigns_to_dictionary + 2575
0x816983d dict_create_add_foreigns_to_dictionary + 1353
0x8168d48 dict_create_or_check_foreign_constraint_tables + 404
0xb75a6dac _end + -1359042352
0xb74e09ea _end + -1359854322

mysql> select @@global.key_buffer_size ,@@global.read_buffer_size ,@@global.sort_buffer_size,@@global.max_connections;
+--------------------------+---------------------------+---------------------------+--------------------------+
| @@global.key_buffer_size | @@global.read_buffer_size | @@global.sort_buffer_size | @@global.max_connections |
+--------------------------+---------------------------+---------------------------+--------------------------+
|                  8388608 |                   1044480 |                   1048568 |                       50 |
+--------------------------+---------------------------+---------------------------+--------------------------+
1 row in set (0.00 sec)

How to repeat:
Seems to happen at random.  I don't know if this is due to a hardware problem
(bad memory - segmentation fault).Also please see the output of top -C for this system

 02:04:44  up 12:01,  5 users,  load average: 0.00, 0.00, 0.00 
79 processes: 78 sleeping, 1 running, 0 zombie, 0 stopped 
CPU states:  cpu    user    nice  system    irq  softirq  iowait    idle 
           total    0.0%    0.5%    0.0%   0.0%     0.0%    0.0%   99.3% 
Mem:  4114240k av,  940464k used, 3173776k free,       0k shrd,  209380k buff 
       389636k active,             428752k inactive 
Swap: 8191976k av,       0k used, 8191976k free                  543368k cached 
 
  PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME CPU COMMAND 
 5752 root      25  10 13196  12M  8744 S N   0.5  0.3   3:08   2 rhn-applet-gui 
    1 root      15   0   508  508   452 S     0.0  0.0   0:04   1 init 
    2 root      RT   0     0    0     0 SW    0.0  0.0   0:00   0 migration/0 
    3 root      RT   0     0    0     0 SW    0.0  0.0   0:00   1 migration/1 
    4 root      RT   0     0    0     0 SW    0.0  0.0   0:00   2 migration/2 
    5 root      RT   0     0    0     0 SW    0.0  0.0   0:00   3 migration/3 
    6 root      15   0     0    0     0 SW    0.0  0.0   0:00   2 keventd 
    7 root      34  19     0    0     0 SWN   0.0  0.0   0:00   0 ksoftirqd/0 
    8 root      39  19     0    0     0 SWN   0.0  0.0   0:00   1 ksoftirqd/1 
    9 root      34  19     0    0     0 SWN   0.0  0.0   0:00   2 ksoftirqd/2 
   10 root      34  19     0    0     0 SWN   0.0  0.0   0:00   3 ksoftirqd/3 
   13 root      25   0     0    0     0 SW    0.0  0.0   0:00   2 bdflush 
   11 root      15   0     0    0     0 SW    0.0  0.0   0:00   1 kswapd 
   12 root      15   0     0    0     0 SW    0.0  0.0   0:00   1 kscand 
   14 root      15   0     0    0     0 SW    0.0  0.0   0:01   1 kupdated 
   15 root      25   0     0    0     0 SW    0.0  0.0   0:00   2 mdrecoveryd 
   21 root      25   0     0    0     0 SW    0.0  0.0   0:00   2 scsi_eh_0 
   24 root      15   0     0    0     0 SW    0.0  0.0   0:00   0 kjournald

Hi!

Please use the correct mysqld.sym file to resolve the stack trace. The resolved trace is nonsensical.

Regards,

Heikki

Thank you for the problem report. Please, do what Heikki asked you to.

Have you upgraded from 4.x.x?

Hello Heikki Tuuri
Thank for looking into this matter
I didn't do any upgradaion .I have directly installed the mysql 5.0.15 from mysql site of OS compatible.But I have change the datadir from /var/lib/mysql to /home/mysql has it is needed for insufficient space on /var partition

[root@toss tmp]# df -H
Filesystem             Size   Used  Avail Use% Mounted on
/dev/sda6             8.3GB  2.0GB  5.9GB  25% /
/dev/sda1             1.1GB   28MB  952MB   3% /boot
/dev/sda2              83GB  4.7GB   74GB   6% /home
/dev/sda9              88GB  1.8GB   82GB   3% /opt
none                  2.2GB      0  2.2GB   0% /dev/shm
/dev/sda7             5.2GB  434MB  4.5GB   9% /tmp
/dev/sda3              21GB  4.2GB   16GB  22% /usr
/dev/sda8             3.1GB  990MB  2.0GB  34% /var

Corrected output for mysqld.sym 

0x8156750 handle_segfault + 416
0xb75acdf8 _end + -1358655032
0x8a76b70 _end + 5320512
0x837d455 my_no_flags_free + 21
0x837dc10 free_root + 192
0x81941de _Z8closefrmP8st_table + 110
0x818a4a4 _Z16free_cache_entryP8st_table + 20
0x83833c3 hash_delete + 531
0x81b89a6 _ZN13select_create8send_eofEv + 182
0x81a951a _Z9do_selectP4JOINP4ListI4ItemEP8st_tableP9Procedure + 538
0x819aa4a _ZN4JOIN4execEv + 4250
0x819b20e _Z12mysql_selectP3THDPPP4ItemP13st_table_listjR4ListIS1_ES2_jP8st_orderSB_S2_SB_mP13select_resultP18st_select_lex_unitP13st_sel + 286
0x8197700 _Z13handle_selectP3THDP6st_lexP13select_resultm + 304
0x816bd61 _Z21mysql_execute_commandP3THD + 2481
0x8172880 _Z11mysql_parseP3THDPcj + 304
0x8169d03 _Z16dispatch_command19enum_server_commandP3THDPcj + 1139
0x816983d _Z10do_commandP3THD + 141
0x8168d48 handle_one_connection + 600
0xb75a6dac _end + -1358679684
0xb74e09ea _end + -1359491654
Please do the needful to sort out this problem.

***********
Thanks & Regards
Parthasarathy

How to repeat:
IT happens offen by executing this query
Some pointers may be invalid and cause the dump to abort...thd->query
at 0x8a552a8 = create table report2(IBU varchar(50),Region
varchar(50),ibubranch varchar(50),customer varchar(50), complainnature
varchar(50),  total varchar(50) ) as SELECT complain1.IBU,
complain1.region,complain1.ibubranch , complain1.customer ,
complain1.complainnature ,Count(*) AS total  FROM complain1 GROUP BY
complain1.region, complain1.IBU,complain1.ibubranch,
complain1.customer, complain1.complainnature
mysql get hanged out, due to this query

***************

Thanks & Regards
Parthasarathy

Thank you for the clarification. Please, send the results of the SHOW CREATE TABLE complain1 statement.

Hello Mr.Valeriy Kravchuk
The ouput of SHOW CREATE TABLE complain1 is as follows
mysql> SHOW CREATE TABLE complain1 ;
| Cnimp |CREATE TABLE `Cnimp` (
  `IBUBranch` varchar(50) default NULL,
  `LRNumber` varchar(50) NOT NULL,
  `LRDate` varchar(50) default NULL,
  `Origin` varchar(50) default NULL,
  `Destination` varchar(50) default NULL,
  `ProductGroup` varchar(50) default NULL,
  `ProductName` varchar(50) default NULL,
  `ActualWeight` varchar(50) default NULL,
  `ChargedWeight` varchar(150) default NULL,
  `UOM` varchar(50) default NULL,
  `Consignor` varchar(50) default NULL,
  `Consignee` varchar(50) default NULL,
  `PaymentMode` varchar(50) default NULL,
  `ExpectedDateOfDelvery` varchar(50) default NULL,
  `PackageType` varchar(50) default NULL,
  `NoofPackages` varchar(50) default NULL,
  `DeliveryAddress` varchar(150) default NULL,
  `CustomerInsurence` varchar(50) default NULL,
  `PolicyNumber` varchar(50) default NULL,
  `PolicyDate` varchar(50) default NULL,
  `MRNumber` varchar(50) default NULL,
  `MRDate` varchar(50) default NULL,
  `MRAmount` varchar(50) default NULL,
  `AdvanceAMTreceived` varchar(50) default NULL,
  `ServiceType` varchar(50) default NULL,
  `Rate` varchar(50) default NULL,
  `Freight` varchar(50) default NULL,
  `SurchargeOnFreight` varchar(50) default NULL,
  `SurchargeOnValue` varchar(50) default NULL,
  `Handling` varchar(50) default NULL,
  `Loading` varchar(50) default NULL,
  `MultipleDelivery` varchar(50) default NULL,
  `Miscellenious` varchar(50) default NULL,
  `TotalFreight` varchar(50) default NULL,
  `AddlChg1Name` varchar(50) default NULL,
  `AddlChg1Amt` varchar(50) default NULL,
  `AddlChg2Name` varchar(50) default NULL,
  `AddlChg2Amt` varchar(50) default NULL,
  `AddlChg3Name` varchar(50) default NULL,
  `AddlChg3Amt` varchar(50) default NULL,
  `AddlChg4Name` varchar(50) default NULL,
  `AddlChg4Amt` varchar(50) default NULL,
  `AddlChg5Name` varchar(50) default NULL,
  `AddlChg5Amt` varchar(50) default NULL,
  `ManifestNumber` varchar(50) default NULL,
  `VHCNumber` varchar(50) default NULL,
  `ViaforExports` varchar(50) default NULL,
  `ContainerNumber` varchar(50) default NULL,
  `SealNumber` varchar(50) default NULL,
  `ContainerType` varchar(50) default NULL,
  `ShippingLine` varchar(50) default NULL,
  `DestinationPort` varchar(50) default NULL,
  `CHA` varchar(50) default NULL,
  `DeliveryBranch` varchar(50) default NULL,
  `DeliveryDate` varchar(50) default NULL,
  `DeliveredQty` varchar(50) default NULL,
  `DeliveredPackages` varchar(50) default NULL,
  `Shortage` varchar(50) default NULL,
  `Damage` varchar(50) default NULL,
  `AmountofLoss` varchar(50) default NULL,
  `ReceivedBy` varchar(50) default NULL,
  `DlySupervisor` varchar(50) default NULL,
  `PODrecdDate` varchar(50) default NULL,
  `DlyRemarks` varchar(50) default NULL,
  `Transhipment` varchar(50) default NULL,
  `AddedDate` varchar(50) default NULL,
  `ModiDate` varchar(50) default NULL,
  `BillNumber` varchar(50) default NULL,
  `BillDate` varchar(50) default NULL,
  `CorporateCode` varchar(50) default NULL,
  `existind` varchar(50) default NULL,
  PRIMARY KEY  (`LRNumber`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

Hello Mr.Valeriy Kravchuk
The ouput of SHOW CREATE TABLE complain1 is as follows
mysql> SHOW CREATE TABLE complain1 ;
| complain1 |CREATE TABLE `complain1` (
  `region` varchar(50) default NULL,
  `ibubranch` varchar(50) NOT NULL default '',
  `person` varchar(50) default NULL,
  `complaindate` varchar(50) default NULL,
  `complainnum` varchar(50) NOT NULL default '',
  `mode` varchar(50) default NULL,
  `corporatecode` varchar(50) default NULL,
  `customer` varchar(50) default NULL,
  `address` varchar(250) default NULL,
  `lrnum` varchar(50) NOT NULL default '',
  `lrdate` varchar(50) default NULL,
  `origin` varchar(50) default NULL,
  `destination` varchar(50) default NULL,
  `invoiceno` varchar(50) default NULL,
  `invoicedate` varchar(50) default NULL,
  `invoiceval` varchar(50) default NULL,
  `qty` varchar(50) default NULL,
  `truckno` varchar(50) default NULL,
  `transit` varchar(50) default NULL,
  `dly_date` varchar(50) default NULL,
  `dly_qty` varchar(50) default NULL,
  `complainnature` varchar(50) default NULL,
  `description` blob,
  `update1` blob,
  `upd1_date` varchar(50) default NULL,
  `update2` blob,
  `upd2_date` varchar(50) default NULL,
  `update3` blob,
  `upd3_Date` varchar(50) default NULL,
  `update4` blob,
  `upd4_date` varchar(50) default NULL,
  `update5` varchar(250) default NULL,
  `upd5_date` varchar(50) default NULL,
  `remarks` blob,
  `closuredat` varchar(50) default NULL,
  `rootcause` blob,
  `custfeedback` blob,
  `closureremarks` blob,
  `addeddate` varchar(50) default NULL,
  `modidate` varchar(50) default NULL,
  `existind` varchar(50) default NULL,
  `uid` varchar(10) default NULL,
  `cdt` varchar(20) default NULL,
  `uname` varchar(20) default NULL,
  `close` char(1) default 'N',
  `HOremark` varchar(255) default '',
  `ROremark` varchar(255) default '',
  `approve` char(1) default 'N',
  `IBU` varchar(50) default '',
  PRIMARY KEY  (`complainnum`,`ibubranch`,`lrnum`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

Hello Mr.Valeriy Kravchuk,
      Sir , can I have solution to fix this problem.
       And I am sorry for giving the details of Cnimp table ,instead of complain1.
      
**********
regards
Parthasarathy

Hello Mr.Heikki Tuuri/Mr.Valeriy Kravchuk ,
              Please help me to correct this problem.

***********
regards
Parthasarathy

Thank you for the additional information. Your query does not give me any crashes by itself on 5.0.17-BK on Linux:

mysql> CREATE TABLE `complain1` (
    ->    `region` varchar(50) default NULL,
    ->    `ibubranch` varchar(50) NOT NULL default '',
    ->   `person` varchar(50) default NULL,
    ->   `complaindate` varchar(50) default NULL,
    ->   `complainnum` varchar(50) NOT NULL default '',
    ->   `mode` varchar(50) default NULL,
    ->   `corporatecode` varchar(50) default NULL,
    ->   `customer` varchar(50) default NULL,
    ->   `address` varchar(250) default NULL,
    ->   `lrnum` varchar(50) NOT NULL default '',
    ->   `lrdate` varchar(50) default NULL,
    ->   `origin` varchar(50) default NULL,
    ->   `destination` varchar(50) default NULL,
    ->   `invoiceno` varchar(50) default NULL,
    ->   `invoicedate` varchar(50) default NULL,
    ->   `invoiceval` varchar(50) default NULL,
    ->   `qty` varchar(50) default NULL,
    ->   `truckno` varchar(50) default NULL,
    ->   `transit` varchar(50) default NULL,
    ->   `dly_date` varchar(50) default NULL,
    ->   `dly_qty` varchar(50) default NULL,
    ->   `complainnature` varchar(50) default NULL,
    ->   `description` blob,
    ->   `update1` blob,
    ->   `upd1_date` varchar(50) default NULL,
    ->   `update2` blob,
    ->   `upd2_date` varchar(50) default NULL,
    ->   `update3` blob,
    ->   `upd3_Date` varchar(50) default NULL,
    ->   `update4` blob,
    ->   `upd4_date` varchar(50) default NULL,
    ->   `update5` varchar(250) default NULL,
    ->   `upd5_date` varchar(50) default NULL,
    ->   `remarks` blob,
    ->   `closuredat` varchar(50) default NULL,
    ->   `rootcause` blob,
    ->   `custfeedback` blob,
    ->   `closureremarks` blob,
    ->   `addeddate` varchar(50) default NULL,
    ->   `modidate` varchar(50) default NULL,
    ->   `existind` varchar(50) default NULL,
    ->   `uid` varchar(10) default NULL,
    ->   `cdt` varchar(20) default NULL,
    ->   `uname` varchar(20) default NULL,
    ->   `close` char(1) default 'N',
    ->   `HOremark` varchar(255) default '',
    ->   `ROremark` varchar(255) default '',
    ->   `approve` char(1) default 'N',
    ->   `IBU` varchar(50) default '',
    ->   PRIMARY KEY  (`complainnum`,`ibubranch`,`lrnum`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0,01 sec)

mysql> create table report2(IBU varchar(50),Region
    -> varchar(50),ibubranch varchar(50),customer varchar(50), complainnature
    -> varchar(50),  total varchar(50) ) as SELECT complain1.IBU,
    -> complain1.region,complain1.ibubranch , complain1.customer ,
    -> complain1.complainnature ,Count(*) AS total  FROM complain1 GROUP BY
    -> complain1.region, complain1.IBU,complain1.ibubranch,
    -> complain1.customer, complain1.complainnature;
Query OK, 0 rows affected (0,12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.17    |
+-----------+
1 row in set (0,00 sec)

So, can you, please, inform about the number of rows in the complain1 table (or upload its dump using File tab)? Send the results of ANALYZE TABLE complain1 statement and all your my.cnf content also (I need to check are you using query cache).

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