Error after upgrading from 1.7.2 to 1.8.0

mp2000 · October 2018

Hello,

after upgrading to 1.8.0 this code does not work anymore.

error: An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'CMDB_Asset.id' in 'field list'

<?php
require_once '../../../../configs/main.php';
require_once '../../../../libs/datatables-editor/lib/DataTables.php';

use DataTables\Editor;
use DataTables\Editor\Field;
use DataTables\Editor\Mjoin;
use DataTables\Editor\Options;
use DataTables\Editor\Upload;
use DataTables\Editor\Validate;

if ($_SESSION['login']['login_ok'] == 1) {
    Editor::inst($dbIntranet, 'CMDB_Asset')
        ->debug(false)
        ->fields(
            Field::inst('CMDB_Asset.id')->set(false),
            Field::inst('CMDB_Asset_Dms.id')->set(false),
            Field::inst('CMDB_Asset_Dms.Name'),
            Field::inst('CMDB_Asset_Dms.Note'),
            Field::inst('CMDB_Status.Name'),
            Field::inst('XRM_Company.id'),
            Field::inst('V_CMDB_Asset.ci')->set(false),
            Field::inst('CMDB_Asset_Dms.DateUpdated')
                ->getFormatter('Format::datetime', array(
                    //'from' => 'Y-m-d H:i:s',
                    'from' => 'Y-m-d H:i:s',
                    'to'   => 'd.m.Y',
                )),
            Field::inst('CMDB_Asset_Dms.ChangedBy')
                ->setValue($_SESSION['login']['userid'])
                ->validator('Validate::notEmpty'),
            Field::inst('CMDB_V_ProductCatalogSub.NameShort'),
            Field::inst('CMDB_V_ProductCatalogSub.Name'),
            Field::inst('CMDB_Asset_Dms.cmdb_productcatalogsub_id')
                ->options(Options::inst()
                        ->table('CMDB_V_ProductCatalogSub')
                        ->value('id')
                        ->label('Name')
                )
                ->validator('Validate::dbValues')
                ->validator('Validate::notEmpty'),

            Field::inst('CMDB_Asset.cmdb_status_id')
                ->options(Options::inst()
                        ->table('CMDB_Status')
                        ->value('id')
                        ->label('Name')
                )
                ->validator('Validate::dbValues')
                ->validator('Validate::notEmpty'),

            Field::inst('XRM_Company.Name'),
            Field::inst('CMDB_Asset.xrm_company_id')
                ->options(Options::inst()
                        ->table('XRM_Company')
                        ->value('id')
                        ->label('Name')
                )
                ->validator('Validate::dbValues')
                ->validator('Validate::notEmpty'),

            Field::inst('CMDB_Asset_Type.Name'),
            Field::inst('CMDB_Asset.cmdb_asset_type_id')
                ->options(Options::inst()
                        ->table('CMDB_Asset_Type')
                        ->value('id')
                        ->label('Name')
                )
                ->validator('Validate::dbValues'),
            Field::inst('CMDB_Asset_DmsCategory.Name'),
            Field::inst('CMDB_Asset_Dms.cmdb_asset_dmscategory_id')
                ->options(Options::inst()
                        ->table('CMDB_Asset_DmsCategory')
                        ->value('id')
                        ->label('Name')
                )
                ->validator('Validate::dbValues')
                ->validator('Validate::notEmpty')

        )
        ->leftJoin('CMDB_Asset_Dms', 'CMDB_Asset_Dms.cmdb_asset_id', '=', 'CMDB_Asset.id')
        ->leftJoin('XRM_Company', 'XRM_Company.id', '=', 'CMDB_Asset.xrm_company_id')
        ->leftJoin('CMDB_Status', 'CMDB_Status.id', '=', 'CMDB_Asset.cmdb_status_id')
        ->leftJoin('CMDB_Asset_Type', 'CMDB_Asset_Type.id', '=', 'CMDB_Asset.cmdb_asset_type_id')
        ->leftJoin('CMDB_V_ProductCatalogSub', 'CMDB_V_ProductCatalogSub.id', '=', 'CMDB_Asset_Dms.cmdb_productcatalogsub_id')
        ->leftJoin('CMDB_Asset_DmsCategory', 'CMDB_Asset_DmsCategory.id', '=', 'CMDB_Asset_Dms.cmdb_asset_dmscategory_id')
        ->leftJoin('V_CMDB_Asset', 'V_CMDB_Asset.id', '=', 'CMDB_Asset.id')
        ->join(
            Mjoin::inst('CMDB_Asset_Dms_File') //many-to-many
                ->link('CMDB_Asset.id', 'CMDB_Asset_Dms_Link_File.cmdb_asset_id')
                ->link('CMDB_Asset_Dms_File.id', 'CMDB_Asset_Dms_Link_File.cmdb_asset_dms_file_id')
                ->fields(
                    Field::inst('id')
                        ->upload(Upload::inst($_SERVER['DOCUMENT_ROOT'] . '/cxintranet_cms/dms/__ID___' . mt_rand() . '.__EXTN__')
                                ->db('CMDB_Asset_Dms_File', 'id', array(
                                    'DateCreated' => Upload::DB_READ_ONLY,
                                    'Filename'    => Upload::DB_FILE_NAME,
                                    'Filesize'    => Upload::DB_FILE_SIZE,
                                    'WebPath'     => Upload::DB_WEB_PATH,
                                    'SystemPath'  => Upload::DB_SYSTEM_PATH,
                                ))
                                ->validator(Validate::fileSize(10485760, 'Max. Dateigröße: 10MB'))
                                ->validator(Validate::fileExtensions(array('pdf', 'txt', 'lic', 'doc', 'docx', 'xls', 'xlsx', 'html'), "Erlaubte Dateitypen: pdf, txt, lic, doc, docx, xls, xlsx, html"))
                        )
                )
        )
        ->where('CMDB_Asset_Type.id', 31)
        ->process($_POST)
        ->json();
}

