Hibernate + MySQL + Mac = Foreign Key Nightmares. A painless solution to a painful problem

tl;dr summary: Avoid using mixed case table names when using MySQL on a Mac.  Use lowercase underscore separated table names instead.

I was using Hibernate to map my Java classes to MySQL tables and columns.  For most classes, inserts worked perfectly.  For other classes, I’d consistently get errors like

- SQL Error: 1452, SQLState: 23000
- Cannot add or update a child row: a foreign key constraint fails

By running the command

show engine innodb status

in my mysql window, I found following clue:

110520 14:26:09 Transaction:
TRANSACTION 85B76, ACTIVE 0 sec, OS thread id 4530606080 inserting
mysql tables in use 1, locked 1
1 lock struct(s), heap size 376, 0 row lock(s)
MySQL thread id 3, query id 2175 localhost root update
insert into TableName (pk_Pdu) values (10)
Foreign key constraint fails for table `myproj`.`tablename`:
,
  CONSTRAINT `FKEC7DE11817B41BEB` FOREIGN KEY (`pk_Pdu`) REFERENCES `ParentClass` (`pk_Pdu`)
Trying to add to index `PRIMARY` tuple:
DATA TUPLE: 3 fields;
 0: len 8; hex 800000000000000a; asc         ;;
 1: len 6; hex 000000085b76; asc     [v;;
 2: len 7; hex 00000000000000; asc        ;;

But the parent table `myproj`.`ParentClass`
or its .ibd file does not currently exist!

I knew for a fact the table existed; I was able to query it and it showed up fine. Something else must be going on.

I finally stumbled onto the answer by way of a StackOverflow post:

However, I did rename the tables all to lowercase and that did make a difference. A quick search indicates I should maybe setting lower_case_table_names = 1 since I am using InnoDB. On Mac OS/X it is 2 by default (and I failed to mention I’m using a new box which may be why it isn’t working locally).

Sure enough, as soon as I renamed the table names to be all lowercase underscore separated, things worked perfectly. The default naming strategy in Hibernate names the tables in exactly the same way as the class names (e.g. in CamelCase as opposed to lower_case_underscore_separated). Fortunately the designers saw fit to make this naming convention overridable. All I had to do was add one line of code to fix my entire problem:


Configuration config = new Configuration();
// Name tables with lowercase_underscore_separated
config.setNamingStrategy(new ImprovedNamingStrategy());

Thanks to this blog post on ImprovedNamingStrategy for pointing the way. This post also helped me find the problem.

Conclusion

If you’re using Hibernate and a MySQL database running on MacOSX, make sure that your table names are all in lowercase.  This can be accomplished by using the ImprovedNamingStrategy class when configuring Hibernate.

This experience taught me a valuable lesson.  The first is, sometimes a problem can be caused by something that’s not directly your fault per se (i.e. I hadn’t incorrectly structured my Hibernate annotations, as I initially suspected), but rather due some quirk in the operating system or external tools you’re using.  The second is it’s crucial for cross platform libraries like Hibernate to provide the hooks for you to be able to swap out default behavior, precisely to be able to work around problems like these.  Thankfully Hibernate had built in just the hooks I needed to solve the problem.

Datetimes in Python – gotchas and workarounds

I’ve written previously about working with dates in Java; as I mentioned there it’s very easy to get dates/times incorrect. I feel like I have a fairly good handle on how things work in Java, but today I was faced with learning how to deal with dates/times in Python. It wasn’t an altogether pleasant experience, but I’m going to show what I learned, so hopefully this is of use to you.

The task I was trying to accomplish was to convert between Unix timestamps (seconds/milliseconds since the Epoch) and more user friendly data objects. Creating a datetime is easy:

>>> from datetime import *
>>> datetime.now()
datetime.datetime(2011, 4, 5, 19, 36, 18, 894325)
>>> datetime(2004, 1, 24)
datetime.datetime(2004, 1, 24, 0, 0)

One gotcha to note is that both the month and day fields are 1 based (1 <= month <= 12, 1 <= day <= number of days in the given month and year), whereas in Java, the month field is 0 indexed.

By default, these datetime objects will use a naïve time zone understanding that ignores offsets from UTC/day light savings time. To fix this, you need to implement your own subclass of tzinfo. It’s kind of unfortunate that they don’t make this easier. Here is an example implementation representing the UTC timezone from the previously linked page:

from datetime import tzinfo, timedelta, datetime

ZERO = timedelta(0)
HOUR = timedelta(hours=1)

class UTC(tzinfo):

    def utcoffset(self, dt):
        return ZERO

    def tzname(self, dt):
        return "UTC"

    def dst(self, dt):
        return ZERO

OK that’s not the end of the world. Now assuming we’ve created out datetime object correctly, how do we retrieve its corresponding Unix style timestamp? Let’s look at the available methods.

>>> [x for x in dir(datetime) if not x.startswith("__")]
["astimezone", "combine", "ctime", "date", "day", "dst", "fromordinal", "fromtimestamp", "hour", "isocalendar", "isoformat", "isoweekday", "max", "microsecond", 
"min", "minute", "month", "now", "replace", "resolution", "second", "strftime", "strptime", "time", "timetuple", "timetz", "today", "toordinal", "tzinfo", "tznam
e", "utcfromtimestamp", "utcnow", "utcoffset", "utctimetuple", "weekday", "year"]

Well, there’s a bunch of methods that convert from a timestamp to a datetime object. But going back the other direction is a little harder. After digging, I found a way to do so:

>>> from datetime import datetime
>>> from time import mktime
>>> dt = datetime(2008, 5, 1, 13, 35, 41, 567777)
>>> seconds = mktime(dt.timetuple())
>>> seconds += (dt.microsecond / 1000000.0)
>>> seconds
1209663341.5677769
>>> 
>>> dt2 = datetime.fromtimestamp(seconds)
>>> dt == dt2
True

Well, there you have it. To convert from a unix timestamp to a datetime object, use datetime.fromtimestamp. To convert the other direction, use time.mktime(datetime_instance.timetuple()). I wish that the library authors had seen fit to maintain symmetry (i.e. datetime should implement a totimestamp method), but fortunately there is an easy workaround. The last thing to note if you’re used to Java is that the timestamps in Python measure seconds from the epoch, as opposed to Java which deals in milliseconds from the epoch.

ack – Better than grep?

I stumbled onto a really nice command line tool named ack while reading a StackOverflow question yesterday.  Living at the domain betterthangrep.com/, it purports to .. be better than grep.  Or, as they put it

ack is a tool like grep, designed for programmers with large trees of heterogeneous source code

I’ve written previously about how to combine find and grep, and really, ack exists to obviate the use of find and grep.  It ignores commonly ignored directories by default (e.g. all those .svn metadata folders that SVN insists on creating), and with a simple command line flag you can tell ack what sort of files you want searched.  Furthermore, because it recurses by default, you don’t need to use the find command to traverse the tree.

Using the todo example, a basic way of searching for the TODOs in all of our java files is to use the command

find . -name "*.java" -exec grep -i -n TODO {} \;

In ack, this is accomplished much easier:

ack -i --java TODO

Furthermore, the matching results are highlighted right away, making it extremely apparent where the matches occur.

I’m going to start using this at work and see if it can replace my grep/find hackery.  Will let you know.  Very impressed so far.

 

If you want to give it a try, the easiest way to install it is with macports:

port install p5-app-ack

Excel 2008 for Mac’s CSV export bug

I ran into this at work a few weeks ago and thought I’d share.

Excel 2008’s CSV export feature is broken.  For instance, enter the following fake data into Excel:

Row	Name	Age
0	Nick	23
1	Bill	48

Save as -> CSV file

Full list of choices

When you use standard unix commands to view the output, the results are all garbled.

[Documents]$ cat Workbook1.csv
1,Bill,48[Documents]$
$ wc -l Workbook1.csv
0 Workbook1.csv

What is the issue?  The file command reveals the problem:

$ file Workbook1.csv
Workbook1.csv: ASCII text, with CR line terminators

CR stands for Carriage return, the ‘\r’ control sequence which, along with the newline character (‘\n’), is used to break up lines on Windows.  Unix OSes like Mac OS expect a single ‘\n’ new line character to terminate lines.

How can we fix this?

dos2unix.

# convert the Workbook1.csv file into a Unix appropriate file
dos2unix Workbook1.csv WithUnixLineEndings.csv

If you don’t have dos2unix on your Mac, and you don’t want to install it, you can fake it with the tr command:

tr '\15' '\n' < Workbook1.csv # remove the carriage returns, replace with a newline
Row,Name,Age
0,Nick,23
1,Bill,48

Very annoying that the Mac Excel doesn’t respect Unix line terminators.  Interestingly, I found a post that talks about ensuring that you choose a CSV file encoded for Mac, but that option seems missing from the Mac version itself.

If I’m missing something obvious, please correct me.

Bash: How to redirect standard error to standard out

Problem:

You have a program which is outputting information to standard error that you wish to search through.  When commands are chained together in Unix via the pipe operator, standard out is connected to standard in.  Thus you cannot easily search the contents of the standard error.  How can you find what you’re looking for?

Solution

The first solution is to save the standard error as a file, and search through the file.

command_producing_standard_error 2> stderr.txt; grep "search string" stderr.txt; rm stderr.txt

This works but you have to remember to remove the text file that’s created in the process.

A better solution, and one that allows you to use the standard error in an existing pipeline is to instead redirect standard error to standard out.

command_producing_standard_error 2>&1 | grep "search string"

Recall that 2 refers to standard error and 1 refers to standard out; those familiar with C/C++ should recognize ‘&’ as the address operator, and it serves a similar role here.  After this command, both the standard out and standard error are in one stream, standard out, and can be connected via the pipe (|) symbol to other programs, such as grep.

This tip is modified from information found in the Bash Cookbook, in the recipe “Saving Output When Redirect Doesn’t Seem To Work”.  Additional solutions and discussion can be found on unix.stackexchange.com.

Quotes, quotes, quotes: A primer for the command line

In Bash programming, there are a lot of ways to get input into programs.  In particular, there are a slew of different quoting methods you should understand.  This article provides a quick reference of the difference between using No quotes, Double Quotes, Single Quotes, and Backticks

No quotes

Standard shell scripts assumes arguments are space delimited.  You can iterate over elements in this way:

 

for i in Hi how are you; do echo $i; done
Hi
how
are
you

 

This is why it is a problem to have spaces in your file names.  For instance,

 

$ ls
with spaces.txt

$ cat with spaces.txt
cat: with: No such file or directory
cat: spaces.txt: No such file or directory

 

Here I naively typed with spaces.txt thinking the cat program could handle it.  Instead, cat saw two arguments: with, and spaces.txt.  In order to handle this, you can either escape the space,

 

$ cat with\ spaces.txt

 

or use the double quotes method.  (Note that if you use tab autocompletion, the backslash escape will be added automatically)

 

Double quotes

Double quotes can be used when you want to group multiple space delimited words together as a single argument.  For instance

for i in "Hi how" "are you"; do echo $i; done
Hi how
are you

In the previous example, I could do

$ cat "with spaces.txt"

and the filename would be passed as a single unit to cat.

An important thing to note is that shell variables are expanded within double quotes.

name=Frank; echo "Hello $name"
Hello Frank

This is crucial to understand.  It also allows you to solve problems caused by having spaces in file names, especially when combined with the * globbing behavior of the shell.  For instance, let’s say we wanted to iterate over all the text files in a directory and do something to them.

$ ls
with spaces.txt   withoutspaces.txt
$ for i in *.txt; do cat $i; done
cat: with: No such file or directory
cat: spaces.txt: No such file or directory
# Surround the $i with quotes and our space problem is solved.
$ for i in *.txt; do cat "$i"; done

(Yes I know iterating over and calling cat on each argument is silly, as cat can accept a list of files (e.g. *.txt).  But it illustrates the point that commands will be confused by spaces in the name and should use double quotes to handle the problem).

Single quotes are also good when you need to embed single quotes in a string (you do not need to escape them)

$ echo "'Single quotes'"
'Single quotes'
$ echo "\"Escaped quotes\""
"Escaped quotes"

Double quotes are my default while I’m working in the terminal.

Single quotes

Single quotes act just like double quotes except that the text inside of them is interpreted literally; in other words, the shell does not attempt to do any more expansion or substitution.  For instance,

$ name=Frank; echo 'Hello $name'
Hello $name

This can save you some backslash escaping your normally would have to do.

Use it when:

 

• You need double quotes embedded in your string

$ echo '"How are you doing?", she said'
"How are you doing?", she said

• You do not need any literal single quotes in your string (it’s very difficult to get single quotes/apostrophe literals to appear in such a string)

• “The single quotes should be used when you want the text left alone.”

Back ticks

Back ticks (“, the key to the left of the 1 and above the Tab key on a standard US keyboard), allow you to substitute in the output of another command.  For instance:

$ current_dir=`pwd`
$ echo $current_dir
/Users/nicholasdunn/Desktop/Scripts
[/sourecode]

This can be combined with the double quotes, but will be treated as literal characters in the single quotes:


echo "`pwd`"
/Users/nicholasdunn/Desktop/Scripts
$ echo '`pwd`'
`pwd`

Use when:

You want to capture the results of another command, usually for purposes of assigning a variable.

Hopefully this brief tour through the different types of quotes in bash has been useful.

Ternary operator in bash

Here’s a really quick tip for bash programmers.

In languages like C++, Java, Python, and the like, there’s the concept of a ternary operator.  Basically it allows you to assign one value if a condition is true, else another.

In C/Java:

int x = valid ? 0 : 1;

In Python:

x = 0 if valid else 1

In Scala:

val x = if (valid) 0 else 1

Well, there’s no ternary operator in Bash, but there is a way to fake it.

valid=1
[ $valid ] && x=1 || x=0

Where whatever conditional you want is within the brackets.

If it’s valid, then the branch after the AND is followed, otherwise that after the OR is followed.

This is equivalent though perhaps a bit less readable then

if [ $valid ]; then x=1; else x=0; fi

So you should be aware of the construct in case you ever run into it, but it’s arguably less readable than just listing out explicitly what you’re doing.

Thanks to experts-exchange for making me aware of this little tip.

bpython – an excellent interpreter for python

If you use Python, you know that its interactive shell is a great way to test out ideas and iterate quickly.  Unfortunately, the basic interactive shell is very barebones – there is no syntax highlighting, autocompletion, or any of the features we come to expect from working in IDEs.  Fortunately if you’re on a Unix system, there is a great program called bpython which adds all of those missing features.

 

As you are typing, suggestions appear at the bottom. Press tab to take the suggestion

 

If you have easy_install, it’s the simplest thing in the world to install:

sudo easy_install bpython

I can’t recommend this product enough.  It’s free, so what’re you waiting for?

Mac OSX – copy terminal output to clipboard

Here’s a quick tip: If you want the results of some shell computation to be accessible to your clipboard (e.g. so you can paste the results into an e-mail or into some pastebin service), you can pipe the command into the `pbcopy` program.

echo "Hello world" | pbcopy
# "Hello world" is now in your clipboard

Apparently there is a way to do a similar thing on Ubuntu as well

How to remove “smart” quotes from a text file

If you’ve copied and pasted text from Microsoft Word, chances are there will be the so-called smart quotes in that text. Some programs don’t handle these characters very well. You can turn them off in Word but if you’re trying to remedy the problem after the fact, sed is your old friend.  I’ll show you how to replace these curly quotes with the traditional straight quote.

Recall that you can do global find/replace by using sed.

sed s/[”“]/'"'/g File.txt

This won’t actually change the contents of the File, but you can save the results to a new file

sed s/[”“]/'"'/g File.txt > WithoutSmartQuotes.txt

If you wish to save the files in place, overwriting the original contents, you would do

sed -i ".bk" s/[”“]/'"'/g File.txt

This tells the sed command to make the change “in place”, while backing up the original file to File.txt.bk in case anything goes wrong.

To fix the smart quotes in all the text files in a directory, do the following:

for i in *.txt; do sed -i ".bk" s/[”“]/'"'/g $i; done

At the conclusion of the command, you will have double the number of text files in the directory, due to all the backup files. When you’ve concluded that the changes are correct (do a diff File.txt File.txt.bk to see the difference), you can delete all the backup files with rm *.bk.