[SOLVED]Problem with MariaDB and trigger delete/create

[OH1KH]

I have mysterious problem with quite large program that uses Mariadb database.
Setup:
Fedora 43 linux with local MariaDB version  "Ver 15.1 Distrib 10.11.16-MariaDB"
Program is compiled with Lazarus 4.6 FPC 3.2.2 (From Fedora package)
I have replicated database in local server (Old Fedora, in local LAN same subnet) running  with Mariadb version "Ver 15.1 Distrib 10.5.15-MariaDB"

Using program there is a phase where I delete triggers from one table during for another operation and then restore triggers back. Table has around 20000 rows.
Now, if there are triggers program HALT when trying to delete them. If there are no triggers (deleted by previous tests, or manual) program HALT when trying to create triggers.

If I change Database Connect settings to point to my server replica of my database everything works fine without HALT during trigger delete and create.
(NOTE: Connection change requires program restart.)
If I then change Database Connect settings back to local computer, but use ipv6 localhost "::1" instead of 127.0.0.1, previously used, everything works ok again!

But only current session. If I restart program the HALT is again there even if ipv6 localhost still used.

I have made further debugging starting my program with --debug=-64 parameter. Then it prints all database communication that happens between Freepascal SQL unit and Sql server.
From that debug I can see that program sends proper trigger delete/create command to MariaDB but then everything stops.
I have traced that also by tcpdump and program really sends TCP frame having that command to MariaDB, but MariaDB does not give any response.

If I leave program in halt state and open a command console and make connect to MariaDB I can see that the trigger that program is trying to delete is still there in
 table.
If I issue same trigger delete command from MariaDB console it also halts without giving prompt back !!

When I kill program it also releases MariaDB console and the if I then check triggers the trigger has gone away (delete trigger command has been run, via released command console. I think).

I have tried to Google same kind of symptoms, without luck.
It can be MariaDB bug (because why mysql console halts too?), SQL unit bug or simply just my program bug (most likely).

But if there is any MariaDB guru who can suggest further testings I would like to hear about those.

P.S.
OK, I just talk about "program" without any example. It is in my GitHub https://github.com/OH1KH/CqrlogAlpha/tree/devel   but its is quite hard to make run without further assistance. And there is lots of source to look at.
How ever the trigger delete/create can be found from dLogUpload.pas  lines 1251 and 1293. Trigger create scripts  are in dData.lfm named scOnlineLogTriggers
(if someone has deep intrest ).

[OH1KH]

Used again whole day with this. No progress.

Tried to find out from where the TCP frame is sent from TSQL unit to sql server to find what happens when command is sent to sql server. Unfortunately the unit is a Class-jungle that is too difficult for my poor programming  skills to clear out.

What I found out was that  if table having triggers has only few (5) rows everything works perfectly.
With around 2000 rows the problem exist as well as with 20000 rows. That sounds like overflow somewhere.

Rolled back 8 versions of program and found out that this problem does not happen there. I guess it must be in changes of data unit but reverting is back while keeping all other changes still working is not reasonable. Mainly because there was then other, random, problems with SQL communications.

This time problem can be reproduced every time that should make it easier to find.
 

[valdir.marcos]

I have mysterious problem with quite large program that uses Mariadb database.
Setup:
Fedora 43 linux with local MariaDB version  "Ver 15.1 Distrib 10.11.16-MariaDB"
Program is compiled with Lazarus 4.6 FPC 3.2.2 (From Fedora package)
I have replicated database in local server (Old Fedora, in local LAN same subnet) running  with Mariadb version "Ver 15.1 Distrib 10.5.15-MariaDB"

Using program there is a phase where I delete triggers from one table during for another operation and then restore triggers back. Table has around 20000 rows.
Now, if there are triggers program HALT when trying to delete them. If there are no triggers (deleted by previous tests, or manual) program HALT when trying to create triggers.

If I change Database Connect settings to point to my server replica of my database everything works fine without HALT during trigger delete and create.
(NOTE: Connection change requires program restart.)
If I then change Database Connect settings back to local computer, but use ipv6 localhost "::1" instead of 127.0.0.1, previously used, everything works ok again!

But only current session. If I restart program the HALT is again there even if ipv6 localhost still used.

I have made further debugging starting my program with --debug=-64 parameter. Then it prints all database communication that happens between Freepascal SQL unit and Sql server.
From that debug I can see that program sends proper trigger delete/create command to MariaDB but then everything stops.
I have traced that also by tcpdump and program really sends TCP frame having that command to MariaDB, but MariaDB does not give any response.

If I leave program in halt state and open a command console and make connect to MariaDB I can see that the trigger that program is trying to delete is still there in
 table.
If I issue same trigger delete command from MariaDB console it also halts without giving prompt back !!

