Using ROLLBACK with MyISAM

Using ROLLBACK with MyISAM is useless. A ROLLBACK command is used to undo any DML that occurs during a transaction (i.e. START TRANSACTION and COMMIT). The MySQL default storage engine MyISAM does not support transactions.

It is easy with the SHOW GLOBAL STATUS command to see if your application code uses ROLLBACK. By performing two samples you can look at the delta over time. The statpack utility is one product that provides a human friendly display of this delta. As seen below, the use of ROLLBACK in combination with the read/write ratio and the my.cnf –skip-innodb indicate unnecessary database work.

====================================================================================================
                  Variable    Delta/Percentage         Per Second              Total
====================================================================================================

                                       Statement Activity
====================================================================================================

                   SELECT:        1,135,589                 1,309.79              189,279,510 (49.62%)
                   INSERT:            6,171                     7.12                  431,987 (0.11%)
                   UPDATE:            4,800                     5.54                  334,620 (0.09%)
                   DELETE:              312                     0.36                   17,910 (0.00%)
                  REPLACE:                0                     0.00                        0 (0.00%)
        INSERT ... SELECT:              121                     0.14                    4,042 (0.00%)
       REPLACE ... SELECT:               11                     0.01                      109 (0.00%)
             Multi UPDATE:                0                     0.00                       30 (0.00%)
             Multi DELETE:                0                     0.00                       28 (0.00%)
                   COMMIT:                0                     0.00                        0 (0.00%)
                 ROLLBACK:        1,154,987                 1,332.16              191,382,775 (50.17%)

If the ROLLBACK command doesn’t do anything you may be tempted to consider this doesn’t do much harm, think again. In the following example of statements analyzed via TCP packets, the ROLLBACK attributed to 21% of the execution time of all SQL in this sample.

# Profile
# Rank Query ID           Response time    Calls R/Call   Item
# ==== ================== ================ ===== ======== ================
#    1 0x4ED092EFA577DAB7     0.0106 24.8%     1   0.0106 SELECT p
#    2 0xC9ECBBF2C88C2336     0.0102 23.8%    52   0.0002 SELECT r_c
#    3 0x19C8068B5C1997CD     0.0092 21.6%   138   0.0001 ROLLBACK
#    4 0x448E4AEB7E02AF72     0.0091 21.3%    52   0.0002 SELECT r_t
#    5 0x56438040F4B2B894     0.0015  3.6%     2   0.0008 SELECT h_c
#    6 0x164962ED9B451586     0.0012  2.9%     9   0.0001 SELECT r
#    7 0x8FDE1484818AAACE     0.0008  1.9%     8   0.0001 SELECT p_c

In a well tuned system, the greatest time to execute an SQL statement is not the running of the SQL inside the MySQL kernel, it is the network latency of making the call, and the time taken to return the resultset requested.

In this extreme case on a production system, 1/2 the statements executed where unnecessary.

Uncovering this issue was three commands and less then 5 minutes of my time. The statpack report uncovered 4 additional red flags at the same time.

The NOSQL databases

What is NoSQL?
NoSQL is a kind of database that, unlike most relational databases, does not provide a SQL interface to manipulate data. NoSQL databases usually organize the data in a different way other than tables.

NoSQL databases are divided into three categories:

1. column-oriented
2. key-value pairs
3. document-oriented databases

SQL based relational databases do not scale well when they are distributed over multiple cluster nodes. Data partition is not an easy to implement solution when the applications use join queries and transactions.

NoSQL databases are not new. Actually, there were key-value pair based databases before relational database became popular.

List of NOSQL databases

Wide Column Store / Column Families

Hadoop / HBase
Cassandra
Hypertable


Document Store

CouchDB
MongoDB
Riak
Terrastore
ThruDB

Key Value / Tuple Store

Amazon SimpleDB
Chordless
Redis
Scalaris
Tokyo Cabinet / Tyrant
GT.M
Scalien
Berkeley DB
MemcacheDB
Mnesia
LightCloud
HamsterDB

Twitter plans to move out of MYSQL to Cassandra database

Twitter plans to move from MySQL to the Cassandra database. San Francisco-based Twitter currently uses a cluster of MySQL servers with a memcached caching system that "is quickly becoming prohibitively costly (in terms of manpower) to operate.







