The InnoDB Forums - InnoDB Plugin

Innodb plugin version (2 replies)

rmandba — Tue, 24 Aug 2010 05:59:29 +0300

I am new with Mysql.
I am using Mysql 5.1.48.
What is the latest innodb plugin version I can use?
Built-in Innodb or Plugin - which one is better to use in Production Databasee?

Regards.

1.0.7 + mysql 5.1.41? (4 replies)

abarringer — Wed, 25 Aug 2010 05:31:47 +0300

Is the innodb_plugin 1.07 compatible with mysql 5.1.41?

I'm using ubuntu 10.04 x64.

The current mysql package installed is "Server version: 5.1.41-3ubuntu12.3 (Ubuntu)"

Anyone try this yet?

building mysql visual studio ha_innodb_plugin error (no replies)

liteon — Fri, 30 Apr 2010 10:05:41 +0300

hi everyone

i try to build mysql 5.1.46 with microsoft visual studio 2008 on windows 7 professional (32 and 64 bits) edition
and it generate an error in the ha_innodb_plugin :

Compiling...
buf0rea.c
buf0lru.c
buf0flu.c
.\buf\buf0flu.c(160) : error C2143: syntax error : missing ';' before 'type'
.\buf\buf0flu.c(161) : error C2143: syntax error : missing ';' before 'const'
.\buf\buf0flu.c(169) : error C2065: 'b2' : undeclared identifier
.\buf\buf0flu.c(169) : error C2223: left of '->oldest_modification' must point to struct/union
.\buf\buf0flu.c(170) : error C2065: 'b1' : undeclared identifier
.\buf\buf0flu.c(170) : error C2223: left of '->oldest_modification' must point to struct/union
.\buf\buf0flu.c(174) : error C2065: 'b2' : undeclared identifier
.\buf\buf0flu.c(174) : error C2223: left of '->oldest_modification' must point to struct/union
.\buf\buf0flu.c(175) : error C2065: 'b1' : undeclared identifier
.\buf\buf0flu.c(175) : error C2223: left of '->oldest_modification' must point to struct/union
.\buf\buf0flu.c(180) : error C2065: 'b2' : undeclared identifier
.\buf\buf0flu.c(180) : error C2223: left of '->space' must point to struct/union
.\buf\buf0flu.c(180) : error C2065: 'b1' : undeclared identifier
.\buf\buf0flu.c(180) : error C2223: left of '->space' must point to struct/union
.\buf\buf0flu.c(183) : error C2065: 'b2' : undeclared identifier
.\buf\buf0flu.c(183) : error C2223: left of '->offset' must point to struct/union
.\buf\buf0flu.c(183) : error C2065: 'b1' : undeclared identifier
.\buf\buf0flu.c(183) : error C2223: left of '->offset' must point to struct/union
.\buf\buf0flu.c(183) : fatal error C1903: unable to recover from previous error(s); stopping compilation
buf0buf.c

everything works when i build without innodb et innodb-plugin, and i can't build innodb without innodb-plugin

does anyone have an solution or tried to build it on windows 7 ?
thanks.

innodb-plugin not working (8 replies)

vivekdeo — Mon, 30 Aug 2010 04:42:17 +0300

I have installed mysql 5.1.41 on linux suse 64 bit. I got the innodb plugin binary "mysql-5.1.41-linux-x86_64-glibc23.tar.gz"

After this I follwed the installation instruction. When try to start the mysq lgto below error:

100428 13:17:02 InnoDB: Starting shutdown...
100428 13:17:03 InnoDB: Shutdown completed; log sequence number 0 44233
100428 13:17:03 [Note] /usr/local/mysql/libexec/mysqld: Shutdown complete

100428 13:17:03 mysqld_safe mysqld from pid file /usr/local/mysql/var/db2-test.pid ended
100428 13:23:52 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/var
100428 13:23:52 [ERROR] Can't open shared library '/usr/local/mysql-5.1.41/lib/mysql/plugin/ha_innodb.so' (errno: 22 undefined symbol: my_pthread_fastmutex_init)
100428 13:23:52 [ERROR] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
100428 13:23:52 [ERROR] /usr/local/mysql/libexec/mysqld: unknown variable 'innodb_data_home_dir=/usr/local/mysql/var/'
100428 13:23:52 [ERROR] Aborting

Below is cnf file detail:
ignore_builtin_innodb
plugin-load=innodb=ha_innodb.so;innodb_trx=ha_innodb.so;
innodb_locks=ha_innodb.so;innodb_lock_waits=ha_innodb.so;
innodb_cmp=ha_innodb.so;innodb_cmp_reset=ha_innodb.so;
innodb_cmpmem=ha_innodb.so;innodb_cmpmem_reset=ha_innodb.so

Plugin is 1.6

mysql is installed as:
./configure --prefix=/usr/local/mysql --with-charset=utf8 --with-collation=utf8_general_ci \
--with-plugins=innobase,innodb_plugin

Innodb Row size too large (8 replies)

jayj — Thu, 22 Apr 2010 09:15:22 +0300

Hi,
I have a similar problem to [forums.innodb.com] However, my table seems to be using mostly TEXT and TINYTEXT columns. I'm using Innodb plugin 1.0.6 on MySQL 5.1.45 on Debian stable (backport of mysql and innodb plugin is from dotdeb.org).
Here's the table:

