search

civicrm / org.civicrm.contactlayout

Customize the contact summary screen
Other
15 stars 20 forks source link

After CiviCRM update 5.40+ fatal errors: db syntax #107

Closed mariav0 closed 3 years ago

mariav0 commented 3 years ago

When using Contact Layout Manager (CLM) limited on groups and contact type many errors occur. We can reproduce this error on more than one instance.

Steps:

  1. create a group for a CLM layout and add a test user
  2. create a CLM layout and choose this group to limit
  3. search for contacts and view one
  4. error

In our scenario we use group admin as well but this should not be the issue. When CLM is deactivated, no errors are occurring. Same when there is no limit on groups or contact type.

Let me know in case you need more details.

error_db_syntax

colemanw commented 3 years ago

I just tried following those steps and did not encounter an error. What version of CiviCRM and what version of this extension are you using?

mariav0 commented 3 years ago

Hi Coleman, Thanks for trying and helping! I am using CiviCRM 5.40.3 and it still worked with 5.39.0 and CLM has version 2.0.4. But I am afraid that it is a permission problem since it works fine for contacts that has given more permissions :/ Now I just have to find out which permission I need to give to that user so that everything works as before.

edit: It seems I need this permission now: View ANY CONTACT in the CiviCRM database, export contact info and perform activities such as Send Email, Phone Call, etc. But in my scenario I can't give that permission.

colemanw commented 3 years ago

Ok, I will take away that permission from my test user and see if I can reproduce the bug. Hopefully it can be fixed so you do not need to grant that permission.

kcristiano commented 3 years ago

@colemanw

I am having same issue (I think)

Contact layout v 2.0.3 all OK. 2.0.4 cannot load contact page at all:

