| Bug #33401 | Select returns wrong number of records | ||
|---|---|---|---|
| Submitted: | 20 Dec 2007 13:21 | Modified: | 31 Dec 2007 12:50 |
| Reporter: | kris Bekkers | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | Connector / NET | Severity: | S3 (Non-critical) |
| Version: | 5.1.4 | OS: | Windows |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | connector.net, falcon preview, SELECT | ||
[31 Dec 2007 8:42]
Tonci Grgin
Hi Kris and thanks for your report. Can you please try the same test in mysql command line client and inform me of result? If my suspicion is right, you'll see the same error there.
[31 Dec 2007 9:08]
kris Bekkers
Tonci,
Here are the results of the test:
Mysql Falcon Preview (6..3 Alpha Community)
Using the command line utility
Here it acts correct returning no records for the query.
Enter password: ********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 6.0.3-alpha-community MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use test_select;
Database changed
mysql> select * from app_messages
-> where app_name <> 'FraClaDocMaint'
-> and FrameWork = 'Y'
-> and Language = 'NL';
Empty set (0.00 sec)
I've tried the same with "Aqua Data Studio 4.7" and that is also correct: it returns no records.
Hope this helps.
Kris
[31 Dec 2007 10:44]
Tonci Grgin
Kris, if I understood correctly mysql cl client returns correct number of rows (0), right? I'm testing c/NET now.
[31 Dec 2007 10:52]
kris Bekkers
Tonci, That's correct. Returning 0 (correct) numbers of records: - Mysql Client Command line utility - Aqua Data studio Returning al (wrong) number of records: Connector.NET But only with the given configuration: falcon preview + Connector.NET With the GA version of the mysql server it works correct. Kris
[31 Dec 2007 12:50]
Tonci Grgin
Kris, I took 5.1.4 and 6.0.3 from our download pages and can't repeat reported behavior (i.e. datareader returns no records). Will attach test case from different machine in a while. As for your report, in the future you might consider: 1 Attaching mysqldump of data and table structure (you have line breaks so I had to edit insert lines manually) 2 Attach full C# test case What I *think* could be wrong is that you have old c/NET driver registered in assembly cache. Can you please check on this condition?
[31 Dec 2007 12:53]
Tonci Grgin
MySqlConnection conn = new MySqlConnection();
MySqlDataReader dr;
conn.ConnectionString = "DataSource=localhost;Database=test;UserID=root;Password=*****;PORT=3306;Allow Zero Datetime=True;logging=True;";
conn.Open();
MySqlCommand command = new MySqlCommand();
command.Connection = conn;
MySqlCommand cmdCreateTable = new MySqlCommand("select * from app_messages where app_name <> 'FraCladocMaint' and Framework = 'Y' and Language = 'NL'", conn);
cmdCreateTable.CommandType = CommandType.Text;
cmdCreateTable.CommandTimeout = 0;
try
{
dr = cmdCreateTable.ExecuteReader();
MessageBox.Show("Before READ");
while (dr.Read())
{
MessageBox.Show("After READ");
MessageBox.Show(dr.RecordsAffected.ToString());
}
dr.Close();
cmdCreateTable.Dispose();
conn.Close();
MessageBox.Show("DONE");
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
Output are message boxes "Before READ" and "DONE" as expected.
Description: A select from a table that should return no records does return records. How to repeat: Create the database 'test' (script below) create the table 'app_messages' (script below) insert the values (scrîpt below) Following command is issued using the connector.NET (creating the connection, issuing a command with the commandtext set). select * from app_messages where app_name <> 'FraCladocMaint' and Framework = 'Y' and Language = 'NL' This is fine (correct) when having the .NET Connector 5.1.4.0 in combination with the release candidate 5.1.22-rc-community of the mysql server. (or lower version of the server) However when issuing the same code with the connector.NET 5.1.4.0 and the falcon version (latest available) it does return all the records in it. The cause is unknown to me, but could be either the Mysql falcon preview or the connector.NET. It seems however strange that the mysql commandline utility does the select correct, therefore I'm posting this in the connector.NET bugs system. ------------------------------------------------------------------ drop database if exists test_Select go create database test_Select go drop table if exists app_messages go CREATE TABLE `app_messages` ( `message_id` varchar(50) NOT NULL DEFAULT '', `Language` varchar(5) NOT NULL DEFAULT '', `Message` varchar(255) NOT NULL DEFAULT '', `app_name` varchar(100) NOT NULL DEFAULT '', `FrameWork` enum('Y','N') NOT NULL DEFAULT 'N', `DELETED` enum('Y','N') NOT NULL DEFAULT 'N', `CRE_USER` varchar(50) DEFAULT NULL, `CRE_TIME` datetime DEFAULT NULL, `UPD_USER` varchar(50) DEFAULT NULL, `UPD_TIME` datetime DEFAULT NULL, PRIMARY KEY (`message_id`,`Language`,`app_name`), KEY `Language` (`Language`), KEY `app_name` (`app_name`), KEY `message_id_2` (`message_id`), KEY `CRE_USER` (`CRE_USER`), KEY `CRE_TIME` (`CRE_TIME`), KEY `UPD_USER` (`UPD_USER`), KEY `UPD_TIME` (`UPD_TIME`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 go Insert into app_messages(`CRE_USER`,`Message`,`CRE_TIME`,`Language`,`UPD_TIME`,`DELETED`,`app_name`,`FrameWork`,`message_id`,`UPD_USER`) Values ('SYSTEM','Error retrieving the help location for the message %1','2006-05-18 12:56:51','EN',null,'N','FraClaDocMaint','Y','FWRK_R_HelpUrl',null) go Insert into app_messages(`CRE_USER`,`Message`,`CRE_TIME`,`Language`,`UPD_TIME`,`DELETED`,`app_name`,`FrameWork`,`message_id`,`UPD_USER`) Values ('SYSTEM','Fout bij het ophalen van de vertalingen voor het menu %1','2006-05-18 12:56:51','NL',null,'N','FraClaDocMaint','Y','FWRK_R_Col_TransMenu',null) go Insert into app_messages(`CRE_USER`,`Message`,`CRE_TIME`,`Language`,`UPD_TIME`,`DELETED`,`app_name`,`FrameWork`,`message_id`,`UPD_USER`) Values ('SYSTEM','Error saving the security settings for the element %1','2006-05-18 12:56:51','EN',null,'N','FraClaDocMaint','Y','FWRK_S_ElementSecurity',null) go Insert into app_messages(`CRE_USER`,`Message`,`CRE_TIME`,`Language`,`UPD_TIME`,`DELETED`,`app_name`,`FrameWork`,`message_id`,`UPD_USER`) Values ('SYSTEM','Error deleting the form %1','2006-05-18 12:56:51','EN',null,'N','FraClaDocMaint','Y','FWRK_D_ApplicationForm',null) go Insert into app_messages(`CRE_USER`,`Message`,`CRE_TIME`,`Language`,`UPD_TIME`,`DELETED`,`app_name`,`FrameWork`,`message_id`,`UPD_USER`) Values ('SYSTEM','Error saving the menu %1','2006-05-18 12:56:51','EN',null,'N','FraClaDocMaint','Y','FWRK_S_Menu',null) go Insert into app_messages(`CRE_USER`,`Message`,`CRE_TIME`,`Language`,`UPD_TIME`,`DELETED`,`app_name`,`FrameWork`,`message_id`,`UPD_USER`) Values ('SYSTEM','Fout bij het bewaren van de element group %1','2006-05-18 12:56:51','NL',null,'N','FraClaDocMaint','Y','FWRK_S_ElementGroup',null) go Insert into app_messages(`CRE_USER`,`Message`,`CRE_TIME`,`Language`,`UPD_TIME`,`DELETED`,`app_name`,`FrameWork`,`message_id`,`UPD_USER`) Values ('SYSTEM','Error saving the form elements collection for the form %1','2006-05-18 12:56:51','EN',null,'N','FraClaDocMaint','Y','FWRK_S_COL_FormElement',null) go Insert into app_messages(`CRE_USER`,`Message`,`CRE_TIME`,`Language`,`UPD_TIME`,`DELETED`,`app_name`,`FrameWork`,`message_id`,`UPD_USER`) Values ('SYSTEM','Fout bij het ophalen van de vertalingen voor het element %1','2006-05-18 12:56:51','NL',null,'N','FraClaDocMaint','Y','FWRK_R_COL_Translation',null) go Insert into app_messages(`CRE_USER`,`Message`,`CRE_TIME`,`Language`,`UPD_TIME`,`DELETED`,`app_name`,`FrameWork`,`message_id`,`UPD_USER`) Values ('SYSTEM','Error saving the application user %1','2006-05-18 12:56:51','EN',null,'N','FraClaDocMaint','Y','FWRK_S_ApplicationUser',null) go Insert into app_messages(`CRE_USER`,`Message`,`CRE_TIME`,`Language`,`UPD_TIME`,`DELETED`,`app_name`,`FrameWork`,`message_id`,`UPD_USER`) Values ('SYSTEM','Het record %1 kan niet verwijderd worden omdat er onderliggende data gevonden is.','2006-05-18 12:56:51','NL',null,'N','FraClaDocMaint','Y','Valid_RecordNotDeleted',null) go Insert into app_messages(`CRE_USER`,`Message`,`CRE_TIME`,`Language`,`UPD_TIME`,`DELETED`,`app_name`,`FrameWork`,`message_id`,`UPD_USER`) Values ('SYSTEM','Fout bij het verwijderen van de security settings voor het element %1','2006-05-18 12:56:51','NL',null,'N','FraClaDocMaint','Y','FWRK_D_ElementSecurity',null) go Insert into app_messages(`CRE_USER`,`Message`,`CRE_TIME`,`Language`,`UPD_TIME`,`DELETED`,`app_name`,`FrameWork`,`message_id`,`UPD_USER`) Values ('SYSTEM','Fout bij het verwijderen van het element %1','2006-05-18 12:56:51','NL',null,'N','FraClaDocMaint','Y','FWRK_D_FormElement',null) go Insert into app_messages(`CRE_USER`,`Message`,`CRE_TIME`,`Language`,`UPD_TIME`,`DELETED`,`app_name`,`FrameWork`,`message_id`,`UPD_USER`) Values ('SYSTEM','Error deleting the message %1','2006-05-18 12:56:51','EN',null,'N','FraClaDocMaint','Y','FWRK_D_Message',null) go Insert into app_messages(`CRE_USER`,`Message`,`CRE_TIME`,`Language`,`UPD_TIME`,`DELETED`,`app_name`,`FrameWork`,`message_id`,`UPD_USER`) Values ('SYSTEM','Error deleting the application user %1','2006-05-18 12:56:51','EN',null,'N','FraClaDocMaint','Y','FWRK_D_User',null) go Insert into app_messages(`CRE_USER`,`Message`,`CRE_TIME`,`Language`,`UPD_TIME`,`DELETED`,`app_name`,`FrameWork`,`message_id`,`UPD_USER`) Values ('SYSTEM','Error deleting the help location for the message %1','2006-05-18 12:56:51','EN',null,'N','FraClaDocMaint','Y','FWRK_D_HelpUrl',null) go Insert into app_messages(`CRE_USER`,`Message`,`CRE_TIME`,`Language`,`UPD_TIME`,`DELETED`,`app_name`,`FrameWork`,`message_id`,`UPD_USER`) Values ('SYSTEM','U geeft aan niets te willen loggen. Dit zorgt ervor dat er geen boodshappen meer getoond worden. Dit is erg gevaarlijk. Wilt u doorgaan?','2006-05-18 12:56:51','NL',null,'N','FraClaDocMaint','Y','INFO_NoLogging',null) go