MySQL limiting INT(64) to INT(32)

[Seufert]

Binary package hint: mysql-server-5.0

MySQL on 64bit Ubuntu 9.04 rel 9.04 'server', is capping any value above what can be stored into a 32bit to the max for a 32bit in, even when storing into a 64bit field.

see mysql code:

mysql> CREATE TABLE `access` (
  `accessid` int(64) unsigned NOT NULL auto_increment,
  `timestamp` int(64) unsigned NOT NULL,
  `resptime` int(32) unsigned NOT NULL,
  `clientip` char(16) NOT NULL,
) ENGINE=MyISAM DEFAULT CHARSET=latin1

mysql> insert into access SET timestamp = 1248755492218;
Query OK, 1 row affected, 10 warnings (0.00 sec)

mysql> select timestamp from access;
+------------+
| timestamp |
+------------+
| 4294967295 |
+------------+
1 rows in set (0.00 sec)

As you can see, something is not right here. I get the same results using php, rather than the mysql command line tool.

mysql-server-5.0 package version is 5.1.30really5.0.75-0ubuntu10.2.

[Seufert (seufert-gmail)]

I believe that its not a problem with mysql. It would appear that the INT type can only hold 32bit int's on any platform. A BIGINT is required for 64bit Integer values.