Duplicate entry

User, date Message
Written by BubikolRamios
1 year ago
Category: General
275 posts since Thu, 14 Jan 10 		Trying to set up unique key, getting
'Duplictae entry on key blabla...'

Is that shortening to ... HSQL or MYSQL thing ?

In status bar, message is a few charaters longer, but not complete, at least there should be complete ?

That would be a big help as I can't find what is the prob on table otherwise.
Written by BubikolRamios
1 year ago
275 posts since Thu, 14 Jan 10 		Hmm, was playing with toad for Mysql on that problem.
It complained that I cant set up unique key coz data to long.

So I converted a field from TEXT to varchar(300) and no more complaints about 'Duplictae entry' in HSQL. Success on unique key creation !

I guess HSQL should do the same, first notify that key to long ?
Or is it db that pops out wrong message ?
Written by ansgar
1 year ago
4029 posts since Fri, 07 Apr 06 		Both messages come from the server. "Duplicate entry" is caused by concurring rows with same content on the wanted key columns. "Data too long" is of course caused by some non-VARCHAR column. HeidiSQL and Toad should have said exactly the same, unless you involved different columns with different types.
Written by BubikolRamios
1 year ago
275 posts since Thu, 14 Jan 10 		Nope , tested it again.
1. removed index
2. changed from varchar to TEXT
3. add index ---> duplicate entry (doh there is no duplicate entry)

will send you test case table ....
Written by ansgar
1 year ago
4029 posts since Fri, 07 Apr 06 		I just tested with your SQL dump you sent me via PM:

After changing baseURL from TEXT to VARCHAR(300), I tried to add a unique key on identifier+baseURL, and get this error:

ALTER TABLE `stat_session_for_display`
ADD UNIQUE INDEX `identifier_baseURL` (`identifier`, `baseURL`);
/* SQL Error (1062): Duplicate entry 'google_by_states-http://www.google.am' for key 'identifier_baseURL' */



So, what's your problem now? Toad said nothing like that? Can't believe you did the same in Toad then - the server, not Toad, throws the error.
Written by BubikolRamios
1 year ago
275 posts since Thu, 14 Jan 10 		Mistery to me how you got that duplicate, it is different as mine.

Run this, for your duplicate:

select identifier, count(baseURL)
from stat_session_for_display
where identifier = 'google_by_states'
and baseURL = 'http://www.google.am'
group by identifier

or other way around:

select count(identifier), baseURL
from stat_session_for_display
where identifier = 'google_by_states'
and baseURL = 'http://www.google.am'
group by baseURL

You will get count 1, so , there is no duplicate, right ?



I'can repeat what I claim, there is no duplicate in that file/table.


1.try set up index: duplicate
2.change TEXT to warchar(300)
3.se up index: done !

checked:
select max(length(baseURL)) from stat_session_for_display
--> 277, so there is no truncate by varchar(300), and I think there would be warning from db anyway.

OK, unpacked exact same file as I send to you, to db again, via open .sql with heidi sql & run.

HSQL: 7.0.0.4058
MYSQL: 5.1.61-community

All sql-s created by HSQL.

ALTER TABLE `stat_session_for_display`
ADD UNIQUE INDEX `Index 1` (`baseURL`(100), `identifier`);
/* SQL Error (1062): Duplicate entry 'http://agrozoo.net/jsp/Galery_list_of_authors.jsp?c_author=Http:' for key 'Index 1' */
ALTER TABLE `stat_session_for_display`
CHANGE COLUMN `baseURL` `baseURL` VARCHAR(300) NULL COLLATE 'utf8_slovenian_ci' AFTER `identifier`;
SELECT `DEFAULT_COLLATION_NAME` FROM `information_schema`.`SCHEMATA` WHERE `SCHEMA_NAME`='test';
SHOW TABLE STATUS FROM `test`;
SHOW FUNCTION STATUS WHERE `Db`='test';
SHOW PROCEDURE STATUS WHERE `Db`='test';
SHOW TRIGGERS FROM `test`;
SHOW EVENTS FROM `test`;
SHOW CREATE TABLE `test`.`stat_session_for_display`;
/* Entering session "local" */
ALTER TABLE `stat_session_for_display`
ADD UNIQUE INDEX `Index 1` (`baseURL`, `identifier`);
SELECT `DEFAULT_COLLATION_NAME` FROM `information_schema`.`SCHEMATA` WHERE `SCHEMA_NAME`='test';
SHOW TABLE STATUS FROM `test`;
SHOW FUNCTION STATUS WHERE `Db`='test';
SHOW PROCEDURE STATUS WHERE `Db`='test';
SHOW TRIGGERS FROM `test`;
SHOW EVENTS FROM `test`;
SHOW CREATE TABLE `test`.`stat_session_for_display`;
/* Entering session "local" */
Written by BubikolRamios
1 year ago
275 posts since Thu, 14 Jan 10 		tested on win 7, 64 bit, I might add.
Written by ansgar
1 year ago
4029 posts since Fri, 07 Apr 06

