Possbile **Bug**: _globalOrder doesn't have a sort order

Hi Jeff,

Can you tell us a bit more about how you are going about inserting the record?

Ideally the error occurs when you don't put a value into a field that is NOT defined as nullable and doesn't have "DEFAULT" clause specified. For instance, if globalOrder is defined as NOT NULL, doesn't have the DEFAULT clause set and yet you try to stick something like NULL or no value at all into the field. MySQL is left wondering "They didn't give me a value for this field, but they say it can't be NULL and yet I don't have a default value to use instead."

But from what I understand of globalOrder it really should always have an order value specified... whether it is NOT NULL or not.

I am wondering if it is just a difference between the schema and database table itself in MySQL being out of sync or perhaps how you are inserting the record is not right and the value for globalOrder is null when really it should be an integer representing the global ordering.

More information you can provide would certainly help narrow things down for us. :)

P.S. I think the way the schema is defined in 3.71 would make things easier as the field is nullable so whether or not you put a value into it or not is acceptable. So again not sure how you are inserting the value.

Hi Tim,

Both the database and schema were defined as NOT NULL for the system fields. These are system fields by default for Category Menu type so I am nt editing these fields directly.

I was simply adding a new record after updating to CMSB 3.71 when I got the errI edited the schema in the database editor and removed Not Null from the necessary fields, which also updated the database. I was then able to add the record. 

I  don't know when the change occurred but I will dig deeper when I have sometime over the next few days.

I did some digging. In CMSB 3.64, the system fields are defined as Not Null.

  'globalOrder' => array(
    'order' => '6',
    'label' => '_globalOrder',
    'type' => 'none',
    'customColumnType' => 'int(10) unsigned NOT NULL',
    'isSystemField' => '1',
  ),
  'siblingOrder' => array(
    'order' => '7',
    'label' => '_siblingOrder',
    'type' => 'none',
    'customColumnType' => 'int(10) unsigned NOT NULL',
    'isSystemField' => '1',
  ),
  'lineage' => array(
    'order' => '8',
    'label' => '_lineage',
    'type' => 'none',
    'customColumnType' => 'varchar(255) NOT NULL',
    'isSystemField' => '1',
  ),
  'depth' => array(
    'order' => '9',
    'label' => '_depth',
    'type' => 'none',
    'customColumnType' => 'int(10) unsigned NOT NULL',
    'isSystemField' => '1',
  ),
  'parentNum' => array(
    'order' => '10',
    'label' => 'Parent Category',
    'type' => 'parentCategory',
    'customColumnType' => 'int(10) unsigned NOT NULL',
    'isSystemField' => '1',
  ),
  'breadcrumb' => array(
    'order' => '11',
    'label' => 'Breadcrumb',
    'type' => 'none',
    'customColumnType' => 'varchar(255) NOT NULL',
    'isSystemField' => '1',
  ),

It was changed in 3.65, and the NOT NULL was removed. It continued to work until 3.71. New category-type tables created after 3.64 would not experience the issue.

Hi Jeff, 

Yes, that's a bug, thanks for bringing that to our attention.  We'll add a patch for this to an upcoming release, but in the meantime here's a workaround: 

• Go to: Database Editor > Your Category Menu
• Change the MySQL Column Type on these fields to the new value below:
  • Change _globalOrder to custom column type int unsigned
  • Change _depth to custom column type int unsigned
  • Change _lineage to varchar(255)
  • Change breadcrumb to varchar(255)

It's because the old category schemas aren't getting updated to the column types used by the new code.

Let me know if that works for you.

Thanks Dave,

had already figured that out.