Category: MySQL

Restarting Apache and MySQL is sometimes necessary when the site isn’t responding. I can never remember exactly where the commands are located on different flavors of Linux/Unix.

Gentoo

This is where the services are located on Gentoo.

sudo vi /etc/php/apache2-php5/php.ini 
sudo /etc/init.d/apache2 restart
sudo /etc/init.d/mysql restart 
sudo /etc/init.d/exim restart 

OSX Mountain Lion

sudo apachectl restart

sudo /usr/local/mysql/support-files/mysql.server stop
sudo /usr/local/mysql/support-files/mysql.server start 

Ubuntu

Automatically upgrade packages using Aptitude.

sudo aptitude safe-upgrade
sudo reboot

If Aptitude doesn’t work, then use apt-get.

sudo apt-get update
sudo apt-get upgrade

Restarting services is easier on Ubuntu since you don’t need to remember where they are. The /etc/init.d restart method used in Gentoo also works.

sudo service apache2 restart
sudo service ssh restart
sudo service mysql restart

Just some miscellaneous code that I’ve been using to update fields in my database. Basically lots of trimming and concatenating. A little substring manipulating thrown in for good measure.

SELECT *  FROM `words_for_slps` WHERE `F` REGEXP 'z$' AND `pronunciation` REGEXP 'z$'

UPDATE`words_for_slps` SET `phonemes` = TRIM(TRAILING 's' FROM phonemes) WHERE `F` REGEXP 'z$' AND `phonemes` REGEXP 's$' AND `word` = 'Ares'

UPDATE`words_for_slps` SET `phonemes` = CONCAT(phonemes,'z') WHERE `F` REGEXP 'z$' AND `pronunciation` REGEXP 'z$'

UPDATE`words_for_slps` SET `phonemes` = TRIM(TRAILING 's hz' FROM phonemes) WHERE `F` REGEXP 'hz$' AND `phonemes` REGEXP 'hz$'

UPDATE`words_for_slps` SET `phonemes` = CONCAT(phonemes,'ʃ ɛ z') WHERE `F` REGEXP 'z$'

UPDATE`words_for_slps` SET `phonemes` = CONCAT(phonemes,'z') WHERE `F` REGEXP 'z$' AND `pronunciation` REGEXP 'z$'
UPDATE`words_for_slps` SET `phonemes` = CONCAT(phonemes,' ɛ s') WHERE `F` REGEXP 's' AND `phonemes` REGEXP 'n$'

UPDATE `words_for_slps` SET `phonemes` = TRIM(LEADING 'c' FROM phonemes) WHERE `word` REGEXP '^c' AND `I` LIKE 'c'
UPDATE `words_for_slps` SET `phonemes` = CONCAT('k',phonemes) WHERE `phonemes` REGEXP '^ ' AND `I` LIKE 'c'

UPDATE `words_for_slps` SET `phonemes` = TRIM(LEADING 'k' FROM phonemes) WHERE `word` REGEXP '^cy' AND `phonemes` REGEXP '^k'
UPDATE `words_for_slps` SET `phonemes` = CONCAT('s',phonemes) WHERE `word` REGEXP '^cy' AND `phonemes` REGEXP '^ '

UPDATE `words_for_slps` SET `phonemes` = REPLACE(phonemes, 't ɛ,i  d', 't ə d') WHERE `phonemes` REGEXP 't ɛ,i  d$' AND `pronunciation` REGEXP 't u d$'

UPDATE `words_for_slps` SET `phonemes` = REPLACE(phonemes, 'n z', 'nz') WHERE `pronunciation` REGEXP 'n z$' AND `phonemes`REGEXP 'n z$'

I used these select statements to look for words that need some manual cleanup.

SELECT *  FROM `words_for_slps` WHERE `F` <> SUBSTRING_INDEX(phonemes, ' ', -1)
SELECT *  FROM `words_for_slps` WHERE `word` REGEXP '^w' AND `pronunciation` NOT REGEXP '^w' AND `pronunciation` IS NOT NULL

This works when I want to update the fields in one table with values from fields in another table.

UPDATE words_for_slps, words_for_slpsBAK12
SET words_for_slps.grade = words_for_slpsBAK12.grade
WHERE words_for_slps.word_id = words_for_slpsBAK12.word_id;

Even though I’m only updating one table you’d think this should work, but it doesn’t.

UPDATE `words_for_slps` 
LEFT JOIN `words_for_slpsBAK12` ON `words_for_slps.word_id` = `words_for_slpsBAK12.word_id`
SET `words_for_slps.grade` = `words_for_slpsBAK12.grade`