AutoMySQLBackup daily rotation only keep one day or latest backup day SOLVED

I had problems with my AutoMySQLBackup script on my ubuntu vps server, using the latest (3.0_rc6) available version of the script. The problem I had was that the daily rotation was not working as expected. The script kept yesterdays backup and did not remove it. It seemed to be happening randomly but nothing is ever random.

So today I took some of my precious time to investigate the problem. I found out that the problem was on line 803:

find "${CONFIG_backup_dir}/${subfolder}${subsubfolder}" -mtime +"${rotation}" -type f -exec rm {} \;

The rotation-parameter was configured to be "0". No problems with that as we only want to keep the latest backup file that was created today. But the mtime function with find is the problem where the argument to mtime are the number of whole days. While some backup-files wasn't created (or modified) a whole day from the current time.

-mtime n
       The primary shall evaluate as true if the file modification time subtracted from the initialization time, divided by 86400 (with any remainder discarded), is n.

So this is the problem. Now the solution can be fixed in various of ways. But I just needed a quick fix to this as I always want to only keep the latest mysql database backup files. So I modified that line (in particularly and some others just on case I need something else than 0 in rotation configuration) to:

find "${CONFIG_backup_dir}/${subfolder}${subsubfolder}" -mmin +$((60*23)) -type f -exec rm {} \;

As you can see from above, I'm looking for files that is 23 hours or older. So this covers my gap of backup file creation time.

I'm happy if this helps you out. AutoMySQLBackup is a great script but not really maintained the best.

Non case and case sensitive MySQL tabels

MySQL table names are sometimes case sensitive depending on what configuration you have and if you are running in a Unix or Windows environment.
What controls if it's non case or case sensitive is a setting in my.cnf (my.ini in Windows)

What you'll be looking for if lower_case_table_names is set in your my.cnf
However if you haven't specified the default value of lower_case_table_names , then they are:

• Unix environment the default value is 0
  
• Windows the default value is 1
• Mac OS X, the default value is 2

You can also make a SQL query to check the current settings, by doing this query:

show variables like "%lower%"

If you don't want to have a non case sensitive setting then lower_case_table_names should be set to:

lower_case_table_names = 1

If you want it to be case sensitive then the settings should be 2.
Save your changes in my.cnf then restart your mysql server so the new configuration will be applied.

MySQL problem, Thread stack overrun: * bytes used of a * byte stack, and * bytes needed. Use 'mysqld -O thread_stack=#' to specify a bigger stack

Are you getting this problem when running custom SQL in phpmyadmin or in the mysql error log? Or maybe if you catch the exception in a programming language like ie php.

One exempel of a problem when requesting data from the database:

#1436 - Thread stack overrun: 5012 bytes used of a 131072 byte stack, and 128000 bytes needed. Use 'mysqld -O thread_stack=#' to specify a bigger stack.

The solution here is to specify a bigger stack as said. One way to do this is to edit your my.cnf file for mysql. You normally will do find the file in Linux at this location: /etc/my.cnf or: /etc/mysql/my.cnf
The location can wary, and sometimes you may not even have a my.cnf then you need to create one.

Open my.cnf in your preferred editor, like vim. And then change or add a line that are higher than the demanded stack size.
You'll be looking for a line like this in your cnf-file:

thread_stack = 192K

Change the thead_stack to a bigger size than what it's specified. If you can't find this line, add it.

After this you'll need to restart mysqld so the changes will apply.

"When the thread stack is too small, this limits the complexity of the SQL statements which the server can handle, the recursion depth of stored procedures, and other memory-consuming actions"