cPanel/WHM 11.30 now supports MySQL TRIGGERS and VIEW privileges

I just noticed that cPanel/WHM 11.30 now supports MySQL TRIGGERS and VIEW privileges. From the official release notes

In 11.30 we are adding support for the MySQL TRIGGER and VIEW privileges.

  • Systems using MySQL 5.0 and higher can use the VIEW privilege. This privilege will grant the database user the CREATE VIEW and SHOW VIEW MySQL privileges.
  • When using MySQL 5.0, the SUPER privilege is required for the TRIGGER privilege. Due to this caveat, we will not support the TRIGGER privilege on systems using MySQL 5.0.
  • Systems using MySQL 5.1 and higher can enable and use the TRIGGER privilege. However, this privilege is not available if binary logging is enabled for the MySQL service.

Neither TRIGGER nor VIEW is available to systems using MySQL 4.0 or 4.1.

Installing Lighttpd, PHP and MySQL on Ubuntu

If you are not getting a package on Ubuntu, please check to see if your /etc/apt/sources.list file is updated. This is common with most VPS providers

On VPS, especially with challenging small memory limit (e.g. 64MB or 128MB) lighttpd is the best option instead of Apache. In this article I will show you how to do that. On a fresh box (which has no apache installed) type this to get lighttpd webserver installed

apt-get install lighttpd

Now install PHP5 CGI package and PHP5 command line package

apt-get install php5-cgi php5-cli

Edit the following file

nano /etc/php5/cgi/php.ini

and uncomment this line

cgi.fix_pathinfo = 1

Now enable fastcgi module

lighty-enable-mod fastcgi

Restart Lighttpd

/etc/init.d/lighttpd restart

You get the welcome page

Lets install MySQL 5.1 server and client

apt-get install mysql-server mysql-client

and optionally phpMyAdmin

apt-get install phpmyadmin

Now create a test page to see if phpinfo is all Ok

nano /var/www/info.php

Type in the following

<?php
phpinfo();

Set mysql’s root password on fresh copy from SolusVM OS template

On SolusVM, your host may provide you with OS templates such as “Ubuntu 10.04 LTS LAMP” and you click-install it to your VPS. Now how do you change its mysql’s root password? simple, run these two commands on SSH after doing

mysql
UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
FLUSH PRIVILEGES;

Good bye CentOS! Hello Ubuntu

Its been about almost more then two years since I have been playing with CentOS on many different platforms etc. Its very stable but on the other hand, PHP/MySQL updates are very hard to get and still PHP is 5.1.6. I tried EPEL repo and it updated PHP to 5.3.1 but still there were conflicts with stuff and many core packages.

So, I thought why not move to Ubuntu since it has just announced 10.04LTS (Long Term Support) in April 2010. Folks at iWeb were really nice to do a reinstall without any charges and also process a memory upgrade request at an amazing 50% discount.

Ubuntu 10.04 LTS

  • Apache 2 version is 2.2.14
  • PHP version is 5.3.2
  • MySQL version is 5.1.41-3ubuntu12
  • SVN (Subversion) version is 1.6.6

As a comparison CentOS 5.4 has the following versions

  • Apache 2 version is 2.2.3
  • PHP version is 5.1.6
  • MySQL version is 5.0.77
  • SVN (Subversion) version is 1.4.2

So if you are a PHP developer looking to deploy a Linux flavour on your new box, choose Ubuntu 🙂

How to solve ‘You do not have sufficient permissions to access this page.’ in WordPress

I have been getting the following error because I changed the database table prefix

You do not have sufficient permissions to access this page.

The cause of the problem is that you have changed the database prefixes but still, some of the word press config is tied down to the previous prefix (wp_ or something)

Just execute the following two queries

-- Replace 'prefix_' with your actual prefix
UPDATE `prefix_usermeta` SET `meta_key` = REPLACE( `meta_key` , 'wp_', 'prefix_' );