When I kill program it also releases MariaDB console and the if I then check triggers the trigger has gone away (delete trigger command has been run, via released command console. I think).

I have tried to Google same kind of symptoms, without luck.
It can be MariaDB bug (because why mysql console halts too?), SQL unit bug or simply just my program bug (most likely).

But if there is any MariaDB guru who can suggest further testings I would like to hear about those.

P.S.
OK, I just talk about "program" without any example. It is in my GitHub https://github.com/OH1KH/CqrlogAlpha/tree/devel   but its is quite hard to make run without further assistance. And there is lots of source to look at.
How ever the trigger delete/create can be found from dLogUpload.pas  lines 1251 and 1293. Trigger create scripts  are in dData.lfm named scOnlineLogTriggers
(if someone has deep intrest ).

Used again whole day with this. No progress.

Tried to find out from where the TCP frame is sent from TSQL unit to sql server to find what happens when command is sent to sql server. Unfortunately the unit is a Class-jungle that is too difficult for my poor programming  skills to clear out.

What I found out was that  if table having triggers has only few (5) rows everything works perfectly.
With around 2000 rows the problem exist as well as with 20000 rows. That sounds like overflow somewhere.

Rolled back 8 versions of program and found out that this problem does not happen there. I guess it must be in changes of data unit but reverting is back while keeping all other changes still working is not reasonable. Mainly because there was then other, random, problems with SQL communications.

This time problem can be reproduced every time that should make it easier to find.

There are probably no problems in either Lazarus or MariaDB (or MySQL).

The problem lies in your strategy.

Creating, modifying, and deleting triggers are tasks for a DBA, not a programmer. Each has their own role.

My suggestion is that you rethink your strategy so that you stop maintaining triggers within your program.

If you explain why you need to manipulate triggers within your program, perhaps I can help you with other alternatives.

[OH1KH]

HI!
Thanks for reply.
If you peeked GitHub link you noticed that this is not originally my program. But I have modified parts of it over 10 years.
Creators idea, what I have dig out from source, goes like follows:

The database that has triggers (3) holds information of kept radioamateur contacts: date, time frequency, mode , name, confirmation of contact etc.
Nowadays there are also internet logbooks where one can upload same information for others to check and for request some awards based on kept contacts.

This program can communicate with 5 of them. I just added 5th when noticed this problem in testing. But it has been there already before.

Now to the point:
When new contact is ADDED, DELETED or UPDATED somehow then each of those functions trigger it's own database trigger of those three defined.
Trigger causes row adding to another table called log_changes that keeps information of the contact's main data including ID of the contact table row and what kind of operation should be done to external online logs (insert,delete,update).
But because there are several Online logs and all have different APIs there is 3rd table that tells the online log's name and the upload state with this online log pointing to last processed ID of the log_upload table. That table is called upload_status.
To mark all jobs done for certain online log the upload_status where the online log's name equals is updated to have latest ID of log_changes marking "all done so far".

 log_changes  is a growing table, but upload_status has always as many rows as supported online logs (I.E. 5 now). Only the column having ID of log_changes changes.

Quite a mess, I would have done this at least bit different way. But done as done, and now it is hard to change without massive work.

Where we need to disable and enable trigger is for example situation where user wants to use online logs but does not want that editing fires trigger and updates log_chnages table for online log uploads.  It editing causes first delete contact from online log, then upload edited contact back and in worst case this all x5. APIs of online logs are quite stupid and can not change just one or more items on already uploaded contact info.
To prevent all that triggers are removed while editing and saving is done and returned back after saving.
Or other case is when user want's temporarily disable all online log updating, but will enable it sometime later.

Yes!
I know there is a trick making conditional trigger that looks existing session variable. I tried that but it does not work. The IF inside trigger does not find session variable made from elsewhere.
Perhaps writing value to some of the tables as column of its own and then peeking it from inside the trigger could work. I have not tested. (there are no examples for that. all are just talking about session variable usage. Perhaps that is not even possible).

Simplest way would be to assign a FPC boolean for this, but you can not read it backwards from mysql server.

[OH1KH]

WHAT is my biggest question is: 

How can program stop without any error and so that it looks that the SQL command is already sent to SQL server but that does not respond any way.

And that it even locks totally separated command line mysql console session if the same command is entered from console while FPC/Laz program is still halted for waiting the reply.

For me it looks like SQL server has got that command and locked it for execution (that locks the mysql console with same command at same time).
What makes my feel even stronger is that if hanging FPC/Laz program is killed and after that trigger status is checked via command console
the trigger has dropped I.E. the FPC/Laz program has sent it and it is received ok at server side but the execution has hanged up. I tested that ad it is really Laz/FPC that drops the trigger when command console is kept closed. Killing Laz/FPC program somehow triggers SQL execution (flushes SQL unit send buffer?)

