Question

Assistance with MySQL my.cnf file preventing mysql from starting

Posted August 29, 2020 295 views
MySQLUbuntu 20.04

I recently upgraded to Ubuntu 20.04 and mysql8. I have a my.cnf file located in /etc/mysql/. which, upon first installation of the mysql 8 ignores that file (because it’s listed as world writeable) and runs msyql fine. However, I need to do things like load data from files stored on the server and need to configure things like sql_mode to respect these scenarios. When I attempt to restrict access to the my.cnf file by doing the following:

chown mysql:mysql /etc/mysql/my.cnf
chmod 600 /etc/mysql/my.cnf

mysql won’t run and I end up removing and reinstalling mysql 8 all over again. Weirdly, the file always seems to revert back to full rights for everyone (777). I’m not sure why that’s occurring.

The full contents of the my.cnf file information is posted below:

# This file has been automatically moved from your previous
# /etc/mysql/my.cnf, with just this comment added at the top, to maintain MySQL
# operation using your previously customised configuration.

# To switch to the new packaging configuration for automated management of

# /etc/mysql/my.cnf across multiple variants:
#
# 1. Move your customisations from this file to /etc/mysql/conf.d/ and
#    to /etc/mysql/<variant>.conf.d/ as appropriate.
# 2. Run "update-alternatives --remove my.cnf /etc/mysql/my.cnf.migrated"
# 3. Remove the file /etc/mysql/my.cnf.migrated

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html


# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port        = 3306
socket      = 

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket      = 
nice        = 0

[mysqld]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#local-infile=1
sql-mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"


#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address       = 127.0.0.1
#
# * Fine Tuning
#
# Obsolete key_buffer option renamed to key_buffer_size by maintainer script
key_buffer_size     = 16M
max_allowed_packet  = 16M
thread_stack        = 258K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
# Obsolete myisam-recover option renamed to myisam_recover_options by maintainer script
myisam_recover_options  = BACKUP
max_connections         = 50
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit = 2M

#default setting for query_cache_size was 16M
query_cache_size        = 15M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries   = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id      = 1
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size         = 100M
#binlog_do_db       = include_database_name
#binlog_ignore_db   = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem



