Got error 28 from storage engine – Mysql Database

Friday, 19. July 2013

I came to a surprising problem this morning with one of our client’s site where all of a sudden Magento was throwing error Got error 28 from storage engine on error logs

Had no clue what that means really so looking around the server ended up noting the server is actually run out of space!

Shame, but that happened to be the reason behind this problem and things went back to normal after fixing the space issue.

Oh, well, shift happen! :)

reset XAMPP MySQL root password via PHPMYADMIN

Thursday, 8. November 2012

A little snippet for resetting a mysql root password via phpmyadmin

UPDATE mysql.USER SET Password=PASSWORD('password') WHERE USER='root';
FLUSH PRIVILEGES;

RESET Mysql Root Password In LAMPP or XAMPP

Sunday, 30. September 2012

If you are reading this, chances are you have either lost or forgotten your mysql root password and definitely in need of help!

OK – this solution assumes you have access to your Lampp installation files.

- Go to your config file: lampp/etc/my.cnf 
- Open the file and look for text: [mysqld]
- Under it, insert text: skip-grant-tables
- Restart MySQL

Now access your phpmyadmin: localhost/phpmyadmin. You should be able to log in without the need of password

You can now change/reset the root password to something else:

UPDATE mysql.USER SET Password='new_password' WHERE USER='root'

Enjoy!

Zend_Db_Statement_Exception Lock wait timeout exceeded try restarting transaction

Friday, 13. April 2012

If you have ever come across this error Next exception ‘Zend_Db_Statement_Exception’ with message ‘SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction’ while running long queries these can possibly be one of your solutions

1) Best option is to try and reduce/break the size of your query into small queries.

2) If reducing the size of your queries is not an option, you may try this. In you my.cnf file (somewhere /etc/mysql/my.cnf) look for and change/add the following entry

innodb_lock_wait_timeout = 50 # Set this to a big number to cater your needs!

REMEMBER: You will need to Restart mysql for changes to take effect. Then re-run your query.

**IMPORTANT
Remember to change the value back to small number while on production server. Its there as a small value for a reason!!

Mysql – Disable Foreign key with Mysqldump

Monday, 28. November 2011

Here is a script/flag you will need to disable foreign keys while backing up your database with mysqldump

mysql -uUSER -pPASSWORS --disable-keys DATABASE_NAME > dump.sql

Enjoy

Can’t connect to local MySQL server through socket – Ubuntu xampp and MySql

Sunday, 30. October 2011

If you have come across this problem while connect to MySQL (possibly via command line tool) this solution may work for you. Th issue is mainly because the script is looking for a file – mysqld.sock – in order to be able to connect as localhost but in the wrong location.

MySQL Conenction Error

Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

Solution
On your terminal run the following two commands

sudo mkdir /var/run/mysqld/

Next run this

sudo ln -s /opt/lampp/var/mysql/mysql.sock /var/run/mysqld/mysqld.sock

Now try again to connect to MySql using your configured MySQL username and password

mysql -u[username] -p[password]

Exclude Tables With Mysqldump – Reduce the Size of Magento database backup

Wednesday, 12. October 2011

If you have ever wondered or be in need of excluding certain tables from your Mysqldump, here is snippet for you!

Basically you only have to specify the –ignore-table flag with your Mysqldump command. In order to exclude as multiple tables you will need to repeat the command as many times as the tables you want to exclude.

One important thing to remember, the –ignore-table flag expects you to specify the full table path i.e include the database name such as dbname.tablename.

Example: How to exclude Magento log table from mysqldump

mysqldump -uUSERNAME -pPASSWORD  --ignore-table=<DBASE_NAME>.log_url --ignore-table=<DBASE_NAME>.log_url_info  --ignore-table=<DBASE_NAME>.log_visitor --ignore-table=<DBASE_NAME>.log_visitor_info  --ignore-table=<DBASE_NAME>.log_customer --ignore-table=<DBASE_NAME>.log_quote --ignore-table=<DBASE_NAME>.report_event  --ignore-table=<DBASE_NAME>.index_event --ignore-table=<DBASE_NAME>.index_process_event  --ignore-table=<DBASE_NAME>.report_viewed_product_index --ignore-table=<DBASE_NAME>.dataflow_batch_export  --ignore-table=<DBASE_NAME>.dataflow_batch_import <DBASE_NAME> | gzip > <DBASE_NAME>_dump.sql.gz

Disable Foreign Key Checks AND Ignore inserts

mysqldump -uUSERNAME -pPASSWORD  --ignore-table=<DBASE_NAME>.log_url --ignore-table=<DBASE_NAME>.log_url_info  --ignore-table=<DBASE_NAME>.log_visitor --ignore-table=<DBASE_NAME>.log_visitor_info  --ignore-table=<DBASE_NAME>.log_customer --ignore-table=<DBASE_NAME>.log_quote --ignore-table=<DBASE_NAME>.report_event  --ignore-table=<DBASE_NAME>.index_event --ignore-table=<DBASE_NAME>.index_process_event  --ignore-table=<DBASE_NAME>.report_viewed_product_index --ignore-table=<DBASE_NAME>.dataflow_batch_export  --ignore-table=<DBASE_NAME>.dataflow_batch_import --disable-keys --extended-insert --insert-ignore  <DBASE_NAME> | gzip > <DBASE_NAME>_dump.sql.gz

Compress MySqlDump Database Output Terminal command

Thursday, 22. September 2011

If you want to compress the output of your mysqldump here is the command you will need to use.

mysqldump -uUSER -pPASSWORD DATABASE|gzip > dump.SQL.gz

You can also use bzip2 compression:

mysqldump -uUSER -pPASSWORD DATABASE|bzip2 > dump.SQL.bz2

Enjoy!

Mysql – Got a packet bigger than ‘max_allowed_packet’ bytes

Friday, 12. August 2011

If you have been restoring a database backup via a command line utility and came across this type of error, this command my help you out if you give it a try!

It allows you to set up the max packet limitations only for this command run

mysql --max_allowed_packet=200M -uroot -p mydb < mydump.sql

If you want to alter you entire server configurations, you will need to do the following:

1) Locate your my.cnf or my.cnf file
2) Locate the section [mysqld] and somewhere underneath you can change/add this line:

[mysqldump]
max_allowed_packet = YOUR_VALUE

You can now change YOUR_VALUE to your new preferred limit.

You can also use command line facility to execute the following commands on your machine/server:

set global net_buffer_length=SOME_BIG_VALUE;  (i.e 1000000)
set global max_allowed_packet=SOME_BIG_VALUE; (i.e. 1000000000;

Magento Admin Login Failure – Can not login to Magento Admin Page

Saturday, 30. July 2011

I have recently come across what appeared to be a rather strange issue that I never seen before.

Basically, I did move the site from a client’s server to out develepment environment and everything seemed to be OK. The moment i tried to login to the admin page, the page was simply not doing anything. No errors, No warnings! Just seemed to try to redirect and fails.

After a little head-scratching moments, I realized it was the cookies that were causing the indecency!

Basically, the client did specify the cookie domains to use for his admin login and since my development domain didn’t match, I simply couldn’t login.

To resolve the issue (the easy way i think) is to connect to MySql database using either PhpMyadmin or Mysql Query analyzer and look for the config_data table

Search for all paths with “cookie” data – something like this.

SELECT * FROM `core_config_data` WHERE `path` LIKE '%cookie%'

Remove the domain entries. All should be good to go now.