Help with MySQL plz lazarus / 16-Databases ---------------------------------------------------------------------- hinst · 2014-05-13 17:11 When I try to execute this statement: Code: [Select]mysql> insert into LinearCalibratorData (id, ActualValue, BiasedValue, Draft) values (1, 1, 2, TRUE); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionWell, it does nothing for some time, then I get this error. I wonder why. Seems like my SQL statement causes MySQL glitch, but there is nothing special about it, I am just trying to write data I originally discovered this error while working on my application, but then I tried executing it from MySQL command line client and got this --- UPDATE: I think I discovered a serious bug. Not this statement, but a group of statements (those a primitive ones) when executed together in one transaction using FreePascal fcl-db & sqldb cause MySQL server to glitch, and it won't insert or delete data until restarted. Perhaps it will take some time to reproduce this bug in a separated  minimal program, I just hope I won't need to do this; maybe someone knows something about this; please share   In my transaction I read table A then I delete all from table B then I insert row into table B Reading works. Nothing gets deleted or inserted. I close query before executing next two statements, but I keep transaction opened ---------------------------------------------------------------------- tionov · 2014-05-14 03:57 Quote from: hinst on May 13, 2014, 07:11:06 pmUPDATE: I think I discovered a serious bug. Not this statement, but a group of statements (those a primitive ones) when executed together in one transaction using FreePascal fcl-db & sqldb cause MySQL server to glitch, and it won't insert or delete data until restarted. Perhaps it will take some time to reproduce this bug in a separated  minimal program, I just hope I won't need to do this; maybe someone knows something about this; please share   In my transaction I read table A then I delete all from table B then I insert row into table B Reading works. Nothing gets deleted or inserted. I close query before executing next two statements, but I keep transaction opened Database deadlock IMHO. Caused by sqldb always starting a transaction. Look here: http://forum.lazarus.freepascal.org/index.php/topic,24535.0.html ---------------------------------------------------------------------- eara · 2014-05-15 11:58 Can you provide us, version of lazarus/fpc, db server/session settings etc Also the sql queries ,special the delete all And if possible some code and a cold beer ---------------------------------------------------------------------- mirce.vladimirov · 2014-05-18 19:48 I have experienced this kind of trouble with a table that had about 30000 records but didn't had a primary key declared.  The table had unique key but no primary key and had no problem whith smaller number of records. When I changed the key from unique to primary my problem was solved and never apeared again altough the number of records is increasing every day. And i think it works a bit faster now. It is recomended that tables have primary key, no matter which database we use MySQL, MSSQL, PGSQL, SQLite, FBSQL, DB2... ---------------------------------------------------------------------- bambamns · 2014-05-19 03:51 Are you using FPC 2.6.4 ? There is a one bug connected with transactions Commit / CommitRetaining. Solution is to use FPC 2.6.2 or trunk 2.7.1 ---------------------------------------------------------------------- eara · 2014-05-20 18:36 @bambamns: what is the bug you are mention? bug-id ? link ? ---------------------------------------------------------------------- mangakissa · 2014-05-21 07:25 Quote from: mirce.vladimirovWhen I changed the key from unique to primary my problem was solved and never apeared again altough the number of records is increasing every day. And i think it works a bit faster now.Because the new versions of MySQL does a recordlocking on the primary key and can handle it much better by deleting and updating records. ---------------------------------------------------------------------- bambamns · 2014-05-23 00:30 @eara http://bugs.freepascal.org/view.php?id=26068