mysql

Who is currently online

Door clemens op do, 2011/01/13 - 11:06am

I want to run svn up on the site but I need to offline it first. So who is online?

SELECT name, mail, (unix_timestamp()-access)/3600 AS hours
  FROM users
  HAVING hours < 1
  ORDER BY access;

Or for short

drush sql-query "select name, mail, (unix_timestamp()-access)/3600 as hours from users having hours < 1 order by access;"

mysql on mac

Door clemens op di, 2011/01/11 - 8:56am

(this is my scratchpad)
I installed mysql on a mac straight from mysql website. Not sure whether that was a wise decision.

free form:

Dumping all tables with drush

Door clemens op do, 2010/05/20 - 12:46pm

I want a clean database without clicking.

echo "\
SELECT concat('drop table ', table_name, ';') \
  FROM information_schema.tables \
  WHERE table_schema=schema()" \
| `drush sql-connect` \
| grep "^drop table " \
| `drush sql-connect`

This work only for single drupal / single schema installs.

Create and Fill the database

Door clemens op di, 2009/06/23 - 11:23am

Just follow README.mysql.txt

mysqladmin -u root -p create drupaljam_demo
mysql -u root -p
> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON drupaljam_demo.* TO 'drupaljam_demo'@'localhost' IDENTIFIED BY 'drupaljam_demo';

Prepare settings.php

mv default.settings.php settings.php
chmod 777 settings.php

Add the site to apache (my steps)

cd /etc/apache2/sites-available/
cp drupaljam.dev drupaljam.demo
vi drupaljam.demo
a2ensite drupaljam.demo
vi /etc/hosts
# add 127.0.0.3 drupal.demo

free form:

Relocating the files directory

Door clemens op di, 2009/02/03 - 11:10am

Trying best practices I occasionally move the files directory from DRUPAL_ROOT/files/my-site.com to DRUPAL_ROOT/sites/my-sites/files

Doing this has some implications for the files table content. All file-path are pointing to the wrong location.

Quick solution for this is an mysql replace update query.

select replace(filepath, 'files/my-site/', 'sites/my-site/files/') from files;
# update files set filepath=replace(filepath, 'files/my-site/', 'sites/my-site/files/')"

Or through drush

free form:

mysql cycle

Door clemens op ma, 2008/03/03 - 12:18pm

I still forget how to do the database and user creation and drop goes. So why not make a note.

Creating

create database test;
create user 'test' identified by 'pwtest';
grant all on test.* to 'test'@'localhost' identified by 'pwtest';

Cleaning up

drop user test;
drop database;

Yeah ... call me stupid ;-)

Subscribe to RSS - mysql