cancel
Showing results for 
Search instead for 
Did you mean: 

Run sql cron job inside Magento

Highlighted

Run sql cron job inside Magento

Hello all,

 

How can I run this sql query below inside Magento 1.9.4 (preferably after each customer registration or every 2 minutes as a cron job)? This is because my customer registration
is not updating the email column in customer_entity table (new account is opened but customer can't login) and I have not been able to debug the cause and rectify hence this approach.

UPDATE customer_entity INNER JOIN customer_entity_varchar ON customer_entity.entity_id = customer_entity_varchar.entity_id SET customer_entity.email = customer_entity_varchar.value WHERE attribute_id = 9

 

Will appreciate your suggestions. Thanks a lot.

11 REPLIES 11
Highlighted

Re: Run sql cron job inside Magento

Hi @richSG 

It is not a suggested approach.  You should identify the root cause.

May be any customer registration event observer creating issue,

Still you want to apply patch then you can do using customer_register_success event observer.

I hope it will help you!

----
Problem solved? Click Accept as Solution!
Highlighted

Re: Run sql cron job inside Magento

Thank you Vimal Parihar

I would have loved to identify the root cause but I'm stuck on how to go about it. My knowledge of Magento is not that good yet.  However, I would be grateful if you could tell me more on how to apply a customer_register_success event observer patch.

 

Thanks

Highlighted

Re: Run sql cron job inside Magento

Hi @richSG 

I have attached a sample module for customer_register_success event,

https://drive.google.com/file/d/15pWEeD_9Kfv2ejz-sUuAvY6_sS9s1HAO/view?usp=sharing

You can update code accordingly in the "app/code/local/VP/CustomerOverride/Model/Observer.php" file.

I hope it will help you!

----
Problem solved? Click Accept as Solution!
Highlighted

Re: Run sql cron job inside Magento

Thanks a lot Vimal,

 

 I have downloaded the attached files and uploaded accordingly. I have updated "app/code/local/VP/CustomerOverride/Model/Observer.php" like

 

<?php
class VP_CustomerOverride_Model_Observer
{

public function UpdateCustomer(Varien_Event_Observer $observer)
{
//Mage::dispatchEvent('admin_session_user_login_success', array('user'=>$user));
//$user = $observer->getEvent()->getUser();
//$user->doSomething();
$connection = Mage::getSingleton('core/resource')->getConnection('write');

$connection->query("UPDATE `customer_entity` INNER JOIN `customer_entity_varchar` 
ON `customer_entity`.`entity_id` = `customer_entity_varchar`.`entity_id` 
SET `customer_entity`.`email` = `customer_entity_varchar`.`value` 
WHERE `attribute_id` = 9 AND `customer_entity`.`email` IS NULL");

}

}

 

I am totally not sure I did the right thing here thus nothing happened. When new account is opened the customer_entity table is not updated and there is nothing in the log either. What did I do wrong?

 

Really appreciated your efforts.

 

Many thanks.

Re: Run sql cron job inside Magento

Hi @richSG ,

The query not looks correct .

Mage::dispatchEvent('admin_session_user_login_success', array('user'=>$user));
$user = $observer->getEvent()->getUser();

You can print data to check info for a while.

Spoiler
print_r($user); die;

After that you can update in the table. Do you want to update in main customer_entity table only?

 

 

----
Problem solved? Click Accept as Solution!
Highlighted

Re: Run sql cron job inside Magento

Thank you Vimal,

 

I will try and explain the situation a little better just so you have a better understanding of my problem.

 

When a customer registers, the account is opened correctly. The email is listed on the Manage Customers page in the Admin backend correctly.
The email is listed in the database customer_entity_varchar table but not listed in the customer_entity table.
The consequence of the email not being listed on customer_entity table is that when a customers registers, the customer will not be able to sign back into his/her Magento account once he logs out. He will get the error that email or password is not correct.
If I then copy the email address from the admin backend (Manage Customers page) or from customer_entity_varchar table and paste it in the database customer_entity table (to the corresponding entity_id) then the customer will be able to sign in.
Thus, after customer registration,
- the email address can be seen in Admin Manage Customers page
- the email address can be seen in database customer_entity_varchar table
- the email address cannot be seen in database customer_entity table though all other columns in the table are filled with data (but Customer cannot sign in)
- but if I copy email address from Admin or from customer_entity_varchar table to database customer_entity table, then customer can sign in.

So the question is why is the register process not saving the email to the database customer_entity table?

 


Spoiler
Do you want to update in main customer_entity table only?

Yes. The table needed to be updated only with email address. All other information are there already.

 


 

Highlighted

Re: Run sql cron job inside Magento

 

<?php
class VP_CustomerOverride_Model_Observer
{

			public function UpdateCustomer(Varien_Event_Observer $observer)
			{
				Mage::dispatchEvent('admin_session_user_login_success', array('user'=>$user));
				$user = $observer->getEvent()->getUser();
				//$user->doSomething();
				print_r($user); die;

			}
		
}

Is this observer.php good?

 

UPDATE `mgyp_customer_entity` INNER JOIN `mgyp_customer_entity_varchar` 
ON `mgyp_customer_entity`.`entity_id` = `mgyp_customer_entity_varchar`.`entity_id` 
SET `mgyp_customer_entity`.`email` = `mgyp_customer_entity_varchar`.`value` 
WHERE `attribute_id` = 9 AND `mgyp_customer_entity`.`email` IS NULL

Where do I need to use the query or I do not need to use the query anywhere?

 

 

Thanks again!

Highlighted

Re: Run sql cron job inside Magento

Hi @richSG 

This looks fine.

<?php
class VP_CustomerOverride_Model_Observer
{

public function UpdateCustomer(Varien_Event_Observer $observer)
{
$connection = Mage::getSingleton('core/resource')->getConnection('write');

$connection->query("UPDATE `customer_entity` INNER JOIN `customer_entity_varchar` 
ON `customer_entity`.`entity_id` = `customer_entity_varchar`.`entity_id` 
SET `customer_entity`.`email` = `customer_entity_varchar`.`value` 
WHERE `attribute_id` = 9 AND `customer_entity`.`email` IS NULL");

}

}


If it is not working and check once is you event is called after echo die in observer and print data what you are receiving.


<?php
class VP_CustomerOverride_Model_Observer
{

public function UpdateCustomer(Varien_Event_Observer $observer)
{
   $customer = $observer->getEvent()->getCustomer();
   echo "<pre>";print_r($customer->getData());die;
}

}
----
Problem solved? Click Accept as Solution!
Highlighted

Re: Run sql cron job inside Magento

Hi Vimal,


If it is not working and check once is you event is called after echo die in observer and print data what you are receiving.

Could you please rephrase what you are saying here because I could not understand what I'm supposed to do.

 

echo "<pre>";print_r($customer->getData());die;

Is the customer data supposed to print to the log or to the browser? otherwise where should I look? At the moment there is till nothing about customer data in the log and the query is not working with the observer.

 

Thank you again.