$(function() {
    //editor
    var editor;
    editor = new $.fn.dataTable.Editor({
        ajax: "ajax/cmdb/assets/dms/processData.php",
        table: "#table_[@ID UID]",
        fields: [{
                label: "ID:",
                name: 'V_CMDB_Asset.ci',
                type: 'readonly'
            }, {
                label: "Firma:",
                name: "CMDB_Asset.xrm_company_id",
                opts: {
                    multiple: false,
                    placeholder: 'Firma auswählen'
                },
                "type": "select2"
            }, {
                label: "Kategorie:",
                name: "CMDB_Asset_Dms.cmdb_asset_dmscategory_id",
                opts: {
                    multiple: false,
                    placeholder: 'Kategorie auswählen'
                },
                "type": "select2"
            }, {
                label: "Notiz:",
                name: 'CMDB_Asset_Dms.Note',
                "type": "ckeditorClassic"
            }, {
                label: "Dateien:",
                name: "CMDB_Asset_Dms_File[].id",
                type: "uploadMany",
                display: function(fileId, counter) {
                    return '<a href="' + editor.file('CMDB_Asset_Dms_File', fileId).WebPath + '" target="_blank">' + moment(editor.file('CMDB_Asset_Dms_File', fileId).DateCreated).format('DD.MM.YYYY') + ' | ' + editor.file('CMDB_Asset_Dms_File', fileId).Filename + '</a>';
                },
                noFileText: 'keine Dateien'
            }, {
                label: "Status:",
                name: "CMDB_Asset.cmdb_status_id",
                opts: {
                    multiple: false,
                    placeholder: 'Status auswählen'
                },
                type: "select2"
            }, {
                label: "(Optional) Produkt:",
                name: "CMDB_Asset_Dms.cmdb_productcatalogsub_id",
                opts: {
                    multiple: false,
                    placeholder: 'Produkt auswählen'
                },
                type: "select2",
                def: function() {
                    return 1
                },
            }, {
                label: "Typ:",
                name: "CMDB_Asset.cmdb_asset_type_id",
                type: "select"
            },
        ]
    });
    //Sortieren
    $.fn.dataTable.moment('DD.MM.YYYY');
    //datatable
    var table = $('#table_[@ID UID]').DataTable({
        "lengthMenu": [
            [10, 25, 50, 75, 100, 150, 200, 300, -1],
            [10, 25, 50, 75, 100, 150, 200, 300, "All"]
        ],
        "columnDefs": [{
            responsivePriority: 2,
            targets: 0
        }],
        "order": [
            [1, 'desc']
        ],
        "scrollX": false,
        "searchPane": true,
        fixedHeader: {
            headerOffset: 43
        },
        responsive: true,
        ajax: 'ajax/cmdb/assets/dms/processData.php',
        dom: 'Bflrtip',
        columns: [{
            data: null
        }, {
            data: 'CMDB_Asset_Dms.DateUpdated'
        }, {
            data: 'V_CMDB_Asset.ci'
        }, {
            data: 'XRM_Company.Name',
            editField: 'CMDB_Asset.xrm_company_id'
        }, {
            data: 'CMDB_Asset_Dms.Name'
        }, {
            data: 'CMDB_Status.Name',
            editField: 'CMDB_Asset.cmdb_status_id'
        }, {
            data: null,
            className: "center",
            defaultContent: '<a href="" class="editor_edit">Edit</a>'
        }, ],
        select: {
            style: 'os',
            //selector: 'td:first-child'
        },
        buttons: [{
                extend: 'create',
                editor: editor,
                text: 'neu'
            }, {
                extend: 'edit',
                editor: editor,
                text: 'anzeigen/bearbeiten',
                formButtons: [{
                    label: 'abbrechen',
                    fn: function() {
                        this.close();
                    }
                }, 'speichern'],
                formOptions: [{
                    focus: null,
                }],
            },
        ]
    });

});

