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:


UPDATE drupal_content_type_housing_application
LEFT JOIN drupal_node
ON drupal_content_type_housing_application.field_housing_inst_affil_nid=drupal_node.nid

SET drupal_content_type_housing_application.field_app_inst_affil_value = drupal_node.title

WHERE drupal_node.nid=drupal_content_type_housing_application.field_housing_inst_affil_nid

h/t devshed

Posted by kkwaiser at 12:22 PM | Comments (0)

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.

Posted by kkwaiser at 12:52 PM | Comments (0)

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

Show processes:
mysql> show processlist;
mysql> show full processlist;
mysql> show processlist\G;

Kill query:
mysql> kill query 1831;

Posted by kkwaiser at 03:44 PM | Comments (0)

January 31, 2011

Reset AutoIncrement counter in MySQL

See this comment:

ALTER TABLE [tablename] AUTO_INCREMENT = [number]

Posted by kkwaiser at 03:29 PM | Comments (0)

Reordering MySQL columns

See this post.

Posted by kkwaiser at 02:16 PM | Comments (0)