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.

How to concat (with comma separated) query result in MySQL

 Let say you have a query that return multiple row, but you want it return in single line separated by comma. 

Example query that returns result in rows:

SELECT invoiceNo FROM payment WHERE paymentStatus = '3' AND paymentYear = DATE_FORMAT(now(),'%Y') AND paymentAmountNett != '0.00' and billpaymentChannel='Cap';

How to trim the explode string in PHP

$string = 'ABC, DEF, GHI';

$expl = array_map('trim', explode(',', $string));

NPM - 'cross-env' is not recognized as an internal or external command

 How to solve this problem? super easy:

1. Remove node_modules folder
2. Remove package-lock.json
3. Run npm install

Create bootable USB from .iso file - Linux

Plug-in USB drive into your computer. Open up terminal and type :

fdisk -l

You'll see listing of drives available on your computer. Normally the USB drive will be shown like this :

/dev/sdb1 or /dev/sdc1

Now run this command:

sudo umount /dev/sd<?><?>

where first question mark is letter and second question mark is number. Then run this command:

sudo dd bs=4M if=the_image_file.iso of=/dev/sd<?><?>

Wait until finish and run command sync

Done!

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