CREATE TABLE `exp_weblog_data` (
`entry_id` int(10) unsigned NOT NULL,
`site_id` int(4) unsigned NOT NULL DEFAULT '1',
`weblog_id` int(4) unsigned NOT NULL,
`field_id_1` text NOT NULL,
`field_ft_1` tinytext,
`field_id_2` text NOT NULL,
`field_ft_2` tinytext,
`field_id_3` text NOT NULL,
`field_ft_3` tinytext,
`field_id_4` text NOT NULL,
`field_ft_4` tinytext,
`field_id_5` text NOT NULL,
`field_ft_5` tinytext,
`field_id_6` text NOT NULL,
`field_ft_6` tinytext,
`field_id_7` text NOT NULL,
`field_ft_7` tinytext,
`field_id_8` text NOT NULL,
`field_ft_8` tinytext,
`field_id_9` text NOT NULL,
`field_ft_9` tinytext,
`field_id_10` text NOT NULL,
`field_ft_10` tinytext,
`field_id_12` text NOT NULL,
`field_ft_12` tinytext,
`field_id_13` text NOT NULL,
`field_ft_13` tinytext,
`field_id_15` text NOT NULL,
`field_ft_15` tinytext,
`field_id_16` text NOT NULL,
`field_ft_16` tinytext,
`field_id_17` text NOT NULL,
`field_ft_17` tinytext,
`field_id_18` text NOT NULL,
`field_ft_18` tinytext,
`field_id_19` text NOT NULL,
`field_ft_19` tinytext,
`field_id_20` text NOT NULL,
`field_ft_20` tinytext,
`field_id_21` text NOT NULL,
`field_ft_21` tinytext,
`field_id_22` text NOT NULL,
`field_ft_22` tinytext,
`field_id_23` text NOT NULL,
`field_ft_23` tinytext,
`field_id_24` text NOT NULL,
`field_ft_24` tinytext,
`field_id_25` text NOT NULL,
`field_ft_25` tinytext,
`field_id_26` text NOT NULL,
`field_ft_26` tinytext,
`field_id_27` text NOT NULL,
`field_ft_27` tinytext,
`field_id_28` text NOT NULL,
`field_ft_28` tinytext,
`field_id_29` text NOT NULL,
`field_ft_29` tinytext,
`field_id_30` text NOT NULL,
`field_ft_30` tinytext,
`field_id_31` text NOT NULL,
`field_ft_31` tinytext,
`field_id_32` text NOT NULL,
`field_ft_32` tinytext,
`field_id_33` text NOT NULL,
`field_ft_33` tinytext,
`field_id_34` text NOT NULL,
`field_ft_34` tinytext,
`field_id_35` text NOT NULL,
`field_ft_35` tinytext,
`field_id_36` text NOT NULL,
`field_ft_36` tinytext,
`field_id_37` text NOT NULL,
`field_ft_37` tinytext,
`field_id_38` text NOT NULL,
`field_ft_38` tinytext,
`field_id_39` text NOT NULL,
`field_ft_39` tinytext,
`field_id_40` text NOT NULL,
`field_ft_40` tinytext,
`field_id_41` text NOT NULL,
`field_ft_41` tinytext,
`field_id_42` text NOT NULL,
`field_ft_42` tinytext,
`field_id_43` text NOT NULL,
`field_ft_43` tinytext,
`field_id_44` text NOT NULL,
`field_ft_44` tinytext,
`field_id_45` text NOT NULL,
`field_ft_45` tinytext,
`field_id_46` text NOT NULL,
`field_ft_46` tinytext,
`field_id_47` text NOT NULL,
`field_ft_47` tinytext,
`field_id_48` text NOT NULL,
`field_ft_48` tinytext,
`field_id_49` text NOT NULL,
`field_ft_49` tinytext,
`field_id_50` text NOT NULL,
`field_ft_50` tinytext,
`field_id_51` text NOT NULL,
`field_ft_51` tinytext,
`field_id_52` text NOT NULL,
`field_ft_52` tinytext,
`field_id_53` text NOT NULL,
`field_ft_53` tinytext,
`field_id_54` text NOT NULL,
`field_ft_54` tinytext,
`field_id_55` text NOT NULL,
`field_ft_55` tinytext,
`field_id_56` text NOT NULL,
`field_ft_56` tinytext,
`field_id_57` text NOT NULL,
`field_ft_57` tinytext,
`field_id_58` text NOT NULL,
`field_ft_58` tinytext,
`field_id_59` text NOT NULL,
`field_ft_59` tinytext,
`field_id_60` text NOT NULL,
`field_ft_60` tinytext,
`field_id_61` text NOT NULL,
`field_ft_61` tinytext,
`field_id_62` text NOT NULL,
`field_ft_62` tinytext,
`field_id_63` text NOT NULL,
`field_ft_63` tinytext,
`field_id_64` text NOT NULL,
`field_ft_64` tinytext,
`field_id_65` text NOT NULL,
`field_ft_65` tinytext,
`field_id_66` text NOT NULL,
`field_ft_66` tinytext,
`field_id_67` text NOT NULL,
`field_ft_67` tinytext,
`field_id_68` text NOT NULL,
`field_ft_68` tinytext,
`field_id_69` text NOT NULL,
`field_ft_69` tinytext,
`field_id_70` text NOT NULL,
`field_ft_70` tinytext,
`field_id_71` text NOT NULL,
`field_ft_71` tinytext,
`field_id_72` text NOT NULL,
`field_ft_72` tinytext,
`field_id_73` text NOT NULL,
`field_ft_73` tinytext,
`field_id_74` text NOT NULL,
`field_ft_74` tinytext,
`field_id_75` text NOT NULL,
`field_ft_75` tinytext,
`field_id_76` text NOT NULL,
`field_ft_76` tinytext,
`field_id_77` text NOT NULL,
`field_ft_77` tinytext,
`field_id_78` text NOT NULL,
`field_ft_78` tinytext,
`field_id_79` text NOT NULL,
`field_ft_79` tinytext,
`field_id_80` text NOT NULL,
`field_ft_80` tinytext,
`field_id_81` text NOT NULL,
`field_ft_81` tinytext,
`field_id_82` text NOT NULL,
`field_ft_82` tinytext,
`field_id_83` text NOT NULL,
`field_ft_83` tinytext,
`field_id_84` text NOT NULL,
`field_ft_84` tinytext,
`field_id_85` text NOT NULL,
`field_ft_85` tinytext,
`field_id_86` text NOT NULL,
`field_ft_86` tinytext,
`field_id_87` text NOT NULL,
`field_ft_87` tinytext,
`field_id_88` text NOT NULL,
`field_ft_88` tinytext,
`field_id_89` text NOT NULL,
`field_ft_89` tinytext,
`field_id_90` text NOT NULL,
`field_ft_90` tinytext,
`field_id_91` text NOT NULL,
`field_ft_91` tinytext,
`field_id_92` text NOT NULL,
`field_ft_92` tinytext,
`field_id_93` text NOT NULL,
`field_ft_93` tinytext,
`field_id_94` text NOT NULL,
`field_ft_94` tinytext,
`field_id_95` text NOT NULL,
`field_ft_95` tinytext,
`field_id_96` text NOT NULL,
`field_ft_96` tinytext,
`field_id_97` text NOT NULL,
`field_ft_97` tinytext,
`field_id_98` text NOT NULL,
`field_ft_98` tinytext,
`field_id_99` text NOT NULL,
`field_ft_99` tinytext,
`field_id_100` text NOT NULL,
`field_ft_100` tinytext,
KEY `entry_id` (`entry_id`),
KEY `weblog_id` (`weblog_id`),
KEY `site_id` (`site_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

I, like the guy in the other post, hate wide tables too, but this is setup from Expression Engine. Any attempt to add more columns gives this:

MySQL ERROR:

Error Number: 1118

Description: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. You have to change some columns to TEXT or BLOBs

Query: ALTER TABLE exp_weblog_data ADD COLUMN field_id_103 text NOT NULL

I'm guessing this is a bug?

select * from INFORMATION_SCHEMA.tables where table_name='exp_weblog_data'\G
*************************** 1. row ***************************
TABLE_CATALOG: NULL
TABLE_SCHEMA: test
TABLE_NAME: exp_weblog_data
TABLE_TYPE: BASE TABLE
ENGINE: InnoDB
VERSION: 10
ROW_FORMAT: Compact
TABLE_ROWS: 278
AVG_ROW_LENGTH: 5716
DATA_LENGTH: 1589248
MAX_DATA_LENGTH: 0
INDEX_LENGTH: 49152
DATA_FREE: 4194304
AUTO_INCREMENT: NULL
CREATE_TIME: 2010-04-15 21:55:26
UPDATE_TIME: NULL
CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
CHECKSUM: NULL
CREATE_OPTIONS:
TABLE_COMMENT:
1 row in set (0.00 sec)

Mysql 5.1.41/Innodb 1.0.5 Server crashes (1 reply)

ajoshi — Mon, 12 Apr 2010 14:42:52 +0300

Hello,

We are seeing mysql 5.1.41 crash regularly (at least once a day) with seg 11. The trace varies. Any help in dealing with this will be appreciated. The installation is the standard MyQSL 5.1.41 binary tarball (not RPM) for an x86 machine. It is installed on a RHEL 4 update 5 machine as a general user.

Trace from the latest crash is below.

Thanks,

AJ

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...
stack_bottom = 0x1e8983a8 thread_stack 0x30000
/opt/mdeapp/lampstack-1.1-4/mysql/bin/mysqld.bin(my_print_stacktrace+0x21)[0x84dc0a1]
/opt/mdeapp/lampstack-1.1-4/mysql/bin/mysqld.bin(handle_segfault+0x381)[0x8203ad1]
/lib/tls/libpthread.so.0[0xa5b9c8]
/opt/mdeapp/lampstack-1.1-4/mysql/lib/plugin/ha_innodb_plugin.so[0x291991]
/opt/mdeapp/lampstack-1.1-4/mysql/lib/plugin/ha_innodb_plugin.so[0x292f00]
/opt/mdeapp/lampstack-1.1-4/mysql/lib/plugin/ha_innodb_plugin.so[0x29a858]
/opt/mdeapp/lampstack-1.1-4/mysql/lib/plugin/ha_innodb_plugin.so[0x25a8d5]
/opt/mdeapp/lampstack-1.1-4/mysql/lib/plugin/ha_innodb_plugin.so[0x2f69d0]
/opt/mdeapp/lampstack-1.1-4/mysql/lib/plugin/ha_innodb_plugin.so[0x2f7c75]
/opt/mdeapp/lampstack-1.1-4/mysql/lib/plugin/ha_innodb_plugin.so[0x2ffeb1]
/opt/mdeapp/lampstack-1.1-4/mysql/lib/plugin/ha_innodb_plugin.so[0x2aa77a]
/opt/mdeapp/lampstack-1.1-4/mysql/bin/mysqld.bin(_ZN7handler12ha_write_rowEPh+0x5b)[0x82f6b2b]
/opt/mdeapp/lampstack-1.1-4/mysql/bin/mysqld.bin(_Z12write_recordP3THDP8st_tableP12st_copy_info+0x5b)[0x828478b]
/opt/mdeapp/lampstack-1.1-4/mysql/bin/mysqld.bin(_Z12mysql_insertP3THDP10TABLE_LISTR4ListI4ItemERS3_IS5_ES6_S6_15enum_duplicatesb+0x10ea)[0x8288e
ca]
/opt/mdeapp/lampstack-1.1-4/mysql/bin/mysqld.bin(_Z21mysql_execute_commandP3THD+0x1b50)[0x82151b0]
/opt/mdeapp/lampstack-1.1-4/mysql/bin/mysqld.bin(_Z11mysql_parseP3THDPKcjPS2_+0x355)[0x821b045]
/opt/mdeapp/lampstack-1.1-4/mysql/bin/mysqld.bin(_Z16dispatch_command19enum_server_commandP3THDPcj+0x1228)[0x821c278]
/opt/mdeapp/lampstack-1.1-4/mysql/bin/mysqld.bin(_Z10do_commandP3THD+0xe0)[0x821cb20]
/opt/mdeapp/lampstack-1.1-4/mysql/bin/mysqld.bin(handle_one_connection+0x253)[0x820d413]
/lib/tls/libpthread.so.0[0xa553cc]
/lib/tls/libc.so.6(__clone+0x5e)[0x98fc3e]

replication lag (1 reply)

ramaki — Fri, 02 Apr 2010 09:38:44 +0300

Hello,

I am using mysql 5.1.43 with Innodb Plugin 1.0.6 with master slave setup based on RBR, I am using compression with key block size 8K as well as daily partitions. I am observing that slave continuously lags to master, even though it has same configuration h/w as well as sw. Does anybody noticed this behavior ?

Also I have another identical setup using MySql 5.1.30 with RBR for same set of data/table, but here no replication lag observed. Though the setup using plugin processes the writes/load 25% faster than one using 5.1.30 .
Any suggestions/comments welcome.

Inifite looping inside btr_page_split_and_insert (3 replies)

ryan.mack — Tue, 06 Apr 2010 19:16:34 +0300

During testing of 1.0.6 w/ 2x compression enabled for most tables in a 75GB (compressed) DB, I'm hitting a reproducible hang where and UPDATE statement is apparently looping indefinitely inside btr_page_split_and_insert. The buffer pool is about 60GB at this point, so it's possible it's just a super-linear algorithm that is blowing up with so many pages. The 10 minute semaphore timeout kills it before it finishes.

The current reproduction steps involves loading the DB and replaying a specific set of binlogs, which unfortunately takes about 3.5 hours in total. Fortunately it's 100% reproducible so I've been able to catch it in the debugger (and can do so again). I would be happy to provide any additional information. The server is running CentOS 5 on an 8 core (16 thread) Intel L5520.

I've attached a full callstack. I'll try to repro in an unoptimized build to provide the rest of the function params.

The update never returns from btr_page_split_and_insert(). It descends into page_move_rec_list_end() and then page_copy_rec_list_end_to_created_page() repeatedly.

Please let me know what additional information would be helpful.

innodb plugin 1.0.6 for 64 bits? (3 replies)

guto — Mon, 12 Apr 2010 14:45:22 +0300

Hi!
I could not find the 64-bit version of innodb plugin version 1.0.6 in the download section - is there any reason for that?

Thanks!

Guto.

Innosb plugin and custom innodb page size (3 replies)

ramaki — Tue, 30 Mar 2010 05:15:24 +0300

Hello,
I am new user to Innodb Plugin 1.0.6 built with MySql 5.1.43 on debian linux. Is it possible to modify the innodb page size from 16K to 32k or 64K in innodb plugin ? I try to increase the innodb page size to 64K, but run into following compilation problem .
======
1. Modify mysql-dfsg-5.1-5.1.43/storage/innodb_plugin/include/univ.i
{{{
#define UNIV_PAGE_SIZE_SHIFT 16
}}}

2. uname -a
Linux XXXXXXXX 2.6.32-admob #1 SMP Fri Dec 4 02:02:51 GMT 2009 x86_64 GNU/Linux
++++++++++++
sudo -H DEB_BUILD_OPTIONS=nocheck debuild -us -uc

nnodb_plugin_la-eval0proc.Tpo -c ../../../storage/innodb_plugin/eval/eval0proc.c -o ha_innodb_plugin_la-eval0proc.o >/dev/null 2>&1
libtool: compile: gcc -DHAVE_CONFIG_H -I. -I../../../storage/innodb_plugin -I../../include -I../../../include -I../../include -I../../../regex -I../../../storage/innodb_plugin/include -I../../../sql -I../../../storage/innodb_plugin -DMYSQL_DYNAMIC_PLUGIN -g -DSAFE_MUTEX -O3 -DBIG_JOINS=1 -fno-strict-aliasing -DUNIV_LINUX -DUNIV_LINUX -MT ha_innodb_plugin_la-eval0eval.lo -MD -MP -MF .deps/ha_innodb_plugin_la-eval0eval.Tpo -c ../../../storage/innodb_plugin/eval/eval0eval.c -o ha_innodb_plugin_la-eval0eval.o >/dev/null 2>&1
../../../storage/innodb_plugin/fsp/fsp0fsp.c:657:4: error: #error
../../../storage/innodb_plugin/fsp/fsp0fsp.c:661:4: error: #error
make[3]: *** [ha_innodb_plugin_la-fsp0fsp.lo] Error 1

CREATE/DROP INDEX copying to temp table despite InnoDB Plugin (2 replies)

Matthew Peach — Tue, 16 Mar 2010 10:04:08 +0200

Hello,

I've recently installed InnoDB plugin and was excited about the fast CREATE/DROP INDEX capabilities of it. My production database has one table in particular with over 20 million rows and almost 30 indexes, so this would be a great administration asset to me.

Unfortunately, I keep getting "copying to temp table" when I try to CREATE/DROP INDEX on the table leading me to believe that my table doesn't support the fast creation for some reason.

For the record, CREATE/DROP INDEX on other large tables works well and is very fast, so InnoDB Plugin is working, just not on this one table.

I'm running MySQL 5.1.37, InnoDB Plugin 1.0.4. ha_innodb.so appears in my list of plugins when I run SHOW PLUGINS.

Here's my table info: (column names changed for privacy)

CREATE TABLE `table_name` (
`id` int(10) unsigned NOT NULL DEFAULT '0',
`col1` tinyint(3) unsigned NOT NULL,
`col2` smallint(5) unsigned NOT NULL,
`col3` varchar(255) NOT NULL,
`col4` tinyint(4) unsigned NOT NULL DEFAULT '0',
`col5` tinyint(7) NOT NULL,
`col6` mediumint(8) unsigned NOT NULL,
`col7` tinyint(1) unsigned NOT NULL DEFAULT '1',
`col8` tinyint(1) unsigned NOT NULL,
`col9` decimal(8,2) unsigned NOT NULL,
`col10` mediumint(8) unsigned DEFAULT NULL,
`col11` tinyint(3) unsigned DEFAULT NULL,
`col12` tinyint(1) NOT NULL DEFAULT '1',
`col13` date DEFAULT NULL,
`col14` tinyint(1) unsigned NOT NULL,
`col15` tinyint(1) unsigned NOT NULL,
`col16` tinyint(1) unsigned NOT NULL,
`col17` tinyint(1) unsigned NOT NULL,
`col18` tinyint(1) unsigned NOT NULL DEFAULT '0',
`col19` tinyint(1) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`,`col1`,`col2`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY LIST (col1)
(PARTITION P1 VALUES IN (1) ENGINE = InnoDB,
PARTITION P2 VALUES IN (2) ENGINE = InnoDB);

Additionally there is 27 indexes, none of which are unique or foreign keys.

Here's the information_schema stuff for the table:

TABLE_CATALOG: NULL
TABLE_SCHEMA: database_name
TABLE_NAME: table_name
TABLE_TYPE: BASE TABLE
ENGINE: InnoDB
VERSION: 10
ROW_FORMAT: Compact
TABLE_ROWS: 22976594
AVG_ROW_LENGTH: 127
DATA_LENGTH: 2927624192
MAX_DATA_LENGTH: 0
INDEX_LENGTH: 24034508800
DATA_FREE: 30408704
AUTO_INCREMENT: NULL
CREATE_TIME: NULL
UPDATE_TIME: NULL
CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
CHECKSUM: NULL
CREATE_OPTIONS: partitioned
TABLE_COMMENT:

I'm guessing the problem is either the large number of indexes, the fact that the table is partitioned, or the large (22.9G) INDEX_LENGTH. (My innodb_buffer_pool_size is 30G, in case that's relevant.)

I can't see any problems that match up with the "Limitations" detailed by the InnoDB documentation.

Any ideas? :)

