Join major names among MySQL customers by learning to power dynamic database-driven websites with MySQL & PHP.

With the MySQL and PHP: Developing Dynamic Web Applications course, in 4 days, you learn how to develop applications in PHP and how to use MySQL efficiently for those applications! Through a hands-on approach, this instructor-led course helps you improve your PHP skills and combine them with time-proven database management techniques to create best-of-breed web applications that are efficient, solid and secure.

You can currently take this course as a:

• Live Virtual Class (LVC): There are a number events on the schedule to suit different timezones in January 2013 and March 2013. With an LVC, you get to follow this live instructor-led class from your own desk - so no travel expense or inconvenience.
• In-Class Event: Travel to an education center to attend this class. Here are some …

SkySQL’s Newest Training Course, “MySQL Database for the Cloud”, Helps DBAs and Developers Find Success in the Cloud  

Increasingly, more organisations are moving their database applications to cloud to take advantage of its superior agility and faster deployment over legacy systems.

read more

Don't you hate disclaimers? I do, but before I do anything else, I must ask that you don't use the techniques below unless you are emailing responsibly.

Today I needed to pull email addresses for people who had signed up to a thing out of MySQL and into MailChimp so that I could actually email them about the thing. MySQL actually has a very cute feature for exporting the results of an SQL query as a CSV file, which I had to look up to remember how to do it. It goes something like this:

My original query*

select u.username, u.email, a.account_name
from users u
inner join accounts a on a.user_id = u.id
where a.status_id = 2;

To write this to a file, we use MySQL's SELECT ... INTO OUTFILE feature, so my query suddenly looks like this:

select u.username, u.email, a.account_name
into outfile '/tmp/users-extract.csv'
from users u
inner join …

Don’t you hate disclaimers? I do, but before I do anything else, I must ask that you don’t use the techniques below unless you are emailing responsibly.

Today I needed to pull email addresses for people who had signed up to a thing out of MySQL and into MailChimp so that I could actually email them about the thing. MySQL actually has a very cute feature for exporting the results of an SQL query as a CSV file, which I had to look up to remember how to do it. It goes something like this:

My original query*

select u.username, u.email, a.account_name
from users u
inner join accounts a on a.user_id = u.id
where a.status_id = 2;

To write this to a file, we use MySQL’s SELECT ... INTO OUTFILE feature, so my query suddenly looks like this:

select u.username, u.email, a.account_name
into outfile '/tmp/users-extract.csv'
from users u
inner …

I previously published stored programs to help implement a (more) comprehensive password policy in MySQL 5.6, building on the password complexity plugin now available in MySQL 5.6.  This proof-of-concept has been expanded recently, and the updated package is available here.  There’s a few notable changes to the earlier version:

Moved all created objects out of mysql system database

The mysql database is meant for system tables, and I try to keep everything not directly managed by the MySQL server out of that database.  The initial proof-of-concept implementation violated this principal – the update corrects this by creating and using a new password_policy database …

Better performance is important in everything, not just MySQL. I don’t want to wait for my text editor to open a file or perform syntax highlighting. I don’t want to wait for my version control system to compute diffs or update my copy of the code with other peoples’ changes. I don’t want to wait for my code to compile. I don’t want to wait, period. Two tools I have enjoyed recently are Git and the Go language.

(Note: when I’m talking about MySQL I usually assume InnoDB storage engine. Any other case I explicitly tell this is MyISAM or Memory etc.)

I’ve heared an interesting aproach of using Master-slave replication in MySQL.

Thesis

So the theory was that since updates by primary keys are fast and by secondary keys are slow the slave has to be queried for the primary key and then run the updates by the fetched primary keys. To make this in context and more understandable:

Original query

UPDATE table_for_test SET value_to_change = 123 WHERE cond_column_1 = 987 AND cond_column_2 > 765;

This query get splitted to two different query. First query has to be run on the slave to fetch the primary keys:

SELECT pr_id_col FROM table_for_test WHERE cond_column_1 = 987 AND cond_column_2 > 765;

When we have the values we can go to the master and update the necessary records. …

I’ve been looking at the new ALTER USER … PASSWORD EXPIRE command as I try to implement a comprehensive password policy for MySQL 5.6.  There’s a few aspects of this feature that I found interesting, and thought others might benefit from what I’ve learned.  Here’s a quick summary:

You can use ALTER USER … PASSWORD EXPIRE in prepared statements as of 5.6.8-rc

This is important because there’s no other way to dynamically bind ALTER USER statements to a user name and host, which is necessary if you are trying to automate anything related to password policies.  This wasn’t the case with earlier 5.6 releases, but was fixed in 5.6.8-rc:

mysql> SELECT password_expired
-> FROM mysql.user
-> WHERE user = 'root' AND host = 'localhost';
+------------------+
| password_expired |
+------------------+
| N                |
+------------------+
1 row in set (0.00 sec)

mysql> SET @sql = 'ALTER USER …

You can do things backwards in MySQL. You can receive a result before sending the query.

1) Pop quiz: What will you do if your client pops up a message like this:

+————+
| sleep(-25) |
+————+
| 0 |
+————+

2) Hint: read MySQL documentation

3) Answer: wait exactly 25 seconds (within the accuracy of a microsecond) and execute “select sleep(-25);”. (now .. if an alias is used for the result it gets a little difficult to know for how long you should wait )

Obviously the argument gets truncated to “0″ when it is outside valid range. But MySQL docs does not tell what the valid range for the argument to SLEEP() is and no error or warning is returned if truncation happens. It is reported …

Well, on noticing that the new version of Slackware, 14.0, is out now, I read the release notes and noticed among other things, the fact that it is now using the version 3 Linux kernels now, and also that Firefox is now upgraded from version 3 to version 15. Since Facebook has been on my case for some time regarding the inferiorness of my web browser, and also the fact that I have been running the 32-bit version of Slackware on a 64-bit machine all this time, I decided it was time to upgrade, and I was going to do it in style.

Okay. First off, the 64-bit version of Slackware is only available on a DVD distribution. I'm sure I could have hacked my way around this if I had to, but anyway, the first step regardless was to download the ISO-9660 image of the installation DVD. I tried a straightforward HTTP download, only to have it crash a quarter of the way along. So I went with the torrent instead. I downloaded the torrent file, brought …