When looking, with tcpdump, for drop trigger command for trigger that was already dropped (and program does not halt for that then)  it looks like after the TCP frame that holds the drop command text there comes another TCP frame without human readable content.
When program halts there exists only the TCP frame that holds the drop command text, but not the following frame (that may have "execute now, no more SQL text").

Looking at this side it looks like FPC SQL unit does not send the final execute flag to SQL server that already has locked SQL text in command buffer (disabling same command feed via another session from console).

So it is finally FPC/Laz problem, but probably because my program code does something stupid that SQL unit can not understand as an error and gets halted state.
Or there is some kind of overflow because all works if there are just some rows in contact table. But not when there are more, like 2000 or 20000 rows.

For example contact list TDBgrid loads always 500 contact rows at time for view. And there are many others too that may together overload the system.
88000 lines, 270000 words of source to check.

[valdir.marcos]

HI!
Thanks for reply.
If you peeked GitHub link you noticed that this is not originally my program. But I have modified parts of it over 10 years.
Creators idea, what I have dig out from source, goes like follows:

The database that has triggers (3) holds information of kept radioamateur contacts: date, time frequency, mode , name, confirmation of contact etc.
Nowadays there are also internet logbooks where one can upload same information for others to check and for request some awards based on kept contacts.

This program can communicate with 5 of them. I just added 5th when noticed this problem in testing. But it has been there already before.

Now to the point:
When new contact is ADDED, DELETED or UPDATED somehow then each of those functions trigger it's own database trigger of those three defined.
Trigger causes row adding to another table called log_changes that keeps information of the contact's main data including ID of the contact table row and what kind of operation should be done to external online logs (insert,delete,update).
But because there are several Online logs and all have different APIs there is 3rd table that tells the online log's name and the upload state with this online log pointing to last processed ID of the log_upload table. That table is called upload_status.
To mark all jobs done for certain online log the upload_status where the online log's name equals is updated to have latest ID of log_changes marking "all done so far".

log_changes  is a growing table, but upload_status has always as many rows as supported online logs (I.E. 5 now). Only the column having ID of log_changes changes.

Quite a mess, I would have done this at least bit different way. But done as done, and now it is hard to change without massive work.

Where we need to disable and enable trigger is for example situation where user wants to use online logs but does not want that editing fires trigger and updates log_chnages table for online log uploads.  It editing causes first delete contact from online log, then upload edited contact back and in worst case this all x5. APIs of online logs are quite stupid and can not change just one or more items on already uploaded contact info.
To prevent all that triggers are removed while editing and saving is done and returned back after saving.
Or other case is when user want's temporarily disable all online log updating, but will enable it sometime later.

Yes!
I know there is a trick making conditional trigger that looks existing session variable. I tried that but it does not work. The IF inside trigger does not find session variable made from elsewhere.
Perhaps writing value to some of the tables as column of its own and then peeking it from inside the trigger could work. I have not tested. (there are no examples for that. all are just talking about session variable usage. Perhaps that is not even possible).

Simplest way would be to assign a FPC boolean for this, but you can not read it backwards from mysql server.

WHAT is my biggest question is:

How can program stop without any error and so that it looks that the SQL command is already sent to SQL server but that does not respond any way.

And that it even locks totally separated command line mysql console session if the same command is entered from console while FPC/Laz program is still halted for waiting the reply.

For me it looks like SQL server has got that command and locked it for execution (that locks the mysql console with same command at same time).
What makes my feel even stronger is that if hanging FPC/Laz program is killed and after that trigger status is checked via command console
the trigger has dropped I.E. the FPC/Laz program has sent it and it is received ok at server side but the execution has hanged up. I tested that ad it is really Laz/FPC that drops the trigger when command console is kept closed. Killing Laz/FPC program somehow triggers SQL execution (flushes SQL unit send buffer?)

When looking, with tcpdump, for drop trigger command for trigger that was already dropped (and program does not halt for that then)  it looks like after the TCP frame that holds the drop command text there comes another TCP frame without human readable content.
When program halts there exists only the TCP frame that holds the drop command text, but not the following frame (that may have "execute now, no more SQL text").

Looking at this side it looks like FPC SQL unit does not send the final execute flag to SQL server that already has locked SQL text in command buffer (disabling same command feed via another session from console).

So it is finally FPC/Laz problem, but probably because my program code does something stupid that SQL unit can not understand as an error and gets halted state.
Or there is some kind of overflow because all works if there are just some rows in contact table. But not when there are more, like 2000 or 20000 rows.

For example contact list TDBgrid loads always 500 contact rows at time for view. And there are many others too that may together overload the system.
88000 lines, 270000 words of source to check.

Ten years is a long time and journey. Congratulations on your dedication.