allan · October 2018

Hi,

What database are you using please?

Also, could you change:

->process($_POST)

to be:

->debug(true)
->process($_POST)

please? Then show me the JSON that the server responds with (which you will be able to get in the network panel of your browser's inspector).

Allan

mp2000 · October 2018

Hello Allan,

mysql version: 5.7.23-0ubuntu0.16.04.1

debug:

{
    "fieldErrors": [],
    "error": "An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'CMDB_Asset.id' in 'field list'",
    "data": [],
    "ipOpts": [],
    "cancelled": [],
    "debug": [{
        "query": "SELECT `id` as 'id' FROM `CMDB_V_ProductCatalogSub` WHERE `id` = :where_0 ",
        "bindings": [{
            "name": ":where_0",
            "value": "1",
            "type": null
        }]
    }, {
        "query": "SELECT `id` as 'id' FROM `CMDB_Status` WHERE `id` = :where_0 ",
        "bindings": [{
            "name": ":where_0",
            "value": "7",
            "type": null
        }]
    }, {
        "query": "SELECT `id` as 'id' FROM `XRM_Company` WHERE `id` = :where_0 ",
        "bindings": [{
            "name": ":where_0",
            "value": "102",
            "type": null
        }]
    }, {
        "query": "SELECT `id` as 'id' FROM `CMDB_Asset_Type` WHERE `id` = :where_0 ",
        "bindings": [{
            "name": ":where_0",
            "value": "31",
            "type": null
        }]
    }, {
        "query": "SELECT `id` as 'id' FROM `CMDB_Asset_DmsCategory` WHERE `id` = :where_0 ",
        "bindings": [{
            "name": ":where_0",
            "value": "7",
            "type": null
        }]
    }, {
        "query": "INSERT INTO `CMDB_Asset` ( `cmdb_status_id`, `xrm_company_id`, `cmdb_asset_type_id` ) VALUES ( :cmdb_status_id, :xrm_company_id, :cmdb_asset_type_id )",
        "bindings": [{
            "name": ":cmdb_status_id",
            "value": "7",
            "type": null
        }, {
            "name": ":xrm_company_id",
            "value": "102",
            "type": null
        }, {
            "name": ":cmdb_asset_type_id",
            "value": "31",
            "type": null
        }]
    }, {
        "query": "SELECT `CMDB_Asset`.`id` as 'CMDB_Asset.id' FROM `CMDB_Asset_Dms` WHERE `cmdb_asset_id` = :where_0 ",
        "bindings": [{
            "name": ":where_0",
            "value": "1302",
            "type": null
        }]
    }]
}

allan · October 2018

That last one doesn't look right:

SELECT `CMDB_Asset`.`id` as 'CMDB_Asset.id'
FROM `CMDB_Asset_Dms`
WHERE `cmdb_asset_id` = :where_0

This is happening on insert of a new record. Is that correct? Does it happen on edit?

Could you try using:

Editor::inst($dbIntranet, 'CMDB_Asset', 'CMDB_Asset.id')

for the constructor?

Thanks,
Allan

mp2000 · October 2018

Yes, that will be the mistake, but why?

SELECT `CMDB_Asset`.`id` as 'CMDB_Asset.id'
FROM `CMDB_Asset_Dms`
WHERE `cmdb_asset_id` = :where_0

the above was "on insert". with or without "Editor::inst($dbIntranet, 'CMDB_Asset', 'CMDB_Asset.id')" there is the same error.

this is "on update" and with "Editor::inst($dbIntranet, 'CMDB_Asset', 'CMDB_Asset.id')".
but with same error:

{
    "fieldErrors": [],
    "error": "An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'CMDB_Asset.id' in 'field list'",
    "data": [],
    "ipOpts": [],
    "cancelled": [],
    "debug": [{
        "query": "SELECT `id` as 'id' FROM `CMDB_V_ProductCatalogSub` WHERE `id` = :where_0 ",
        "bindings": [{
            "name": ":where_0",
            "value": "1",
            "type": null
        }]
    }, {
        "query": "SELECT `id` as 'id' FROM `CMDB_Status` WHERE `id` = :where_0 ",
        "bindings": [{
            "name": ":where_0",
            "value": "6",
            "type": null
        }]
    }, {
        "query": "SELECT `id` as 'id' FROM `XRM_Company` WHERE `id` = :where_0 ",
        "bindings": [{
            "name": ":where_0",
            "value": "102",
            "type": null
        }]
    }, {
        "query": "SELECT `id` as 'id' FROM `CMDB_Asset_Type` WHERE `id` = :where_0 ",
        "bindings": [{
            "name": ":where_0",
            "value": "31",
            "type": null
        }]
    }, {
        "query": "SELECT `id` as 'id' FROM `CMDB_Asset_DmsCategory` WHERE `id` = :where_0 ",
        "bindings": [{
            "name": ":where_0",
            "value": "4",
            "type": null
        }]
    }, {
        "query": "SELECT `CMDB_Asset`.`id` as 'CMDB_Asset.id' FROM `CMDB_Asset` WHERE `CMDB_Asset`.`id` = :where_0 ",
        "bindings": [{
            "name": ":where_0",
            "value": "1280",
            "type": null
        }]
    }, {
        "query": "UPDATE `CMDB_Asset` SET `cmdb_status_id` = :cmdb_status_id, `xrm_company_id` = :xrm_company_id, `cmdb_asset_type_id` = :cmdb_asset_type_id WHERE `CMDB_Asset`.`id` = :where_0 ",
        "bindings": [{
            "name": ":cmdb_status_id",
            "value": "6",
            "type": null
        }, {
            "name": ":xrm_company_id",
            "value": "102",
            "type": null
        }, {
            "name": ":cmdb_asset_type_id",
            "value": "31",
            "type": null
        }, {
            "name": ":where_0",
            "value": "1280",
            "type": null
        }]
    }, {
        "query": "SELECT `CMDB_Asset`.`id` as 'CMDB_Asset.id' FROM `CMDB_Asset_Dms` WHERE `cmdb_asset_id` = :where_0 ",
        "bindings": [{
            "name": ":where_0",
            "value": "1280",
            "type": null
        }]
    }]
}

mp2000 · October 2018

do you have any idea what else I can test?

allan · October 2018

Sorry - I'm not clear if you used CMDB_Asset.id as the third parameter for the Editor::inst() method? Did you try that? Did it make any difference?

Allan

mp2000 · October 2018

no difference, same error

allan · October 2018

Thanks. I'll see if I can reproduce the error locally. I've tried with a few simple examples but haven't managed to do so yet.

Are you able to send me a dump of your sql db schema (just the schema, not the data)? PM me by clicking my name above and then the "Send message" button if you don't want to make it public.

Thanks,
Allan

mp2000 · October 2018

I sent you a message.

btree · October 2018

Hi,

can I join in to this topic? Just upgrade to 1.8.0, all tables fine except for one where I cannot edit rows anymore (creating rows is no problem).

{  
   "fieldErrors":[  

   ],
   "error":"An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'hive.hive_id' in 'field list'",
   "data":[  

   ],
   "ipOpts":[  

   ],
   "cancelled":[  

   ],
   "debug":[  
      {  
         "query":"SELECT  `hive`.`hive_id` as 'hive.hive_id' FROM  `hive` WHERE `hive`.`hive_id` = :where_0 ",
         "bindings":[  
            {  
               "name":":where_0",
               "value":"13744",
               "type":null
            }
         ]
      },
      {  
         "query":"UPDATE  `hive` SET  `edit_id` = :edit_id, `edited` = :edited, `name` = :name, `note` = :note, `source_id` = :source_id, `type_id` = :type_id, `modus` = :modus, `modus_date` = :modus_date, `position` = :position WHERE `hive`.`hive_id` = :where_0 ",
         "bindings":[  
            {  
               "name":":edit_id",
               "value":"137",
               "type":null
            },
            {  
               "name":":edited",
               "value":"2018-10-30 22:16:42",
               "type":null
            },
            {  
               "name":":name",
               "value":"1804",
               "type":null
            },
            {  
               "name":":note",
               "value":"",
               "type":null
            },
            {  
               "name":":source_id",
               "value":"241",
               "type":null
            },
            {  
               "name":":type_id",
               "value":"179",
               "type":null
            },
            {  
               "name":":modus",
               "value":1,
               "type":null
            },
            {  
               "name":":modus_date",
               "value":"",
               "type":null
            },
            {  
               "name":":position",
               "value":"4",
               "type":null
            },
            {  
               "name":":where_0",
               "value":"13744",
               "type":null
            }
         ]
      },
      {  
         "query":"SELECT  `hive`.`hive_id` as 'hive.hive_id' FROM  hive_group as hive_group WHERE `hive_id` = :where_0 ",
         "bindings":[  
            {  
               "name":":where_0",
               "value":"13744",
               "type":null
            }
         ]
      }
   ]
}

Maybe it's too late but can't figure it out. Maybe you can see anything odd in the debug message.

As I see it he tries to select hive.hive_id from hive_group table. But it should be hive_group.hive_id? The hive_group is a simple LEFT JOIN.

 ->leftJoin( 'hive_group as hive_group', 'hive_group.hive_id', '=', 'hive.hive_id')

Thanks!

allan · October 2018

Apologies for the delay in replying back here. Thanks for sending over the schema @mp2000!

I've isolated the issue - it was because I was explicitly telling the database layer to get the primary key for the main table on insert. If you insert into a different table then it incorrectly still uses that same primary key field. That is corrected in this commit.

Regards,
Allan

btree · October 2018

Works for me, thanks a lot.

mp2000 · November 2018

Thank you so much, I'll implement it the days when I have time.

Error after upgrading from 1.7.2 to 1.8.0

Error after upgrading from 1.7.2 to 1.8.0

Replies

Howdy, Stranger!

Categories

DataTables

Error after upgrading from 1.7.2 to 1.8.0

Error after upgrading from 1.7.2 to 1.8.0

Replies

Howdy, Stranger!

Quick Links

Categories

DataTables