MySQL Bugs: #34204: Selecting from tables in different db's within TransactionScope does not work

Bug #34204	Selecting from tables in different db's within TransactionScope does not work
Submitted:	31 Jan 2008 19:46	Modified:	1 Mar 2008 11:06
Reporter:	Please Remove	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	5.1.4	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
When accessing tables from different databases within the same TransactionScope, the user/connection string(?) used to access the first database is used to access the second database. This is a problem if this user doesn't have enough privileges .

How to repeat:
Run this script:

CREATE DATABASE db1;
CREATE DATABASE db2;

DROP TABLE IF EXISTS `db1`.`a`;
CREATE TABLE  `db1`.`a` (
  `Test` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `db2`.`b`;
CREATE TABLE  `db2`.`b` (
  `test` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

GRANT USAGE ON * . * TO `User1`@'localhost' IDENTIFIED BY 'foo1';
GRANT SELECT ON `db1` . * TO `User1`@'localhost';

GRANT USAGE ON * . * TO `User2`@'localhost' IDENTIFIED BY 'foo2';
GRANT SELECT ON `db2` . * TO `User2`@'localhost';

Executing this code will trigger the error:

private const string ConString = "Data Source=localhost;Database=db1;User ID=User1; PWD=foo1;Port=3306";
private const string ConString2 = "Data Source=localhost;Database=db2;User ID=User2; PWD=foo2;Port=3306";

static void Main(string[] args)
{
     using (TransactionScope transactionScope = new TransactionScope())
     {
            MySqlHelper.ExecuteScalar(ConString, "SELECT * FROM a");

            MySqlHelper.ExecuteScalar(ConString2, "SELECT * FROM b");

            transactionScope.Complete();
     }
}

Suggested fix:
Not sure. This has probably to do with the excpetion I asked about in the forum:
http://forums.mysql.com/read.php?38,188429,188429#msg-188429 

So, the best way would be implementing support for connections using different connection strings inside the same transaction ;-)

If this won't be done anytime soon, a more appropriate exception message should be used.

Hi Dennis and thanks for your report. This is an intriguing question and I don't think it's possible but I'll ask Reggie to make ruling.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/41579

We didn't fix the underlying problem which is our lack of support for multiple connections inside a single transaction.  That is coming in a future release.  However we did fix the code to yield a more appropriate exception for this case.  This change is for 5.1.5 and 5.2+

A note has been added to the 5.1.5 and 5.2.0 changelogs: 

When accessing tables from different databases within the same TransactionScope, the same user/password combination would be used for each database connection. Connector/NET does not handle multiple connections within the same transaction scope. An error is now returned if you attempt this process, instead of using the incorrect authorization information.