MySQL Bugs: #32492: too many columns (error #1117) breaks replication

Bug #32492	too many columns (error #1117) breaks replication
Submitted:	19 Nov 2007 11:51	Modified:	6 Mar 2009 16:49
Reporter:	Aurimas Mikalauskas	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.0.45-log, 5.0 bzr	OS:	Linux
Assigned to:	Mats Kindahl	CPU Architecture:	Any
Tags:	replication
Triage:	Needs Triage: D2 (Serious) / R5 (Severe) / E4 (High)

Description:
Using table with "big" schema breaks replication with empty error '' (#1117) on slave although table is successfully created on both - slave and master. "Big" depends on length of column names, type etc. In this example having 1665 tinyint(1) columns from col_0 to col_1664 breaks the replication. On Master creating this table, adding an index to it etc. gives a warning:

mysql> SHOW WARNINGS;
+-------+------+------------------+
| Level | Code | Message          |
+-------+------+------------------+
| Error | 1117 | Too many columns | 
+-------+------+------------------+
1 row in set (0.00 sec)

On slave it breaks with error:

mysql> show slave status\G
...
                 Last_Errno: 1117
                 Last_Error: Error '' on query. Default database: 'test'. Query: 'CREATE TABLE `test` (
 col_0 tinyint(1), 
 col_1 tinyint(1), 
 col_2 tinyint(1), 
 col_3 tinyint(1), 
 col_4 tinyint(1), 
...

How to repeat:
Use this python script to generate a schema:

#!/usr/bin/env python

cols = 1665

print "CREATE TABLE `test` ("
for line in range(cols-1):
    print "  col_%d tinyint(1)," % (line)
print " col_%d tinyint(1)) " % (cols-1)

then apply it on master:

$ ./schema.py | mysql test

check replication status

Suggested fix:
Use:

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;

to skip these queries on slave as changes are actually applied.

Thank you for a problem report. Please, send SHOW CREATE TABLE results for the table from both master and slave (I'd like to check what storage engines are used), and the results of SHOW GLOBAL VARIABLES LIKE 'sql%' from both master and slave.

table "test" schema and variables like 'sql%'

Attachment: table_schema_and_variables.txt (text/plain), 61.62 KiB.

I have uploaded output from your requested commands. In short - I used the default MyISAM. It seems that InnoDB won't even allow to create this table:

crz.lt tmp # ./testsql.py | mysql test -p
Enter password: 
ERROR 1005 (HY000) at line 1: Can't create table './test/test.frm' (errno: 139)

Thank you for the feedback.

I can not repeat described behaviour in myself environment. Please indicate your operating system and provide accurate name of package you use (file name) and configuration files for both master and slave.

Sveta,

we're running into this on Gentoo system. It's Gentoo Base System release 1.12.9, Linux 2.6.18-xenU x86_64 so it's a Virtual Machine. MySQL is compiled using emerge system with single additional patch being microseconds slow query log patch http://www.mysqlperformanceblog.com/files/patches/patch.slow-micro.5.0.45.diff. Let me know if you need any further information.

Thank you for the feedback.

Verified as described.

For some reason could not create repeatable test case loadable by our test suite.

To repeat:

1. cd /path/to/mysql/binaries
2. ./bin/mysql_install_db --basedir=. --datadir=./data501
3. ./bin/mysql_install_db --basedir=. --datadir=./data502
 1006  ./libexec/mysqld --no-defaults --port=33501 --socket=/tmp/mysql501.sock --basedir=/users/ssmirnova/build/mysql-5.0 --datadir=/users/ssmirnova/build/mysql-5.0/data501 --server-id=501 --log-error --log-bin &
4. ./libexec/mysqld --no-defaults --port=33502 --socket=/tmp/mysql502.sock --basedir=/users/ssmirnova/build/mysql-5.0 --datadir=/users/ssmirnova/build/mysql-5.0/data502 --server-id=502 --log-error &
5. MASTER (M): show master status;
6. M: grant replication slave on *.* to repl@localhost identified by 'replrepl';
7. SLAVE (S): change master to master_log_file='host-bin.000001', master_log_pos=98, master_host='localhost', master_port=33501, master_user='repl', master_password='replrepl';
8. S: start slave
9. M: create table (paste from attached file)
10. S: show slave status \G

Note that the SHOW WARNINGS statement displays errors, warnings, and notes, not just warnings. In this particular case, there is an error is the warnings log, so the statement should not be written to the binary log, and even less, the table should not have been created on the master in the first place.

The behavior of the slave is correct, since an error is generated on the master, there should be an error on the slave as well.

The problem could either be with the replication code on the master (since the create table statement is logged despite the error), but since an error is generated for the InnoDB engine, I suspect that the MyISAM engine reports the error correctly but might not return correct error codes internally, thereby fooling the replication code to log the statement.

Could you please confirm that the CREATE TABLE statement when using the InnoDB engine does not log the statement?

Mats,

yes, I confirm "that the CREATE TABLE statement when using the InnoDB engine does not log the statement".

The server tries to create the table with "unireg screens" first. In this mode, less columns are possible. The function pack_header() detects the overflow and reports back ER_TOO_MANY_FIELDS (1117). The calling function mysql_create_frm() does now try to create the table without "unireg screens" to get more columns. But it does not clear the error, so that it persists in the "warnings stack". In the above test case, the second attempt succeeds. The table is created correctly, and a successful execution is reported. However, a SHOW WARNINGS reveals the stacked error.

When using InnoDB, pack_header() and mysql_create_frm() behave the same. But InnoDB itself has another limit, which leads to an error that prevents creation of the table. The operation reports that error back as it is the real cause for the failure. But SHOW WARNINGS still reveals the other error too.

I don't know how the replication behaves in this case. But it is possible that either master or slave, but seemingly not both, grab the error code from the warnings stack and the slave compares it with the successful operation.

Clearing the error in mysql_create_frm(), before trying the second attempt, might solve the problem.

Issue is 5.0 specific and does not exist in 5.1 - will not fix