Mysql - ERROR 1148: The used command is not allowed with this MySQL version

This happen when you try to use the LOAD command.

How to Fixed.
* Add the --local-infile in you mysql command.

mysql -u root -pfatword test --local-infile

* Or Add the local-infile=1 in [mysql] section in your my.cnf file.

[mysql]
local-infile=1

TIPS: my.cnf commonly found in /etc/my.cnf

Magento - Adding existing product to new category via SQL

Most the time you edit the the product to assign the category or edit category and assign the products. Since magento admin is some too small on thousand product of catalog. Adding via SQL Query will faster to assigned.

Adding the Product into a category

INSERT INTO
catalog_category_product(category_id, product_id,position ) 
VALUES(48,85545,2);

Adding the Product to webstore

INSERT INTO catalog_category_product_index(
    category_id,
    product_id,
    position,
    is_parent,
    store_id,
    visibility
)
VALUES(
    48, -- CategoryID 
    85545, -- Product ID
    1, -- Posistion Normaly 1(1)
    1, -- Parent category ID
    3, -- Store ID
    4, -- Visibility
);

Mysql: How to recover mysql table.

One time I get real problem about mysql table. It was crashed and I don't have any back up on it.

After I get on an internet connected pc I googled it and this helpfull resources.
how-to-repair-corrupted-mysql-tables-using-myisamchk

MYSQL: Error 1062 - Duplicate key

Slave Replicate SLAVE_SKIP_COUNTER

In some reason this mostly happened when your slave server already out of synchronization to the master server.

The exercise, we need to skip that error Duplicate key and can be done by adding the line in the mysql config my.cnf

slave-skip-errors = 1062

If you encounter this kind of error. Just easy, you need only to skip the counter by by 1 until you fix it.

mysql> SLAVE STOP;
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; 
mysql> START SLAVE; 

Mysql Server error.

ERROR 1206 (HY000): The total number of locks exceeds the lock table size

THis is commonly occures when you are trying to execute query on a big data. For example when you are trying select/delete a million of record try chunk it by 5%-10% of the record.

Also you may try to higher the the buffer memory, as default it only 8MB which is too short for that kind of large data. To do it, modify the /etc/my.cnf

set-variable = innodb_buffer_pool_size=100M
set-variable = innodb_additional_mem_pool_size=10M
innodb_flush_log_at_trx_commit=1

Replication can often break

When a query cannot process properly on a slave (due to duplicate key or another MySQL error), replication will stop running until you manually resume it.

To avoid replication breaking due to duplicate keys, make sure your /etc/my.cnf configuration file includes these two lines:

slave_exec_mode = IDEMPOTENT 
slave-skip-error = 1062