You should disable have_symlink for mysql

Have you ever noticed a variables named "have_symlink", by default it's set "YES".

What have_symlink is capable of? Enabling symbolic links means that you can link a MyISAM index file or data file to another directory with the INDEX DIRECTORY or DATA DIRECTORY options of the CREATE TABLE statement. If you delete or rename the table, the files that its symbolic links point to also are deleted or renamed.

      mysql> create table test_myisam (id int not null) engine=myisam data directory='/tmp';

This will make a symbolic link on data file of table test1:

You may use it if you are running low on disk space on the partition where your mysql data directory is, or you want to move some tables to a different partition or disk for performance reasons or you just leave it as default.

This is insecure, for two reasons:

1.  mysqld can be used to drop or rename a file outside of the data directory.


2. MySQL before 5.0.67 allows local users to bypass certain privilege checks by calling CREATE TABLE on a MyISAM table with modified (1) DATA DIRECTORY or (2) INDEX DIRECTORY arguments that are originally associated with pathnames without symlinks, and that can point to tables created at a future time at which a pathname is modified to contain a symlink to a subdirectory of the MySQL home data directory. NOTE: this vulnerability exists because of an incomplete fix for CVE-2008-4097.

So if you are not using symlinks, you should disable it with the --skip-symbolic-links option, it's not dynamic, you need to restart your mysql.

How does Error 'You cannot 'ALTER' a log table if logging is enabled' on query happen when running mysql_upgrade? and why?

Last_Error: Error 'You cannot 'ALTER' a log table if logging is enabled' on query. Default database: 'mysql'. Query: 'ALTER

TABLE slow_log ...

Normally when you met above error is because you are running msyql_upgrade on master, subsequently the error happens on the slave.

The solution is:

1.  stop slave;
2. set global slow_query_log = 'OFF';
3. start slave;
4. set global slow_query_log = 'ON';

why does it happen?

When you run mysql_upgrade, it actually contains 'alter table' sql:

ALTER TABLE general_log

   MODIFY event_time TIMESTAMP NOT NULL,

   MODIFY user_host MEDIUMTEXT NOT NULL,

   MODIFY thread_id INTEGER NOT NULL,

   MODIFY server_id INTEGER UNSIGNED NOT NULL,

   MODIFY command_type VARCHAR(64) NOT NULL,

   MODIFY argument MEDIUMTEXT NOT NULL;

ALTER TABLE slow_log

   MODIFY start_time TIMESTAMP NOT NULL,

   MODIFY user_host MEDIUMTEXT NOT NULL,

   MODIFY query_time TIME NOT NULL,

   MODIFY lock_time TIME NOT NULL,

   MODIFY rows_sent INTEGER NOT NULL,

   MODIFY rows_examined INTEGER NOT NULL,

   MODIFY db VARCHAR(512) NOT NULL,

   MODIFY last_insert_id INTEGER NOT NULL,

   MODIFY insert_id INTEGER NOT NULL,

   MODIFY server_id INTEGER UNSIGNED NOT NULL,

   MODIFY sql_text MEDIUMTEXT NOT NULL;

Before running "ALTER TABLE", mysql_upgrade run "set global slow_query_log = 'OFF';", however, this will not be logged into binary log, subsequently "ALTER TABLE" sql are logged, so the slave run "ALTER TABLE" with slow_query_log=ON.