Educating the world

Our blog has over 10,000 readers a month

Creating a MySQL user under Plesk

January 24th, 2012

Plesk has a comprehensive web interface but every now and again you just want to get at the nuts and bolts. Avoiding the Plesk interface will allow you to do batches of tasks or automated work.

This article shows us how to create a database, add a user and set up their permissions. I’ve always found MySQL’s permission and grant structure unnecessarily complicated so in this example we are just going to give the users all the default permissions.

First up create the database. Under Plesk you can call the database anything you like, but I’ve adopted the naming convention <domain>_<usage>. This gives me several advantages:

  1. It groups the databases together in the file system so it is easier to find what you are looking for.
  2. Avoids name clashes.
  3. If you create a database outside Plesk it is not managed, which means it won’t get deleted when you delete the domain. All MySQL database files are owned by MySQL so it’s difficult to see which domain a particular database instance belongs to. Not any more!
  4. Your system looks like there is order!

First up is to create a database for and we are going to use it for the world’s best blogging software B2evolution.

mysqladmin -uadmin -p`cat /etc/psa/.psa.shadow` create example_com_b2evolution

There’s no need to enter our password on the command line because Plesk stores it in a file.
The database creation time is almost instant.
Next up, we need to enter the MySQL command line.

mysql -uadmin -p`cat /etc/psa/.psa.shadow`

Strictly speaking you can create database instances from inside MySQL using the following command.

CREATE DATABASE example_com_b2evolution;

I prefer to do it externally though, partly because after the database has been created you can enter the MySQL command prompt straight into that clean database but mostly because soon after creating the database I do an import which is another external command.

The only people accessing the database will be my web server on the localhost. Users can have access to database, tables, DDL commands, functions, store procedures and triggers. In reality though, in almost all cases you just want one user who can access everything in one database instance and that’s it. I’ve been using MySQL for 15 years and never had to give a user a single table, select only permission! Enough babbling, MySQL says you have to create a floating user first then assign permissions to them. If I was going to go completely anal I would insist on having a separate user for each application and add table level permissions for that user to access just those tables, but generally speaking it’s not worth the hassle. Domain specific administrators want to be able to log in and check their databases, they don’t want to keep switching users to administer different applications in the same database. You’ll find in fact that you’ll need a database user for some operations like repairing or optimising tables.

From the MySQL command prompt:


Now that we have a user we’ll assign them some rights. This statement says “Give david access to all the tables in the example_com_b2evolution database, but only when he logs in from the localhost. You can set different permissions for when I log in from home or whether I login from work! I don’t like the idea that my security level changes depending on where I’m logging in from. It just feels wrong. I always thought it would be better and cleaner to have a separate user as it can lead to confusion.

GRANT ALL ON example_com_b2evolution.* TO david@localhost;

Finally we must set a password for our user. A side effect of having floating users and host name restrictions is that you can have a different password depending on where you log in from. This seems even wronger!

SET PASSWORD FOR david@localhost=PASSWORD(’!,DaViD54890′);

I have experimented with this in the past and it was a pain in the arse! There were all sorts of peculiar behaviours and I seemed to spend most of my time trying to figure out why you could log in from hostB with userA but not with userB.

The internet is one big soapbox

January 16th, 2012

The Internet’s content can be split into many pieces, but it can be characterised into just 2 (well 3 if you count all the porn!): what people know and what people think.

What people know is simple; it includes all the journals, academic papers, research, medical journals, technical specifications and documentation. All of these sources are considered true (at the time of writing). What people think is also simple. These are (but not limited to) blogs, forums, comments sections, Facebook and Twitter.

But wait… is it really that simple? No. If you know my mate Switfy, he talks like it’s true but he has no idea and is generally just making it up. So at the end of one of his explanations we have to ask: “Is that true or Swifty-True?". The Internet is still run by people and everybody lies. In some cases they may not know they are telling untruths they could just be mistaken.

There are many places devoted to collecting peoples opinion. Sites like YouGov ask people how they are feeling about their lives, their aspirations and how they feel about the current state of the country. It also conducts market research.

There are several examples of mistaken truths in action. The UK press reported recently a story about Nadine Dorries M.P. and her blog. She had been keeping a diary reassuring constituents about how hard she was working but caused outrage when it was discovered that about 70% of it was untrue. Another ongoing example is that of Wikipedia. It’s content is generated by people, most of the pages are written by the experts in that field, but a lot of them are not. Wikipedia has started requiring citations to back up each statement made. This reduces the number of vague or inaccurate claims but they can still be indexed, searched and referenced.

