Magento: Remove best seller products from admin dashboard

Use the below MySQL queries:


TRUNCATE `sales_bestsellers_aggregated_daily`;
TRUNCATE `sales_bestsellers_aggregated_monthly`;
TRUNCATE `sales_bestsellers_aggregated_yearly`;

Magento: Remove best seller products from admin dashboard

MySQL: Query to get the MySQL database size in MB

SELECT 
table_schema "Database Name", 
SUM(data_length+index_length)/1024/1024 "Database Size in MB", 
SUM(data_free)/1024/1024 "Free Space in MB" 
FROM information_schema.TABLES 
GROUP BY table_schema;
MySQL: Query to get the MySQL database size in MB

The total number of locks exceeds the lock table size

This issue can be resolved by setting the higher values for the MySQL variable “innodb_buffer_pool_size”. The default value for innodb_buffer_pool_size will be 8,388,608.

To change the settings value for “innodb_buffer_pool_size” please see the below set.

1) Locate the file my.cnf from the server. For Linus servers this will be mostly at /etc/my.cnf
2) Add the line “innodb_buffer_pool_size=64MB” to this file
3) Restart the MySQL server

To restart the MySQL server, you can use anyone of the below 2 options:

1) service mysqld restart
2) /etc/init.d/mysqld restart

The total number of locks exceeds the lock table size

ACT the database could not be accessed

Receiving the error:

The database could not be accessed. In order to access this database, check your networkconnection and verify that your database server is available. It may be necessary to disable andy firewall software on you computer or the server.

The 2 most likely causes on a standalone are a damaged or invalid .pad file or SQL is not running, or is damaged.

.PAD file

  • Close ACT!
  • Click your Windows Start button, choose Run and type in actdiag
  • When ACT! Diagnostics comes up, click Databases, the Database List
  • Locate your database in the list, right-click on it and select Detach. Make sure you know the filepath to your database before detaching it.
  • When the database has been detached, open My Computer (or Windows Explorer) and browse to the location of your database.
  • Locate the .PAD file for your database [database name].pad and delete it
  • Reopen ACT!…you will get a message that the last database opened cannot found, then will be taken to the Open Database dialog box
  • At the bottom of this box, change the Files of Type to .adf, browse to the location of your database and double-click on the .adf file for your database
  • You will get a message that ACT! will verify your database, click OK and ACT! will reattach your database to SQL and create a new .pad file
SQL Server
  • Close ACT!
  • Click on your Windows Start button, select run, and type in services.msc
  • When the list of Local Services comes up, scroll down and locate the service called SQL Server (ACT7)
  • Look in the Status column and make sure it says Started…if not, then right-click on the service and select Start
Courtesy:

Act Error: The Database Could Not Be Accessed

ACT the database could not be accessed

Shell: mysqldump with only the table structure and no data

mysqldump -h localhost -u root -p --no-data --compact database_name > database_name.sql

Shell: mysqldump with only the table structure and no data

Backuping a single table using mysqldump

Backuping a single table from a database
mysqldump -u USERNAME -p DATABASE_NAME TABLE_NAME > /path/to/folder/file_name.sql

Backuping a single table using mysqldump

MySql: query result in csv file using command

Form the mysql prompt use the below command:

SELECT * FROM table_name
INTO OUTFILE '/tmp/table_name.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
MySql: query result in csv file using command