Concurrency violation

[chrisdouglass]

I get an error msg stating "Concurrency Violation: the DeleteCommand affected 0 of the expected 1 records." when I attempt to delete a row from an innoDb table. the table has a tiny amount of data in it, only about 10 rows. i'm attempting to do the delete from within the CRLab dataView window inside VStudio 2005. i launched the data window via right click on a table and select Retrieve Data.

the strange thing about this table is that i changed the LastUpdate field to be a DateTime from a TimeStamp type. and when i had the field created as a TimeStamp field mySql added a default timestamp value for the field of 01/01/0001. that value is still in the field even after i changed the field to be a DateTime.

so, i'm guessing somehow that change from TimeStamp with a default value of time NULL to a DateTime has in some way confused mySql. what is the solution?

i'm going to try and drop the column and see if i can recreate the column as DateTime.

thanks!

[chrisdouglass]

I've found that by changing the DATETIME field to ALLOW NULLS the problem disappears. but, i have to recreate the column for that change to take affect.

so, it didn't have anything to do with the default NULL values or the different time datatypes (timestamp, datetime, etc). it has everything to do with the rules surrounding ALLOW NULLS with ANY date/time field.

basically, if you want any kind of flexibility you need to allow nulls for date/time columns. i have a LOT to learn about MySQL's handling of date/time fields... what a pain.

[Alexey]

Please provide us with your table definition.

[sorin]

This was happend when I made upgrade to new ver. 4

[chrisdouglass]

I have just a few records in a table and when Retrieve Data and open a data view window, I can't update any fields without getting this "Concurrency Vioation" error. What is going on???

I can easily make the edits using MySQL Query Browser. That works fine.

Here's the table def:

CREATE TABLE test.contacts (
id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
FName TINYTEXT,
MName TINYTEXT,
LName TINYTEXT,
Salutation VARCHAR(5),
Suffix VARCHAR(30),
HomeAddress1 TINYTEXT,
HomeAddress2 TINYTEXT,
HomeApartmentOrUnit VARCHAR(30),
HomeZip VARCHAR(10),
HomeCity VARCHAR(128),
HomeStateId INT(11),
HomeCountryId INT(11),
WorkAddress1 TINYTEXT,
WorkAddress2 TINYTEXT,
WorkBuildingOrSuite VARCHAR(30),
WorkZip VARCHAR(10),
WorkStateId INT(11),
WorkCity VARCHAR(128),
WorkCountryId INT(11),
HomePhone VARCHAR(30),
CellPhone VARCHAR(30),
WorkPhone VARCHAR(30),
WorkExtension VARCHAR(10),
HomeFax VARCHAR(30),
WorkFax VARCHAR(30),
HomeEmail TINYTEXT,
WorkEmail TINYTEXT,
HomeEmailAlt TINYTEXT,
IMYahoo VARCHAR(128),
IMMSN VARCHAR(128),
IMGoogle VARCHAR(128),
IMICQ VARCHAR(128),
IMSkype VARCHAR(128),
HomeBlog TINYTEXT,
WorkBlog TINYTEXT,
GamingProfile VARCHAR(128),
HomeWebsite TINYTEXT,
WorkWebsite TINYTEXT,
SpouseOrSignificantOther VARCHAR(128),
Anniversary DATE,
Birthday DATE,
WorkEmailAlt TINYTEXT,
AccountId INT(11) UNSIGNED,
LastUpdate DATETIME,
WorkCompanyName TINYTEXT,
PRIMARY KEY (id)
)
AUTO_INCREMENT=10
CHARACTER SET utf8 COLLATE utf8_general_ci;

[Alexey]

Unfortunately, we can't reproduce such problems.
Please specify the full version of MySQL Developer Tools you are using and your MySQL Server version.