-- Replace 'prefix_' with your actual prefix
UPDATE `prefix_options` SET `option_name` = 'prefix_user_roles' WHERE `option_name` ='wp_user_roles' AND `blog_id` =0;

How to Install Apache, PHP and MySQL on CentOS

I have seen people installing Apache, PHP and MySQL from source code on a production server. You should NEVER, NEVER, NEVER install these packages from source on an RPM or DEB based Linux distributions. Did I say “NEVER” enough times to make your realize that it is not a good practice?

Installing from source is bad idea; there are many reasons for this but the major one is that you will be spending a lot of time keeping the system updated. If you love installing from source then I would suggest using a different distribution e.g. Gentoo.

You can build from source if the package is not available in yum but still, try to use “yum” (or “apt-get” on Ubuntu) because it will save a lot of your time when upgrading your packages.

Continue reading

How to list Stored Procedures and Functions in MySQL database

How to check if there are any STORED PROCEDURE in MySQL database?

SHOW PROCEDURE STATUS;

Similarly for MySQL database FUNCTIONS

SHOW FUNCTION STATUS;

But it displays a list of all PROCEDURE and all FUNCTIONS, you can chop down the list by appending

## display FUNCTIONS from 'test' db
SHOW FUNCTION STATUS WHERE db LIKE ='test';

Hope it helps somebody.
Enjoy!

Installing MySQL 5.1 on Ubuntu

I wanted to use PARTITIONING introduced in MySQL 5.1 to handle some 2 Million records (and growing everyday) in a table. Ubuntu 8.10 does not have MySQL 5.1 available if you do apt-get the latest version available is 5.0

So this is what I did, its easy and this might help somebody.

nano /etc/apt/sources.list

I prefer nano editor but you can use any editor of your choice. Add these new lines at the end of the end of the file.

deb http://ppa.launchpad.net/monty/ubuntu gutsy main universe restricted multiverse
deb http://ppa.launchpad.net/smurf/ubuntu gutsy main universe restricted multiverse

(These packages were targeted for 7.10 so I will recommend using them and removing them from this list once you got the latest MySQL version)

Now run the following commands

apt-get update
apt-get upgrade

Note that you may need to do sudo prefix to these commands if you are using a non-root account. Now lets do the one last step, Installing MySQL 5.1

apt-get install mysql-server-5.1

Update: I have got another source to get even latest minor version of MySQL 5.1, just follow the above steps, then start over again and add following lines to “/etc/apt/sources.list” instead of the one above.

deb http://packages.dotdeb.org stable all
deb-src http://packages.dotdeb.org stable all

It worked for me on Ubuntu 8.10, im sure it will work for just any server version.

I am not a linux guru so if someone has a better option to upgrade to MySQL 5.1 or the latest version, please let me know.

Intall Apache, MySQL and PHP on Ubuntu 8.10

WARNING: Please use this method to install Apache, PHP and MySQL because its more suitable for most of your needs and has options to auto-upgrade your installation. The following method is obsolete and not recommended.

After trying to install Apache, MySQL and PHP on Ubuntu 8.10 via several methods listed over the web. I opted for xampp package for linux.

It is as easy as ABC

Download the xampp linux package from their official website

wget http://nchc.dl.sourceforge.net/sourceforge/xampp/xampp-linux-1.7.tar.gz

(I have selected NCHC, Taiwan as the mirror to download this package, you may try another)

Extract the files

sudo tar xvfz xampp-linux-1.7.tar.gz -C /opt

Chmod the files to have nobody ownership

sudo chown -R nobody:root /opt/lampp/

Configure xampp security

sudo /opt/lampp/lampp security

Start xampp with this command

sudo /opt/lampp/lampp start

Stop xampp

sudo /opt/lampp/lampp stop

Restart xampp

sudo /opt/lampp/lampp restart

Login to ftp using the username nobody and the password that you have set up in the “xampp security”

To uninstall xampp

sudo rm -fr /opt/lampp

Enjoy!!