Every couple of months I get a scaremongering chain email from one of my friends who seems not to be able to tell the difference between a genuine email and a hoax or spam email. They tend to read the subject of “Let stop baby rabbits being used in underwater mining” think this is terrible and send it to everyone they know. Most of them believe that because they “think” they know who sent the message they automatically trust the contents of the email. Some are not so obvious, for example the Marks & Spencer Voucher Giveaway Hoax is one such email I tend to get a lot around Christmas time. It shows that in some cases the original recipient has not even read the email before passing it along. If you read the email it says that someone will give you £100 for passing the email to 8 friends. In fact they are doing the spammer’s job for them.

Spammers are lazy. They just cut and paste the email which makes it really easy to lookup at Google. Copy one sentence from the middle of the email and paste it into Google. You will always come up with a hit because you are highly unlikely to be the first person who is targeted and most of the circulars are reused from a couple of years ago.

You, as an Internet user, must decide for yourself what is true and what is false. Often there is just opinion which is very subjective and dressed up as truth. Often there is someone trying to con you. In fact, it’s just like life.

Start with just six words

January 13th, 2012

Homework: write opening and first scenes, try to introduce aspects of the characters without explicitly referencing them.

Jeff leaned over the guard rails of the bed. His eyes were bloodshot and his cheeks were wet with tears. The little girl looked jaundice, her skin was getting more yellow. The only colour Jeff could see was the blood stained bandages wrapping her arms and legs. Her body was like a rag doll that the dog had been throwing about.
Dr. Barrett placed his hand on Jeff’s shoulder and in a deep comforting voice said,
“Come on Jeff, there’s not much you can do here. Come into my office and you can go through some of the details of what happened to Emily".
Dr. Barrett helped Jeff to his feet and put his black arm around Jeff’s waist. They walked slowly towards the door. Jeff’s breathing quickened each time he placed his right foot down: Dr. Barrett’s arm muscle flexed as they moved in time.
It was only a small hospital on the edge of the reserve which worried Jeff. Emily seemed too weak to be moved and he wasn’t sure if the facilities here would be enough.


  1. The rag doll reference is a nice simile.
  2. Reader is thrown in a the deep end, harsh on the reader.
  3. Good opening line.
  4. There’s a good example of “Showing” which is describing the picture instead of “Telling” which is explaining what the picture it. Jeff was injured too.

To cut a long story short

This week’s lesson was about condensing a story into the fewest words possible but still include a beginning, middle and an end. Ernest Hemingway said that the best story he ever wrote was:

For sale: baby shoes, never worn

It’s a synopsis of a story.

My effort:

“Excessive drinking with a mystery woman, son”

Homework for next time:
Start with a 6 word story.
Break it up into a beginning of 75 words and middle of 75 words and an end of 50 words.
Split the beginning up into a beginning, middle and end. Include 3 crisis points in the beginning, 6 crisis points in the middle and 2 in the end.
6 word story should be 6 words.
First story draft should be 300 words.
Final story should be 3000 words.

Science Fiction

January 9th, 2012

There are many people who turn their noses up at Science Fiction films and yet the majority of people do like them, it’s just they re-classify them in their mind. I over heard a conversation in the pub on Friday.

John: Don’t like SciFi.
Al: What about Terminator 2?
John: That’s not SciFi, that’s an action film.
Al: Robots coming from the future to change history, how much more SciFi can you get??

rpmdb: unable to join the environment

January 6th, 2012

How time flies? It only seemed like yesterday that I installed CentOS 5.4 at a customer site. This week I needed to update rsync from 2.6.8 to what ever the latest version is. So I typed:

yum update rsync

and got the following message.