Aug 24 13:33:55  [error]                                                                                                                
$Fatal Error Details = array:3 [                                                                                                        
  "message" => "DB Error: syntax error"                                                                                                 
  "code" => null                                                                                                                        
  "exception" => PEAR_Exception {#3482                                                                                                  
    #cause: DB_Error {#3479                                                                                                             
      +error_message_prefix: ""                                                                                                         
      +mode: 16                                                                                                                         
      +level: 1024                                                                                                                      
      +code: -2                                                                                                                         
      +message: "DB Error: syntax error"                                                                                                
      +userinfo: "SELECT child_group_id, parent_group_id FROM civicrm_group_nesting WHERE parent_group_id IN () [nativecode=1064 ** You 
have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' 
at line 1]"                                                                                                                             
      +backtrace: array:45 [                                                                                                            
        0 => array:6 [                                                                                                                  
          "file" => "/home/a/public_html/wp-content/plugins/civicrm/civicrm/vendor/pear/db/DB.php"                                   
          "line" => 997                                                                                                                 
          "function" => "__construct"                                                                                                   
          "class" => "PEAR_Error"                                                                                                       
          "type" => "->"                                                                                                                
          "args" => array:5 [                                                                                                           
            0 => "DB Error: syntax error"                                                                                               
            1 => -2                                                                                                                     
            2 => 16                                                                                                                     
            3 => array:2 [                                                                                                              
              0 => "CRM_Core_Error"                                                                                                     
              1 => "exceptionHandler"                                                                                                   
            ]                                                                                                                           
            4 => "SELECT child_group_id, parent_group_id FROM civicrm_group_nesting WHERE parent_group_id IN () [nativecode=1064 ** You 
have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' 
at line 1]"                                                                                                                             
          ]
        ]

I've reverted to 2.0.3 for now. Let me know if you need more details. I have a dev/staging site for this client so I can get more details if needed.

colemanw commented 3 years ago

Sounds like it's definitely related to e5124a33dff530468530f29e93b0910d0edf918f

AHowiller commented 3 years ago

@kcristiano: I can confirm your observation that a downgrade to 2.0.3 made the problem disappear on the system my colleague @mariav0 encountered the issue first, thanks for that hint! We also have a development system at hand where we can still reproduce the problem and can provide details or do testing if that helps.

AsylumSeekersCentre commented 3 years ago

I can confirm that this affected our system running Civi 5.39.2 and CLE 2.0.4.

We've downgraded to CLE 2.0.3 and it's working again.

colemanw commented 3 years ago

I reproduced and found a fix. Can you please test this patch? #108

mariav0 commented 3 years ago

@colemanw I've tested it and it works! Thank you :)

colemanw commented 3 years ago

PR has been merged and I've published a new release https://github.com/civicrm/org.civicrm.contactlayout/releases/tag/2.0.5

kcristiano commented 2 years ago

@colemanw I finally got back to this and have traced the issue to:

SELECT child_group_id, parent_group_id FROM civicrm_group_nesting WHERE parent_group_id IN ()

This statement fails on MariaDB 10.3

SELECT child_group_id, parent_group_id FROM civicrm_group_nesting WHERE parent_group_id IN (24)

will succeed as will

SELECT child_group_id, parent_group_id FROM civicrm_group_nesting WHERE parent_group_id IN ('')

sending () instead of () is what produces the fatal error:

[message] => DB Error: syntax error                                                                                                                        
    [mode] => 16                                                                                                                                               
    [debug_info] => SELECT child_group_id, parent_group_id FROM civicrm_group_nesting WHERE parent_group_id IN () [nativecode=1064 ** You have an error in your
 SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1]                                  
    [type] => DB_Error                                                                                                                                         
    [user_info] => SELECT child_group_id, parent_group_id FROM civicrm_group_nesting WHERE parent_group_id IN () [nativecode=1064 ** You have an error in your 
SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1]                                   
    [to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::exceptionHandler prefix="" info="SELECT child_gro
up_id, parent_group_id FROM civicrm_group_nesting WHERE parent_group_id IN () [nativecode=1064 ** You have an error in your SQL syntax; check the manual that c
orresponds to your MariaDB server version for the right syntax to use near ')' at line 1]"]

This is still an issue on v 2.0.5

colemanw commented 2 years ago

@kcristiano thanks. Based on your description and the backtrace posted above, I think civicrm/civicrm-core#22329 will solve it, although I'm still not sure what's causing the problem exactly. It seems to be related to how your groups and permissions are configured.

kcristiano commented 2 years ago

Thanks @colemanw I will review this particular site, but there are no parent child groups, no ACLs. All permissions are via roles.

kcristiano commented 2 years ago

@colemanw could this be a version issue? The site is on ESR 5.39. Perhaps there are other patches needed that are only in core and API v4

The PR does not solve this sites issue with Contact Layout editor.

colemanw commented 2 years ago

@kcristiano are you still getting the same backtrace? With the patch in place, I'm not sure how an empty IN() could still be getting into that query, as it does an early return if the clause will be empty.

kcristiano commented 2 years ago

Yes I am

Dec 27 12:37:09  [error] $Fatal Error Details = Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => exceptionHandler
        )

    [code] => -2
    [message] => DB Error: syntax error
    [mode] => 16
    [debug_info] => SELECT child_group_id, parent_group_id FROM civicrm_group_nesting WHERE parent_group_id IN () [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1]
    [type] => DB_Error
    [user_info] => SELECT child_group_id, parent_group_id FROM civicrm_group_nesting WHERE parent_group_id IN () [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1]
    [to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::exceptionHandler prefix="" info="SELECT child_group_id, parent_group_id FROM civicrm_group_nesting WHERE parent_group_id IN () [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1]"]
)

I'll also test on 5.45RC and see if that changes things. This site is on 5.39

kcristiano commented 2 years ago

Ok upgraded to 5.45-RC added PR, updated Contactlayouteditor to 2.06

Dec 27 12:44:38  [error] $Fatal Error Details = Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => exceptionHandler
        )

    [code] => -2
    [message] => DB Error: syntax error
    [mode] => 16
    [debug_info] => SELECT child_group_id, parent_group_id FROM civicrm_group_nesting WHERE parent_group_id IN () [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1]
    [type] => DB_Error
    [user_info] => SELECT child_group_id, parent_group_id FROM civicrm_group_nesting WHERE parent_group_id IN () [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1]
    [to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::exceptionHandler prefix="" info="SELECT child_group_id, parent_group_id FROM civicrm_group_nesting WHERE parent_group_id IN () [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1]"]
)
colemanw commented 2 years ago

The ^ problem turned out to be slightly different and caused by a deleted group that was being used as a ContactLayout filter. Fixed with #114