I came across this problem this morning when copying a table structure and creating a backup table. The reason I received it was because I had not not also renamed my foreign keys constraints.

Using the fk_<table_name>_key pattern fixed the problem and the table was created without error.

Nick

Stuck? Try the following command:

mysqldump --lock-tables=false -u******** -p {database_name} > {database_name}.sql

Nick

I have been meaning to actually look at the storage differences between signed and unsigned for a few months now, and I have finally remembered to do it! A quick Google search returned just the information I was looking for, so I thought that I would repost the blurb that was worthwhile.

An UNSIGNED INT can go from 0 to 4294967295 (aprox 4 billion). A SIGNED INT , on the other hand, starts at -2147483648 and goes to 2147483648 (aprox 2 billion).

source: http://www.verysimple.com/blog/2006/10/22/mysql-data-type-optimization-tips/

Nick

Yep, you read the title correctly. This problem presented itself when I needed a quick and dirty way to update an auto_increment [a_i] field in a database. I wanted to move the a_i field up by n, where a_i > x. The query looked like this:

UPDATE table_name SET id = (id + n) WHERE id > x ORDER BY id DESC

To see why one must do it this way, try to do the query without the ORDER BY.

Nick