Event Detail Log

Event Date12/21/10
Problem: User cannot access mysql shell on Ubuntu 10.04.1 LTS: # mysql --user root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
Resolution: Forgotten password? Reset password:# service mysql stop # mysqld_safe --skip-grant-tables # mysql -u root mysql> use mysql; mysql> update user set password=PASSWORD("secret") where User='root'; mysql> flush privileges; mysql> quit # service mysql restart # mysql -u root -psecret mysql>
 
Event Date12/21/10
Problem: Syslog and /var/og/daemon.log are showing lots of mysql "Installation of MySQL is already upgraded to 5.1.41, use --force if you still need to run mysql_upgrade" warnings from /etc/mysql/debian-start and "mysql main process (####) terminated with status 1" every ~100 seconds from init on mysql 5.1.41-3ubuntu12.7
Resolution: Based on the bug #615869 report below, restarted all mysql services and errors went away.
Links: Bug #49823: mysql_upgrade fatal error due to general_log / slow_low CSV NULL
Bug #615869: Infinite loop upgrading to 5.1.41-3ubuntu12.6
 
Event Date12/22/10
Problem: Partition an existing (syslog) mysql 5.1.41-3ubuntu12.7 table based on date: mysql> ALTER TABLE SystemEvents PARTITION BY RANGE ( DATE(DeviceReportedTime) ) ( PARTITION p1 VALUES LESS THAN(2010-12-22), PARTITION p2 VALUES LESS THAN(2010-12-25) ); ERROR 1564 (HY000): This partition function is not allowed
Resolution: You can ONLY partition on a primary/unique key field that stores integer values on MySQL 5.1.51 (and older at least). In this case, I had to switch to using the TO_DAYS() function because even formatting the DATE as an integer-like YYYYMMDD also did not work.
Links: Bug 13436: Partitions: most data types don't work
 
Event Date2/24/11
Problem: MySQL has to many open files - This is most likely caused by partitioning a database which dramatically increased the number of open files. ERROR 23 (HY000) at line 1: Out of resources when opening file './syslog/saffron#P#p20110116.MYD' (Errcode: 24)
Resolution: This was caused by partitioning. The MyISAM engine stores the table format, table data and tables indexes in separate files so, 3 files will be accessed a table or partition. You can check the number of open files with "lsof | grep mysqld" from the command line (also see "ulimit -a" or, on gentoo, "cat /proc/sys/fs/file-nr"). From the mysql console you can also run "STATUS;" to see open files or "SHOW VARIABLES LIKE '%file%';" and check the open files limit.

MySQL keeps some files open for previously cached queries. Execute "FLUSH TABLES;" to clear the internal cache for temporary relief.

Internet reports state to increase the maximum amount of file handles edit /etc/security/limits.conf and add: mysql soft nofile 4096
mysql hard nofile 4096
However, local sources stated that this did not work in their experience so, I did not try it.

I did add the following line to /etc/mysql/my.cnf which did increase the maximum file descriptors in mysql after restarting the service: set-variable = open_files_limit=4096 I then ran the following to increase the maximum number of file descriptors on the system (was at 1024): host ~ # ulimit -n 5120
Links: MySQL Error 23 on Linux
5.1.2. Server Command Options: open_files_limit
E.7.3. Windows Platform Limitations
 
Event Date3/08/11
Problem: sendmail command not sending email from one server (using telnet to manually send email works fine) in mysql-related shell script. mailq and /var/log/mail.log show the following: postqueue: warning: Mail system is down -- accessing queue directly
Resolution: postfix was off. Started it. /etc/init.d/postfix status
/etc/init.d/postfix start
Unfortunately, the multitude of email messages that had been queueing up since postfix shut down a month ago were then delivered to their recipients. Remember that mailq only shows messages in the sendmail queue. Also check qshape.pl, pfqueue or postqueue -p for postfix queues.
In this case, the messages were so old that they could have been safely deleted with postsuper: postsuper -d ALL
Links: kangry.com - Sendmail command line with subject example linux