MySQL Error 1231: How to Fix “Variable ‘general_log_file’ Can’t Be Set to ‘/var/lib/mysql/ubuntu.log'”


9 views

When working with MySQL's general query log configuration, you might encounter Error 1231 when attempting to set the general_log_file path. The specific error message indicates the system refuses to accept the file path you're trying to configure.

The most frequent reasons for this error include:

  • Typographical errors in the path (notice the typo "msyql" in your example)
  • Insufficient filesystem permissions
  • SELinux/AppArmor restrictions
  • The parent directory doesn't exist
  • MySQL service account lacks write permissions

First, double-check your path spelling. In your example:

-- Incorrect:
SET GLOBAL general_log_file = '/var/lib/msyql/ubuntu.log';

-- Correct:
SET GLOBAL general_log_file = '/var/lib/mysql/ubuntu.log';

Even with the correct path, permission issues might prevent MySQL from using the location:

# Check directory ownership
ls -ld /var/lib/mysql

# Verify mysql user has write access
sudo -u mysql touch /var/lib/mysql/test.log

Instead of using SET GLOBAL, you can permanently configure this in your MySQL configuration file:

[mysqld]
general_log = 1
general_log_file = /var/lib/mysql/ubuntu.log

After making changes, restart MySQL:

sudo systemctl restart mysql

If the issue persists, try using different locations to isolate the problem:

-- Test with /tmp first
SET GLOBAL general_log_file = '/tmp/mysql_general.log';

-- Then try MySQL's datadir
SHOW VARIABLES LIKE 'datadir';
SET GLOBAL general_log_file = '/var/lib/mysql/mysql_general.log';

On RHEL-based systems, you might need to adjust SELinux contexts:

# Check current context
ls -Z /var/lib/mysql

# Apply proper context
chcon -R -t mysqld_db_t /var/lib/mysql

After resolving the issue, verify your configuration:

SHOW VARIABLES LIKE 'general_log%';
SELECT @@general_log, @@general_log_file;

When attempting to modify MySQL's general_log_file variable, you might encounter ERROR 1231 with the message indicating the path cannot be set. This typically occurs due to one of these fundamental reasons:


-- Common error example:
SET GLOBAL general_log_file = '/var/lib/msyql/ubuntu.log';
-- ERROR 1231 (42000): Variable 'general_log_file' can't be set...

MySQL performs strict validation on log file paths. Notice the intentional typo in "msyql" (should be "mysql") in the original path. The system checks:

  • Directory existence (require MySQL user access)
  • File naming conventions
  • Filesystem permissions (typically needs mysql:mysql ownership)

First verify the correct path structure:


SHOW VARIABLES LIKE 'general_log_file';
-- Compare with intended path

For Ubuntu/Debian systems, the standard path should be:


/var/lib/mysql/hostname.log  -- Not /var/lib/msyql/

1. Correct the path typo:


SET GLOBAL general_log_file = '/var/lib/mysql/ubuntu.log';

2. If still failing, temporarily disable logging:


SET GLOBAL general_log = 'OFF';
-- Change path
SET GLOBAL general_log = 'ON';

3. For permission issues (common with AppArmor/SELinux):


sudo chown mysql:mysql /var/lib/mysql/
sudo chmod 755 /var/lib/mysql/

When dealing with custom log locations, always update AppArmor profiles (for Ubuntu):


sudo nano /etc/apparmor.d/usr.sbin.mysqld
# Add line: "/new/log/path/* rw,"
sudo systemctl restart apparmor

After changes, verify with:


SELECT @@global.general_log_file;
SHOW VARIABLES LIKE 'general_log%';

Check MySQL error log for detailed failure reasons if issues persist:


sudo tail -n 50 /var/log/mysql/error.log