After all this time, you must know the project very well.

Even though it's quite more new work, my first suggestion for you to consider would be to create a "version 2.0" of the project, keeping only the GUI so as not to impact users, and rewriting all the source code and database to reflect your current knowledge and maturity.

This redesign is painful, but the result usually compensates and brings lightness to your work from then on.

My second suggestion, obviously, is to create a new project and do everything from scratch (GUI, database, and source code).



Please, run this command to create a schema.sql file containing all structural definitions without table data:

Code: Bash  [Select][+][-]

1. $ mariadb-dump -u [username] -p'[password]' --no-data --routines --triggers --events --databases [database_name] > schema.sql

Explanation:

• no-data (-d): Dumps only the CREATE TABLE statements.
• routines (-R): Includes stored procedures and functions.
• triggers: Includes triggers for each table.
• events (-E): Includes event scheduler events.
• databases: Ensures CREATE DATABASE and USE statements are included.

WHAT is my biggest question is:

Administrative tasks, such as creating or deleting triggers, should be performed in database maintenance windows without any users connected to the database server.

Furthermore, performing those administrative tasks [within your program] with users still connected to the database server can lead to transactional control locking issues.

[OH1KH]

Well I know something about program, but not all.
My Pascal knowledge comes from 1982 when there was no PCs. Just big mainframe computer with power and capacity less than a smartphone today. Interfaced with green crts and teletype matrix printers connected with 300baud wired async lines.
No Object Oriented Programming with class-jungles or other bells and whistles like graphics widgets.

If I would be 50 years younger I could consider rewriting the program. Now I just try to keep it up and running. There are so many other similar programs, specially for Windoze.

I made the dump, it is attached for your interest.
Next I just try to find bypass for this problem. Simply as possible.
Perhaps reverting the data unit backwards 8 versions, when it looked like working with this side, and trying to fit it against current program version.
Or something else...

[OH1KH]

I managed to create conditional triggers that use another table's new column "stop_trigs" as semaphore.

Code: MySQL  [Select][+][-]

1. CREATE OR REPLACE TRIGGER `cqrlog_main_ai` AFTER INSERT ON `cqrlog_main` FOR EACH ROW
2. BEGIN
3.  IF NOT EXISTS (SELECT 1 FROM `db_version` WHERE stop_trigs = 1) THEN
4.    insert into `log_changes` (id_cqrlog_main,cmd,qsodate,time_on,callsign,mode,freq,band) values
5.    (NEW.id_cqrlog_main,'INSERT',NEW.qsodate,NEW.time_on,NEW.callsign,NEW.mode,NEW.freq,NEW.band);
6.  END IF;
7. END;  
8.  

That prevents program to halt, but does not solve the main problem I.E. why there is no error message, just only halt.

But that is enough for now...

[valdir.marcos]

Well I know something about program, but not all.
My Pascal knowledge comes from 1982 when there was no PCs. Just big mainframe computer with power and capacity less than a smartphone today. Interfaced with green crts and teletype matrix printers connected with 300baud wired async lines.
No Object Oriented Programming with class-jungles or other bells and whistles like graphics widgets.

If I would be 50 years younger I could consider rewriting the program. Now I just try to keep it up and running. There are so many other similar programs, specially for Windoze.

I made the dump, it is attached for your interest.
Next I just try to find bypass for this problem. Simply as possible.
Perhaps reverting the data unit backwards 8 versions, when it looked like working with this side, and trying to fit it against current program version.
Or something else...

I couldn't find any triggers inside the schema.sql file you provided.

P.S.
OK, I just talk about "program" without any example. It is in my GitHub https://github.com/OH1KH/CqrlogAlpha/tree/devel   but its is quite hard to make run without further assistance. And there is lots of source to look at.
How ever the trigger delete/create can be found from dLogUpload.pas  lines 1251 and 1293. Trigger create scripts  are in dData.lfm named scOnlineLogTriggers
(if someone has deep intrest ).

But I managed to find them in the file ~/Downloads/CqrlogAlpha-devel/src/dData.lfm:

Code: MySQL  [Select][+][-]