Innodb assertion failure in file lock0lock.c (4 replies)

Song — Tue, 16 Mar 2010 04:48:43 +0200

Hi All,

One db instance with v5.1.42, innodb plugin 1.0.6 enabled, crashed with assertion failure when running sysbench against it. It first happened with sysbench test mode being non-trx with update_key, test table 10M rows, running 256 threads. Consequent run of the same test didn't reproduce the failure. When changed test mode to non-trx with update_nokey, the first run caused the same assertion failure and consequent executions of the same test finished fine.

Error log recorded:
100223 12:11:59 InnoDB: Assertion failure in thread 141 in file lock/lock0lock.c line 1767
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to [bugs.mysql.com].
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: [dev.mysql.com]
InnoDB: about forcing recovery.
InnoDB: Thread 292 stopped in file sync/sync0arr.c line 167
InnoDB: Thread 254 stopped in file sync/sync0arr.c line 352
InnoDB: Thread 223 stopped in file handler/ha_innodb.cc line 5203
InnoDB: Thread 43 stopped in file handler/ha_innodb.cc line 5203
InnoDB: Thread 260 stopped in file handler/ha_innodb.cc line 5203
100223 12:11:59 - 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.
InnoDB: Thread 142 stopped in file handler/ha_innodb.cc line 5203
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=268435456
read_buffer_size=2097152
max_used_connections=301
max_threads=3000
threads_connected=256
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 12580261 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

