Description:
070709 18:00:22 InnoDB: Error: trying to declare trx to enter InnoDB, but
InnoDB: it already is declared.
TRANSACTION 0 233523, ACTIVE 0 sec, OS thread id 418786816, thread declared inside InnoDB 0
mysql tables in use 2, locked 2
3 lock struct(s), heap size 320, 1 row lock(s)
MySQL thread id 2, query id 56 localhost localGameUser Sending data
INSERT INTO test_dst (src_id, name, clock) SELECT id, name, NOW() FROM test_src
How to repeat:
/* Create source table */
CREATE TABLE test_src
(id int(11) NOT NULL AUTO_INCREMENT,
name char(32) NOT NULL,
PRIMARY KEY (id),
KEY name (name))
ENGINE=InnoDB DEFAULT CHARSET=cp1251;
/* Create destination table */
CREATE TABLE test_dst
(id int(11) NOT NULL AUTO_INCREMENT,
src_id int(11) NOT NULL,
name char(32) NOT NULL,
clock datetime NOT NULL,
PRIMARY KEY (id),
KEY src_id (src_id),
KEY name (name),
KEY clock (clock))
ENGINE=InnoDB DEFAULT CHARSET=cp1251;
/* Init source table */
delimiter //
CREATE PROCEDURE fill_src()
BEGIN
DECLARE count INT DEFAULT 5;
SET count = 0;
WHILE count < 200 DO
INSERT test_src SET name = MD5(RAND());
SET count = count + 1;
END WHILE;
END
//
delimiter ;
CALL fill_src();
/* sample insert-select query */
INSERT INTO test_dst
(src_id, name, clock)
SELECT
id, name, NOW()
FROM
test_src;
/* sample php daemon */
#!/usr/local/bin/php
<?php
mysql_connect('localhost', 'root', '');
mysql_select_db('sc');
mysql_query("SET AUTOCOMMIT=1");
while (true)
{
mysql_query("INSERT INTO test_dst (src_id, name, clock) SELECT id, name, NOW() FROM test_src");
echo mysql_result(mysql_query("SELECT COUNT(*) FROM test_dst"), 0)."\n";
}
?>
.err file after start mysqld:
070709 17:58:40 mysqld started
070709 17:58:40 [Warning] No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts as a
070709 17:58:40 [Warning] Server variable data_file_path of plugin InnoDB was forced to be read-only: string variable without update_func and PLUGIN_VAR_MEMA
070709 17:58:40 [Warning] Server variable data_home_dir of plugin InnoDB was forced to be read-only: string variable without update_func and PLUGIN_VAR_MEMAL
070709 17:58:40 [Warning] Server variable flush_method of plugin InnoDB was forced to be read-only: string variable without update_func and PLUGIN_VAR_MEMALL
070709 17:58:40 [Warning] Server variable log_arch_dir of plugin InnoDB was forced to be read-only: string variable without update_func and PLUGIN_VAR_MEMALL
070709 17:58:40 [Warning] Server variable log_group_home_dir of plugin InnoDB was forced to be read-only: string variable without update_func and PLUGIN_VAR_
070709 17:58:40 InnoDB: Started; log sequence number 0 3733818349
070709 17:58:40 [Note] Event Scheduler: Loaded 0 events
070709 17:58:40 [Note] /usr/local/libexec/mysqld: ready for connections.
Version: '5.1.18-beta-log' socket: '/tmp/mysql.sock' port: 3306 FreeBSD port: mysql-server-5.1.18
.err file if started TWO OR MORE php daemons:
070709 18:00:22 InnoDB: Error: trying to declare trx to enter InnoDB, but
InnoDB: it already is declared.
TRANSACTION 0 233523, ACTIVE 0 sec, OS thread id 418786816, thread declared inside InnoDB 0
mysql tables in use 2, locked 2
3 lock struct(s), heap size 320, 1 row lock(s)
MySQL thread id 2, query id 56 localhost localGameUser Sending data
INSERT INTO test_dst (src_id, name, clock) SELECT id, name, NOW() FROM test_src
070709 18:00:23 InnoDB: Error: trying to declare trx to enter InnoDB, but
InnoDB: it already is declared.
TRANSACTION 0 233527, ACTIVE 0 sec, OS thread id 418786816, thread declared inside InnoDB 0
mysql tables in use 2, locked 2
3 lock struct(s), heap size 320, 1 row lock(s)
MySQL thread id 2, query id 60 localhost localGameUser Sending data
INSERT INTO test_dst (src_id, name, clock) SELECT id, name, NOW() FROM test_src
070709 18:00:23 InnoDB: Error: trying to declare trx to enter InnoDB, but
InnoDB: it already is declared.
TRANSACTION 0 233531, ACTIVE 0 sec, OS thread id 418786816, thread declared inside InnoDB 0
mysql tables in use 2, locked 2
3 lock struct(s), heap size 320, 1 row lock(s)
MySQL thread id 2, query id 64 localhost localGameUser Sending data
INSERT INTO test_dst (src_id, name, clock) SELECT id, name, NOW() FROM test_src
etc...
And then some all insert-select queries on InnoDB executes much slower than on MyISAM tables.
my.cnf file. With this mysql-server using InnoDB tables only except mysql database:
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer_size = 4M
sort_buffer_size = 1M
read_buffer_size = 1M
max_connections = 1024
join_buffer_size = 1M
table_cache = 1500
max_allowed_packet = 1M
read_buffer_size = 1M
net_buffer_length = 8K
myisam_sort_buffer_size = 1M
read_rnd_buffer_size = 512K
tmp_table_size = 32M
max_tmp_tables = 256
query_cache_type = 2
query_cache_size = 16777216
query_cache_limit = 16777216
default-character-set = cp1251
default-collation = cp1251_general_ci
character-sets-dir = /usr/local/share/mysql/charsets
innodb_data_home_dir = /var/db/mysql/
innodb_data_file_path = ibdata1:100M:autoextend
innodb_log_group_home_dir = /var/db/mysql/
innodb_log_arch_dir = /var/db/mysql/
innodb_buffer_pool_size = 200M
innodb_additional_mem_pool_size = 16M
innodb_log_file_size = 50M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout = 50
Description: 070709 18:00:22 InnoDB: Error: trying to declare trx to enter InnoDB, but InnoDB: it already is declared. TRANSACTION 0 233523, ACTIVE 0 sec, OS thread id 418786816, thread declared inside InnoDB 0 mysql tables in use 2, locked 2 3 lock struct(s), heap size 320, 1 row lock(s) MySQL thread id 2, query id 56 localhost localGameUser Sending data INSERT INTO test_dst (src_id, name, clock) SELECT id, name, NOW() FROM test_src How to repeat: /* Create source table */ CREATE TABLE test_src (id int(11) NOT NULL AUTO_INCREMENT, name char(32) NOT NULL, PRIMARY KEY (id), KEY name (name)) ENGINE=InnoDB DEFAULT CHARSET=cp1251; /* Create destination table */ CREATE TABLE test_dst (id int(11) NOT NULL AUTO_INCREMENT, src_id int(11) NOT NULL, name char(32) NOT NULL, clock datetime NOT NULL, PRIMARY KEY (id), KEY src_id (src_id), KEY name (name), KEY clock (clock)) ENGINE=InnoDB DEFAULT CHARSET=cp1251; /* Init source table */ delimiter // CREATE PROCEDURE fill_src() BEGIN DECLARE count INT DEFAULT 5; SET count = 0; WHILE count < 200 DO INSERT test_src SET name = MD5(RAND()); SET count = count + 1; END WHILE; END // delimiter ; CALL fill_src(); /* sample insert-select query */ INSERT INTO test_dst (src_id, name, clock) SELECT id, name, NOW() FROM test_src; /* sample php daemon */ #!/usr/local/bin/php <?php mysql_connect('localhost', 'root', ''); mysql_select_db('sc'); mysql_query("SET AUTOCOMMIT=1"); while (true) { mysql_query("INSERT INTO test_dst (src_id, name, clock) SELECT id, name, NOW() FROM test_src"); echo mysql_result(mysql_query("SELECT COUNT(*) FROM test_dst"), 0)."\n"; } ?> .err file after start mysqld: 070709 17:58:40 mysqld started 070709 17:58:40 [Warning] No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts as a 070709 17:58:40 [Warning] Server variable data_file_path of plugin InnoDB was forced to be read-only: string variable without update_func and PLUGIN_VAR_MEMA 070709 17:58:40 [Warning] Server variable data_home_dir of plugin InnoDB was forced to be read-only: string variable without update_func and PLUGIN_VAR_MEMAL 070709 17:58:40 [Warning] Server variable flush_method of plugin InnoDB was forced to be read-only: string variable without update_func and PLUGIN_VAR_MEMALL 070709 17:58:40 [Warning] Server variable log_arch_dir of plugin InnoDB was forced to be read-only: string variable without update_func and PLUGIN_VAR_MEMALL 070709 17:58:40 [Warning] Server variable log_group_home_dir of plugin InnoDB was forced to be read-only: string variable without update_func and PLUGIN_VAR_ 070709 17:58:40 InnoDB: Started; log sequence number 0 3733818349 070709 17:58:40 [Note] Event Scheduler: Loaded 0 events 070709 17:58:40 [Note] /usr/local/libexec/mysqld: ready for connections. Version: '5.1.18-beta-log' socket: '/tmp/mysql.sock' port: 3306 FreeBSD port: mysql-server-5.1.18 .err file if started TWO OR MORE php daemons: 070709 18:00:22 InnoDB: Error: trying to declare trx to enter InnoDB, but InnoDB: it already is declared. TRANSACTION 0 233523, ACTIVE 0 sec, OS thread id 418786816, thread declared inside InnoDB 0 mysql tables in use 2, locked 2 3 lock struct(s), heap size 320, 1 row lock(s) MySQL thread id 2, query id 56 localhost localGameUser Sending data INSERT INTO test_dst (src_id, name, clock) SELECT id, name, NOW() FROM test_src 070709 18:00:23 InnoDB: Error: trying to declare trx to enter InnoDB, but InnoDB: it already is declared. TRANSACTION 0 233527, ACTIVE 0 sec, OS thread id 418786816, thread declared inside InnoDB 0 mysql tables in use 2, locked 2 3 lock struct(s), heap size 320, 1 row lock(s) MySQL thread id 2, query id 60 localhost localGameUser Sending data INSERT INTO test_dst (src_id, name, clock) SELECT id, name, NOW() FROM test_src 070709 18:00:23 InnoDB: Error: trying to declare trx to enter InnoDB, but InnoDB: it already is declared. TRANSACTION 0 233531, ACTIVE 0 sec, OS thread id 418786816, thread declared inside InnoDB 0 mysql tables in use 2, locked 2 3 lock struct(s), heap size 320, 1 row lock(s) MySQL thread id 2, query id 64 localhost localGameUser Sending data INSERT INTO test_dst (src_id, name, clock) SELECT id, name, NOW() FROM test_src etc... And then some all insert-select queries on InnoDB executes much slower than on MyISAM tables. my.cnf file. With this mysql-server using InnoDB tables only except mysql database: [mysqld] port = 3306 socket = /tmp/mysql.sock skip-locking key_buffer_size = 4M sort_buffer_size = 1M read_buffer_size = 1M max_connections = 1024 join_buffer_size = 1M table_cache = 1500 max_allowed_packet = 1M read_buffer_size = 1M net_buffer_length = 8K myisam_sort_buffer_size = 1M read_rnd_buffer_size = 512K tmp_table_size = 32M max_tmp_tables = 256 query_cache_type = 2 query_cache_size = 16777216 query_cache_limit = 16777216 default-character-set = cp1251 default-collation = cp1251_general_ci character-sets-dir = /usr/local/share/mysql/charsets innodb_data_home_dir = /var/db/mysql/ innodb_data_file_path = ibdata1:100M:autoextend innodb_log_group_home_dir = /var/db/mysql/ innodb_log_arch_dir = /var/db/mysql/ innodb_buffer_pool_size = 200M innodb_additional_mem_pool_size = 16M innodb_log_file_size = 50M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 0 innodb_lock_wait_timeout = 50