Bug 1144314 - suggestion to change default my.cnf (skip-networking, slow log, sql_mode as in MySQL, rocksdb.cnf)
suggestion to change default my.cnf (skip-networking, slow log, sql_mode as i...
Status: RESOLVED FIXED
Classification: openSUSE
Product: openSUSE Tumbleweed
Classification: openSUSE
Component: Other
Current
All openSUSE Factory
: P5 - None : Normal (vote)
: ---
Assigned To: Kristyna Streitova
E-mail List
:
Depends on:
Blocks:
  Show dependency treegraph
 
Reported: 2019-08-05 12:04 UTC by Илья Индиго
Modified: 2021-12-21 18:35 UTC (History)
0 users

See Also:
Found By: ---
Services Priority:
Business Priority:
Blocker: ---
Marketing QA Status: ---
IT Deployment: ---


Attachments
diff-file.patch (1.02 KB, patch)
2019-08-05 12:04 UTC, Илья Индиго
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description Илья Индиго 2019-08-05 12:04:22 UTC
Created attachment 812804 [details]
diff-file.patch

I want to suggest changing the default configuration.
Switching to another major version is the best time for this.

--- my.ini 2019-08-04 10:29:32.437576291 +0300
+++ my.cnf 2019-08-03 18:37:39.000000000 +0300
@@ -10,17 +10,16 @@
# The MariaDB server
[mysqld]

-# For security reasons, bind to 127.0.0.1 by default to enable networking
-# only on the loopback interface.
-bind-address = 127.0.0.1
+# For security reasons, disable networking and using socket only.
+skip-networking

# If log-error is not set, mysqld will write to "/var/lib/mysql/$HOSTNAME.err"
# which is not beneficial for rotating the log file if it grows in size.
log-error = /var/log/mysql/mysqld.log

# Enable the slow query log to see queries with especially long duration
-# slow_query_log=1
-# slow_query_log_file = /var/log/mysql/mysqld_slow.log
+slow_query_log=1
+slow_query_log_file = /var/log/mysql/mysqld_slow.log

# Operations 'LOAD DATA', 'SELECT ... INTO' and 'LOAD FILE()' will only
# work with files in the specified directory
@@ -52,14 +51,14 @@ server-id = 1
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
-# read_rnd_buffer_size = 2M
+# read_rnd_buffer_size = 2M

-# Configure the MariaDB server to use SSL
+# Configure the MariaDB server to use SSL
# ssl-ca=/etc/mysql/ssl/ca-cert.pem
# ssl-cert=/etc/mysql/ssl/server-cert.pem
# ssl-key=/etc/mysql/ssl/server-key.pem

-sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
+sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
@@ -103,4 +102,3 @@ log = /var/log/mysqld_multi.log
# user = mysql

!includedir /etc/my.cnf.d
-

1 skip-networking is safer and more productive than bind-address = 127.0.0.1

2 I don’t understand why the slow connection log is disabled by default?
This is a very useful tool!

3 And the most controversial, I want to make the default sql mode as in Mysql (the most strict).

I was surprised that in MariaDB the mode is less strict than in Mysql, and especially since you made it even less strict. What for?

Those who have a specific server, those with their own settings are not only sql_mode.
But those who are just starting to develop web-sites, they are basically, and use the default settings.
And they will not even know that they are using simplified mode, and they will be very surprised that their queries may not work with Mysql.

The only really dangerous and controversial mode is ONLY_FULL_GROUP_BY.
It was never included in Mariadb, however it has long been included in Mysql.
After that, I myself had to rewrite several queries and revise the rules and grouping requirements required by the SQL 97 standard.

This config will not be replaced by default, and will not affect old users, but new developers using openSUSE will write better code. :-)

4 Connect the ROCKSDB engine plugin for architectures that support it.

