MySQL Bugs: #61552: Replication from cluster to non-cluster breaks on STORAGE MEMORY/DISK syntax

Bug #61552	Replication from cluster to non-cluster breaks on STORAGE MEMORY/DISK syntax
Submitted:	17 Jun 2011 15:04	Modified:	1 Jul 2011 10:04
Reporter:	Frans Grotepass	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	All Cluster/5.1	OS:	Any
Assigned to:	Jon Stephens	CPU Architecture:	Any

Description:
When replicating from cluster to non-cluster, the table creation statements containing a STORAGE MEMORY or STORAGE DISK token produces a syntax error in the slave. This is irrespective of whether the relevant table should be replicated or not according the my.cnf configuration. 

How to repeat:
Set up replication. On master have database bla. On slave the configuration specifies replication replicate-do-table = bla.foo
On master execute:
CREATE TABLE IF NOT EXISTS oof ( oof1 INT STORAGE MEMORY, off2 INT STORAGE MEMORY, soof1 VARCHAR(240) STORAGE DISK, PRIMARY KEY(oof1) USING HASH) TABLESPACE foo_ts ENGINE = NDBCLUSTER MAX_ROWS = 1000000;

On slave replication stops with:
Last_Errno: 1064
Last_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'STORAGE MEMORY, off2 INT STORAGE MEMORY, soof1 VARCHAR(240) ' at line 1' on query. Default database: 'bla'. Query: 'CREATE TABLE IF NOT EXISTS oof ( oof1 INT STORAGE MEMORY, off2 INT STORAGE MEMORY, soof1 VARCHAR(240) STORAGE DISK, PRIMARY KEY(oof1) USING HASH) TABLESPACE foo_ts ENGINE = NDBCLUSTER MAX_ROWS = 1000000;'

Suggested fix:
Ignore STORAGE MEMORY /STORAGE DISK TABLESPACE tokens when NDBCLUSTER is not supported and storage engine drops to the default storage engine for processing replication logs.

This has also been verified with version 5.1.57. 

In the manual (http://dev.mysql.com/doc/refman/5.1/en/create-table.html) the following remark is included:

For NDB tables, STORAGE DEFAULT is equivalent to STORAGE MEMORY.
The STORAGE clause has no effect on tables using storage engines other than NDB. 

According to the documentation, the storage clause should thus be ignored.

Is MySQL 5.1.48 used on both the master and the slave?

The master is ndbcluster 7.1(.9 I think) with mysql 5.1.51. For this reason it was also verified with a slave that is newer than 5.1.51.

> In the manual (http://dev.mysql.com/doc/refman/5.1/en/create-table.html) the 
> following remark is included:

> For NDB tables, STORAGE DEFAULT is equivalent to STORAGE MEMORY.
> The STORAGE clause has no effect on tables using storage engines other 
> than NDB. 

> According to the documentation, the storage clause should thus be ignored.

This is not a Server or Cluster bug. According to the documentation,

  For NDB tables, *beginning with MySQL Cluster NDB 6.2.5 and MySQL Cluster NDB 6.3.2,*...

Note we say *MySQL Cluster*, and we say nothing about support in the regular MySQL Server. In other words: This syntax is supported in MySQL Cluster only; it is not supported anywhere else.

I have amended the item to make this clear(er).

cheers

jon.

A Docs issue.

Updated category/severity/lead, assigned to myself.

Updated version/OS.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

:) I suspected that the bug might be solved by changing the docs. The problem is that with replication there is no way to govern this interpretation (to my knowledge). 

http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-replication.html
Does imply that cluster to non-cluster replication is supported. The interpretation of the binary logs on the non cluster node unfortunately has no means of handling the binary logs correctly.

Seeing that the logs can not be handled correctly, I still see this as an open bug in the replication framework. It is acceptable if the client produces a syntax error if the command is issued.

Hi Frans,

Well, it says, "...it is not necessary to use the Cluster storage engine on the slave." It doesn't say you can use a non-Cluster mysqld. Basically, after Cluster split from the main branch and support for NDB was dropped in 5.1.27 (main) and later, 

However, I've added some notes about this:

As for the SQL incompatibility issue for replication in general, see http://dev.mysql.com/doc/refman/5.1/en/replication-compatibility.html

cheers

jon.

Seeing that I still consider this a bug, a solution is available that I wish to publish here.  Hopefully someone with a similar problem will stumble upon it and lets hope that the workaround works for him. The solution is as follows: 

When one creates tables on the fly that is not replicated and breaks the replication due to syntax errors, one can create a template before setting up the replication. Using the "CREATE TABLE bla LIKE template_bla" produces no syntax error and will be skipped since bla is not replicated by the slave.