| Bug #11490 | certain incorrect queries trigger connection must be valid and open message | ||
|---|---|---|---|
| Submitted: | 21 Jun 2005 22:12 | Modified: | 2 Nov 2005 16:05 |
| Reporter: | Mathieu van Loon | ||
| Status: | Closed | ||
| Category: | Connector/Net | Severity: | S3 (Non-critical) |
| Version: | 1.0.4 | OS: | Microsoft Windows (XP professional) |
| Assigned to: | Reggie Burnett | Target Version: | |
[22 Jun 2005 13:01]
Vasily Kishkin
Could you please write here a definition of table "hosts" ?
[22 Jun 2005 13:08]
Mathieu van Loon
oops. I should have written
MySqlHelper.ExecuteReader(ConnectionString, "SELECT * FROM host
INTENTIONAL_SYNTAX_ERROR");
and
MySqlHelper.ExecuteReader(ConnectionString, "SELECT * FROM host WHERE
TIME('2005/6/21')");
This way you can run the query against the default mysql database.
[22 Jun 2005 13:30]
Vasily Kishkin
I was able to repeat the bug. Thanks. Tested on Win 2000 Sp4, Microsoft Visual C# .NET , Connector .NET 1.0.4 Test case is attached.
[22 Jun 2005 21:03]
Reggie Burnett
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.
If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information
about accessing the source trees is available at
http://www.mysql.com/doc/en/Installing_source_tree.html
Additional info:
Fixed in 1.0.5 and all 2.0.0 releases
[23 Jun 2005 19:24]
Mike Hillyer
Documented in the 1.0.5 and 2.0.0 changelogs: <listitem><para>Certain malformed queries will trigger a "Connection must be valid and open" error message. (Bug #11490)</para></listitem>
[2 Nov 2005 15:21]
Matthew Lord
Looks like this may have creeped back into 1.0.6.
[29 Dec 2005 18:49]
chris clement
I am getting this msg on code that once worked. It is a followup UPDATE from a modified
grid. It worked in Access 2000 but will not work w MySQL 5.0.16-nt.
This is the code as it is at present. There are many commented lines that serve as a
guide or refer to the Access tie-in. The message appears when I try to see the UPDATE
code, or perform it marked with ****. I have XP pro and VBNET 2003.
Private Sub update2() 'from grid
'get a timestamp, formatted, w year 1st
temp3 = Date.Now.Year & "/" & Date.Now.Month & "/" & Date.Now.Day & " " &
Date.Now.Hour & ":" & Date.Now.Minute & ":" & Date.Now.Second
If m_DataSet.HasChanges() Then
'Dim db3 As MySql.Data.MySqlClient.MySqlConnection = db5
Dim data_adapter2 As New MySqlDataAdapter
' Dim command_builder As OleDb.OleDbCommandBuilder
Dim command_builder = New
MySql.Data.MySqlClient.MySqlCommandBuilder(data_adapter2)
'MySql.Data.MySqlClient
' Create the DataAdapter.
data_adapter2 = New MySqlDataAdapter(sqlString, "server=localhost;user
id=root;password=r00t;database=SIUDB") 'db3)
' Map Table to Contacts.
data_adapter2.TableMappings.Add("Table", "CREDITS")
' Make the CommandBuilder generate the
' insert, update, and delete commands.
command_builder = New
MySql.Data.MySqlClient.MySqlCommandBuilder(data_adapter2)
'dim command_builder = New _
' OleDb.OleDbCommandBuilder(data_adapter)
' Uncomment this code to see the INSERT,
' UPDATE, and DELETE commands.
'Debug.WriteLine("*** INSERT ***")
'Debug.WriteLine(command_builder.GetInsertCommand.CommandText)
'Debug.WriteLine("*** UPDATE ***")
'Debug.WriteLine(command_builder.GetUpdateCommand.CommandText)
'Debug.WriteLine("*** DELETE ***")
'Debug.WriteLine(command_builder.GetDeleteCommand.CommandText)
'ListBox1.Items.Add(command_builder.GetInsertCommand.CommandText)
ListBox1.Items.Add(command_builder.GetUpdateCommand.CommandText) '****
'ListBox1.Items.Add(command_builder.GetDeleteCommand.CommandText)
'An unhandled exception of type 'System.InvalidOperationException' occurred
in microsoft.visualbasic.dll
'Additional information: Connection must be valid and open
' Save the changes.
data_adapter2.Update(m_DataSet) '****
'www.vb-helper.com/howto_net_datagrid.html
End If
' sqlString = "SELECT * FROM CREDITS
'where StudentNo=" & StudentNoSave & " ORDER BY YEARC,SEMESTER,COURSEID"
' If m_DataSet.HasChanges() Then
' Dim data_adapter As OleDb.OleDbDataAdapter
' Dim command_builder As OleDb.OleDbCommandBuilder
' ' Create the DataAdapter.
' data_adapter = New OleDb.OleDbDataAdapter(sqlString,
"Provider=Microsoft.Jet.OLEDB.4.0;Jet 'OLEDB:Database Password=sirhc;Data
source=SIUdb.mdb")
' ' Map Table to Contacts.
' data_adapter.TableMappings.Add("Table", "CREDITS")
' ' Make the CommandBuilder generate the
' ' insert, update, and delete commands.
' command_builder = New _
' OleDb.OleDbCommandBuilder(data_adapter)
' ' Uncomment this code to see the INSERT,
' ' UPDATE, and DELETE commands.
' Debug.WriteLine("*** INSERT ***")
' Debug.WriteLine(command_builder.GetInsertCommand.CommandText)
' Debug.WriteLine("*** UPDATE ***")
' Debug.WriteLine(command_builder.GetUpdateCommand.CommandText)
' Debug.WriteLine("*** DELETE ***")
' Debug.WriteLine(command_builder.GetDeleteCommand.CommandText)
' ' Save the changes.
' data_adapter.Update(m_DataSet)
' 'www.vb-helper.com/howto_net_datagrid.html
' End If
End Sub
[29 Dec 2005 18:56]
chris clement
Additionally in the above code. I was never able to pass the connection string in as a variable, getting some absurd "unable to cast a string" msg. I have to use the literal to fill the flexgrid and here to update it. Rest of program (with textboxes) works fine with a symbolic variable.
[5 Apr 2006 20:20]
Jacob Anderson
I have a similar problem where I specify a date as "03-01-2006" (in western notation as MM-DD-YYYY), and I get warning 1292 during the query. That also triggers this error: System.InvalidOperationException: Connection must be valid and open at MySql.Data.MySqlClient.MySqlCommand.CheckState() at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader() at MySql.Data.MySqlClient.Driver.ShowWarnings(Int32 count) at MySql.Data.MySqlClient.NativeDriver.ReadEOF(Boolean readPacket) at MySql.Data.MySqlClient.NativeDriver.OpenDataRow(Int32 fieldCount, Boolean isBinary) at MySql.Data.MySqlClient.CommandResult.Load() at MySql.Data.MySqlClient.MySqlDataReader.NextResult() at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior MYSQL: mysql Ver 14.7 Distrib 4.1.12a, for Win32 (ia32) Connector: MySql.Data.dll version 1.0.4.20163
Description: Certain malformed queries will trigger a "Connection must be valid and open" error message. This appears to be incorrect, because normally a "You have an error in your SQL syntax" message appears on malformed queries. Some remarks: - The issue is present when using the MysqlHelper class. I have not tried circumventing the MysqlHelper. - The issue seems fairly difficult to trigger. I have only been able to trigger it with an incorrect TIME('some_date') query fragment. - The issue is clearly not urgent. It is however undesirable that a misleading error message is shown. the database connection is valid and open after all, contrary to what is suggested. How to repeat: 1. Create a .NET project. Issue the following query from some method: MySqlHelper.ExecuteReader(ConnectionString, "SELECT * FROM hosts INTENTIONAL_SYNTAX_ERROR"); Notice that the error message returned is a "You have an error in your ...". 2. Create a .NET project. Issue the following query from some method: MySqlHelper.ExecuteReader(ConnectionString, "SELECT * FROM hosts WHERE TIME('2005/6/21')"); Notice that the error message returned is a "connection must be valid and open".