MySQL Bugs: #40843: Issues when multiple mysql instances with 'ibmdb2i' interact with DB2

Bug #40843	Issues when multiple mysql instances with 'ibmdb2i' interact with DB2
Submitted:	19 Nov 2008 9:00	Modified:	20 Aug 2009 12:28
Reporter:	Nidhi Shrotriya	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1.29rc	OS:	Other (IBM i)
Assigned to:	MC Brown	CPU Architecture:	Any

Description:
This issue is reported as part of certification testing of 'IBMDB2I' SE.
The report talks about the issues we faced in the scenario when multiple mysql instances with 'ibmdb2i' plugin interact with one DB2 instance (the default case when no IASPs configured).

Issues -

1) 2 mysql instances can create schemas with same name but a mysql instance can't create a table within the schema if the table with same name is created by  by another mysql instance till the instance who created it drops it or DB2 drops it. DB2 shares a single namespace for the tables created in same schema by different instances??
2) 2 mysql instances can create schemas with same name and then one mysql instance while dropping its own schema drops the other instance's schema too. This issue looks more serious as it is a security breach and develops inconsistency too.

How to repeat:
Take mysql built with 'ibmdb2i' and install the 'ibmdb2i' plugin.

Example :

Ist Instance
----------------

mysql> create database newdb;
Query OK, 1 row affected (0.04 sec)

mysql> use newdb;
Database changed

mysql> create table t1 ( i int) engine=ibmdb2i;
Query OK, 0 rows affected (0.16 sec)

mysql> insert into t1 values (1);
Query OK, 1 row affected (0.09 sec)

IInd instance
---------------
mysql> create database newdb;
Query OK, 1 row affected (0.04 sec)

mysql> use newdb;
Database changed

mysql> create table t1(a int) engine=ibmdb2i;
ERROR 1005 (HY000): Can't create table 'newdb.t1' (errno: 2021)

mysql> drop database newdb;

After dropping 'newdb' from IInd instance
-----------------------------------------
Ist Instance 

mysql> show tables;
+-----------------+
| Tables_in_newdb |
+-----------------+
| t1              | 
+-----------------+
1 row in set (0.01 sec)

mysql> select * from t1;
ERROR 1030 (HY000): Got error 2021 from storage engine

show tables still shows the table whereas it actually doesn't exist. Dropping database from IInd instance has dropped tables created by Ist instance too.

From MySQL server log:
ibmdb2i error 2021: See message MCH3401 in joblog for job 144225/QUSER/QSQSRVR.

IBM's Response: is working as designed. As documented, will need IASPs to truly separate the different instances of MySQL.
If you do have IASPs configured, you can set the MySQL ibmdb2i_rdb_name option with the name of the IASP. This will cause the IBMDB2I engine to communicate with the specified user database. One caveat to note is that, while multiple user databases can have schemas with an identical name, a user database cannot contain a schema name that also exists in the system database.

The documentation talks about running multiple mysql server instances (and not always strictly using IASPs for that). It doesn't seem to be suggesting the mishappenings that could happen due to using multiple MySQL server instances without IASPs.

From IBMRedBook-
Security: You may want to maintain multiple MySQL databases that are totally
independent from each other in all aspects. Although you may be able to do this with one MySQL server instance, using separated MySQL server instances can be a more robust way for such a purpose.
Catering for different globally scoped MySQL environments: You may encounter a
situation where you cannot or should not mix multiple MySQL databases within one
MySQL server instance due to conflicting requirements such as:
– Using different IBM i independent ASP (IASP): For example, if you want to create and maintain two MySQL databases in two different IASPs for some reasons (which is controlled by the IBMDB2I system variable named ibmdb2i_rdb_name), you can run two instances of MySQL server with each using a different IASP to serve this purpose.

I believe the documentation while mentioning about using multiple MySQL server instances without IASPs should cover this, as this is also an available option and scenarios mentioned here can rise.

Same issue occurs while multiple server instances using 1 IASP. 
Hence the bug could be reproduced with the following 2 scenarios
Multiple server instances using no IASP.
Multiple server instances using same IASP.

The documentation should mention about the data security/inconsistency possibility for the above 2 cases and hence different IASPs should strictly be used with different server instances.

I guess, this could be documented in IBMRedbook as well as in MySQL Doc.

Setting it to documentation based on IBM(Timothy Clark)'s response -

We could add it to the MySQL reference material and IBM would take care of it as below -
I think you have raised some valid documentation concerns. I do not believe that experienced IBM i users will be surprised by the interaction between multiple MySQL instances, but I can see how users who are less familiar with the platform would find additional documentation helpful.

Because the IBM Redbook is a printed publication, we cannot easily update it. However, we are working to publish a web page with Redbook errata and additions, and I can add this information there.

A note has been added intot he DB2 Notes section that highlights the issues and how to get round them by using separate IASPs.