Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
8
Erster Beitrag:
vor 1 Woche, 2 Tagen
Letzter Beitrag:
vor 1 Woche, 1 Tag
Beteiligte Autoren:
GuenterP, stefan.kern, Fabrice Harari, Danny Lauwers, Beauregarderson

MySQL/Replication Advice

Startbeitrag von Beauregarderson am 16.06.2017 05:39

I have created a POS application using version 20. It uses hfsql c/s files. I use HAdd/Modify/Delete for single records and HExecuteSQLQuery for multiple records. I was going to use Fabrice's WXReplication, but from the documentation it seems that any queries that use HExecuteSQLQuery will not work with replication. It also sounds necessary to use WebDev server and I don't want to have to buy WebDev.

I'm starting to think it would be less work to switch to MySQL which I believe has built in replication I can trust compared to PCSoft's built in replication. Can anyone share their experiences using WinDev with MySQL and/or replication?

Antworten:

Hi,

we have a POS solution as well and we're bi-directionally replicating the databases of cash registers and server databases using our own solution, which is 10+ years old. Direct Database access is done from the cash registers which are initiating the replication. While MySQL databases are not secure and you'd have to use a web service or a socket connection, HFSQL C/S is secure. We never ever had a security issue with HFSQL C/S. So, switching to MySQL would cause a lot of additional work to you and MySQL isn't free anymore. The main database of our system is running on the servers (Windows and CentOS ones) of our customers. Replication is done on basis of GUIDs, the current "official" system is using 8-byte integers, which I deeply mistrust. Fabrice Harari's system uses GUIDs too. See thread http://27130.foren.mysnip.de/read.php?27131,779939,779939#msg-779939 Fabrice has an open source replication system, see the link in his signature.

von GuenterP - am 16.06.2017 06:10
Hi,

Can't you use Triggers in the HFSQL Server side to catch the changes that the stored procedure is making, and handle them the same way as HADD, HMODIFY, HDELETE ?

https://doc.pcsoft.fr/en-US/?3044369&name=Server_trigger

If you would implement this, I think you can catch all changes whether they are made in the application or on the server. And you only need to handle this on the server else you catch the change twice.

Maybe something you can check-out !

Hope this helps !
Danny

von Danny Lauwers - am 16.06.2017 06:39
Hi,

Quote
Beauregarderson
I have created a POS application using version 20. It uses hfsql c/s files. I use HAdd/Modify/Delete for single records and HExecuteSQLQuery for multiple records. I was going to use Fabrice's WXReplication, but from the documentation it seems that any queries that use HExecuteSQLQuery will not work with replication. It also sounds necessary to use WebDev server and I don't want to have to buy WebDev.

I'm starting to think it would be less work to switch to MySQL which I believe has built in replication I can trust compared to PCSoft's built in replication. Can anyone share their experiences using WinDev with MySQL and/or replication?


You can use all the READING queries you want. If you are using WRITING queries, then no, it's not possible (and I explain in details why you WILL have data inconsistencies in some cases when trying to replicate the result of queries on temporaly different datasets).

Best regards

von Fabrice Harari - am 16.06.2017 12:03
Hi all,

different Situation, but same question: I have a System running where I Need to synchronize two databases, one inhouse at the customers site and one for a customer Portal running on a rented root Server (Windows).
I also use many SQL Update Statements, so Fabrice's solution does not work for me.
I am using the buildt in HFSQL bi-directional replication, which is working fine.

But I am running into Performance issues with the HFSQL Server. I installed MySQL for test reasons, and I do not have this issues using MySQL.

So I am also thinking about to Switch to MySQL, using the MySQL replication.
@ Guenther: Why do you think MySQL is not secure, because of the open port? As I only have 2 databases this should not be an issues, as I would use VPN for the replication.
@ Fabrice:
Quote

and I explain in details why you WILL have data inconsistencies in some cases when trying to replicate the result of queries on temporaly different datasets

Why?

Best Regards

Stefan.

von stefan.kern - am 16.06.2017 21:54
Quote

@ Guenter: Why do you think MySQL is not secure, because of the open port? As I only have 2 databases this should not be an issues, as I would use VPN for the replication.


MySQL is a hacker's generic playground. We have more than a dozen web sites under Wordpress (they're using MySQL) running and we routinely stop any internet communication with MySQL databases. My nephew's company takes care of 500+ Wordpress web sites and again, all MySQL databases are closed to the outside. Maybe that a VPN connection helps in isolating communication between two computers on the web but not necessarily. From a viewpoint of security we routinely mistrust MySQL ...

Performance problems? Which type of? Did you adjust the HFSQL server using https://help.windev.com/en-US/?3540004&name=CC_HF_Server_Configuration ?? HFSQL Server is configurable and shouldn't give any performance problems unless there are several hundred clients communicating at the same time. If your server hardware allows for more than a single thread then you could run several Manta services on the server (number of processor cores - 1) to ease the load.

von GuenterP - am 17.06.2017 05:30
Hi Stefan,

I recommend to read https://www.terrashop.de/Buch/Metasploit-Die-Kunst-des-Penetration-Testing-David-Kennedy-Jim-OGorman-Devon-Kearns-Mati-Aharoni-ISBN-3826691768/art/82669176/ (it's only 8,- Euros at Terrashop). HFSQL is far from a hacker's or scriptkiddy's focus, MySQL and its standard ports 1433 & 1434 are well-known targets. Moreover, if you define different ports for MySQL, which is possible with newer versions, it will be so nice to tell attackers the correct port numbers when asking the standard ports.

von GuenterP - am 17.06.2017 05:52
Quote

Performance problems? Which type of?


Hi Günther,

the Performance is ok except of one query, which is very slow and used very often.

3 Tables:

Auftrag_Kopf (Shipping_Orders)
===========
Auftrag_KopfID (Primary Key, guid)
.....

Auftrag_Pos (Shipping Details)
===========
Auftrag_PosID (Primary Key, guid)
refAuftrag_Kopf (guid)
sReference (string)
nPieces (numeric)
nWeight (Numeric)
...

Umsatz (Shipping costs)
========
UmsatzID (Primary Key, guid)
refAuftragKopf (guid)
sDiscription (string)
nAmount (Numeric)


Auftrag_kopf : Auftrag_pos 1:n
Auftrag_kopf : Umsatz 1:n

so each order has:
1 row in Auftrag_kopf containing adress data etc-
n rows in Auftrag_pos containing the shipping Details
n rowse in Umsatz containing the shipping costs.


I Need a result which Shows in one row:
Adress data from Auftrag_kopf, the total sum of weight and pieces from Auftrag_pos and the total sum of shipping costs from table Umsatz.

I tried to join the tables, which works with excellent Performance, but the result is wrong,
as: If i have 2 lines in Auftrag_pos the sum of the entries in table Umsatz are calculated twice.

So I join only Auftrag_kopf with auftrag_pos and calculate the sum of table Umsatz with an Sub query, which is extremely slow.

Best Regards


Stefan.

von stefan.kern - am 17.06.2017 11:50
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.