Bug #18939 Wrong 'primary' index due to linebreak
Submitted: 10 Apr 2006 12:42 Modified: 10 Jan 2007 13:29
Reporter: Yahoo Serious (Silver Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Query Browser Severity:S2 (Serious)
Version:1.2.4 beta OS:Windows (Windows)
Assigned to: Mike Lischke CPU Architecture:Any
Tags: Result Set

[10 Apr 2006 12:42] Yahoo Serious
Description:
Selecting from a table the Query Browser has the wrong column as 'primary key' if another index-field is present.

Indications:
a) it shows the 'primary key'-symbol at the wrong column
b) it changes multiple fileds when changing a secondary 
c) it generates an access violation in libmysqlx.dll when I try to 'Apply changes'.

What may be relevant:
I also have MySQL Admin installed, which also uses libmysqlx.dll (2004-07-16 23:18)
MySQL Query Browser has 2006-02-07 16:23)

How to repeat:
Create a tabel with a primary key - field  and another index-field. 
Probably relevant: the second index field is an int; 
Maybe relevant: the second index field is the second field; 
Probably irrelevant we use collation latin1_bin.

CREATE TABLE gios_test.test_primary2 (
  PrimaryIndex int(11) NOT NULL auto_increment,
  OtherIndex int(11) NOT NULL default '0',
  PRIMARY KEY  (PrimaryIndex),
  KEY OtherIndex (OtherIndex)
) ENGINE=MyISAM 

a) 'primary key'-symbol at the wrong column.
Select the table
  SELECT *
  FROM gios_test.test_primary2
Notice the 'primary key'-symbol at the wrong column.

b) it changes multiple fields when changing the contents of one (secondary index) field
- Fill the table:
  INSERT INTO gios_test.test_primary2
  (OtherIndex)
  VALUES (1), (2), (1), (2)
- Select all
- Edit a field in the result set (Edit, change '1' into '3', Apply changes)
- Reselect all
Notice that both fields with this value have been changed.

c) it generates an access violation when I try to 'Apply changes'.
- Add a varchar-field
  ALTER TABLE gios_test.test_primary2 ADD SomeText VARCHAR(20);
- Select all
- Edit a field in the result set (Edit, change NULL into 'a', Apply changes)
Notice that the change is not applied but instead some Access Violation appears at the bottom.

Suggested fix:
Set the right primary key.
[10 Apr 2006 14:15] MySQL Verification Team
Thank you for the bug report. Could you please attach a screenshot
of the behavior you reported, because I was unable to repeat.
Thanks in advance.
[10 Apr 2006 15:14] Yahoo Serious
Notice the 'primary key' in the wrong column

Attachment: ScreenShotWrongPrimaryIndex.jpg (image/jpeg, text), 83.80 KiB.

