1

I have a model that had the following fields:

+----------+----------+
| username | password |
+----------+----------+
| AAA      | ***      |
| BBB      | ***      |
| CCC      | ***      |
+----------+----------+

But then I deleted the last row (CCC) and tried to insert a new one with the same username:

INSERT INTO users (username, password) SELECT 'CCC', '***' 

But I got this error:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'CCC' for key 'username_UNIQUE'

And if I query for all the existent rows, I get only two rows:

SELECT * FROM users
+----------+----------+
| username | password |
+----------+----------+
| AAA      | ***      |
| BBB      | ***      |
+----------+----------+

I suspect this has to do with the data not syncing to disk, since in the first days, when I first created the model using MySQL Workbench, it wouldn't appear on Adminer only until after a reboot, but I have no idea as to how to force a sync on the data to disk, or even if it's the issue.

I'm using MariaDB 5.5.39 on Fedora 20 64 bit on linux 3.17.4-200 as the DBMS and InnoDB as the DB storage engine.

Update:

It looks like this happens with all and every username I try, so 'askaksjk' will also trigger the same error, but if I insert a new row without using a SELECT statement, it adds the record correctly:

INSERT INTO users (username, password) VALUES ('CCC', '***')

Does it has anything to do with the fact that the SELECT already returns a record with the same username as the one I'm trying to add?

  • Were you using the INSERT INTO ... SELECT ... with a second table or just static values? Your update is the correct method for inputting data. Only use the additional SELECT if you're copying data across tables. – LowlyDBA Dec 16 '14 at 20:57
  • Yes, I wasn't retrieving data from another table, I was just using static values. Is this the intended behavior? To take that into account the next time. – arielnmz Dec 16 '14 at 20:59
  • Yeah, use SELECT for a source table and VALUES for static data being input. Two different use cases altogether. – LowlyDBA Dec 16 '14 at 21:05
  • Odd, but anyway, can you put that as an answer to close this question please? And for future reference of course. If you happen to know any source or document about this matter please let me know. Thank you so much. – arielnmz Dec 16 '14 at 21:07
  • do you have cluster environment or replica? – Ahmad Abuhasna Dec 17 '14 at 9:25
0

Your update is the correct way to insert data. Using INSERT INTO ... SELECT ... requires a source table and is not meant to be used with static values.

Using INSERT INTO ... VALUES ... is for inserting static data, such as CCC, into the table. The two are not interchangable and will cause the issues you've been seeing.

You can check the documentation on INSERT INTO ... SELECT ... here and note that it only talks about using a table source.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.