Thanks for the response. There is currently no primary key on the field so I am relying on the data being there, or not being there, to decide whether it is safe to insert the new data.
Through MySQLCC, have_query_cache = YES. Is this the variable I am looking for? What affect would this have? God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. GOD BLESS AMERICA! To God Be The Glory! -----Original Message----- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 07, 2004 11:49 AM To: 'Paul McNeil '; 'MySQL General ' Subject: RE: Very strange database behavior Are you saying that you are not receiving an Unique key violation when you enter in duplicate data or you are relying on a value being present? If you are using the later, do you have query caching turned on? -----Original Message----- From: Paul McNeil To: MySQL General Sent: 7/7/04 10:43 AM Subject: Very strange database behavior Good morning to all. I have a very strange problem in my database and am trying to track down whether it is a JAVA problem or a mySQL problem. I have a table that is supposed to accept only unique data, however, at this time, the PK for that information is set to the auto incrementing ID field. The unique field is called 'pat'. Here is a description of the problem. I have a class that posts data to the pat field using java preparedStatements. The statement executes like this.... // test that the insert is unique SELECT ID from table where pat = 'MyValue'; // If that comes back with an ID, no insert is made // No ID means we insert. if(NoIdReturned){ INSERT INTO table(pat) Values('MyValue'); // I have added a test here to insure that the // data was inserted.... // I perform the same query as above // This is not in the production code. SELECT ID from table where pat = 'MyValue'; // In tests this comes back with a value every time. // The production code gets the last_insert_id() Select last_insert_id() as myID; // In tests this value and the one above always match. } OK. Here's the oddity, you would think that if I run this class again with the same parameter for MyValue, the flow would be... test for MyValue.. test returns "IDExists" No insert happens. That's not what is happening. If I run the class... once - Insert happens twice - Insert happens 3 times - ID is found and no insert happens. Oddity #2 - In test 2, the first query returns no ID. After the insert I ask for last insert ID and get 1234. I ask for the ID using the SAME query that returned me NO ID and I get 1233, the ID of the first insert. I have tested this as many ways as I can think. The insert is NOT a delayed insert. I have tried spacing out the tests. I have checked the DB using MySQLCC AND using the query that my test prints to screen and the first insert is always happening. Below are the actual test results from run#2. // Query to see if the data exists. SELECT ID from Rules where pat = 'AUTO\\_DOGGY'; Identity is -1 because no rows exist. AUTO_DOGGY Identity was -1 // Insert the rule Inserting new rule.AUTO_DOGGY // test Testing for AUTO_DOGGY // Same query as above SELECT ID from Rules where pat = 'AUTO\\_DOGGY'; // Below LIID = Last_Insert_ID() // Query ID is what is returned from query LIID = 124566 Query ID = 124565 HELP! God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. GOD BLESS AMERICA! To God Be The Glory! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
