I was debugging some DB code and Claude said that MySQL has a feature where if a SP returns multiple records, they must all be consumed before the query is closed. Otherwise you get Out of Sync Errors.
I am getting that, but its because I accidentally, didn’t wait in TMSWebcore client for the first query’s result , before trying to fetch the next query. But I want to fix it for the future.
I didn’t believe it so I asked another Claude session.
They both insist that their solution is correct and the other one is wrong. has anyone come across it?
Claude 1
try
FetchAll;
except
end;
Close;
Claude 2
try
repeat
NextRecordSet;
until False;
except
end;
Close;
So, for now, I am putting both in
It sounds like a totally stupid implementation in MySQL, and I have never been fond of it (there are other stupidities). Perhaps I should have stuck to Firebird.
I can’t really comment on MySQL or Firebird (not used it in a very long time). I’ve used microsoft sqlserver for the longest time and grown to dislike it more every day.
My client/server db of choice for the last 13 years is Postgresql - very easy to setup and administer - free and open source, but enterprise support is available if needed (I have neve needed it). Windows downloads are available from the link below, also available for linux/macos, docker etc
We’ve been supporting SQLServer and Postgresql in Continua CI for the last 13 years, and we have never had any issues with Postgresql, whereas with sqlserver we have run into all manner of problems (depending on the version) - the most common ones are deadlocks at customer sites that we have never been able to reproduce.
I am a MySQL ‘expert’ having used it at very large scale for many years - but I’ve also used Postgresql extensively in production. Postgresql is awesome with some quite cool features that are not found elsewhere. But, more importantly, it really is free. It does do a few things ‘the Postgresql way’ so it can be a learning curve if you’re more used to MySQL but you’ll easily get to grips with that.
Got a new customer who is mySQL so I am learning, so this is not an expert answer.
My guess would be (1) fetchall as it should do what it says on the tin - get all results back.
In FB you definitely don’t have to wait for all the results, in fact, I used to use the ability to close an SP query to terminate long running SP’s. Use Case: SP touching millions of records and doing something pretty complicated……. add a result row every n steps with a “where am I, how many rows have I processed”, make it wide enough to fill up a return buffer every couple of minutes…. On the client - process the results and if you got bored / wanted to abort, then just close the query… result SP stopped. I know there are other ways, but worked great back in IB 5.6.7 FB 1.5 days.
Very happy to be pointed to any other gotchas / docs on the foibles of mySQL from a Firebird PoV so I don’t kill the new customers system!
I have sort of forgotten some of the issues I had.
Mysql issues -
Cursor usage inside SP is more verbose
Replication does not execute triggers on slave. This can give different data. Hosts can disallow some types of triggers - this was very frustrating. As I put all my business logic in triggers and SP. But replication is sort of built in.
Triggers can not modify other rows of the same table. Absolutely useless feature
Returning multiple rows is more esoteric in Stored Procedures
Transactions are not multigenerational but may depend on engine you select
Auto-increment is less flexible as its defined in the column
Case sensitivity varies between Linux and Windows, so be careful.
No native Boolean type
Mysqlworkbench is a pig, PHPAdmin is better
Null is problematic. It doesn’t always evaluate all expressions involving null to null.
If you do not fetch all the rows that a SP is returning then basically the engine fails.
There is no trigger for “update and insert” - this annoys me the most, having to replicate massive amount of code.
There is no alter command for procedures and triggers. You have to drop them first.
Mysql Pros
Enum support, make use of this. Its fantastic. You can declare a column as enum (‘A’,‘I’) for Active/Inactive and it will enforce it. You write a char to it. Or if you want to be verbose you can use enum(‘Active’,‘Inactive, Deleted’). I am using these for status on each table.
Easier to access other databases in stored procs or triggers
Tips
Use backticks for database, table and column names when using them - it avoids issues
When altering schema, redefine the delimiter first - this should really be a CON. Claude forgets it all the time.
I have developed backup scripts for Linux and Windows when you want to do automated backups via cron. You will come across them on Stack Exchange.
Store timestamps in UTC (if clients are in different zones)
Mysqlworkbench limits updates for safety. I leave it on. So you cant do update x set a=1. But you can do it by adding a where clause such as where Id > 0. Its a hole to fall into
Mysqlworkbench wont remember the path you want to use for export and import. Why do turds insist on using the document folder for development tools? On the production sever, I leave it running for this reason. Yes, it applies to Delphi as well.
Please correct me if I have erred.
P.S. Use Claude to make schema changes. It can do it much faster, although it forgets
To redefine delimiters
drop procedures first
Sometimes usses features from other versions.
So instruct it first.
Excellent - thank you for your time digging into this. I’ve saved these away on my “next session” on their code. They are on 8.3 (from memory), so that is what I installed AND mySQLWorkbench.
Mysql procedures (when compiled) do not check if a table or column exists. Absolutely stupid feature. Every time you change a schema, you have to trawl through everything. (Note ot myself, must make it an automatic task for Claude, somehow). Arrghh. after writing this, I decided to set Claude loose, it found a few triggers that no longer work, because I changed one column name a month ago.
Mysql built in functions must have no space before the brackets. Yeah, someone didn’t know how to write code. The excuse is that, that’s the only way they know you are referring to an internal function. The error you get is not helpful.