[10 Apr 2006 15:15] Yahoo Serious
It was a bit hard to reproduce it myself, but you need to put the FROM-clause on the next-line (maybe a newline-bug?), and of course you need the option to 'Enforce queries to be editable [...]')
[10 Apr 2006 19:40] MySQL Verification Team
might be related to http://bugs.mysql.com/bug.php?id=17920 (Access violation at address 005BC7B4 in module MySQLQueryBrowser.exe)
[11 Apr 2006 8:07] Yahoo Serious
Shane Bester probably assumes the relation with the other bug, due to the statusbar-message (Access violation at address 005BC7B4 in module MySQLQueryBrowser.exe), but my colleague also reproduced the error, without any access violations in the statusbar.  (I'll see if I can get a screenshot form him.)
[11 Apr 2006 8:18] Yahoo Serious
Wrong primary index (empty statusbar)

Attachment: ScreenShotQueryBrowserWrongPrimaryIndex.jpg (image/jpeg, text), 95.09 KiB.

[11 Apr 2006 8:19] Yahoo Serious
right primary index (empty status bar) - FROM on same line

Attachment: ScreenShotQueryBrowserRightPrimaryIndex.jpg (image/jpeg, text), 96.79 KiB.

[19 Apr 2006 17:46] MySQL Verification Team
Thank you for the feedback. Sorry still I was unable to repeat I will ask
to my coworkers to try on their side.
[19 Apr 2006 18:04] Valeriy Kravchuk
What MySQL server version you are working with? I was not able to repeat with QB 1.1.20 and 5.0.21-BK
[20 Apr 2006 7:48] Yahoo Serious
According to MySQL Administrator (1.0.7 beta):
 server: 4.1.11
 client: 5.0.0

According to Windows ODBC-datasource:
 MySQL ODBC 3.51 Driver DSN

But I can't see how this would be relevant, since MySQL Control Center (0.9.4 beta) has no problem with the same query (with or without linebreak).
[1 May 2006 8:55] Yahoo Serious
BTW: MySQL server is on Fedora Core 4, QueryBrowser is on Windows 2000 SP4
[2 May 2006 15:58] Jorge del Conde
Hi!

I was able to reproduce this bug under FC5 using 1.1.20.  It doesn't happen consistently, and it appears to occure when the columns are selected with really fast selections.

While I was able to get this to happen twice, I have not found a consistent way of reproducing it.
[12 May 2006 15:45] Yahoo Serious
I can reproduce it very consistently with and without WHERE-clause, and selecting '*' (everything) from 1 table.  If I use a NEWLINE or TAB between * and FROM I get the wrong primary index. If I don't use any whitespace between * and FROM I get no primary index! 

I am not quite sure what "really fast selections" means.  Does it point at the size of the data or at the complexity of the query?  
I tried a LIMIT-ed query of 100,000 out of 8,000,000 records and got the same result [100,000 rows fetched in 18.9680 seconds (0.0028s)].
I could test a complex WHERE-clause if you could suggest some nice functions/comparisons.
[31 May 2006 16:52] Henry G
This bug caused major problems for me when I unintentionally deleted about 235,000 customer records, and I really think it should be moved above "Non-Critical" status.

The problem is that when the primary key is mislabelled, it also affects any generated edit (or in my case delete) queries that you think would be applied normally to the selected rows.  I've been able to consistently reproduce this on Windows running Query Browser version 1.1.17 and 1.1.20.  Here's the code to create the test table (excerpt from mysqldump):

CREATE TABLE `foobar` (
  `foo` varchar(255) NOT NULL,
  `bar` varchar(255) NOT NULL,
  `primary_id` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`primary_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `foobar` VALUES ('Test','Test',1),('Foo','Bar',2),('Bar','Foo',3),('Test','Test',4);

...then run the query with the linebreak:

SELECT *
FROM foobar f;

...and you'll see that clicking "Edit", marking the last row for deletion and clicking "Apply Changes" deletes the first row as well!
[31 May 2006 16:55] Henry G
And note in the example above that it doesn't matter if any other fields are indexed, however I have noticed that removing one of the first 2 columns does seem to prevent the error.
[26 Jun 2006 9:37] Yahoo Serious
Upgrading to serious (and changing title)
[27 Jul 2006 9:01] Yahoo Serious
Is anyone working on this?  It is an annoying bug and I'd like to see it fixed.  Besides I think the program can not be called mature without this being fixed.

I am willing to help, as mentioned before I can reproduce it very consistently.
Could I help e.g. by turning on some extreme client-side logging (I mean at my QB-using PC) or using a special QB debug version: please provide me some pointers.  (At this moment I do not have the knowledge nor can I afford the time to examine source code and trace it myself.)
[4 Sep 2006 9:27] Yahoo Serious
The bug is still present in QueryBrowser 1.2.2 beta
[13 Sep 2006 11:35] Dave Wright
How we reproduce this error using 
5.0.17 engine, innodb storage, windows 2003 server
5.0r3 client tools

On the query browser, goto options and tick 'Enforce queries to be editable ....'

Logged in as root/super-user Query:
SELECT * FROM mysql.tables_priv t;

You will see the index is labeled on the Host and other fields
Change the query to:
SELECT * 
FROM mysql.tables_priv t;

And you will see the index is no longer labeled on the Host field, but is still labeled correctly on the other fields

This shows the index's being shown incorrectly, to see the index moving from one field to the other change the query to:

SELECT * FROM mysql.func f;
-- and then --
SELECT *
FROM mysql.func f;

And here you will see the index move from name (correct index) to ref (incorrect index)

Untick 'Enforce queries to be editable ....'
and repeat these queries and you will see the index's on the second query's (those with newlines) don't display the index at all on the first field.

I hope this is a clear log so these errors can be reproduced.
[31 Oct 2006 16:19] Yahoo Serious
The bug is still present in QueryBrowser 1.2.4 beta.
(BTW: my opinion is, that it can not be a release candidate with this bug still in there.)
[27 Nov 2006 13:05] Yahoo Serious
The wrong primary index with a NEWLINE (or TAB) before "FROM" seems to be fixed in 1.2.6 beta.
  SELECT *
  FROM mysql.tables_priv t;

However, all if I don't use any whitespace between * and FROM I still get no index in 1.2.6 beta: 
  SELECT *FROM mysql.tables_priv t;
[10 Jan 2007 13:29] Mike Lischke
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 bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html