Funny read: MySQL/MariaDB vs. PostgreSQL Datahandling

Forum for things that doesn't really have anything to do with hMailServer. Such as php.ini, beer, etc etc.
Post Reply
User avatar
Dravion
Senior user
Senior user
Posts: 1397
Joined: 2015-09-26 11:50
Location: Germany
Contact:

Funny read: MySQL/MariaDB vs. PostgreSQL Datahandling

Post by Dravion » 2017-10-23 17:14

I just upgrade to the new Win10 1709 Version and there is time for a good read about MySQL/MariaDB vs. PostgreSQL
DB's things.
http://www.cybertec.at/why-favor-postgr ... adb-mysql/

User avatar
mattg
Moderator
Moderator
Posts: 19883
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: Funny read: MySQL/MariaDB vs. PostgreSQL Datahandling

Post by mattg » 2017-10-24 01:01

The comments section is where the fun is...

No comparison to MS SQL, or like most Linux purists just wouldn't consider a Microsoft product, even if it is now available on Linux

I've never used PostgreSQL in Anger, and only a few times in play
I've used MySQL (and MS SQL) well well over 15 years without a problem that wasn't recoverable fairly easily.
Never noticed data corruption, that also wasn't evident in say Excel or electronic calculations in general.
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

User avatar
Dravion
Senior user
Senior user
Posts: 1397
Joined: 2015-09-26 11:50
Location: Germany
Contact:

Re: Funny read: MySQL/MariaDB vs. PostgreSQL Datahandling

Post by Dravion » 2017-10-24 06:42

Yeah, you can use MySQL successfully but you should know its weakspots as well.

ps: I allready tested hMailServer 5.6.6 with MS-SQL Server 2017 on Linux/Ubuntu as remote database.
I noticed you need at least 2 GBytes RAM for MS-SQL 2017 on Linux and 1 GBytes for Linux itself to be able to
start MS-SQL 2017 on Linux and only 64-Bit Linux versions are supported.
See viewtopic.php?f=10&t=31646

Regarding the blogpost, this is the Problem:

MySQL
INSERT INTO data
VALUES (1, 1234.5678);
Query OK, 1 row affected, 1 warning (0.01 sec)

Postgres
INSERT INTO data
VALUES (1, 1234.5678);
ERROR: numeric field overflow
DETAIL: A field with precision 4, scale 2 must round to an absolute

Both SQL-DB's doing the same operations but Postgres cancels and abort the SQL Query because it will violate
the Dataintegrity while MySQL will silently accept this corruption. Someone in the comment section mentioned
you can reconfigure MySQL config file to prevent such behavior of MySQL but you need to know the param and understand the Problem.

For example:
If you use MySQL with PHP and you doing some sort of calculation wrong in PHP and your script inserts the data
into a MySQL table, you are doomed and your data ist corrupted. Postgres simply will raise a error and reject such
a query to prevent damage to your data.

User avatar
mattg
Moderator
Moderator
Posts: 19883
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: Funny read: MySQL/MariaDB vs. PostgreSQL Datahandling

Post by mattg » 2017-10-24 07:31

my understanding of that article was that that MySQL rounds the number to what is allowed in the field, whereas PostGreSQL just stops.

This is only an issue if you try to insert a field value that is not allowed.

Personally I think the MySQL method of rounding to fit the constraints of the field is OK
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

tunis
Normal user
Normal user
Posts: 215
Joined: 2015-01-05 20:22
Location: Sweden

Re: Funny read: MySQL/MariaDB vs. PostgreSQL Datahandling

Post by tunis » 2017-10-24 10:34

You can change SQL_MODE for this in mysql.ini for mariadb and mysql.
https://mariadb.com/kb/en/library/sql-mode/#strict-mode
https://dev.mysql.com/doc/refman/5.7/en ... ode-strict

Strict mode are the default on mariadb 10.2.4 and up.

I tested this on my mariadb 10.2.9 and I get same errors as in postgreSQL.
HMS 5.6.8 B2437.17 on Windows Server 2019 Core VM.
HMS 5.6.8 B2437.17 on Windows Server 2016 Core VM.
HMS 5.6.7 B2425.16 on Windows Server 2012 R2 Core VM.

User avatar
Dravion
Senior user
Senior user
Posts: 1397
Joined: 2015-09-26 11:50
Location: Germany
Contact:

Re: Funny read: MySQL/MariaDB vs. PostgreSQL Datahandling

Post by Dravion » 2017-10-24 12:54

Nice but MariaDB since 10.x isnt any longer relevant for hMailServer nice MySQL 5.7 breaks libmysql.lib compatibility.

tunis
Normal user
Normal user
Posts: 215
Joined: 2015-01-05 20:22
Location: Sweden

Re: Funny read: MySQL/MariaDB vs. PostgreSQL Datahandling

Post by tunis » 2017-10-24 13:26

I use mariadb 10.2.9 on centos for hmailserver and it work fine.
But I have never updated libmysql.dll, I have version 6.1.6.0.
HMS 5.6.8 B2437.17 on Windows Server 2019 Core VM.
HMS 5.6.8 B2437.17 on Windows Server 2016 Core VM.
HMS 5.6.7 B2425.16 on Windows Server 2012 R2 Core VM.

User avatar
Dravion
Senior user
Senior user
Posts: 1397
Joined: 2015-09-26 11:50
Location: Germany
Contact:

Re: Funny read: MySQL/MariaDB vs. PostgreSQL Datahandling

Post by Dravion » 2017-10-24 14:34

Yeah, because CentOS7 doesnt have MySQL any longer in its official repo thats why i use MariaDB for Wordpress and phpbb on my CentOS7 Rootserver as well ;)

prisma
Senior user
Senior user
Posts: 309
Joined: 2010-07-09 13:16

Re: Funny read: MySQL/MariaDB vs. PostgreSQL Datahandling

Post by prisma » 2017-10-24 15:32

We use PostgreSQL with roundabout 600 installations at customers side. We are impressed how robust the WAL tactic of postgres is. Our customers have from time to time very unstable systems, corrupt file systems and so on. We never had any bigger data losses and postgres was evry time repairable or was able to repair itself. And yes, postgres is much more stricter than other DBs, regarding types and automatic type casts and other things. But we love it. No mysterious automatics and no difficult to interpret behaviour.

We also use PostgrSQL for hMailerver. We are able to really recommend it. Never had any issues.

Post Reply