Edit your mysql config file
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
bind-address = 127.0.0.1
bind-address = 0.0.0.0
0.0.0.0 implies the mysql server can be accessible from any ip. If you want to allow only certain ips, then replace 127.0.0.1 with the desired ip
Restart mysql server
sudo service mysql restart
By default your mysql user is set to accept connection from localhost. To access database on the mysql server from external host, the user’s hostname need to be set.
CREATE USER '[username]'@'%' IDENTIFIED BY '[password]';
Replace [username] and [password] from above with your desired username and password
Grant desired permission to the user
GRANT ALL ON *.* TO 'username'@'%';
Now you should be able to access the database from host machine with the username that you just set.
Thank you for reading. Cheers!!!
Sql query to search for a value in csv field
SELECT * FROM `TABLE_NAME` WHERE `FIELD_NAME` REGEXP ‘(\,VALUE\,)|(^VALUE\,)|(\,VALUE$)’
$collection->addAttributeToFilter(‘FIELD_NAME’, array(‘regexp’=> ”(\,VALUE\,)|(^VALUE\,)|(\,VALUE$)’));
id |value |
1 |1,2,3,4,5,6 |
2 |2,4,11,12 |
Search for a row which has a value of 1 in the csv in value column
SELECT * FROM ‘test’ WHERE `value` REGEXP ‘(\,1\,)|(^1\,)|(\,1$)’
Have you ever wondered what sql queries are executed when we open a new page in magento or when a product is added to cart or an order is placed?
Well, Magento has a very useful profiler tool that lets us view all the queries that are run during a page request.
One way to see all the queries in the browser itself when we run a code is :
Activate the Zend SQL Profiler with the following node in your app/etc/local.xml
<resources> <default_setup> <connection> <profiler>1</profiler>
Then you can access the profiler somewhere in your code and retrieve a lot of informations about all executed queries:
$profiler = Mage::getSingleton('core/resource')->getConnection('core_write')->getProfiler();
To simply output all queries:
This code will echo all the queries that are executed before this code is executed. So you might wanna place this code at the end of index.php file at the root for quick viewing. but this may break the ajax calls. So beware and use it as you feel right.
We can also log all the queries in a text file.
For that we can follow the following process.
protected $_debug = true;
protected $_logAllQueries = true;
protected $_debugFile = ‘var/debug/pdo_mysql.log; // this is the file where the logs are stored. change the filename to your need. Also give read / write permission to the file
And its done. You can now view all the sql queries run. Beware Magento run loads and loads of query during a request. So you might have to forge your way through the log file to find the query you want 😀
Thank you for reading.
Hope this was helpful to you.