Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 8

Displaying posts with tag: MySQL Tips (reset)

MySQL Random Data Selection
+0 Vote Up -0Vote Down

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
+0 Vote Up -0Vote Down

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
+0 Vote Up -0Vote Down

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, 13-APR-09 will become …

  [Read more...]
On Triggers, Stored Procedures, and Call Stacks
+0 Vote Up -0Vote Down

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 …
  [Read more...]
Followup: On IPs, hostnames, and MySQL
+0 Vote Up -0Vote Down

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 …

  [Read more...]
On 1U cases, PCI risers, and LSI MegaRAID
+0 Vote Up -0Vote Down

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: …
  [Read more...]
On disk performance and MySQL replication
+0 Vote Up -0Vote Down

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 …

  [Read more...]
On IPs, hostnames, and MySQL
+0 Vote Up -0Vote Down

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, jcole@foo.example.bar could be a valid username and hostname combination. As far as I know, this is a historical …

  [Read more...]
Showing entries 1 to 8

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.