Has this class worked properly in the past? Have you tried executing the queries your class executes manually via the mysql client or a similar application removing your Java code from the equation? Have you run 'check table' if the table is either MyISAM or InnoDB?
On Jul 7, 2004, at 11:43 AM, Paul McNeil wrote:
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]