1. object scOnlineLogTriggers: TSQLScript
2.     Transaction = trmQ
3.     AutoCommit = False
4.     UseDollarString = False
5.     Directives.Strings = (
6.       'SET TERM'
7.       'COMMIT'
8.       '#IFDEF'
9.       '#IFNDEF'
10.       '#ELSE'
11.       '#ENDIF'
12.       '#DEFINE'
13.       '#UNDEF'
14.       '#UNDEFINE'
15.     )
16.     Script.Strings = (
17.       'CREATE TRIGGER `cqrlog_main_ai` AFTER INSERT ON `cqrlog_main` FOR EACH ROW'
18.       'insert into log_changes(id_cqrlog_main,cmd,qsodate,time_on,callsign,mode,freq,band) values'
19.       '  (NEW.id_cqrlog_main,''INSERT'',NEW.qsodate,NEW.time_on,NEW.callsign,NEW.mode,NEW.freq,NEW.band);'
20.       ''
21.       'CREATE TRIGGER `cqrlog_main_bd` BEFORE DELETE ON `cqrlog_main` FOR EACH ROW'
22.       'insert into log_changes(id_cqrlog_main,cmd,old_qsodate, old_time_on, old_callsign, old_mode, old_band,old_freq)'
23.       'values (OLD.id_cqrlog_main,''DELETE'',OLD.qsodate, OLD.time_on, OLD.callsign, OLD.mode, OLD.band, OLD.freq);'
24.       ''
25.       'CREATE TRIGGER `cqrlog_main_bu` BEFORE UPDATE ON `cqrlog_main` FOR EACH ROW'
26.       'insert into log_changes(id_cqrlog_main,cmd,old_qsodate, old_time_on, old_callsign, old_mode, old_band,old_freq,'
27.       'qsodate, time_on, callsign, mode, band,freq)'
28.       'values (OLD.id_cqrlog_main,''UPDATE'',OLD.qsodate, OLD.time_on, OLD.callsign, OLD.mode, OLD.band,OLD.freq,'
29.       'NEW.qsodate, NEW.time_on, NEW.callsign, NEW.mode, NEW.band, NEW.freq);'
30.     )
31.     Terminator = ';'
32.     CommentsinSQL = True
33.     UseSetTerm = True
34.     UseCommit = True
35.     UseDefines = True
36.     Left = 688
37.     Top = 576
38.   end
39.   object

Are there any more triggers beyond the three below?

Code: MySQL  [Select][+][-]

1.       'CREATE TRIGGER `cqrlog_main_ai` AFTER INSERT ON `cqrlog_main` FOR EACH ROW'
2.       'CREATE TRIGGER `cqrlog_main_bd` BEFORE DELETE ON `cqrlog_main` FOR EACH ROW'
3.       'CREATE TRIGGER `cqrlog_main_bu` BEFORE UPDATE ON `cqrlog_main` FOR EACH ROW'

There seems to be a very old problem in the design of your project's database.

log_changes  is a growing table, but upload_status has always as many rows as supported online logs (I.E. 5 now). Only the column having ID of log_changes changes.

Perhaps an excessive growth of the "log_changes" table, which could be solved with a cleanup routine being executed under certain conditions [in a trigger of the database itself or in a routine in your program] or from time to time, periodically.

Considering the premise that administrative tasks in a database should be performed by a DBA and not within a program, instead of the original problem being corrected within the database structure itself, the attempt at correction was repeated in numerous points of the program:

Code: Bash  [Select][+][-]

