| Bug #34204 | Selecting from tables in different db's within TransactionScope does not work | ||
|---|---|---|---|
| Submitted: | 31 Jan 2008 20:46 | Modified: | 1 Mar 2008 12:06 |
| Reporter: | Dennis Gronewold | ||
| Status: | Closed | ||
| Category: | Connector/Net | Severity: | S3 (Non-critical) |
| Version: | 5.1.4 | OS: | Microsoft Windows |
| Assigned to: | Target Version: | ||
[1 Feb 2008 9:16]
Tonci Grgin
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.
[1 Feb 2008 19:26]
Bugs System
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
[1 Feb 2008 19:27]
Reggie Burnett
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+
[1 Mar 2008 12:06]
MC Brown
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.
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.