MySQL Bugs: #20275: MySQL server crashes when access from phpmyadmin is done

Bug #20275	MySQL server crashes when access from phpmyadmin is done
Submitted:	5 Jun 2006 21:20	Modified:	8 Jul 2006 13:16
Reporter:	Pavol Luptak	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.22-log	OS:	Linux (Gentoo Linux)
Assigned to:		CPU Architecture:	Any

Description:
I suppose that this problem can be related with my previous reported  problem (http://bugs.mysql.com/bug.php?id=20132) which I have temporary resolved with "replicate-ignore-table" directive, i.e. I simply ignore the InnoDB tables that causes MySQL server crash during replication.

Now I have the related problem - when I try to access to my "replicate-ignored" InnoDB tables of replicated database (as a SQL root user), the MySQL server (5.0.22) completely crashes with error:

060605 23:12:05 [Note] Slave I/O thread: connected to master 'repl@127.0.0.1:3307',  replication started in log 'mysql-bin.001598' at position 19015948
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=16777216
read_buffer_size=258048
max_used_connections=1
max_connections=100
threads_connected=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 92783 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x8bd05c8
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...
frame pointer (ebp) is NULL, did you compile with
-fomit-frame-pointer? Aborting backtrace!
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x20202020  is invalid pointer
thd->thread_id=538976288
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.

mysql> use database;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from table_that_is_ignored_during_replication ;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

The "replicate-ignored" tables really exist (as a part of snapshot from master server) - they only do not replicate from master server.

How to repeat:
mysql> use database;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from table_that_is_ignored_during_replication ;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

Thank you for a problem report. Please, answer my last question in related bug #20132. Is it possible that these problematic tables were simply copied from your master, version 4.x.y, (not dumped and restored)?

Yes, they were simply copied according to http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html (snapshot with TABLES WITH READ LOCK;)
Till now I have not used mysql dump / restore because it is time consuming.

On the other hand, manual clearly states (http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html):

"MySQL generally recommends that you dump and reload your tables from any previous version to upgrade to 5.0."...

Anyway, please, send the SHOW CREATE TABLE results for the "problematic" tables.

Hi Valeryi,
I have tried to transfer InnoDB tables using dump + restore (between MySQL 4.1 and 5.0.22) and it seems to work perfectly!

The main problem of MySQL dump comparing to binary snapshot is the fact that it is much more time-consuming (I can not afford to set read-lock for all tables of 5 GB production database - it takes approximately 30 minutes).

Now I transfer all MYISAM tables using binarny snapshot and all InnoDB tables using SQL dump/restore. I hope the binary structure of MYISAM is the same for MySQL 4.1 and MySQL 5.0.x, or not?

Thanks a lot for your help.

Pavol

Here are SHOW CREATE TABLE results for my InnoDB tables:

| tblOrgEvent | CREATE TABLE `tblOrgEvent` (
  `org_event_id` int(11) NOT NULL auto_increment,
  `org_event_from` datetime NOT NULL default '0000-00-00 00:00:00',
  `org_event_to` datetime NOT NULL default '0000-00-00 00:00:00',
  `org_event_title` varchar(100) collate utf8_slovak_ci NOT NULL default '',
  `org_event_body` mediumtext collate utf8_slovak_ci NOT NULL,
  `org_event_security` enum('all','owner') collate utf8_slovak_ci NOT NULL default 'all',
  `org_event_type` enum('event','note') collate utf8_slovak_ci NOT NULL default 'event',
  `org_event_repeat_period` int(11) NOT NULL default '0',
  `org_event_repeat_count` int(11) NOT NULL default '0',
  `org_event_up_id` int(11) default NULL,
  PRIMARY KEY  (`org_event_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_slovak_ci | 

| tblOrgEventMember | CREATE TABLE `tblOrgEventMember` (
  `org_event_id` int(11) NOT NULL default '0',
  `member_id` int(11) NOT NULL default '0',
  `member_type` enum('admin','contact') collate utf8_slovak_ci NOT NULL default 'admin',
  `org_event_owner` enum('no','in_list','hidden') collate utf8_slovak_ci NOT NULL default 'no',
  KEY `org_event_id` (`org_event_id`,`member_id`,`member_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_slovak_ci | 

| tblOrgContact | CREATE TABLE `tblOrgContact` (
  `org_contact_id` int(11) NOT NULL auto_increment,
  `org_contact_email` varchar(60) collate utf8_slovak_ci NOT NULL default '',
  `org_contact_name` varchar(100) collate utf8_slovak_ci NOT NULL default '',
  `org_contact_address` text collate utf8_slovak_ci NOT NULL,
  `org_contact_desc` text collate utf8_slovak_ci NOT NULL,
  PRIMARY KEY  (`org_contact_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_slovak_ci |

If your MyISAM tables has varchar, decimal or text columns, you definitely have to use dump and restore for them.

I am closing this report as a result of improper upgrade.