Truncate Mysql Table Cron Job?

Question

I am having some trouble on how to Truncate a Mysql Table with a cron job. No matter what I try I can't seem to get the database to clear out the table.. Thanks for the help. ``` mysql -uderp_example -pexample -hlocalhost -Dexample -e"TRUNCATE TABLE juicebox" ``` I am using this: https://i.stack.imgur.com/FUmow.png && /var/lib/mysql/fruitloops_juicebox adding the path still doesn't solve the issue... I don't understand whats wrong.

Accepted Answer

The most likely reason your command is failing is that you didn't provide an absolute path for mysql. Do it this way

```
/path/to/mysql -uderp_example -pexample -hlocalhost -Dexample -e"TRUNCATE TABLE juice box"
^^^^^^^^
```

It should work just fine.

It's because cron executes under an account which either does not have PATH defined or does not include a path to mysql.

Now there is another option - using a MySQL event

```
CREATE EVENT update_date_column 
  ON SCHEDULE EVERY 1 HOUR STARTS NOW()
  DO TRUNCATE TABLE juicebox;
```

If you'll decide to go with an event approach:

- use `SHOW EVENTS` to list created events with their attributes (e.g. `status`)

- use `SHOW PROCESSLIST` to check if the event scheduler is enabled. If it's ON you should see a process "`Daemon`" by user "`event_scheduler`".

- use `SET GLOBAL event_scheduler = ON;`to enable the scheduler if it's currently not enabled.

- More on configuring event scheduler read here

Truncate Mysql Table Cron Job?

Solution

Problem