Unable to insert two different data into MYSQL DB - shows duplicate entry

Question

I have the below table, with the following details:

Feilds       | type    | null | key 
-----------------------------------

access_token | text    | No   | PRI
scope        | varchar | Yes  | MUL
client_id    | varchar | Yes  | MUL

when i executed the below query, it inserted.

insert into access_tokens(access_token,refresh_token,expires_in,scope,type,client_id,
        code_id,user_id,refresh_expires_in,created) values('
        eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiJ9.eyJVc2VyIjoie1widXNlcklkXCI6OCxcInVzZXJOYW1lXCI6XCJQb29qYVwiLFwiYWRtaW5GbGFnXCI6MixcImZpcnN0TmFtZVwiOlwiUG9vamFcIixcImxhc3ROYW1lXCI6XCJSYWlrYXJcIixcImlzTG9nZ2VkSW5cIjpmYWxzZSxcImFjY291bnRcIjp7XCJhY2NvdW50SWRcIjoxMCxcImFjY291bnROYW1lXCI6XCJUaGJzLWNvdXBsZXJcIn0sXCJjYXBcIjpbMSwyLDMsNCw1LDYsNyw4LDksMTAsMTEsMTIsMTMsMTQsMTUsMTYsMTcsMTgsMTksMjAsMjEsMjIsMjMsMjQsMjUsMjYsMjcsMjgsMjksMzAsMzEsMzIsMzMsMzQsMzUsMzYsMzcsMzgsMzksNDAsNDEsNDIsNDMsNDQsNDUsNDYsNDcsNDgsNDksNTAsNTEsNTIsNTMsNTQsNTUsNTYsNTcsNTgsNTksNjAsNjEsNjIsNjMsNjQsNjUsNjYsNjcsNjgsNjksNzAsNzEsNzIsNzMsNzQsNzUsNzYsNzcsNzgsNzksODAsODEsODIsODMsODQsODUsODYsODcsODgsODksOTAsOTEsOTIsOTMsOTQsOTUsOTYsOTcsOTgsOTksMTAwLDEwMSwxMDIsMTAzLDEwNCwxMDUsMTA2LDEwNywxMDgsMTA5LDExMCwxMTEsMTEyLDExMywxMTQsMTE1LDExNiwxMTcsMTE4LDExOSwxMjAsMTIxLDEyMiwxMjMsMTI0LDEyNSwxMjYsMTI3LDEyOCwxMjksMTMwLDEzMSwxMzIsMTMzLDEzNCwxMzUsMTM2LDEzNywxMzgsMTM5LDE0MCwxNDEsMTQyLDE0MywxNDQsMTQ1LDE0NiwxNDcsMTQ4LDE0OSwxNTAsMTUxLDE1MiwxNTMsMTU0LDE1NSwxNTYsMTU3LDE1OCwxNTksMTYwLDE2MSwxNjIsMTYzLDE2NCwxNjUsMTY2LDE2NywxNjgsMTY5LDE3MCwxNzEsMTcyLDE3MywxNzQsMTc1LDE3NiwxNzcsMTc4LDE3OSwxODAsMTgxLDE4MiwxODMsMTg0LDE4NSwxODYsMTg3LDE4OCwxODksMTkwLDE5MSwxOTIsMTkzLDE5NCwxOTUsMTk2LDE5NywxOTgsMTk5LDIwMF0sXCJkZWZhdWx0RW52aXJvbm1lbnRcIjp7fSxcImNsaWVudElkXCI6XCIyYjhhYTdlMTQ2ZjE3NWJkNjE2OWIzZGNkZDA4MDZkNDk3N2ZlMjBmXCJ9IiwiaWF0IjoxNTg0NDI4MzE1fQ.OsH8DNIOMe4fnAsMyVE6nr3m2Q0rfd_w8a6CQQT4ONq__Al6_qVGzBKuOgRVYKlfn_kqdP7HxCPFn2GPRpSzirzyLDKGjAgR0KRbB5rYHzj_KhkSZaJV29qUTlH1go80q5uXamjCVi7byzPRw4JxuxBcjtX3Z3eC74_xPMGegjSRWDNNJuMeQiHNTevvzAjXH5mmRexAw7czmkTHvYT3UqrzR24kYdd3Rnash8TD5dG-uO09Tv664NQls3hX6Ai5QcwLjLfi0rUtBmVL40Q0JeQe3v8H1F-kx3xqWnNH7JDq47YvfMGa2j4ChR_1I-5eyNN_oDI_hKVRmaTCIMZ6',
        'ec944e165a28a02edbce25ffd8a405aefa95cace6a35a7576acf5a0217f9c1c1',
        '3600','basic','Bearer','2b8aa7e146fb3dcdd0806d4977fe20f','','8',
        '7200',1584428315142);

Now i have to insert another row, with different data,