select identifier, count(baseURL)
from stat_session_for_display
where identifier = 'google_by_states'
and baseURL = 'http://www.google.am'
group by identifier



identifier|count(baseURL)
google_by_states|348

select count(identifier), baseURL
from stat_session_for_display
where identifier = 'google_by_states'
and baseURL = 'http://www.google.am'
group by baseURL



count(identifier)|baseURL
348|http://www.google.am

Again, what's your problem with HeidiSQL currently? This is time consuming, man.
Written by BubikolRamios
1 year ago
275 posts since Thu, 14 Jan 10 		OK drop that. There is obviously one big weirdness in between.

I get count 1(using HSQL). If you look at anse.sql (23435 lines total) as text file you should found only one occurance of 'http://www.google.am', so, have no idea how you get to 348.
Written by ansgar
1 year ago
4029 posts since Fri, 07 Apr 06 		Sorry for the confusion. I rechecked the dump and noticed the import did some unwanted loop as I checked "Run batch in one go". However, I now have some thousand rows and I created the unique key without errors.

What's next?
Written by BubikolRamios
1 year ago
275 posts since Thu, 14 Jan 10 		Ok, sure of unique & still having TEXT type ?
That should pop up 'data to long' on UNIQUE INDEX creation as TOAD did.
But heidi pops duplicate key instead.

That would be it.
Written by ansgar
1 year ago
4029 posts since Fri, 07 Apr 06 		Yes:

ALTER TABLE `stat_session_for_display` ADD UNIQUE INDEX `identifier_baseURL` (`identifier`, `baseURL`(100));
/* SQL Error (1062): Duplicate entry 'top hits-http://agrozoo.net/jsp/Galery_list_of_authors.jsp?c_aut' for key 'identifier_baseURL' */



And now, please post the ALTER/UNIQUE query from Toad so we can see the difference.
Written by BubikolRamios
1 year ago
275 posts since Thu, 14 Jan 10 		1.Toad has left click on table: edit duplicate data
--> no duplicates found
2.
select s.identifier,s.baseURL, count(*)
from stat_session_for_display s
group by s.identifier, s.baseURL
order by count(*) asc

No record with count > 1 ! So again there is no duplicates.

3. To sum up, there is no duplicates in table, but , if I paste ALTER from previous post to TOAD, I get same: duplicate .... So I guess error in mysql itself ?

4. Testing TOAD index creation wizard - TOAD does not show what is create index code(at least to my knowledge) from it's create index wizard , but pops up:
BLOB/TEXT column 'baseURL' used in key specification without a key length
see this: http://stackoverflow.com/questions/1827063/mysql-error-key-specification-without-a-key-length

As far as I see this message comes from db, but looks like heidi does not see it.Or something.


5. can't recreate "Data too long" now, but sure TOAD gave me that at one point, probably when I converted TEXT to VARCHAR but varchar to long.

'BLOB/TEXT column used in key specification without a key length' mesage would be ok instead of 'Duplicate' which is wrong. TOAD seems to see that somehow.
Written by ansgar
1 year ago
4029 posts since Fri, 07 Apr 06 		In HeidiSQL, you specified a key length for baseURL, man:

...ADD UNIQUE INDEX `Index 1` (`baseURL`(100), `identifier`);


If you would not have done so, you would get this "without a key length" error.

Now, you're about to use the first 100 chars of baseURL for the index. That means you can have two different values which cause a duplicate warning because the first 100 chars are the same. Find out:

select s.identifier,LEFT(s.baseURL, 100), count(*)
from stat_session_for_display s
group by s.identifier, LEFT(s.baseURL, 100)
order by count(*) asc
Written by ansgar
1 year ago
4029 posts since Fri, 07 Apr 06 		Ah, and some cosmetics, e.g. sort DESC to see >1 count's at the top:

SELECT s.identifier, LEFT(s.baseURL, 100) AS baseURL_short, COUNT(*) AS cnt
FROM stat_session_for_display AS s
GROUP BY s.identifier, baseURL_short
HAVING cnt > 1
ORDER BY cnt DESC
Written by BubikolRamios
1 year ago
275 posts since Thu, 14 Jan 10 		Note: this (100) is added by Heidi automaticaly as you drag drop text field to new index. I saw it, but ignored, I gues, I thought, it must be right as wizard added it.
Written by ansgar
1 year ago
4029 posts since Fri, 07 Apr 06 		Hehe, phew, glad we fixed that. happy
 

Please login to leave a reply, or register at first.