October 02, 2012
Copy data from 1 table to another
Upon getting rid of the Institution lookup field in favor of a simple text field, I wanted to migrate the existing Institutions to the new field prior to deleting the old. Here is the syntax to update a database table field with values drawn from another field in the same statement:
LEFT JOIN drupal_node
SET drupal_content_type_housing_application.field_app_inst_affil_value = drupal_node.title
June 29, 2012
Reset lost mySQL root password without authenticating
First, confirm that there is a password set for Root by changing the phpMyAdmin configuation to allow login without password.
$ cd /usr/share/phpmyadmin/
$ grep -r AllowNoPassword .
$ sudo nano libraries/config.default.php &
- Find the allownopassword setting and change to true
Try to login at localhost/phpmyadmin. If fails, then password is set and you need to login without authenticating so you can reset it.
$ sudo /etc/init.d/mysql stop
$ sudo mysqld --skip-grant-tables --skip-networking &
$ mysql mysql -u root
Type in the following command and reset your password:
> UPDATE user SET password=PASSWORD('newpassword') WHERE user="root";
If you didn't go through the first step, to validate the changes simply type:
> FLUSH PRIVILEGES;
If you started MySQL manually (bypassing the authentication process), the server must be restarted:
$ /etc/init.d/mysql restart
Then visit localhost/phpmyadmin and test the password.
February 01, 2011
Identify and kill slow queries
I've run into problems where a Drupal Views query involving Users executes extremely slow. Here is how to identify and kill it:
Login to mysql
$ mysql -u [dbuser] -h [dbhost] -p
mysql> show processlist;
mysql> show full processlist;
mysql> show processlist\G;
mysql> kill query 1831;
January 31, 2011
Reset AutoIncrement counter in MySQL
See this comment:
ALTER TABLE [tablename] AUTO_INCREMENT = [number]
Reordering MySQL columns
See this post.