insert into access_tokens(access_token,refresh_token,expires_in,scope,type,client_id,
        code_id,user_id,refresh_expires_in,created) values(
        'eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiJ9.eyJVc2VyIjoie1widXNlcklkXCI6OCxcInVzZXJOYW1lXCI6XCJQb29qYVwiLFwiYWRtaW5GbGFnXCI6MixcImZpcnN0TmFtZVwiOlwiUG9vamFcIixcImxhc3ROYW1lXCI6XCJSYWlrYXJcIixcImlzTG9nZ2VkSW5cIjpmYWxzZSxcImFjY291bnRcIjp7XCJhY2NvdW50SWRcIjoxMCxcImFjY291bnROYW1lXCI6XCJUaGJzLWNvdXBsZXJcIn0sXCJjYXBcIjpbMSwyLDMsNCw1LDYsNyw4LDksMTAsMTEsMTIsMTMsMTQsMTUsMTYsMTcsMTgsMTksMjAsMjEsMjIsMjMsMjQsMjUsMjYsMjcsMjgsMjksMzAsMzEsMzIsMzMsMzQsMzUsMzYsMzcsMzgsMzksNDAsNDEsNDIsNDMsNDQsNDUsNDYsNDcsNDgsNDksNTAsNTEsNTIsNTMsNTQsNTUsNTYsNTcsNTgsNTksNjAsNjEsNjIsNjMsNjQsNjUsNjYsNjcsNjgsNjksNzAsNzEsNzIsNzMsNzQsNzUsNzYsNzcsNzgsNzksODAsODEsODIsODMsODQsODUsODYsODcsODgsODksOTAsOTEsOTIsOTMsOTQsOTUsOTYsOTcsOTgsOTksMTAwLDEwMSwxMDIsMTAzLDEwNCwxMDUsMTA2LDEwNywxMDgsMTA5LDExMCwxMTEsMTEyLDExMywxMTQsMTE1LDExNiwxMTcsMTE4LDExOSwxMjAsMTIxLDEyMiwxMjMsMTI0LDEyNSwxMjYsMTI3LDEyOCwxMjksMTMwLDEzMSwxMzIsMTMzLDEzNCwxMzUsMTM2LDEzNywxMzgsMTM5LDE0MCwxNDEsMTQyLDE0MywxNDQsMTQ1LDE0NiwxNDcsMTQ4LDE0OSwxNTAsMTUxLDE1MiwxNTMsMTU0LDE1NSwxNTYsMTU3LDE1OCwxNTksMTYwLDE2MSwxNjIsMTYzLDE2NCwxNjUsMTY2LDE2NywxNjgsMTY5LDE3MCwxNzEsMTcyLDE3MywxNzQsMTc1LDE3NiwxNzcsMTc4LDE3OSwxODAsMTgxLDE4MiwxODMsMTg0LDE4NSwxODYsMTg3LDE4OCwxODksMTkwLDE5MSwxOTIsMTkzLDE5NCwxOTUsMTk2LDE5NywxOTgsMTk5LDIwMF0sXCJkZWZhdWx0RW52aXJvbm1lbnRcIjp7fSxcImNsaWVudElkXCI6XCIyYjhhYTdlMTQ2ZjE3NWJkNjE2OWIzZGNkZDA4MDZkNDk3N2ZlMjBmXCJ9IiwiaWF0IjoxNTg0NDI4NDU0fQ.Er2OO5gUQVKIJBSKGIpuWD__hH13RvBu-vlI7RVWZLkm_9q82dFbZqTwKYlQnl7vBeDTQPzpslYgK6-6VJibd_LDIQdZroj42bRnH41ZcQMGiAZ5blIZLQ7Ko8iXgs3sq8NlGwg80iJMfzjfqUZwOVEXrXE1d2GSXSha2qdarIImveP0Umfhy_kcbEP1u9s-UvjUGa7UCcKlSMk39w7zuocEJDJ6sg6WHhe6XASLmJL-6we1Fxu05LL6T46PH8JrFrqQAEJ1wV5kUam8ODOpUA2bGXl5lJX5LdT_JZxr1C0RcjV-2mDK-32Kqj5FjX0U4ktZWsHvln88iWrYq4CY',
        'ec944e165a28a02edbce25ffd8a405aefa95cace6a35a7576acf5a0217f9c1c1','3600',
        'basic','Bearer','2b8bd6169b3dcdd0806d4977fe20f','','8',
        '7200',1584428315142);

Caused by: java.sql.SQLException: Duplicate entry
         'eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiJ9.eyJVc2VyIjoie1widXNlcklkXCI'
         for key 'PRIMARY'

The data being passed to access_token column, which is primary key, is not the same. But still getting duplicate entry.

How long is the value you are passing to access_token and what's the limit on key length? — P.Salmon, Mar 17 '20 at 09:17
The primary key must be unique to insert new data. Otherwise, if you want to update the existing row then use the update query. — Rajpal Singh, Mar 17 '20 at 09:45
Yes, Pooja and you also can ignore to enter manually if you created key with auto increment — Rajpal Singh, Mar 17 '20 at 09:51

score 2 · Accepted Answer · answered Mar 17 '20 at 10:03

When you create a primary key on a text field, you need to provide a key length, otherwise you cannot create the index.

This means, you also have a key length specified on your text field primary key. The key length is important because uniqueness is checked on the indexed portion of the field.

Now, the full access_tokens in the two inserts are different, but both start with the same substring. Probably, your key length is not long enough to differentiate between the two values.

You need either to increase the key length or create a hash of the access_token values and index that, or extract that part of the access_token that is truly unique and index that.

Unable to insert two different data into MYSQL DB - shows duplicate entry

1 Answers1