Mysql syntax error in query to external using community builder autoaction

Question

A couple of years ago, using joomla 2.5 and community builder with it's auto action plugin, I created a mysql script that registers a user on a secondary site after the user has registered on a primary site.

Using a CB auto action that triggers after user registration on the primary site, the relevant user table, comprofiler table, and user group map table data for the newly registered user is sent to the secondary site. After the user has confirmed their registration (via the email from the primary site) another auto action, triggered on user confirmation, sets the user confirmed status in the secondary site to active.

All of that worked fine. I am now trying to update the script to work with joomla 3.5 (due to database changes) but I have a syntax error that I can't figure out. The script (only for the first auto action) is below, the only things have changed since the j2.5 version are the database fields, so I don't understand why there is a syntax error atm.

The syntax error is: #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 ')' at line 1

INSERT INTO `joomla_3_5_1_3`.`lpwo8_users` (
`id` ,
`name` ,
`username` ,
`email` ,
`password` ,
`block` ,
`sendEmail` ,
`registerDate` ,
`lastvisitDate` ,
`activation` ,
`params` ,
`lastResetTime` ,
`resetCount` ,
`otpKey` ,
`otep` ,
`requireReset`
)


VALUES (
NULL , '[name]', '[username]', '[email]', '[cb:userdata field="password" /]', '[block]', '[sendEmail]', '[registerDate]', '[lastvisitDate]', '[activation]', '[params]', '[lastResetTime]', '[resetCount]', '[otpKey]', '[otep]', '[requireReset]'
);
);
;

SET @idtemp = (SELECT `id` FROM `lpwo8_users` WHERE `activation` = '[activation]');
INSERT INTO `joomla_3_5_1_3`.`lpwo8_comprofiler` (
`id` , 
`user_id` , 
`firstname` , 
`middlename` , 
`lastname` , 
`hits` , 
`message_last_sent` , 
`message_number_sent` , 
`avatar` , 
`avatarapproved` , 
`canvas` , 
`canvasapproved` , 
`approved` , 
`confirmed` , 
`lastupdatedate` , 
`registeripaddr` , 
`cbactivation` , 
`banned` , 
`banneddate` , 
`unbanneddate`,  
`bannedby` , 
`unbannedby` , 
`bannedreason` , 
`acceptedterms` , 
`cb_antispam_ipaddress` , 
`cb_antispam_captcha` , 
`cb_fb_userid` , 
`cb_twitter_userid` , 
`cb_linkedin_userid` , 
`cb_windowslive_userid` , 
`cb_google_userid` , 
`cb_instagram_userid` , 
`cb_foursquare_userid` , 
`cb_github_userid` , 
`cb_vkontakte_userid` , 
`cb_steam_userid` , 
`cb_tumblr_userid` , 
`cb_invite_code` , 
`cb_privacy_profile` , 
`cb_privacy_disable_me` , 
`cb_privacy_delete_me` , 
`cb_template_profile` , 
`cb_pb_enable` , 
`cb_pb_autopublish` , 
`cb_pb_notifyme` , 
`cb_pb_enable_blog` , 
`cb_pb_enable_wall` , 
`cb_pb_autopublish_wall` , 
`cb_pb_notifyme_wall` , 
`cb_pb_profile_rating`
)



VALUES (
@idtemp, @idtemp,
'[firstname]', 
'[middlename]', 
'[lastname]', 
'[hits]', 
'[message_last_sent]', 
'[message_number_sent]', 
'[avatar]', 
'[avatarapproved]', 
'[canvas]', 
'[canvasapproved]', 
'[approved]', 
'[confirmed]', 
'[lastupdatedate]', 
'[registeripaddr]', 
'[cbactivation]', 
'[banned]', 
'[banneddate]', 
'[unbanneddate]',  
'[bannedby]', 
'[unbannedby]', 
'[bannedreason]', 
'[acceptedterms]', 
'[cb_antispam_ipaddress]', 
'[cb_antispam_captcha]', 
'[cb_fb_userid]', 
'[cb_twitter_userid]', 
'[cb_linkedin_userid]', 
'[cb_windowslive_userid]', 
'[cb_google_userid]', 
'[cb_instagram_userid]', 
'[cb_foursquare_userid]', 
'[cb_github_userid]', 
'[cb_vkontakte_userid]', 
'[cb_steam_userid]', 
'[cb_tumblr_userid]', 
'[cb_invite_code]', 
'[cb_privacy_profile]', 
'[cb_privacy_disable_me]', 
'[cb_privacy_delete_me]', 
'[cb_template_profile]', 
'[cb_pb_enable]', 
'[cb_pb_autopublish]', 
'[cb_pb_notifyme]', 
'[cb_pb_enable_blog]', 
'[cb_pb_enable_wall]', 
'[cb_pb_autopublish_wall]', 
'[cb_pb_notifyme_wall]', 
'[cb_pb_profile_rating]'
);
);
;


SET @idtempg = (SELECT `id` FROM `lpwo8_users` WHERE `activation` = '[activation]');
INSERT INTO `joomla_3_5_1_3`.`lpwo8_user_usergroup_map`(
`user_id` ,
`group_id`
) 
VALUES (
@idtempg, '2'
);
);'

Answer 1

I'm not familiar with CB autoaction, but it looks like you have some additional closing parenthesis and semicolons after each set of VALUES:

VALUES (
NULL , '[name]', '[username]', '[email]', '[cb:userdata field="password" /]', '[block]', '[sendEmail]', '[registerDate]', '[lastvisitDate]', '[activation]', '[params]', '[lastResetTime]', '[resetCount]', '[otpKey]', '[otep]', '[requireReset]'
);
);
;

Try removing them so it ends with a single closing parenthesis only, like this:

VALUES (
NULL , '[name]', '[username]', '[email]', '[cb:userdata field="password" /]', '[block]', '[sendEmail]', '[registerDate]', '[lastvisitDate]', '[activation]', '[params]', '[lastResetTime]', '[resetCount]', '[otpKey]', '[otep]', '[requireReset]'
)

Remember to make this change in each set of VALUES.

You can also check your SQL syntax using an online tool, but since you're using a special CB syntax it will always fail. Still, it might give you a hint as to where you have an error.