1
0
-1

Any field I try to update using bulk edit fails with an error similar to this: A Database Error Occurred Error Number: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' system WHERE .system_id = system.system_id AND = '' AND system.man_status = '' at line 1 SELECT man_type FROM system WHERE system_id = '83' AND system_id in ( SELECT distinct(system.system_id) FROM , system WHERE .system_id = system.system_id AND = '' AND system.man_status = 'production' AND .timestamp = system.timestamp ) Filename: models/m_oa_group.php Line Number: 127 Tried with a change to asset number, purchase date and Status. Same with selecting a single PC or multiple PCs in a group.

    CommentAdd your comment...

    2 answers

    1.  
      1
      0
      -1

      Sorry for not coming back to this issue for so long!

      I updated to version 1.12.4 (actually made a new virtual machine, installed 1.8.1, restored database, then upgraded - old vm was i686 linux so had to build an x64 vm). Still having this same error message.

       

      The bulk edit change actually succeeds, but the error still appears.When changing the Status field to Deleted, it actually gets changed to 'deleted' (lower case D) and so the machine still appears in production until I manually change each one to 'Deleted'.

      I also confirmed I have no group where group_dynamic_select='' .

      Let me know if I can provide any more info.

       

      JDC

      1. Mark Unwin

        The edit does success, then updating the groups fails. I'll dig into this some more. Can you tell me which line is causing the issue?

      2. Jay Carter

        Yes, it succeeds...same error whether I am setting the Asset number field or the status field, only the system_id changes: Error Number: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' system WHERE .system_id = system.system_id AND = '' AND system.man_status = 'pr' at line 1 /* M_oa_group::update_system_groups */ SELECT system_id FROM system WHERE system_id = '198' AND system_id in ( SELECT distinct(system.system_id) FROM , system WHERE .system_id = system.system_id AND = '' AND system.man_status = 'production' AND .timestamp = system.timestamp ) Filename: models/m_oa_group.php Line Number: 151 JDC

      3. Mark Unwin

        JDC, If you're using Windows, first cd to the correct directory: cd c:\xampplite\mysql\bin Then log on to the MySQL console - mysql -u openaudit -popenauditpassword openaudit Next run the below SQL SELECT group_id, group_name FROM oa_group WHERE group_dynamic_select LIKE '%FROM , system%'; That should get you the id and name of the group causing the issue. From there you can go to the web interface and delete this group (menu -> Admin -> Groups -> List Groups then click the delete icon). Type 'exit' (sans quotes) to get out of the MySQL console. Mark.

      4. Jay Carter

        OK, I found two groups called 'No Location' for devices without a location specified. Deleted these and the error message is gone...also the status update to Deleted seems to be working as expected. Thanks! JDC

      CommentAdd your comment...
    2.  
      1
      0
      -1

      Looks like you have a bad group definition stored in the database.

      If you added any custom group definitions yourself, I'd try deleting them.

      Failing that you can log in to MySQL and run the following to see which group is causing the issue.

      You should see any groups without the required 'group_dyamic_select'. Note down the group name and id of any that appear and delete them using the web interface via menu -> Admin -> Groups -> List Groups.

       

       

        CommentAdd your comment...