| Bug #48666 | Memory tables + max_heap_table_size can cause replication problems | ||
|---|---|---|---|
| Submitted: | 10 Nov 2009 12:15 | Modified: | 3 Sep 2010 9:50 |
| Reporter: | Victor Kirkebo | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 5.0.88-bzr, 5.1.41, 5.1.42-bzr | OS: | Any |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
| Tags: | Error_code 1114, memory table, table full | ||
[10 Nov 2009 14:02]
Valeriy Kravchuk
Verified with 5.1.42 on Mac OS X:
77-52-7-73:mysql-test openxs$ ./mysql-test-run.pl --suite=rpl rpl_bug48666
Logging: ./mysql-test-run.pl --suite=rpl rpl_bug48666
091110 15:57:52 [Warning] Setting lower_case_table_names=2 because file system for /var/folders/dX/dXCzvuSlHX4Op1g-o1jIWk+++TI/-Tmp-/yuSqssNxa2/ is case insensitive
091110 15:57:52 [Note] Plugin 'FEDERATED' is disabled.
091110 15:57:52 [Note] Plugin 'ndbcluster' is disabled.
MySQL Version 5.1.42
Checking supported features...
- using ndbcluster when necessary, mysqld supports it
- SSL connections supported
- binaries are debug compiled
Collecting tests...
- adding combinations for rpl
vardir: /Users/openxs/dbs/5.1/mysql-test/var
Checking leftover processes...
Removing old var directory...
Creating var directory '/Users/openxs/dbs/5.1/mysql-test/var'...
Installing system database...
Using server port 59657
==============================================================================
TEST RESULT TIME (ms)
------------------------------------------------------------
worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
rpl.rpl_bug48666 'row' [ pass ] 1960
...
rpl.rpl_bug48666 'stmt' [ fail ]
Test ended at 2009-11-10 15:58:08
CURRENT_TEST: rpl.rpl_bug48666
=== SHOW MASTER STATUS ===
---- 1. ----
File slave-bin.000001
Position 1296
Binlog_Do_DB
Binlog_Ignore_DB
==========================
=== SHOW SLAVE STATUS ===
---- 1. ----
Slave_IO_State Waiting for master to send event
Master_Host 127.0.0.1
Master_User root
Master_Port 13000
Connect_Retry 1
Master_Log_File master-bin.000001
Read_Master_Log_Pos 1296
Relay_Log_File slave-relay-bin.000003
Relay_Log_Pos 1307
Relay_Master_Log_File master-bin.000001
Slave_IO_Running Yes
Slave_SQL_Running No
Replicate_Do_DB
Replicate_Ignore_DB
Replicate_Do_Table
Replicate_Ignore_Table
Replicate_Wild_Do_Table
Replicate_Wild_Ignore_Table
Last_Errno 0
Last_Error Query caused different errors on master and slave. Error on master: 'The table '%-.192s' is full' (1114), Error on slave: 'no error' (0). Default database: 'test'. Query: 'insert into tb_mem values('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8')'
Skip_Counter 0
Exec_Master_Log_Pos 1161
Relay_Log_Space 1597
Until_Condition None
Until_Log_File
Until_Log_Pos 0
Master_SSL_Allowed No
Master_SSL_CA_File
Master_SSL_CA_Path
Master_SSL_Cert
Master_SSL_Cipher
Master_SSL_Key
Seconds_Behind_Master
Master_SSL_Verify_Server_Cert No
Last_IO_Errno 0
Last_IO_Error
Last_SQL_Errno 0
Last_SQL_Error Query caused different errors on master and slave. Error on master: 'The table '%-.192s' is full' (1114), Error on slave: 'no error' (0). Default database: 'test'. Query: 'insert into tb_mem values('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8')'
=========================
...
analyze: sync_with_master
mysqltest: At line 20: sync_with_master failed: 'select master_pos_wait('master-bin.000001', 1296, 300)' returned NULL indicating slave SQL thread failure
...
[10 Nov 2009 14:44]
MySQL Verification Team
Thank you for the bug report. Verified as described.
091110 12:28:04 [Note] bin\mysqld: ready for connections.
Version: '5.1.41-Win X64-debug-log' socket: '' port: 3307 Source distribution
bin\mysqld: missing DBUG_RETURN or DBUG_VOID_RETURN macro in function "?func"
091110 12:31:47 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has h
is hostname changed!! Please use '--relay-log=nenem-relay-bin' to avoid this problem.
091110 12:31:57 [Note] Slave SQL thread initialized, starting replication in log 'mydb.000001' at position 342, relay log '.\nenem-relay-bin.000001' pos
ition: 4
091110 12:31:57 [Note] Slave I/O thread: connected to master 'miguel@localhost:3306',replication started in log 'mydb.000001' at position 342
091110 12:38:40 [ERROR] Slave SQL: Query caused different errors on master and slave. Error on master: 'The table '%-.192s' is full' (1114), Error o
n slave: 'no error' (0). Default database: 'test'. Query: 'insert into tb_mem values('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8')', Error_code: 0
091110 12:38:40 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped
at log 'mydb.000001' position 1345
[12 Nov 2009 17:43]
Valeriy Kravchuk
5.0.88 is also affected, so we always had this problem:
77-52-7-73:mysql-test openxs$ ./mysql-test-run.pl rpl_bug48666
Logging: ./mysql-test-run.pl rpl_bug48666
MySQL Version 5.0.88
Using ndbcluster when necessary, mysqld supports it
Setting mysqld to support SSL connections
Binaries are debug compiled
mysql-test-run: WARNING: Could not find all required ndb binaries, all ndb tests will fail, use --skip-ndbcluster to skip testing it.
Using MTR_BUILD_THREAD = 0
Using MASTER_MYPORT = 9306
Using MASTER_MYPORT1 = 9307
Using SLAVE_MYPORT = 9308
Using SLAVE_MYPORT1 = 9309
Using SLAVE_MYPORT2 = 9310
Using NDBCLUSTER_PORT = 9311
Using IM_PORT = 9313
Using IM_MYSQLD1_PORT = 9314
Using IM_MYSQLD2_PORT = 9315
Killing Possible Leftover Processes
Removing Stale Files
Creating Directories
Installing Master Database
Installing Slave1 Database
=======================================================
Starting Tests in the 'main' suite
TEST RESULT TIME (ms)
-------------------------------------------------------
rpl_bug48666 [ fail ]
=== SHOW MASTER STATUS ===
---- 1. ----
File slave-bin.000001
Position 1398
Binlog_Do_DB
Binlog_Ignore_DB
==========================
=== SHOW SLAVE STATUS ===
---- 1. ----
Slave_IO_State Waiting for master to send event
Master_Host 127.0.0.1
Master_User root
Master_Port 9306
Connect_Retry 1
Master_Log_File master-bin.000001
Read_Master_Log_Pos 1288
Relay_Log_File slave-relay-bin.000003
Relay_Log_Pos 1291
Relay_Master_Log_File master-bin.000001
Slave_IO_Running Yes
Slave_SQL_Running No
Replicate_Do_DB
Replicate_Ignore_DB
Replicate_Do_Table
Replicate_Ignore_Table
Replicate_Wild_Do_Table
Replicate_Wild_Ignore_Table
Last_Errno 0
Last_Error Query caused different errors on master and slave. Error on master: 'The table '%-.64s' is full' (1114), Error on slave: 'no error' (0). Default database: 'test'. Query: 'insert into tb_mem values('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8')'
[6 Aug 2010 6:30]
Daogang Qu
Will be fixed in WL#5135.
[25 Jul 2011 15:42]
Ryan Martin
We recently encountered this bug with a deployed 5.1.43 master, and are wondering if it has been fixed in a more recent release. It's marked "duplicate" but no duplicate bug number is listed. Also, the link to worklog #5135 is broken. Was this bug ever fixed?
[22 Feb 2012 11:06]
Jon Stephens
This is a known issue which has now been documented for MySQL 5.0, 5.1, 5.5, 5.6. This was marked as a duplicate due to Daogang Qu's comment above; the issue is expected to be addressed as part of a larger task. I don't know when that work might be completed. The documentation updates have committed to our repo and should appear online in a day or 2. Thanks!
Description: When creating a table with engine=memory the maximum size of the table is determined by the session system variable max_heap_table_size. If the master sets this variable it will not be replicated to the slave. The maximum size of the memory table might then be different on the slave. This can cause some problems. 1) If the max table size on the master is higher than on the slave and the master adds more data to the table than the slave can do the slave aborts with error: 091110 13:07:22 [Warning] Slave: The table 'tb_mem' is full Error_code: 1114 091110 13:07:22 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'master-bin.000002' position 9836371 2) In statement replication mode if the max size on the master is lower than on the slave the slave can successfully add more rows to the table than the master can. 3) The same issues (1 and 2 above) exist if the master and slave initially have identical max_heap_table_size but the master later changes the value of the variable and rebuilds the memory table with ALTER TABLE <tbname> engine=memory; How to repeat: This example is with MBR mode. On master: ---------- mysql> set max_heap_table_size=16384; Query OK, 0 rows affected (0.00 sec) mysql> create table tb_mem(f1 char(255)) engine=memory; Query OK, 0 rows affected (0.00 sec) mysql> insert into tb_mem values('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'); Query OK, 8 rows affected (0.00 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> insert into tb_mem values('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'); Query OK, 8 rows affected (0.00 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> insert into tb_mem values('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'); Query OK, 8 rows affected (0.00 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> insert into tb_mem values('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'); Query OK, 8 rows affected (0.01 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> insert into tb_mem values('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'); Query OK, 8 rows affected (0.00 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> insert into tb_mem values('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'); Query OK, 8 rows affected (0.00 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> insert into tb_mem values('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'); ERROR 1114 (HY000): The table 'tb_mem' is full mysql> select count(*) from tb_mem; +----------+ | count(*) | +----------+ | 50 | +----------+ 1 row in set (0.00 sec) On slave: --------- mysql> select count(*) from tb_mem; +----------+ | count(*) | +----------+ | 56 | +----------+ 1 row in set (0.00 sec) On master: ----------- mysql> set max_heap_table_size=1024*1024*2; Query OK, 0 rows affected (0.00 sec) mysql> alter table tb_mem engine=memory; Query OK, 50 rows affected (0.00 sec) Records: 50 Duplicates: 0 Warnings: 0 mysql> insert into tb_mem select * from tb_mem; Query OK, 50 rows affected (0.00 sec) Records: 50 Duplicates: 0 Warnings: 0 mysql> insert into tb_mem select * from tb_mem; Query OK, 100 rows affected (0.00 sec) Records: 100 Duplicates: 0 Warnings: 0 mysql> insert into tb_mem select * from tb_mem; Query OK, 200 rows affected (0.00 sec) Records: 200 Duplicates: 0 Warnings: 0 mysql> insert into tb_mem select * from tb_mem; Query OK, 400 rows affected (0.00 sec) Records: 400 Duplicates: 0 Warnings: 0 mysql> insert into tb_mem select * from tb_mem; Query OK, 800 rows affected (0.00 sec) Records: 800 Duplicates: 0 Warnings: 0 mysql> insert into tb_mem select * from tb_mem; Query OK, 1600 rows affected (0.00 sec) Records: 1600 Duplicates: 0 Warnings: 0 mysql> insert into tb_mem select * from tb_mem; Query OK, 3200 rows affected (0.00 sec) Records: 3200 Duplicates: 0 Warnings: 0 mysql> select count(*) from tb_mem; +----------+ | count(*) | +----------+ | 6400 | +----------+ 1 row in set (0.00 sec) On slave: --------- mysql> select count(*) from tb_mem; +----------+ | count(*) | +----------+ | 3970 | +----------+ 1 row in set (0.00 sec) In ./var/log/mysqld.2.err: --------------------------- 091110 15:04:21 [ERROR] /mysql-advanced-5.1.41-solaris10-x86_64/bin/mysqld: The table 'tb_mem' is full 091110 15:04:21 [ERROR] Slave SQL: Error 'The table 'tb_mem' is full' on query. Default database: 'systest2'. Query: 'insert into tb_mem select * from tb_mem', Error_code: 1114 091110 15:04:21 [Warning] Slave: The table 'tb_mem' is full Error_code: 1114 091110 15:04:21 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'master-bin.000002' position 11133896 Suggested fix: Allow setting of system variable max_heap_table_size to be replicated