1. $ find ~/Downloads/CqrlogAlpha-devel/src/ -type f -name "*.*" -exec grep -rni "DisableOnlineLogSupport" {} +
2. ~/Downloads/CqrlogAlpha-devel/src/fMain.pas:1383:    dmLogUpload.DisableOnlineLogSupport;
3. ~/Downloads/CqrlogAlpha-devel/src/fMain.pas:2359:     dmLogUpload.DisableOnlineLogSupport;
4. ~/Downloads/CqrlogAlpha-devel/src/fMain.pas:2759:     dmLogUpload.DisableOnlineLogSupport;
5. ~/Downloads/CqrlogAlpha-devel/src/fLoTWExport.pas:184:          dmLogUpload.DisableOnlineLogSupport;
6. ~/Downloads/CqrlogAlpha-devel/src/dData.pas:3939:    dmLogUpload.DisableOnlineLogSupport;
7. ~/Downloads/CqrlogAlpha-devel/src/dData.pas:3983:    dmLogUpload.DisableOnlineLogSupport;
8. ~/Downloads/CqrlogAlpha-devel/src/feQSLUpload.pas:276:      dmLogUpload.DisableOnlineLogSupport;
9. ~/Downloads/CqrlogAlpha-devel/src/fRebuildMembStat.pas:162:      dmLogUpload.DisableOnlineLogSupport;
10. ~/Downloads/CqrlogAlpha-devel/src/fImportProgress.pas:770:      dmLogUpload.DisableOnlineLogSupport;
11. ~/Downloads/CqrlogAlpha-devel/src/fImportProgress.pas:1130:    dmLogUpload.DisableOnlineLogSupport;
12. ~/Downloads/CqrlogAlpha-devel/src/dLogUpload.pas:70:    procedure DisableOnlineLogSupport;
13. ~/Downloads/CqrlogAlpha-devel/src/dLogUpload.pas:1251:procedure TdmLogUpload.DisableOnlineLogSupport;
14. ~/Downloads/CqrlogAlpha-devel/src/fNewQSO.pas:3506:       dmLogUpload.DisableOnlineLogSupport;
15. ~/Downloads/CqrlogAlpha-devel/src/fPreferences.pas:1805:      dmLogUpload.DisableOnlineLogSupport
16. ~/Downloads/CqrlogAlpha-devel/src/fPreferences.pas:1811:        dmLogUpload.DisableOnlineLogSupport;
17. ~/Downloads/CqrlogAlpha-devel/src/fExLabelPrint.pas:565:           dmLogUpload.DisableOnlineLogSupport;
18. ~/Downloads/CqrlogAlpha-devel/src/fMarkQSL.pas:81:     dmLogUpload.DisableOnlineLogSupport;
19.  
20.  
21. $ find ~/Downloads/CqrlogAlpha-devel/src/ -type f -name "*.*" -exec grep -rni "EnableOnlineLogSupport" {} +
22. ~/Downloads/CqrlogAlpha-devel/src/fMain.pas:1384:    dmLogUpload.EnableOnlineLogSupport;
23. ~/Downloads/CqrlogAlpha-devel/src/fMain.pas:2381:   dmLogUpload.EnableOnlineLogSupport;
24. ~/Downloads/CqrlogAlpha-devel/src/fMain.pas:2780:     dmLogUpload.EnableOnlineLogSupport;
25. ~/Downloads/CqrlogAlpha-devel/src/fLoTWExport.pas:206:          dmLogUpload.EnableOnlineLogSupport(False)
26. ~/Downloads/CqrlogAlpha-devel/src/dData.pas:3962:      dmLogUpload.EnableOnlineLogSupport(False);
27. ~/Downloads/CqrlogAlpha-devel/src/dData.pas:4006:      dmLogUpload.EnableOnlineLogSupport(False);
28. ~/Downloads/CqrlogAlpha-devel/src/feQSLUpload.pas:298:      dmLogUpload.EnableOnlineLogSupport(False)
29. ~/Downloads/CqrlogAlpha-devel/src/fRebuildMembStat.pas:218:      dmLogUpload.EnableOnlineLogSupport(False);
30. ~/Downloads/CqrlogAlpha-devel/src/fImportProgress.pas:954:      dmLogUpload.EnableOnlineLogSupport(False)
31. ~/Downloads/CqrlogAlpha-devel/src/fImportProgress.pas:1283:      dmLogUpload.EnableOnlineLogSupport(False);
32. ~/Downloads/CqrlogAlpha-devel/src/dLogUpload.pas:71:    procedure EnableOnlineLogSupport(RemoveOldChanges : Boolean = True);
33. ~/Downloads/CqrlogAlpha-devel/src/dLogUpload.pas:1293:procedure TdmLogUpload.EnableOnlineLogSupport(RemoveOldChanges : Boolean = True);
34. ~/Downloads/CqrlogAlpha-devel/src/dLogUpload.pas:1325:          Writeln('EnableOnlineLogSupport:',E.Message);
35. ~/Downloads/CqrlogAlpha-devel/src/dLogUpload.pas:1354:        Writeln('EnableOnlineLogSupport:',E.Message);
36. ~/Downloads/CqrlogAlpha-devel/src/fNewQSO.pas:3507:       dmLogUpload.EnableOnlineLogSupport;
37. ~/Downloads/CqrlogAlpha-devel/src/fPreferences.pas:1812:      dmLogUpload.EnableOnlineLogSupport
38. ~/Downloads/CqrlogAlpha-devel/src/fExLabelPrint.pas:660:     dmLogUpload.EnableOnlineLogSupport;
39. ~/Downloads/CqrlogAlpha-devel/src/fMarkQSL.pas:255:     dmLogUpload.EnableOnlineLogSupport;

Those program patches worsened over time, and you just added another layer:

I managed to create conditional triggers that use another table's new column "stop_trigs" as semaphore.

Code: MySQL  [Select][+][-]

1. CREATE OR REPLACE TRIGGER `cqrlog_main_ai` AFTER INSERT ON `cqrlog_main` FOR EACH ROW
2. BEGIN
3.  IF NOT EXISTS (SELECT 1 FROM `db_version` WHERE stop_trigs = 1) THEN
4.    insert into `log_changes` (id_cqrlog_main,cmd,qsodate,time_on,callsign,mode,freq,band) values
5.    (NEW.id_cqrlog_main,'INSERT',NEW.qsodate,NEW.time_on,NEW.callsign,NEW.mode,NEW.freq,NEW.band);
6.  END IF;
7. END;

That prevents program to halt, but does not solve the main problem I.E. why there is no error message, just only halt.

But that is enough for now...