Twitter hopes that deploying the Apache Software Foundation's Cassandra database will improve the Uptime

GreenSQL – Open Source Database Firewall Software

GreenSQL is an Open Source database firewall used to protect databases from SQL injection attacks. GreenSQL works as a proxy and has built in support for MySQL. The logic is based on evaluation of SQL commands using a risk scoring matrix as well as blocking known db administrative commands (DROP, CREATE, etc). GreenSQL provides MySQL database security solution. GreenSQL is distributed under the GPL license.














More info here : http://www.greensql.net/

Boosting MySQL Performance and Scalability with the InnoDB Plugin

This doc provide technical overview of the MySQL pluggable storage engine architecture used by the new InnoDB Plugin, including the features, performance and scalability gains users can expect to see when enabling the InnoDB Plugin in MySQL 5.1.38 or later

https://docs.google.com/fileview?id=0B0O_BgcxiZJsM2RlOTVhZjUtZDhlYi00ZmEwLTg5M2YtN2IzYjBjMTgzOGE3&hl=en

MYSQL (Google code documentation)

Software engineers are most effective when programming with the assistance of tools, such as debuggers or IDEs. Below you will find tutorials that introduce some well-known and indispensible tools:

http://code.google.com/edu/tools101/mysql.html

myterm - extensible mysql command line client

myterm is an open-source project on launchpad. Myterm is a crossover between the standard mysql command line client and the concept of pipes and filters in bash.

http://www.jetprofiler.com/blog/8/myterm---extensible-mysql-command-line-client/

MYSQL MYISAM table corrupt

Today I faced with a issue of table corruption in MYSQL 

Investigating the cause, this is what is indicated in the mysql site

if any of the following events occur:

• The mysqld process is killed in the middle of a write.
  
• An unexpected computer shutdown occurs (for example, the computer is turned off).
  
• Hardware failures.
  
• You are using an external program (such as myisamchk) to modify a table that is being modified by the server at the same time.
  
• A software bug in the MySQL or MyISAM code.
  

Analyze table gave the result

Found 1213886 keys of 1213885

and table corrupt

Fortunately the error was rectified easily by the repair tables command

smart quotes when inserted to MYSQL

Got a problem last day when smart quotes got inserted to the mysql database.

The solution got from the following link worked

http://www.toao.net/48-replacing-smart-quotes-and-em-dashes-in-mysql

Mysql database migration special character issue

While migrating data from one database to another in mysql, I hit with a small issue.

it’s was changing to itâs in the database and get rendered as it’s on the website

Solution

For a varchar(255) column named “column_name” in a table named “example_table”:

ALTER TABLE example_table MODIFY column_name BINARY(255);
ALTER TABLE example_table MODIFY column_name VARCHAR(255) CHARACTER SET utf8;

For a text column named “text_column_name” in a table named “example_table”:

ALTER TABLE example_table MODIFY text_column_name BLOB;
ALTER TABLE example_table MODIFY text_column_name TEXT CHARACTER SET utf8;

reference

http://www.orthogonalthought.com/blog/index.php/2007/05/mysql-database-migration-and-special-characters/

MYSQL Sandbox

The easy way of installing a MySQL server

MySQL Sandbox is a tool that installs one or more MySQL servers within seconds, easily, securely, and with full control.

• Single sandboxes
• Standard replication
• Circular replication
• Multiple sandboxes (same version)
• Multiple sandboxes (different versions)
• Administrative tools

http://mysqlsandbox.net/

MySQL Storage Engine Comparison Guide June 2009

A comparison of storage engines in MYSQL

https://docs.google.com/fileview?id=0B64gKKyEcYx1M2NlMWJiOWItMzJhNy00ZDZmLWJjMTgtMTMxYmY5MGFkOTA2&hl=en

How to find duplicate Rows in SQL

Find the duplicate rows in a mysql table

SELECT username,

COUNT(username) AS NumOccurrences

FROM users

GROUP BY username

HAVING ( COUNT(username) > 1 )

mysqldiff MYSQL Database structure Comparison Tool