InnoDB: Thread 285 stopped in file handler/ha_innodb.cc line 5203
InnoDB: Thread 42 stopped in file sync/sync0arr.c line 352
InnoDB: Thread 190 stopped in file sync/sync0arr.c line 352
InnoDB: Thread 162 stopped in file sync/sync0arr.c line 352
InnoDB: Thread 300 stopped in file sync/sync0arr.c line 352
InnoDB: Thread 296 stopped in file sync/sync0arr.c line 352
InnoDB: Thread 274 stopped in file sync/sync0arr.c line 352
InnoDB: Thread 158 stopped in file sync/sync0arr.c line 352
100223 12:12:05 mysqld_safe mysqld restarted
100223 12:12:05 [Note] Plugin 'FEDERATED' is disabled.
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use Solaris atomic functions
100223 12:12:11 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 20085684347
100223 12:12:14 InnoDB: Database was not shut down normally!

The second crash happened with similar error output:

100223 13:39:35 InnoDB: Assertion failure in thread 226 in file lock/lock0lock.c line 1767

while many other threads stopped at the following two places:
InnoDB: Thread 69 stopped in file sync/sync0arr.c line 352
...
InnoDB: Thread 247 stopped in file handler/ha_inn odb.cc line 5203
...

I looked up the source code, but not savvy enough to interpret it:

lock/lock0lock.c line 1767

