CakePHP 3 - MySQL 'BIGINT' field not processed correctly in entity

Question

I have a database where I store MAC addresses. There is quite some discussion about how to store a MAC address in a MySQL database, I decided to store it as an unsigned BIGINT to save a few bytes per row and achieve faster searches.

This would work well, except that when I retrieve an entity from the table the mac address in the entity is limited to 2147483647, e.i. the PHP MAX_INT value for a 32 bit system. So the mac address will not be correct if it exceeds 2147483647 (which will happen for 99% of addresses).

It seems that somewhere in the process from database to entity the value is parsed as int, which breaks the values.

Is there some way of avoiding this? Would it be possible to force the entity property to be a string instead of int?

Similar question that didn't receive an answer: Cakephp 3 Bigint issue - Not receiving the same contact number
It seems the PHP deployment on my Windows 10 laptop runs in 32 bit. A solution would trying to make my PHP + Apache (XAMPP) run in 64 bit, but I would rather have a general solution.

ndm · Accepted Answer · 2019-07-31T09:31:11.257

1

The conversion happens in the type layer (\Cake\Database\Type), it's triggered by the query object when it fetches rows.

By default big integers are mapped to the \Cake\Database\Type\IntegerType. If you need to run this on a 32-bit system, then you could for example remap the biginteger type to \Cake\Database\Type\StringType. In your config/bootstrap.php:

Type::map('biginteger', \Cake\Database\Type\StringType::class);

See also

Cookbook > Database Access & ORM > Database Basics > Adding Custom Types

edited Jul 31 '19 at 09:31

answered Jul 30 '19 at 12:33

ndm

59,784
9
71
110

That's very cool, will try it soon. To your knowledge, would there be side effects to re-mapping the `biginteger`? E.g. is it used somewhere internally that could break? – Roberto Jul 30 '19 at 12:42
1

@Roberto One side effect would of course be that you're passing strings to the database, so you'll end up with string literals in your queries, not integers. For the most part the SQL optimizer should be able to handle that fine though, but if you're not validating your data properly, then non-numeric strings could make it into your queries. There might be more that doesn't come to my mind right now, but I don't think anything should really break, at least not in the core, plugins could be a different story. – ndm Jul 30 '19 at 12:53
Tested this and seems to work well enough. As long as I remember to use strings when e.g. setting new values instead of numbers. – Roberto Jul 31 '19 at 09:31

CakePHP 3 - MySQL 'BIGINT' field not processed correctly in entity

1 Answers1