Duplicate entry

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.

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 ?

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.

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 ....

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.

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" */

tested on win 7, 64 bit, I might add.

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.

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.

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?

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.

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.

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.

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

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

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.

Hehe, phew, glad we fixed that.