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`;


Related articles

• How Do I Optimize Magento? | Knowledge Center | Rackspace Hosting (rackspace.com)
• Mastering Magento – A Magento User’s Manual (stephanmiller.com)

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;

Related articles

• PHP MySql Delete not working (daniweb.com)
• PHP & MySQL (daniweb.com)

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

Related articles

• MySQL Tips For WordPress That Don’t Suck (bostinno.com)
• Troubleshooting MySQL Memory Usage (mysqlperformanceblog.com)
• Fixing MySQL tables in Fedora 17 (morefedora.blogspot.com)

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

Shell: mysqldump with only the table structure and no data

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

Related articles

• Regularly Back Up Your Large MySQL Website Database With MySQLDumper (makeuseof.com)
• Backup and Restore mysql database ?? (pankajkhalkar.wordpress.com)
• Moving a Production MySQL Database to Amazon RDS with Minimal Downtime (geehwan.posterous.com)
• How To Backup Your Website Through SSH Command Line (makeuseof.com)

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

Related Articles

• An argument for not using mysqldump (mysqlperformanceblog.com)
• 90% Answers (and when they’re wrong) (standalone-sysadmin.com)
• Learn To Program In 24 Hours (notes.torrez.org)
• Replication of MEMORY (HEAP) Tables (mysqlperformanceblog.com)
• Skyler Call: Backup Linux Server To Amazon S3 (skylercall.com)
• Baron Schwartz interviewed on WebPulp.tv (mysqlperformanceblog.com)
• Oracle updates MySQL 5.5 for Windows and web (v3.co.uk)
• MySQL in the cloud at Airbnb – Airbnb Engineering – Nerds Blog (nerds.airbnb.com)
• SetUp and test MySQL Replication in 20 easy steps (learnmysql.blogspot.com)
• How to Back Up a BlackBerry Enterprise Server (brighthub.com)

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';

Related Articles

• Processing Tweets with LingPipe: Search and CSV Data Structures (lingpipe-blog.com)
• SQL Pocket Guide (oreilly.com)
• How to export data from a SQL database into a CSV file and add some new data? (ask.metafilter.com)
• Percona white paper: Forecasting MySQL Scalability (mysqlperformanceblog.com)
• Advanced index analysis with mk-index-usage (mysqlperformanceblog.com)
• 6 CSV Related Libraries/Functions In Actionscript Save Your Time – Ntt.cc (ntt.cc)
• ProjectTemplate Version 0.1-3 Released (r-bloggers.com)
• DBF Viewer 2000 v3.45: View and Edit DBF Files with a Really Fast Tool (prweb.com)
• Enabling InnoDB databases (myeasylinux.wordpress.com)
• Integrate disparate data sources with Semantic Web technology (ibm.com)
• MySQL in the cloud at Airbnb – Airbnb Engineering – Nerds Blog (nerds.airbnb.com)
• Shard-Query adds parallelism to queries (mysqlperformanceblog.com)
• Clementson’s Blog: CLSQL – Part 1 (bc.tech.coop)
• Reset MySql root password (myeasylinux.wordpress.com)
• MySQL: How to Selectively Update a Table from a CSV List? (ask.metafilter.com)
• Do MySQL privileges really work like this? (ask.metafilter.com)
• Connecting to a MySQL server using Visual Studio 2010 and VB.Net (edugeek.net)
• Comparing MySQL and Postgres 9.0 Replication (theserverside.com)
• imabonehead: Top 20 Design Tips for MySQL Data Architects (slideshare.net)
• Add NoSQL Capabilities to MySQL With the HandlerSocket Plugin (readwriteweb.com)
• What’s New in MySQL 5.5 (blogs.sitepoint.com)
• Use gkDebconf to reconfigure debconf-using programs (ghacks.net)
• Schedule for MySQL-and-beyond conference is live (xaprb.com)
• How to Move from MySQL to CouchDB: Part 1 (couchone.com)
• Iptables Logs Mapping on GoogleMaps (rootshell.be)
• ovigia: 12 Best MySQL Database #Books for Your Library (thegeekstuff.com)

MSSQL: logs/httpd.pid overwritten — Unclean shutdown of previous Apache run

1. Stop all WAMP services and the SQL Server (Express) service

2. Get ntwdblib.dll version 2000.80.194.0 (http://webzila.com/dll/1/ntwdblib.zip OR get a free copy of ntwdblib.dll by downloading MS SQL Server 2000 SP4 from Microsoft Website. It contains a copy of ntwdblib.dll version 2000.80.194.0 in the system directory). Copy and replace any other versions of this file to the following locations:

• Your PHP binaries folder (ie C:\wamp\bin\php\php5.2.6)
• Your Apache binaries folder (ie C:\wamp\bin\apache\apache2.2.8\bin)
• Windows\System32\

3. Configure SQL Server to accept TCP connections and Named Pipes through the SQL server configuration manager (yes you will need the client tools installed).

4. Configure SQL Server for Mixed mode authentication and remember the password you set for sa

5. Start the SQL Server Service

6. Edit your php.ini and set the

mssql.secure_connection = On

7. Make sure your wamp folder has proper access rights (full control for ‘everyone’).

8. Start all services from the WAMP menu.

Related Articles

• SQL Injection Cheat Sheet (ferruh.mavituna.com)
• Switching Apache from Prefork to Worker MPM in RHEL / CentOS 5.x / Fedora 13 (jqueryin.com)
• Installing ColdFusion9, WAMP, Win7x64 (blogs.adobe.com)
• Install php5 and php4 in cPanel server (hackerz4u.com)
• Setting up Lisp & Hunchentoot on a web server (ponto-dot.com)
• imabonehead: RedHat / CentOS Install and Configure Cacti Network Graphing Tool (cyberciti.biz)
• LightSquid installation Steps – Squid Graphical Report Viewer (hackerz4u.com)
• How I completely remove a SQL Server database – Guaranteed to work – At least on my current setup (blogs.msdn.com)
• Uncommon Web installation (ikki.ws)
• Combining Virtualization and Microsoft SQL Server: The Path from Squeamish to Savvy (itexpertvoice.com)
• Steven Harms: Using Apache to decrypt SSL pages (sharms.org)
• Error while loading shared libraries (kholix.com)
• XAMPP: how to configure Apache for PHP (ask.metafilter.com)
• SQL UNIQUE Constraint | Content delivery network (xyzvn.com)
• Install WordPress on Your Computer Using WampServer (sixrevisions.com)
• Using WAMP Server with Infobright (infobright.org)
• Native local development environment in OS X (echodittolabs.org)
• Sophos upgrade need SQL help first to u/g MSDE to Sql 2005 (edugeek.net)

MySQL: query result to file using console

Given a query like below to log the results to a text file

select id from accounts where deleted=1;

try using the command below to output the result to file to ‘C:\test.txt’

select id from accounts where deleted=1 into outfile 'C:\\test.txt';

try using the command below to output the reuslt to csv

select id from accounts where deleted=1 INTO OUTFILE 'C:\\test.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Related Articles

• SQL Group By Statement: Explanation & Examples (brighthub.com)
• The story of one MySQL Upgrade (mysqlperformanceblog.com)
• Amazon Scales MySQL for Heavy Traffic (java.sys-con.com)
• SQL Replace Function: Explanation & Examples (brighthub.com)
• 
  http://dumptruck.rubyforge.org/svn/README
  (dumptruck.rubyforge.org)
• DBF Viewer 2000 v3.45: View and Edit DBF Files with a Really Fast Tool (prweb.com)
• Blind SQL Injector Tool (acunetix.com)
• Tips For Logging Your Own Statistics (readwriteweb.com)
• pgedit.com | Power Tools for PostgreSQL (pgedit.com)
• AWS Management Console Support for DB Engine Version Management (aws.typepad.com)
• Export MYSQL To Text File (edugeek.net)
• ARel 2.0: Active Record in Rails 3.0.2 (engineering.attinteractive.com)
• Using ioprofile with MySQL is cool ! (serge.frezefond.free.fr)
• JQuery 1.4.3 and jQuery Mobile Expected on October 16th (css.dzone.com)

mysql: ERRIR 3 : Error writing file ‘C:\WINDOWS\TEMP\MYD62.tmp’ 13631488

For me this was due to the temp folder was stored at a FAT32 drive. Changes the temp directory to NTFS drive and the problem solved.

On my.ini file change the variable tmpdir or create one using tmpdir = Your\path

Related Articles

• Howto setup mysql-cluster (beginners tutorial) (teabreak.pk)
• Mysql Essentials Part 1 (slideshare.net)
• Mysql pathological case? (almirkaric.com)