lock_rec_enqueue_waiting(
/* Test if there already is some other reason to suspend thread:
we do not enqueue a lock request if the query thread should be
stopped anyway */

if (UNIV_UNLIKELY(que_thr_stop(thr))) {

ut_error;

return(DB_QUE_THR_SUSPENDED);
}

sync/sync0arr.c line 352
sync_array_reserve_cell(

ut_a(object);

handler/ha_innodb.cc line 5203
ha_innobase::change_active_index(

ut_a(prebuilt->trx == thd_to_trx(user_thd));

Looks like innodb find a reason to suspend the thread that requesting a lock, but should that result in a crash? Btw, where is the ut_a function defined? curious of what it does...

Thank you in advance for any comments!

winXP, mysql 5.1.42 and innodb plugin-1.0.6 API is too different (1 reply)

GrayMan — Wed, 03 Mar 2010 23:28:57 +0200

100219 15:25:50 [ERROR] Can't open shared library 'ha_innodb.dll' (errno: 0 API version for STORAGE ENGINE plugin is too different)

Why I get this error message? I am using mysql 5.1.42-community and innodb plugin-1.0.6 on WinXP x86.

my_pthread_fastmutex_lock undefined on mysql 5.1.42 startup (2 replies)

goodgulf — Mon, 15 Feb 2010 14:44:22 +0200

If this is the wrong forum; I apologize.
I am trying to upgrade the RedHat linux systems from the agonizingly old versions of software that's installed on them.
I'm interested in getting away from the MyISAM Engine and looking into InnoDB.

I installed ha_innodb.so 1.0.6 in /usr/lib/mysql/plugin and added the lines to use it to my /etc/my.cnf.

Linux RHEL4 Update 8 (GLIBC 2.3)

MySQL was not compiled but loaded from the rpm's
Installed packages...

MySQL-client-community-5.1.42-0.rhel4
MySQL-devel-community-5.1.42-0.rhel4
MySQL-server-community-5.1.42-0.rhel4
MySQL-shared-compat-5.1.42-0.rhel4

I get the following error in my log file...

100210 19:20:08 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
100210 19:20:09 [Note] Plugin 'FEDERATED' is disabled.
100210 19:20:09 [ERROR] Can't open shared library '/usr/lib/mysql/plugin/ha_innodb.so' (errno: 0 undefined symbol: my_pthread_fastmutex_lock)
100210 19:20:09 [ERROR] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
100210 19:20:09 [Note] Event Scheduler: Loaded 0 events
100210 19:20:09 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.42-community' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)

Am I missing a library somewhere?

Unceremonious hang in innodb plugin 1.0.6 + mysql 5.1.42 during ALTER (15 replies)

dormando — Mon, 30 Aug 2010 04:41:19 +0300

Hello! I tried out MySQL 5.1 + innodb plugin for the first time today. It didn't go so well.

I took a slave database that was on 5.0.67 and upgraded it to 5.1.42 + innodb plugin 1.0.6. The plugin came with 5.1.42, which was the 64bit binary download from mysql.com (the tarball, if it matters). Ran mysql_upgrade to check all the tables/upgrade the mysql database. That went fine. Replication worked fine. Selects/etc worked fine.

At this point all of the tables in the DB are of the A format, and I started ALTER'ing a 65G table with heavy blob usage (122 million rows), with:
ALTER TABLE XXXXXXX ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16

After a few minutes I kill'ed the ALTER since I needed to adjust the redo log settings. Doing that hung innodb... no other query would run. I kill -9'ed the server, let it recover, and had a dangling tablespace from the temp table for the ALTER. I'll get more into this detail shortly.

Then I ALTER'ed the table again, same exact command. This worked fine and two hours later produced a 30G table.

Then I tried ALTER'ing it again, this time with:
ALTER TABLE XXXXXXX ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
... to see if block size 8 would do any better.

This ran for an hour, producing a 15G table. Then InnoDB hung again.
| 4 | root | localhost | XXXXXXXXXXXXXXXX | Query | 5835 | rename result table | ALTER TABLE XXXXXXXXXXXX ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8

... a REPLACE INTO for a heartbeat query was running at the same time, in state 'Opening Tables' and hung.
Then a few selects ran for that heartbeat table, all hung under "Opening Tables". I ran 'SHOW ENGINE INNODB STATUS', which hung as well, with a NULL state. I left the system in this state for 30 minutes just in case it'd recover on its own, without luck. None of the queries were killable.

This whole time one of the MySQL threads (likely the one doing the ALTER) is pegging CPU at 100%. No disk IO.

I ran the poor man's profiler on the instance to get all of the thread stacks:

10 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,os_aio_simulated_handle,fil_aio_wait,io_handler_thread,start_thread,clone
X 6 __lll_mutex_lock_wait,_L_mutex_lock_1133,pthread_mutex_lock,open_table,open_tables,open_and_lock_tables_derived,execute_sqlcom_select,mysql_execute_command,mysql_parse,dispatch_command,do_command,handle_one_connection,start_thread,clone
1 select,os_thread_sleep,srv_lock_timeout_and_monitor_thread,start_thread,clone
1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,sync_array_wait_event,mutex_spin_wait,buf_print_io,srv_printf_innodb_monitor,innodb_show_status,ha_show_status,mysql_execute_command,mysql_parse,dispatch_command,do_command,handle_one_connection,start_thread,clone
1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,sync_array_wait_event,mutex_spin_wait,buf_LRU_stat_update,srv_error_monitor_thread,start_thread,clone
1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,sync_array_wait_event,mutex_spin_wait,buf_get_modified_ratio_pct,srv_master_thread,start_thread,clone
1 pthread_cond_wait@@GLIBC_2.3.2,main,pthread_cond_wait@@GLIBC_2.3.2
1 pthread_cond_wait@@GLIBC_2.3.2,kill_server,kill_server_thread,start_thread,clone
X 1 __lll_mutex_lock_wait,_L_mutex_lock_1133,pthread_mutex_lock,open_table,open_tables,open_and_lock_tables_derived,mysql_insert,mysql_execute_command,mysql_parse,dispatch_command,do_command,handle_one_connection,start_thread,clone
1 do_sigwait,sigwait,signal_hand,start_thread,clone
X 1 at,fil_delete_tablespace,row_drop_table_for_mysql,ha_innodb::delete_table,ha_delete_table,quick_rm_table,mysql_alter_table,mysql_execute_command,mysql_parse,dispatch_command,do_command,handle_one_connection,start_thread,clone

Ones I prefixed with an X are of note. 6 hung running SELECT's, 1 hung doing an INSERT (the REPLACE INTO), 1 one hung doing the drop tablespace. Inspecting the files on disk back this up, since the temporary table had been renamed to the final, and the old table was renamed to an #sql2- table. So at this point it *just* had to drop the old tablespace.

After kill -9'ing mysql:

InnoDB: Apply batch completed
InnoDB: In a MySQL replication slave the last master binlog file
InnoDB: position 0 1043639133, file name binlog.000652
InnoDB: Last MySQL binlog file position 0 3628710, file name
XXXXXXXXXXX/binlog.000609
100115 22:26:29 InnoDB: Rolling back trx with id 46771DC1F, 14 rows to undo
InnoDB: Dropping table with id 0 392 in recovery if it exists
InnoDB: Error: trying to load index PRIMARY for table
XXXXXXXXXXXXXXXXXXX/#sql2-1798-4
InnoDB: but the index tree has been freed!

InnoDB: Rolling back of trx id 46771DC1F completed
100115 22:26:29 InnoDB Plugin 1.0.6 started; log sequence number 1072760499062

... never seen that 'trying to load index' error before. None of the normal tricks worked for removing that temp table.

For the other file that died, I tried adding the frm back, but:
InnoDB: Error: trying to load index PRIMARY for table
archetype_partition1_comet/#sql-7c21_14
InnoDB: but the index tree has been freed!
100115 17:24:18 [ERROR] Cannot find or open table
archetype_partition1_comet/#sql-7c21_14 from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See [dev.mysql.com]
how you can resolve the problem.

... no dice.

With the .frm, it thinks the table doesn't exist. Without the .frm, it thinks the table exists and I'm a moron who didn't drop it correctly.

Sorry for how long this is. I wanted to be thorough since I'm not sure if the second hang was caused by me killing the first and having a corrupt table in the tablespace. It seems highly unlikely since between the first hang and the second hang I was able to successfully ALTER the table.

The stack trace looks good though, and both hangs are likely in the exact same code, though I didn't get a stack dump the first time.

Any way to set compressed table as the default? (1 reply)

Andy — Mon, 11 Jan 2010 12:24:38 +0200

Is there any way to set (e.g. in my.cnf) as default something like:
ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;

so that all tables created will be compressed by default?

I'm using Django and all tables are created by Django & its various pluggables. It'd be a pain to go into every single created table to do an ALTER TABLE to enable compression. If there's a way to set compression as the default, then all tables created by Django will automatically be compressed.

Decreasing compressed block size increases data_length, but decreases on-disk file size (1 reply)

neilf — Mon, 11 Jan 2010 12:18:42 +0200

For a compressed InnoDB table, when I decrease the key_block_size the data_length (as reported by "show table status") increases. However, the size of the table's file on disk decreases (I'm using innodb_file_per_table). Does anyone know why this might be? I've included the various sizes below. I'm using MySQL 5.1.41 with the version of the InnoDB plugin that comes with that distribution.

Block Size 16

Database:

+-----------------------------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+-----------------------------------------+---------+
| 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 |
+-----------------------------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+-----------------------------------------+---------+
| mytable                           | InnoDB |      10 | Compressed | 186169 |             65 |    12124160 |               0 |            0 |   4194304 |           NULL | 2010-01-07 16:37:36 | NULL        | NULL       | utf8_general_ci |     NULL | row_format=COMPRESSED KEY_BLOCK_SIZE=16 |         |
+-----------------------------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+-----------------------------------------+---------+

File:

-rw-rw---- 1 mysql mysql 19M 2010-01-07 17:02 mytable.ibd

Block Size 8

Database:

+-----------------------------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------------------------------+---------+
| 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 |
+-----------------------------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------------------------------+---------+
| mytable                           | InnoDB |      10 | Compressed | 186169 |             65 |    12124160 |               0 |            0 |   2621440 |           NULL | 2010-01-07 16:39:01 | NULL        | NULL       | utf8_general_ci |     NULL | row_format=COMPRESSED KEY_BLOCK_SIZE=8 |         |
+-----------------------------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------------------------------+---------+

File:

-rw-rw---- 1 mysql mysql 10M 2010-01-07 17:04 mytable.ibd

Block Size 4

Database:

+-----------------------------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------------------------------+---------+
| 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 |
+-----------------------------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------------------------------+---------+
| mytable                           | InnoDB |      10 | Compressed | 186766 |             98 |    18448384 |               0 |            0 |   1835008 |           NULL | 2010-01-07 16:40:14 | NULL        | NULL       | utf8_general_ci |     NULL | row_format=COMPRESSED KEY_BLOCK_SIZE=4 |         |
+-----------------------------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------------------------------+---------+

File:

-rw-rw---- 1 mysql mysql 7.0M 2010-01-07 17:04 mytable.ibd

Block Size 2

Database:

+-----------------------------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------------------------------+---------+
| 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 |
+-----------------------------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------------------------------+---------+
| mytable                           | InnoDB |      10 | Compressed | 183155 |            210 |    38600704 |               0 |            0 |    917504 |           NULL | 2010-01-07 16:42:14 | NULL        | NULL       | utf8_general_ci |     NULL | row_format=COMPRESSED KEY_BLOCK_SIZE=2 |         |
+-----------------------------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------------------------------+---------+

File:

-rw-rw---- 1 mysql mysql 6.0M 2010-01-07 16:42 mytable.ibd

InnoDB Plugin - patch coding / increment of pointer to unknown structure (no replies)

RootUser — Tue, 05 Jan 2010 15:31:51 +0200

Hi,

I'm trying to patch InnoDB Plugin to dump the buffer pool to disk before shutdown and reload it from the file on startup.

I have some kind of problem while referencing the buf_pool. The code is as follows:

chunk = buf_pool->chunks;
for(n_chunks = buf_pool->n_chunks; n_chunks--; chunk++)

This part generates a compilation error telling me the following:
srv/srv0start.c:2085: error: increment of pointer to unknown structure

Does anyone have an idea how to fix this?

:wr
:q

Mac OS X Snow Leopard (no replies)

ChopperPhil — Wed, 16 Dec 2009 05:49:50 +0200

Is there a download of the InnoDB plugin that works with Snow Leopard?

Thanks

MySQL 5.1.41 and plugin 1.0.6 signal 11 (6 replies)

MikeGrafton — Fri, 04 Dec 2009 17:46:52 +0200

New install of 5.1.41 on Red Hat 5 with InnoDB plugin 1.0.6 throws signal 11 and crashes (and restarts) as soon as an InnoDB table is created or subsequently accessed.

091203 21:23:31 - 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=8384512
read_buffer_size=262144
max_used_connections=1
max_threads=300
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 164839 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x210a8c20
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...
stack_bottom = 0x40526100 thread_stack 0x40000
/opt/mysql/5.1.41/bin/mysqld(my_print_stacktrace+0x2e)[0x8aaece]
/opt/mysql/5.1.41/bin/mysqld(handle_segfault+0x322)[0x5dfbe2]
/lib64/libpthread.so.0[0x30c120de70]
/opt/mysql/5.1.41/lib/plugin/ha_innodb.so[0x2aaaab31c1d3]
/opt/mysql/5.1.41/lib/plugin/ha_innodb.so[0x2aaaab323026]
/opt/mysql/5.1.41/bin/mysqld(_ZN7handler7ha_openEP8st_tablePKcii+0x3f)[0x6cd3ef]
/opt/mysql/5.1.41/bin/mysqld(_Z21open_table_from_shareP3THDP14st_table_sharePKcjjjP8st_tableb+0x56a)[0x6300fa]
/opt/mysql/5.1.41/bin/mysqld[0x629e8b]
/opt/mysql/5.1.41/bin/mysqld(_Z10open_tableP3THDP10TABLE_LISTP11st_mem_rootPbj+0x58f)[0x62acaf]
/opt/mysql/5.1.41/bin/mysqld(_Z11open_tablesP3THDPP10TABLE_LISTPjj+0x6a9)[0x62bb29]
/opt/mysql/5.1.41/bin/mysqld(_Z30open_normal_and_derived_tablesP3THDP10TABLE_LISTj+0x1e)[0x62bc9e]
/opt/mysql/5.1.41/bin/mysqld(_Z18mysqld_list_fieldsP3THDP10TABLE_LISTPKc+0x22)[0x6fe182]
/opt/mysql/5.1.41/bin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0xc9a)[0x5f666a]
/opt/mysql/5.1.41/bin/mysqld(_Z10do_commandP3THD+0xe6)[0x5f7176]
/opt/mysql/5.1.41/bin/mysqld(handle_one_connection+0x246)[0x5e9ad6]
/lib64/libpthread.so.0[0x30c12062f7]
/lib64/libc.so.6(clone+0x6d)[0x30c06d1b6d]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x21101620 =
thd->thread_id=1
thd->killed=NOT_KILLED
The manual page at [dev.mysql.com] contains
information that should help you find out what is causing the crash.
091203 21:23:31 mysqld_safe Number of processes running now: 0
091203 21:23:31 mysqld_safe mysqld restarted
091203 21:23:31 [Warning] Could not increase number of max_open_files to more than 1024 (request: 1310)
091203 21:23:31 [Note] Plugin 'FEDERATED' is disabled.
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
091203 21:23:32 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
091203 21:23:33 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...
091203 21:23:34 InnoDB Plugin 1.0.6 started; log sequence number 44279
091203 21:23:34 [Note] Recovering after a crash using /opt/mysql/logs/cds-binlog
091203 21:23:34 [Note] Starting crash recovery...
091203 21:23:34 [Note] Crash recovery finished.
091203 21:23:34 [Note] Event Scheduler: Loaded 0 events
091203 21:23:34 [Note] /opt/mysql/5.1.41/bin/mysqld: ready for connections.
Version: '5.1.41-log' socket: '/opt/mysql/data01/mysql.sock' port: 3306 MySQL Community Server (GPL)

Dynamic innodb_buffer_pool_size (no replies)

SaveTheRbtz — Mon, 16 Nov 2009 15:12:24 +0200

Is there any planing functionality for ability of dynamically change innodb_buffer_pool_size without server restart?

bugfix (no replies)

knielsen — Sat, 14 Nov 2009 01:00:49 +0200

I don't know if the innodb plugin developers follow XtraDB development?

If not, it might be worth it to check out this bug:

[bugs.launchpad.net]

I think it is present also in unmodified innodb plugin.

Regression on ALTER TABLE found for InnoDB plugin 1.0.4 (4 replies)

jaypipes — Fri, 16 Oct 2009 22:35:25 +0300

Hi all!

In the Drizzle project, we run a series of automated benchmarks with every commit. Recently, when merging in the latest InnoDB plugin 1.0.4, we've discovered an apparent significant regression in the performance of ALTER TABLE:

[lists.launchpad.net]

r1183 is the revision where we merged in the new InnoDB plugin:

[bazaar.launchpad.net]

As you can see from the benchmark results, we see a large dropoff in the alter_table_add and alter_table_drop benchmark tests (from the sqlbench suite, BTW)

For the record, we see some very nice improvements in other benchmark areas though! :) However, we wanted to alert you to our findings and see if you had a comment on it? Perhaps we have a poorly-set configuration variable in the handler? Any assistance would be most appreciated!

