Category Archives: MySQL

MySQL Exclusive Row Relationships

This post will explore how to find exclusive row relationships — rows that may be grouped together by some non-unique id that identifies some kind of grouping of those rows. For example, let’s say that we are managing a team of players, and that each team has to be unique. In other words, players A and B form Team 1, but players A,B,C form Team 2. Searching for existing teams with players A and B should only return Team 1, searching for teams with players A,B,C should return only Team 2, and searching for B,C returns an empty set. How to do this in SQL?

Continue reading →

Resetting MySQL Passwords

I downloaded a Ubuntu 8.10 VMPlanet.net image with MySQL server, but the root password was set to something other than the standard vmplanet.net password. Logging in without a password or username didn’t give me enough permissions to set my own users and change passwords. Here is the solution for this problem (thanks Keystone IT Tech!). Continue reading →

MySQL Triggers

I came across an interesting problem: I needed to create priorities for records in a database so that they would be able to be displayed in a particular order. Moreover, I needed to find a way to reorder the priorities on the fly. My first instinct was for each record to have its own priority (PRI) column. But what would happen on an insert, if, say, the newly inserted item’s priority is somewhere in the middle? I would have to reorder the list. My table looks like this:

Table TEST ID VARCHAR(10) PRI INT(5)

I have never used triggers before, but from what I understood, they would have been the perfect solution.

Continue reading →