MariaDB broken

It happened again.

I did brew update/upgrade a while ago then weeks after that restarted MariaDB then days later tried to access MariaDB.

MariaDb too many open files

Trying to get info over the config file seems not easy.

As Drupal 8 now required a mysql version higher then my current manually installed version 5.1.46 released on April 23, 2010 I have to try yo get rid of that version and install the latest through homebrew.

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;"

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

cp /usr/local/mysql/support-files/my-medium.cnf /etc/my.cnf
vi /etc/my.cnf 
/Library/StartupItems/MySQLCOM/MySQLCOM stop
/Library/StartupItems/MySQLCOM/MySQLCOM start

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.

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

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

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 ;-)