Now to the point:
When new contact is ADDED, DELETED or UPDATED somehow then each of those functions trigger it's own database trigger of those three defined.
Trigger causes row adding to another table called log_changes that keeps information of the contact's main data including ID of the contact table row and what kind of operation should be done to external online logs (insert,delete,update).
But because there are several Online logs and all have different APIs there is 3rd table that tells the online log's name and the upload state with this online log pointing to last processed ID of the log_upload table. That table is called upload_status.
To mark all jobs done for certain online log the upload_status where the online log's name equals is updated to have latest ID of log_changes marking "all done so far".

 log_changes  is a growing table, but upload_status has always as many rows as supported online logs (I.E. 5 now). Only the column having ID of log_changes changes.

Quite a mess, I would have done this at least bit different way. But done as done, and now it is hard to change without massive work.

Where we need to disable and enable trigger is for example situation where user wants to use online logs but does not want that editing fires trigger and updates log_chnages table for online log uploads.  It editing causes first delete contact from online log, then upload edited contact back and in worst case this all x5. APIs of online logs are quite stupid and can not change just one or more items on already uploaded contact info.
To prevent all that triggers are removed while editing and saving is done and returned back after saving.
Or other case is when user want's temporarily disable all online log updating, but will enable it sometime later.

It's still confusing.

Why can't those 3 triggers be permanent within the database?

Why do they need to be deleted and recreated in so many places of the program?

If you can answer those two questions, I can guide you in correcting the database structure so that this manipulation of triggers is removed from the program.

Now to the point:
When new contact is ADDED, DELETED or UPDATED somehow then each of those functions trigger it's own database trigger of those three defined.
...
But because there are several Online logs and all have different APIs there is 3rd table that tells the online log's name and the upload state with this online log pointing to last processed ID of the log_upload table. That table is called upload_status.
To mark all jobs done for certain online log the upload_status where the online log's name equals is updated to have latest ID of log_changes marking "all done so far".
...
Quite a mess, I would have done this at least bit different way. But done as done, and now it is hard to change without massive work.

Where we need to disable and enable trigger is for example situation where user wants to use online logs but does not want that editing fires trigger and updates log_chnages table for online log uploads.  It editing causes first delete contact from online log, then upload edited contact back and in worst case this all x5. APIs of online logs are quite stupid and can not change just one or more items on already uploaded contact info.

Or I can help you modify the program to prevent triggers from firing unnecessarily.

[valdir.marcos]

Well I know something about program, but not all.
My Pascal knowledge comes from 1982 when there was no PCs. Just big mainframe computer with power and capacity less than a smartphone today. Interfaced with green crts and teletype matrix printers connected with 300baud wired async lines.
No Object Oriented Programming with class-jungles or other bells and whistles like graphics widgets.

If I would be 50 years younger I could consider rewriting the program.

Just out of historical curiosity, in another thread here or on a YouTube video, I'd like to hear more about your experiences as a young person in the early days of programming.

Thank you for sharing.

[OH1KH]

Hi!
Collection of answers to you messages:

Sorry,forgot to repeat that scripts are in dData.lfm as I originally mentioned in first message. They were not added to zip.

log_changes does grow but user can clean it with option from main.lfm menu: "Online logs/mark all QSO as uploaded". Unfortunately this is not clearly stated anywhere for user to know the growth, and that this menu option cleans the table.


There is not DBA. User is that, because all data is held on local machine (except that user can define another SQL server, instead of local, that could be anywhere in network).
Logs are local, but this option "Online Logs" are kind of replication to internet where users can store their logs for everyone to search with any logging program that supports those internet logs.
This is quite long story to explain if one is not involved with radio amateur hobby.



"Those program patches worsened over time, and you just added another layer:", you say.
I think I replaced those original triggers with *better* solution that does not need deleting and recreating.



With this new conditional trigger (there are all 3 of old ones replaced with this similar solution) triggers can now be permanent.
Disabling and enabling their functions, now with column "stop_trig" in DB_version table, we can stop and start newly added qsos (contact rows) to be added to Online log loading list.
Disabling and enabling is needed when user wants that modifying an old contact record does not cause it's recreation (delete + insert) again to Online logs

Now it is in testing and looks like a good solution. So far...