Since the release of mariadb 10.3, the ROCKSDB engine is in a stable state, and if there are no serious reasons, I want to enable it by default for architectures that support it.
Comment 1 Илья Индиго 2019-08-05 12:19:20 UTC
https://build.opensuse.org/request/show/721045
Comment 2 Илья Индиго 2019-08-05 12:30:44 UTC
It is very important that these changes, or part of these changes, be accepted before the Factory accepts the SR https://build.opensuse.org/request/show/721015 or any SR update mariadb to 10.3.
Comment 3 Kristyna Streitova 2019-08-05 13:58:28 UTC
(In reply to Илья Индиго from comment #0)

> 1 skip-networking is safer and more productive than bind-address = 127.0.0.1
I don't see a reason why to use "skip-networking". "skip-networking" was used in the old MySQL packages but MariaDB upstream uses "bind-address" in their packages now.


> 2 I don’t understand why the slow connection log is disabled by default?
> This is a very useful tool!
Why do you think that it should be enabled for all users by default? It is a useful tool indeed but not all users have DB with slow SQL queries.


> 3 And the most controversial, I want to make the default sql mode as in
> Mysql (the most strict).
>
> I was surprised that in MariaDB the mode is less strict than in Mysql, and
> especially since you made it even less strict. What for?
> 
> Those who have a specific server, those with their own settings are not only
> sql_mode.
> But those who are just starting to develop web-sites, they are basically,
> and use the default settings.
> And they will not even know that they are using simplified mode, and they
> will be very surprised that their queries may not work with Mysql.

The changes you suggest are highly subjective settings. I think that we should let the settings of sql_mode up to the users so they can adjust it according to their needs.


> 4 Connect the ROCKSDB engine plugin for architectures that support it.
> 
> Since the release of mariadb 10.3, the ROCKSDB engine is in a stable state,
> and if there are no serious reasons, I want to enable it by default for
> architectures that support it.

We build plenty of engines apart from rocksdb in mariadb package. Why we should enable just MyRocks? Again, I think that it should be up to the users to enable the engine that they plan to use.
Comment 4 Илья Индиго 2019-08-06 07:16:41 UTC
(In reply to Kristyna Streitova from comment #3)
> (In reply to Илья Индиго from comment #0)
> 
> > 1 skip-networking is safer and more productive than bind-address = 127.0.0.1
> I don't see a reason why to use "skip-networking". "skip-networking" was
> used in the old MySQL packages but MariaDB upstream uses "bind-address" in
> their packages now.

The main reason is logic!
If you do not need a network, then you need to turn it off, not cut it.
That is, if you didn’t need a refrigerator, instead of turning it off to prevent it wasting electricity, you would set it to the maximum temperature it supports (minimum power).
I’m already silent about the fact that the brandmauer should deal with network cutting, and not the application itself, especially if it is network-based.

> > 2 I don’t understand why the slow connection log is disabled by default?
> > This is a very useful tool!
> Why do you think that it should be enabled for all users by default? It is a
> useful tool indeed but not all users have DB with slow SQL queries.

Most users don’t need a regular log, log rotation is not needed, and man pages are not needed, and examples with documentation in "/usr/share/mysql" are not needed, they don’t even write about them.
Nevertheless, they are installed by default for all users.
And here I do not see the difference between why the regular log is turned on and the slow log is not.
Moreover, many users may simply not know about it, and learn about it by reading logs.
This can be regarded as an action aimed at improving the quality of development of junior developers.

Despite the above described in paragraphs 1 and 2, I can take your decision to leave them as they are, for the reason that upstream so accepted, if you finally decide so.

> > 3 And the most controversial, I want to make the default sql mode as in
> > Mysql (the most strict).
> >
> > I was surprised that in MariaDB the mode is less strict than in Mysql, and
> > especially since you made it even less strict. What for?
> > 
> > Those who have a specific server, those with their own settings are not only
> > sql_mode.
> > But those who are just starting to develop web-sites, they are basically,
> > and use the default settings.
> > And they will not even know that they are using simplified mode, and they
> > will be very surprised that their queries may not work with Mysql.
> 
> The changes you suggest are highly subjective settings. I think that we
> should let the settings of sql_mode up to the users so they can adjust it
> according to their needs.

Almost all Junior Developers they use the default settings without changing anything there, at first being afraid to spoil something, and then trying to ensure maximum code compatibility. But they don’t even realize that these settings are already changed and differ from the settings in upstream.

The main reason I want to use MySQL mode by default is to improve the code quality of junior developers.

> > 4 Connect the ROCKSDB engine plugin for architectures that support it.
> > 
> > Since the release of mariadb 10.3, the ROCKSDB engine is in a stable state,
> > and if there are no serious reasons, I want to enable it by default for
> > architectures that support it.
> 
> We build plenty of engines apart from rocksdb in mariadb package. Why we
> should enable just MyRocks? Again, I think that it should be up to the users
> to enable the engine that they plan to use.

Because MyRocks is qualitatively different from everyone else.
This is a development from Facebook, which is the best solution for archival, statistical or other "junk" tables, where a lot of data is written and very rarely read.
If in TokuDB there were problems with the support of large transparent pages in the kernel, then in MyRocks there is no approval and the engine is more efficient.
And here is the problem of website hosts, which often also use the default settings, and you can install optimized website only on a virtual or dedicated server, but not on the hosting.

If you still do not want to enable MyRocks by default, due to the upstream solution, then at least add a configuration file with commented-out plugin loading lines to increase the likelihood that web hosting administrators will pay attention to it, and maybe even open and uncomment it. :-)

I really hope that we will come to a compromise on this issue. :-)
Comment 5 Илья Индиго 2019-08-06 07:24:33 UTC
Oh, I lost a large piece of text to the 3rd paragraph. Now I will write it again. :-(
Comment 6 Илья Индиго 2019-08-06 07:57:01 UTC
(In reply to Kristyna Streitova from comment #3)
> (In reply to Илья Индиго from comment #0)
>
> > 3 And the most controversial, I want to make the default sql mode as in
> > Mysql (the most strict).
> >
> > I was surprised that in MariaDB the mode is less strict than in Mysql, and
> > especially since you made it even less strict. What for?
> > 
> > Those who have a specific server, those with their own settings are not only
> > sql_mode.
> > But those who are just starting to develop web-sites, they are basically,
> > and use the default settings.
> > And they will not even know that they are using simplified mode, and they
> > will be very surprised that their queries may not work with Mysql.
> 
> The changes you suggest are highly subjective settings. I think that we
> should let the settings of sql_mode up to the users so they can adjust it
> according to their needs.

Almost all Junior Developers they use the default settings without changing anything there, at first being afraid to spoil something, and then trying to ensure maximum code compatibility. But they don’t even realize that these settings are already changed and differ from the settings in upstream.

The main reason I want to use MySQL mode by default is to improve the code quality of junior developers.

> I think that we should let the settings of sql_mode up to the users

The upstream settings have already been changed. ERROR_FOR_DIVISION_BY_ZERO and NO_AUTO_CREATE_USER modes removed
The absence of NO_AUTO_CREATE_USER leads, on the one hand, to a decrease in security and, on the other hand, to a deterioration in the quality of the code and its compatibility.
The lack of the correct mathematical mode ERROR_FOR_DIVISION_BY_ZERO ... I don’t even know who might need it ... then let’s still extract the square root of -1 ...
This leads to the fact that the junior developer, who first began to develop websites by installing openSUSE, to the fact that he will do this constantly, that is correct, since he uses the default configuration and the server does not report errors.
He will also think that this will work in MySQL, because prior to version 10 MariaDB was a transparent replacement for MySQL.
But we know that he is mistaken, since his code will not work even on MariaDB with the default configuration. I am already silent about MySQL.

Also openSUSE, provides only MariaDB, I would even say it imposes it.
And he will not be able to test his code in MySQL, even if he wants to.
He has already been limited in this choice.

That's why I want to, including due to the fact that MySQL 8 can no longer be installed out of the box in openSUSE, at least to ensure the operation mode that is most compatible with it in MariaDB, since in this mode the code will be as much as possible it is possible compatible with both MariaDB and MySQL.

And I insist that at least the default mode from upstream be returned!

And if you don’t want to set the MySQL mode, because upstream is so accepted, then add the MySQL mode in the commented form, and above it add a comment that MariaDB uses a less strict mode which is not compatible with MySQL.
If you want to use compatible with both MariaDB and Mysql, then uncomment this line.

P.S. I know that even when installing sql_mode as in MySQL, the code will still not be fully compatible with MySQL, for example, MariaDB does not have ANY_VALUE () functions, but this is not a reason not to even try to write the most compatible code and not to accustom it junior developers.
Comment 7 Kristyna Streitova 2019-08-07 13:51:28 UTC
(In reply to Илья Индиго from comment #4)
> > > 1 skip-networking is safer and more productive than bind-address = 127.0.0.1
> The main reason is logic!
> If you do not need a network, then you need to turn it off, not cut it.
> That is, if you didn’t need a refrigerator, instead of turning it off to
> prevent it wasting electricity, you would set it to the maximum temperature
> it supports (minimum power).
> I’m already silent about the fact that the brandmauer should deal with
> network cutting, and not the application itself, especially if it is
> network-based.

Sorry, but I can't agree here because "bind-address" just doesn't consume any "electricity". It's comprehensible and as secure as skip-networking. As I wrote, it's an upstream default for their mariadb packages (Debian [1], Fedora, ...)  and moreover, it allows to easily alter it to bind to a specific address (for testing purposes) or open it to the whole world (production). I don't see any reason why to change it back to the old skip-networking.


> > > 2 I don’t understand why the slow connection log is disabled by default?
> > > This is a very useful tool!
> Most users don’t need a regular log, log rotation is not needed, and man
> pages are not needed, and examples with documentation in "/usr/share/mysql"
> are not needed, they don’t even write about them.
> Nevertheless, they are installed by default for all users.
> And here I do not see the difference between why the regular log is turned
> on and the slow log is not.
> Moreover, many users may simply not know about it, and learn about it by
> reading logs.
> This can be regarded as an action aimed at improving the quality of
> development of junior developers.

Regular log, logrotate or manpages are UNIX essentials. A specialized slow log is not. 

The slow log is currently present and commented-out in my.cnf so users are aware of it and they can enable it if they need it. I believe that this is sufficient.

<snippet from my.cnf>
# Enable the slow query log to see queries with especially long duration
# slow_query_log=1
# slow_query_log_file = /var/log/mysql/mysqld_slow.log
</>

> Despite the above described in paragraphs 1 and 2, I can take your decision
> to leave them as they are, for the reason that upstream so accepted, if you
> finally decide so.

Ok, my result here is to leave it as is.


(In reply to Илья Индиго from comment #6)
> > > 3 And the most controversial, I want to make the default sql mode as in
> > > Mysql (the most strict).

> And I insist that at least the default mode from upstream be returned!

Can you please send me a link where this default MariaDB upstream sql_mode is defined? Thanks!

> And if you don’t want to set the MySQL mode, because upstream is so
> accepted, then add the MySQL mode in the commented form, and above it add a
> comment that MariaDB uses a less strict mode which is not compatible with
> MySQL.
> If you want to use compatible with both MariaDB and Mysql, then uncomment
> this line.

It's not our goal to make MariaDB compatible with MySQL. Again, this is a highly specific situation and I don't think that it should be addressed in the general config neither as a valid config nor as commented-out. If somebody wants their code to be MySQL compatible, they need to do more than just to add this MySQL sql_mode and they need to address it in a more broad way.


> > > 4 Connect the ROCKSDB engine plugin for architectures that support it.

> Because MyRocks is qualitatively different from everyone else.
> This is a development from Facebook, which is the best solution for
> archival, statistical or other "junk" tables, where a lot of data is written
> and very rarely read.
> If in TokuDB there were problems with the support of large transparent pages
> in the kernel, then in MyRocks there is no approval and the engine is more
> efficient.
> And here is the problem of website hosts, which often also use the default
> settings, and you can install optimized website only on a virtual or
> dedicated server, but not on the hosting.

I understand that you think that MyRocks is better than other engines but it's still a very personal opinion and I don't think that it's a real reason why to enable it by default to all users.

> If you still do not want to enable MyRocks by default, due to the upstream
> solution, then at least add a configuration file with commented-out plugin
> loading lines to increase the likelihood that web hosting administrators
> will pay attention to it, and maybe even open and uncomment it. :-)

Then I would have to add these #plugin-load-add lines for all other engines as well which doesn't sound nice at all. I believe that administrators can responsibly decide which engines they want to use and then enable them on their own.


> I really hope that we will come to a compromise on this issue. :-)

I understand that you want to make the administration of the MariaDB easy for juniors but adding engines or options that suit just for a small specific group of users is not the right way. I appreciate that you are interested in MariaDB package in openSUSE but I just can't agree with your suggestion.


[1] https://github.com/MariaDB/server/blame/10.5/debian/additions/my.cnf#L45
Comment 8 Илья Индиго 2019-08-07 20:15:43 UTC
> Can you please send me a link where this default MariaDB upstream sql_mode
> is defined? Thanks!
https://mariadb.com/kb/en/library/sql-mode

Okay, I revoke my request, you are a maintainer.
Comment 9 Kristyna Streitova 2019-08-08 10:11:20 UTC
(In reply to Илья Индиго from comment #8)
> > Can you please send me a link where this default MariaDB upstream sql_mode
> > is defined? Thanks!
> https://mariadb.com/kb/en/library/sql-mode

Thanks, I've removed sql_mode from my.ini/my.cnf and restored the original values (NO_ENGINE_SUBSTITUTION and STRICT_TRANS_TABLES are already set by default from version 10.2.4 so we don't need them anyway).

Closing as fixed.