want to apply transaction on below procedure but its not working

Question

DELIMITER $$

USE `g4winners2`$$

DROP PROCEDURE IF EXISTS `ebetToTbConsumer`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `ebetToTbConsumer`()
BEGIN
START TRANSACTION;
  INSERT INTO  g4winners2.`tb_consumer`(first_name,middle_name,last_name,username,PASSWORD ,ssn,email,home_phone
  ,date_of_birth,gender_type,is_locked,is_active,is_online,bonus_level_id,modified_by_entity,modified_by_id,record_created_on,record_modified_on
  ,consumer_status_type
  ,business_id,driver_license,driver_license_state_provice,mothers_maiden_name,id_type,intl_id_state_code,id_expiration,id_status,COMMENT,reward_profile_id
   ,player_tracking_id
 )
  SELECT  cont.first_name, cont.middle_initial, cont.last_name,cust.user_name, cust.encrypted_password, cust.social_security_number, cont.email,cont.home_phone,cust.birthdate,
  cust.sex,
  b'0',b'1',b'0',1,'3',1, FROM_UNIXTIME(cust.signup_date), FROM_UNIXTIME(cust.updated_date) ,cust.status,cust.business_id,cust.drivers_license,cust.drivers_license_state_province,cust.mothers_maiden_name,cust.id_type,cust.intl_id_state_code,
  cust.id_expiration,cust.id_status,cust.comment,cust.reward_profile_id,cust.player_tracking_id
  FROM  ebet.`contact_info` cont ,ebet.`customer` cust WHERE cust.id=cont.customer_id ;


  INSERT INTO `g4winners2`.`tb_consumer_address`(consumer_id,address_line_1,address_line_2
 ,city,state,zip_code,country,county,region,record_modified_on,mailing_list_flag,
  funding_notification_flag,title,marketing1,partner_list_flag,company_name)

  SELECT  cont.customer_id, cont.address1,cont.address2,cont.city,cont.state_province,cont.postal_code,cont.country,cont.county,cont.suburb ,cont.updated_date, cont.mailing_list_flag,
  cont.funding_notification_flag,cont.title,cont.marketing1,cont.partner_list_flag,cont.company_name FROM ebet.`contact_info` cont;


  INSERT INTO  g4winners2.tb_consumer_funds(tote_account_id,pin,total_available_fund,onhold_fund,tote_account_status,last_access)
  SELECT  acc.acct_number,acc.encrypted_pin, FLOOR(acc.current_balance),FLOOR(acc.hold_balance), acc.status,acc.last_access
  FROM ebet.`account` acc;

  INSERT INTO g4winners2.`tb_system_settings`(setting_id,setting_name,setting_value,business_id )

   SELECT  0,conf.name,conf.value,conf.business_id FROM ebet.`config1` conf;

    COMMIT;
  END$$

DELIMITER ;

Answer 1

After creating the procedure, you need to run it. Have you tried to run it using

CALL ebetToTbConsumer(); ?

Your table g4winners2.tb_consumer contains some fields named with reserved words. PASSWORD, and COMMENT MUST be escaped when when using them in your table or you avoid them completely.

Also for g4winners2.tb_system_settings.setting_id field, make sure of the inserted value 0, if it would not throw some constraint error. You're better off if you take a look at the MySQL error log to really see what's going wrong.

Answer 2

You have to set the Server variable autocommit to OFF. You can set it globally bu adding it to server configuration file or explicitly set in procedure as below:

DELIMITER $$

USE `g4winners2`$$

DROP PROCEDURE IF EXISTS `ebetToTbConsumer`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `ebetToTbConsumer`()
BEGIN
SET autocommit=0; /******************Check this line******************/
START TRANSACTION;
  INSERT INTO  g4winners2.`tb_consumer`(first_name,middle_name,last_name,username,PASSWORD ,ssn,email,home_phone
  ,date_of_birth,gender_type,is_locked,is_active,is_online,bonus_level_id,modified_by_entity,modified_by_id,record_created_on,record_modified_on
  ,consumer_status_type
  ,business_id,driver_license,driver_license_state_provice,mothers_maiden_name,id_type,intl_id_state_code,id_expiration,id_status,COMMENT,reward_profile_id
   ,player_tracking_id
 )
  SELECT  cont.first_name, cont.middle_initial, cont.last_name,cust.user_name, cust.encrypted_password, cust.social_security_number, cont.email,cont.home_phone,cust.birthdate,
  cust.sex,
  b'0',b'1',b'0',1,'3',1, FROM_UNIXTIME(cust.signup_date), FROM_UNIXTIME(cust.updated_date) ,cust.status,cust.business_id,cust.drivers_license,cust.drivers_license_state_province,cust.mothers_maiden_name,cust.id_type,cust.intl_id_state_code,
  cust.id_expiration,cust.id_status,cust.comment,cust.reward_profile_id,cust.player_tracking_id
  FROM  ebet.`contact_info` cont ,ebet.`customer` cust WHERE cust.id=cont.customer_id ;


  INSERT INTO `g4winners2`.`tb_consumer_address`(consumer_id,address_line_1,address_line_2
 ,city,state,zip_code,country,county,region,record_modified_on,mailing_list_flag,
  funding_notification_flag,title,marketing1,partner_list_flag,company_name)

  SELECT  cont.customer_id, cont.address1,cont.address2,cont.city,cont.state_province,cont.postal_code,cont.country,cont.county,cont.suburb ,cont.updated_date, cont.mailing_list_flag,
  cont.funding_notification_flag,cont.title,cont.marketing1,cont.partner_list_flag,cont.company_name FROM ebet.`contact_info` cont;


  INSERT INTO  g4winners2.tb_consumer_funds(tote_account_id,pin,total_available_fund,onhold_fund,tote_account_status,last_access)
  SELECT  acc.acct_number,acc.encrypted_pin, FLOOR(acc.current_balance),FLOOR(acc.hold_balance), acc.status,acc.last_access
  FROM ebet.`account` acc;

  INSERT INTO g4winners2.`tb_system_settings`(setting_id,setting_name,setting_value,business_id )

   SELECT  0,conf.name,conf.value,conf.business_id FROM ebet.`config1` conf;

    COMMIT;
  END$$

DELIMITER ;