Thanks!

jay

how to do backup (full and incremental) (1 reply)

Manfred — Tue, 20 Oct 2009 09:10:05 +0300

Hello,

Given the "Barracuda" file format I'm wondering how to do full and incremental backup.
Given the documentation that says that innodb logs are differents from normal innodb engine and since is incompatible with innodb hot copy 3.0, I was thinking of using zrm.
Still zrm relies on logs (binary probably), so it may not well handle the new log format.

Does the compression occurs also for the bin log ? (I guess no ?)

Regards,
Manfred

Max row size with plugin (3 replies)

ytrudeau — Mon, 19 Oct 2009 22:47:30 +0300

Hi,
I recently encountered an unexpected behavior with the InnoDB Plugin. Before, I was able to create table with schema potentially much larger than 8k/row (no blob) but now, I receive the following error:

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. You have to change some columns to TEXT or BLOBs"

Is this the new expected behavior? If so, it is a big change compare to the "classic" InnoDB engine and it should be clearly documented.

Regards,

Yves

mysql 5.1.38 and plugin 1.0.4 - signal 11 (12 replies)

jgrasse — Thu, 05 Nov 2009 23:32:02 +0200

I am getting a signal 11 error when trying to run 'make test' after compiling mysql 5.1.38. I thought it might be because of Bug# 46977 at first, but then noticed the backtrace is different as well as the error number. Error from mysql log is as follows.

