Node edits prevented by "PDOException: SQLSTATE[HY000]: General error: 1366 Incorrect integer value" error

The column "siblingmenutree_enable" stores unsigned integers (a 1 to enable siblingmenus and a 0 to disable sibling menus). It also must be not null, so you're right about the error being caused by the lack of a value in that column.

I haven't been able to duplicate this using a fresh Drupal 7 install. Can you disable any other modules and see if you are able to submit the node edit for successfully?

I'm not sure why "submenutree_enable" is not getting a value, but I think that providing a better default value or adding in validation when the node edit form is submitted should fix the problem.

Are you willing to test out patches?

> upon submitting a node edit form for certain nodes

If this behaviour happens only sometimes under certain conditions, then possibly, something is unintentionally interfering with the submenutree fields in the node edit form. Maybe the submenutree fieldset has been messed up or overwritten ... then submenutree_node_update() would receive missing values.

Can you tell us what 'certain conditions' seem to trigger this issue? Also, do you have any other menu-related modules enabled on your site?

Hi,

Same problem here.
Solved by changing permissions.

Alex

Hi apollonet,

Can you explain which permissions you had to change?
That way we can look into any problems with permissions or address any confusion that site builders might have with Submenu Tree and permissions.

Also, other people who might have this issue could attempt to fix the problem by following the steps that you took.

I'm going to assume that you have solved your issue.

Please reopen this issue and provide the information that bengtan and myself requested if you are still experiencing problems.

Hi

I have reopened this since it is the only issue addressing this problem, and I reproduced this this morning.

Essentially, I cloned a node which had a subtree set and the setting did not copy across, leaving a null value in the field

PDOException: SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'siblingmenutree_enable' at row 1: UPDATE {node_submenutree} SET nid=:db_update_placeholder_0, submenutree_enable=:db_update_placeholder_1, submenutree_title=:db_update_placeholder_2, submenutree_display=:db_update_placeholder_3, submenutree_weight=:db_update_placeholder_4, submenutree_block=:db_update_placeholder_5, siblingmenutree_enable=:db_update_placeholder_6, siblingmenutree_title=:db_update_placeholder_7, siblingmenutree_display=:db_update_placeholder_8, siblingmenutree_weight=:db_update_placeholder_9, siblingmenutree_block=:db_update_placeholder_10 WHERE ( (nid = :db_condition_placeholder_0) ); Array ( [:db_update_placeholder_0] => 1448 [:db_update_placeholder_1] => 1 [:db_update_placeholder_2] => [:db_update_placeholder_3] => submenutree_items [:db_update_placeholder_4] => 10 [:db_update_placeholder_5] => 0 [:db_update_placeholder_6] => [:db_update_placeholder_7] => [:db_update_placeholder_8] => submenutree_items [:db_update_placeholder_9] => 10 [:db_update_placeholder_10] => 0 [:db_condition_placeholder_0] => 1448 ) in submenutree_node_update() (line 213 of /sites/all/modules/custom/submenutree/submenutree.module).

Hope this helps in tracing the issue

RichardCanoe

I had the same problem after the latest update (7.x-1.2).
According to the hint about permissions, I tried to adjust them step by step. What I found: initially no permissions were granted for any role at all. After setting the permission for "Administer submenu trees" and "Administer sublingmenu trees" the user with the corresponding role was able to edit notes successfully.
Maybe this is helpful to anyone?

If you are manually making database changes and incorrect values are being inserted into the submenutree table then issues can arise which are not the responsibility of Submenu Tree.

The cause of the issue in #6 is outlined in #1; a value of 0 or 1 must be present in the submenutree_enable or siblingmenutree_enable columns.

The recommended 7.x-2.x branch of Submenu Tree has a large number of additional features which required significant changes to the way that Submenu Tree deals with the database. You may want to upgrade to the latest 2.x version of Submenu Tree and see if it does a better job of dealing with incorrect database values.

I'm marking this as "works as designed". If you are having a similar issue and have not manually modified the database in any way, please create a new issue and provide steps to duplicate the problem.

I am able to replicate something related in 7.x-2.3.
The explanation in #1 is fair, but the code around it is not waterproof.

Through a specific (but not hacky) course of events you can get into an unstable and fatal situation.

On a fresh site, do NOT enable submenutree.
Add a bunch of content (eg with devel_generate)
Then:
Download/install/enable submenu tree module
Enable it for your content type(s)

At this point, the database is regarded as unstable, as there is node content present that submenu tree doesn't know about. This is the situation that the module does not expect ... but it also shouldn't be a problem really.

If you now:
use the content management form to perform a bulk update - eg select some things and 'promote to front page' submenu tree causes death with :

