Focus on IT Recommend

Home > CakePHP 3 Tree Behavior - numeric value out of range on save, delete, or move functions

CakePHP 3 Tree Behavior - numeric value out of range on save, delete, or move functions



I'm working with CakePHP 3.0's Tree Behavior and get an interesting issue when I'm trying to delete nodes. There are two types of nodes under a team - internal and external. The internal nodes are teams in the tree structure beneath me that I have complete access over; the external nodes are teams underneath me that have their own teams they've fleshed out, and as such I don't have permission to alter them (I only have permission to remove the entire top-level owner, but not modify the actual structure of their team).

So let's say I have a structure like this:

        /  |  \
     * o   o   x            o = internal
      /                     x = external
     o                      * = node I'm trying to delete
    / \
   o   x

I have a delete function I've written to delete team nodes. If I delete the node with an asterisk by it, I expect it to delete every node underneath it. When I hit an external node (the x at the bottom) it should instead change the parent_id to null, since I don't have permission to delete somebody else's external team.

My logic is recursive and basically calls itself for every child under with logic similar to this:

if ($team->external === 1) {
    $team->parent_id = null;
} else {

I won't write out the whole function for the sake of brevity, but this functionality continues until all nodes have been deleted. Unfortunately, I get this error when I try and run the function:

"SQLSTATE[22003]: Numeric value out of range: 1690 BIGINT UNSIGNED value is out of range in '((`my_application`.`teams`.`lft` + 1182) * -(1))'"

So this looks like an issue with the lft or rght values (perhaps) and I'm confused as to why it's getting multiplied by a -1...either way, I'm not exactly sure what the case is here.

I get the same issue when I'm running other functions, like a move command. If I want to move a user underneath a different node, I might do something like this in my controller:

$team->parent_id = $this->request->data['id']; // let's say it's '21'

The same issue happens here where I get a 'numeric value out of range' issue. I've run $this->recover(); on the table a few times just to make sure the table's lft and rght values were all correct and the issue still occurs.

Does anybody have any idea?

cakephp tree behavior cakephp-3.0
  this question
asked May 18 '15 at 21:27 Isaac Askew 711 10 25


1 Answers


Well, this is an implementation gotcha for the tree behavior in its current form. In order to speed up some operations, it relies on being able to set negative values to some of the nodes. Therefore, having an UNSIGNED column will not work correctly.

The easiest solution right now is to remove the UNSIGNED flag in the lft and rght columns.

  this answer
answered May 19 '15 at 8:54 José Lorenzo 7,637 18 22      Perfect. I made the changes and I posted some suggested edits to the documentation so other users don't run into the same issue. –  Isaac Askew May 19 '15 at 22:42


------splitte line----------------------------