Tag: MySQL

Workaround for Cygwin path issues

A colleague of mine had a problem with his Cygwin setup.  For some reason, he couldn’t just run “mysql” to start his MySQL command-line client.  The error that he was getting back was:

$ mysql
sh.exe: mysql.exe: command not found

Typing the full path to mysql.exe every time is more than annoying.  After searching the web for a bit, I learned that the problem might be with the msys/cygwin terminal, which doesn’t like the backslashes that Windows uses in the PATH variable. I’ve tried a few different variations of setting up the path, but eventually gave up. It just didn’t work.

But since there is more than one way to do it, I solved the problem in a completely different way – an alias.  Just edit the .bashrc file and add the following line:

alias mysql="/c/full/path/to/your/mysql.exe"

Obviously, replace the fake path with the full path to your mysql.exe and restart the terminal.  From now on, every time you type “mysql“, it’ll be like you’ve typed the whole thing again.

P.S.: The same solution is applicable to the other similar problems.

MySQL prompt

I’ve been using MySQL for quite a few years by now, but only today I learned that it is possible to define MySQL prompt.  As per this blog post, all it takes is a couple of lines in .my.cnf file with something like:

[mysql]
prompt="\u@\h (\d)> "

That alone will help to prevent a gadzillion of destructive mistakes when you think that you are working with one database, when, in fact, you are working with a totally other.  On top of that, the blog post suggests using rlwrap tool, with which one could add some colors to the prompts as well.

MySQL export CSV into OUTFILE triggers “access denied” error

I came across a weird problem today.  Gladly, the web is full of solutions, but I’m going to post this anyway, just to have it nearby for the next time.  I needed to export the results of some query into a CSV file directly from MySQL.  I prepared my query, made sure that I can see the correct results and than changed it to export into the file. The query looked something like this:

SELECT id, field1, field2, field3
INTO OUTFILE '/tmp/data.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM data_table
WHERE field1 = 0;

I was quite surprised to find myself staring at:

ERROR 1045 (28000): Access denied for user ‘db_user’@’localhost’ (using password: YES)

My database user definitely had full access to the database.  I definitely could see the results of the query before the redirect to the file.  And I definitely had enough permissions to create files in /tmp directory.  And on top of that, I’m sure I used MySQL export functionality a gadzillion times and it always worked without any problems.   What’s wrong this time?

A quick search around got me to this Stack Overflow question.  Apparently, database user has to be given a FILE privilege, which is global (not per-database).   Here is what I did to solve the problem (you’ll need to use MySQL root user of course):

USE mysql;
UPDATE user SET File_priv = 'Y' WHERE User = 'db_user';
FLUSH PRIVILEGES;

I think that it worked for me before was because I exported as root, who does have this permission set to ‘Y’.

On scalability of MySQL

Anyone who says that MySQL is not scalable has no idea.  Facebook is one of the examples for a large deployment of MySQL:

How big is Facebook’s Internet infrastructure? Facebook VP of Technology Jeff Rothschild provided some details in a panel at the recent MySQL user conference. Rothschild says Facebook is now running 10,000 servers, including 1,800 MySQL servers that are overseen by just two database administrators.

Facebook recently surpassed 500,000,000 users – half a billion!

Understanding WordPress database in 10 minutes

Almost two years ago I wrote a blog post titled “A look inside the WordPress database“.  While a lot of people enjoyed it (and, apparently still do, even though it’s a bit outdated), I think it could be greatly simplified.  And it will probably take you less time to understand WordPress database now than it would take you to read through that blog post back then.

For the simplified approach to the WordPress database, you’ll need three things and three things only.

  1. Database description – WordPress Codex page.  The main thing to pay attention to on that page is the database diagram.  If you can’t grasp it all at once – DON’T PANIC – there is a description of each table further down the same page.  But trust me, you don’t need that just yet.
  2. Fresh installation of WordPress.  And by fresh I mean the one that you just did, complete with database setup and all, but which you haven’t touched yet – no options changed, no posts or pages published, no comments moderated.  Virgin WordPress.
  3. MySQL client.  And you can use whatever suits your fancy.  Command line, PHPMyAdmin, MySQL Query Browser, or anything else.  The more comfortable you are with it, the better.

Have you got everything?  OK.  Now you’ll just need to use that MySQL client to see the tables in your fresh WordPress installation, the structure of those tables, and the content that they have.  If your memory fails you, here is a quick guide to MySQL.

List all tables in the database:

mysql> show tables;
+-----------------------+
| Tables_in_xxxxxxx_com |
+-----------------------+
| wp_comments           |
| wp_links              |
| wp_options            |
| wp_postmeta           |
| wp_posts              |
| wp_term_relationships |
| wp_term_taxonomy      |
| wp_terms              |
| wp_usermeta           |
| wp_users              |
+-----------------------+
10 rows in set (0.00 sec)

Show structure of the table:

mysql> explain wp_postmeta;
+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| meta_id    | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| post_id    | bigint(20) unsigned | NO   | MUL | 0       |                |
| meta_key   | varchar(255)        | YES  | MUL | NULL    |                |
| meta_value | longtext            | YES  |     | NULL    |                |
+------------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

Select all records from the table:

mysql> select * from wp_terms;
+---------+---------------+---------------+------------+
| term_id | name          | slug          | term_group |
+---------+---------------+---------------+------------+
|       1 | Uncategorized | uncategorized |          0 |
|       2 | Blogroll      | blogroll      |          0 |
+---------+---------------+---------------+------------+
2 rows in set (0.00 sec)

Select a single record with a lot of fields for a close-up look:

mysql> select * from wp_links where link_id = 1\G
*************************** 1. row ***************************
         link_id: 1
        link_url: http://codex.wordpress.org/
       link_name: Documentation
      link_image:
     link_target:
link_description:
    link_visible: Y
      link_owner: 1
     link_rating: 0
    link_updated: 0000-00-00 00:00:00
        link_rel:
      link_notes:
        link_rss:
1 row in set (0.00 sec)

While you have just installed a brand new WordPress, you’ll notice that there is already a bunch of data in the database. That data consists of your configuration options, one post, one page, one comment, and a few blogroll links. You might not need all those for your other blogs, but now all of that plays an important role – it shows you were things are and how they are related.

Look into every table. Then change something. Edit a post or add another comment. Tag something. But don’t do everything at once – one step at a time. Check the data in the database after each step, and see how is it different from what you had there before you made a step.

10 minutes later you’ll know everything there is to know about WordPress database structure. Enjoy!