Solve AutoMySQLBackup warning mysql.event

Fix for: -- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.

edit /usr/sbin/automysqlbackup (wherever your /etc/cron.daily/automysqlbackup points)
find the OPT="--quote-names" line (about line 354)

Change it to
OPT="--quote-names --events --ignore-table=mysql.event"

OR, (Based upon additional reading of responses):
one of:
OPT="--quote-names --events"

OPT="--quote-names --ignore-table=mysql.event"

The original line suppresses the warning and ignores the table (redundant).
If you wish to have the table in your backup, don't ignore the table, just use --events
If you don't care about the table, ignoring the table will also suppress the event message.

What's optimal? Depends on if you use events and require them to be restored.
http://dev.mysql.com/doc/refman/5.1/en/events-table.html



Reference: http://bugs.mysql.com/bug.php?id=68376

automysqlbackup error fix

You get this:
mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user 'debian-sys-maint'@'localhost' for table 'cond_instances' when using LOCK TABLES

to fix it:
edit /etc/default/automysqlbackup
change the DNAMES line to this:

DBNAMES=`mysql --defaults-file=/etc/mysql/debian.cnf --execute="SHOW DATABASES" | awk '{print $1}' | grep -v ^Database$ | grep -v ^mysql$ | grep -v ^performance_schema$ | tr \\\r\\\n ,\ `

or, to be succinct: add

 | grep -v ^performance_schema$ 

into the list. If you're doing any different method of DBNAMES, just make sure to exclude performance_schema.

Why is this happening?

automysqlbackup's default configuration attempts to lock tables before dumping. This error isn't specifically automysqlbackup's problem to fix, as much as it's a problem (or not) that performance_schema.cond_instances can't be locked [by debian-sys-maint] at the time of mysqldump, and mysqldump is what's throwing the error.

Is it a problem of backing up or not backing up performance_schema?

I can't answer that for your situation, though if you understand what the table does and how to recreate it you can be better informed about whether the backup is necessary to you. (hint: probably not unless corporately you need to keep all diagnostic logs on everything.) Also, this is not related to the other databases which actually hold your data and which you do want to back up.

 What does grep -v do?

It says, "don't include this in the list". Note, while you're here, that this also includes the "mysql" table. If you need to keep your users and permissions for disaster recovery reasons, you may wish to consider not excluding that table.

What is "Database"? Why is that grep -v? I don't have a database called Database.

Database is the column title of the result of "SHOW DATABASES" SQL query.

OpenOffice Base MySQL ALTER table structure

I may have missed it, but it's likely that you will have trouble refreshing table structure in OpenOffice Base if you connect to MySQL and ALTER a table.

Basically, the answer is to add the fields in the Edit of the table(s) in Base to match the new columns. If you don't know what they are, create a new table connecting to the ODBC of MySQL and edit the appropriate table.

It's a real pain, but there you are.

MySQL fix some phone numbers

This is long and convoluted, but basically, it takes unknown inputs of phone numbers from a table:

5551212

555-1212

212555-1212

212-555-1212

and makes them all

2125551212

select Concat(if(length(PhoneNumber)< 10, "212", ""), replace(PhoneNumber, "-","")) FROM AddressBook;

update addressbook set PhoneNumber=Concat(if(length(PhoneNumber)< 10, "212", ""), replace(PhoneNumber, "-",""));

MySQL Query to check if a birthday is upcoming in 7 days

Someone probably has done this before, but in case it hasn't been done, here's my query:

$result = mysql_query("SELECT FirstName, LastName, DATE_FORMAT(birthday, '%M %d') as bday, CONCAT(IF(MONTH(birthday)=1 AND MONTH(CURRENT_DATE())=12, YEAR(CURRENT_DATE())+1, YEAR(CURRENT_DATE())), DATE_FORMAT(birthday, '-%m-%d')) AS fakebirthday FROM birthdays WHERE CONCAT(IF(MONTH(birthday)=1 AND MONTH(CURRENT_DATE())=12, YEAR(CURRENT_DATE())+1, YEAR(CURRENT_DATE())), DATE_FORMAT(birthday, '-%m-%d')) BETWEEN CURRENT_DATE() AND DATE_ADD(CURRENT_DATE(), INTERVAL 7 DAY) ORDER BY fakebirthday;");

What it does (this is PHP code, but the stuff in double-quotes is the real meat.):

1. birthday is stored in datetime format
   
2. The SELECT AS is relatively straight-forward. I don't want to divulge age/years of this list, so I'm only reporting month and day (January 1) in the result
   
3. I'm making a fakebirthday calculation in order to sort/Order by. Unfortunately, I can't use the fakebirthday in "WHERE" clause, so I repeated the calculation to do my "WHERE".
   
4. The idea is to check for the next 7 days, and cheat by prepending *this* year on the birthday's month and day. Unfortunately, if this month is December (12) and the birthday is in January (1), this won't work, so add 1 to this year and prepend it to the January birthdates -- but only when running this in December.
   
5. of course, check if it's between today and 7 days from today
   
6. and sort by the "fake" birthday because it's easier to sort this way so that January comes after December
   

Oh, I know it's ugly code. It's probably slow, too. However, the idea is this should run once a day (once a week?) on fairly small amounts of data (office active employee records).