[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
local-infile=1

[isamchk]
# Obsolete key_buffer option renamed to key_buffer_size by maintainer script
# changed from 16 to 8M -djb
key_buffer_size     = 8M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

I’m wondering if there’s a way to get a clean my.cnf file to start over again and also wondering why the file permissions seemed to be weird on this.

TIA

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

×
1 answer

Hi there @davidboudia,

Note that the /etc/mysql/my.cnf file is not really a file but a symlink.

It basally is a ‘short cut’ to the /etc/alternatives/my.cnf file which on the other hand is a short cut to the /etc/mysql/mysql.cnf file.

/etc/mysql/my.cnf -> /etc/alternatives/my.cnf
/etc/alternatives/my.cnf -> /etc/mysql/mysql.cnf

Changing the permissions to the symlink will not work.

What is the exact error that you get? And also what is the exact change that you are trying to make?

If you want to make some changes to your MySQL variables for example, you need to make the change in the /etc/mysql/mysql.conf.d/mysqld.cnf file.

Hope that this helps!
Regards,
Bobby

by joshtronic
Learn how to create symbolic links between files and directories from the command-line using the ln command.
  • Thank you for the response. I’m running into several errors when I run php-files.

    the first is communicating that I’m getting an issue with one of my queries that loads data from a stored file.

    3948 Loading local data is disabled; this must be enabled on both the client and server sides

    I know that this is related to the local_infile variable being set. I did add an item to set the value to equal 1 in the mysqld.cnf file mentioned above and restarted the mysql service and same error.

    The other issue is that I want the sqlmode to not use ONLYFULLGROUPBY so I’ve purposely excluded it from that variable. I get this error when I try to run the php page that’s executing the query:

    In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'LeagueX.b.Owner_ID'; this is incompatible with sql_mode=only_full_group_by
    

    When I run MySQLWorkbench it looks like the configuration file it’s pulling from is the /etc/mysql/my.cnf which would also have these variable set.

    However, when I start mysql from the prompt, it tells me that file is World-Writeable and is ignored.

    • Hi there @davidboudia,

      Regarding the World-Writeable error, as the my.cnf file is a symlink to /etc/mysql/mysql.cnf, have you checked the permissions of the /etc/mysql/mysql.cnf file?

      Also regarding the sql_mode, what I could suggest is using the mysql CLI tool to access MySQL and then run:

      show variables like 'sql_mode';
      

      Let me know how it goes!
      Regards,
      Bobby

      • Changing the file permissions to restrict world writable privileges on the /etc/alternatives/my.cnf

        gave me the following error after restarting the mysql service:

        Job for mysql.service failed because the control process exited with error code.

        See "systemctl status mysql.service" and "journalctl -xe" for details.
        
        

        This is what I see for the systemctl command:

        ● mysql.service - MySQL Community Server
             Loaded: loaded (/lib/systemd/system/mysql.service; disabled; vendor preset: enabled)
             Active: activating (start) since Sat 2020-09-12 11:36:28 EDT; 140ms ago
            Process: 21772 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
           Main PID: 21780 (mysqld)
             Status: "Server startup in progress"
              Tasks: 1 (limit: 1111)
             Memory: 8.3M
             CGroup: /system.slice/mysql.service
                     └─21780 /usr/sbin/mysqld
        
        

        and this is what I see for the jorurnalct1 -xe:

        Sep 12 11:37:30 ndd-php systemd[1]: mysql.service: Failed with result 'exit-code'.
        -- Subject: Unit failed
        -- Defined-By: systemd
        -- Support: http://www.ubuntu.com/support
        -- 
        -- The unit mysql.service has entered the 'failed' state with result 'exit-code'.
        Sep 12 11:37:30 ndd-php systemd[1]: Failed to start MySQL Community Server.
        -- Subject: A start job for unit mysql.service has failed
        -- Defined-By: systemd
        -- Support: http://www.ubuntu.com/support
        -- 
        -- A start job for unit mysql.service has finished with a failure.
        -- 
        -- The job identifier is 8899 and the job result is failed.
        Sep 12 11:37:30 ndd-php systemd[1]: mysql.service: Scheduled restart job, restart counter is at 95.
        -- Subject: Automatic restarting of a unit has been scheduled
        -- Defined-By: systemd
        -- Support: http://www.ubuntu.com/support
        -- 
        -- Automatic restarting of the unit mysql.service has been scheduled, as the result for
        -- the configured Restart= setting for the unit.
        Sep 12 11:37:30 ndd-php systemd[1]: Stopped MySQL Community Server.
        -- Subject: A stop job for unit mysql.service has finished
        -- Defined-By: systemd
        -- Support: http://www.ubuntu.com/support
        -- 
        -- A stop job for unit mysql.service has finished.
        -- 
        -- The job identifier is 8955 and the job result is done.
        Sep 12 11:37:30 ndd-php systemd[1]: Starting MySQL Community Server...
        -- Subject: A start job for unit mysql.service has begun execution
        -- Defined-By: systemd
        -- Support: http://www.ubuntu.com/support
        -- 
        -- A start job for unit mysql.service has begun execution.
        

        weirdly, the /etc/alternatives/my.cnf reverted back to 777 after I recieved the error messages.

        The /etc/mysql/mysql.cnf is configured as 644 so this wasn’t open to write.

        Unfortunately, I can’t restart mysql now because of the error to show sql_mode.

        • Hi there @davidboudia,

          Note that the /etc/alternatives/my.cnf is a symbolic link, so you can’t really change the permissions of that file directly.

          What I could suggest here is checking your MySQL error log for more information on what’s preventing it from starting.

          • tail -100 /var/log/mysql/error.log

          Regards,
          Bobby

          • Bobby, thanks again for your response. I was able to check the lgo and fix the issue - it was a bad variable in the my.cnf file.

            getting back on track I was able to pull the sql_mode from running mysql from the cli:

            ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
            

            the ONLY_FULL_GROUP_BY is one of the problems (the other being the local_infile). I’ve attempted to override.

            Per your prior comment, I’ve added the following lines to the /etc/mysql/mysql.conf.d/mysqld.cnffile:

            local_infile=1
            sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
            
          • Hey @davidboudia,

            Happy to hear that you’ve got MySQL backup and running.

            Regarding the sql_mode, did you add the lines at the bottom of the /etc/mysql/mysql.conf.d/mysqld.cnf file?

            If so, I could suggest testing it with the following line instead:

            sql_mode = ""
            

            Regarding the local_infile value, after adding local_infile=1 at the bottom of the /etc/mysql/mysql.conf.d/mysqld.cnf file, I was able to verify that the change was successful with the following query:

            • show variables like '%local_infile%';

            The output that I saw was:

            +---------------+-------+
            | Variable_name | Value |
            +---------------+-------+
            | local_infile  | ON    |
            +---------------+-------+
            

            Let me know how it goes!
            Regards,
            Bobby

          • I can’t reply to your last message so I needed to use one of your former posts

            I did add the two lines to the /etc/mysql/mysql.conf.d/mysqld.cnf file and set sql_mode to “” and the local_infile using that query showed that the variable was set to off.

            I’ve set the same two variables in the following locations and every time I restart the service no dice:

            /etc/mysql/mysql.conf.d/mysqld.cnf
            /etc/mysql/mysql.conf.d/mysqld.cnf
            /etc/mysql/conf.d/mysql.cnf
            
          • Hi @davidboudia,

            Note that the lines need to be added after the [mysqld] directive in order for them to take effect.

            Let me know how it goes!
            Regards,
            Bobby

          • Ok, figured it out. I saw that there the alternatives was an symlink (as mentioned in this post). I chmod to 644 which seem to cause mysql to look to the /etc/mysql/my.cnf.migrated file. Once it hit that file, I was getting a socket error but was able to rectify by commenting out the socket references.

          • Hi there @davidboudia,

            No problem at all. I’m really happy to hear that you’ve got it working now!

            Regards,
            Bobby

Submit an Answer