Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

MYSQL table primary key to be automatic UNIQUE INDEX #856

Closed
oguzsaka opened this issue Jan 17, 2020 · 30 comments
Closed

MYSQL table primary key to be automatic UNIQUE INDEX #856

oguzsaka opened this issue Jan 17, 2020 · 30 comments
Labels
bug
Milestone

Comments

@oguzsaka
Copy link

@oguzsaka oguzsaka commented Jan 17, 2020

Firts: Sorry for some type errors, English is not my main language.

does not accept my edit and again to be UNIQUE INDEX,All primary keys in my database were UNIQUE INDEX, This happened after the last 3 updates

Version : 10.3.0.5828

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

@ansgarbecker ansgarbecker commented Jan 17, 2020

Ok, then please post

  • the result of SHOW CREATE TABLE mytable
  • and your MySQL server version
@oguzsaka

This comment has been minimized.

Copy link
Author

@oguzsaka oguzsaka commented Jan 17, 2020

CREATE TABLE region (
regid int(11) NOT NULL AUTO_INCREMENT,
Sütun 4 char(50) DEFAULT '-',
PRIMARY KEY (regid)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1
wiw

@oguzsaka

This comment has been minimized.

Copy link
Author

@oguzsaka oguzsaka commented Jan 17, 2020

MYSQL VERSİON 5.7
MYS

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

@ansgarbecker ansgarbecker commented Jan 17, 2020

MYSQL VERSİON 5.7

Your screenshot says you are on MariaDB 10.3.16 ? What's the correct version?

I created your table here two times. The first one was the code from you above, showing me a primary key in the table designer:
grafik

The second table got a unique key via "create table", and then the table designer displays a unique key. So, I see no problem here:
grafik

But probably we're really on MySQL 5.7?

@oguzsaka

This comment has been minimized.

Copy link
Author

@oguzsaka oguzsaka commented Jan 17, 2020

previously used as the primary key in the table designer

HEİDİSQL TAB Create Code click

CREATE TABLE `region` (
	`regid` INT(11) NOT NULL AUTO_INCREMENT,
	Sütun 4` CHAR(50) NULL DEFAULT '-' COLLATE 'latin1_swedish_ci',
	**UNIQUE INDEX `PRIMARY` (`regid`) USING BTREE**
)
COLLATE='latin1_swedish_ci'
;

they are the same thing but , Why does UNIQUE INDEX not show as PRIMARY KEY instead of PRIMARY

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

@ansgarbecker ansgarbecker commented Jan 17, 2020

Ok, you just named a unique key "PRIMARY". That is still a unique key, not a primary key, which is a different thing.
I'm confused about the bug here. Was that now a misunderstanding or..? Where exactly do you see a bug, and which server version is it - MySQL 5.7 or MariaDB 10.3?

@oguzsaka

This comment has been minimized.

Copy link
Author

@oguzsaka oguzsaka commented Jan 17, 2020

72618577-b3c35c80-393b-11ea-9464-bf3cfcc5fce6
MariaDB 10.3

should always be displayed as above. requesting indexs should be able to change the tab type now it cannot be changed as UNIQUE INDEX even if I update it as the primary key heidisql

I'm using mariaDB but when I use heidisql when connecting to heidisql he sees it as mariadb heidisql was fast but some different events developed after the last updates you did not used to

@oguzsaka

This comment has been minimized.

Copy link
Author

@oguzsaka oguzsaka commented Jan 17, 2020

navicat mysql is not the primary key I am not having such a problem but heidisql is changing its auto UNIQUE INDEX

@oguzsaka

This comment has been minimized.

Copy link
Author

@oguzsaka oguzsaka commented Jan 17, 2020

72618577-b3c35c80-393b-11ea-9464-bf3cfcc5fce6
I want this

@oguzsaka

This comment has been minimized.

Copy link
Author

@oguzsaka oguzsaka commented Jan 17, 2020

wiw

not this

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

@ansgarbecker ansgarbecker commented Jan 18, 2020

I understand your primary keys are wrongly detected as unique keys in HeidiSQL.
I just cannot reproduce that here, neither on MariaDB 10.3 nor on MySQL 5.7 - both just tested:
grafik
Probably there is some more stuff to examine, for example the SQL modes you have set. You could post the result of SELECT @@sql_mode here.

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

@ansgarbecker ansgarbecker commented Jan 18, 2020

Could someone else please also try to reproduce that?

@ansgarbecker ansgarbecker pinned this issue Jan 18, 2020
@oguzsaka

This comment has been minimized.

Copy link
Author

@oguzsaka oguzsaka commented Jan 18, 2020

fff

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

@ansgarbecker ansgarbecker commented Jan 20, 2020

Same report here: https://www.heidisql.com/forum.php?t=35431
I am still unable to reproduce this.

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

@ansgarbecker ansgarbecker commented Jan 20, 2020

Please post the result of the following query:

SHOW INDEXES FROM `region`;
@oguzsaka

This comment has been minimized.

Copy link
Author

@oguzsaka oguzsaka commented Jan 20, 2020

CFFF

@oguzsaka

This comment has been minimized.

Copy link
Author

@oguzsaka oguzsaka commented Jan 20, 2020

I think there is a problem with the Heidisql indexs detection engine

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

@ansgarbecker ansgarbecker commented Jan 21, 2020

Yes, that is what I am thinking too, but the logic is so simple, and it works for me. So this turned out more complex than it seems:

      NewKey.Name := KeyQuery.Col('Key_name');
      NewKey.OldName := NewKey.Name;
      if NewKey.Name.ToLower = 'primary' then
        NewKey.IndexType := 'PRIMARY'
      else if KeyQuery.Col('Non_unique') = '0' then
        NewKey.IndexType := 'UNIQUE'
      else
        NewKey.IndexType := 'KEY';

Are you on the latest build? If not please update.

@oguzsaka

This comment has been minimized.

Copy link
Author

@oguzsaka oguzsaka commented Jan 21, 2020

I'm in the latest version. actual . Version 9 does not have such a problem

NewKey.Name := KeyQuery.Col('Key_name');
can you show the incoming values ​​by testing. because it might perceive the incoming value differently

if NewKey.Name.ToLower = 'primary' then NewKey.IndexType := 'PRIMARY'

may return a different value

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

@ansgarbecker ansgarbecker commented Jan 21, 2020

Good idea. Next build will have a debug output in the lower log panel, saying NewKey.Name="PRIMARY". As this is a debug message, you need to activate these in preferences:

  • rightclick log panel, and click "Logging preferences":
    grafik
  • activate debug messages:
    grafik
  • apply options, and watch out for such a line in the log panel (you may need to scroll up a bit after clicking a table):
    grafik
@oguzsaka

This comment has been minimized.

Copy link
Author

@oguzsaka oguzsaka commented Jan 21, 2020

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

@ansgarbecker ansgarbecker commented Jan 25, 2020

Even in your log I see now NewKey.Name="PRIMARY", and I'm sure in lowercase that's "primary". I'm lost here.

@oguzsaka

This comment has been minimized.

Copy link
Author

@oguzsaka oguzsaka commented Jan 28, 2020

ALTER TABLE region
DROP INDEX PRIMARY,
ADD PRIMARY KEY (regid) USING BTREE;
11
2
3

@oguzsaka

This comment has been minimized.

Copy link
Author

@oguzsaka oguzsaka commented Jan 28, 2020

4

I think it gives the value in the last condition because it satisfies two conditions
the problem is here
else if KeyQuery.Col('Non_unique') = '0' then

`
you have to change it this way

  NewKey.OldName := NewKey.Name;
  if NewKey.Name.ToLower = 'primary' then
    NewKey.IndexType := 'PRIMARY'
  else if KeyQuery.Col('Non_unique') = '0' and NewKey.Name.ToLower != 'primary' then
    NewKey.IndexType := 'UNIQUE'
  else
    NewKey.IndexType := 'KEY';`

`

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

@ansgarbecker ansgarbecker commented Jan 28, 2020

No, the if ... else ... construct prefers the first one if that already matches. It cannot prefer both.

@oguzsaka

This comment has been minimized.

Copy link
Author

@oguzsaka oguzsaka commented Jan 29, 2020

Can you print NewKey.IndexType as log?

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

@ansgarbecker ansgarbecker commented Jan 29, 2020

Next build should show some more details, like this:

/* NewKey.Name="PRIMARY", NewKey.Name.ToLower="primary", NewKey.IndexType="PRIMARY" */
@oguzsaka

This comment has been minimized.

Copy link
Author

@oguzsaka oguzsaka commented Jan 29, 2020

/* NewKey.Name="PRIMARY", NewKey.Name.ToLower="prımary", NewKey.IndexType="UNIQUE" */
5

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

@ansgarbecker ansgarbecker commented Jan 30, 2020

Ok, your "prımary" has an "i" without dot on it, which is I guess a localization issue in the ToLower method. Will try to change that without locale.

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

@ansgarbecker ansgarbecker commented Jan 30, 2020

Got it, hopefully. Preferring ToLowerInvariant now, as described on https://stackoverflow.com/questions/6225808/string-tolower-and-string-tolowerinvariant - that thread talks about exactly the same character differences, in the Turkish "i".

Please check in the next build.

@ansgarbecker ansgarbecker added this to the v11.0 milestone Jan 30, 2020
@ansgarbecker ansgarbecker unpinned this issue Jan 31, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants
You can’t perform that action at this time.