Loaded plugins: fastestmirror
rpmdb: unable to join the environment
error: db4 error(11) from dbenv->open: Resource temporarily unavailable
error: cannot open Packages index using db3 - Resource temporarily unavailable (11)
error: cannot open Packages database in /var/lib/rpm
Traceback (most recent call last):
File “/usr/bin/yum", line 29, in ?
yummain.user_main(sys.argv[1:], exit_code=True)
File “/usr/share/yum-cli/", line 309, in user_main
errcode = main(args)
File “/usr/share/yum-cli/", line 157, in main
File “/usr/share/yum-cli/", line 187, in getOptionsConfig
File “/usr/lib/python2.4/site-packages/yum/", line 664, in <lambda>
conf = property(fget=lambda self: self._getConfig(),
File “/usr/lib/python2.4/site-packages/yum/", line 239, in _getConfig
self._conf = config.readMainConfig(startupconf)
File “/usr/lib/python2.4/site-packages/yum/", line 804, in readMainConfig
yumvars[’releasever’] = _getsysver(startupconf.installroot, startupconf.distroverpkg)
File “/usr/lib/python2.4/site-packages/yum/", line 877, in _getsysver
idx = ts.dbMatch(’provides’, distroverpkg)
TypeError: rpmdb open failed

I also tried:

rpm -qa

which resulted in:

rpmdb: unable to join the environment
error: db4 error(11) from dbenv->open: Resource temporarily unavailable
error: cannot open Packages index using db3 - Resource temporarily unavailable (11)
error: cannot open Packages database in /var/lib/rpm

Even though the message says Resource temporarily unavailable, it was in fact always unavailable!
Hunted around and found this article which helped a lot.

The problem is caused by an empty database index file in the RPM repository, probably caused by corruption. In my case it was because most of the index files were missing.

[root@host tmp]# ls -l /var/lib/rpm
total 39656
-rw-r–r– 1 root root 5107712 Nov 11 11:58 Basenames
-rw-r–r– 1 root root 12288 Oct 5 2010 Conflictname
-rw-r–r– 1 root root 0 Dec 12 11:42 __db.001
-rw-r–r– 1 root root 1036288 Nov 11 11:58 Dirnames
-rw-r–r– 1 root root 5259264 Nov 11 11:58 Filemd5s
-rw-r–r– 1 root root 24576 Nov 11 11:58 Group
-rw-r–r– 1 root root 16384 Nov 11 11:58 Installtid
-rw-r–r– 1 root root 40960 Nov 11 11:58 Name
-rw-r–r– 1 root root 30818304 Nov 11 11:58 Packages
-rw-r–r– 1 root root 331776 Nov 11 11:58 Providename
-rw-r–r– 1 root root 102400 Nov 11 11:58 Provideversion
-rw-r–r– 1 root root 12288 Oct 5 2010 Pubkeys
-rw-r–r– 1 root root 413696 Nov 11 11:58 Requirename
-rw-r–r– 1 root root 208896 Nov 11 11:58 Requireversion
-rw-r–r– 1 root root 81920 Nov 11 11:58 Sha1header
-rw-r–r– 1 root root 45056 Nov 11 11:58 Sigmd5
-rw-r–r– 1 root root 12288 Feb 16 2010 Triggername

So all I needed to do was remove all the index files and rebuild them.

rm -rf /var/lib/rpm/__db*
rpm -rebuilddb

The RPM repository now looks like:

-rw-r–r– 1 root root 2768896 Jan 6 09:49 Basenames
-rw-r–r– 1 root root 12288 Oct 5 2010 Conflictname
-rw-r–r– 1 root root 0 Jan 6 09:49 __db.000
-rw-r–r– 1 root root 24576 Jan 6 09:49 __db.001
-rw-r–r– 1 root root 1318912 Jan 6 09:49 __db.002
-rw-r–r– 1 root root 450560 Jan 6 09:49 __db.003

-rw-r–r– 1 root root 978944 Jan 6 09:49 Dirnames
-rw-r–r– 1 root root 5267456 Jan 6 09:49 Filemd5s
-rw-r–r– 1 root root 24576 Jan 6 09:49 Group
-rw-r–r– 1 root root 16384 Jan 6 09:49 Installtid
-rw-r–r– 1 root root 45056 Jan 6 09:49 Name
-rw-r–r– 1 root root 25427968 Jan 6 09:49 Packages
-rw-r–r– 1 root root 335872 Jan 6 09:49 Providename
-rw-r–r– 1 root root 98304 Jan 6 09:49 Provideversion
-rw-r–r– 1 root root 12288 Oct 5 2010 Pubkeys
-rw-r–r– 1 root root 409600 Jan 6 09:49 Requirename
-rw-r–r– 1 root root 196608 Jan 6 09:49 Requireversion
-rw-r–r– 1 root root 81920 Jan 6 09:49 Sha1header
-rw-r–r– 1 root root 45056 Jan 6 09:49 Sigmd5
-rw-r–r– 1 root root 12288 Feb 16 2010 Triggername

I re-issued the update command:

yum update rsync

and it successfully installed rsync 3.0.6-4.el5_7.1.

Interestingly, CentOS 5.4 has been deprecated and so this version of rsync was for CentOS 5.7, but it didn’t need to install any dependencies.