090908 18:45:13 - 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=1048576
read_buffer_size=131072
max_used_connections=0
max_threads=151
threads_connected=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 59969 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x0
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...
stack_bottom = (nil) thread_stack 0x30000
//mysql-5.1.38-i686-default/sql/mysqld(my_print_stacktrace+0x21)[0x84c1c78]
//mysql-5.1.38-i686-default/sql/mysqld(handle_segfault+0x36e)[0x81f39ae]
/lib/tls/libpthread.so.0[0xa00a98]
//mysql-5.1.38-i686-default/storage/innodb_plugin/.libs/ha_innodb_plugin.so[0x438106]
//mysql-5.1.38-i686-default/sql/mysqld(_Z24ha_initialize_handlertonP13st_plugin_int+0x132)[0x82e5178]
//mysql-5.1.38-i686-default/sql/mysqld[0x836d098]
//mysql-5.1.38-i686-default/sql/mysqld(_Z11plugin_initPiPPci+0x519)[0x8370ef7]
//mysql-5.1.38-i686-default/sql/mysqld[0x81f568f]
//mysql-5.1.38-i686-default/sql/mysqld(main+0x5d1)[0x81f947f]
/lib/tls/libc.so.6(__libc_start_main+0xd3)[0x89bdf3]
//mysql-5.1.38-i686-default/sql/mysqld[0x8138ec1]

CentOS release 4.7
i686 platform
libc 2.3

objdump -h ha_innodb_plugin.so output:
ha_innodb_plugin.so: file format elf32-i386

Sections:
Idx Name          Size      VMA       LMA       File off  Algn
  0 .hash         00002b34  000000d4  000000d4  000000d4  2**2
                  CONTENTS, ALLOC, LOAD, READONLY, DATA
  1 .dynsym       00006c40  00002c08  00002c08  00002c08  2**2
                  CONTENTS, ALLOC, LOAD, READONLY, DATA
  2 .dynstr       00009a6f  00009848  00009848  00009848  2**0
                  CONTENTS, ALLOC, LOAD, READONLY, DATA
  3 .gnu.version  00000d88  000132b8  000132b8  000132b8  2**1
                  CONTENTS, ALLOC, LOAD, READONLY, DATA
  4 .gnu.version_r 000000b0  00014040  00014040  00014040  2**2
                  CONTENTS, ALLOC, LOAD, READONLY, DATA
  5 .rel.dyn      00001bc8  000140f0  000140f0  000140f0  2**2
                  CONTENTS, ALLOC, LOAD, READONLY, DATA
  6 .rel.plt      00002548  00015cb8  00015cb8  00015cb8  2**2
                  CONTENTS, ALLOC, LOAD, READONLY, DATA
  7 .init         00000017  00018200  00018200  00018200  2**2
                  CONTENTS, ALLOC, LOAD, READONLY, CODE
  8 .plt          00004aa0  00018218  00018218  00018218  2**2
                  CONTENTS, ALLOC, LOAD, READONLY, CODE
  9 .text         000f7b64  0001ccc0  0001ccc0  0001ccc0  2**4
                  CONTENTS, ALLOC, LOAD, READONLY, CODE
 10 .fini         0000001a  00114824  00114824  00114824  2**2
                  CONTENTS, ALLOC, LOAD, READONLY, CODE
 11 .rodata       00021340  00114840  00114840  00114840  2**5
                  CONTENTS, ALLOC, LOAD, READONLY, DATA
 12 .eh_frame_hdr 0000002c  00135b80  00135b80  00135b80  2**2
                  CONTENTS, ALLOC, LOAD, READONLY, DATA
 13 .eh_frame     0000009c  00135bac  00135bac  00135bac  2**2
                  CONTENTS, ALLOC, LOAD, READONLY, DATA
 14 .ctors        0000000c  00136000  00136000  00136000  2**2
                  CONTENTS, ALLOC, LOAD, DATA
 15 .dtors        00000008  0013600c  0013600c  0013600c  2**2
                  CONTENTS, ALLOC, LOAD, DATA
 16 .jcr          00000004  00136014  00136014  00136014  2**2
                  CONTENTS, ALLOC, LOAD, DATA
 17 .dynamic      000000e8  00136018  00136018  00136018  2**2
                  CONTENTS, ALLOC, LOAD, DATA
 18 .got          000004ac  00136100  00136100  00136100  2**2
                  CONTENTS, ALLOC, LOAD, DATA
 19 .got.plt      000012b0  001365ac  001365ac  001365ac  2**2
                  CONTENTS, ALLOC, LOAD, DATA
 20 .data         000018ac  00137860  00137860  00137860  2**5
                  CONTENTS, ALLOC, LOAD, DATA
 21 .bss          000035a0  00139120  00139120  0013910c  2**5
                  ALLOC
 22 .comment      000011f8  00000000  00000000  0013910c  2**0
                  CONTENTS, READONLY

I also got this same problem when compiling mysql 5.1.37 and replacing the storage/innobase directory with the downloaded innodb 1.0.4 source.

Is this related to Bug# 46977, or is this something different?

InnoDB 1.0.4 plugin in mysql 5.1.37 and google patch for SMP scaling (1 reply)

