How to run MySQL event only on weekdays (Monday - Friday)

 I have a stored procedure that need to be execute automatically at 12 AM daily except Saturday and Sunday. In order to achieve this, here you need to use IF condition as screenshot below:

or create using below script:

delimiter //
CREATE EVENT IF NOT EXISTS your_event_name
ON SCHEDULE EVERY 1 DAY ON COMPLETION PRESERVE ENABLE
DO
    if DAYOFWEEK(curdate()) between 2 and 6 then
        call your_stored_procedure_name;
    end if;
//

Hope this may helps others

 

How to change MySQL root password - XAMPP

 First open Xampp control panel and make sure the MySQL service is running. Then click on Shell button

Wait till the shell open, then type :

mysqladmin -u root -p

Enter your new password and verify new password. That's it.

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

I ran this command:

LOAD DATA INFILE '/root/tk_worker_latestjan17.csv' INTO TABLE tk_worker_temp FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;

I got the following error:

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

Solution:

1. Run this command  SHOW VARIABLES LIKE "secure_file_priv"; to show the configured directory.
2. Move the file to the directory specified by secure-file-priv 

Thank you

MySQL - Error code 1140

Error Code: 1140. In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'dbname.a.column_name'; this is incompatible with sql_mode=only_full_group_by

In order to resolve this issue, we have to turn off ONLY_FULL_GROUP_BY sql_mode.

1. Check sql_mode by issue this command:
   SELECT @@sql_mode;
2. You'll see the output like below:
   ONLY_FULL_GROUP_BY
3. Now turn off the ONLY_FULL_GROUP_BY by issuing this command:
   SET sql_mode = '';

Done!

[MySQL] Show All Connections

Run this command:

show status like '%conn%';

* tips: add skip-name-resolve at my.cnf to speedup the database process

[MySQL] - Host 'xxx.xxx.x.xx' is not allowed to connect to this MySQL server

This problem happen when the mysql server doesn't allow remote connection. So first, go to the mysql server that you want to access.

• Use putty (for windows) or terminal (for unix/linux) and perform the ssh
  ssh root@xxx.xxx.x.xx
Enter password: (enter the server's password) 
• Then type:
  mysql -u root -p
Enter password:(enter the mysql password) 
• Then type the following command:
  GRANT ALL PRIVILEGES ON *.* TO your_mysql_user@'%' IDENTIFIED BY 'your_mysql_password';
  

* make sure you change the value of your_mysql_user and your_mysql_password.Now you can access the mysql server through remote connection.