Re: getting a Duplicate entry error when inserting a new record - but there is no duplicate record
thanks for the suggestions. doing the insert in the commandline gives the same error. i deleted all the other indexes except the one in question (just to see if it changed anything)...same error. did a check table (and a repair table and an optimize table) - no change. is it possible there is a MySQL server setting somewhere that is causing it to truncate the index? i was running MySQL version 5.0.27-community-nt on WindowsXP just upgraded to 5.0.37 - but no change i'm stumped...anyone got any ideas? thanks, jerad On Mar 13, 2007, at 6:24 PM, Steve Edberg wrote: At 5:24 PM -0700 3/13/07, jerad sloan wrote: thanks for the quick reply. i want that to be unique so there is no way to insert the same URL more than once. there isn't a record with that URL...but it gives the error anyway...it seems to be just checking a portion of the entry i'm trying to insert. i don't want uniqueness across a set of columns...just that one column. Aah, I wasn't following the emails closely enough; sorry. Your error message does indeed look a bit puzzling, as it only shows the first 64 characters. Key length is limited to 1024 - http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html - and even the sum total length of all your keys is only something like 440 bytes. Two other options I can think of: there's a hidden ascii NUL character in there between the 'vid' and 'eoid...' that's terminating the string, or you might have some table corruption. A check table - http://dev.mysql.com/doc/refman/5.0/en/check-table.html - might illuminate that. One other thought: have you tried inserting the records by hand from the Mysql commandline prompt? Perhaps the program is truncating the field at 64 chars, so it's not a mysql problem at all. Or, if this data is coming from an HTML form, perhaps there's a maxlength parameter on the videourl form field. what do you mean by (3) drop index videourl; create index videourl (videourl). drop the unique index and create a non-unique index? that isn't what i'm trying to do...i need it to be unique. You can disregard this; again, didn't read the original message closely enough. This would indeed create a non-unique index. steve thanks, jerad On Mar 13, 2007, at 4:55 PM, Steve Edberg wrote: At 4:36 PM -0700 3/13/07, jerad sloan wrote: thanks. i had an extra index setup for one of my fields...i deleted it and it started working as expected...but now the same thing is happening again. i'm getting the error Duplicate entry 'http://vids.myspace.com/index.cfm? fuseaction=vids.individualvid' for key 2 when doing the following insert into tblvideoURL (videourl,userid,shortname,videourlcreated,videoURLcurrentstatus ,x mltitle ) values ('http://vids.myspace.com/index.cfm? fuseaction=vids.individualvideoid=1951753288',3,'MySpace',{ts '2007-03-13 15:04:01'},404 ,'Sporting Riff Raff - Absolutely Wasted (director unknown - hopefully Emily?!)' ) here is the Show Create Table SNIP PRIMARY KEY (`videourlID`), UNIQUE KEY `videourl` (`videourl`), KEY `videoURLcurrentstatus` (`videourlcurrentstatus`), Key 2 is defined as unique; thus, the error message says you're inserting a record where videourl duplicates an existing record. Solutions: (1) don't do that. (2) drop index videourl (3) drop index videourl; create index videourl (videourl). (4) if you want to ensure uniqueness across a set of columns, define a unique composite key, for instance: create unique index01 (videourl,userid); Indexes/keys do not have to be unique unless they are primary or defined as unique. steve -- +--- my people are the people of the dessert, ---+ | Steve Edberghttp:// pgfsun.ucdavis.edu/ | | UC Davis Genome Center [EMAIL PROTECTED] | | Bioinformatics programming/database/sysadmin (530) 754-9127 | + said t e lawrence, picking up his fork + -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: getting a Duplicate entry error when inserting a new record - but there is no duplicate record
thanks. i had an extra index setup for one of my fields...i deleted it and it started working as expected...but now the same thing is happening again. i'm getting the error Duplicate entry 'http://vids.myspace.com/index.cfm? fuseaction=vids.individualvid' for key 2 when doing the following insert into tblvideoURL (videourl,userid,shortname,videourlcreated,videoURLcurrentstatus ,xmltit le ) values ('http://vids.myspace.com/index.cfm? fuseaction=vids.individualvideoid=1951753288',3,'MySpace',{ts '2007-03-13 15:04:01'},404 ,'Sporting Riff Raff - Absolutely Wasted (director unknown - hopefully Emily?!)' ) here is the Show Create Table +- +--- + | Table | Create Table | +- +---
Re: getting a Duplicate entry error when inserting a new record - but there is no duplicate record
At 4:36 PM -0700 3/13/07, jerad sloan wrote: thanks. i had an extra index setup for one of my fields...i deleted it and it started working as expected...but now the same thing is happening again. i'm getting the error Duplicate entry 'http://vids.myspace.com/index.cfm?fuseaction=vids.individualvid' for key 2 when doing the following insert into tblvideoURL (videourl,userid,shortname,videourlcreated,videoURLcurrentstatus ,xmltitle ) values ('http://vids.myspace.com/index.cfm?fuseaction=vids.individualvideoid=1951753288',3,'MySpace',{ts '2007-03-13 15:04:01'},404 ,'Sporting Riff Raff - Absolutely Wasted (director unknown - hopefully Emily?!)' ) here is the Show Create Table SNIP PRIMARY KEY (`videourlID`), UNIQUE KEY `videourl` (`videourl`), KEY `videoURLcurrentstatus` (`videourlcurrentstatus`), Key 2 is defined as unique; thus, the error message says you're inserting a record where videourl duplicates an existing record. Solutions: (1) don't do that. (2) drop index videourl (3) drop index videourl; create index videourl (videourl). (4) if you want to ensure uniqueness across a set of columns, define a unique composite key, for instance: create unique index01 (videourl,userid); Indexes/keys do not have to be unique unless they are primary or defined as unique. steve -- +--- my people are the people of the dessert, ---+ | Steve Edberghttp://pgfsun.ucdavis.edu/ | | UC Davis Genome Center[EMAIL PROTECTED] | | Bioinformatics programming/database/sysadmin (530)754-9127 | + said t e lawrence, picking up his fork + -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: getting a Duplicate entry error when inserting a new record - but there is no duplicate record
thanks for the quick reply. i want that to be unique so there is no way to insert the same URL more than once. there isn't a record with that URL...but it gives the error anyway...it seems to be just checking a portion of the entry i'm trying to insert. i don't want uniqueness across a set of columns...just that one column. what do you mean by (3) drop index videourl; create index videourl (videourl). drop the unique index and create a non-unique index? that isn't what i'm trying to do...i need it to be unique. thanks, jerad On Mar 13, 2007, at 4:55 PM, Steve Edberg wrote: At 4:36 PM -0700 3/13/07, jerad sloan wrote: thanks. i had an extra index setup for one of my fields...i deleted it and it started working as expected...but now the same thing is happening again. i'm getting the error Duplicate entry 'http://vids.myspace.com/index.cfm? fuseaction=vids.individualvid' for key 2 when doing the following insert into tblvideoURL (videourl,userid,shortname,videourlcreated,videoURLcurrentstatus ,xml title ) values ('http://vids.myspace.com/index.cfm? fuseaction=vids.individualvideoid=1951753288',3,'MySpace',{ts '2007-03-13 15:04:01'},404 ,'Sporting Riff Raff - Absolutely Wasted (director unknown - hopefully Emily?!)' ) here is the Show Create Table SNIP PRIMARY KEY (`videourlID`), UNIQUE KEY `videourl` (`videourl`), KEY `videoURLcurrentstatus` (`videourlcurrentstatus`), Key 2 is defined as unique; thus, the error message says you're inserting a record where videourl duplicates an existing record. Solutions: (1) don't do that. (2) drop index videourl (3) drop index videourl; create index videourl (videourl). (4) if you want to ensure uniqueness across a set of columns, define a unique composite key, for instance: create unique index01 (videourl,userid); Indexes/keys do not have to be unique unless they are primary or defined as unique. steve -- +--- my people are the people of the dessert, ---+ | Steve Edberghttp:// pgfsun.ucdavis.edu/ | | UC Davis Genome Center [EMAIL PROTECTED] | | Bioinformatics programming/database/sysadmin (530) 754-9127 | + said t e lawrence, picking up his fork + -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: getting a Duplicate entry error when inserting a new record - but there is no duplicate record
At 5:24 PM -0700 3/13/07, jerad sloan wrote: thanks for the quick reply. i want that to be unique so there is no way to insert the same URL more than once. there isn't a record with that URL...but it gives the error anyway...it seems to be just checking a portion of the entry i'm trying to insert. i don't want uniqueness across a set of columns...just that one column. Aah, I wasn't following the emails closely enough; sorry. Your error message does indeed look a bit puzzling, as it only shows the first 64 characters. Key length is limited to 1024 - http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html - and even the sum total length of all your keys is only something like 440 bytes. Two other options I can think of: there's a hidden ascii NUL character in there between the 'vid' and 'eoid...' that's terminating the string, or you might have some table corruption. A check table - http://dev.mysql.com/doc/refman/5.0/en/check-table.html - might illuminate that. One other thought: have you tried inserting the records by hand from the Mysql commandline prompt? Perhaps the program is truncating the field at 64 chars, so it's not a mysql problem at all. Or, if this data is coming from an HTML form, perhaps there's a maxlength parameter on the videourl form field. what do you mean by (3) drop index videourl; create index videourl (videourl). drop the unique index and create a non-unique index? that isn't what i'm trying to do...i need it to be unique. You can disregard this; again, didn't read the original message closely enough. This would indeed create a non-unique index. steve thanks, jerad On Mar 13, 2007, at 4:55 PM, Steve Edberg wrote: At 4:36 PM -0700 3/13/07, jerad sloan wrote: thanks. i had an extra index setup for one of my fields...i deleted it and it started working as expected...but now the same thing is happening again. i'm getting the error Duplicate entry 'http://vids.myspace.com/index.cfm?fuseaction=vids.individualvid' for key 2 when doing the following insert into tblvideoURL (videourl,userid,shortname,videourlcreated,videoURLcurrentstatus ,xmltitle ) values ('http://vids.myspace.com/index.cfm?fuseaction=vids.individualvideoid=1951753288',3,'MySpace',{ts '2007-03-13 15:04:01'},404 ,'Sporting Riff Raff - Absolutely Wasted (director unknown - hopefully Emily?!)' ) here is the Show Create Table SNIP PRIMARY KEY (`videourlID`), UNIQUE KEY `videourl` (`videourl`), KEY `videoURLcurrentstatus` (`videourlcurrentstatus`), Key 2 is defined as unique; thus, the error message says you're inserting a record where videourl duplicates an existing record. Solutions: (1) don't do that. (2) drop index videourl (3) drop index videourl; create index videourl (videourl). (4) if you want to ensure uniqueness across a set of columns, define a unique composite key, for instance: create unique index01 (videourl,userid); Indexes/keys do not have to be unique unless they are primary or defined as unique. steve -- +--- my people are the people of the dessert, ---+ | Steve Edberghttp://pgfsun.ucdavis.edu/ | | UC Davis Genome Center[EMAIL PROTECTED] | | Bioinformatics programming/database/sysadmin (530)754-9127 | + said t e lawrence, picking up his fork + -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: getting a Duplicate entry error when inserting a new record - but there is no duplicate record
Jerad - First of all, please reply all with a SHOW CREATE TABLE of the table you're inserting the row into, so that we can identify the indexes you've established for the table. thanks, --bemansell On 3/7/07, jerad sloan [EMAIL PROTECTED] wrote: when i try to insert the string http://vids.myspace.com/index.cfm?fuseaction=vids.individualvideoid=2012774576;, it returns error : Duplicate entry ' http://vids.myspace.com/index.cfm?fuseaction=vids.individualvideoid=' for key 3 when i check the table and do a search for the string, there is no match...it seems that they index is not using the full field to index and finding a duplicate index key even though there is not a dupliate field - is there a 70 character limit on varchar unique indexes or something weird?. this is an InnoDB table with 2 unique keys and a few other keys...i'm assuming key 3 deals with this data since it is one of the unique keys and it shows this data in the error...but how do i confirm which is 'key 3'? i've been searching around and can't find any mention known issues around this. any help/suggestions would be greatly appreciated. thanks, jerad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
getting a Duplicate entry error when inserting a new record - but there is no duplicate record
when i try to insert the string http://vids.myspace.com/index.cfm?fuseaction=vids.individualvideoid=2012774576;, it returns error : Duplicate entry ' http://vids.myspace.com/index.cfm?fuseaction=vids.individualvideoid=' for key 3 when i check the table and do a search for the string, there is no match...it seems that they index is not using the full field to index and finding a duplicate index key even though there is not a dupliate field - is there a 70 character limit on varchar unique indexes or something weird?. this is an InnoDB table with 2 unique keys and a few other keys...i'm assuming key 3 deals with this data since it is one of the unique keys and it shows this data in the error...but how do i confirm which is 'key 3'? i've been searching around and can't find any mention known issues around this. any help/suggestions would be greatly appreciated. thanks, jerad
Re: Duplicate entry error
Hello. | 4.0.17-standard | It is an old version of MySQL which could contain lots of bugs. It is strongly recommended to upgrade to the latest release 4.1.12, or if it is impossible in your production environment to 4.0.24. Usually upgrade solves such kind of errors. Please, next time send your messages to the list, because the probability to obtain more help grows a lot. 1. Send an output of the following statement executed both on master and slave: select version(); from master :- mysql select version(); +-+ | version() | +-+ | 4.0.17-standard-log | +-+ 1 row in set (0.02 sec) mysql from slave :- === mysql select version(); +-+ | version() | +-+ | 4.0.17-standard | +-+ 1 row in set (0.00 sec) mysql 3. The queries are same in bin log and relay logs. 4. You should find where the error occurred, fix it, possibly by executing SQL query on the slave. Than 'CHANGE MASTER' to corresponding position in the master binary log and start slave. ---This may not be possible to do every time on and on every m/c. Can you tell any way to avoid this problem. Thanks,[EMAIL PROTECTED] wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Duplicate entry error
Gleb, Thanks for your suggestions. I would like to know 1.How to send MySQL versions on master and slave. 2.Where I have to include output of 'SHOW SLAVE STATUS'. 3.How to Fix the problem if it does occurs. 4.How to restart the slave SQL thread with SLAVE START. Thanks again for your help and cooperation. Anurag -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Thursday, June 30, 2005 3:20 PM To: mysql@lists.mysql.com Subject: Re: Duplicate entry error Hello. Please examine your master's binary and slave's relay logs with mysqlbinlog utility to find if sent and received SQL statements are different. Sent your MySQL versions on master and slave. Include complete output of 'SHOW SLAVE STATUS'. If your version is rather old, check if the problem remains after upgrade. [EMAIL PROTECTED] wrote: Hi all, I am getting duplicate entry error while inserting data in the table. My table name is TEMP and it contains only one field which is primary key= ,nt null as well as auto increment . 050628 16:05:00 Slave: Error 'Duplicate entry '77' for key 1' on query 'INSERT INTO TEMP VALUES ()'. Default database: 'our database name is= here', Error_code: 1062 050628 16:05:00 Error running query, slave SQL thread aborted.=0D Fix the problem, and restart the slave SQL thread with SLAVE START. We= stopped at log 'log-bin.018' position 37918 I have a master slave setup.So slave database has entries upto 77 only but= master database has entries upto 114. If I again start inesrting entries then entries in slave database will= start from 115 which is really danagerous for me. I couldn't understand the statement=0D Fix the problem, and restart the slave SQL thread with SLAVE START. Can anybody help me in solving this issue ? Thanks and Regards, Anurag -Original Message- From: Johan H=F6=F6k [mailto:[EMAIL PROTECTED] Sent: Thursday, June 30, 2005 2:01 PM To: [EMAIL PROTECTED] Cc: Siegfried Heintze; mysql@lists.mysql.com Subject: Re: Wanted: Help with 'ON DUPLICATE KEY' syntax Hi Jocelyn, you're right of course. I didn't read the original post closely enough (it's early in the morning here...). I missed that SELECT wasn't used. Regards, /Johan [EMAIL PROTECTED] wrote: Hi Johan, =0D I don't think it's its problem, since MySQL returns a You have an=0D error in your SQL syntax. error, and not those specified in this bug=0D report. This means the SQL parser failed to understand its query. =0D Regards, Jocelyn =0D =0D Hi Siegfried, I think you've run into bug #8732: ... Description: if you do a INSERT INTO table (col_list) SELECT ... ON DUPLICATE KEY=0D UPDATE and refer to the same column in the col_list and in the UPDATE=0D clause, it will bail out, doesn't seem like it should, since not listing the column in the INSERT list will make it work. ... I've run into it myself on 4.1.12. /Johan Siegfried Heintze wrote: Thanks for deciphering that terrible message, Shawn. I accidentally=0D must have hit the paste key too many times. Anyway, here is my new insert statement: INSERT INTO jobtitlecount (fkJobPosting, dtSnapShot, cJobTitle)=0D VALUES (211584,'2005-06-26',2) ON DUPLICATE KEY UPDATE cJobTitle=3D2 DBD::mysql::st execute failed: You have an error in your SQL syntax.=0D Check the manual that corresponds to your MySQL server version for=0D the right syntax to use near 'ON DUPLICATE KEY UPDATE cJobTitle=3D2' at= =0D line 1 at ./crawl-hot-jobs.pl line 675. I'm looking at the documentation on=0D http://dev.mysql.com/doc/mysql/en/insert.html and I don't see what I=0D am doing wrong. Thanks, Siegfried -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub= [EMAIL PROTECTED] =0D =0D =0D =0D Confidentiality Notice=0D The information contained in this electronic message and any attachments to= this message are intended for the exclusive use of the addressee(s) and may contain confidential or= privileged information. If you are not the intended recipient, please notify the sender at Wipro or= [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please
Re: Duplicate entry error
Hello. 1.How to send MySQL versions on master and slave. Send an output of the following statement executed both on master and slave: select version(); 2.Where I have to include output of 'SHOW SLAVE STATUS'. Execute SHOW SLAVE STAUTS on your slave (run START SLAVE before) and include the output in your answer. In such way you could find the position of the last event executed by the SQL thread from the master's binary log. See: http://dev.mysql.com/doc/mysql/en/show-slave-status.html 3.How to Fix the problem if it does occurs. First check with mysqlbinlog utility if master and relay binary logs contain the same query. 4.How to restart the slave SQL thread with SLAVE START. You should find where the error occurred, fix it, possibly by executing SQL query on the slave. Than 'CHANGE MASTER' to corresponding position in the master binary log and start slave. If your's data size is small, sometimes it is better to setup replication again. However, finding the cause of the problem is preferred way. Thanks again for your help and cooperation. Anurag -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Thursday, June 30, 2005 3:20 PM To: mysql@lists.mysql.com Subject: Re: Duplicate entry error Hello. Please examine your master's binary and slave's relay logs with mysqlbinlog utility to find if sent and received SQL statements are different. Sent your MySQL versions on master and slave. Include complete output of 'SHOW SLAVE STATUS'. If your version is rather old, check if the problem remains after upgrade. [EMAIL PROTECTED] wrote: =0D Hi all, I am getting duplicate entry error while inserting data in the table.=0D My table name is TEMP and it contains only one field which is primary=0D key=3D ,nt null as well as auto increment . =0D 050628 16:05:00 Slave: Error 'Duplicate entry '77' for key 1' on=0D query 'INSERT INTO TEMP VALUES ()'. Default database: 'our database=0D name is=3D here', Error_code: 1062 050628 16:05:00 Error running query, slave SQL thread aborted.=3D0D Fix the problem, and restart the slave SQL thread with SLAVE START. We=3D=0D stopped at log 'log-bin.018' position 37918 =0D I have a master slave setup.So slave database has entries upto 77 only but=3D master database has entries upto 114. If I again start inesrting=0D entries then entries in slave database will=3D start from 115 which is=0D really danagerous for me. =0D I couldn't understand the statement=3D0D =0D Fix the problem, and restart the slave SQL thread with SLAVE START. =0D Can anybody help me in solving this issue ? =0D Thanks and Regards, Anurag =0D =0D -Original Message- From: Johan H=3DF6=3DF6k [mailto:[EMAIL PROTECTED] Sent: Thursday, June 30, 2005 2:01 PM To: [EMAIL PROTECTED] Cc: Siegfried Heintze; mysql@lists.mysql.com Subject: Re: Wanted: Help with 'ON DUPLICATE KEY' syntax =0D =0D Hi Jocelyn, you're right of course. I didn't read the original post closely enough (it's early in the morning here...). I missed that SELECT wasn't used. =0D Regards, /Johan =0D [EMAIL PROTECTED] wrote: Hi Johan, =3D0D I don't think it's its problem, since MySQL returns a You have an=3D0D error in your SQL syntax. error, and not those specified in this=0D bug=3D0D report. This means the SQL parser failed to understand its=0D query. =3D0D Regards, Jocelyn =3D0D =3D0D Hi Siegfried, I think you've run into bug #8732: ... Description: if you do a INSERT INTO table (col_list) SELECT ... ON DUPLICATE=0D KEY=3D0D UPDATE and refer to the same column in the col_list and in the UPDATE=3D0D clause, it will bail out, doesn't seem like it should,=0D since not listing the column in the INSERT list will make it work. ... I've run into it myself on 4.1.12. /Johan Siegfried Heintze wrote: Thanks for deciphering that terrible message, Shawn. I=0D accidentally=3D0D must have hit the paste key too many times. Anyway, here is my new insert statement: INSERT INTO jobtitlecount (fkJobPosting, dtSnapShot, cJobTitle)=3D0D=0D VALUES (211584,'2005-06-26',2) ON DUPLICATE KEY UPDATE cJobTitle=3D3D2 DBD::mysql::st execute failed: You have an error in your SQL=0D syntax.=3D0D Check the manual that corresponds to your MySQL server=0D version for=3D0D the right syntax to use near 'ON DUPLICATE KEY UPDATE cJobTitle=3D3D2' at=3D =3D0D line 1 at ./crawl-hot-jobs.pl line 675. I'm looking at the documentation on=3D0D=0D http://dev.mysql.com/doc/mysql/en/insert.html and I don't see what=0D I=3D0D am doing wrong. Thanks, Siegfried -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=3D [EMAIL PROTECTED] =3D0D =3D0D =3D0D =3D0D =0D =0D =0D =0D Confidentiality Notice=3D0D
Duplicate entry error
Hi all, I am getting duplicate entry error while inserting data in the table. My table name is TEMP and it contains only one field which is primary key ,nt null as well as auto increment . 050628 16:05:00 Slave: Error 'Duplicate entry '77' for key 1' on query 'INSERT INTO TEMP VALUES ()'. Default database: 'our database name is here', Error_code: 1062 050628 16:05:00 Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log 'log-bin.018' position 37918 I have a master slave setup.So slave database has entries upto 77 only but master database has entries upto 114. If I again start inesrting entries then entries in slave database will start from 115 which is really danagerous for me. I couldn't understand the statement Fix the problem, and restart the slave SQL thread with SLAVE START. Can anybody help me in solving this issue ? Thanks and Regards, Anurag -Original Message- From: Johan Höök [mailto:[EMAIL PROTECTED] Sent: Thursday, June 30, 2005 2:01 PM To: [EMAIL PROTECTED] Cc: Siegfried Heintze; mysql@lists.mysql.com Subject: Re: Wanted: Help with 'ON DUPLICATE KEY' syntax Hi Jocelyn, you're right of course. I didn't read the original post closely enough (it's early in the morning here...). I missed that SELECT wasn't used. Regards, /Johan [EMAIL PROTECTED] wrote: Hi Johan, I don't think it's its problem, since MySQL returns a You have an error in your SQL syntax. error, and not those specified in this bug report. This means the SQL parser failed to understand its query. Regards, Jocelyn Hi Siegfried, I think you've run into bug #8732: ... Description: if you do a INSERT INTO table (col_list) SELECT ... ON DUPLICATE KEY UPDATE and refer to the same column in the col_list and in the UPDATE clause, it will bail out, doesn't seem like it should, since not listing the column in the INSERT list will make it work. ... I've run into it myself on 4.1.12. /Johan Siegfried Heintze wrote: Thanks for deciphering that terrible message, Shawn. I accidentally must have hit the paste key too many times. Anyway, here is my new insert statement: INSERT INTO jobtitlecount (fkJobPosting, dtSnapShot, cJobTitle) VALUES (211584,'2005-06-26',2) ON DUPLICATE KEY UPDATE cJobTitle=2 DBD::mysql::st execute failed: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON DUPLICATE KEY UPDATE cJobTitle=2' at line 1 at ./crawl-hot-jobs.pl line 675. I'm looking at the documentation on http://dev.mysql.com/doc/mysql/en/insert.html and I don't see what I am doing wrong. Thanks, Siegfried -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Duplicate entry error
Hello. Please examine your master's binary and slave's relay logs with mysqlbinlog utility to find if sent and received SQL statements are different. Sent your MySQL versions on master and slave. Include complete output of 'SHOW SLAVE STATUS'. If your version is rather old, check if the problem remains after upgrade. [EMAIL PROTECTED] wrote: Hi all, I am getting duplicate entry error while inserting data in the table. My table name is TEMP and it contains only one field which is primary key= ,nt null as well as auto increment . 050628 16:05:00 Slave: Error 'Duplicate entry '77' for key 1' on query 'INSERT INTO TEMP VALUES ()'. Default database: 'our database name is= here', Error_code: 1062 050628 16:05:00 Error running query, slave SQL thread aborted.=0D Fix the problem, and restart the slave SQL thread with SLAVE START. We= stopped at log 'log-bin.018' position 37918 I have a master slave setup.So slave database has entries upto 77 only but= master database has entries upto 114. If I again start inesrting entries then entries in slave database will= start from 115 which is really danagerous for me. I couldn't understand the statement=0D Fix the problem, and restart the slave SQL thread with SLAVE START. Can anybody help me in solving this issue ? Thanks and Regards, Anurag -Original Message- From: Johan H=F6=F6k [mailto:[EMAIL PROTECTED] Sent: Thursday, June 30, 2005 2:01 PM To: [EMAIL PROTECTED] Cc: Siegfried Heintze; mysql@lists.mysql.com Subject: Re: Wanted: Help with 'ON DUPLICATE KEY' syntax Hi Jocelyn, you're right of course. I didn't read the original post closely enough (it's early in the morning here...). I missed that SELECT wasn't used. Regards, /Johan [EMAIL PROTECTED] wrote: Hi Johan, =0D I don't think it's its problem, since MySQL returns a You have an=0D error in your SQL syntax. error, and not those specified in this bug=0D report. This means the SQL parser failed to understand its query. =0D Regards, Jocelyn =0D =0D Hi Siegfried, I think you've run into bug #8732: ... Description: if you do a INSERT INTO table (col_list) SELECT ... ON DUPLICATE KEY=0D UPDATE and refer to the same column in the col_list and in the UPDATE=0D clause, it will bail out, doesn't seem like it should, since not listing the column in the INSERT list will make it work. ... I've run into it myself on 4.1.12. /Johan Siegfried Heintze wrote: Thanks for deciphering that terrible message, Shawn. I accidentally=0D must have hit the paste key too many times. Anyway, here is my new insert statement: INSERT INTO jobtitlecount (fkJobPosting, dtSnapShot, cJobTitle)=0D VALUES (211584,'2005-06-26',2) ON DUPLICATE KEY UPDATE cJobTitle=3D2 DBD::mysql::st execute failed: You have an error in your SQL syntax.=0D Check the manual that corresponds to your MySQL server version for=0D the right syntax to use near 'ON DUPLICATE KEY UPDATE cJobTitle=3D2' at= =0D line 1 at ./crawl-hot-jobs.pl line 675. I'm looking at the documentation on=0D http://dev.mysql.com/doc/mysql/en/insert.html and I don't see what I=0D am doing wrong. Thanks, Siegfried -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub= [EMAIL PROTECTED] =0D =0D =0D =0D Confidentiality Notice=0D The information contained in this electronic message and any attachments to= this message are intended for the exclusive use of the addressee(s) and may contain confidential or= privileged information. If you are not the intended recipient, please notify the sender at Wipro or= [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Duplicate entry error??
Hi, You have a unique key defined which includes Ekey and another field (the key 2 mentioned in the error message), and the entry "other_field = '88' and Ekey='252'" has already been used. Regards Quentin -Original Message- From: Paul Wilkinson [mailto:[EMAIL PROTECTED]] Sent: Tuesday, 13 February 2001 10:39 To: Help MySQL List Subject: Duplicate entry error?? Thanks in advance for your help! How does one replace a single field value when the value repeats? I tryed the following and get an error message "Duplicate entry '88-252' for key 2" UPDATE DKWords SET EKey='252' WHERE EKey='13' What is wrong? Paul - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php