| Bug #55044 | Lost connection | ||
|---|---|---|---|
| Submitted: | 7 Jul 2010 7:45 | Modified: | 8 Aug 2010 12:18 |
| Reporter: | deepak ugale | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 5.0.27 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[7 Jul 2010 7:45]
deepak ugale
[7 Jul 2010 7:52]
Anil Alpati
Total Nos. of records to be updated ?
[7 Jul 2010 7:57]
Anil Alpati
since its consumes more memory Solution - Increase max_allowed_packet size - 256M in my.cnf file (By Default 16M) OR By command line - SET GLOBAL MAX_ALLOWED_PACKET=256M RESTART THE SERVER.
[7 Jul 2010 9:08]
Valeriy Kravchuk
Please, send the results of explain select DAYOFWEEK(s.checkin_date) as name ,ifnull(avg(s.property_occ_perc),0) as property_occ_perc from stg_star s where s.property_id=169 group by DAYOFWEEK(s.checkin_date); and error log from your server.
[7 Jul 2010 10:31]
deepak ugale
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE s ref fk_stg_star_properties fk_stg_star_properties 8 const 1 Using where; Using temporary; Using filesort
[7 Jul 2010 10:42]
deepak ugale
no error for explain query
[7 Jul 2010 10:46]
Valeriy Kravchuk
I still need error log for the period that starts several minutes before the lost connection and up to the end. Also, please, send the results of SHOW CREATE TABLE and SHOW TABLE STATUS for the tables used in UPDATE, stg_star and tab1.
[7 Jul 2010 12:04]
deepak ugale
error for explain :-
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to
use near 'update tab1 t,
(select DAYOFWEEK(s.checkin_date) as name
,ifnull(a' at line 1
Basically tab1 is temperory table used in stored procedure
Drop table if exists tab1 ;
create temporary table tab1
( name integer(2) ,
property_occ_perc decimal(15,4) Default 0.00 ,
compset_occ_perc decimal(15,4) Default 0.00,
property_adr decimal(15,4) Default 0.00,
compset_adr decimal(15,4) Default 0.00,
property_revpar decimal(15,4) Default 0.00,
compset_revpar decimal(15,4) Default 0.00,
var_occ_perc decimal(15,4) Default 0.00,
var_adr decimal(15,4) Default 0.00,
var_revpar decimal(15,4) Default 0.00 );
Defination for `stg_star`
DROP TABLE IF EXISTS `stg_star`;
CREATE TABLE `stg_star` (
`property_id` bigint(20) NOT NULL,
`user_id` bigint(20) NOT NULL,
`checkin_date` date NOT
NULL,
`property_occ_perc` decimal(15,4) NOT NULL,
`compset_occ_perc` decimal(15,4) NOT NULL,
`property_adr`
decimal(15,4) NOT NULL,
`compset_adr` decimal(15,4) NOT NULL,
`property_revpar` decimal(15,4) NOT NULL,
`compset_revpar` decimal(15,4) NOT NULL,
`date_created` timestamp NOT NULL default CURRENT_TIMESTAMP,
KEY
`fk_stg_star_properties` (`property_id`),
CONSTRAINT `fk_stg_star_properties` FOREIGN KEY (`property_id`) REFERENCES
`properties` (`property_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
[7 Jul 2010 12:13]
Valeriy Kravchuk
EXPLAIN does not work for UPDATE or any other statements besides SELECT. I still miss the results of SHOW TABLE STATUS for the stg_star table. If tab1 is temporary, do you know how many rows does it have usually? Also I need error log of your server, not just errors you get as a result of some statements. Usually error log is <hostname>.err file in the data directory. I want to check if there was a server crash when you ran that UPDATE. Also I will see the exact server version in the error log.
[8 Jul 2010 9:24]
deepak ugale
Table status for stg_star COLUMNS : 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 VALUES : stg_star InnoDB 10 Compact 27713 133 3686400 0 1589248 0 2010-07-08 14:26:20 latin1_swedish_ci InnoDB free: 1444864 kB; (`property_id`) REFER `revup/properties`(`property_id`) And temporary table tab1 have only 7 rows . If i use temperory table instead of derived table in update it works fine
[8 Jul 2010 10:20]
deepak ugale
error log : 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=8388600 read_buffer_size=131072 max_used_connections=7 max_connections=100 threads_connected=7 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225791 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x9a72f38 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=0xb4b11e08, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x817bbf3 0x8163131 0x80ff7d5 0x812bc59 0x812c554 0x8119fd0 0x8128ec8 0x812a55e 0x81b85f0 0x81d1de7 0x827648b 0x8277e39 0x8277d1a 0x81edf78 0x81911c4 0x828cff1 0x828d159 0x82917ea 0x8290acf 0x829117f 0x8196ed4 0x8198569 0x8198b15 0x8199edc 0x819a96a 0x47d633db 0x47cbd06e 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 0x9bc8ac8 = update tab1 t, (select DAYOFWEEK(s.checkin_date) as name ,ifnull(avg(s.property_occ_perc),0) as property_occ_perc from stg_star s where s.property_id=p_property_id and s.checkin_date between sf_start_or_end_date_of_month(p_month,p_year,0) and sf_start_or_end_date_of_month(p_month1,p_year1,1) and s.property_occ_perc <>0 group by DAYOFWEEK(s.checkin_date) ) s set t.property_occ_perc =s.property_occ_perc where t.name=s.name thd->thread_id=95 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 100708 14:57:40 mysqld restarted 100708 14:57:40 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... 100708 14:57:40 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 12 2517594300. InnoDB: Doing recovery: scanned up to log sequence number 12 2517594736 100708 14:57:40 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed 100708 14:57:40 InnoDB: Started; log sequence number 12 2517594736 100708 14:57:40 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.0.27' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution 100708 14:59:51 [Note] /usr/libexec/mysqld: Normal shutdown 100708 14:59:53 InnoDB: Starting shutdown... 100708 14:59:55 InnoDB: Shutdown completed; log sequence number 12 2517594746 100708 14:59:55 [Note] /usr/libexec/mysqld: Shutdown complete 100708 14:59:55 mysqld ended 100708 14:59:56 mysqld started 100708 14:59:56 InnoDB: Started; log sequence number 12 2517594746 100708 14:59:56 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.0.27' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution
[8 Jul 2010 10:24]
Valeriy Kravchuk
Please, send the resolved stack trace for this server crash. Also I noted that you use a very old version, 5.0.27. Can you try to upgrade to current 5.0.91 and check if the crash is repeatable with it?
[8 Jul 2010 11:36]
deepak ugale
instead of derived table in update query i have used temp table to store
data of derived table like
instead of this below query :
update tab1 ,(select DAYOFWEEK(s.checkin_date) as name
,ifnull(avg(s.property_occ_perc),0) as property_occ_perc
from stg_star s
where s.property_id=169
group by DAYOFWEEK(s.checkin_date) ) tab2
set tab1.property_occ_perc =tab2.property_occ_perc
where tab1.name=tab2.name
updated query :
Drop table if exists temp2;
create temporary table temp2
( name integer(2) ,
property_occ_perc decimal(15,4) Default 0.00)
insert into temp2 (name,property_occ_perc)
select DAYOFWEEK(s.checkin_date) as name,ifnull(avg(s.property_occ_perc),0) as property_occ_perc
from stg_star s
where s.property_id=169
group by DAYOFWEEK(s.checkin_date)
update tab1 ,tab2
set tab1.property_occ_perc =tab2.property_occ_perc
where tab1.name=tab2.name ;
#---------------------------------------------------------------
This way it is working fine
I can't upgrade to new version B'cos as production server is having same version
[8 Jul 2010 11:52]
Valeriy Kravchuk
You can not upgrade and you had found a workaround for this problem. Good. But what about resolved stack trace? It may help me to find older, known bug report with same symptoms.
[8 Jul 2010 12:04]
deepak ugale
I am not able to get what exactly "resolved stack trace" means and how to get it ?
[8 Jul 2010 12:18]
Valeriy Kravchuk
This is a stack trace in your error log: 0x817bbf3 0x8163131 0x80ff7d5 0x812bc59 0x812c554 0x8119fd0 0x8128ec8 0x812a55e 0x81b85f0 0x81d1de7 0x827648b 0x8277e39 0x8277d1a 0x81edf78 0x81911c4 0x828cff1 0x828d159 0x82917ea 0x8290acf 0x829117f 0x8196ed4 0x8198569 0x8198b15 0x8199edc 0x819a96a 0x47d633db 0x47cbd06e And here, http://dev.mysql.com/doc/refman/5.0/en/using-stack-trace.html, you can read how to resolve it against your mysqld if you are on Unix-like OS.
[8 Aug 2010 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".