mysqldiff -- a utility for comparing MySQL database structures

http://adamspiers.org/computing/mysqldiff/

MySqloit , SQL Injection Takeover Tool For LAMP

MySqloit is a SQL Injection takeover tool focused on LAMP (Linux, Apache, MySQL, PHP) and WAMP (Windows, Apache, MySQL, PHP) platforms. It has the ability to upload and execute metasploit shellcodes through the MySql SQL Injection vulnerabilities.

http://www.darknet.org.uk/2009/09/mysqloit-sql-injection-takeover-tool-for-lamp/

Free PHP-MYSQL Hosting

http://fa.by/free-hosting free mysql php hosting

MYSQL Query Browser

MySQL Query Browser. Part of MySQL GUI Tools – native toolkit from Sun for managing MySQL databases.

What it does

Query browser allows to make connection to local or remote MySQL databases and run queries on it.

Strong features

• native tool that doesn’t need intermediary drivers (like AnySQL Maestro does);


• as result – good performance;


• some import and multiply export capabilities;


• embedded documentation for MySQL syntax and functions.

Downsides

No internal manager for queries. They can only be saved as external plain text files. Makes hard to manage large amount of those.

Overall

While it is hardly flashy, Query Browser is native, cross-platform and open source tool that works without installation. It doesn’t come with more complex visual functions but is just fine for writing queries, running them and exporting results.

Home&download http://dev.mysql.com/downloads/gui-tools/5.0.html

How to find un-indexed queries in MySQL, without using the log

How to find un-indexed queries in MySQL, without using the log: "

You probably know that it’s possible to set configuration variables to log queries that don’t use indexes to the slow query log in MySQL. This is a good way to find tables that might need indexes.

But what if the slow query log isn’t enabled and you are using (or consulting on) MySQL 5.0 or earlier, where it can’t be enabled on the fly unless you’re using a patched server such as Percona’s enhanced builds? You can still capture these queries.

The key is knowing what it really means for a query to “not use an index.” There are two conditions that trigger this — not using an index at all, or not using a “good” index. Both of these set a bit. If either bit is set, the query is captured by the filter and logged. Both of these bits also set a corresponding bit in the protocol, so the TCP response to the client actually says “here comes the result of your query, and by the way it didn’t use an index.” This is very useful information.

I’m sure you can see where this is going. Let’s use tcpdump to capture queries, consume the output with mk-query-digest, and filter out all but ones that don’t use an index or use no good index:

$ sudo tcpdump -i lo port 3306 -s 65535  -x -n -q -tttt \
| mk-query-digest --type tcpdump \
--filter '($event->{No_index_used} eq "Yes" || $event->{No_good_index_used} eq "Yes")'

If I run a few full table scans now, and then cancel mk-query-digest, I’ll get output like the following (abbreviated for clarity):

#              pct   total     min     max     avg     95%  stddev  median
# Count        100       8
# Exec time    100     5ms   511us   857us   604us   839us   106us   582us
# 100% (8)    No_index_used
select * from t\G

You can see I ran the query 8 times and each time it reported back that it didn’t use an index. This is a dead-easy way to find queries that might not have an index available!

Want to print out tables from those queries? You can do that too. Just add --group-by tables --report-format profile to the command above, and instead of grouping queries together by the query text, it’ll group them by the tables they mention. Then the report will contain one item per table and you’ll just see a summary at the end, like so:

# Rank Query ID           Response time    Calls   R/Call     Item
# ==== ================== ================ ======= ========== ====
#    1 0x                     0.0037 100.0%       8   0.000467 test.t

"

Zmanda Backup

This document describes how to install and configure Zmanda for MYSQL (ZRM)

Step 1

Create Mysql User

CREATE USER user [IDENTIFIED BY [PASSWORD] 'clear-text-password']

Step 2

Install 

-bash-3.1# ls -lh MySQL-zrm-1* 
-rw-rw-r-- 1 root root 101K Oct 16 17:29 MySQL-zrm-1.1-1.noarch.rpm

-bash-3.1# rpm -ivh MySQL-zrm-1.1-1.noarch.rpm
Preparing...                ########################################### [100%]
  1:MySQL-zrm              ########################################### [100%]