Export (DB syntax) error when Smart Groups are part of a larger Regular Group

[KarinG]

Hi everyone,

I've have found a workaround for this issue - but thought I would report it - as it may be helpful to document it for others who may be experiencing something similar.

We were in the process of building a 7K Contacts list for our annual Fall Appeal -> this list is a complex addition of donors, members, etc. with certain timing (has been a member since; has donated x years ago; has donated > $x etc.). Basically it's about a dozen of searches (some using Advanced Search, some using Search Builder) which we put into subGroups and then add/subtract (using Custom Searches) to produce our final list (a regular Group). This list of Contacts is then exported to CSV for final vetting and printing labels.

Originally one of the dozen subGroups was a Smart Group (current Members of a certain type). The final list (a regular Group) could not be exported: after 15-20min. the response was DB syntax error ... (see all the way below).

I tried a smaller numbers (thinking we were perhaps dealing with a memory issue, but even simply including the LIKE a% of this final list Group (only 207 Contacts) would produce the same error. Primary fields vs Selected fields (I had some custom fields in it - perhaps there was an issue there): same error.

I found a workaround by rebuilding the final appeals list with nothing but regular Groups only. 7K Contacts exported in a few minutes - no errors. Worked perfectly!

If anyone is interested I would be happy to give you some more details.

Regards, -- Karin


<< append error message >>

Search Builder
Sorry. A non-recoverable error has occurred.
DB Error: syntax error
Error Details
Database Error Code: 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 'group varchar(64), PRIMARY KEY ( id ) ) ENGINE=MyISAM DEFAULT CHARACTER SET u' at line 18, 1064
Additional Details:

Array
(
    [callback] => Array
        (
           

• => CRM_Core_Error
[1] => handle
        )

   

Code: [Select]

=> -2
    [message] => DB Error: syntax error
    [mode] => 16
    [debug_info] =>
CREATE TABLE civicrm_export_temp (
     id int unsigned NOT NULL AUTO_INCREMENT,
last_name varchar(64),
first_name varchar(64),
external_identifier varchar(32),
home_street_address varchar(64),
home_street_number varchar(64),
home_street_number_suffix varchar(64),
home_street_name varchar(64),
home_street_unit varchar(64),
home_city varchar(64),
home_postal_code varchar(64),
home_state varchar(64),
custom_9 varchar(255),
contact_type varchar(64),
contact_sub_type varchar(64),
sort_name varchar(128),
group varchar(64),
  PRIMARY KEY ( id )

) ENGINE=MyISAM DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci
 [nativecode=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 'group varchar(64),
  PRIMARY KEY ( id )

) ENGINE=MyISAM DEFAULT CHARACTER SET u' at line 18]
    [type] => DB_Error
    [user_info] =>
CREATE TABLE civicrm_export_temp (
     id int unsigned NOT NULL AUTO_INCREMENT,
last_name varchar(64),
first_name varchar(64),
external_identifier varchar(32),
home_street_address varchar(64),
home_street_number varchar(64),
home_street_number_suffix varchar(64),
home_street_name varchar(64),
home_street_unit varchar(64),
home_city varchar(64),
home_postal_code varchar(64),
home_state varchar(64),
custom_9 varchar(255),
contact_type varchar(64),
contact_sub_type varchar(64),
sort_name varchar(128),
group varchar(64),
  PRIMARY KEY ( id )

) ENGINE=MyISAM DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci
 [nativecode=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 'group varchar(64),
  PRIMARY KEY ( id )

) ENGINE=MyISAM DEFAULT CHARACTER SET u' at line 18]
    [to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::handle prefix="" info="
CREATE TABLE civicrm_export_temp (
     id int unsigned NOT NULL AUTO_INCREMENT,
last_name varchar(64),
first_name varchar(64),
external_identifier varchar(32),
home_street_address varchar(64),
home_street_number varchar(64),
home_street_number_suffix varchar(64),
home_street_name varchar(64),
home_street_unit varchar(64),
home_city varchar(64),
home_postal_code varchar(64),
home_state varchar(64),
custom_9 varchar(255),
contact_type varchar(64),
contact_sub_type varchar(64),
sort_name varchar(128),
group varchar(64),
  PRIMARY KEY ( id )

) ENGINE=MyISAM DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci
 [nativecode=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 'group varchar(64),
  PRIMARY KEY ( id )

) ENGINE=MyISAM DEFAULT CHARACTER SET u' at line 18]"]
)

Return to home page.


[Kurund Jalmi]

Search Builder
Sorry. A non-recoverable error has occurred.
DB Error: syntax error
Error Details
Database Error Code: 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 'group varchar(64), PRIMARY KEY ( id ) ) ENGINE=MyISAM DEFAULT CHARACTER SET u' at line 18, 1064
Additional Details:

Looks like your few tables are MyISAM, so I am assuming this was upgraded db.

CiviCRM does not support MyISAM, so I would suggest checking your schema integrity

http://wiki.civicrm.org/confluence/display/CRMDOC32/Ensuring+Schema+Integrity+on+Upgrades

and also convert MyISAM to InnoDB

HTh
Kurund

[adixon]

Hey Kurund:

That table type issue is good, but has me confused. I inherited this project so I wasn't around to create the database initially, but I just checked and all the databases tables are actually InnoDB.

What you're seeing above is that the temp tables that CiviCRM is creating on the fly are MyISAM. Which as per:
http://forum.civicrm.org/index.php/topic,16491.msg70424.html#msg70424
appears to be the intended behaviour.

[Kurund Jalmi]

Alan,
You are right. I think I figured out the problem.

group varchar(64),

Group is reserved word in MySQL, hence it fails.

karin, please file an issue for this in bug tracker.

Thanks,
Kurund

[KarinG]

Wiil do.
Thank you, Kurund.

-- Karin