| Event Date | 12/21/10 |
| Problem: | User cannot access mysql shell on Ubuntu 10.04.1 LTS:
# mysql --user root |
| 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 Date | 12/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 Date | 12/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 Date | 2/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
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 Date | 3/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
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 |