Showing entries 1 to 8
Displaying posts with tag: MySQL Tips (reset)
MySQL Random Data Selection

Some days ago I was working in a vocabulary game and dictionary. The dictionary contains 1,10,000 words and meanings. I developed a vocabulary game where I had to randomly choose 10 words out of 1,10,000 dataset. Here I’m describing the possible solutions for retrieving 10 random words from 1,10,000…  Read Full Article

Obtaining values from UPDATE statements

Today, I would like to post one of my more favorite tricks. This is a useful trick when you want to obtain a value of a row you have updated. For instance, you have a blogs table and you’ve updated its timestamp and want to know the ID of the table you updated. You can emply this:

First, make sure to set the user-defined variable to NULL:

select @v_id = NULL;

UPDATE blogs SET timestampcol = ?, id = (@v_id := id) WHERE owner = ’someone’ AND title = ‘Hejsan Kaj!’;

Now, you can run:

SELECT @v_id;

And @v_id will contain the id of the row updated. If @v_id is NULL, then that means the row was not updated!

Note: This syntax is for MySQL. Other DBs mileage may vary

Oracle to MySQL date type considerations

I’m in the process of migrating remaining functionality of from using Oracle to using MySQL. There were some assumptions I had made about various data types, particularly dates. One thing I discovered while converting one piece of code is the Oracle function to_char(). I have a table:

SQL> select member_name, change_time from access_changes where member_name = 'phptester10' order by change_time desc;

-------------------- ---------
phptester10          13-APR-09
phptester10          13-APR-09

So, I thought “ok, this is just going to be a ‘date’ type with a different format. For instance, 13-APR-09 will become 2009-04-13. But then I stumbled upon a query in a piece of code that does a sort on dates from this access_changes table:

SQL> select member_name, to_char(change_time, 'YYYY-MM-DD HH24:mi:SS') from access_changes where member_name = …
[Read more]
On Triggers, Stored Procedures, and Call Stacks

If you’re a frequent reader, you might have noticed that I’m on a roll contributing MySQL patches by now… there are many more to come. This is part of the reason that I founded Proven Scaling — to be able to spend my time solving interesting problems and making MySQL better. So what about triggers, stored procedures, and call stacks?

I’ve written a patch that implements three new functions in MySQL 5.01:

  • CALLER(level) — Returns the SQL statement in the call stack at level, where 0 is the level containing the call to CALLER() itself (which is nearly useless), and 1 and above are any stored procedure or trigger calls that got us here.
  • CALLER_DEPTH() — Returns the current depth of the call stack, not counting 0. …
[Read more]
Followup: On IPs, hostnames, and MySQL

Back in April, I wrote On IPs, hostnames, and MySQL, which described the (sometimes surprising) ways in which MySQL deals with IP addresses, hostnames, and privileges, as well as some basics about the host cache itself. In a footnote to that post, I mentioned a patch I had written against MySQL 4.1 to give some more visibility into the host cache.

Over the past two days, I have worked on porting that patch to MySQL 5.01, 2, and making some fairly large improvements to it. The patch implements a few things:

  1. Configurable Size — Without the patch, the size of the cache is fixed at 128 entries, and can only be changed by changing a #define and recompiling. You may now tune the size of the host cache using SET GLOBAL …
[Read more]
On 1U cases, PCI risers, and LSI MegaRAID

I’ve been working with one Proven Scaling customer that has had some interesting issues recently, involving InnoDB corruption, resulting in messages similar to these:

InnoDB: Page checksum 3156980109, prior-to-4.0.14-form checksum 577557610
InnoDB: stored checksum 741279449, prior-to-4.0.14-form stored checksum 577557610
InnoDB: Page lsn 0 2323869442, low 4 bytes of lsn at page end 2323869442
InnoDB: Page number (if stored to page already) 195716,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
InnoDB: Page may be an index page where index id is 0 2831
InnoDB: (index PRIMARY of table db/table)
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 195716.

The problem was encountered when testing hardware for a move from software RAID to hardware RAID using …

[Read more]
On disk performance and MySQL replication

If you’re not using replication, the only thing you have to worry about is MyISAM and InnoDB performance. There are a lot of things you can easily do to get more (or less!) performance out of either of them. So, you get your system tuned and handling 1500 updates per second, easy as pie. You’ve got a single IDE, SATA, or SCSI disk, or maybe two using software RAID.

Then, due to natural progression, needed scalability, or catastrophic failure, you realize that you should probably be using replication, so that you can have more than one of these systems working at once.

The first step in setting up replication is normally to turn on binary logging (by enabling log-bin) on the future master. This ensures that every change to the database is logged so that it can be sent to your future slaves and executed there. Here, though, you find your first obstacle.

You enable binary logging, and all of the sudden, performance goes …

[Read more]
On IPs, hostnames, and MySQL

This is the first official post in my new category: MySQL Tips. Feel free to subscribe to the category-specific RSS feed, if you prefer.

First, for a bit of background information…

In MySQL, access is always granted based on the combination of username and hostname (or IP address, in some cases), and password—for example, could be a valid username and hostname combination. As far as I know, this is a historical thing—MySQL authentication is based on username and hostname because it has always been that way.

In order to verify the “network credentials” of the connecting client, MySQL uses a “double reverse-DNS lookup” on the IP of the incoming connection. In short, MySQL first finds out …

[Read more]
Showing entries 1 to 8