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
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:
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
I’m in the process of migrating remaining functionality of Tripod.com 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; MEMBER_NAME CHANGE_TI -------------------- --------- 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,
2009-04-13. But then I stumbled upon a query
in a piece of code that does a sort on dates from this
SQL> select member_name, to_char(change_time, 'YYYY-MM-DD HH24:mi:SS') from access_changes where member_name = …[Read more]
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. …
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:
- 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 …
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]
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]
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, firstname.lastname@example.org 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]