MySQL keys acting a little strange
I have a bit of a strange problem with MySQL that I've never seen before. Basically, I need to enter the same entry into a table x number of times. The Primary Key is auto increment so there shouldn't be any duplicate value problems. However, it takes the last few columns and seems to clump them all into one value and is using that as a key. The error I'm getting back is:
Duplicate entry '80-0--2011-06-16-0-1' for key 'idx_mt'
Mind you, there is no field called idx_mt. The primary key field is called just ID. And that 80-0--2011-06-16-0-1 is the last 8 columns or so (which you can see the names of in the cols variable) concatenated, which they obviously shouldn't be doing. Also, the first time the loop runs through, the entry goes through, but the later ones conflict with it. I confirmed that this is the problem by changing one of the last 8 columns to a value based on a loop, and that went through with no problems. Any insight?
Dim cols As String = ""
Dim msi As System.Globalization.DateTimeFormatInfo = New System.Globalization.DateTimeFormatInfo()
开发者_如何学运维 cols = "TEMPLATE_NAME," & _
"DESCRIPTION," & _
"FORMAT," & _
"SENDER," & _
"`REPLY-TO`," & _
"SUBJECT," & _
"BODYHTML," & _
"BODYTEXT," & _
"CAMPAIGN_ID," & _
"SPLIT_GROUP_ID," & _
"TEMPLATE_IDENTIFIER," & _
"MESSAGE_SEND_DATE," & _
"DAYS_TO_DELAY_SEND," & _
"ACTIVE"
Try
Dim test As Integer = CInt(cmbEffort.Text)
Catch ex As Exception
MsgBox("Please use numerical values from 1 - 6 for the effort #.")
Return
End Try
If (cmbEffort.Text < 1 Or cmbEffort.Text > 6) Then
MsgBox("Please use numerical values from 1 - 6 for the effort #.")
Return
End If
Dim query As String = ""
Dim _date As Date = DateTimePicker1.Value
Dim dateSent As String = ""
Dim html As String = ""
Dim temp As message
If (_date.DayOfWeek <> DayOfWeek.Thursday) Then
MsgBox("Selected date must be a Thursday.")
Return
End If
dateSent = formatDate(_date)
Try
fileStreamIn = New IO.FileStream(txtHTML.Text, IO.FileMode.Open)
streamReader = New IO.StreamReader(fileStreamIn)
Catch ex As Exception
MsgBox("HTML File not found.")
Return
End Try
html = streamReader.ReadToEnd()
html = html.Replace("'", "&apos")
streamReader.Close()
server.query("SELECT * FROM TEMPLATES_TO_COPY WHERE CAMPAIGN_ID = " & cmpgnID)
server.read()
temp = getMessage(cmpgnID.ToString())
temp.bodyText = txtMessage.Text
temp.HTML = html
temp.sendDate = dateSent
temp.description = txtDescription.Text & "_" & cmbEffort.Text
temp.campaignID = cmpgnID
temp.name = temp.name.Replace("MMM", msi.GetMonthName(_date.Month).Substring(0, 3).ToUpper())
temp.name = temp.name.Replace("YY", _date.Year.ToString().Substring(2, 2))
For i As Integer = 1 To cmbEffort.Text
temp.name = temp.name.Replace("X", (i + 1).ToString())
query = "INSERT INTO MESSAGE_TEMPLATES (" & _
cols & ")" & _
"VALUES ('" & temp.name & "','" & temp.description & "','" & temp.format & "','" & temp.sender & "','" & temp.replyTo & "','" & temp.subject & "','" & temp.HTML & "','" & temp.bodyText & "'," & temp.campaignID & "," & 0 & ",'','" & temp.sendDate & "'," & temp.daysToDelay & "," & temp.active & ");"
If (Not server.query(query)) Then
Return
End If
temp.name = temp.name.Replace((i + 1).ToString(), "X")
Next
The error is precisely what it says it is:
Duplicate entry '80-0--2011-06-16-0-1' for key 'idx_mt'
There is a key/index that you have defined called idx_mt
. This key is set up among a handful of columns. As others have pointed out in the comments, the values of all of those columns together are identical to a row already in your table, thus causing this conflict.
The duplicate entry with the fields concatenated together is how MySQL displays the value (and maybe even stores, I'm not sure) for that key. It keeps track of it essentially like another column. The values that are put there are made up of other columns in the table.
When you see 80-0--2011-06-16-0-1
, it means that the fields in this index on this particular row have the values of 80, 0, null
, 2011-06-16, 0, and 1. What you INSERT is conflicting with that.
精彩评论