esorek — Wed, 30 Sep 2009 14:53:37 +0300

Hi,

Is InnoDB plugin 1.0.4 inmysql 5.1.37 includes Google patch for SMP scaling on multi-core machines ?
Any other scaling features in contains for multi-core machines ?

Thanks,
Eyal

upgrading to 1.0.4 and mysql 5.1.37 (5 replies)

joep — Sat, 05 Sep 2009 01:31:45 +0300

Looking for suggestions on what I did wrong on an update from 5.1.30 and plugin 1.0.3 to 5.1.37 and 1.0.4.

Did the slow shutdown, symlinked new version of mysql with the new plugin in the lib/plugin dir and added the new options to my.cnf.
ignore_builtin_innodb
plugin-load=innodb=ha_innodb.so;innodb_trx=ha_innodb.so;innodb_locks=ha_innodb.so;innodb_lock_waits=ha_innodb.so;innodb_cmp=ha_innodb.so;innodb_cmp_reset=ha_innodb.so;innodb_cmpmem=ha_innodb.so;innodb_cmpmem_reset=ha_innodb.so

After that MySQL failed to start

090813 10:59:10 mysqld_safe Starting mysqld daemon with databases from /db/mysql
090813 10:59:10 [Note] Plugin 'FEDERATED' is disabled.
090813 10:59:10 - mysqld got signal 8 ;
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=536870912
read_buffer_size=262144
max_used_connections=0
max_threads=500
threads_connected=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 913381 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x0
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...
stack_bottom = (nil) thread_stack 0x40000
/usr/local/mysql/bin/mysqld(my_print_stacktrace+0x2e)[0x8ab3be]
/usr/local/mysql/bin/mysqld(handle_segfault+0x322)[0x5deb82]
/lib/tls/libpthread.so.0[0x2ae62531c270]
/lib64/ld-linux-x86-64.so.2[0x2ae625200ef0]
/lib64/ld-linux-x86-64.so.2[0x2ae6252012d1]
/lib64/ld-linux-x86-64.so.2[0x2ae62520258f]
/lib/tls/libc.so.6[0x2ae6259df450]
/lib64/ld-linux-x86-64.so.2[0x2ae625204470]
/lib/tls/libc.so.6(_dl_open+0xaa)[0x2ae6259dfb7a]
/lib/libdl.so.2[0x2ae6254260b4]
/lib64/ld-linux-x86-64.so.2[0x2ae625204470]
/lib/libdl.so.2[0x2ae625426593]
/lib/libdl.so.2(dlopen+0x32)[0x2ae6254260f2]
/usr/local/mysql/bin/mysqld[0x74f50c]
/usr/local/mysql/bin/mysqld[0x750ccf]
/usr/local/mysql/bin/mysqld(_Z11plugin_initPiPPci+0x60f)[0x75283f]
/usr/local/mysql/bin/mysqld[0x5df355]
/usr/local/mysql/bin/mysqld(main+0x1cc)[0x5e360c]
/lib/tls/libc.so.6(__libc_start_main+0xe4)[0x2ae625916674]
/usr/local/mysql/bin/mysqld(fmod+0x62)[0x51331a]

InnoDB Plugin Version 1.0.4 for MySQL 5.1.37 Released (no replies)

marko — Tue, 11 Aug 2009 12:38:08 +0300

The InnoDB team is pleased to announce the availability of InnoDB Plugin release 1.0.4, which brings additional performance gains of 20% or more!

This latest early adopter version of the InnoDB Plugin introduces bug fixes and additional new features, further enhancing InnoDB performance, scalability, reliability, and ease of use. We are especially pleased that this release includes several significant community contributions. We are grateful to Sun Microsystems, Google and Percona Inc., for their contributions.

Please see the announcement and download the newest InnoDB Plugin.

Please report your comments and any problems or issues with the InnoDB Plugin in the InnoDB Forums or in the MySQL bug database.

Thank you, and enjoy!

Marko Mäkelä
Software Engineer
Innobase Oy/Oracle Corp.

Query optimizer going horribly wrong (no replies)

wr0ngway — Fri, 07 Aug 2009 18:32:34 +0300

I have a simple query that only uses its index *sometimes*. The desired query is the first one I explain below. The strange thing is that the second query below uses the right index when I use a different value for user_id. The third query also uses an index if I remove the order by. I could always force the index, but certainly with such a simple query I shouldn't have to. This is with mysql-5.1.36-xtradb6 (and thus innodb plugin 1.0.3). I tried rebuilding the table, and it just shifted this behavior to different values for user_id. Anyone have any ideas or tips on how else I can debug this?

mysql> explain SELECT SQL_NO_CACHE * FROM pictures WHERE pictures.user_id = 1262761 ORDER BY pictures.id\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: pictures
type: index
possible_keys: index_pictures_on_user_id,index_pictures_on_user_id_and_is_private
key: PRIMARY
key_len: 4
ref: NULL
rows: 6161948
Extra: Using where
1 row in set (0.00 sec)

mysql> explain SELECT SQL_NO_CACHE * FROM pictures WHERE pictures.user_id = 7 ORDER BY pictures.id\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: pictures
type: ref
possible_keys: index_pictures_on_user_id,index_pictures_on_user_id_and_is_private
key: index_pictures_on_user_id
key_len: 5
ref: const
rows: 302
Extra: Using where
1 row in set (0.00 sec)

mysql> explain SELECT SQL_NO_CACHE * FROM pictures WHERE pictures.user_id = 1262761\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: pictures
type: ref
possible_keys: index_pictures_on_user_id,index_pictures_on_user_id_and_is_private
key: index_pictures_on_user_id_and_is_private
key_len: 5
ref: const
rows: 6
Extra: Using where
1 row in set (0.00 sec)

mysql> show create table pictures\G
*************************** 1. row ***************************
Table: pictures
Create Table: CREATE TABLE `pictures` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) DEFAULT NULL,
`user_id` int(11) DEFAULT NULL,
`parent_id` int(11) DEFAULT NULL,
`content_type` varchar(255) DEFAULT NULL,
`filename` varchar(255) DEFAULT NULL,
`thumbnail` varchar(255) DEFAULT NULL,
`size` int(11) DEFAULT NULL,
`width` int(11) DEFAULT NULL,
`height` int(11) DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`is_private` tinyint(1) DEFAULT '0',
`description` varchar(255) DEFAULT NULL,
`updated_at` datetime NOT NULL DEFAULT '2007-11-29 00:00:00',
`file_hash` varchar(255) DEFAULT NULL,
`flooding` tinyint(1) DEFAULT '0',
`status` int(11) DEFAULT NULL,
`date_time_original` datetime DEFAULT NULL,
`view_count` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `index_pictures_on_parent_id` (`parent_id`),
KEY `index_pictures_on_user_id` (`user_id`),
KEY `index_pictures_on_is_private` (`is_private`),
KEY `index_pictures_on_created_at` (`created_at`),
KEY `index_pictures_on_flooding` (`flooding`),
KEY `index_pictures_on_status` (`status`),
KEY `index_pictures_on_status_and_is_private_and_flooding` (`status`,`is_private`,`flooding`),
KEY `index_pictures_on_user_id_and_is_private` (`user_id`,`is_private`),
KEY `index_pictures_on_updated_at` (`updated_at`),
KEY `index_pictures_on_file_hash` (`file_hash`)
) ENGINE=InnoDB AUTO_INCREMENT=10587102 DEFAULT CHARSET=utf8