MySQL Bugs: #15822: MySQLReader limited.

Bug #15822	MySQLReader limited.
Submitted:	17 Dec 2005 11:48	Modified:	25 Oct 2006 15:12
Reporter:	Michael Davies	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	4.1.16	OS:	Windows (XP)
Assigned to:	Reggie Burnett	CPU Architecture:	Any

Description:
Using latest version of MySQL and .Net Connector under Visual Studio 2005.

Wrote an application using ODBC and converting it to use MySQL connector.

Application basically uses SELECT to get records from a table reads them using a datareader and then makes another command and SELECT againsta other tables using data from the first command, attempts to open that as a datareader too.

When the open attempt happens for the second datareader an error datareader already open on the connector occurs.

No changes made to code except changing ODBC to MySQL. ODBC works fine.

Michael

How to repeat:
Here is a part of the code line with the line that fails marked **

Sub BuildTreeRoot(ByVal catroot As TreeView)
        OpenConnection()

        ShowStatus("Build Categories...")

        catroot.Nodes.Clear()

        Dim strCmd As String

        strCmd = "select categories.categories_id, categories_name from categories_description "
        strCmd = strCmd + "left outer join categories on categories.categories_id = categories_description.categories_id "
        strCmd = strCmd + "where categories.parent_id = 0 order by categories_name;"

        Dim TopCmd As New MySqlCommand(strCmd, db)
        Dim rdrCat As MySqlDataReader
        Dim ri As Integer

        rdrCat = TopCmd.ExecuteReader
        ri = 0
        Do Until rdrCat.Read = False
            catroot.Nodes.Add(New TreeNode(GetCategoryName(rdrCat("categories_id").ToString)))
            catroot.Nodes(ri).Tag = rdrCat("categories_id")
            BuildTreeRecurse(catroot.Nodes(ri), rdrCat("categories_id").ToString)
            ri = ri + 1
        Loop
        rdrCat.Close()

        CloseConnection()

        TopCmd.Dispose()
        dlgStatus.Close()
End Sub

Sub BuildTreeRecurse(ByVal leaf As TreeNode, ByVal tag As String)
        Dim LevelCmd As New MySqlCommand("", db)
        Dim rdrSubCat As MySqlDataReader
        Dim lf As TreeNode
        Dim strcmd As String

        strcmd = "select categories.categories_id, categories_name from categories_description "
        strcmd = strcmd + "left outer join categories on categories.categories_id = categories_description.categories_id "
        strcmd = strcmd + "where categories.parent_id =" + tag + " order by categories_name;"

        LevelCmd.CommandText = strcmd
        rdrSubCat = LevelCmd.ExecuteReader
        Do Until rdrSubCat.Read = False
            lf = New TreeNode(GetCategoryName(rdrSubCat("categories_id").ToString))
            lf.Tag = rdrSubCat("categories_id")
            leaf.Nodes.Add(lf)
            BuildTreeRecurse(lf, rdrSubCat("categories_id").ToString)
        Loop
        rdrSubCat.Close()
        LevelCmd.Dispose()
End Sub

Function GetCategoryName(ByVal Cat As String) As String
        Dim catNames As New MySqlCommand("", db)
        Dim rdrCatName As MySqlDataReader
        Dim tmp As String

        tmp = ""
        catNames.CommandText = "select * from categories_description where categories_id=" + Cat

**        rdrCatName = catNames.ExecuteReader
        Do Until rdrCatName.Read = False
            tmp = rdrCatName("categories_name")
        Loop
        rdrCatName.Close()
        catNames.Dispose()
        GetCategoryName = tmp
End Function

Suggested fix:
Dunno.

Sorry...The your code works fine. Could you pease write here table definitions? I mean categories_description and categories.

Files Attached as requested.

Could you please replace all "+" on "&" in VB code in string concatenations and test more ?

Changed source as requested all string concatenates + replaced with &.

Problem persists.

Tried to write a simple program to initialise a database then read data from an MS Access db using ODBC to read it and MySQL connector to write to the MySQL database one table at a time. Same problem had to change to using ODBC connector for the MySQL as well.

Are you sure it is not an issue with compatability with the latest Visual Studio .Net. That is the only thing I can think of if this connector has been heavily used with old versions, there are a lot of changes in VS 2005.

Michael.

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

This is expected behavior.  Without using server side cursors in the connector (something that is coming), you cannot have more than 1 datareader active on a given connection.

Please clarify what you mean when you say this is normal behaviour.

Do you mean that MySQL connector only supports one datareader?
Do you mean that MySQL command only supports one datareader?

If it is the first then what is the point of the connector?

If the second then the code adheres to the rule, there is only one datareader per command, the code recursively calls itself and a NEW command is created and executed. Again this works using ODBC.

Michael.

Michael

ADO.Net connectors typically only allow one datareader to be executing at a time on a given connection.  Try this with SQL Server.  Perform a query with SqlDataReader and, while reading it's results, execute and try to retrieve another SqlDataReader using the same connection.  It will fail unless you have activated MARS support (which MySQL does not support).

I looked at the source for the connector, the datareader is in the connector not in the command so there can be only one, short of opening multiple connectors. Although I managed to alter the code I can not get it to compile, I am not an expert in theses things so no surprise there, and have stuck with using ODBC.

If the datareader was managed by the command you could have as many readers as you like, after all you can execute multiple commands; I can open a datareader then use another command to execute nonquery or scalars (wonder about this as scalar uses a datareader to retrieve the value to return). Where you need to perform recursion it is necessary to be able to open multiple readers.

ODBC supports this, the datareader is on a per command basis not connection, thus you can have recursion.

So maybe I can make a change request for this feature?

Michael.

I have no idea what you mean by the datareader is in the connector not in the command.  Nor do I have any idea what you mean by the if datareader is managed by the command.  

It is possible for me to change the provider to support multiple simultaneous datareaders per connection but that would require having the datareader read the entire resultset at the very beginning.  This would be both very slow and be a huge memory hog.  Some ODBC drivers do this internally and that allows multiple ODBCDataReader objects to coexist on the same connection.  However, you should be aware that you are still incurring the huge memory cost and slow execution of reading the entire resultset.  The whole point of a datareader is to make a forward only "firehose" type of object that reads results off the wire as they are needed.  If you want to read the entire resultset up front, then  use the data adapter and fill a datatable.  It's the same thing.

The connector provides several components, primarily a CONNECTOR from which all else hangs. Its purpose is to establish and maintain the connection. For some reason the DATAREADER code is in the CONNECTOR code.

The next important structure is the COMMAND which takes an SQL command and a CONNECTOR as arguments. Once created you can then execute the COMMAND in several ways:

1. As a datareader - I want the datareader back so I can walk the returned data myself.
2. As a NONQUERY which will simply return the number of rows affected by the command.
3. As a SCALAR which will return the value in the first field of the first row affected.
4.  There are more but not relevant here...

If the datareader is managed at the COMMAND level then there can only be one without as you say messy code and memory management/overuse.

If the datareader is managed by the COMMAND then each instance of a COMMAND has the restriction of only one DATAREADER open at anytime but you can create multiple COMMANDS/DATAREADER sets without being restricted.

It would not be harsh on memory, each COMMAND is created, used then DISPOSED.

Take a look at the code at the start of this thread for an example.

Michael.