Description:
I was testing the federated table performance of Mysql. But I got problems when I execute a sql statement like "select count(*) from wp_tracking.track_tracking where site_id =4;" when the table is a federated table. It said "Lost connection to MySQL server" and the local Mysql service restart at this point. Every time I executed the sql statement the problem alway occured.
the error message is ERROR 2013 (HY000): Lost connection to MySQL server during query. And if you turn to localhost.localdomain.err you will find that:
Number of processes running now: 0
070307 17:08:02 mysqld restarted
070307 17:08:04 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...
070307 17:08:05 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 2 398555669.
InnoDB: Doing recovery: scanned up to log sequence number 2 398555669
InnoDB: In a MySQL replication slave the last master binlog file
InnoDB: position 0 1044948, file name binlog.000035
InnoDB: Last MySQL binlog file position 0 446702979, file name ./binlog.000010
070307 17:08:05 InnoDB: Started; log sequence number 2 398555669
070307 17:08:05 [Note] Recovering after a crash using binlog
070307 17:08:05 [Note] Starting crash recovery...
070307 17:08:05 [Note] Crash recovery finished.
070307 17:08:05 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.0.24-max-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition - Experimental (GPL)
The sql statement will execute about 100 minisecond when the table is local table. The table contain 927410 rows and the sql statement will return 42196.
How to repeat:
remote host:
create a table like this:
CREATE TABLE track_tracking ( id varchar(32) NOT NULL default '', domain_id char(32) default NULL, site_id char(32) NOT NULL default '', browser char(20) default NULL, ip char(32) default NULL, screen_width int(11) default NULL, screen_height int(11) default NULL, site_url char(250) default NULL, os char(20) default NULL, screen_color char(10) default NULL, javascript char(5) default NULL, referrer_url char(250) default NULL, referrer_name char(30) default NULL, cookie char(80) default NULL, loading_time int(11) default NULL, country char(32) default NULL, region char(32) default NULL, city char(32) default NULL, user_id char(32) default NULL, extend_info char(32) default NULL, page_rank char(2) default NULL, page_owner char(32) default NULL, sso_key char(32) default NULL, is_new char(1) default NULL, screen_resolution char(20) default NULL, session_id char(60) default NULL, browser_version char(32) default NULL, page_name char(32) default NULL, update_time timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, create_time timestamp NOT NULL default '1990-01-01 00:00:00', PRIMARY KEY (id,site_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8
local host:
CREATE TABLE track_tracking ( id varchar(32) NOT NULL default '', domain_id char(32) default NULL, site_id char(32) NOT NULL default '', browser char(20) default NULL, ip char(32) default NULL, screen_width int(11) default NULL, screen_height int(11) default NULL, site_url char(250) default NULL, os char(20) default NULL, screen_color char(10) default NULL, javascript char(5) default NULL, referrer_url char(250) default NULL, referrer_name char(30) default NULL, cookie char(80) default NULL, loading_time int(11) default NULL, country char(32) default NULL, region char(32) default NULL, city char(32) default NULL, user_id char(32) default NULL, extend_info char(32) default NULL, page_rank char(2) default NULL, page_owner char(32) default NULL, sso_key char(32) default NULL, is_new char(1) default NULL, screen_resolution char(20) default NULL, session_id char(60) default NULL, browser_version char(32) default NULL, page_name char(32) default NULL, update_time timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, create_time timestamp NOT NULL default '1990-01-01 00:00:00', PRIMARY KEY (id,site_id)) ENGINE=FEDERATED DEFAULT CHARSET=utf8 connection='mysql://root:password@192.168.1.114:3306/wp_tracking/track_tracking';
And issue "select count(*) from wp_tracking.track_tracking where site_id =4;". You are expect to see the error of
I have dump the table which is 450M. I am sorry I can not attach it on the site.