In old days when I was in technical school the first contact to programming was a multi shared Basic mainframe computer that could serve 8 (or 16, I don't remember any more) terminals. Those terminals were mechanical typewriters (telex machines) connected with 50baud async wired connect.

Next year they got more money and got bigger machine with more terminals, now 300baud matrix typewriters and green CRTs. Programming language changed form Basic to Pascal and now students got small quotas to keep some of their programs online. Saving/loading program, if it was not in quota, a paper ribbon punch hole reader/writer was used.
I think I had tens of meters paper ribbon then.

At the same time I bought my first own computer. Sinclair ZX81. With built in Basic and 1k (yes, 1024 bytes) memory it could do really something.
Saving and loading was done with C cassette recorder using coded audio.
Very soon I built more memory for it. 4k expansion. Later bought 16k factory made ram. With that and learning z80 CPU machine code one could really do usable programs.
It used a tailored chip that could produce video display using TV. With one friend we got disassembly from the Basic interpreter that was the whole OS (8k EEPROM) for ZX81 and by using that we could dig out how this tailored chip was designed to produce the video signal.
When knowledge increased I could add another 8k (using 16k eprom instead) to produce more OS commands like printing from program to electrical typewriter. And of course to build needed electronics to connect with serial or parallel  I/O lines to Z80 CPU.

Later I bought Amstrad-128 that was already a Big machine. Passed Commodore-64 because Amstrad used same Z80 CPU that I already could program directly with machine code.

After that came the PCs starting with 8086 CPU and programming languages like Turbo Basic, and Turbo Pascal from Borland.

[valdir.marcos]

Hi!
Collection of answers to you messages:

Sorry,forgot to repeat that scripts are in dData.lfm as I originally mentioned in first message. They were not added to zip.

log_changes does grow but user can clean it with option from main.lfm menu: "Online logs/mark all QSO as uploaded". Unfortunately this is not clearly stated anywhere for user to know the growth, and that this menu option cleans the table.


There is not DBA. User is that, because all data is held on local machine (except that user can define another SQL server, instead of local, that could be anywhere in network).
Logs are local, but this option "Online Logs" are kind of replication to internet where users can store their logs for everyone to search with any logging program that supports those internet logs.
This is quite long story to explain if one is not involved with radio amateur hobby.



"Those program patches worsened over time, and you just added another layer:", you say.
I think I replaced those original triggers with *better* solution that does not need deleting and recreating.



With this new conditional trigger (there are all 3 of old ones replaced with this similar solution) triggers can now be permanent.
Disabling and enabling their functions, now with column "stop_trig" in DB_version table, we can stop and start newly added qsos (contact rows) to be added to Online log loading list.
Disabling and enabling is needed when user wants that modifying an old contact record does not cause it's recreation (delete + insert) again to Online logs

Now it is in testing and looks like a good solution. So far...

Fingers crossed! 

[valdir.marcos]

Well I know something about program, but not all.
My Pascal knowledge comes from 1982 when there was no PCs. Just big mainframe computer with power and capacity less than a smartphone today. Interfaced with green crts and teletype matrix printers connected with 300baud wired async lines.
No Object Oriented Programming with class-jungles or other bells and whistles like graphics widgets.

If I would be 50 years younger I could consider rewriting the program.

Just out of historical curiosity, in another thread here or on a YouTube video, I'd like to hear more about your experiences as a young person in the early days of programming.

Thank you for sharing.

In old days when I was in technical school the first contact to programming was a multi shared Basic mainframe computer that could serve 8 (or 16, I don't remember any more) terminals. Those terminals were mechanical typewriters (telex machines) connected with 50baud async wired connect.

Next year they got more money and got bigger machine with more terminals, now 300baud matrix typewriters and green CRTs. Programming language changed form Basic to Pascal and now students got small quotas to keep some of their programs online. Saving/loading program, if it was not in quota, a paper ribbon punch hole reader/writer was used.
I think I had tens of meters paper ribbon then.

At the same time I bought my first own computer. Sinclair ZX81. With built in Basic and 1k (yes, 1024 bytes) memory it could do really something.
Saving and loading was done with C cassette recorder using coded audio.
Very soon I built more memory for it. 4k expansion. Later bought 16k factory made ram. With that and learning z80 CPU machine code one could really do usable programs.
It used a tailored chip that could produce video display using TV. With one friend we got disassembly from the Basic interpreter that was the whole OS (8k EEPROM) for ZX81 and by using that we could dig out how this tailored chip was designed to produce the video signal.
When knowledge increased I could add another 8k (using 16k eprom instead) to produce more OS commands like printing from program to electrical typewriter. And of course to build needed electronics to connect with serial or parallel  I/O lines to Z80 CPU.

Later I bought Amstrad-128 that was already a Big machine. Passed Commodore-64 because Amstrad used same Z80 CPU that I already could program directly with machine code.

After that came the PCs starting with 8086 CPU and programming languages like Turbo Basic, and Turbo Pascal from Borland.

Could you tell a bit more about your experience with mainframe computers, punched cards and "paper ribbon punch hole reader/writer" before the PC era began?

Have you started programming with machine code or Assembly?

When and why did you transit from Assembly to Basic?

When and why did you transit from Basic to Pascal?

Have you worked profissionally with programming or it was always a hobby on your personal life?