Bug #61088 Cannot alter table or update all row in table at all
Submitted: 7 May 2011 7:59 Modified: 17 Jan 2012 20:17
Reporter: He Ha Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:5.5.9 OS:Linux (Ubuntu 64 bit)
Assigned to: CPU Architecture:Any
Tags: alter, innodb

[7 May 2011 7:59] He Ha
Description:
My server has 4GB Ram. I used it for both mysql server and webserver using lighttpd. I have one table (gc_user_data) which is innodb engine and its size is 3GB. I cannot do anything with following action:
1. alter table by add more field in
2. I created `gc_user_data2` table with no data and could alter table gc_user_data2 as I want but when I do "INSERT INTO gc_user_data2 SELECT ... FROM gc_user_data", it failed.
3. UPDATE `gc_user_data` SET image_gauth = 'member'
I can do this following action normally
- UPDATE individual row with specified id

When I do one of these action, mysql stop itself and restart automatically without doing anything finish as expected. 
Here's my table structure, my.cnf config and log from mysql error log at that time as attached (110506 19:53:17 is alter table, 110506 20:01:02 is INSERT INTO, 110506 20:18:10 is UPDATE)

********** My table structure **********

CREATE TABLE IF NOT EXISTS `gc_user_data` (
  `user_id` bigint(20) NOT NULL DEFAULT '0',
  `fb_user_id` bigint(11) NOT NULL,
  `third_party_id` varchar(255) NOT NULL,
  `gauth` varchar(50) DEFAULT NULL,
  `deleted` enum('N','Y') DEFAULT 'N',
  `firstname` varchar(100) DEFAULT NULL,
  `lastname` varchar(100) DEFAULT NULL,
  `nick` varchar(50) DEFAULT NULL,
  `rank_name` varchar(50) NOT NULL DEFAULT 'ทาส',
  `rank_image` varchar(100) NOT NULL DEFAULT 'achievement/fame_0',
  `sex` enum('U','M','F') DEFAULT 'U',
  `image` text,
  `image_gauth` varchar(100) NOT NULL,
  `refer_user` varchar(50) DEFAULT NULL,
  `country` varchar(50) DEFAULT NULL,
  `class` int(11) DEFAULT '0',
  `farm_level` tinyint(4) DEFAULT '0',
  `time_premium_expired` datetime DEFAULT '0000-00-00 00:00:00',
  `status` enum('offline','online','ban','notactive') DEFAULT 'offline',
  `time_ban_expired` datetime DEFAULT '0000-00-00 00:00:00',
  `ban_reason` text,
  `money` int(11) DEFAULT '100',
  `point` int(11) DEFAULT '0',
  `point_all` int(11) NOT NULL,
  `wood` int(11) NOT NULL,
  `training` int(11) NOT NULL,
  `fame` float DEFAULT '0',
  `fame_lv` int(11) DEFAULT '0',
  `turn` int(11) DEFAULT '0',
  `turn_max` int(11) DEFAULT '0',
  `turn_regen` int(20) DEFAULT '0',
  `bonus` int(11) NOT NULL,
  `char_main_id` int(11) DEFAULT '0',
  `char_id` text,
  `boost` int(11) DEFAULT '0',
  `time_boost_expired` datetime DEFAULT '0000-00-00 00:00:00',
  `time_register` datetime DEFAULT '0000-00-00 00:00:00',
  `time_lastlogin` datetime DEFAULT '0000-00-00 00:00:00',
  `time_lastaccess` datetime DEFAULT '0000-00-00 00:00:00',
  `playedtime_all` int(11) DEFAULT '0',
  `playedtime_today` int(11) DEFAULT '0',
  `session_id` varchar(32) DEFAULT NULL,
  `session_expired` datetime DEFAULT '0000-00-00 00:00:00',
  `item` longtext,
  `guild_id` int(11) DEFAULT '0',
  `active_key` varchar(32) DEFAULT NULL,
  `bot_count` int(11) DEFAULT NULL,
  `bot_key` varchar(32) DEFAULT NULL,
  `decor_storage` longtext,
  `data_serialized` longtext,
  `emoticon_unlock` text NOT NULL,
  `comment_theme_unlock` text NOT NULL,
  `party_exp` int(11) NOT NULL,
  `party_lv` int(11) NOT NULL,
  `party_data` text NOT NULL COMMENT 'specie,specie,specie',
  `travel_serialized` text,
  `timezone` tinyint(4) NOT NULL,
  `email` varchar(50) NOT NULL,
  `time_last_profile_update` datetime NOT NULL,
  `locale` varchar(10) NOT NULL,
  `image_big` text NOT NULL,
  `birthday` varchar(15) NOT NULL,
  `others` text NOT NULL,
  `last_facebook_session` varchar(60) NOT NULL,
  `greeting_text` text NOT NULL,
  `friend_count` int(11) NOT NULL,
  `friend_invite_all` int(11) NOT NULL,
  `friend_invite_week` int(11) NOT NULL,
  `quest` text NOT NULL,
  `collection` text NOT NULL,
  `item2` text NOT NULL,
  `energy` int(11) NOT NULL DEFAULT '10',
  `energy_max` int(11) NOT NULL DEFAULT '10',
  `time_refill_energy` datetime NOT NULL,
  `badge` text NOT NULL,
  `fight_win_event` int(11) NOT NULL DEFAULT '0',
  `fight_win` int(11) NOT NULL,
  `fight_lose` int(11) NOT NULL,
  `is_gameindy` tinyint(1) NOT NULL DEFAULT '0',
  `is_fan` tinyint(1) NOT NULL DEFAULT '0',
  `is_invite` tinyint(1) NOT NULL DEFAULT '0',
  `is_publish_allow` tinyint(1) NOT NULL DEFAULT '0',
  `is_review` tinyint(1) NOT NULL DEFAULT '0',
  `is_migrate` tinyint(1) NOT NULL,
  PRIMARY KEY (`user_id`),
  KEY `farm_level` (`farm_level`),
  KEY `gauth` (`gauth`),
  KEY `time_register` (`time_register`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

********* Here's my.cnf config ***********

# The following options will be passed to all MySQL clients
[client]
#password       = your_password
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock
basedir         = /usr/local/mysql
datadir         = /usr/local/mysql/data
tmpdir          = /tmp
log_error       = /var/log/mysql/error.log
skip-external-locking
key_buffer_size = 32M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 32M
thread_cache_size = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin

# expire log
expire_logs_days=3

# binary logging format - mixed recommended
binlog_format=mixed

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id       = 1

# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin

# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /usr/local/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/data
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 384M
# 384
innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 96M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
#innodb_thread_concurrency = 8
innodb_flush_method=O_DSYNC
innodb_change_buffering=all
#innodb_file_per_table

[mysqldump]
quick
max_allowed_packet = 16M

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

[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

How to repeat:
I don't know if this event happen only to table that have 3GB or this structure only or not.
[7 May 2011 8:00] He Ha
My error log.

Attachment: error.log (application/octet-stream, text), 30.25 KiB.

[7 May 2011 8:03] He Ha
Insert data with only 1 row does not have a problem too.
[15 May 2011 14:28] Valeriy Kravchuk
You had got assertion failure during UPDATE:

110506 20:18:10  InnoDB: Assertion failure in thread 140690106472192 in file /export/home/pb2/build/sb_0-2859905-1295553452.13/mysql-5.5.9/storage/innobase/row/row0sel.c line 4465
InnoDB: Failing assertion: trx->isolation_level == TRX_ISO_READ_UNCOMMITTED
InnoDB: We intentionally generate a memory trap.

The code where assertion happened is the following: 

...
                        if (!row_sel_store_mysql_rec(buf, prebuilt, result_rec,
                                                     result_rec != rec,
                                                     offsets)) {
                                /* Only fresh inserts may contain
                                incomplete externally stored
                                columns. Pretend that such records do
                                not exist. Such records may only be
                                accessed at the READ UNCOMMITTED
                                isolation level or when rolling back a
                                recovered transaction. Rollback
                                happens at a lower level, not here. */
                                ut_a(trx->isolation_level
                                     == TRX_ISO_READ_UNCOMMITTED);
                                goto next_rec;
                        }
...

I wonder had you rolled back that failing INSERT ... SELECT statement?
[15 May 2011 15:53] He Ha
The mysql server crashed and auto re-start. It's do auto rollback by itself. For update, I do it in number 3 (update all row with no where cause). The log contained all 3 action log that cause the mysql server crashed. You can look at these time for each error:

110506 19:53:17 is alter table 
110506 20:01:02 is INSERT INTO 
110506 20:18:10 is UPDATE
[30 Jun 2011 15:32] He Ha
I found the problem now. It's problem with some text field in some row only. I cannot even 

SELECT text_field FROM table WHERE id=1

And Check Table command report is OK, nothing wrong which is very strange. 
Workaround for this problem is you must re-insert each row into new table with same structure one by one until you found the culprit row. You can also just select * each row one by one too. You can find the culprit this way as well. When you found all culprit rows, just re-insert into a new table without those rows and it's fixed.
[3 Aug 2011 0:34] MySQL Verification Team
Could you please upgrade and verify if still happen that behavior?. Thanks.
[3 Aug 2011 1:04] He Ha
I cannot verify that problem anymore because I have deleted the table that cause the problem already and re-create a new one with clean data.
[26 Aug 2011 12:55] MySQL Verification Team
Hi!

Please, upgrade to 5.5.15, and then try rebuild that table:
ALTER TABLE .. ENGINE=InnoDB;

See http://bugs.mysql.com/bug.php?id=62037 for reference...
[26 Aug 2011 13:05] He Ha
I have not upgrade yet and this problem do not happen anymore. Maybe it's just an accident of something?
[17 Jan 2012 20:17] Sveta Smirnova
Thank you for the feedback.

Closing as "Can't repeat" as we can not check if this is duplicate of bug #62037 or not.