1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

How to force an INSERT INTO job to continue despite errors?

Discussion in 'Databases' started by Carl29, Nov 18, 2017.

  1. #1
    HI guys

    So, I'm adding content to a db but sometimes one of the entries already exists, and it stops the job.
    Is there a way to force continue despite finding duplicates?

    thanks
     
    Carl29, Nov 18, 2017 IP
  2. Benanamen

    Benanamen Greenhorn

    Messages:
    22
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    20
    #2
    INSERT IGNORE is your friend.
     
    Benanamen, Nov 18, 2017 IP
  3. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #3
    Can you explain more on exactly how you're inserting data? Using insert ignore is one method which prevents the importing of duplicates but you're not really solving your problem if this is something that happens a lot or the new insert should overwrite the existing one.
     
    jestep, Nov 29, 2017 IP
  4. phpmillion

    phpmillion Member

    Messages:
    145
    Likes Received:
    11
    Best Answers:
    4
    Trophy Points:
    45
    #4
    It may actually happen pretty often, but it doesn't indicate a problem with application. For example, let's say some script allows user to insert keywords (no matter what these keywords are used for). If user inserts them from time to time, sooner or later he will encounter a situation when records won't be added because one or more keywords already exist in database. And if he adds hundreds or thousands keywords at once, it will become a mission impossible to easily find which keyword was already used. Hence, INSERT IGNORE is a great solution.
     
    phpmillion, Dec 1, 2017 IP
  5. Carl29

    Carl29 Active Member

    Messages:
    114
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    51
    #5
    simple: I am inserting data to an existing database, where it may happen that the "Artist Name X" already exists. So, I simply want to ignore that and continue. and INSERT IGNORE is working as expected.
     
    Carl29, Dec 7, 2017 IP
  6. Carl29

    Carl29 Active Member

    Messages:
    114
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    51
    #6
    btw, I forgot to mention or ask: there is text in some of the values which include words that are being considered as sentences (in, or, and, local, event, ...)
    How to stop those to be seen like that?

    thanks
     
    Carl29, Dec 8, 2017 IP
  7. phpmillion

    phpmillion Member

    Messages:
    145
    Likes Received:
    11
    Best Answers:
    4
    Trophy Points:
    45
    #7
    Can you copy/paste the exact code you use to insert text?
     
    phpmillion, Dec 9, 2017 IP
  8. Carl29

    Carl29 Active Member

    Messages:
    114
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    51
    #8
    
    INSERT IGNORE INTO `bx_artists_main` (`title`, `uri`, `desc`, `country`, `city`, `zip`, `status`, `thumb`, `created`, `author_id`, `tags`, `categories`, `views`, `rate`, `rate_count`, `comments_count`, `fans_count`, `featured`, `allow_view_artist_to`, `allow_view_fans_to`, `allow_comment_to`, `allow_rate_to`, `allow_post_in_forum_to`, `allow_join_to`, `join_confirmation`, `allow_upload_photos_to`, `allow_upload_videos_to`, `allow_upload_sounds_to`, `allow_upload_files_to`, `soundcloud_id`) VALUES  ('Aaiste', 'aaiste', 'Lithuanian DJ, Based in Sydney. One of the best female DJ's in Lithuania. Very young, talented and full of energy. Music became interesting when she was 14. After a couple of years she was noticed by most famous Lithuanian DJs and producers. She earned people's attention by playing remarkable techy warm ups. She already played alongside James Zabiela, Kevin Saunderson, Radio Slave, Seth Troxler, Ripperton, Henry Saiz, AFFKT, Alex Niggemann, Sebo K, Silicone Soul, Kolombo, Uone, Robag Wruhme ,Marco Effe, Echomen, and all Lithuanian electronic music scene.', '', 'Australia', '', 'approved', 0, '', 2, '', '', 2, 0, 0, 0, 0, 0, 2, '4', 'f', 'f', 'f', 4, 0, 'a', 'a', '', '', '');
    
    Code (markup):
     
    Carl29, Dec 9, 2017 IP
  9. Benanamen

    Benanamen Greenhorn

    Messages:
    22
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    20
    #9
    Your problem is the single quotes in your data. If you are using Php with prepared statements it will be no problem. If you are inserting the data directly as you have posted you will need to escape the quotes.

    How exactly is your data getting in the query that you posted?

    For direct data entry there are 3 options
    1. Use another single quote: 'I mustn''t sin!'
    2. Use the escape character \ before the single quote': 'I mustn\'t sin!'
    3. Use double quotes to enclose string instead of single quotes: "I mustn't sin!"
    https://dev.mysql.com/doc/refman/5.7/en/string-literals.html#character-escape-sequences
     
    Benanamen, Dec 9, 2017 IP
  10. Carl29

    Carl29 Active Member

    Messages:
    114
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    51
    #10
    thanks a lot!

    Data is being dumped to a CSV file, then I insert that data separated by commas to an excel file to clean up, then on another tab there is a formula that on each of the value fields it goes to the data tab and fill it. (formula in https://codeshare.io/5gK0nV)

    Because I have thousands of entries I guess the option 3 would be the most appropriate.

    My question is, can I use the double quotes only on the values where the text has single quotes?
    Or do I need to replace all the single quotes on the values?
     
    Carl29, Dec 9, 2017 IP
  11. Benanamen

    Benanamen Greenhorn

    Messages:
    22
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    20
    #11
    Rather than just answer your question, how about try it and see what happens.
     
    Benanamen, Dec 9, 2017 IP
  12. daoustma

    daoustma Member

    Messages:
    4
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    36
    #12
    Nobody asked, but the first question is: what database engine are you using?

    The simplest answer that will work on every database is to first query for the item, and insert it only if it doesn't exist (and optionally update it if it does).
     
    daoustma, Dec 16, 2017 IP
  13. Benanamen

    Benanamen Greenhorn

    Messages:
    22
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    20
    #13
    No, No, and NO! You create a race condition when you do that. The correct method is to set a unique constraint on the column, attempt the insert and capture the duplicate error if any or else use INSERT IGNORE depending on what you are doing.
     
    Benanamen, Dec 16, 2017 IP
  14. daoustma

    daoustma Member

    Messages:
    4
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    36
    #14
    A key and a unique constraint of some kind is a given for a database (well maybe, depending on how optimized the load needs to be). Selecting and conditionally inserting won't cause a race condition (especially if the process is linear and not parallel and transaction are used properly) it's always preferable (and faster) to avoid errors rather than ignoring them.

    If you want to up the complexity a bit but add flexibility and safety (I'm not a fan of importing data straight into production tables), you could load the new data into an import table. Once that is complete you can then insert into the target table from the import table where the record exist in the import table but not in target table (and add whatever other sanitizing or validation you want). You can also identify duplicates with a second query by selecting those records in the import table where they exist in the target table and output a report. When everything is done you truncate the content of the import table.
     
    daoustma, Dec 16, 2017 IP
  15. Benanamen

    Benanamen Greenhorn

    Messages:
    22
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    20
    #15
    Going back to the OP's question, INSERT IGNORE is the solution.
    "So, I'm adding content to a db but sometimes one of the entries already exists, and it stops the job.
    Is there a way to force continue despite finding duplicates?".

    Additionally, OP didn't say how he was doing the updates. If he is using LOAD DATA INFILE you aren't going to be selecting records to see if they exist first. You would just simply use INSERT IGNORE.

    If you have the constraints properly set to your needs I don t see any additional safety to an import table. Either way, no duplicates will be inserted. If you wanted to know what the duplicates are then I could see the import table being of use.
     
    Benanamen, Dec 16, 2017 IP