Commands out of sync; you can’t run this command now

Sometimes the solutions to what seems to be complex problems are easy and often something completely unrelated.

I woke up to one of my websites not working although I did not touch the code for weeks. First thing that I got was Firefox telling me this :

The page isn’t redirecting properly

Firefox has detected that the server is redirecting the request for this address in a way that will never complete

After ruling out .htaccess and other problems I came to conclusion that it has something to do with a database querying.

So I went to phpmyadmin and tried to manually run the query. To my surprise I got another problem:

Commands out of sync; you can’t run this command now

Great, so suddenly the nested MySQL command I have been using for years stopped working. If you Google for this you will get tons of sites telling you that MySQL has problem with stored procedures (I’m not even using them), that we need to switch to mysqli or PDO extensions, that we are calling client functions in the wrong order etc. However this did not explain why did my code suddenly stop working despite me not touching anything. Maybe database got too large?

To troubleshoot this I wanted to make a copy of a specific table in question and lo and behold – when I copied it via phpmyadmin I got error saying something like “This table is corrupt so it cant be copied”. I checked the table and there it was – a corrupt table! I ran a repair table command, refreshed a site and voila, it worked!

So as you can see the solution was something completely unrelated to error messages and what most websites suggested it was. That does not mean that most people with this errors have the same problem like I did. But it is a reminder that sometimes we should try a simple solutions like checking if database or table became corrupt before spending few hours into troubleshooting something that is working fine anyway :)

.

Database table relationships: One-to-One vs. One-to-Many vs. Many-to-Many

A lot of people have problem with relational databases and how to define relationships between two tables.

This is a guest post by Dave from saturnasoftware.com where he teaches us how to easily recognize the correct relationship:

General Rule and One-to-Many Relationship

Back when I taught database design at university I had a LOT of students who struggled with One-to-One vs. One-to-Many vs. Many-to-Many. Part of the problem is that English can be a bit confusing with plural forms: Person/People, Mouse/Mice, etc. Another part of the problem is that verbs do not always have exact inverses so we end up saying ‘Queen Elizabeth RULES England’ but then have to say the rather awkward ‘England IS RULED BY Queen Elizabeth’. There are so many slight variations in English that it gets confusing. Here’s a very simple way to ALWAYS get it right:

If you want to figure out the relationship between X and Y, complete the follow two statements:

  • Each X GOES WITH, at most, ____ Y
  • Each Y GOES WITH, at most, ____ X

Let’s assume we have Person and Country tables. For the sake of this example we assume that every person lives in only one country. So, for this specific case, we have the context of ‘where a person lives’:

  • Each Person GOES WITH, at most, ONE Country
  • Each Country GOES WITH, at most, MANY Person(s)

Now, keep the last two words of each statement…

  • We have [ONE Country] to [MANY Person]

A One-to-Many relationship is always “owned” by the MANY side, which is to say that the Foreign Key goes in the table for the MANY side (we put the Country identifier in the Person table).

Many-to-Many Relationship

A Many-to-Many relationship (use the same two statements to figure this out) is special; it requires a third table. Each of the two related items has a One-to-Many relationship with this new third table, so the third table contains TWO Foreign Keys, one for each related table.

So, in the context of ‘places a person has visited’, we have:

  • Each Person GOES WITH, at most, MANY Country(ies)
  • Each Country GOES WITH, at most, MANY Person(s)

Incidentally, this ‘third table’ is conventionally named for the other two tables, in alphabetical order: CountryPerson or Country_Person, but can be named anything (e.g. Visited). Naming it after the other two tables makes it easy to figure out what the purpose of the table is; alphabetical is just arbitrary.

One-to-One Relationship

A One-to-One relationship (again, same two statements) is super easy – it does not matter which of the two tables gets the Foreign Key. Almost always one or the other of the items logically “owns” the other one… but it technically does not matter.

The most common kind of One-to-One relationship, in my experience, is an “IS-A” relationship – like “A Customer IS-A User” or “An iPhone IS-A Mobile Device.” A trick that I use for IS-A type relationships is to use the same Primary Key(identifier) in both tables. That way either Primary Key can serve as the Foreign Key to the other table.

But One-to-One relationships are not ALWAYS IS-A relationships… for example…

In the context of “place that this person rules (as in King/Queen)”, we have:

  • Each Person GOES WITH, at most, ONE Country
  • Each Country GOES WITH, at most, ONE Person

Here we could put the Person identifier in the Country table OR we could put the Country Identifier in the Person table. Since there will likely be fewer Countries than Persons, I’d put the Person identifier in the Country table because it will result in less data to store/handle.

There you have it, the (very) short version of my ‘what kind of relationship is this?’ lecture.
Hope it helps someone.