PDOException: SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'submenutree_enable' cannot be null: INSERT INTO {node_submenutree} (nid, submenutree_enable, submenutree_title, submenutree_display, submenutree_weight, siblingmenutree_enable, siblingmenutree_title, siblingmenutree_display, siblingmenutree_weight) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6, :db_insert_placeholder_7, :db_insert_placeholder_8); Array ( [:db_insert_placeholder_0] => 67 [:db_insert_placeholder_1] => [:db_insert_placeholder_2] => [:db_insert_placeholder_3] => [:db_insert_placeholder_4] => [:db_insert_placeholder_5] => [:db_insert_placeholder_6] => [:db_insert_placeholder_7] => [:db_insert_placeholder_8] => ) in submenutree_node_update() (line 345 of sites/all/modules/contrib/submenutree/submenutree.module).

I would have expected that core (db_merge in submenutree_node_update()) should have been able to deal with that - it knows what the default values *should* be . but for whatever reason, it's not using them.

MY FIX for now is to ensure that unset expected values default to 0 when saving:

@@ -336,7 +336,7 @@ function submenutree_node_update($node) {
     $values = array();
 
     foreach ($schema['fields'] as $field => $unused) {
-      $values[$field] = $node->$field;
+      $values[$field] = empty($node->$field) ? 0 : $node->$field;
     }
 
     db_merge('node_submenutree')

If this isn't the exact same issue as reported above, it's definitely in the same family. Submenu tree should provide useful defaults if trying to save a node that doesn't have those values provided from the form.

Ideally I guess it shouldn't even save a row in the DB if no data at all is set on that node.

Sorry, I see you asked for a new issue here. My bad.

Patch above is not good - it sets the 'submenutree_title' to '0'. I guess it needs more smarts based on the schema ...

Slightly modified patch, doesn't attempt to save settings unless sub or siblingmenu tree is enabled for the node. And if it is, then it only tries to save those values that have data set for them.

Revisiting this, on a new site, doing something different, but still triggering the same family of error.

Using a bulk node importer (import_html) which fundamentally is similar to what feeds or node_clone would do:
The node content type has submenutree functions available to it.
The node is created via code, code that doesn't know (and should not need to know) whether submenutree is around.
This node does nod go through any FAPI, it gets validated then goes straight to node_save.

submenutree blows up the whole process with

An AJAX HTTP error occurred. HTTP Result Code: 500 Debugging information follows. Path: /batch?id=6&op=do StatusText: Service unavailable (with message) ResponseText: PDOException: SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'submenutree_enable' cannot be null: INSERT INTO {node_submenutree} (nid, submenutree_enable, submenutree_title, submenutree_display, submenutree_weight, siblingmenutree_enable, siblingmenutree_title, siblingmenutree_display, siblingmenutree_weight) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6, :db_insert_placeholder_7, :db_insert_placeholder_8); Array ( [:db_insert_placeholder_0] => 82 [:db_insert_placeholder_1] => [:db_insert_placeholder_2] => [:db_insert_placeholder_3] => [:db_insert_placeholder_4] => [:db_insert_placeholder_5] => [:db_insert_placeholder_6] => [:db_insert_placeholder_7] => [:db_insert_placeholder_8] => ) in submenutree_node_update() (line 345 of /private/var/www/agov/sites/all/modules/submenutree/submenutree.module).

I can confirm that patch #14 from mrfelton fixes this - in an appropriate way, and things no longer crash & burn just because a value wasn't set somewhere.
+1

As todays 7.x-2.x-dev doesn't seem to work (for other reasons?), I'm not sure if it's good to RTBC. But the patch applies to 7.x-2.3 and performs as expected.
It also applies to 7.x-2.x-dev, but testing there doesn't work right now.

A question remains about whether nodes saved in this 'undefined' state should later default to on or off when editing or viewing.
That would be a feature request for a 'default behavior' option on a per-content type basis I think.
It should at least be made consistent - currently when editing a page, the UI will default to 'ON' whereas when viewing an untouched page, it defaults to 'OFF'

An effect of this is that
* you view a fresh page (submenutrees are not shown)
* you 'edit' the page, but make no changes (the hidden checkbox becomes clicked without your intervention)
* you save (no deliberate changes) and suddenly submenutrees are on.
- either on or off consistently would make more sense. This one is a wierd gotcha for editors.

updated patch depending on #14, to allow unset the settings on update.

Rahul.

Above patch and from #14 was wrong, causing to not save the data on unchecking both sub and sibling content. proper patch form #9 attached.

Same problem for me. I don't want to patch and prefer to wait for a corrected release.

I can confirm that the patch of #17 works.

But why do you add the check after requesting the database scheme?

As far as I can see you can save this call, moving the check before

    $schema = drupal_get_schema('node_submenutree');

Thanks to all!